1. 索引

索引是数据库查询优化最常用的方式之一，合理的建立索引能够加速数据库数据的读取效率，但这不意味着索引越多越好。索引越多，更新（增删改）数据的速度越慢，如果索引建立的不合理可能会适得其反拖慢数据库的响应速度。常见的索引类型有：

索引类型 描述
普通索引 它没有任何的限制条件，是最基础的索引类型。
唯一索引 它要求字段的值必须是唯一的。
主键索引 它是一种特殊的唯一索引，不允许为空，MySQL会自动的为表的主键创建主键索引。
复合索引
（多列索引）		 它可以为表的多个列创建一个索引，一个复合索引最多可以包含16个列。

1.1 创建索引

数据库版本：

1
2
3
4
5
6
SELECT VERSION();
+------------+
| version()  |
+------------+
| 5.7.21-log |
+------------+

数据库建表语句：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `emp` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dept_id` int(11) DEFAULT NULL COMMENT '部门ID',
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `tel` char(11) DEFAULT NULL COMMENT '电话',
  `email` varchar(64) DEFAULT NULL COMMENT '邮件',
  `salary` int(11) DEFAULT NULL COMMENT '薪资',
  `birthday` date DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=gbk COMMENT='员工表';
1.1.1 普通索引

语法：CREATE INDEX index_name ON tb_name(index_col_name)

1
CREATE INDEX idx_deptid ON emp(dept_id);
1.1.2 唯一索引

语法：CREATE UNIQUE INDEX index_name ON tb_name(index_col_name)

1
CREATE UNIQUE INDEX idx_unique_tel ON emp(tel);
1.1.2 复合索引

语法：CREATE INDEX index_name ON tb_name(index_col_name, ...)

1
CREATE INDEX idx_name_email ON emp(name, email);

1.2 删除索引

语法：DROP INDEX index_name ON tb_name

1
DROP INDEX idx_deptid ON emp;

1.3 查看索引

语法：SHOW INDEX FROM tb_name

1
2
3
4
5
6
7
8
9
SHOW INDEX FROM emp;
+-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY                  |            1 | id          | A         |     1989769 |     NULL | NULL   |      | BTREE      |         |               |
| emp   |          0 | idx_unique_tel           |            1 | tel         | A         |     1989769 |     NULL | NULL   | YES  | BTREE      |         |               |
| emp   |          1 | idx_name_email           |            1 | name        | A         |     1986892 |     NULL | NULL   | YES  | BTREE      |         |               |
| emp   |          1 | idx_name_email           |            2 | email       | A         |     1989769 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1.4 使用索引

下面列举几种MySQL查询优化器可以选择使用索引的常见场景。为排除其它索引项的干扰，下面每在分析一条查询语句的执行计划前都假设表还没有建立过索引，相应的建索引语句也将在分析前依次给出。

1.4.1 最左前缀

最左前缀匹配原则是相对于复合索引（多列索引）而言的。假设现有查询SQL如下：

1
SELECT * FROM tbl_name WHERE col1 = val1 AND col2 = val2 AND col3 = val3;

如果在col1列和col2列和col3列分别建立单独的索引，由于MySQL在执行查询时通常只会使用到一个索引项，因此它会尝试使用索引合并或选择一个最严格的可以排除更多的行以得到更少结果的索引来优化查询。

1
CREATE INDEX idx_name ON emp(name);
1
CREATE INDEX idx_deptid ON emp(dept_id);
1
CREATE INDEX idx_birthday ON emp(birthday);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE name = '秋田函' AND dept_id = 2 AND birthday = '1994-09-02';
+----+-------------+-------+------------+-------------+-----------------------------------+-----------------------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                     | key                   | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------+------------+-------------+-----------------------------------+-----------------------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | emp   | NULL       | index_merge | idx_name,idx_deptid,idx_birthday | idx_name,idx_birthday | 66,3    | NULL |    1 |    21.32 | Using intersect(idx_name,idx_birthday); Using where |
+----+-------------+-------+------------+-------------+-----------------------------------+-----------------------+---------+------+------+----------+-----------------------------------------------------+

如果在col1列和col2列和col3列上建立一个复合索引 (col1, col2, col3)，根据最左前缀匹配原则，过滤筛选条件只要是在 (col1)、(col1, col2)、(col1, col2, col3) 列上的都会具有索引检索的功能。

1
CREATE INDEX idx_name_deptid_birthday ON emp(name, dept_id, birthday);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE name = '秋田函' AND dept_id = 2 AND birthday = '1994-09-02';
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_name_deptid_birthday | idx_name_deptid_birthday | 73      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+

复合索引可以看成是一个有序的数组，最左前缀匹配原则是只有在匹配 (col1) 列的前提下，才可以继续匹配下一个有序项 (col2) 。它不能直接匹配 (col2) 或 (col2, col3)。如果条件检索的列不能满足最左前缀匹配原则，则不会使用索引进行结果查找。

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE dept_id = 2 AND birthday = '1994-09-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1952590 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1.4.2 全值匹配

全值匹配原则是相对于复合索引（多列索引）而言的。即索引中的所有列在WHERE子句中都有对应的字段检索条件。在此前提下，即便没有满足复合索引的最左前缀匹配原则，MySQL的查询优化器也会自动的对WHERE子句中的字段检索条件顺序做出相应的调整以至于能够使用合适的索引。

1
CREATE INDEX idx_name_deptid_birthday ON emp(name, dept_id, birthday);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE dept_id = 2 AND name = '秋田函' AND birthday = '1994-09-02';
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_name_deptid_birthday | idx_name_deptid_birthday | 73      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1.4.3 范围查询

对索引的值进行范围查找，常见于>、>=、<、<=、!=、IN、BETWEEN。

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE id > 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 994884 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1.4.4 覆盖索引

如果查询的列正好都是索引列，这就意味着，查询所需的数据直接从索引树中就能够获得，而不需要再通过索引回表查询。这样可以减少I/O，提升效率。如果一个查询只需要访问索引树就能获得查询所需的所有数据，我们就称使用了覆盖索引。

1
CREATE INDEX idx_name ON emp(name);

以下查询语句中因使用了条件!=，导致MySQL进行全表扫描而放弃走索引：

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE name != '秋田函';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 1989769 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

将SELECT *改为SELECT 索引列后，MySQL走上了索引，其Extra中的Using index就是覆盖索引扫描，通过条件过滤检索出来的结果不需要再回表获取额外的数据。

1
2
3
4
5
6
EXPLAIN SELECT id FROM emp WHERE name != '秋田函';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | idx_name      | idx_name | 67      | NULL | 1989769 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
1.4.5 列前缀匹配

列前缀匹配是指检索条件中使用了索引的开始一部分内容进行查找，形式通常是LIKE '限定字符%'。

1
CREATE INDEX idx_name ON emp(name);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE name LIKE '秋田%';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | emp   | NULL       | range | idx_name      | idx_name | 67      | NULL |    6 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

1.5 索引失效

下面列举几种索引存在但是不能被MySQL查询优化器选择使用的常见场景。

1.5.1 以%开头的LIKE查询
1
CREATE INDEX idx_name ON emp(name);

以%限定字符或%限定字符%进行的LIKE查询都会导致查询无法使用索引：

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE name LIKE '%秋田';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1989769 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

使用覆盖索引扫描进行优化：

1
2
3
4
5
6
7
EXPLAIN SELECT * FROM emp WHERE id IN (SELECT id FROM emp WHERE name LIKE '%秋田');
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------+---------+----------+--------------------------+
| id | select_type | table | partitions | type   | possible_keys | key      | key_len | ref         | rows    | filtered | Extra                    |
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------+---------+----------+--------------------------+
|  1 | SIMPLE      | emp   | NULL       | index  | PRIMARY       | idx_name | 67      | NULL        | 1989769 |    11.11 | Using where; Using index |
|  1 | SIMPLE      | emp   | NULL       | eq_ref | PRIMARY       | PRIMARY  | 4       | test.emp.id |       1 |   100.00 | NULL                     |
+----+-------------+-------+------------+--------+---------------+----------+---------+-------------+---------+----------+--------------------------+

在一张200万条数据的表中，原查询耗时5.21 sec，优化后的查询耗时2.23 sec。

1.5.2 数据类型出现隐式转换时
1
CREATE UNIQUE INDEX idx_unique_tel ON emp(tel);

特别是字符串类型，如果缺少引号就会导致查询出现数据类型的隐式转换，致使无法正常使用索引：

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE tel = 15873225085;
+----+-------------+-------+------------+------+----------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_unique_tel | NULL | NULL    | NULL | 1989769 |    10.00 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+---------+----------+-------------+

给字符串常量加上引号之后，就可以正确的使用索引了：

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE tel = '15873225085';
+----+-------------+-------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | const | idx_unique_tel | idx_unique_tel | 23      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
1.5.3 不满足最左前缀匹配原则
1
CREATE INDEX idx_name_deptid_birthday ON emp(name, dept_id, birthday);

在复合索引（多列索引）的场景下，如果检索的列不能满足最左前缀匹配原则，则不会使用复合索引：

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE dept_id = 2 AND birthday = '1994-09-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1952590 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1.5.4 出现OR条件查询

如果查询语句中出现OR，除非OR前后的列都是索引项，否则不能使用索引：

1
CREATE UNIQUE INDEX idx_unique_tel ON emp(tel);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE tel = '18993283686' or name = '秋田函';
+----+-------------+-------+------------+------+----------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_unique_tel | NULL | NULL    | NULL | 1989769 |    10.00 | Using where |
+----+-------------+-------+------------+------+----------------+------+---------+------+---------+----------+-------------+

如果为name列也加上一个单独的索引（或使用复合索引），查询就可以正常使用索引：

1
CREATE INDEX idx_name ON emp(name);
1
2
3
4
5
6
XPLAIN SELECT * FROM emp WHERE tel = '18993283686' or name = '秋田函';
+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | emp   | NULL       | index_merge | idx_unique_tel,idx_name | idx_unique_tel,idx_name | 23,67   | NULL |    4 |   100.00 | Using union(idx_unique_tel,idx_name); Using where |
+----+-------------+-------+------------+-------------+-------------------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
1.5.5 在索引列上做计算或函数操作

如果在索引列上进行运算或对索引列使用函数操作，也会导致索引失效：

1
CREATE INDEX idx_birthday ON emp(birthday);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE DATE_FORMAT(birthday, '%Y') - DATE_FORMAT(NOW(), '%Y') = 18;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1989769 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1.5.6 使用!=判断条件

如果在索引列上使用!=进行条件判断，也会导致索引失效：

1
CREATE INDEX idx_name ON emp(name);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE name != '秋田函';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 1989769 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1.5.7 如果MySQL估算全表扫描比索引快时

当MySQL估算全表扫描比索引快时，则不会使用索引：

1
CREATE INDEX idx_deptid ON emp(dept_id);
1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE dept_id < 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_deptid    | NULL | NULL    | NULL | 1989769 |    23.21 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

通过开启optimizer_trace可以跟踪优化器在整个执行计划中是如何选择最优方案的。

查看optimizer_trace相关参数配置：

1
2
3
4
5
6
7
8
9
10
SHOW VARIABLES LIKE '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=off,one_line=off                                                    |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 16384                                                                      |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+

可以看到optimizer_trace选项的enabled=off，默认是关闭的。开启optimizer_trace：

1
SET optimizer_trace="enabled=on";

开启之后，首先先执行一个查询SQL的执行计划：

1
2
3
4
5
6
EXPLAIN SELECT * FROM emp WHERE dept_id < 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | idx_deptid    | NULL | NULL    | NULL | 1989769 |    23.21 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

然后查看优化器在整个执行计划中是如何选择最优方案的：

1
SELECT * FROM information_schema.optimizer_trace\G

下面是全表扫描估计要访问的记录行数以及代价计算：

1
2
3
4
"table_scan": {
    "rows": 1989769,
    "cost": 410416
}

下面是索引扫描估计要访问的记录行数以及代价计算：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
"range_scan_alternatives": [
    {
        "index": "idx_deptid",
        "ranges": [
            "NULL < dept_id < 2"
        ],
        "index_dives_for_eq_ranges": true,
        "rowid_ordered": false,
        "using_mrr": false,
        "index_only": false,
        "rows": 461886,
        "cost": 554264,
        "chosen": false,
        "cause": "cost"
    }
]

索引的代价计算（cost）为554264，全表扫描的代价计算（cost）为410416。可见，索引的代价计算要高于全表扫描的代价。因此，优化器更倾向于选择全表扫描。