Explain函数

MySQL 的 EXPLAIN 语句用于分析 SQL 查询的执行计划，帮助开发者理解查询是如何执行的，以及优化查询性能。以下是 EXPLAIN 语句输出各个字段的详细深入解释：

字段意义

测试SQL:

 1-- 演员表
 2DROP TABLE IF EXISTS `actor`;
 3CREATE TABLE `actor` (
 4 `id` int(11) NOT NULL,
 5 `name` varchar(45) DEFAULT NULL,
 6 `update_time` datetime DEFAULT NULL,
 7 PRIMARY KEY (`id`)
 8) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 9INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
10
11-- 电影表
12DROP TABLE IF EXISTS `film`;
13CREATE TABLE `film` (
14 `id` int(11) NOT NULL AUTO_INCREMENT,
15 `name` varchar(10) DEFAULT NULL,
16 PRIMARY KEY (`id`),
17 KEY `idx_name` (`name`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8;
19INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
20
21-- 电影和演员关联表
22DROP TABLE IF EXISTS `film_actor`;
23CREATE TABLE `film_actor` (
24 `id` int(11) NOT NULL,
25 `film_id` int(11) NOT NULL,
26 `actor_id` int(11) NOT NULL,
27 `remark` varchar(255) DEFAULT NULL,
28 PRIMARY KEY (`id`),
29 KEY `idx_film_actor_id` (`film_id`,`actor_id`)
30) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

1. `id`

定义：查询中每个子查询的标识符。
作用：标识执行顺序和执行的层次。通常情况下，id 值越大，优先级越低。id 相同的表示同一级别的查询，可以并行执行。id为null的最后执行

2. `select_type`

定义：查询的类型，表示查询中各个部分的类别。
常见值：
- SIMPLE：简单查询，不包含子查询或 UNION。
- PRIMARY：最外层的查询。
- UNION：UNION 中的第二个或后续查询。
- DEPENDENT UNION：依赖于外部查询的 UNION 查询。
- UNION RESULT：UNION 的结果。
- SUBQUERY：子查询中的第一个 SELECT。
- DEPENDENT SUBQUERY：依赖于外部查询的子查询。
- DERIVED：派生表的查询，如 FROM 子句中的子查询。
- MATERIALIZED：物化子查询，通常在优化子查询时使用。

3. `table`

定义：查询涉及的表名或别名。
作用：指出当前正在访问或处理的表。

4. `partitions`

定义：分区信息。
作用：显示查询涉及的表的分区，如果表进行了分区。

5. `type`

定义：连接类型，表示 MySQL 如何查找行。

重要值：

null: myslq能够在优化阶段分解查询语句, 在之心该阶段用不着再访问表或者索引, 例如: 获取索引列中的最小值等

1mysql> explain select min(id) from film;
2
3+----+-------------+-------+------------+------+---------------+----+---------+------+-------+-------------------------+------------------------------+
4| id | select_type | table | partitions | type | possible_keys | key| key_len | ref  | rows  | filtered                | Extra                        |
5+----+-------------+-------+------------+------+---------------+----+---------+------+-------+-------------------------+------------------------------+
6| 1  | SIMPLE      |       |            |      |               |    |         |      |       |                         | Select tables optimized away |
7+----+-------------+-------+------------+------+---------------+----+---------+------+-------+-------------------------+------------------------------+

system：表只有一行（等同于 system 表）。
const：表最多有一行匹配，常用于主键或唯一索引。

eq_ref：对于每个来自前一个表的行，只有一行被读出，通常用于主键(如下面的film.id)或唯一索引的连接。

1mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
2
3+----+-------------+-------------+------------+-------+-------------------+--------------------------------------+---------+------------------------------------+------+----------+-------+
4| id | select_type | table       | partitions | type  | possible_keys     | key                                  | key_len | ref                                | rows | filtered | Extra |
5+----+-------------+-------------+------------+-------+-------------------+--------------------------------------+---------+------------------------------------+------+----------+-------+
6| 1  | SIMPLE      | film_actor  |            | ALL   |                   |                                      |         |                                    | 3    | 100.00   |       |
7| 1  | SIMPLE      | film        |            | eq_ref| PRIMARY           | PRIMARY                              | 4       | explain_test.film_actor.film_id    | 1    | 100.00   |       |
8+----+-------------+-------------+------------+-------+-------------------+--------------------------------------+---------+------------------------------------+------+----------+-------+

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

简单的select查询, name是普通索引

1mysql> explain select * from film where name = 'film1';
2
3+----+-------------+-------+------------+------+--------------+---------+---------+-----+------+----------+-----------------+
4| id | select_type | table | partitions | type | possible_keys| key     | key_len | ref | rows | filtered | Extra           |
5+----+-------------+-------+------------+------+--------------+---------+---------+-----+------+----------+-----------------+
6| 1  | SIMPLE      | film  |            | ref  | idx_name     | idx_name| 33      | const| 1    | 100.00   | Using index     |
7+----+-------------+-------+------------+------+--------------+---------+---------+-----+------+----------+-----------------+

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

1mysql> explain select film_id from film left join film_actor on film.id = film_actor.film_id;
2
3+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+------------------------+------+----------+------------------+
4| id | select_type | table       | partitions | type  | possible_keys     | key               | key_len | ref                    | rows | filtered | Extra            |
5+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+------------------------+------+----------+------------------+
6| 1  | SIMPLE      | film        |            | index | idx_name          | idx_name          | 33      |                        | 3    | 100.00   | Using index      |
7| 1  | SIMPLE      | film_actor  |            | ref   | idx_film_actor_id | idx_film_actor_id | 4       | explain_test.film.id   | 1    | 100.00   | Using index      |
8+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+------------------------+------+----------+------------------+

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

1mysql> explain select * from actor where id > 1;
2
3+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
4| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
5+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
6| 1  | SIMPLE      | actor |            | range | PRIMARY       | PRIMARY  | 4       |     | 2    | 100.00   | Using where          |
7+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

index：Full Index Scan，index与ALL区别为index类型只遍历索引树,扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

1mysql> explain select * from film;
2
3+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
4| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
5+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
6| 1  | SIMPLE      | film  |            | index | idx_name      | idx_name | 33      |     | 3    | 100.00   | Using index          |
7+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

ALL：全表扫描。

1mysql> explain select * from film;
2
3+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
4| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
5+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
6| 1  | SIMPLE      | film  |            | index | idx_name      | idx_name | 33      |     | 3    | 100.00   | Using index          |
7+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

一般来说, 得保证查询至少要达到range级别, 最好达到ref

6. `possible_keys`

定义：查询中可能使用的索引。
作用：显示查询优化器认为可能有效的索引。explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

7. `key`

定义：查询实际使用的索引。
作用：优化器选择的用于执行查询的索引。

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

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通

过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

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

8. `key_len`

定义：使用的索引的长度。
作用：显示查询使用的索引的字节长度，越短越好。

9. `ref`

定义：显示使用哪个列或常量与 key 一起用于查找行。
作用：表示索引列与之比较的值或列。

10. `rows`

定义：MySQL 估计要读取的行数。
作用：用于估计查询的成本，行数越少，查询性能越好。

11. `filtered`

定义：显示在存储引擎层过滤掉的行的百分比。
作用：估计剩余的行数比例。

12. `Extra`

定义：执行计划的附加信息。

常见值：

Using where：使用 WHERE 子句过滤行。

1explain select film_id from film_actor where film_id
2
3+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
4| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
5+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
6| 1  | SIMPLE      | actor |            | ALL  |               |    |         |     | 3    | 33.33    | Using where      |
7+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+

Using index：查询仅使用索引，不读取实际的行数据。使用覆盖索引

1mysql> explain select film_id from film_actor where film_id
2
3+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------+----------+------------------------+-------------------------+
4| id | select_type | table       | partitions | type  | possible_keys     | key               | key_len | ref   | rows     | filtered               | Extra                   |
5+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------+----------+------------------------+-------------------------+
6| 1  | SIMPLE      | film_actor  |            | index | idx_film_actor_id | idx_film_actor_id | 8       |       | 3        | 100.00                 | Using where; Using index|
7+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------+----------+------------------------+-------------------------+

Using temporary：使用临时表保存中间结果。

actor.name没有索引, 此时创建了张临时表

1mysql> explain select distinct name from actor;
2
3+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
4| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
5+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
6| 1  | SIMPLE      | actor |            | ALL  |               |    |         |     | 3    | 100.00   | Using temporary  |
7+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+

fim.name有个idx_name的索引, 此时使用的using index

1mysql> explain select distinct name from film;
2
3+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
4| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
5+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
6| 1  | SIMPLE      | film  |            | index | idx_name      | idx_name | 33      |     | 3    | 100.00   | Using index          |
7+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
Using join buffer：使用连接缓存。
Impossible WHERE：WHERE 子句永远为 false，返回空结果集。

Select tables optimized away：优化器消除了不必要的表查询。

1mysql> explain select min(id) from film;
2
3+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+-------------------------+
4| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra                   |
5+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+-------------------------+
6| 1  | SIMPLE      |       |            |      |               |    |         |     |      |          | Select tables optimized away  |
7+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+-------------------------+

最佳实践

1. 全值匹配

 1mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
 2+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+
 3| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra               |
 4+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+
 5| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 74      | const | 1    | 100.00   |                     |
 6+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+
 7
 8
 9EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
10+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
11| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra               |
12+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
13| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 78      | const,const | 1    | 100.00   |                     |
14+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+

2. 最左前缀法则

查询从索引的最左前列开始并且不跳过索引中的列

 1mysql> EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
 2+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
 3| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra               |
 4+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
 5| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 78      | const,const | 1    | 100.00   |                     |
 6+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
 7
 8
 9mysql> EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
10+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
11| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
12+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
13| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 50.00    | Using where      |
14+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
15
16
17mysql> EXPLAIN SELECT * FROM employees WHERE position = 'manager';
18+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
19| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
20+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
21| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 50.00    | Using where      |
22+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+

3. 不在索引列上做任何计算操作（计算、函数、自动或手动类型转换）

 1mysql> EXPLAIN SELECT * FROM employees where name = 'LiLei';
 2+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+
 3| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra               |
 4+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+
 5| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 74      | const | 1    | 100.00   |                     |
 6+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+
 7
 8
 9mysql> EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
10+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
11| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
12+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
13| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 50.00    | Using where      |
14+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
15
16
17-- 给hire_time增加一个普通索引： 
18ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
19EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
20+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
21| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
22+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
23| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 100.00   | Using where      |
24+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
25-- 还原最初状态
26ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

4. 不能使用索引中范围条件右边的列

 1EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
 2+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+---------------------+
 3| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref               | rows | filtered | Extra               |
 4+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+---------------------+
 5| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 140     | const,const,const | 1    | 100.00   |                     |
 6+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+---------------------+
 7
 8
 9EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
10+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+
11| id | select_type | table     | partitions | type  | possible_keys             | key                       | key_len | ref | rows  | filtered | Extra                |
12+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+
13| 1  | SIMPLE      | employees |            | range | idx_name_age_position     | idx_name_age_position     | 78      |     | 1     | 50.00    | Using index condition|
14+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+

上面例子中的第二条position没有走索引，因为在满足age>22的条件下，不能保证manager都是有序的

5. 尽量使用覆盖索引，只访问索引的查询（索引列包含查询列），减少select *

1EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';
2+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------------------+------+----------+---------------------+
3| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref              | rows | filtered | Extra               |
4+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------------------+------+----------+---------------------+
5| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 140     | const,const,const| 1    | 100.00   | Using index         |
6+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------------------+------+----------+---------------------+

6. 在使用不等于(≠，<>)，not in，not exists的时候无法使用索引

< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

 1-- 当employees表只有3条数据时
 2EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
 3+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+
 4| id | select_type | table     | partitions | type  | possible_keys             | key                       | key_len | ref | rows  | filtered | Extra                |
 5+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+
 6| 1  | SIMPLE      | employees |            | range | idx_name_age_position     | idx_name_age_position     | 74      |     | 2     | 100.00   | Using index condition|
 7+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+
 8-- 当employees表有20w条数据时
 9+----+-------------+-----------+------------+------+---------------------------+----+---------+-----+--------+----------+------------------+
10| id | select_type | table     | partitions | type | possible_keys             | key| key_len | ref | rows   | filtered | Extra            |
11+----+-------------+-----------+------------+------+---------------------------+----+---------+-----+--------+----------+------------------+
12| 1  | SIMPLE      | employees |            | ALL  | idx_name_age_position     |    |         |     | 199696 | 50.00    | Using where      |
13+----+-------------+-----------+------------+------+---------------------------+----+---------+-----+--------+----------+------------------+

7. `is null`，`is not null` 一般情况下也无法使用索引

1EXPLAIN SELECT * FROM employees WHERE name is null
2+----+-------------+-------+------------+------+----------------+-----+---------+-----+------+----------+------------------+
3| id | select_type | table | partitions | type | possible_keys  | key | key_len | ref | rows | filtered | Extra            |
4+----+-------------+-------+------------+------+----------------+-----+---------+-----+------+----------+------------------+
5| 1  | SIMPLE      |       |            |      |                |     |         |     |      |          | Impossible WHERE |
6+----+-------------+-------+------------+------+----------------+-----+---------+-----+------+----------+------------------+

8. like以通配符开头（`%abc…`）无法使用索引

 1EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
 2+----+-------------+-----------+------------+------+----------------+-----+---------+-------+--------+----------+----------------+
 3| id | select_type | table     | partitions | type | possible_keys  | key | key_len | ref   | rows   | filtered | Extra          |
 4+----+-------------+-----------+------------+------+----------------+-----+---------+-------+--------+----------+----------------+
 5| 1  | SIMPLE      | employees |            | ALL  |                |     |         |       | 199696 | 11.11    | Using where    |
 6+----+-------------+-----------+------------+------+----------------+-----+---------+-------+--------+----------+----------------+
 7
 8
 9-- 可以走索引
10EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'
11+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+------+----------+----------------------+
12| id | select_type | table     | partitions | type  | possible_keys             | key                       | key_len | ref | rows | filtered | Extra                |
13+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+------+----------+----------------------+
14| 1  | SIMPLE      | employees |            | range | idx_name_age_position     | idx_name_age_position     | 74      |     | 1    | 100.00   | Using index condition |
15+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+------+----------+----------------------+

使用覆盖索引，查询字段必须是建立覆盖索引字段 , 解决like '%字符串%'索引不被使用的问题

1EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
2+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+--------+----------+----------------------+
3| id | select_type | table     | partitions | type  | possible_keys             | key                       | key_len | ref | rows   | filtered | Extra                |
4+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+--------+----------+----------------------+
5| 1  | SIMPLE      | employees |            | index | idx_name_age_position     | idx_name_age_position     | 140     |     | 199696 | 11.11    | Using where; Using index |
6+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+--------+----------+----------------------+

9. 字符串不加单引号，导致索引失效

 1EXPLAIN SELECT * FROM employees WHERE name = '1000';
 2+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------+--------+----------+-------+
 3| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref  | rows   | filtered | Extra |
 4+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------+--------+----------+-------+
 5| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 74      | const| 1      | 100.00   |       |
 6+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------+--------+----------+-------+
 7
 8-- 走的全表查询
 9EXPLAIN SELECT * FROM employees WHERE name = 1000;
10+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-----+--------+----------+------------------+
11| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref | rows   | filtered | Extra            |
12+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-----+--------+----------+------------------+
13| 1  | SIMPLE      | employees |            | ALL  | idx_name_age_position     |                           |         |     | 199696 | 10.00    | Using where      |
14+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-----+--------+----------+------------------+

10. 少用or，in，他们不一定使用索引，

mysql内部优化器会更具检索比例，表的大小等多个因素来整体评估是否使用索引。

11. 范围查询

 1-- 走全表
 2explain select * from employees where age >=1 and age <=100000;
 3+----+-------------+-----------+------------+------+---------------------------+-----+---------+-----+--------+----------+------------------+
 4| id | select_type | table     | partitions | type | possible_keys             | key | key_len | ref | rows   | filtered | Extra            |
 5+----+-------------+-----------+------------+------+---------------------------+-----+---------+-----+--------+----------+------------------+
 6| 1  | SIMPLE      | employees |            | ALL  | idx_age                   |     |         |     | 199696 | 50.00    | Using where      |
 7+----+-------------+-----------+------------+------+---------------------------+-----+---------+-----+--------+----------+------------------+
 8
 9-- 走索引
10explain select * from employees where age >= 1 and age <= 10000;
11+----+-------------+-----------+------------+-------+----------------+---------+---------+-----+-------+----------+---------------------------------+
12| id | select_type | table     | partitions | type  | possible_keys  | key     | key_len | ref | rows  | filtered | Extra                           |
13+----+-------------+-----------+------------+-------+----------------+---------+---------+-----+-------+----------+---------------------------------+
14| 1  | SIMPLE      | employees |            | range | idx_age        | idx_age | 4       |     | 38352 | 100.00   | Using index condition           |
15+----+-------------+-----------+------------+-------+----------------+---------+---------+-----+-------+----------+---------------------------------+

没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。

比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引。

优化方法：可以将大的范围拆分成多个小范围。

 1CREATE TABLE `employees` (
 2  `id` int(11) NOT NULL AUTO_INCREMENT,
 3  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 4  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 5  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 6  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 7  PRIMARY KEY (`id`),
 8  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 9) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
10
11INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
12INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
13INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
14
15-- 插入一些示例数据
16DROP PROCEDURE
17IF EXISTS insert_emp;
18delimiter;;
19CREATE PROCEDURE insert_emp () BEGIN
20	DECLARE i INT;
21	SET i = 1;
22	WHILE( i <= 100000 ) DO
23		INSERT INTO employees ( NAME, age, position )VALUES( CONCAT( 'lisi', i ), i, 'dev' );
24		SET i = i + 1;	
25	END WHILE;
26END;;
27delimiter;
28CALL insert_emp ();
29
30
31
32-- mysql5.7关闭ONLY_FULL_GROUP_BY报错
33-- select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

测试例子

联合索引第一个字段是范围的不走索引

1EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
2
3
4----+-------------------+----------+------------+------+------------------------+------------------------+----------+-----+------+----------+---------------------+
5id  | select_type       | table    | partitions | type | possible_keys          | key                    | key_len  | ref | rows | filtered | Extra               |
6----+-------------------+----------+------------+------+------------------------+------------------------+----------+-----+------+----------+---------------------+
71   | SIMPLE            | employees|            | range| idx_name_age_position  | idx_name_age_position  | 74       |     | 1    | 33.33    | Using index condition|
8----+-------------------+----------+------------+------+------------------------+------------------------+----------+-----+------+----------+---------------------+

MySQL会认为结果集应该比较大, 回表效率不高, 不如直接走全表

强制走索引(针对上面的情况)

1EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
2
3
4
5+----+-------------+-----------+------------+-------+------------------------+------------------------+----------+-----+------+----------+---------------------+
6| id | select_type | table     | partitions | type  | possible_keys          | key                    | key_len  | ref | rows | filtered | Extra               |
7+----+-------------+-----------+------------+-------+------------------------+------------------------+----------+-----+------+----------+---------------------+
8| 1  | SIMPLE      | employees |            | range | idx_name_age_position  | idx_name_age_position  | 74       |     | 1    | 50.00    | Using index condition|
9+----+-------------+-----------+------------+-------+------------------------+------------------------+----------+-----+------+----------+---------------------+

虽然看上去走了索引了, 但是效率不一定高, 因为需要大量的回表。

Gang's Blog

Go语言入门指南

Explain函数

字段意义

1. `id`

2. `select_type`

3. `table`

4. `partitions`

5. `type`

6. `possible_keys`

7. `key`

8. `key_len`

9. `ref`

10. `rows`

11. `filtered`

12. `Extra`

最佳实践

最佳实践

1. 全值匹配

2. 最左前缀法则

3. 不在索引列上做任何计算操作（计算、函数、自动或手动类型转换）

4. 不能使用索引中范围条件右边的列

5. 尽量使用覆盖索引，只访问索引的查询（索引列包含查询列），减少select *

6. 在使用不等于(≠，<>)，not in，not exists的时候无法使用索引

7. `is null`，`is not null` 一般情况下也无法使用索引

8. like以通配符开头（`%abc…`）无法使用索引

9. 字符串不加单引号，导致索引失效

10. 少用or，in，他们不一定使用索引，

11. 范围查询

测试例子

Explain函数

字段意义

1. id

2. select_type

3. table

4. partitions

5. type

6. possible_keys

7. key

8. key_len

9. ref

10. rows

11. filtered

12. Extra

最佳实践

最佳实践

1. 全值匹配

2. 最左前缀法则

3. 不在索引列上做任何计算操作（计算、函数、自动或手动类型转换）

4. 不能使用索引中范围条件右边的列

5. 尽量使用覆盖索引，只访问索引的查询（索引列包含查询列），减少select *

6. 在使用不等于(≠，<>)，not in，not exists的时候无法使用索引

7. is null，is not null 一般情况下也无法使用索引

8. like以通配符开头（%abc…）无法使用索引

9. 字符串不加单引号，导致索引失效

10. 少用or，in，他们不一定使用索引，

11. 范围查询

测试例子

1. `id`

2. `select_type`

3. `table`

4. `partitions`

5. `type`

6. `possible_keys`

7. `key`

8. `key_len`

9. `ref`

10. `rows`

11. `filtered`

12. `Extra`

7. `is null`，`is not null` 一般情况下也无法使用索引

8. like以通配符开头（`%abc…`）无法使用索引