十二、执行计划分析

MySQL在执行语句时会使用多种算法方案，最终会从中选择效率最高的方案执行我们的sql语句。

获取执行方案

#将sql语句放在desc后面，可以得到该sql语句的执行计划，并不是执行sql语句，对系统性能无影响
mysql> desc select * from vote_record where user_id='tGR7RL4T5AyPl358fElA';
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | vote_record | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996424 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

#等同于
mysql> explain select * from vote_record where user_id='tGR7RL4T5AyPl358fElA';

说明:
type
表示查询的类型，分为如下两种扫描
全表扫描 : ALL(效率最差)
索引扫描 : index,range,ref,eq_ref,const(system),NULL(性能依次从低到高)

ALL
全表扫描
所查询的列未创建索引，需要遍历全表的数据。

mysql> desc select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#sage列未建索引时作为条件查询是全表扫描
mysql> desc select * from student where sage=18;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

#对于辅助索引、唯一键索引来说，!=和not in 等语句，type为ALL，全表扫描
mysql> SELECT COUNT(sname) FROM student WHERE sname NOT IN ('zhang3', 'li4');
mysql> desc select * from student where sname like '%z%';
mysql> SELECT sname FROM student WHERE sname != 'zhang3';

index
全索引扫描
满足条件：

1. 需要查询的列已创建索引；
2. 需要遍历整棵索引树来实现；

例如

#查询student表中的学号，需要遍历整个索引树，所以type是index全索引扫描
mysql> desc select sno from student;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

range
索引范围扫描
包括>,<,>=,<=,between and,or,in,like都是索引范围扫描
注意：对于主键索引来说，!=和not in 等语句是走索引，type为range。

mysql> desc select sno from student where sno > 5;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | student | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    5 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

注意：在同等查询数据量下，or跟in的效率要低于其他范围条件查询，因为mysql使用的b*树，叶子节点有双向指针Q，能优化范围查找。

#in跟or虽然执行方式是range但是要比其他范围条件查询效率低
mysql> desc select * from student where sname in ('zhang3','li4');
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 62      | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

#使用union all代替or跟in，执行方式会变为ref
mysql> desc  select * from student where sname = 'zhang3' union all select * from student where sname = 'zh4';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | student | NULL       | ref  | idx_name      | idx_name | 62      | const |    1 |   100.00 | NULL  |
|  2 | UNION       | student | NULL       | ref  | idx_name      | idx_name | 62      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

使用Like进行查询时有如下两种情况

#%z%进行的是全表扫描
mysql> desc select * from student where sname like '%z%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

#而w%是range
mysql> desc select * from student where sname like 'w%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 62      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

ref
辅助索引等值查询
因为是等值查询，所以比范围查询效率要高

#除了union on等值查询，还有如下形式
mysql> desc select * from student where sname='ls4';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 62      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref
多表连接查询时，子表使用主键列或唯一索引列作为连接条件进行查询。

#teacher表的tno为主键列作为子表进行查询，所以type为eq_ref
#驱动表为ALL，全表查询，所以驱动表数据量越少越好，或者为驱动表列创建索引进行连接查询也可以提高效率
mysql> desc SELECT teacher.tname ,GROUP_CONCAT(student.sname)
    -> FROM student 
    -> JOIN score
    -> ON student.sno=score.sno
    -> JOIN course 
    -> ON score.cno=course.cno
    -> JOIN teacher
    -> ON course.tno=teacher.tno
    -> WHERE teacher.tname='oldguo';
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------+------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type   | possible_keys   | key     | key_len | ref              | rows | filtered | Extra                                              |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | teacher | NULL       | ALL    | PRIMARY,uidx_tn | NULL    | NULL    | NULL             |    3 |    33.33 | Using where                                        |
|  1 | SIMPLE      | course  | NULL       | ALL    | PRIMARY         | NULL    | NULL    | NULL             |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | score   | NULL       | ALL    | NULL            | NULL    | NULL    | NULL             |   17 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | student | NULL       | eq_ref | PRIMARY         | PRIMARY | 4       | school.score.sno |    1 |   100.00 | NULL                                               |
+----+-------------+---------+------------+--------+-----------------+---------+---------+------------------+------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

const或system
主键或者唯一键的等值查询

mysql> desc select * from teacher where tno = 101;
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | teacher | NULL       | const | PRIMARY,uidx_tn | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

NULL
想要查询的数据在表里找不到，当数据不存在时查询速度最快。

#注意查询列是主键列或者唯一键列时才为NULL
mysql> desc select * from student where sno=123;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

#查询的列是辅助索引时，为ref
mysql> desc select * from student where sname='tz';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 62      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

#当查询的列未设置索引时，type为ALL
mysql> desc select * from teacher where tname='tz';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

学习来自：郭老师博客，老男孩深标DBA课程 第四章