Gang's Blog.
Gang's Blog.

MySQL索引优化

Gang Gang 收录于 MySQL
 约 3000 字 预计阅读 15 分钟 

Explain函数

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


字段意义

测试SQL:

 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

-- 演员表
DROP TABLE IF EXISTS `actor`;
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');

-- 电影表
DROP TABLE IF EXISTS `film`;
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');

-- 电影和演员关联表
DROP TABLE IF EXISTS `film_actor`;
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. 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能够在优化阶段分解查询语句, 在之心该阶段用不着再访问表或者索引, 例如: 获取索引列中的最小值等

      1
2
3
4
5
6
7

      mysql> explain select min(id) from film;

+----+-------------+-------+------------+------+---------------+----+---------+------+-------+-------------------------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref  | rows  | filtered                | Extra                        |
+----+-------------+-------+------------+------+---------------+----+---------+------+-------+-------------------------+------------------------------+
| 1  | SIMPLE      |       |            |      |               |    |         |      |       |                         | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+----+---------+------+-------+-------------------------+------------------------------+

    • system：表只有一行（等同于 system 表）。

    • const：表最多有一行匹配，常用于主键或唯一索引。

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

      1
2
3
4
5
6
7
8

      mysql> explain select * from film_actor left join film 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_actor  |            | ALL   |                   |                                      |         |                                    | 3    | 100.00   |       |
| 1  | SIMPLE      | film        |            | eq_ref| PRIMARY           | PRIMARY                              | 4       | explain_test.film_actor.film_id    | 1    | 100.00   |       |
+----+-------------+-------------+------------+-------+-------------------+--------------------------------------+---------+------------------------------------+------+----------+-------+

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

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

        1
2
3
4
5
6
7

        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  |            | ref  | idx_name     | idx_name| 33      | const| 1    | 100.00   | Using index     |
+----+-------------+-------+------------+------+--------------+---------+---------+-----+------+----------+-----------------+

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

        1
2
3
4
5
6
7
8

        mysql> explain select film_id from film left join film_actor 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        |            | index | idx_name          | idx_name          | 33      |                        | 3    | 100.00   | Using index      |
| 1  | SIMPLE      | film_actor  |            | ref   | idx_film_actor_id | idx_film_actor_id | 4       | explain_test.film.id   | 1    | 100.00   | Using index      |
+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+------------------------+------+----------+------------------+

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

      1
2
3
4
5
6
7

      mysql> explain select * from actor where id > 1;

+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| 1  | SIMPLE      | actor |            | range | PRIMARY       | PRIMARY  | 4       |     | 2    | 100.00   | Using where          |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

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

      1
2
3
4
5
6
7

      mysql> explain select * from film;

+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| 1  | SIMPLE      | film  |            | index | idx_name      | idx_name | 33      |     | 3    | 100.00   | Using index          |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

    • ALL：全表扫描。

      1
2
3
4
5
6
7

      mysql> explain select * from film;

+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| 1  | SIMPLE      | film  |            | index | idx_name      | idx_name | 33      |     | 3    | 100.00   | Using index          |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

  • 一般来说, 得保证查询至少要达到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 子句过滤行。

      1
2
3
4
5
6
7

      explain select film_id from film_actor where film_id

+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| 1  | SIMPLE      | actor |            | ALL  |               |    |         |     | 3    | 33.33    | Using where      |
+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+

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

      1
2
3
4
5
6
7

      mysql> explain select film_id from film_actor where film_id

+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------+----------+------------------------+-------------------------+
| id | select_type | table       | partitions | type  | possible_keys     | key               | key_len | ref   | rows     | filtered               | Extra                   |
+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------+----------+------------------------+-------------------------+
| 1  | SIMPLE      | film_actor  |            | index | idx_film_actor_id | idx_film_actor_id | 8       |       | 3        | 100.00                 | Using where; Using index|
+----+-------------+-------------+------------+-------+-------------------+-------------------+---------+-------+----------+------------------------+-------------------------+

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

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

        1
2
3
4
5
6
7

        mysql> explain select distinct name from actor;

+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| 1  | SIMPLE      | actor |            | ALL  |               |    |         |     | 3    | 100.00   | Using temporary  |
+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+------------------+

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

        1
2
3
4
5
6
7

        mysql> explain select distinct name from film;

+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref | rows | filtered | Extra                |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+
| 1  | SIMPLE      | film  |            | index | idx_name      | idx_name | 33      |     | 3    | 100.00   | Using index          |
+----+-------------+-------+------------+-------+---------------+----------+---------+-----+------+----------+----------------------+

    • Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

    • Using join buffer：使用连接缓存。

    • Impossible WHERE：WHERE 子句永远为 false，返回空结果集。

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

      1
2
3
4
5
6
7

      mysql> explain select min(id) from film;

+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra                   |
+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+-------------------------+
| 1  | SIMPLE      |       |            |      |               |    |         |     |      |          | Select tables optimized away  |
+----+-------------+-------+------------+------+---------------+----+---------+-----+------+----------+-------------------------+


最佳实践


最佳实践


1. 全值匹配 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

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 |            | ref  | idx_name_age_position     | idx_name_age_position     | 74      | const | 1    | 100.00   |                     |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+


EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra               |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 78      | const,const | 1    | 100.00   |                     |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+

2. 最左前缀法则

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

mysql> EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref         | rows | filtered | Extra               |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+
| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 78      | const,const | 1    | 100.00   |                     |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------+------+----------+---------------------+


mysql> EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 50.00    | Using where      |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+


mysql> EXPLAIN SELECT * FROM employees WHERE position = 'manager';
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 50.00    | Using where      |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+

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

 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

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 |            | ref  | idx_name_age_position     | idx_name_age_position     | 74      | const | 1    | 100.00   |                     |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+---------------------+


mysql> EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 50.00    | Using where      |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+


-- 给hire_time增加一个普通索引： 
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| id | select_type | table     | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra            |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
| 1  | SIMPLE      | employees |            | ALL  |               |    |         |     | 2    | 100.00   | Using where      |
+----+-------------+-----------+------------+------+---------------+----+---------+-----+------+----------+------------------+
-- 还原最初状态
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

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 |            | ref  | idx_name_age_position     | idx_name_age_position     | 140     | const,const,const | 1    | 100.00   |                     |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-------------------+------+----------+---------------------+


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 |            | range | idx_name_age_position     | idx_name_age_position     | 78      |     | 1     | 50.00    | Using index condition|
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+

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


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

1
2
3
4
5
6

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

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 当employees表只有3条数据时
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 |            | range | idx_name_age_position     | idx_name_age_position     | 74      |     | 2     | 100.00   | Using index condition|
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+-------+----------+----------------------+
-- 当employees表有20w条数据时
+----+-------------+-----------+------------+------+---------------------------+----+---------+-----+--------+----------+------------------+
| id | select_type | table     | partitions | type | possible_keys             | key| key_len | ref | rows   | filtered | Extra            |
+----+-------------+-----------+------------+------+---------------------------+----+---------+-----+--------+----------+------------------+
| 1  | SIMPLE      | employees |            | ALL  | idx_name_age_position     |    |         |     | 199696 | 50.00    | Using where      |
+----+-------------+-----------+------------+------+---------------------------+----+---------+-----+--------+----------+------------------+

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

1
2
3
4
5
6

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

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 |            | ALL  |                |     |         |       | 199696 | 11.11    | Using where    |
+----+-------------+-----------+------------+------+----------------+-----+---------+-------+--------+----------+----------------+


-- 可以走索引
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 |            | range | idx_name_age_position     | idx_name_age_position     | 74      |     | 1    | 100.00   | Using index condition |
+----+-------------+-----------+------------+-------+---------------------------+---------------------------+---------+-----+------+----------+----------------------+

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

1
2
3
4
5
6

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

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

EXPLAIN SELECT * FROM employees WHERE name = '1000';
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------+--------+----------+-------+
| 1  | SIMPLE      | employees |            | ref  | idx_name_age_position     | idx_name_age_position     | 74      | const| 1      | 100.00   |       |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+------+--------+----------+-------+

-- 走的全表查询
EXPLAIN SELECT * FROM employees WHERE name = 1000;
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-----+--------+----------+------------------+
| id | select_type | table     | partitions | type | possible_keys             | key                       | key_len | ref | rows   | filtered | Extra            |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-----+--------+----------+------------------+
| 1  | SIMPLE      | employees |            | ALL  | idx_name_age_position     |                           |         |     | 199696 | 10.00    | Using where      |
+----+-------------+-----------+------------+------+---------------------------+---------------------------+---------+-----+--------+----------+------------------+

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

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


11. 范围查询 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 走全表
explain select * from employees where age >=1 and age <=100000;
+----+-------------+-----------+------------+------+---------------------------+-----+---------+-----+--------+----------+------------------+
| id | select_type | table     | partitions | type | possible_keys             | key | key_len | ref | rows   | filtered | Extra            |
+----+-------------+-----------+------------+------+---------------------------+-----+---------+-----+--------+----------+------------------+
| 1  | SIMPLE      | employees |            | ALL  | idx_age                   |     |         |     | 199696 | 50.00    | Using where      |
+----+-------------+-----------+------------+------+---------------------------+-----+---------+-----+--------+----------+------------------+

-- 走索引
explain select * from employees where age >= 1 and age <= 10000;
+----+-------------+-----------+------------+-------+----------------+---------+---------+-----+-------+----------+---------------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key     | key_len | ref | rows  | filtered | Extra                           |
+----+-------------+-----------+------------+-------+----------------+---------+---------+-----+-------+----------+---------------------------------+
| 1  | SIMPLE      | employees |            | range | idx_age        | idx_age | 4       |     | 38352 | 100.00   | Using index condition           |
+----+-------------+-----------+------------+-------+----------------+---------+---------+-----+-------+----------+---------------------------------+

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

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

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



 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

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=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

-- 插入一些示例数据
DROP PROCEDURE
IF EXISTS insert_emp;
delimiter;;
CREATE PROCEDURE insert_emp () BEGIN
	DECLARE i INT;
	SET i = 1;
	WHILE( i <= 100000 ) DO
		INSERT INTO employees ( NAME, age, position )VALUES( CONCAT( 'lisi', i ), i, 'dev' );
		SET i = i + 1;	
	END WHILE;
END;;
delimiter;
CALL insert_emp ();



-- mysql5.7关闭ONLY_FULL_GROUP_BY报错
-- select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

测试例子

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

1
2
3
4
5
6
7
8

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|            | range| idx_name_age_position  | idx_name_age_position  | 74       |     | 1    | 33.33    | Using index condition|
----+-------------------+----------+------------+------+------------------------+------------------------+----------+-----+------+----------+---------------------+

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


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

1
2
3
4
5
6
7
8
9

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) 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 |            | range | idx_name_age_position  | idx_name_age_position  | 74       |     | 1    | 50.00    | Using index condition|
+----+-------------+-----------+------------+-------+------------------------+------------------------+----------+-----+------+----------+---------------------+

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

相关内容

更新于 2022-05-03 
CC BY-NC-SA 4.0
MySQL性能调优
返回 | 主页
0%