Mysql查询优化——Explain使用

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