一文看懂如何分析MySQL Explain（3/3）

程序员小强

发布于 2019-06-11 19:04:10

1.6K00

代码可运行

文章被收录于专栏：小强的进阶之路小强的进阶之路

运行总次数：0

代码可运行

接上文“一文看懂如何分析MySQL Explain（2/3）”

常用调优原则

1. 查询条件必须包含索引前面字段后续字段才会用到组合索引

注：t_classes表增加了一个组合索引，字段名`grade`, `classes_name`, `student_num`, `head_teacher_id`，索引名是idx_name_grade_student_num_teacher

例1：以下SQL使用了idx_name_grade_student_num_teacher索引，且使用了四个字段

EXPLAIN EXTENDED select id from t_classes where grade = 1 and classes_name = '高一1班' and student_num = 55 and head_teacher_id = 1;
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------------------+------+----------+--------------------------+
| id | select_type | table     | type | possible_keys                                                                | key                                | key_len | ref                     | rows | filtered | Extra                    |
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------------------+------+----------+--------------------------+
| 1  | SIMPLE      | t_classes | ref  | idx_grade,idx_name_grade,idx_classes_name,idx_name_grade_student_num_teacher | idx_name_grade_student_num_teacher | 779     | const,const,const,const | 17   | 100.00   | Using where; Using index |
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------------------+------+----------+--------------------------+

例2：以下SQL使用了idx_name_grade_student_num_teacher索引，但是仅使用了grade字段和classes_name字段，因为无student_num查询条件，所以head_teacher_id查询条件无法使用索引

EXPLAIN EXTENDED select id from t_classes where grade = 1 and classes_name = '高一1班' and head_teacher_id = 1;
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------+------+----------+--------------------------+
| id | select_type | table     | type | possible_keys                                                                | key                                | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------+------+----------+--------------------------+
| 1  | SIMPLE      | t_classes | ref  | idx_grade,idx_name_grade,idx_classes_name,idx_name_grade_student_num_teacher | idx_name_grade_student_num_teacher | 771     | const,const | 17   | 100.00   | Using where; Using index |
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------+------+----------+--------------------------+

例3：以下SQL使用了idx_name_grade_student_num_teacher索引，且使用了四个字段，因为MySQL查询引擎会根据索引字段顺序改写SQL，改写完之后的SQL和例1是一样的，查询条件顺序对是否使用索引无影响

EXPLAIN EXTENDED select id from t_classes where head_teacher_id = 1 and classes_name = '高一1班' and grade = 1 and student_num = 55 ;
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------------------+------+----------+--------------------------+
| id | select_type | table     | type | possible_keys                                                                | key                                | key_len | ref                     | rows | filtered | Extra                    |
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------------------+------+----------+--------------------------+
| 1  | SIMPLE      | t_classes | ref  | idx_grade,idx_name_grade,idx_classes_name,idx_name_grade_student_num_teacher | idx_name_grade_student_num_teacher | 779     | const,const,const,const | 17   | 100.00   | Using where; Using index |
+----+-------------+-----------+------+------------------------------------------------------------------------------+------------------------------------+---------+-------------------------+------+----------+--------------------------+

2. 不在索引列上做任何操作，如使用函数、索引列参加计算

例1：对比以下两个查询的执行计划，第一个使用了date_format函数，最终未使用idx_create_time索引，第二条SQL走了idx_create_time索引

EXPLAIN EXTENDED select * from t_classes where date_format(create_time,'%Y-%m-%d') = '2019-06-01';
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | t_classes | ALL  | NULL          | NULL | NULL    | NULL | 577  | 100.00   | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+

EXPLAIN EXTENDED select * from t_classes where create_time = '2019-06-01';
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table     | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | t_classes | ref  | idx_create_time | idx_create_time | 8       | const | 1    | 100.00   |       |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-------+

例2: 对比以下两个查询的执行计划，第一条SQLstudent_num查询条件未包含计算，最终走idx_student_num索引，第二条查询SQL包含计算导致全表扫描

EXPLAIN EXTENDED select * from t_classes where student_num = 20 * 2;
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
| 1  | SIMPLE      | t_classes | ref  | idx_student_num | idx_student_num | 4       | const | 1    | 100.00   | Using index condition |
+----+-------------+-----------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+

EXPLAIN EXTENDED select * from t_classes where student_num / 2 = 20;
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | t_classes | ALL  | NULL          | NULL | NULL    | NULL | 577  | 100.00   | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+----------+-------------+

3. 范围查询之后的条件无法使用索引，如：>=、<=、>、<

例：以下SQL使用了idx_name_grade_student_num_teacher索引，但是只使用了grade字段、classes_name字段和student_num字段三个字段，因为student_num字段的查询条件是大于（>），小于号(<)和大于号(>)之后的查询条件都是不会走索引的

EXPLAIN EXTENDED select id from t_classes where grade = 1 and classes_name = '高一1班' and student_num > 55 and head_teacher_id = 1;
+----+-------------+-----------+-------+------------------------------------------------------------------------------+------------------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | type  | possible_keys                                                                | key                                | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+-------+------------------------------------------------------------------------------+------------------------------------+---------+------+------+----------+--------------------------+
| 1  | SIMPLE      | t_classes | range | idx_grade,idx_name_grade,idx_classes_name,idx_name_grade_student_num_teacher | idx_name_grade_student_num_teacher | 775     | NULL | 1    | 100.00   | Using where; Using index |
+----+-------------+-----------+-------+------------------------------------------------------------------------------+------------------------------------+---------+------+------+----------+--------------------------+

4. 尽量使用覆盖索引（只查询索引中有的字段），减少回表操作

比如以下SQL，select * from t_classes where grade = 1 and classes_name = '高一1班' and student_num = 55 and head_teacher_id = 1;和select classes_name from t_classes where grade = 1 and classes_name = '高一1班' and student_num = 55 and head_teacher_id = 1;如果业务中只要求查询classes_name，第二条SQL会直接从索引中读取到对应的classes_name信息，不会再根据主键id从主键索引中再次读取所有的数据（普通索引的执行原理：主键索引是B+tree结构，树的非叶子节点存储主键，叶子节点存储记录行，普通索引非叶子节点存储索引列对应的值，叶子节点存储主键，查询时先根据查询条件从普通索引检索到要查询的数据的主键id，然后再根据主键id从主键索引树中查询记录行，称之为回表），第一条SQL因为要查询出所有表字段，需要回表查询，再走一次主键查找，同时回表会涉及到随机IO，查询成本比较高

5. 不等于操作(<>、!=)会导致全表扫描

EXPLAIN EXTENDED select * from t_classes where student_num != 1;
+----+-------------+-----------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+-----------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | t_classes | ALL  | idx_student_num | NULL | NULL    | NULL | 577  | 88.56    | Using where |
+----+-------------+-----------+------+-----------------+------+---------+------+------+----------+-------------+

6. is null和is not null 无法使用索引

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

EXPLAIN EXTENDED select * from t_classes where student_num is not null;
+----+-------------+-----------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------+-----------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | t_classes | ALL  | idx_student_num | NULL | NULL    | NULL | 577  | 100.00   | Using where |
+----+-------------+-----------+------+-----------------+------+---------+------+------+----------+-------------+

7. like操作以通配符(%、_、[charlist]、[^charlist]、[!charlist])开头会导致全表扫描

EXPLAIN EXTENDED select * from t_student where student_name like '李四%';
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+-----------------------+
| 1  | SIMPLE      | t_student | range | idx_student_name | idx_student_name | 767     | NULL | 1    | 100.00   | Using index condition |
+----+-------------+-----------+-------+------------------+------------------+---------+------+------+----------+-----------------------+

EXPLAIN EXTENDED select * from t_student where student_name like '%李四%';
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------+-------------+
| 1  | SIMPLE      | t_student | ALL  | NULL          | NULL | NULL    | NULL | 100198 | 100.00   | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------+-------------+

注：%condition%这种的可以使用覆盖索引（只查询索引列），覆盖索引长度超过380失效

EXPLAIN EXTENDED select student_name from t_student where student_name like '%李四%';
+----+-------------+-----------+-------+---------------+------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table     | type  | possible_keys | key              | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-----------+-------+---------------+------------------+---------+------+--------+----------+--------------------------+
| 1  | SIMPLE      | t_student | index | NULL          | idx_student_name | 767     | NULL | 100198 | 100.00   | Using where; Using index |
+----+-------------+-----------+-------+---------------+------------------+---------+------+--------+----------+--------------------------+

8. 字符串不加单引号索引失效

EXPLAIN EXTENDED select * from t_student where id_card = '0';
+----+-------------+-----------+-------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+-------+---------------+-------------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | t_student | const | idx_id_card   | idx_id_card | 767     | const | 1    | 100.00   |       |
+----+-------------+-----------+-------+---------------+-------------+---------+-------+------+----------+-------+

EXPLAIN EXTENDED select * from t_student where id_card = 0;
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------+-------------+
| 1  | SIMPLE      | t_student | ALL  | idx_id_card   | NULL | NULL    | NULL | 99794 | 100.00   | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------+-------------+

9. 少用or，可能会导致索引失效，可以转成union all

EXPLAIN EXTENDED select * from t_student where id_card < '10000' or id_card > '15000';
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------+-------------+
| 1  | SIMPLE      | t_student | ALL  | idx_id_card   | NULL | NULL    | NULL | 99794 | 50.01    | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+-------+----------+-------------+

EXPLAIN EXTENDED select * from t_student where id_card < '10000' union all select * from t_student where id_card > '15000';
+------+--------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------+
| id   | select_type  | table      | type  | possible_keys | key         | key_len | ref  | rows  | filtered | Extra                 |
+------+--------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------+
| 1    | PRIMARY      | t_student  | range | idx_id_card   | idx_id_card | 767     | NULL | 5     | 100.00   | Using index condition |
| 2    | UNION        | t_student  | ALL   | idx_id_card   | NULL        | NULL    | NULL | 99794 | 50.00    | Using where           |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL        | NULL    | NULL | NULL  | NULL     |                       |
+------+--------------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------+

10. 除非确实需要去掉重复的行，否则尽量使用union all而不是union。因为union会自带distinct操作，代价很大

11. 使用where限制记录行数，减少HAVING开销

HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

12. 性能方面，表连接 > (not) exists > (not) in

13. order by 索引

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。ORDER BY中不能既有ASC也有DESC

14 limit优化

如果能保证id是连续的则可以使用select * from t_user where id > xxx limit 11的方式，较为简单，这里不做过多赘述，以下是不能保证id连续的情况：

select * from t_user limit 5000000,2;
+---------+---------------+------------+-----+------------+
| id      | name          | birthday   | sex | address    |
+---------+---------------+------------+-----+------------+
| 5201244 | 钱七5201244    | 2019-07-09 | 1   | 广东4764   |
| 5201245 | 张三5201245    | 2019-08-11 | 1   | 天津5245   |
+---------+---------------+------------+-----+------------+
2 rows in set (3.23 sec)

以下是执行计划：

EXPLAIN EXTENDED select * from t_user limit 5000000,2;
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------+
| 1  | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL | 4969471 | 100.00   |       |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------+

使用子查询的方式可以提高查询效率

select * from t_user,(select id from t_user limit 5000000,2) u2 where u2.id = t_user.id;
+---------+---------------+------------+-----+------------+---------+
| id      | name          | birthday   | sex | address    | id      |
+---------+---------------+------------+-----+------------+---------+
| 5201244 | 钱七5201244    | 2019-07-09 | 1   | 广东4764   | 5201244 |
| 5201245 | 张三5201245    | 2019-08-11 | 1   | 天津5245   | 5201245 |
+---------+---------------+------------+-----+------------+---------+
2 rows in set (2.60 sec)

以下是执行计划：

EXPLAIN EXTENDED select * from t_user,(select id from t_user limit 5000000,2) u2 where u2.id = t_user.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+-------+---------+----------+-------------+
| 1  | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  | 4969471 | 100.00   |             |
| 1  | PRIMARY     | t_user     | eq_ref | PRIMARY       | PRIMARY | 4       | u2.id | 1       | 100.00   |             |
| 2  | DERIVED     | t_user     | index  | NULL          | PRIMARY | 4       | NULL  | 4969471 | 100.00   | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+---------+----------+-------------+

这里分析以下第二条SQL为什么会快：① 根据id分析，先执行第一条id为1的执行计划，但是第1条还款计划的table是derived2，所以执行id为2的还款计划，使用了主键索引，扫描了4969471行，但是因为只查询了主键，不会涉及大量随机IO，效率比较高，继续执行第一条id为1的执行计划，生成临时表derived2，把查询出的2条数据插入临时表，然后再和t_user表使用主键索引关联查询，最终查询出数据，需要回表查询的数据只有2条，select * from t_user limit 5000000,2;这种查询SQL会涉及到大量的回表操作，所以说虽然和带有子查询的方式都是全表扫描操作但是带有子查询的方式查询效率相对于select * from t_user limit 5000000,2;这种方式要高

End

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-06-06，如有侵权请联系 cloudcommunity@tencent.com 删除

sql