通过 EXPLAIN 浅析数据库查询优化方法
EXPLAIN 思维导图
思维导图
知识点
explain 或者 desc 命令
获取Myswl如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
- index：
这种类型表示是mysql会对整个该索引进行扫描。
要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。
缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。
- ref：
这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一 一的扫描判断，也就是平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。
索引字段的数据必须有序才能实现这种类型的查找，才能利用到索引。
- Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
- Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。
在一般稍大的系统中，基本尽可能的减少join ，子查询等等。mysql就使用最简单的查询，这样效率最高。至于 join 等，可以放在应用层去解决。
- Using temporary
优化。MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
- Using filesort
优化。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
执行计划中什么情况下会出现using filesort和using tempatory？
通常当查询中出现连接（JOIN）且连接中的所有列都来自连接的第一个表时，就会出现using filesort。
除此之外的所有情况，mysql都会先将连接结果放到临时表中，然后在所有的连接都结束后，再进行文件排序（实际上是快速排序），不管这种排序是在内存中还是在硬盘中进行的。
即使查询中出现limit 子句，也会在排序结束后才做分页处理，所以实际上临时表和需要排序的数据量依旧会非常大。
当执行计划中出现使用临时表的情况时，首先要检查一下是不是ORDER BY 和GROUP BY 语句中字段的问题。
如果调整过字段还是出现使用临时表的情况，考虑联合索引来覆盖这些字段，并尽可能的使用小结果集驱动大结果集。
explain 示例代码
mysql > explain select A.id , A.title , B.title
from base_content A
left join base_categories B on A.catid = B.id
left join base_sections C on A.sectionid = C.id
order by B.id ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 46585 | Using temporary ; Using filesort |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
3 rows in set ( 0.00 sec )
更改排序字段后：
mysql > explain select A.id , A.title , B.title
from base_content A
left join base_categories B on A.catid = B.id
left join base_sections C on A.sectionid = C.id
order by A.id ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 46585 | Using filesort |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
第一个使用了Using temporary，而第二个没有用呢？
因为如果有ORDER BY子句和一个不同的GROUP BY子句，或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。
那么，对于上面例子中的第一条语句，我们需要对base_categories的id进行排序，可以将SQL做如下改动：
mysql > explain select B.id , B.title , A.title
from base_categories A
left join base_content B on A.id = B.catid
left join base_sections C on B.sectionid = C.id
order by A.id ;
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL | NULL | 18 | Using filesort |
| 1 | SIMPLE | B | ref | idx_catid | idx_catid | 4 | joomla_test . A . id | 3328 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . B . sectionid | 1 | Using index |
+----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
3 rows in set ( 0.00 sec )
这样我们发现，不会再有Using temporary了，而且在查询base_content时，查询的记录明显有了数量级的降低，这是因为base_content的idx_catid起了作用。
结论：
为提高查询效率，应尽量对第一个表的索引键进行排序
扩展：
Using filesort 字面意思：使用文件进行排序或中文件中进行排序。
这是不正确的，当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。
我们再看：
mysql > explain select A.id , A.title , B.title
from base_content A , base_categories B , base_sections C
where A.catid = B.id and A.sectionid = C.id
order by C.id ;
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
| 1 | SIMPLE | C | index | PRIMARY | PRIMARY | 4 | NULL | 1 | Using index |
| 1 | SIMPLE | A | ref | idx_catid , idx_section | idx_section | 4 | joomla_test . C . id | 23293 | Using where |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | Using where |
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
3 rows in set ( 0.00 sec )
我们会发现Using filesort没有了,而这条语句中C表的主键对排序起了作用。
而尽管在上面的语句中也是对第一个表的主键进行排序，却没有得到想要的效果（第一个表的主键没有用到），这是为什么呢？
实际上以上运行过的所有left join的语句中，第一个表的索引都没有用到，尽管对第一个表的主键进行了排序也无济于事。不免有些奇怪！
测试：
于是我们继续测试了下一条SQL：
mysql > explain select A.id , A.title , B.title
from base_content A
left join base_categories B on A.catid = B.id
left join base_sections C on A.sectionid = C.id
where A.id < 100 ;
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 90 | Using where |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index |
+----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
3 rows in set ( 0.05 sec )
再次进行排序操作的时候，Using filesoft也没有再出现
mysql > explain select A.id , A.title, B.title
from base_content A
left join base_categories B on A.catid = B.id
left join base_sections C on A.sectionid = C.id
where A.id < 100
order by A.id ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
| 1 | SIMPLE | A | range | PRIMARY | PRIMARY | 4 | NULL | 105 | Using where |
| 1 | SIMPLE | B | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . catid | 1 | |
| 1 | SIMPLE | C | eq_ref | PRIMARY | PRIMARY | 4 | joomla_test . A . sectionid | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
3 rows in set ( 0.00 sec )
结论：
对where条件里涉及到的字段，Mysql会使用索引进行搜索，而这个索引的使用也对排序的效率有很好的提升
测试
分别让以下两个SQL语句执行200次：
select A.id , A.title , B.title
from base_content A
left join base_categories B on A.catid = B.id
left join base_sections C on A.sectionid = C.id
select A.id, A.title, B.title
from base_content A , base_categories B, base_sections C
where A.catid = B.id and A.sectionid = C.id
select A.id , A.title , B.title
from base_content A
left join base_categories B on A.catid = B.id
left join base_sections C on A.sectionid = C.id
order by rand ()
limit 10
select A.id from base_content A
left join base_categories B on B.id = A.catid
left join base_sections C on A.sectionid = C.id
order by A.id
结果是第(1)条平均用时27s ，第(2)条平均用时54s ，第(3)条平均用时80s ，第(4)条平均用时3s 。
用explain观察第(3)条语句的执行情况，会发现它创建了temporary临时表来进行排序。
知识点：
- 对需要查询和排序的字段要加索引。
- 尽量少地连接表。left join 比普通连接查询效率要高，注意观察索引是否起了作用。
- 排序尽量对第一个表的索引字段进行，可以避免mysql创建临时表，这是非常耗资源的。
- 对where条件里涉及到的字段，应适当地添加索引，这样会对排序操作有优化的作用。
- 如果说在分页时我们能先得到主键，再根据主键查询相关内容，也能得到查询的优化效果。
- 避免使用order by rand()。在执行过程中用show processlist查看，会发现第(3)条有Copying to tmp table on disk。
- Slow queries 检查一下是哪些语句降低的Mysql 的执行效率，并进行定期优化。
优化GROUP BY语句
如果查询包括GROUP BY 但想要避免排序结果的消耗，则可以指定ORDER By NULL禁止排序
例如：
explain select id, sum(moneys) from sales2 group by id \G
explain select id, sum(moneys) from sales2 group by id order by null \G
比较发现第一条语句会比第二句在Extra:里面多了Using filesort.而恰恰filesort是最耗时的。
优化ORDER BY语句
在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。WHERE 条件和 ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。
例如：
SELECT * FROM t1 ORDER BY key_part1,key_part2,....:
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC,key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下的情况不使用索引：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
--ORDER by的字段混合ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
--用于查询行的关键字与ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2;
--对不同的关键字使用ORDER BY
优化LIMIT分页
当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。
如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。
当偏移量非常大的时候，比如：LIMIT 20000 20这样的查询，MySQL需要查询120020条记录然后只返回20条记录，前面的20000条都将被抛弃，这样的代价非常高。
优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。
测试：
select film_id, description
from base_film
order by title limit 50,5;
如果这张表非常大，那么这个查询最好改成下面的样子：
select film.film_id, film.description
from base_film INNER JOIN(
select film_id from base_film order by title limit 50,5
) as tmp USING(film_id);
这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。
有时候如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如下面的查询：
select id from t limit 10000, 10;
select id from t where id > 10000 limit 10;
其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。
优化UNION
MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。
除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。
特定类型查询优化
优化COUNT()查询
COUNT()有两种不同的作用，其一是统计某个列值的数量，其二是统计行数。
统计列值时，要求列值是非空的，它不会统计NULL。如果确认括号中的表达式不可能为空时，实际上就是在统计行数。
最简单的就是当使用COUNT(*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计行数。
我们最常见的误解也就在这儿，在括号内指定了一列却希望统计结果是行数，而且还常常误以为前者的性能会更好。
但实际并非这样，如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。
有时候某些业务场景并不需要完全精确的COUNT值，可以用近似值来代替，EXPLAIN出来的行数就是一个不错的近似值，而且执行EXPLAIN并不需要真正地去执行查询，所以成本非常低。通常来说，执行COUNT()都需要扫描大量的行才能获取到精确的数据，因此很难优化，MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题，只有从架构层面解决了，比如添加汇总表，或者使用redis这样的外部缓存系统。
优化关联查询
在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：
确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。
要理解优化关联查询的第一个技巧，就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。
太抽象了？以上面的示例来说明，比如有这样的一个查询：
select A.xName, b.yName
from A INNER JOIN B USING(c)
WHERE A.xName IN (5,6)
假设MySQL按照查询中的关联顺序A、B来进行关联操作，那么可以用下面的伪代码表示MySQL如何完成这个查询：
outer_iterator = select A.xName, A.c
from A
where A.xName IN(5,6);
outer_row = outer_iterator.next;
while(outer_row){
inner_iterator = SELECT B.yName from B WHERE B.c = outer_row.c;
inner_row = inner_iterator.next;
while(outer_row) {
outpur[inner_row.yName, outer_row.xx];
inner_row = inner_iterator.next;
}
outer_row = outer_iterator.next;
}
可以看到，最外层的查询是根据A.xName，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。
1、MySQL不会使用索引的情况：非独立的列
“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：
select * from t where id + 1 = 15
其等价于 id = 14，但是MySQL无法自动解析这个表达式，使用函数是同样的道理。
2、前缀索引
如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。
3、多列索引和索引顺序
在多数情况下，在多个列上建立独立的索引并不能提高查询性能。
因为MySQL不知道选择哪个索引的查询效率更好，所以在老版本，比如MySQL5.0之前就会随便选择一个列的索引，而新的版本会采用合并索引的策略。
示例：
在一张电影演员表中，在actor_id和film_id两个列上都建立了独立的索引，然后有如下查询：
select film_id, actor_id
from film_actor
where actor_id = 1 or film_id = 1
老版本的MySQL会随机选择一个索引，但新版本做如下的优化：
select film_id, actor_id from film_actor where actor_id = 1
union all
select film_id, actor_id from film_actor where film_id and actor_id <> 1
当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。
当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。
结论：
如果发现有索引合并（Extra字段出现Using union），检查查询和表结构，检查索引（或许一个包含所有相关列的多列索引更适合）。
多列索引时索引的顺序对于查询是至关重要的，应该把选择性更高的字段放到索引的前面，这样通过第一个字段就可以过滤掉大多数不符合条件的数据。
索引选择性
索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
示例：
select * from base_payment where staff_id = 2 and customer_id = 785
是应该创建(staff_id,customer_id)的索引还是应该颠倒一下顺序？
selct count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*) from base_payment
哪个字段的选择性更接近1就把哪个字段索引前面，多数情况下使用这个原则没有任何问题，但仍然注意你的数据中是否存在一些特殊情况。
示例：
比如要查询某个用户组下有过交易的用户信息：
select user_id from base_trade
where user_group_id = 1 and trade_amount > 0
MySQL为这个查询选择了索引(user_group_id,trade_amount)，看起来没有任何问题。
但实际情况是这张表的大多数数据都是从老系统中迁移过来的，由于新老系统的数据不兼容，所以就给老系统迁移过来的数据赋予了一个默认的用户组。
这种情况下，通过索引扫描的行数跟全表扫描基本没什么区别，索引也就起不到任何作用。
经验法则可以指导我们开发和设计，但实际业务场景下的某些特殊情况可能会摧毁你的整个设计。
4、避免多个范围条件
实际开发中，我们会经常使用多个范围条件，比如想查询某个时间段内登录过的用户：
select USER.* from base_user USER
where login_time > '2019-01-01'
and age between 18 and 30
这个查询有一个问题：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们。
5、覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。
覆盖索引可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：
- 索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
- 索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多
6、使用索引扫描来排序
MySQL有两种方式可以生产有序的结果集
- 其一是对结果集进行排序的操作，
- 其二是按照索引顺序扫描得出的结果自然是有序的。
如果type列的值为index表示使用了索引扫描来做排序。扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。
但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。
这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。
知识点：
- 索引最好既能够满足排序，又满足查询。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。
- 如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。
- ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求，其他情况下都需要执行排序操作，而无法利用索引排序。
示例
有一种情况例外，就是最左的列被指定为常数
// 最左列为常数，索引：(date,staff_id,customer_id)
select staff_id,customer_id from base_staff where date = '2015-06-01' order by staff_id,customer_id
7、冗余和重复索引
立即删除。冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。
大多数情况下都应该尽量扩展已有的索引而不是创建新索引，冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。
但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询。
8、定期检查，可删除长期未使用的索引
只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时，索引才是有效的。所以 explain 后再提测是一种美德。
talk is easy, show me the code.
【一只阿木木】
网友评论