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

更多相关文章

  1. 用sql语句如何实现小数位数四舍六入,小数位数是五的不变?
  2. sqlserver各种注释语句的写法
  3. 关于sql语句的多重循环
  4. 深度探索 -- 生成SQL语句的向导应如何做?
  5. oracle:使用cmd命令在远程oracle服务器上执行sql语句
  6. SQL语句字符串
  7. SQL语句的优化
  8. 如何使用SQL语句查到当前SQL SERVER 2000服务器的IP地址
  9. Oracle 常用sql语句

随机推荐

  1. Android五大布局
  2. Android(安卓)proguard混淆编译的问题
  3. Android入门篇一:Android Activity生命周
  4. SDK中模拟器创建命令行!!!
  5. Android自定义dialog以及如何去除黑色背
  6. Android反编译工具介绍
  7. 设置listview的背景颜色
  8. Android——SeekBar(拖动条)相关知识总结贴
  9. android 如何让 EditText 默认不获取焦点
  10. android语音识别之科大讯飞语音API的使用