MySql执行计划

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 4.9k 阅读时长 ≈ 18 分钟

摘要

常见面试题

执行计划

慢SQL的完整排查流程（找SQL、看锁、看扫描行数、回归验证）见 MySql--慢查询，本文只讲拿到SQL之后如何读 EXPLAIN。

什么是执行计划

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划，我们需要搞懂EXPLAIN的各个输出项都是干嘛使的，从而可以有针对性的提升我们查询语句的性能。
通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析查询语句或是表结构的性能瓶颈。
通过EXPLAIN我们可以知道：
⚫ 表的读取顺序
⚫ 数据读取操作的操作类型
⚫ 哪些索引可以使用
⚫ 哪些索引被实际使用
⚫ 表之间的引用
⚫ 每张表有多少行被优化器查询

查看执行计划

执行计划的语法其实非常简单：在SQL查询的前面加上EXPLAIN关键字就行

1
2
3
4
5
6
mysql> explain select * from employees where id = 1
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | employees | <null>     | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.0    | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+

  • 以下示例用到两张表，employees与employees2表结构相同

1
2
3
4
5
6
7
8
9
10
11
12
show create table employees\G
***************************[ 1. row ]***************************
Table        | employees
Create Table | CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100004 DEFAULT CHARSET=utf8mb3 COMMENT='员工记录表'

重点属性说明

id

id列的编号是 select 的序列号，每个SELECT关键字都对应一个唯一的 id，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type

select_type 查询的类型，表示对应行是简单还是复杂的查询。

  • 1）SIMPLE：简单查询。查询不包含子查询和union
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 单表查询
explain select * from employees
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref    | rows  | filtered | Extra  |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| 1  | SIMPLE      | employees | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 92796 | 100.0    | <null> |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+

# inner join 连接查询
explain select * from employees inner join employees2 on employees.id = employees2.id
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra  |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| 1  | SIMPLE      | employees2 | <null>     | ALL    | PRIMARY       | <null>  | <null>  | <null>             | 10   | 100.0    | <null> |
| 1  | SIMPLE      | employees  | <null>     | eq_ref | PRIMARY       | PRIMARY | 4       | test.employees2.id | 1    | 100.0    | <null> |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+

# 效果同inner join
explain select * from employees,employees2 where employees.id = employees2.id
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra  |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| 1  | SIMPLE      | employees2 | <null>     | ALL    | PRIMARY       | <null>  | <null>  | <null>             | 10   | 100.0    | <null> |
| 1  | SIMPLE      | employees  | <null>     | eq_ref | PRIMARY       | PRIMARY | 4       | test.employees2.id | 1    | 100.0    | <null> |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
  • 2）PRIMARY：复杂查询中最外层的 select
  • 3）UNION：在 union 中的第二个和随后的 select查询,不依赖于外部查询的结果集
  • 4）UNION RESULT：UNION 结果集
1
2
3
4
5
6
7
8
explain select * from employees union select * from employees2
+--------+--------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+-----------------+
| id     | select_type  | table      | partitions | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra           |
+--------+--------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+-----------------+
| 1      | PRIMARY      | employees  | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 92796  | 100.0    | <null>          |
| 2      | UNION        | employees2 | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 10     | 100.0    | <null>          |
| <null> | UNION RESULT | <union1,2> | <null>     | ALL  | <null>        | <null> | <null>  | <null> | <null> | <null>   | Using temporary |
+--------+--------------+------------+------------+------+---------------+--------+---------+--------+--------+----------+-----------------+
  • 5）SUBQUERY：包含在 select 中的子查询（不在 from 子句中），不依赖于外部查询的结果集
1
2
3
4
5
6
7
explain select * from employees where id in (select id from employees2) or name = 'a'
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref    | rows  | filtered | Extra       |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+-------------+
| 1  | PRIMARY     | employees  | <null>     | ALL   | idx_name_age_position | <null>                | <null>  | <null> | 92796 | 100.0    | Using where |
| 2  | SUBQUERY    | employees2 | <null>     | index | PRIMARY               | idx_name_age_position | 140     | <null> | 10    | 100.0    | Using index |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+-------------+
  • 6）DEPENDENT UNION：UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
  • 7）DEPENDENT SUBQUERY：子查询中的第一个 select 查询,依赖于外部查询的结果集
1
2
3
4
5
6
7
8
9
 explain select * from employees where id in (select id from employees2 where name = 'a' union select id from employees where age =20)
+--------+--------------------+------------+------------+--------+-------------------------------+---------+---------+--------+--------+----------+-----------------+
| id     | select_type        | table      | partitions | type   | possible_keys                 | key     | key_len | ref    | rows   | filtered | Extra           |
+--------+--------------------+------------+------------+--------+-------------------------------+---------+---------+--------+--------+----------+-----------------+
| 1      | PRIMARY            | employees  | <null>     | ALL    | <null>                        | <null>  | <null>  | <null> | 92796  | 100.0    | Using where     |
| 2      | DEPENDENT SUBQUERY | employees2 | <null>     | eq_ref | PRIMARY,idx_name_age_position | PRIMARY | 4       | func   | 1      |  10.0    | Using where     |
| 3      | DEPENDENT UNION    | employees  | <null>     | eq_ref | PRIMARY,idx_name_age_position | PRIMARY | 4       | func   | 1      |  10.0    | Using where     |
| <null> | UNION RESULT       | <union2,3> | <null>     | ALL    | <null>                        | <null>  | <null>  | <null> | <null> | <null>   | Using temporary |
+--------+--------------------+------------+------------+--------+-------------------------------+---------+---------+--------+--------+----------+-----------------+
  • 8）DERIVED：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
1
2
3
4
5
6
7
explain select * from (select age,count(*) countNum from employees group by age) tmp where countNum > 3
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+------------------------------+
| id | select_type | table      | partitions | type  | possible_keys         | key                   | key_len | ref    | rows  | filtered | Extra                        |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+------------------------------+
| 1  | PRIMARY     | <derived2> | <null>     | ALL   | <null>                | <null>                | <null>  | <null> | 92796 | 100.0    | <null>                       |
| 2  | DERIVED     | employees  | <null>     | index | idx_name_age_position | idx_name_age_position | 140     | <null> | 92796 | 100.0    | Using index; Using temporary |
+----+-------------+------------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+------------------------------+
  • 9）MATERIALIZED：物化子查询，子查询物化就是将子查询的结果缓存在内存或临时表中，<subquery2>表示就是物化后的表
1
2
3
4
5
6
7
8
explain select * from employees where name not in (select name from employees2)
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+---------------------+-------+----------+-------------------------+
| id | select_type  | table       | partitions | type   | possible_keys         | key                   | key_len | ref                 | rows  | filtered | Extra                   |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+---------------------+-------+----------+-------------------------+
| 1  | SIMPLE       | employees   | <null>     | ALL    | <null>                | <null>                | <null>  | <null>              | 92796 | 100.0    | <null>                  |
| 1  | SIMPLE       | <subquery2> | <null>     | eq_ref | <auto_distinct_key>   | <auto_distinct_key>   | 75      | test.employees.name | 1     | 100.0    | Using where; Not exists |
| 2  | MATERIALIZED | employees2  | <null>     | index  | idx_name_age_position | idx_name_age_position | 140     | <null>              | 10    | 100.0    | Using index             |
+----+--------------+-------------+------------+--------+-----------------------+-----------------------+---------+---------------------+-------+----------+-------------------------+
  • 10）UNCACHEABLE SUBQUERY： 结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估，出现极少。
  • 11）UNCACHEABLE UNION：UNION 中的第二个或随后的 select 查询,属于不可缓存的子查询，出现极少。
1
2
3
4
5
6
7
explain select * from employees where id = (select id from employees2 where id= @@sql_log_bin)
+----+----------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type          | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+----------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1  | PRIMARY              | employees  | <null>     | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.0    | <null>      |
| 2  | UNCACHEABLE SUBQUERY | employees2 | <null>     | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.0    | Using index |
+----+----------------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

table

table列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查 询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type

type表示关联类型或访问类型，从最优到最差分别为：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
出现比较多的是: system > const > eq_ref > ref > range > index > ALL，一般来说，得保证查询达到range级别，最好达到ref。
NULL表示mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

类型说明

  • const, system: 常量查询，只查询一条记录
1
2
3
4
5
6
explain select * from employees where id = 1
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | employees | <null>     | const | PRIMARY       | PRIMARY | 4       | const | 1    | 100.0    | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
  • eq_ref: 主键索引或唯一索引关联查询
1
2
3
4
5
6
7
explain select * from employees inner join employees2 on employees.id = employees2.id
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra  |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
| 1  | SIMPLE      | employees2 | <null>     | ALL    | PRIMARY       | <null>  | <null>  | <null>             | 10   | 100.0    | <null> |
| 1  | SIMPLE      | employees  | <null>     | eq_ref | PRIMARY       | PRIMARY | 4       | test.employees2.id | 1    | 100.0    | <null> |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------+
  • ref: 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀
1
2
3
4
5
6
7
explain select * from employees inner join employees2 on employees.name = employees2.name
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+----------------------+------+----------+--------+
| id | select_type | table      | partitions | type | possible_keys         | key                   | key_len | ref                  | rows | filtered | Extra  |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+----------------------+------+----------+--------+
| 1  | SIMPLE      | employees2 | <null>     | ALL  | idx_name_age_position | <null>                | <null>  | <null>               | 10   | 100.0    | <null> |
| 1  | SIMPLE      | employees  | <null>     | ref  | idx_name_age_position | idx_name_age_position | 74      | test.employees2.name | 1    | 100.0    | <null> |
+----+-------------+------------+------------+------+-----------------------+-----------------------+---------+----------------------+------+----------+--------+
  • range: 范围扫描，如 in(), between ,> ,<, >=
1
2
3
4
5
6
explain select * from employees where id > 1
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref    | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | range | PRIMARY       | PRIMARY | 4       | <null> | 46398 | 100.0    | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
  • index: 扫描全索引，一般扫描的是某个二级索引，二级索引一般比较小，所以这种通常比ALL快一些。
1
2
3
4
5
6
explain select name,position from employees where age = 20
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref    | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| 1  | SIMPLE      | employees | <null>     | index | idx_name_age_position | idx_name_age_position | 140     | <null> | 92796 | 10.0     | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
  • ALL: 即全表扫描，扫描你的聚簇索引的所有叶子节点。
1
2
3
4
5
6
explain select * from employees
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref    | rows  | filtered | Extra  |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+
| 1  | SIMPLE      | employees | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 92796 | 100.0    | <null> |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+-------+----------+--------+

其它不常出现的也做简单说明

  • fulltext: 全文索引，不推荐使用

  • ref_or_null: 不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，如WHERE order_no= 'abc' OR order_no IS NULL;

  • index_merge: 一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询

  • unique_subquery: 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配

  • index_subquery: 与unique_subquery类似，只不过访问⼦查询中的表时使⽤的是普通的索引

possible_keys

possible_keys列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有值，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。
某些情况下，比如类型是index时，可能会出现 possible_keys 列是空的，而 key 列展示的是实际使用到的索引，这是因为index的意思就是扫描二级索引。
possible keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key

key列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
key_len计算规则如下：

  1. 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字 或字母占1个字节，一个汉字占3个字节
    char(n)：如果存汉字长度就是 3n 字节
    varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为 varchar是变长字符串
  2. 数值类型
    tinyint：1字节
    smallint：2字节
    int：4字节
    bigint：8字节
  3. 时间类型
    date：3字节
    timestamp：4字节
    datetime：8字节
    如果字段允许为 NULL，需要1字节记录是否为 NULL。
    索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref

ref列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows

rows列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数。
如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

filtered

查询优化器预测有多少条记录满⾜其余的搜索条件,即基于全表扫描或索引扫描计算出要扫描的rows后，满足其余的查询条件的记录数在这些rows中所占的百分比，这也是一个预估值。比如下面这个查询计划：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 基于主键索引扫描的rows是46398，此时filtered=100，因为没有其它查询条件，此时表示过滤的记录占比总的扫描rows是100%
explain select * from employees where id > 10
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref    | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | range | PRIMARY       | PRIMARY | 4       | <null> | 46398 | 100.0    | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+

# 如果在加一个条件 age = 20，此时表示该过滤条件占比 rows 的百分比是 10%
explain select * from employees where id > 10 and age = 20
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref    | rows  | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | range | PRIMARY       | PRIMARY | 4       | <null> | 46398 | 10.0     | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+-------+----------+-------------+

Extra

Extra列展示的是额外信息，常见信息如下：
1）Using index：使用覆盖索引，索引排序
2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖，出现Using where 只是表示 MySQL 使用 where 子句中的条件对记录进行了过滤，并不表示当前sql没有使用索引，也不表示一定使用全表扫描
3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
4）Using temporary：mysql需要创建一张临时表来处理查询，比如去重、排序、分组、Union之类的，需要使用索引优化
5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序，需要使用索引优化
6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

拿到一条慢 SQL 的 EXPLAIN，你怎么读？

场景：employees 有联合索引 idx_name_age_position(name, age, position)。

1
2
EXPLAIN SELECT * FROM employees
WHERE name = 'LiLei' AND age > 20 AND position = 'manager';

1. 优先看哪几列？

排查时先盯这四列（完整字段含义见上文各小节）：

  • type：访问方式好不好，差到 ALL/index 往往就是慢因。

  • key：实际用了哪个索引；为 NULL 基本等于没走索引定位。

  • rows：优化器估的扫描行数，不是结果集行数；越大越慢。可与慢日志里真实的 Rows_examined 对照（见 MySql–慢查询）。

  • Extra：有没有覆盖索引、临时表、文件排序等「额外代价」。

辅看：possible_keys（候选）、key_len（用了索引多长/几列）、filtered（在扫描行里预计还剩百分之几满足其余条件）。

2. type 常见档位（好 → 差）

文中完整链更长，面试常背这一截：
system > const > eq_ref > ref > range > index > ALL
一般至少到 range，最好到 ref。

type 人话
const / system 最多命中一行，如主键/唯一键等值
eq_ref JOIN 时被驱动表用主键或唯一键，一行对一行
ref 普通索引等值（或唯一索引前缀），可能多行
range 索引范围：>、<、BETWEEN、IN 等
index 扫完整个二级索引（比 ALL 轻，仍可能很重）
ALL 全表扫聚簇索引叶子，大表上的红灯

本题有 age > 20，正常应是 range（或更好），不该是 ALL。

3. 联合索引里哪些列真正用于定位？key_len 看什么？

条件：name = ?（等值）→ age > ?（范围）→ position = ?（等值）。

  • 最左前缀：name 能用。

  • age 是范围，也能用于索引范围扫描。

  • age 截断后续列：position 不能再用于索引定位（可能靠 ICP 在索引里过滤，见 Using index condition），详见 MySql索引。

  • key_len：实际用到的索引字节数。本题 utf8mb3、name varchar(24) NOT NULL 约 24×3+2=74，再加 age int 的 4，若只用到 name+age，key_len 大约是 78；若看到明显更长，再对照是否把后续列也算进了使用长度。以 key_len 为准判断「用到第几列」，不要想当然以为三个条件都走了索引。

  • SELECT * 无法覆盖索引，即使 key 有值也往往要回表；只查索引列时 Extra 才容易出现 Using index。

4. possible_keys 有值但 key 为 NULL？Extra 红灯？

  • 候选有、实际不用：常见是表很小，优化器觉得全表扫更便宜；或统计信息不准（ANALYZE TABLE）；也可能条件写法导致用索引更亏。不是「索引坏了」。

  • Using index：覆盖索引，通常是好事。

  • Using filesort：排序没走索引，额外排序。

  • Using temporary：要建临时表（去重/分组/部分 Union 等）。

  • 优先处理：type=ALL 且 key=NULL，以及不该出现的 Using filesort / Using temporary（能靠索引消掉就消）。Using where 单独出现不等于没走索引，别误判。

面试可背：先看 type/key/rows/Extra；type 至少 range；联合索引等值在前、范围截断后面列，用 key_len 核对；possible_keys≠一定用上；filesort/temporary 优先优化。

filesort文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
MySQL通过比较系统变量 max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式;
如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# mysql8是4k，mysql5是1k
mysql> show global variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 4096  |
+--------------------------+-------+

# 这里设置的4M
mysql> show global variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 4194304 |
+------------------+---------+

优化总结

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
2、order by满足两种情况会使用Using index:

  1. order by语句使用索引最左前列。
  2. 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

查看优化后的sql

mysql8.0以前的版本需要explain extended select * from actor where id = 1;

1
2
3
4
5
6
7
8
9
mysql> explain select * from actor where id = 1;
# 显示优化后的sql
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                               |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'a' AS `name`,'2017-12-22 15:27:18' AS `update_time` from `test`.`actor` where true |
+-------+------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

关联查询inner join之显式连接 VS 隐式连接，从执行计划上看两者是一回事，推荐带on的显示查询

1
2
3
4
5
6
7
# 显式连接1
select * from film_actor inner join film on film_actor.film_id = film.id;
# 显式连接2，有on会先执行on的关联，之后在进行where过滤，推荐关联关系放到on里面
select * from film_actor inner join film where film_actor.film_id = film.id;

# 隐式链接
select * from film_actor,film where film_actor.film_id = film.id;