【MySQL运维】SQL性能分析之EXPLAIN执行计划

tanglu MySQL 2021-10-12 2432 0

一、MySQL执行计划的作用

MySQL执行计划可以用于计算出数据库自身优化器在处理SQL时的资源成本,对于SQL优化起到关键作用。在MySQL中使用执行计划的命令是explain(也可以写为desc、describe),通过该命令可以查看SQL在真正执行时所需要经历的一系列过程,通过执行计划的结果可以分析出一条SQL在进行查询时的查找顺序、是否有使用索引等有用信息。在使用执行计划对一条SQL进行分析时,其SQL并不会真正执行,所以不会担心占用系统资源


二、MySQL EXPLAIN执行计划详解

1、使用执行计划分析语句

mysql > EXPLAIN SELECT id,name FROM table;  #explain后面接上SQL即可


2、explain执行计划结果解析

explain1.png

· id列:id值相同的为同组SQL,会从上往下执行;如果存在子查询,id编号会递增,ID大的SQL会优先执行

· select_type列:查询类型,SIMPLE为不包含UNION或子查询的简单查询;PRIMARY为包含子查询的主查询语句;SUBQUERY为包含子查询语句中的子查询;UNION代表有UNION操作的表

· table列:SQL所涉及到的表,一条SQL可能会涉及多张表,可以看这个分析具体是查哪张表有问题

· type列:数据查找方式,不同的查找方式性能不同,通常需要对ALL或者INDEX类型做优化,至少达到range级别。按照执行效率从高到低为const(表最多只有一行匹配,代表该SQL使用主键或唯一索引进行查询,可以直接返回需要查询的某条记录) > eq_ref(多表查询时使用主键或唯一索引进行查询) > ref(基于普通索引等值查询,比如包含了=的语句) > range(基于索引进行范围查询,比如包含了>、<、like、in、between、and、or的语句) > index(对索引列进行全索引扫描,需要优化)> all(对整张表全表扫描,最糟糕的情况)

· possible_keys与key列:分别显示了可能会用到的索引和实际用到的索引。如果possible_keys非空而key为空,则要检查语句是否存在索引失效的清空,比如select *

· key_len列:计算索引长度,主要用于计算联合索引的索引命中情况,单列索引没有计算意义。比如定义了字段'user_name char(20) NOT NULL DEFAULT',由于char(20)且非空,utf8字符集占用3个字节,那么最终长度就是20*3=60。如果允许NULL,那么还要额外+1字节,变长额外+2字节。假设有字段为varchr(10)、允许NULL、字符集为UTF8,那么索引长度就是10 * 3 +1(NULL)+2(变长字段);有字段为varchr(10)且不允许NULL,那么长度就是10 * 3 +2(变长字段)

Create Table: CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '',
  `name1` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),  
  KEY idx_key_name_name1 (`name`,`name1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


explain select * from t1 where name='atlas' and name1='yayun';  # explain中key_len=121,计算方式为20 * 3 + (20 * 3 +1)=121


· ref:多表查询时表之间字段引用关系,显示索引哪一列被使用了

· rows:SQL查询预估需要扫描的行数,也基本是回表的次数。如果表的.ibd文件存在大量碎片空洞,需要使用optimize回收空间后这个预估值才会更准确

· filtered:rows列扫描行数与实际满足查询条件行数的比例,100%为最好情况。比如rows列显示扫描10000行,有5000行满足查询要求,那么显示为50。如果filtered数值很低,那么说明通过优化空间很大,因为实际需要的数据行数很少

· extra:额外信息,如果出现Using temporary表示需要使用临时表来存储查询结果,通常是GROUP BY上的列缺少索引导致,建议查询哪个字段就按哪个字段分组;如果出现Using filesort表示查询使用了order by、group by语句排序。对于单索引,如果排序和查找是同一个字段,则不会出现using filesort,建议where哪些字段就order by哪些字段;如果出现Using index表示查询使用了覆盖索引,该SQL无需回表,性能最好;如果出现Using join buffer (Block Nested Loop) 代表多表 JOIN 连接没有走索引;Using where代表需要进行回表查询,属于中性结果

# 创建一个复合索引
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;

# using filesort
explain select *from test02 where a1='' order by a3 ; 
explain select *from test02 where a2='' order by a3 ; 

# 无using filesort
explain select *from test02 where a1='' order by a2 ;

# using temporary
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ;

# 无using temporary
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;

# where后面是a2和a4,按照SQL执行顺序先查出了a2和a4然后再对其进行分组,所以不需要temporary
explain select * from test03 where a2=2 and a4=4 group by a2,a4;

# where后面是a2和a4,但是最后分组是a3字段,此时确实相关数据,因此需要临时表,出现using temporary
explain select * from test03 where a2=2 and a4=4 group by a3;


三、索引成本导致优化器用错索引问题

1、索引基数

在执行SQL前优化器会分析所有可能的执行计划,然后根据CBO(基于成本的优化器)方法选择一个它认为成本最低的方法去执行,如下语句可以查看表中索引的基数,其中cardinality列就是语句根据索引可能使用到的查询可能性,数值越大越接近表的行数,说明区分度越高

#查看表中索引的基数
show index from table_name;


2、索引基数会影响区分度

如果explain预估的rows值跟实际情况差距比较大,或者原本应该选择索引的语句最终放弃了索引,一般是因为索引统计不准确导致的。还有一种情况就是如果全表扫描的成本比二级索引成本低,就会存在有索引但是不使用的问题(为什么全表扫描比二级索引查询快? 因为二级索引需要回表,当回表的记录数非常大时,成本就会比直接扫描要慢,因此这取决于回表的记录数)

# 查看成本方法示例
EXPLAIN FORMAT=json SELECT o_custkey,SUM(o_totalprice) FROM orders GROUP BY o_custkey


3、优化索引

使用analyze table可以重新统计索引信息,并重新计算索引基数,适用于一些索引预估明显有问题的情况

analyze table test_table  #重新统计索引信息


4、强制索引

大部分情况可以通过analyze命令解决,或者强制指定索引进行查询。甚至还可以通过删掉不必要的索引来规避优化器选错索引的问题。

select * from tast_table force index(a) where a between 1 and 1000 and b between 50000 and 100000 order by b limit 1;
版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!

评论