1. 什么是索引#

想象一下要从一本书中查找某个章节，如果这本书没有目录，那么只能从头到尾翻页，直到找到为止。如果这本书有目录，那么可以根据目录快速定位到要查找的章节。

对应到数据库中，如果没有索引，检索时只能从头到尾扫描，复杂度为 O(N)，如果数据量很大，查询速度会非常慢。

一个很容易想到的优化是使用平衡二叉搜索树，将数据按照某个字段构造搜索树，可以将复杂度降低到 O(logN)。

但二叉搜索树有一个缺点，即当数据量大的时候，树会变得很高，1024 条数据就会达到 10 层。在跳转节点时，会产生磁盘 IO 操作，而磁盘 IO 是很慢的。

面对这种情况，可以使用 B 树 降低树的高度，进而减少磁盘 IO 操作。

B 树其实就是多叉搜索树的一种实现，每个节点有多个子节点。虽然多个子节点会导致在单个节点上需要遍历，但对于数据库来说磁盘 IO 的时间远大于遍历消耗的时间。

B 树有个特性和二叉搜索树是一致的，即它会将数据分布在所有节点上，当查找到包含目标 key 的节点时就停止查找，直接从节点取出数据。这个特性看似很好，但在数据库的场景下，如果节点要携带数据，则需要额外储存指针指向数据。在 MySQL 中，一个指针至少也需要占据 6B，而一个 int 索引本身才占据 4B，额外储存一个指针会导致节点变大，减少节点可用的分叉数，可能还会触发额外的 IO 操作。

节点的大小
为什么说中的元素变大会减少节点可用的分叉数？
因为节点的大小是根据分页大小来决定的，目的是是做到一次 IO 操作读取一个节点。由于 MySQL 中一个分页的大小一般是 16KB，因此节点的大小也不超过 16KB。
用 INT 字段作为索引时，一个元素中 4B 存 key，6B 存指针，总共 10B，一个节点最多可以分 1600 个叉。
如果增加节点内部元素的大小，自然会使得有限的空间内一个节点能储存的元素变少，进而使得能指向的子节点变少，需要跳更多个节点才能到达叶子节点，最终导致 IO 次数增加。

因此，MySQL 选择了 B+ 树，和 B 树相比，B+ 树将数据全部储存在叶子节点上。

虽然这样会导致每次查找都要到叶子节点，但由于减小了中间节点的大小，可以增加一个节点能储存的分叉数，避免触发额外的 IO 操作。

同时，由于数据库通常更强调稳定性，而 B+树的最差读取时间是优于 B 树的，因此从稳定性考虑也应当使用 B+ 树。

最后，数据储存在叶子节点，且每个叶子节点还指向前后叶子节点，因此在需要顺序遍历（如范围查询）的使用场景下会显著优于 B 树。

2. 创建索引的原则#

索引虽然好，但除了主键索引，其他索引使用时容易产生额外的消耗，不能无脑给所有表所有字段都添加索引。

以下是创建索引时要注意的一些原则。

首先，由于 B+ 树的特性，数据都在主键索引树中，使用二级索引时一旦出现回表，就会导致额外的 IO 操作。如果表本身很小，一次 IO 操作的耗时可能就会超过遍历整个表的耗时。
例如假设磁盘的寻道时间为 5ms，转速为 120/s，旋转延迟就是 1 / 120 / 2 = 4.17ms，即一次磁盘 IO 的时间约等于 5+4.17 = 9ms 左右。再假设 CPU 每秒可以执行 5 亿条指令，执行一次 IO 的时间就可以执行 40 万条指令。
因此，至少在数据达到十万级别时建立索引比较合适，对于性能较高的 CPU，在到达百万级再建立索引也是可以的。
其次，对于同一个表不同的字段，也要分别考虑是否要建立索引。索引虽然可以加速查找，但也会增加插入和删除的消耗，如果某个字段很少作为查询条件，又经常变动，那添加索引反而可能降低效率。
一般来说，会针对作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
第三，对于区分度很低的字段，建立索引没什么意义。例如假设有个省会字段，总共就 23 种值，而表的数据可能有几百万，在这种字段上面单独建立索引就没什么意义，最好是配合其他字段建立联合索引。
区分度最高，效率最高的索引当然是唯一索引，只要有条件，就尽量给索引添加 UNIQUE 限定。对于原本不唯一的字段，通过建立联合索引就有可能变成唯一索引。例如假设有个 tb_permission，其中有 user 和 permission 字段，这两个字段都可能重复，但通过建立联合索引，由于一个用户只能拥有一种权限一次，因此就变成唯一的了。
第四，对于较长的字符串类型的字段，最好不要作为索引。如果一定要建立索引，需要结合具体业务，针对字段特点建立前缀索引。
第五，为了尽量做到覆盖索引，避免回表，尽量避免建立单列索引。可以根据业务需求建立联合索引，使得查询时可以从所以中直接获取结果。
当然，也不是无脑为所有字段建立联合索引就好，联合索引毕竟比普通索引更大，肯定会有些代价，还是要结合具体业务为关联紧密的字段建立联合索引。
最后，如果字段不能储存 NULL 值，应使用 NOT NULL 进行约束。当优化器知道字段不会包含 NULL 值时，可以更好的进行优化。

3. 如何定位慢查询#

慢查询是指执行时间较长的 SQL 语句，具体多慢算慢查询由实际业务场景决定，例如美团将执行超过 100ms 的查询定义为慢查询。

要定位慢查询，最直接的方法是使用调试工具如 Arthas、运维工具如 Prometheus, Skywalking 测试接口的响应情况，可以直观的看到每个接口的执行时间。

MySQL 本身也自带了慢查询的记录功能，默认是关闭的，开启需要在配置文件中将 slow_query_log 设为 1。同时需要配置 long_query_time 参数，表示记录的阈值，单位是秒，执行时间超过该值的查询会被记录到 [hostname]-slow.log 中。还可以配置 min_examined_row_limit，记录扫描记录数不小于该值的 SQL。

相比于使用工具，使用 MySQL 的日志的好处是可以看到关于慢查询的更详细的执行信息，而且由于通常只会在测试环境中开启慢查询记录功能，可以把阈值设定的比较低，以及时发现执行效率偏低的 SQL。对于一些重要的业务，甚至可以把阈值设成 0，跟踪所有 SQL 的执行情况。

但由于慢查询日志需要消耗额外的系统资源，通常不会在线上开启，即使要开启也会把阈值设的比较高，只能记录极端情况。因此如果要在线上定位慢查询，还是要使用工具。

最后，如果慢查询正在执行，已经导致数据库负载偏高了，但由于慢查询还没执行完，因此慢查询日志中还看不到相关语句。这种情况下要快速定位慢查询，可以通过 show processlist 命令查看当前正在运行的线程，输出中 Time 表示已经运行了多久，Info 表示具体的 SQL 语句内容。

4. 如何分析慢查询#

MySQL 提供了 EXPLAIN 命令显示 SQL 的详细执行计划，其中关于查询效率的重要信息包括：

字段名	含义
type	连接的类型
possible_key	可能会用到的索引
key	实际使用到的索引
key_len	使用到的索引的长度
ref	与索引进行比较的列
rows	扫描的行数

其中 type 字段表示查询优化器对 SQL 语句的优化力度，基本取决于 SQL 语句中对索引的使用情况，可以说是最重要的字段。

type 字段共有 12 种可能的值，优化力度从高到低。

值	含义
system	只用到了一个表，且表中只有一行数据，是`const`的特殊情况
const	只用到了一个表，且查询结果只有一行，即只使用到了主键索引或唯一索引
eq_ref	用到了多个表，且查询结果只有一行，即只使用到了主键索引或唯一索引
ref	用到了多个表，且查询结果大于等于一行，即使用到了非 UNIQUE 索引或联合索引的前导列
fulltext	使用到了 fulltext 索引
ref_or_null	类似`ref`,但条件中包含`IS NONE`，常见于子查询中
index_merge	使用到了索引合并优化，即当条件中存在同一索引的多个范围查询时（包括`IN`, `LIKE`, `BETWEEN` 和用 `OR` 连接的条件），优化器会对该索引的范围取交集
unique_subquery	子查询的结果是主键或唯一索引，且主查询用`IN`表达式处理子查询的结果时，会进行此优化
index_subquery	类似`unique_subquery`，区别在于子查询的结果是索引但不是主键或唯一索引
range	使用索引进行范围查询，且范围是常量
index	全表扫描，但扫描的是索引树，不需要回表
all	全表扫描，没有使用到索引，效率最烂

关于 EXPLAIN 更详细的解释和案例可以看 MySQL 的官方文档。

5. 如何优化慢查询#

检查索引失效
先看查询的表是否建立了索引，如果没建立就先建立索引。
如果建立了索引但没命中，说明发生了索引失效。可以尝试修改 SQL 语句的条件，使索引生效。具体做法参照下一小节内容。
覆盖索引
当要添加多个索引时，由于一个索引树只能用于一个索引，为了不把数据全部数值一遍，会采用 二级索引 的形式储存非主键索引。
具体来说，就是在主键索引树引储存数据，在其他索引树储存主键。如果表没有主键，MySQL 会创建一个隐藏字段作为主键。
这样的主键索引树称为 聚簇索引，而其他索引树称为 二级索引。
但这样使用二级索引时显然就需要进行两次搜索，先根据二级索引找到主键，在用主键在聚簇索引中找到实际数据。这种现象被称作回表，回表导致了二级索引的查询效率不如主键索引。
可以思考一下，所有使用非主键索引的情况都要回表吗？
还是拿书作为例子，可以想象一下，如果你的目标本来就不是查询某章节的内容，而是查询某章节位于第几页，那只需要在目录中查找对应的页码即可，不需要实际执行翻阅操作。
也就是说，如果索引中已经包含了所需要的数据，那么就可以避免回表，只需要进行一次搜索即可，这种情况称之为 覆盖索引。
当然，不是所有情况都可以避免回表，但了解回表的现象后应当避免无脑使用 SELECT * 查询数据，尽可能做到覆盖索引。
尽量避免子查询
由于 MySQL 的优化器对于子查询的处理能力比较弱，且执行中需要创建临时表，因此不建议使用子查询，可以改写为 JOIN 形式。
例如
```
SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = 'lry');
```
可以改写为
```
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.name = 'lry';
```
尽量使用 UNION ALL 而不是 UNION
UNION 会自动对结果进行去重判断，如果你确定查找的数据中没有重复的，或是不在意重复的数据，可以使用 UNION ALL，效率会更高。
深分页优化
在使用 LIMIT 进行分页查询时，如果查询的 OFFSET 很大，会导致效率非常低。
这是因为 MySQL 在执行 LIMIT 时，会查询出所有满足条件的数据，然后从中取出 OFFSET 后指定的数据。例如如果写的是 LIMIT 90000000, 10，则会查询出 90000010 条数据，然后抛弃前 90000000 条数据。
对于上面这个例子，可以改写为
```
SELECT * FROM t1 WHERE id > 90000000 ORDER BY id LIMIT 10;
```
优化 JOIN 语句
INNER JOIN 会自动进行优化，将小表放在左边，大表放在右边，做到小表驱动。而 LEFT JOIN 和 RIGHT JOIN 由于左右顺序会影响结果，无法自动优化。
因此尽量避免使用 LEFT JOIN 和 RIGHT JOIN，如果一定要用，尽量做到小表驱动，否则效率会很低。
小表驱动
JOIN 的本质是嵌套循环，驱动指的是将哪个表作为循环的外层。
假设有一个小表有 100 条数据，一个大表有 10000 条数据，假设小表的索引树高 2，大表的索引树高 3。
当以小表驱动时，外层循环 100 次，内层循环 3 次，共 300 次查询。
当以大表驱动时，外层循环 10000 次，内层循环 2 次，共 20000 次查询。
由此可见，小表驱动的查询效率更高。
主从复制，分库分表
如果 SQL 语句已经优化到极限了，但数据量实在是太大，则只能考虑横向扩容。
这部分的内容放到后续文章中进行说明。

6. 如何避免索引失效#

以下是一些常见的导致索引失效的原因：

违反左前缀法则
对于联合索引，如果查询条件中只使用了部分索引列，必须保证用到的列为左前缀列，否则索引不会生效。如果跳跃了部分索引列，只有左前缀部分会生效。
例如，对于联合索引(a, b, c)，如果查询条件是b = 1，则索引不会生效。如果查询条件是a = 1 AND b = 1，则索引生效。如果查询条件是a = 1 AND c = 1，则只有a = 1部分生效。
范围查询右边的列不会使用索引
例如，对于联合索引(a, b), 如果查询条件是a > 1 AND b = 1, 则b = 1不会生效。如果查询条件是a = 1 AND b > 1，则索引完整生效。
对文本索引使用左模糊匹配
在对文本类索引进行模糊匹配时，如果使用的是左模糊匹配，例如 LIKE '%林'，则索引不会生效。

TIP
以上三点其实可以归结于 B+ 树按大小顺序储存数据，而联合索引会从左到右进行大小比较，例如对于联合索引(a, b, c)，会依次比较 a, b, c 的大小，因此只有左前缀索引在 B+ 树中是连续储存的。
如果索引不是左前缀，例如b = 1，则需要查找所有形如(*, 1)的位置。
如果索引不连续，例如a = 1 AND c = 2，则要遍历所有形如(1, *, 2)的位置。由于中间不连续，c = 2无法参与到索引定位中，只能遍历(1, *, *)查找c = 2的位置。
对于范围查询，一旦有一列为范围查询，同样会导致后续列的不连续，进而导致后续列的索引失效。
对于文本索引也是同理，文本索引其实可以视为一个联合索引，只不过每个字段是一个字符。

对索引列进行计算
无论是使用 MySQL 内置的函数还是自定义表达式，都会导致索引失效。
这个应该挺显而易见的，计算后的东西显然和索引树里储存的不是同一个东西，自然无法走索引。
用文本类索引和数字比较
将一个文本类型索引和整数进行比较，例如phone = 1300000001，会导致索引失效，但将整数类索引和文本进行比较，例如age = '18'不会失效。
原因在与 MySQL 只会将文本类的值转换为数字，而不会将数字类的值转换为文本。文本转换为数字后，自然可以和整数类索引进行比较；而整数无法自动转为文本，则无法和文本类索引进行比较。
那可能有人会想，如果给文本类索引使用 CAST，例如CAST(phone AS unsigned int)，将其转换为数字，是不是就可以比较了？
如果你觉得这个想法没毛病，那建议看看上一条失效情况。调用 CAST 显然是对索引列进行了计算，也会导致索引失效。因此，只能对数字调用索引，将其转换为文本，例如phone = CAST(1300000001 AS TEXT)，不过正常来说不用这么麻烦，给数字加上引号即可。
在用 OR 连接的条件中出现非索引列
例如，如果 age 没有索引，则id = 1 OR age = 18会导致索引失效。因为OR需要检索出所有满足条件的数据，即使已经找到 id = 1 的结果，仍然需要继续查找 age = 18 的结果。

以上就是常见的导致索引失效的情况，一旦没有可用的索引，查询效率会严重下降。要优化慢查询，最基础的自然就是想办法避免出现未命中索引的情况。

如果确定相关索引都已经建立，可以根据 EXPLAIN 的输出，尝试优化 SQL 语句。可以根据 key 和 key_len 来判断是否命中了索引，如果命中了索引，还要注意是否是最优的索引，可以通过 type 字段的信息来判断。

当然，不可能在所有情况下都可以优化到理想情况，通常只需要避免出现 type 为 index 或 all 的查询即可。