MySQL之explain

发表于 分类于 评论数： 阅读次数：
本文字数： 29k 阅读时长 ≈ 26 分钟

分享主题

mysql explain查询计划

分享目标

  1. 掌握最左前缀原则
  2. 掌握explain查询计划显示索引是否被使用到
  3. 掌握如何正确使用索引，才不会引起索引失效

查看执行计划

介绍

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句的执行计划进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。

EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:

参数说明

expain出来的信息有10列，分别是

1
2
3
4
5
6
7
8
9
10
id: SELECT查询的标识符，每个SELECT都会自动分配一个唯一的标识符
select_type: SELECT查询的类型
table: 查询的是哪张表
type: join类型
possible_keys:此次查询中可能选用的索引
key: 此次查询中确切使用到的索引
key_len: 此次查询中使用到索引的长度
ref: 哪个字段或常数与key一起被使用
rows: 显示此次查询一共扫描多少行。这是一个估计值
Extra: 额外信息

建表语句

新建user表, 添加组合索引idx_name_age和单列索引idx_sex，案例mysql版本是5.7版本；

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `user` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`),
  KEY `idx_sex` (`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1
2
INSERT INTO `user` (`id`, `name`, `age`, `sex`, `address`) VALUES ('1', 'zhangsan', '20', '0', '中信广场');
INSERT INTO `user` (`id`, `name`, `age`, `sex`, `address`) VALUES ('2', 'lisi', '20', '0', '中信广场');

select_type（重要）

查询类型，主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。

simple： 简单SELECT(不使用UNION或子查询)
1
2
3
4
5
6
7
mysql> explain select *from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
primary：最外面的SELECT
1
2
3
4
5
6
7
8
mysql> explain select(select name from user) from user;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | PRIMARY     | user  | index | NULL          | idx_sex      | 4       | NULL |    2 | Using index |
|  2 | SUBQUERY    | user  | index | NULL          | idx_name_age | 308     | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
2 rows in set (0.00 sec)
subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
1
2
3
4
5
6
7
8
mysql> explain select *from user where id=(select max(id) from user);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                        |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL                         |
|  2 | SUBQUERY    | NULL  | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+
2 rows in set (0.00 sec)
type（重要）
1
2
3
依次从好到差:
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，
index_merge，index，ALL

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引,最少要索引使用到range级别

system
1
表中只有一行数据或者是空表
1
2
3
4
5
6
7
8
mysql> explain select *from (select *from user where id=1) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | user       | const  | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.00 sec)
const（重要）
1
使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
1
2
3
4
5
6
7
mysql> explain select *from user where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
eq_ref（重要）
1
2
连接字段主键或者唯一性索引。
此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 '=', 查询效率较高.
1
2
3
4
5
6
7
8
mysql> explain select a.id from user a left join user b on a.id=b.id;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | a     | index  | NULL          | idx_sex | 4       | NULL              |    2 | Using index |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | fcp_kkb_demo.a.id |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------------+
2 rows in set (0.01 sec)
ref（重要）
1
针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
--非唯一索引
mysql> explain select a.id from user a left join user b on a.name=b.name;
+----+-------------+-------+-------+---------------+--------------+---------+---------------------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref                 | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+---------------------+------+-------------+
|  1 | SIMPLE      | a     | index | NULL          | idx_name_age | 308     | NULL                |    2 | Using index |
|  1 | SIMPLE      | b     | ref   | idx_name_age  | idx_name_age | 303     | fcp_kkb_demo.a.name |    1 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+---------------------+------+-------------+
2 rows in set (0.00 sec)

--最左前缀
mysql> explain select * from user where name = 'zhangsan';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 303     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

思考：explain select * from user where sex = '1';
range（重要）
1
索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
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
mysql> explain select * from user where id >1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

--like前缀索引
mysql> explain select * from user where name like 'z%';
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name_age  | idx_name_age | 303     | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

--索引覆盖
mysql> explain select name from user where name like '%san';
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_name_age | 308     | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
all（重要）
1
这个就是全表扫描数据文件，然后再在**server**层进行过滤返回符合要求的记录。
1
2
3
4
5
6
7
mysql> explain select * from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
extra（重要）
1
2
3
4
5
6
7
8
这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有
distinct：在select部分使用了distinct关键字
no tables used：不带from字句的查询或者From dual查询
使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接
即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。
using filesort（重要）：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
using index（重要）：查询时不需要回表查询，直接通过索引就可以获取查询的数据
using where（重要）：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
using filesort
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> explain select * from user order by address;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

--order by id
mysql> explain select * from user order by id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL |    2 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)
using index（重要）
1
2
3
表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错
如果同时出现Using Where ，说明索引被用来执行查找索引键值
如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--全值匹配 覆盖索引
mysql> explain select name,age from user;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_name_age | 308     | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

--索引参与查找
mysql> explain select name,age from user where name='zhangsan';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 303     | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
using where（重要）
1
2
3
4
查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根
据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎
层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显
示using index condition
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
--无索引查询
mysql> explain select name,age from user where address='zhangsan';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

--索引失效
mysql> explain select *from user where age=18;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
--索引下推
mysql> explain select * from user where name='zhangsan';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 303     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name='zhangsan' and age=20;
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 308     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name='zhangsan' and age=20 and address='中信广场';
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra                              |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 308     | const,const |    1 | Using index condition; Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)

索引失效分析

1
2
3
4
5
6
7
8
9
10
1.全值匹配我最爱
2.最佳左前缀法则
3.不要在索引上做计算
4.范围条件右边的列失效
5.尽量使用覆盖索引
6.索引字段上不要使用不等
7.主键索引字段上不可以判断null
8.索引字段使用like不以通配符开头
9.索引字段字符串要加单引号
10.索引字段不要使用or

全值匹配我最爱

1
2
3
4
5
6
7
mysql> explain select * from user where name='zhangsan' and age=20;
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 308     | const,const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

最佳左前缀法则

1
组合索引，带头索引不能死，中间索引不能断
1
2
3
4
5
6
7
8
--错误示例
mysql> explain select * from user where  age=20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

不要在索引上做计算

1
不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> explain select * from user where name='zhangsan';
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 303     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

--错误示例
mysql> explain select * from user where left (name,1)='zhangsan';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

范围条件右边的列失效

1
不能继续使用索引中范围条件（bettween、<、>、in等）右边的列
1
2
3
4
5
6
7
mysql> explain select * from user where name='zhangsan' and age>20;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name_age  | idx_name_age | 308     | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

尽量使用覆盖索引

1
尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少select *
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> explain select * from user;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select name,age from user;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_name_age | 308     | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select id,name,age from user;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_name_age | 308     | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

索引字段上不要使用不等

1
索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from user where name!='zhangsan';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_age  | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name<>'zhangsan';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_age  | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

主键索引字段上不可以判断null

1
2
主键字段上不可以使用 null
索引字段上使用 is null / is not null 判断时，可使用索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--非主键索引
mysql> explain select * from user where name is null;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_age  | idx_name_age | 303     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name is not null;
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name_age  | idx_name_age | 303     | NULL |    2 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
--主键索引
mysql> explain select * from user where id is not null;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

索引字段使用like不以通配符开头

1
索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描
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
26
27
28
29
30
31
mysql> explain select * from user where name like '%san';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from user where name like 'zhang%';
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name_age  | idx_name_age | 303     | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select name,age from user where name like 'zhang%';
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | idx_name_age  | idx_name_age | 308     | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select name,age from user where name like '%san';
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_name_age | 308     | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

索引字段字符串要加单引号

1
索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
1
2
3
4
5
6
7
mysql> explain select * from user where name=123;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_age  | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

索引字段不要使用or

1
索引使用or会导致索引失效，全表扫描
1
2
3
4
5
6
7
mysql> explain select * from user where name='zhangsan' or age=20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_age  | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

总结

1
2
3
4
5
全值匹配我最爱，最左前缀要遵守 
带头大哥不能死，中间兄弟不能断 
索引列上少计算，范围之后全失效 
LIKE符号写最右，覆盖索引不写星 
不等空值还有or，索引失效要少用



[参考链接]

觉得文章有用的话，欢迎点赞打赏~