The palest ink is better than best memory——好记性不如烂笔头。2012补记
一、关键词
SQL优化、执行计划、explain、using filesort、using temporary
二、摘要
开发中,为满足一业务功能,使用mysql书写sql时,一条sql往往有多种写法,那么我们就需要选择执行效率比较高的sql。
因此要比较分析sql的执行过程,且同一条sql我们要比较选择使用最优索引。
通过explain命令可以得到:
整体执行顺序(子查询)、查询类型、使用什么表(表 泛指)、数据访问/读取操作类型、使用到索引等。
先整体了解下SQL查询的基本执行过程:
1)应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析
2)检查权限、MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(query
plan),并可以被缓存3)如果存在索引,那么先扫描索引,如果数据被索引覆盖,那么不需要额外的查找,如果不是,根据索引查找和读取对应的记录
4)如果有关联查询,查询次序是扫描第一张表找到满足条件的记录,按照第一张表和第二张表的关联键值,扫描第二张表查找满足条件的记录,按此顺序循环
5)输出查询结果,并记录binary logs
三、Explain返回信息:
列名 | 说明 |
---|---|
id | 执行编号,标识select所属的行。如果在语句中没子查询或关联查询,只有唯一的select,每行都将显示1。否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置 |
select_type | 显示本行是简单或复杂select。如果查询有任何复杂的子查询,则最外层标记为PRIMARY(DERIVED、UNION、UNION RESUlT) |
table | 访问引用哪个表(引用某个查询,如“derived3”) |
type | 数据访问/读取操作类型(ALL、index、range、ref、eq_ref、const/system、NULL) |
possible_keys | 揭示哪一些索引可能有利于高效的查找 |
key | 显示mysql决定采用哪个索引来优化查询 |
key_len | 显示mysql在索引里使用的字节数 |
ref | 显示了之前的表在key列记录的索引中查找值所用的列或常量 |
rows | 为了找到所需的行而需要读取的行数,估算值,不精确。通过把所有rows列值相乘,可粗略估算整个查询会检查的行数 |
Extra | 额外信息,如using index、filesort等 |
- select_type列:
select_type类型 | 说明 |
---|---|
SUBQUERY | 在select列表中的子查询,如SELECT *,(SELECT id FROM product_info) AS id FROM product_info |
DERIVED | 在from子语句中子查询,如SELECT * FROM product_info p1 ,(SELECT * FROM product_info) p2.Mysql会递归执行,并把结果放到临时表中 |
UNION | 在UNION中第二个和随后的SELECT被标记为UNION |
UNION RESULT | 用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT |
- type列(依次从最差到最优):
type类型 | 说明 |
---|---|
All | 最坏的情况,从头到尾全表扫描 |
index | 和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index,说明正在使用覆盖索引,只扫描索引的数据,它比按索引次序全表扫描的开销要小很多 |
range | 范围扫描,一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range |
ref | 一种索引访问,它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生 |
eq_ref | 最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 (高效) |
const/system | 当主键放入where子句时,mysql把这个查询转为一个常量(高效) |
Null | 意味说mysql能在优化阶段分解查询语句,在执行阶段甚至用不到访问表或索引(高效) |
- Extra列常见情况(需要优化):
Extra情况 | 说明 |
---|---|
Using temporary | 表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by |
Using filesort | 表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序” |
四、事例(2012公司项目):
案例1:
EXPLAIN
SELECT e1.*,0 AS 'hasCount' FROM eticketdetail e1 WHERE e1.brandUserId =5303 AND e1.status =1 AND e1.parentId =0
UNION
SELECT e2.*,0 AS 'hasCount' FROM eticketdetail e2 WHERE e2.status =1 AND e2.id IN (SELECT e3.parentId FROM eticketdetail e3 WHERE e3.parentId >0 AND e3.brandUserId = 5303)
ORDER BY createtime LIMIT 0,10
EXPLAIN
SELECT e1.*,0 AS 'hasCount' FROM eticketdetail e1 WHERE e1.brandUserId =5303 AND e1.status =1 AND e1.parentId =0
UNION
SELECT e2.*,0 AS 'hasCount' FROM eticketdetail e2 WHERE e2.status =1 AND EXISTS (SELECT 1 FROM eticketdetail e3 WHERE e3.parentId >0 AND e3.brandUserId = 5303)
ORDER BY createtime LIMIT 0,10
EXPLAIN
SELECT e1.*,0 AS 'hasCount' FROM eticketdetail e1 WHERE e1.brandUserId =5303 AND e1.status =1 AND e1.parentId =0
UNION
SELECT e2.*,0 AS 'hasCount' FROM eticketdetail e2 INNER JOIN (SELECT e3.parentId FROM eticketdetail e3 WHERE e3.parentId >0 AND e3.brandUserId = 5303) tab ON e2.id = tab.parentid WHERE e2.status =1
ORDER BY createtime LIMIT 0,10
案例2:
第一种写法:
SELECT
mp.*
FROM memberpromotion mp
INNER JOIN memberpromotionlocation mpl
ON mpl.MemberPromotionID = mp.ID
WHERE mp.status = 1
AND mp.BrandUserID = 2402
AND mpl.shopId = 0
第二种写法:
SELECT
mp.*
FROM (SELECT * FROM memberpromotion memb WHERE memb.BrandUserID = 2402) mp
INNER JOIN memberpromotionlocation mpl
ON mpl.MemberPromotionID = mp.ID
WHERE mp.status = 1
AND mpl.shopId = 0
案例3:
第一幅图语句:
SELECT SQL_NO_CACHE
e.*,
(SELECT
COUNT(id)
FROM eticketdetail
WHERE userId = e.userId
AND orderId = e.orderId
AND promotionId = e.promotionId
AND promotionProductId = e.promotionProductId
AND STATUS != 3
AND STATUS = 0 ) AS 'hasCount'
FROM eticketdetail e
WHERE userId = 5302
AND isGift = 0 AND isReal = 0 AND STATUS = 0
GROUP BY userId,orderId,promotionId,promotionProductId
UNION
SELECT e.*, 1 AS 'hasCount' FROM eticketdetail e
WHERE userId = 5302 AND isGift = 1 AND STATUS = 0
ORDER BY CreateTime DESC
第二幅图语句:(UNION改为UNION ALL)
SELECT SQL_NO_CACHE
e.*,
(SELECT
COUNT(id)
FROM eticketdetail
WHERE userId = e.userId
AND orderId = e.orderId
AND promotionId = e.promotionId
AND promotionProductId = e.promotionProductId
AND STATUS != 3
AND STATUS = 0 ) AS 'hasCount'
FROM eticketdetail e
WHERE userId = 5302
AND isGift = 0
AND isReal = 0
AND STATUS = 0
GROUP BY userId,orderId,promotionId,promotionProductId
UNION ALL
SELECT e.*, 1 AS 'hasCount' FROM eticketdetail e
WHERE userId = 5302 AND isGift = 1 AND STATUS = 0
ORDER BY CreateTime DESC
第三幅图: 增加userId索引后
第四幅图:
SELECT
e.*,
tab.t AS 'hasCount'
FROM eticketdetail e
INNER JOIN (
SELECT id,COUNT(id) AS t FROM eticketdetail WHERE userid=5302 AND isGift = 0 AND isReal = 0 AND STATUS != 3 AND STATUS = 0 GROUP BY orderId,promotionId,promotionProductId ORDER BY NULL
) tab ON tab.id = e.ID
WHERE userId = 5302
AND isGift = 0
AND isReal = 0
AND STATUS = 0
GROUP BY orderId,promotionId,promotionProductId
UNION ALL
SELECT e.*, 1 AS 'hasCount' FROM eticketdetail e
WHERE userId =5302 AND isGift = 1 AND STATUS = 0
ORDER BY CreateTime DESC
这玩意,只有动手多折腾比较几下~
五、参考:
1.《高性能MySQL》第3版
2.官网:http://dev.mysql.com/doc/refman/5.7/en/explain-output.html