在MySQL中,可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。
EXPLAIN命令虽然没有提供任何优化建议,但它能够提供重要的信息有助于调优决策。
EXPLAIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。
在要查询的SQL语句前加上explain,然后执行就可以了。如:
EXPLAIN SELECT
goods_name,
seckill_price
FROM
seckill_goods,
goods
WHERE
seckill_goods.id = goods.id
执行上面SQL语句之后。
explain
各属性含义:(笔者常关注的参数:type、key、rows)
查询的序列号。
id是一组数字,表示查询中执行select子句或操作表的顺序;如果id相同,则执行顺序从上至下,如果是子查询,id的序号会递增,id越大则优先级越高,越先会被执行。
id列为null则表示这一行是一个结果集,不需要使用它来进行查询。
显示每个select子句的查询类型。
输出的行所引用的表。
版本5.7以前,该项是explain partitions显示的选项,5.7以后成为了默认选项。该列显示的是分区表命中的分区情况。非分区表该字段为空(null)。
对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。
性能依次由好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,all。
除了all之外,其他的type都可以使用到索引。除了index_merge之外,其他的type只可以用到一个索引。
显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句是否引用某些列或适合索引的列来提高查询性能
显示MySQL实际决定使用的键(索引),必然包含在possible_keys中,如果没有索引被选择,是NULL。
type为index_merge时,这里可能出现两个以上的索引,其他的type这里只会出现一个。
使用到索引字段的长度。
如果是单列索引,那就返回整个索引长度;如果是多列索引,那么查询不一定都能使用到所有的列,返回具体使用索引的长度(没有使用到的列,这里不会计算进去)。对比这个数值和多列索引的总长度,就可以推测是否使用到所有的列。
mysql的ICP特性使用到的索引不会计入其中。 key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。 key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
显示索引的那一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。
MySQL根据表统计信息及索引选用情况,估算mysql查询过程中遍历的行数,不是准确值。
使用explain extended时会出现这个列,5.7之后的版本默认就有这个字段,不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,这个值是百分比,不是具体记录数。
执行情况的说明和描述,显示信息种类非常多,下面只列举常见的结果。
优化示例章节,节选“美团技术团队”的“MySQL索引原理及慢查询优化”文章,点击查看(如果链接失效,请查看原文)
慢查询优化基本步骤:
不同的SQL语句书写方式对于效率往往有本质的差别,这要求我们对mysql的执行计划和索引原则有非常清楚的认识,请看下面的语句:
select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted=0
) cert
on (
cl.ref_table='Employee'
and cl.ref_oid= cert.emp_id
)
or (
cl.ref_table='EmpCertificate'
and cl.ref_oid= cert.cert_id
)
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00';
先运行一下,53条记录 1.87秒,又没有用聚合语句,比较慢
53 rows in set (1.87 sec)
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer |
| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where |
| 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
简述一下执行计划,首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录;然后查表扫描了63727条记录,分为两部分,derived表示构造表,也就是不存在的表,可以简单理解成是一个语句形成的结果集,后面的数字表示语句的ID。derived2表示的是ID = 2的查询构造了虚拟表,并且返回了63727条记录。我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据,首先全表扫描employee表13317条记录,然后根据索引emp_certificate_empid关联emp_certificate表,rows = 1表示,每个关联都只锁定了一条记录,效率比较高。获得后,再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据,返回的数据绝大部分cm_log都用不到,因为cm_log只锁定了379条记录。
如何优化呢?可以看到在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢?仔细分析语句不难发现,其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表,如果ref_table是Employee就关联employee表,我们完全可以拆成两部分,并用union连接起来,注意这里用union,而不用union all是因为原语句有“distinct”来得到唯一的记录,而union恰好具备了这种功能。如果原语句中没有distinct不需要去重,就可以直接使用union all了,因为使用union需要去重的动作,会影响SQL性能。
优化过的语句如下:
select
emp.id
from
cm_log cl
inner join
employee emp
on cl.ref_table = 'Employee'
and cl.ref_oid = emp.id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
union
select
emp.id
from
cm_log cl
inner join
emp_certificate ec
on cl.ref_table = 'EmpCertificate'
and cl.ref_oid = ec.id
inner join
employee emp
on emp.id = ec.emp_id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
本次优化不需要了解业务场景,只需要改造的语句和改造之前的语句保持结果一致
现有索引可以满足,不需要建索引
用改造后的语句实验一下,只需要10ms,降低了近200倍!
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where |
| 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | |
| 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)
更多内容,欢迎关注微信公众号:全菜工程师小辉~