1. 引言

1.1 为什么做索引优化？

这一篇来谈谈为什么要通过加索引的方式对程序整体的运行做优化。

而谈到这个问题，就不得不提及是什么因素限制了程序的性能和速度（暂不考虑并发），如果你在内存中进行整数集合的遍历等操作，你会发现其实即使数据量略大，例如达到了几万，整体的耗时，还是可以在毫秒级完成，当然了，前提你要用聪明一些的算法，避免各种嵌套循环。但是如果你试图从数据库，例如 MySQL 中去读写数据，就会发现数据可能会访问时间大大增加，例如增加到几百毫秒甚至几秒。

所以，对于一个应用来说，如果不考虑异步化、以及编码算法的优劣，其实瓶颈一般都卡在了网络 IO（例如 Dubbo 调用），和磁盘 IO（数据库读写） 中。而这一篇我们重点就看看对数据库层面的索引优化。

MySQL 5.7参考手册 中有这么一段，关于 insert 优化的篇章

8.2.4.1 Optimizing INSERT Statements

To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.（建议尽量将 insert 中多带几个值，从而减少 insert 的次数因为时间耗损在客户端和服务端的通信时间非常多）

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:（时间损耗，构成如下，中文是我翻译的百分比）

  • Connecting: (3) | 连接：30%
  • Sending query to server: (2) | 向服务器发送查询：20%
  • Parsing query: (2) | 解析查询：20%
  • Inserting row: (1 × size of row) | 插入行：10% * 行的大小
  • Inserting indexes: (1 × number of indexes) | 插入索引：10% * 索引数
  • Closing: (1) | 结束：10%

1.2 优化思路

一般来说，优化数据库层面的方向，以查询为例，大概有两个思路：

  • 通过 ES 等大数据手段，转移压力
    • 例如跑一份凌晨的定时任务，将多张 MySQL 表的数据灌到 ES 的日表中。实现快速查询，但是这已经脱离了对关系型数据库层面的优化了。
  • 优化关系型数据库本身，例如增加合理的索引等。

2. 创建索引

2.1 类型

创建索引之前，我们先看一下索引的类型，大家可以直接在 Navicat 或者 SQLYog 等可视化软件上，查看创建索引时索引类型下拉框的选项：

  • 全文索引：FULLTEXT
  • 普通索引：NORMAL
  • 空间索引：SPATIAL
  • 唯一索引：UNIQUE

其实我们需要关注的主要就是普通索引和唯一索引，因为全文索引我们一般都会考虑用 ES ，很少用，空间索引我也几乎没用过。

普通索引其实已经可以组织树的结构了，唯一索引就是在其基础上，要求不可重复。

  • 例如因为网络抖动等原因，造成了本只应该存储一条的数据，存储了多条，就会导致下一次查询的地方会报 MyBatis 异常。

而我们尝尝听说的主键索引其实就是是唯一索引 + 非空的约束

2.2 索引的方法

除了类型外，你还需要知道实现索引有两种方式，一个是默认的 B+ 树，另一个就是哈希。虽然叫做 TREE 但是要知道其本质是 B+ 树。

关于 B+ 树，上一篇已经谈过了。如果接触过 Java 中的 HashMap，那么我相信对于哈希来说，大家也并不会陌生。

需要说明的就三点

  • 哈希结构不能范围查询，因为结果是散列无序的。
  • 哈希只适合精确值查询，不能做模糊查询。
  • 不过哈希结构的查询速度是非常快的，其时间复杂度O(1)，而B+树是O(logn)

我们一般来说都是选择默认的 TREE 类型。

2.3 创建

一般建表的时候，我们就会将必要的索引加上，但是后期根据业务和数据等的需要，在表结构中，修改或者增删索引的场景也非常常见。既然是优化，所以必然都是事后，来看一下对应的语法：

  1. -- 1.添加PRIMARY KEY（主键索引） 
  2. ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) ;
  3. -- 2.添加UNIQUE(唯一索引) 
  4. ALTER TABLE `table_name` ADD UNIQUE (`column`);
  5. -- 3.添加INDEX(普通索引) 
  6. ALTER TABLE `table_name` ADD INDEX index_name (`column`);
  7. -- 4.添加联合索引 
  8. ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`);

上面是通过命令行的方式，如果是自己个人的表，通过 Navicat 等可视化软件直接编辑/设计表就可以修改。

但是如果是公司的表，数据的变更就需要提个工单，会有 DBA 的同学帮忙操作。

但是如果表中的数据很多，修改既有索引的时候一定要慎之又慎，以及最好设置成凌晨的定时任务，规避风险。

3. 索引的优劣及如何选择

3.1 优势

快！！！
查询的时候，如果命中了索引，那么查询的速度会非常快。

但是它的快，不仅仅是查询速度上的快，对于排序（ORDER BY）以及分组（GROUP BY）的速度也会有极大的提升，虽然从人的感知上，仍然是查询速度很快，但是对于我们写 SQL 而言，就可能会有意识的在使用排序分组的时候向索引靠拢。

3.2 劣势

创建索引新增了一个维护的内容，查询效率会获得极大地提升，但是会一定程度降低增删改速度（待研究）。
联合索引时要考虑索引失效的问题。
索引若过多会增加查询优化器的选择时间。

3.3 如何选择

如果已经参加工作了，那么大家可能很容易感受到，建立索引的难度根本不在语法上，而在于选择和决策上。因为只需要短短的一两句语法就可以实现索引，但是如何选择合适的语法才是重中之重。

如果建立索引的话，可以有这么几个点参考：

3.3.1 建立在高区分度的字段上

这个原则其实很好理解，如果通过索引不能很快的缩小范围，那么处理较多数据的时间成本或许仍然很大。例如一个买家对应一个唯一的 userId，那么userId 就可以作为一个索引，这样就可以指向到属于这个用户的数据中去。当然如果分库分表，这个 userId 一般还会作为分库分表键。

3.3.2 建立在查询多、修改少的字段上

索引我们可以理解为目录，但是如果一个目录已经编排好了，但是你对这个字段频繁的修改，那么就带来了一个额外的操作，即重新修改目录，这也会带来一定的成本。

所以一般我们的索引字段都几乎不改或者改的少。

3.3.3 避免重复索引

例如你通过 uid1 和 uid2 共同建立了联合索引，但是此时你再建立一个 uid1 的普通索引，那么这个索引就是冗余的，因为联合索引已经涵盖了这种情况，所以查询已经没问题了。但是又需要多余维护一个 uid1 的普通索引，在物理存储上会有成本。

3.3.4 联合索引优先于单个索引

因为联合索引会根据最左原则来走，所以会比单个索引更有价值，而且索引不是越多越好，最终也只会执行一条索引。

4. 聚簇索引和非聚簇索引

4.1 定义

说明：我们的讲解都是以现在最常用的 InnoDB 引擎来说的，至于它和 MyISAM 的区别，在我开源的知识问答的 MySQL 篇已经提过了，这里不再赘述。

简单先说一下定义：

  • 如果找到索引就可以找到数据，就是聚簇索引，InnoDB 就是聚簇索引。
  • 如果索引的存储和数据的存储是分离的，也就是找到索引但是没有找到数据，需要根据索引上的值再次回查数据，就是非聚簇索引，例如 MyISAM。

对于聚簇索引你可以理解为索引和数据是挂在一起的，相当于每一颗树下都带着数据，即每一行完整的数据都直接挂在叶子节点下，查询时可以直接返回。

例如下图中右侧的图就是聚簇索引。
6.png

4.2 主键索引、辅助索引

主键索引就是唯一索引 + 非空的约束，例如常见的自增 id，而辅助索引其实就是我们上面谈到的普通索引。

重点要说的是，我们知道个索引都是一棵树，如果一个辅助索引也和主账号一致树的叶子结点挂满了数据，这样的话，相当于在物理存储中，存储了两份一模一样的数据，数据冗余，而且更新时还要同步多张表，有存在数据不一致的风险。

所以对于 InnoDB 来说，辅助索引只存储索引字段 + 主键，必要时，例如查询整行数据，就需要根据主键进行回表查询。

4.3 索引覆盖避免回表

这是很重要的一个概念，如果进行回表，相当于要多去查询一趟主键索引，所以其实效率会降低，例如主键索引和普通索引的树都是 5 层，根据一个普通索引去查询全部字段后，走完普通索引的树，还需要根据主键去回表查询，相当于要走 10 趟。

所以使用主键查询是最快的方式，但是我们大部分时候自增的主键 id 往往是和业务无关的，所以其它索引不可避免，那么有没有办法能避免回表呢？

答案是肯定的，这也就是索引覆盖。

举个例如：SELECT id, uid, user_type FROM t_user where uid1 = 123456

拿用户uid 去查询出想要的三个字段，主键id、用户uid、用户类型。

这个时候大家可能会直接给 uid1 加上索引，但其实不太对，因为以 uid1 为索引的树，能取到的只有主键 id 和 uid1，如果还需要查询出 user_type，就需要根据主键回表查询。

所以最好的办法就是将 uid1 和 user_type 共同组成联合索引，这样每个节点中就会存储这两个字段，同时辅助索引还存储了主键 id，这样就完全满足了所有查询，这也就是索引覆盖，不再需要回表，大大提升了性能。

这也就是为什么没有公司用 SELECT * 这样的语法。因为如果后期因也无需要增加新字段的时候，一些索引覆盖可能就不存在了。

5. Explain

那么对于一条慢 SQL ，或者加完索引的数据，如何分析其是否命中了索引呢？我们可以通过 Explain 进行分析SQL执行计划，例如查询是否使用了关联查询、是否使用了索引、扫描行数等等。

语法很简单，直接在 SQL 语句前加上 EXPLAIN 即可

结果会输出以下列，比较关键的例如 type、key、Extra

列名 解释
id 查询编号
select_type 查询类型：显示本行是简单还是复杂查询
table 涉及到的表
partitions 匹配的分区：查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对于非分区表，该值为 NULL。
type 本次查询的表连接类型
possible_keys 可能选择的索引
key 实际选择的索引
key_len 被选择的索引长度：一般用于判断联合索引有多少列被选择了
ref 与索引比较的列
rows 预计需要扫描的行数，对 InnoDB 来说，这个值是估值，并不一定准确
filtered 按条件筛选的行的百分比
Extra 附加信息

type

type 的值 解释
system 查询对象表只有一行数据，且只能用于 MyISAM 和 Memory 引擎的表，这是最好的情况
const 基于主键或唯一索引查询，最多返回一条结果
eq_ref 表连接时基于主键或非 NULL 的唯一索引完成扫描
ref 基于普通索引的等值查询，或者表间等值连接
fulltext 全文检索
ref_or_null 表连接类型是 ref，但进行扫描的索引列中可能包含 NULL 值
index_merge 利用多个索引
unique_subquery 子查询中使用唯一索引
index_subquery 子查询中使用普通索引
range 利用索引进行范围查询
index 全索引扫描
ALL 全表扫描

possible_keys、key、key_len
possible_keys 是 MySQL 预估会使用的索引（基本忽略就行），最终实际选择的索引直接参考 key 就行了，但是可以通过 key_len 来推测联合索引的 “使用率”。比如联合索引 index(a,b,c)，假设每个索引长度为 1，而 key_len 却是 2，那么可以推测联合索引只用了前两个。

Extra
Extra是最难的，含义也最多，和索引覆盖、排序等都有关系。

Extra 常见的值 解释 例子
Using filesort 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序 explain select * from t1 order by create_time;
Using temporary 需要创建一个临时表来存储结构，通常发生对没有索引的列进行 GROUP BY 时 explain select * from t1 group by create_time;
Using index 使用覆盖索引 explain select a from t1 where a=111;
Using where 使用 where 语句来处理结果 explain select * from t1 where create_time=‘2022-01-18 11:27:15’;
Impossible WHERE 对 where 子句判断的结果总是 false 而不能选择任何数据 explain select * from t1 where 1<0;
Using join buffer (Block Nested Loop) 关联查询中，被驱动表的关联字段没索引 explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);
Using index condition 先条件过滤索引，再查数据 explain select * from t1 where a >900 and a like “%8”;
Select tables optimized away 使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是 explain select max(a) from t1;

6. 联合索引

联合索引就是多个字段共同构成索引，例如 index(a, b, c) 这样的结构，而生产中，我们往往知道它还有最左匹配原则，例如 a 和 b 字段也能命中这个索引。

我们先来看看最左匹配中的一些注意点

6.1 最左匹配原则

6.1.1 WHERE

有一个例子，例如建立联合索引 index(height, score) ，如果我们使用 WHERE socre = xx 是无法命中索引的，因为索引在插入数据的时候就是按照索引顺序排列的，当初向树中插入这条数据的时候，就会根据联合索引的 name 先排序，然后同等的情况才继续用 age 排序。

就例如顺序可能如下：

【175 - 99】 【175 - 99】【180 - 100】【183 - 78】【185 - 83】【185 - 88】

可以看到，只有在身高一致例如都是 185 的情况下，才会继续按照分数 83 88 这样排序。

当 MySQL 解析 SQL 的时候，如果判断不符合 ”最左匹配原则“ 则会直接放弃走索引，选择全表扫描。

如果想要满足索引，就可以用 WHERE name = xx and socre = xx

对于 index(a, b, c) 可以满足的最左匹配有：

  • WHERE a = xx, b = xx, c = xx
  • WHERE a = xx, b = xx（只能命中a, b）
  • WHERE a = xx, c = xx（只能命中a）
    • 例如这种 a 和 c 的情况，但是只能命中到 a 这个索引，所以他就会根据 a 这个字段，先深入到能走到的位置，然后开始拿 b 和 c 的值对数据进行比对。

不满足的例如：WHERE b, c

补充：尽量少使用范围查询，例如 index(a, b, c) 如果使用 WHERE a=xx, b>xx, c=xx 其实命中的只有 a 和 b

6.1.2 ORDER BY

对于排序来说就简单了，因为你创建了索引，例如 index(height, score)，那么你后面插入数据的时候，必然要更新索引，就需要向树中插入数据，而其实插入的顺序是和索引一致的，即先拿 height 排序，相同的 height 再拿 score 排序。

所以如果有 SLECT xx FROM xx WHERE xx=xx ORDER BY height, score

就对于这条语句，其实已经没必要排序了，因为索引影响，插入数据的时候已经完美的按照这个顺序排了。

所以最快的排序就是不用排序，也就是利用索引排序，把对顺序的维护分散到每一次增删改的过程中。

大家可以自行验证，如果没有走索引排序，则会在 EXPLAIN 命令中 Extra 一列出现 filesort，即说明这条 SQL 没有走索引排序，而是利用内存或磁盘自己重新排序。

例如如下这两种情况：

  • ORDER BY score, height（字段顺序不一致）
  • ORDER BY height DESC, score ASC（DESC 和 ASC 混用）

6.1.3 GROUP BY

GROUP BY = 先排序，后归并，对于它的优化，就是尽可能让它也走索引，如果能命中联合索引，就会跳过排序这一步，直接归并。

6.2 使用场景

  • 多条件查询（尽量把区别度高的字段放在前面）
  • 避免回表
  • 索引排序
  • count 统计