本文详细介绍了如何使用EXPLAIN命令来查看MySQL查询的执行计划，理解索引的使用情况。通过示例展示了id、select_type、type等关键信息，帮助优化SQL性能，包括全表扫描、索引扫描、回表等概念，并提供了索引优化的思路和不适合走索引的场景。
用expain查看SQL的执行计划
在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN去完成。
EXPLAIN命令是查看优化器如何决定执行查询的主要方法。可以帮助我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
需要注意的是，生成的QEP并不确定，它可能会根据很多因素发生改变。MySQL不会将一个QEP和某个给定查询绑定，QEP将由SQL语句每次执行时的实际情况确定，即便使用存储过程也是如此。尽管在存储过程中SQL语句都是预先解析过的，但QEP仍然会在每次调用存储过程的时候才被确定。
1.1 查看 select 语句的执行过程
mysql> explain select id,name from test where name=‘xjbw’;
±—±------------±------±-----±--------------±---------±--------±------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±--------------±---------±--------±------±-----±------------+
| 1 | SIMPLE | test | ref | name_idx | name_idx | 24 | const | 1 | Using where |
±—±------------±------±-----±--------------±---------±--------±------±-----±------------+
1 row in set (0.00 sec)
explain select SQL_NO_CACHE * from test where name=‘xjbw’ '\ \G G
SQL_NO_CACHE 的作用是禁止缓存查询结果。
如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
下面是这个表的初始化语句。
mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
explain select * from T where k between 3 and 5;
±-----±------------±------±------±--------------±-----±--------±-----±-----±----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±-----±------------±------±------±--------------±-----±--------±-----±-----±----------------------+
| 1 | SIMPLE | T | range | k | k | 4 | NULL | 2 | Using index condition |
现在，我们一起来看看这条 SQL 查询语句的执行流程：
1.在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2.再到 ID 索引树查到 ID=300 对应的 R3；
3.在 k 索引树取下一个值 k=5，取得 ID=500；
4.再回到 ID 索引树查到 ID=500 对应的 R4；
5.在 k 索引树取下一个值 k=6，不满足条件，循环结束。
在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？
如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
- 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
1.2执行计划包含的信息**
±—±------------±------±------±--------------±--------±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±--------±--------±-----±-----±------------+
1.2.1 id
包含一组数字，表示查询中执行select子句或操作表的顺序
【示例一】id相同，执行顺序由上至下
mysql> explain select t2.* from t1, t2, t3 where t1.id=t2.id and t1.id=t3.id and t1.name=’’;
±—±------------±------±-------±--------------±--------±--------±-----------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-------±--------------±--------±--------±-----------±-----±-------------------------+
| 1 | SIMPLE | t1 | ref | PRIMARY,name | name | 63 | const | 1 | Using where; Using index |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.id | 1 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.id | 1 | Using index |
±—±------------±------±-------±--------------±--------±--------±-----------±-----±-------------------------+
3 rows in set (0.00 sec)
【示例二】如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
mysql> explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name=’’));
±—±------------±------±-----±--------------±-----±--------±-----±-----±----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±--------------±-----±--------±-----±-----±----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
| 3 | SUBQUERY | t3 | ref | name | name | 63 | | 1 | Using where; Using index |
±—±------------±------±-----±--------------±-----±--------±-----±-----±----------------------------------------------------+
3 rows in set (0.00 sec)
【示例三】id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
mysql> explain select t2.* from (select t3.id from t3 where t3.name=’’)s1, t2 where s1.id=t2.id;
±—±------------±-----------±-------±--------------±--------±--------±------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-----------±-------±--------------±--------±--------±------±-----±-------------------------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | t3 | ref | name | name | 63 | | 1 | Using where; Using index |
±—±------------±
最低0.47元/天 解锁文章
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
