sql explain

1. sql explain

使用 Explain 可以查看 sql 的性能瓶颈信息, 并根据结果进行 sql 的相关优化。在 select 语句前加上 explain 关键字, 执行的时候并不会真正执行 sql 语句, 而是返回 sql 查询语句对应的执行计划信息。

当然如果 select 语句的 from 后面有一个子查询的话, 就会执行子查询了并把结果放到一个临时表中。

有三张表:

-- 演员表
 CREATE TABLE `actor` (
 `id` INT ( 11 ) NOT NULL,
 `name` VARCHAR ( 45 ) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY ( `id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT 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');
 
-- 电影表
CREATE TABLE `film` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR ( 10 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_name` ( `name` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
 
-- 演员和电影中间表
CREATE TABLE `film_actor` (
 `id` INT ( 11 ) NOT NULL,
 `film_id` INT ( 11 ) NOT NULL,
 `actor_id` INT ( 11 ) NOT NULL,
 `remark` VARCHAR ( 255 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_film_actor_id` ( `film_id`, `actor_id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
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

执行 explain select * from actor;

结果:

mysql> explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

根据返回的信息可以分析 sql 的性能瓶颈从而进行优化。

下面分析其中每个字段对应的含义。

每个字段对应的含义

1.1. id

代表 sql 中查询语句的序列号, 序列号越大则执行的优先级越高, 序号一样谁在前谁先执行。id 为 null 则最后执行。

1.2. select_type

查询类型, 表示当前被分析的 sql 语句的查询的复杂度。这个字段有多个值。

• SIMPLE: 表示简单查询。

mysql> explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

• PRIMARY: 表示复杂查询中的最外层的 select 查询语句。

• SUBQUERY: 表是子查询语句 跟在 select 关键字后面的 select 查询语句;

mysql> explain select (select 1 from film where id =1) from actor;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | actor | NULL       | index | NULL          | PRIMARY | 4       | NULL  |    3 |   100.00 | Using index |
|  2 | SUBQUERY    | film  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

• derived: 派生查询, 跟在一个 select 查询语句的 from 关键字后面的 select 查询语句 例如:

mysql> set session optimizer_switch='derived_merge=off'; -- 关闭 mysql5.7 新特性对衍生表的合并优化
Query OK, 0 rows affected (0.00 sec)

mysql> explain select (select 1 from actor where id =1) from (SELECT * from film where id=1) ac;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived3> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL        |
|  3 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | actor      | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

1.3. table

表示当前访问的表的名称。

当 from 中有子查询时, table 字段显示的是 N 为 derived 的 id 的值。

1.4. partitions

返回的是数据分区的信息, 不常用 这里不做分析。

1.5. type

这个字段决定 mysql 如何查找表中的数据, 查找数据记录的大概范围。这个字段的所有值表示的从最优到最差依次为:

system > const > eq_ref > ref > range > index > all;

一般来说我们优化到 range 就可以了, 最好到 ref。

• null: type 字段的值如果为 null, 那么表示当前的查询语句不需要访问表, 只需要从索引树中就可以获取我们需要的数据;

一般如果是主键索引的话 , 查询主键字段或者唯一索引的话 查询主键字段 type 字段的值就为 null。

mysql> explain select id from actor where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

• system/const: 用户主键索引或者唯一索引查询时, 只能匹配 1 条数据 一般可以对 sql 查询语句优化成一个常量, 那么 type 一般就是 system 或者 const, system 是 const 的一个特例。

mysql> explain select * from (select * from film where id = 1) tmp;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL  |
|  2 | DERIVED     | film       | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

• eq_ref: 在进行连接查询时, 例如 left join 时, 如果是使用主键索引或者唯一索引连接查询 , 结果返回一条数据, 则 type 的值为一般为 eq_ref。

mysql> explain SELECT * from film_actor left join film on film.id = film_actor.film_id;
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref                      | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
|  1 | SIMPLE      | film_actor | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |    3 |   100.00 | NULL  |
|  1 | SIMPLE      | film       | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | mysql.film_actor.film_id |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8

分析下这个 sql, 首先我们需要查询的是 film_actor 中间表 且这个表是与 film 表进行主键关联的, 索引 film_actor 表中的 film_id 字段在 film 表中只有一个唯一值, 所以: eq_ref

那么, 反过来在看一下

mysql>  explain SELECT * from film left join film_actor on film_actor.film_id = film.id;
+----+-------------+------------+------------+-------+-------------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys     | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+------------+------------+-------+-------------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | film       | NULL       | index | NULL              | idx_name | 33      | NULL |    3 |   100.00 | Using index                                        |
|  1 | SIMPLE      | film_actor | NULL       | ALL   | idx_film_actor_id | NULL     | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+-------+-------------------+----------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8

film 表和 film_actor 中间表关联查询, 根据 film 电影表中的主键 id 和 film_actor 表中的 film_id 字段进行关联的。电影表中的主键 id 在 film_actor 中并不是唯一的。所以: index ALL

对于 film 需要确定查询 id 从索引树中就可以获取值 所以是 index。对于 film_actor 就是全表扫描了。

• ref: 相比较 eq_ref, 不使用主键索引或者唯一索引, 使用的是普通索引或者唯一索引的部分前缀, 索引与一个值进行比较后可能获取到多个符合条件的行, 不在是唯一的行了。

简单查询, name 是普通索引

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

复杂查询, film_actor 有联合索引 idx_film_actor_id('film_id','actor_id') 这里使用了联合索引的左前缀 film_id

mysql> explain select fa.film_id from film f left join film_actor fa on fa.film_id = f.id;
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys     | key               | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | index | NULL              | idx_name          | 33      | NULL       |    3 |   100.00 | Using index |
|  1 | SIMPLE      | fa    | NULL       | ref   | idx_film_actor_id | idx_film_actor_id | 4       | mysql.f.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-------------------+-------------------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

• range: 通常使用范围查找, 例如 between, in, <, >, >= 等使用索引进行范围检索。

mysql> explain select * from film where id >2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

• index: 扫描索引树就能获取到的数据, 一般是扫描二级索引, 并且不会从根节点扫描, 一般直接扫描二级索引的叶子节点, 速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键, 所以二级索引还是比较小的, 扫描速度相比 All 还是很快的。这里用到了覆盖索引, 什么是覆盖索引: 可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历 name 索引树就可以获取到主键 id 的值就是覆盖索引。

mysql> explain select id from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_name | 33      | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

• ALL: 这是一种效率最低的 type, 需要扫描主键索引树的叶子节点, 获取数据是表中其他列的数据, 即全表扫描。

和 index 有什么区别呢?

拿 film 电影表举例: 添加一个 remark 影评字段, film 表结构如下:

CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

表中建了两个索引: id 主键索引 idx_name(name) 二级索引。

那么:

mysql> explain select id,name from film ;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_name | 33      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7

上述 sql 查询 id, name 两个字段, 分析 mysql 索引数据结构, 以及 mysql 优化后一般扫描二级索引, 索引会扫描 idx_name 索引树的叶子节点, 那么根据 B+Tree 树的结构, 叶子节点保存的是 name 字段的索引值 和 data 数据(主键 id)。而正好我们只需要查询 id 和 name 两个字段, 我们查询的字段被索引(二级索引)给覆盖了 这就是覆盖索引, 因此 type 的类型就是 index。

再来:

mysql> explain select remark from film ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

比较上一个 sql, 这个 sql 只查询了一个字段: remark, 经过上面分析, 这个字段是不在 idx_name 索引树的叶子节点上的, 所以 mysql 不会在扫描 idx_name 索引树了, 直接扫描主键索引的叶子节点, 即进行全表扫描, 这个时候 type 类型为 ALL。

1.6. possible_keys

这个字段显示的是 sql 在查询时可能使用到的索引, 但是不一定真的使用, 只是一种可能。

如果在进行 explain 分析 sql 时, 发现这一列有值, 但是 key 列为 null, 因为 mysql 觉得可能会使用索引, 但是又因为表中的数据很少, 使用索引反而没有全表扫描效率高, 那么 mysql 就不会使用索引查找, 这种情况是可能发生的。

如果该列是 NULL, 则没有相关的索引。在这种情况下, 可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能, 然后用 explain 查看效果。

1.7. key

sql 执行中真正用到的索引字段。

1.8. key_len

用到的索引字段的长度, 通过这个字段可以显示具体使用到了索引字段中的哪些列(主要针对联合索引): 计算公式如下

• 字符串
  • char(n): n 字节长度
  • varchar(n): 如果是 utf-8, 则长度 3n + 2 字节, 加的 2 字节用来存储字符串长度
• 数值类型
  • tinyint: 1 字节
  • smallint: 2 字节
  • int: 4 字节
  • bigint: 8 字节
• 时间类型
  • date: 3 字节
  • timestamp: 4 字节
  • datetime: 8 字节
• 如果字段允许为 NULL, 需要 1 字节记录是否为 NULL

索引最大长度是 768 字节, 当字符串过长时, mysql 会做一个类似左前缀索引的处理, 将前半部分的字符提取出来做索引。

1.9. ref

表示那些列或常量被用于查找索引列上的值

1.10. rows

表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。

1.11. Extra

展示一些额外信息。

索引实践

以下实践以 employees 表为例。一个主键索引 一个联合索引

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) 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=11 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
1
2
3
4
5
6
7
8
9

联合索引最左列原则

例 1:

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
1

使用联合索引中的 name 字段索引。

例 2:

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
1

使用联合索引中的 name 和 gae 字段索引。

例 3:

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

使用联合索引中的 name age position 字段索引。

例 4:

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev'; 
1

仅仅使用了联合索引中的 name 字段, 因为中间 age 字段断了, 所以 position 字段索引并未用到。解释一下:

索引是一个有序的数据结构, 也就是说使用索引时, 需要索引保证有序, 那么在联合索引中, 是先按照 name 排序, name 相同情况下, 在按照 age 排序, age 相同情况下 在按照 position 排序, 因此如果 age 不确定情况下, position 是无序的, 所以即使你是用 position 查询了 也无法走索引的。这就是最左列原则并且中间不能断。

例 5:

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

这个使用了联合索引中的 name 和 age 字段, 没有使用 position, 为什么? 原理其实和上面差不多。分析一波:

首先按照顺序 name->age->position,name 已经确定了等于 LiLei, 那么 age 就是有序的了, 所以检索 age>22 的就很容易了 因为 age 有序。但是 age 值其实是不确定的, age 可以是 23,24,25… 等等, 所以在 age 不确定情况下 position 是无序的 因此是不走 position 索引字段的。

全值匹配

mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

不建议在索引列上做任何操作, 否则索引会失效转而全表扫描

-- 查询 name 的最左变的两个字符为 Li 的行
mysql> EXPLAIN SELECT * FROM employees WHERE LEFT(name,2) = 'Li';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8

尽量使用覆盖索引 不需要再回表查询了 效率较高

再试用 != 或 <> 不等于查询时, 会导致索引失效。

mysql> EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

尽量不要使用 or, in 操作, 在某些情况下也会导致索引失效。

• 第一种情况: 当表中只有两条数据 数据量很少的时候

mysql> explain SELECT * from employees where name in ('LiLei','abc');
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

使用 in 查询, 没有走索引, 进行了全表扫描, 为什么? 分析一波:

首先 如果使用索引的话, mysql 大概会怎么操作? 应该先在 name 索引树中定位到 name=LiLei 这个节点(最少一次 I/O), 然后定位到 name=abc 这个节点(一次 I/O), 然后分别拿到主键 id, 在去主键索引树上扫描定位(最少又要两次 I/O), 总共 4 次 I/O。

如果不使用索引, 直接全表扫描, 那么直接扫描主键索引树的叶子节点 只需要两次 I/O 即可(因为只有两条数据), 所以 mysql 评估全表扫描效率可能会更高, 就不会在走索引了。

• 第二种情况: 当表中数据量很多, 例如 7 条数据

同样的 sql 查询

mysql> explain SELECT * from employees where name in ('LiLei','abc');
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

结果: 走了索引

为什么会出现这种情况? 再来分析一波:

首先走索引的话 大概需要 4 次 I/O 上面已经分析过了。

那么不走索引的话 需要全表扫描 最坏的情况需要扫描 7 次, 进行 7 次 I/O,mysql 评估一下发现全表扫描的效率可能是低于走索引的, 所以就走了索引。

• 第三种情况: 数据还是 7 条, 但是我 in 查询时条件有 8 个

mysql> explain SELECT * from employees where name in ('LiLei','abc','cde','asc','ssw','2dff','wsa','sda');
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

看下结果:

为啥又不走索引了呢? 经过上面的两波强势分析, 这里也很容知道原因, 就不过多的赘述了。or 查询的情况类似。

is null, is not null 一般情况下也无法使用索引

是用字符串查询 不见引号 索引也会失效

mysql> explain SELECT * from employees where name = 1324;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
1
2
3
4
5
6
7

针对范围查找的不走索引的优化

首先看个例子:

-- 先给 age 加一个独立索引
mysql> ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查询 age 在 1 到 2000 分为内的数据
mysql> explain SELECT * from employees where age >1 and age < 2000 ;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

显然并没有走索引 为什么? 再来强势分析一波:

首先, 我们脑海中要有一个 age 的索引树:

我们要找到 1-2000 的数据, 那么在这棵树书上怎么定位?

如果我来定位的话 我会定位一个 age=2 在树上的位置 在定位一个 age=1999 在树上的位置, 然后从 age=2 的节点开始取右边的节点, 一直取下去 直到 age=1999 为止, 但是我们表总只有 7 条数据, mysql 觉得这样操作还没有全表扫描快, 毕竟一共才几条数据全表扫描反而更快些, 所以 mysql 就去全表扫描了。

怎么优化呢?

mysql> explain SELECT * from employees where age >1 and age < 1000 ;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * from employees where age >1001 and age < 2000 ;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_age       | idx_age | 4       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

把一个大的范围拆成多个小的范围 可以利用索引查询。

like 查询建议使用 xxx% 方式匹配, %xxx 或者 %xxx% 索引失效

mysql> EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM employees WHERE name like '%Lei%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

结果: 全表扫描

思考下在索引树上 name 的排序规则, 先按照第一个字符比较然后第二个字符依次向后比较, 如果是用 %xxx, 字符串前面的字符不确定, 怎么在树上定位呢? 显然没法按照顺序定位, 只能一个一个遍历比较 所以不会走索引。%xxx% 也一样。

在看下面这个例子

mysql> EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7

结果: 走了索引

其实 Lei% 匹配相当于范围查询, 只要 name 的值的前三个字符为 Lei 符合条件, 等价于查找前三个字符 =Lei 的字符串, 这个在索引树上是有序的, 当然可以使用索引定位。

总结:

1. 使用 explain 关键字, 可以分析出 sql 的性能瓶颈并加以优化
2. 了解 explain 返回的各字段值代表的意义, 结合索引数据结构有助于我们对 sql 的查询效率的分析和优化
3. 列举部分可能不会进行索引检索的情况, 例如 !=, <>, is null, like 的某些情况, or 或者 in 的某些情况, 字符串不加引号等
4. 对某些不走索引查询的情况作了一些比较详细的分析