帮看一下2个sql,哪个效率比较高

MySql 码拜 6年前 (2016-02-18) 426次浏览
EXPLAIN 
SELECT A.mid, A.serial_id, A.name, A.singer, A.path, 
A.original_track, A.sound_track, A.start_volume_1, 
A.start_volume_2, A.lyric, A.prelude, A.match, 
B.name AS effect, C.name AS VERSION 
FROM media A 
LEFT JOIN media_effect B ON A.effect = B.id 
LEFT JOIN media_version C ON A.version=C.id 
WHERE A.enabled=1 AND A.header LIKE "%%" 
ORDER BY A.lang_part, A.words, A.header, A.desc_count ASC 
LIMIT 87030,90;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	ALL	enabled_match	\N	\N	\N	195079	Using where; Using temporary; Using filesort
1	SIMPLE	B	ALL	PRIMARY	\N	\N	\N	3	Using where; Using join buffer (Block Nested Loop)
1	SIMPLE	C	eq_ref	PRIMARY	PRIMARY	4	yiqiding_ktv.A.version	1	Using where
执行时间1.7s
EXPLAIN
SELECT A.mid, A.serial_id, A.name, A.singer, A.path, 
A.original_track, A.sound_track, A.start_volume_1, 
A.start_volume_2, A.lyric, A.prelude, A.match, 
B.name AS effect, C.name AS VERSION 
FROM 
( SELECT *
FROM media 
WHERE enabled=1 AND header LIKE "%%"  
ORDER BY lang_part, words, header, desc_count ASC 
LIMIT 87030,90) A 
LEFT JOIN media_effect B ON A.effect = B.id 
LEFT JOIN media_version C ON A.version=C.id 
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL	\N	\N	\N	\N	87120	\N
1	PRIMARY	B	ALL	PRIMARY	\N	\N	\N	3	Using where; Using join buffer (Block Nested Loop)
1	PRIMARY	C	eq_ref	PRIMARY	PRIMARY	4	A.version	1	Using where
2	DERIVED	media	ALL	enabled_match	\N	\N	\N	195079	Using where; Using filesort
执行时间1s

问一下这两个执行出来那个比较好,有没有其他更好的写法?谢谢请指点。

解决方案

20

从时间来看,第二个要稍微好一点。
第二个是,先limit出你要的记录,然后再和b,c表关联,这样的开销要稍微好一点,不过前提是a表和b,c是一对一的关系,否则可能会导致结果不同。
另外,可以开启跟踪:

mysql> set @@profiling = 1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select count(*) from test;
+--+
| count(*) |
+--+
|       11 |
+--+
1 row in set (0.11 sec)
mysql> show profiles;
+--+--+--+
| Query_ID | Duration     | Query                     |
+--+--+--+
|        1 | 229.80533675 | select count(*) from tb   |
|        2 |   0.00030400 | set @@profiling = 1       |
|        3 |   0.11819400 | select count(*) from test |
+--+--+--+
3 rows in set, 1 warning (0.04 sec)
mysql> show profile for query 3;
+--+--+
| Status               | Duration |
+--+--+
| starting             | 0.000079 |
| checking permissions | 0.000011 |
| Opening tables       | 0.045497 |
| init                 | 0.000041 |
| System lock          | 0.020266 |
| optimizing           | 0.000021 |
| statistics           | 0.000033 |
| preparing            | 0.000023 |
| executing            | 0.000020 |
| Sending data         | 0.041491 |
| end                  | 0.000019 |
| query end            | 0.000036 |
| closing tables       | 0.000021 |
| freeing items        | 0.000198 |
| cleaning up          | 0.010438 |
+--+--+
15 rows in set, 1 warning (0.28 sec)

20

第二种,第二种是小表驱动b,c,
第一种是大表驱动b,c,
header LIKE “%%”是什么意思?
a表没有用到索引们可以看下建立索引试下。
profiling看下过程用的时间多。

引用 4 楼 yupeigu 的回复:
Quote: 引用 2 楼 baidu_32386903 的回复:

嗯嗯 谢谢  不知道是不是还有其他更好的写法

语句可以有很多种写法,但是你的这种写法用join,已经是比较好的了,至少比子查询要好。
接下去,假如还要进一步提高性能,就是要看看你的语句中假如不加查询条件记录会有多少条,加了之后又是多少,看看能否可以加上索引,来减少排序和过滤的开销。

是的。这种一步步去测试。
本人关注了你,为什么又会变成没有关注?bug?


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明帮看一下2个sql,哪个效率比较高
喜欢 (0)
[1034331897@qq.com]
分享 (0)