分析一条sql的性能的标准总结
这篇文章将给大家介绍如何使用explain来分析一条sql。
网上其实已经有非常多的文章都很详细的介绍了explain的使用,这篇文章将实例和原理结合起来,尽量让你有更好的理解,相信我,认真看完你应该会有特别的收获。
explain翻译过来就是解释的意思,在mysql里被称作执行计划,即可以通过该命令看出mysql在经过优化器分析后决定要如何执行该条sql。
说到优化器,再多说一句,mysql内置了一个强大的优化器,优化器的主要任务就是把你写的sql再给优化一下,尽可能以更低成本去执行,比如扫描更少的行数,避免排序等。执行一条sql语句都经历了什么?我在前面的文章中有介绍过优化器相关的。
你可能会问,一般在什么时候会要用explain呢,大多数情况下都是从mysql的慢查询日志中揪出来一些查询效率比较慢的sql来使用explain分析,也有的是就是在对mysql进行优化的时候,比如添加索引,通过explain来分析添加的索引能否被命中,还有的就是在业务开发的时候,在满足需求的情况下,你可能需要通过explain来选择一个更高效的sql。
那么explain该怎么用呢,很简单,直接在sql前面加上explain就行了,如下所示。
mysql>explainselect*fromt; +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ |1|SIMPLE|t|ALL|NULL|NULL|NULL|NULL|100332|NULL| +----+-------------+-------+------+---------------+------+---------+------+--------+-------+ 1rowinset(0.04sec)
可以看到,explain会返回约10个字段,不同版本返回的字段有些许差异,每个字段都代表着具体的意义,这篇文章我不打算把每个字段都详细的介绍一遍,东西比较多,怕你也不容易记住,不如先把几个重要的字段好好理解了。
其中type、key、rows、Extra这几个字段我认为是比较重要的,我们接下来通过具体的实例来帮你更好的理解这几个字段的含义。
首先有必要简单介绍下这几个字段的字面意思。
type表示mysql访问数据的方式,常见的有全表扫描(all)、遍历索引(index)、区间查询(range)、常量或等值查询(ref、eq_ref)、主键等值查询(const)、当表中只有一条记录时(system)。下面是效率从最好到最差的一个排序。
system>const>eq_ref>ref>range>index>all
key表示查询过程实际会用到的索引名称。
rows表示查询过程中可能需要扫描的行数,这个数据不一定准确,是mysql抽样统计的一个数据。
Extra表示一些额外的信息,通常会显示是否使用了索引,是否需要排序,是否会用到临时表等。
好了,接下来正式开始实例分析。
还是沿用前面文章中创建的存储引擎创建一个测试表,我们这里插入10w条测试数据,表结构如下:
CREATETABLE`t`( `id`int(11)NOTNULL, `a`int(11)DEFAULTNULL, `b`int(11)DEFAULTNULL, PRIMARYKEY(`id`) )ENGINE=InnoDB;
然后看下面这条查询语句,注意这个表目前只有一个主键索引,还没有创建普通索引。
mysql>altertabletaddindexa_index(a); QueryOK,0rowsaffected(0.19sec) Records:0Duplicates:0Warnings:0 mysql>altertabletaddindexb_index(b); QueryOK,0rowsaffected(0.20sec) Records:0Duplicates:0Warnings:0 mysql>showindexfromt; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment| +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |t|0|PRIMARY|1|id|A|100332|NULL|NULL||BTREE||| |t|1|a_index|1|a|A|100332|NULL|NULL|YES|BTREE||| |t|1|b_index|1|b|A|100332|NULL|NULL|YES|BTREE||| +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3rowsinset(0.00sec)
其中type值为ALL,表示全表扫描了,大家注意看到rows这个字段显示有100332条,实际上我们一共才10w条数据,所以这个字段只是mysql的一个预估,并不一定准确。这种全表扫描的效率非常低,是需要重点被优化的。
接下来我们分别给字段a和b添加普通索引,然后再看下添加索引后的几条sql。
mysql>altertabletaddindexa_index(a); QueryOK,0rowsaffected(0.19sec) Records:0Duplicates:0Warnings:0 mysql>altertabletaddindexb_index(b); QueryOK,0rowsaffected(0.20sec) Records:0Duplicates:0Warnings:0 mysql>showindexfromt; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment| +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |t|0|PRIMARY|1|id|A|100332|NULL|NULL||BTREE||| |t|1|a_index|1|a|A|100332|NULL|NULL|YES|BTREE||| |t|1|b_index|1|b|A|100332|NULL|NULL|YES|BTREE||| +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3rowsinset(0.00sec)
mysql>explainselect*fromtwherea>1000; +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |1|SIMPLE|t|ALL|a_index|NULL|NULL|NULL|100332|Usingwhere| +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1rowinset(0.00sec)
上面这条sql看起来是不是有点疑惑呢,type竟然显示刚刚不是给字段a添加索引了么,而且possible_keys也显示了有a_index可用,但是key显示null,表示mysql实际上并不会使用a索引,这是为啥?
这里是因为select*的话还需要回到主键索引上查找b字段,这个过程叫回表,这条语句会筛选出9w条满足条件的数据,也就是说这9w条数据都需要回表操作,全表扫描都才10w条数据,所以在mysql的优化器看来还不如直接全表扫描得了,至少还免去了回表过程了。
当然也不是说只要有回表操作就不会命中索引,用不用索引关键还在于mysql认为哪种查询代价更低,我们把上面的sql中where条件再稍微改造一下。
mysql>explainselect*fromtwherea>99000; +----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+ |1|SIMPLE|t|range|a_index|a_index|5|NULL|999|Usingindexcondition| +----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+ 1rowinset(0.00sec)
这回type值为range了,key为a_index,表示命中了a索引,是一个不错的选择,是因为满足这条sql条件的只有1000条数据,mysql认为1000条数据就算回表也要比全表扫描的代价低,所以说mysql其实是个很聪明的家伙。
我们还可以看到Extra字段中值为Usingindexcondition,这个意思是指用到了索引,但是需要回表,再看下面这个语句。
mysql>explainselectafromtwherea>99000; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ |1|SIMPLE|t|range|a_index|a_index|5|NULL|999|Usingwhere;Usingindex| +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1rowinset(0.00sec)
这个Extra中的值为Usingwhere;Usingindex,表示查询用到了索引,且要查询的字段在索引中就能拿到,不需要回表,显然这种效率比上面的要高,所以不要轻易写select*,只查询业务需要的字段即可,这样可以尽可能避免回表。
再来看一个需要排序的。
mysql>explainselectafromtwherea>99000orderbyb; +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+ |1|SIMPLE|t|range|a_index|a_index|5|NULL|999|Usingindexcondition;Usingfilesort| +----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+ 1rowinset(0.00sec)
这个Extra中返回了一个Usingfilesort,意味着需要排序,这种是需要重点优化的的,也就是说查到数据后,还需要mysql在内存中对其进行排序,你要知道索引本身就是有序的,所以一般来讲要尽量利用索引的有序性,比如像下面这样写。
mysql>explainselectafromtwherea>99990orderbya; +----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+ |1|SIMPLE|t|range|a_index,ab_index|a_index|5|NULL|10|Usingwhere;Usingindex| +----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+ 1rowinset(0.00sec)
我们再创建一个复合索引看看。
mysql>altertabletaddindexab_index(a,b); QueryOK,0rowsaffected(0.19sec) Records:0Duplicates:0Warnings:0
mysql>explainselect*fromtwherea>1000; +----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+ |1|SIMPLE|t|range|a_index,ab_index|ab_index|5|NULL|50166|Usingwhere;Usingindex| +----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+ 1rowinset(0.00sec)
这条sql刚刚在上面也有讲到过,在没有创建复合索引的时候,是走的全表扫描,现在其实是利用了覆盖索引,同样是免去了回表过程,即在(ab_index)索引上就能找出要查询的字段。
这篇文章通过几个实例介绍了如何使用explain分析一条sql的执行计划,也提到了一些常见的索引优化,事实上还有更多的可能性,你也可以自己去写一个sql,然后使用explain分析,看看有哪些是可以被优化的。