【MySQL】高性能高可用设计实战-索引篇

一、组合索引提升查询性能

1、什么是组合索引

组合索引是由多个列组成的B+树索引。这与我们前面介绍的B+树索引的原理完全相同，只是它以前对一列进行排序，现在对多列进行排序。组合索引可以是主键索引或辅助索引，没有限制。

组合索引本质上是B+树索引。它只从一个键值更改为多个键值。

当选择复合索引时，MySQL 是计算 key_len，以了解有效索引长度对于索引优化。

key_len 计算规则，key_Len 表示用于获取结果集的选定索引的长度[字节数]，不包括order by，也就是说，如果order by也使用索引，则key_Len不计算在内。（key_Len在第三节MySQL选择索引的原理中详解）

key_len计算规则从两个方面考虑：一个是索引字段的数据类型，另一个是表和字段使用的字符集。

2、组合索引的陷阱

假设我们设置下面sql的索引，需要注意 (​

​LOG_ID​

​, ​

​SUB_ODR_ID​

​) 和 (​

​SUB_ODR_ID​

​, ​

​LOG_ID​

​) 这样的组合索引，其排序结果是完全不一样的。

INDEX `UNQ_LOG_SUBODR_ID`(`LOG_ID`, `SUB_ODR_ID`) USING BTREE,      

对组合索引 (​

​LOG_ID​

​, ​

​SUB_ODR_ID​

​) 来说，因为其对列 log_id、sub_odr_id  做了排序，所以它可以对下面两个查询进行优化，但是下面的 SQL 无法使用组合索引 (​

​LOG_ID​

​, ​

​SUB_ODR_ID​

​) ，因为 (​

​LOG_ID​

​, ​

​SUB_ODR_ID​

​) 排序并不能推出 (​

​SUB_ODR_ID​

​, ​

​LOG_ID​

​) 排序，如下代码所示：

-- 可以执行联合索引
SELECT * FROM table WHERE LOG_ID = ?
SELECT * FROM table WHERE LOG_ID = ？ AND SUB_ODR_ID = ？

-- 不可执行索引
SELECT * FROM table WHERE SUB_ODR_ID = ?

-- 可以执行联合索引
SELECT * FROM table WHERE LOG_ID = ？ ORDER BY SUB_ODR_ID DESC

-- 不可执行索引
SELECT * FROM table WHERE SUB_ODR_ID = ？ ORDER BY LOG_ID DESC
复制代码      

3、组合索引设计实战

3.1 表的时间列建立联合索引

当我们ORDER BY create_date DESC ，如果时间字段没有索引，会首先进行 Index lookup 索引查询，然后进行 Sort 排序，最终得到结果。但是where 后查询的列有创建索引，所以上述SQL语句的执行不会特别慢。

然而，在大规模并发业务访问下，每次SQL执行都需要排序，这将对业务性能产生非常显著的影响，例如较高的CPU负载和较低的QPS。

要解决这个问题，最好的方法是：当检索结果时，字段 create_date 排序，因此不需要额外的排序。

建议，我们在表上创建新的组合索引 idx__create_date，对字段（ ，create_date）

3.2 防止回表

上一篇文章已经讲了回表的概念，即SQL需要通过二级索引查询获得主键值，然后根据主键值搜索主键索引，最后找到完整的数据。

但是，由于组合索引的叶节点包含索引键值和主键值，因此如果查询的字段位于组合索引的叶子节点中，则可以直接返回结果而不返回表。这种通过组合索引避免返回表的优化技术也称为覆盖索引。

回表次数是根据查询条件判断的

如果查询单条数据，没有覆盖索引的话，只回表一次。

如果查询数据量大的话，比如查询200条数据，就需要回表200次。

若表的记录数越多，需要回表的次数也就越多，通过索引覆盖技术性能的提升也就越明显。

二、最左匹配原则

通过 key_len 计算可以帮助我们理解索引的最左前缀匹配原理。

最左边的前缀匹配原则意味着，当使用B+树联合索引进行数据检索时，MySQL 优化器将读取过滤条件（谓词），并根据联合索引字段的创建顺序将其自左向右匹配，直到遇到范围查询或非等效查询后停止匹配。 将不使用此字段后面的索引列。

这时计算 key_len 可以分析联邦索引实际使用了哪些索引列。

B+ 树可以利用索引的“最左前缀”，来定位记录。

基于上面对最左前缀索引的说明，在建立联合索引的时候，如何安排索引内的字段顺序？

评估标准是，索引的复用能力。

因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

三、MySQL选择索引的原理

首先，如何在创建索引后确认SQL语句是否被索引？

创建索引后，您可以通过查看SQL语句的执行计划（explain）来了解SQL语句是否被索引。执行计划侧重于与索引相关的关键项，包括 type、possible_keys、key、key_len、ref、Extra 等。

possible_Keys表示可能用于查询的索引

key表示实际使用的索引

key_len表示使用的索引字段的长度。

Extra 显示 use index 时就表示该索引是覆盖索引，表示该索引是覆盖索引，通常，性能排序的结果是usd index>use where>use filsort

当选择复合索引时，MySQL 是计算 key_len，以了解有效索引长度对于索引优化。

key_len 计算规则，key_Len 表示用于获取结果集的选定索引的长度[字节数]，不包括order by，也就是说，如果order by也使用索引，则key_Len不计算在内。

key_len计算规则从两个方面考虑：一个是索引字段的数据类型，另一个是表和字段使用的字符集。

四、企业MySQL创建索引设计规范

1、每个公司的命名标准都是统一的，首先应该遵守公司DBA规定。

2、表必须具有主键。建议使用 UNSIGNED 自增列作为主键。

3、考虑到索引维护的成本，一般情况下单个索引中的字段数不超出5个。

当没有为表设置主键时，InnoDB将默认设置隐藏的主键列。表不方便定位数据，也会增加MySQL的运维成本（例如主从复制效率严重受损）。

4、唯一索引由三个或更少的字段组成，不建议设置为主键。

5、由于索引维护需要成本，建议删除冗余索引和重复索引。

添加新索引时，应优先考虑基于现有索引进行重建，如（a，b，c）和（a），这些索引是冗余索引，占用磁盘空间，可以删除。

6、查询关联表时，JOIN 列的数据类型必须相同，并且必须创建索引。

7、不在低区分度的列增加索引，选择具有较大辨别力（选择率）的列以构建索引。在复合索引中，具有高分辨力（选择率）的字段被放在第一位。

8、合理使用覆盖索引以减少IO并避免排序。

因为如果SQL需要查询未包含在辅助索引中的数据列，需要首先通过辅助索引找到主键值，然后通过主键查询返回表以查找其他数据列（即返回表查询）。需要查询两次，覆盖索引可以直接从索引中获取查询所需的所有数据，并避免在后表中进行二次查找，节省了IO，效率更高。

五、分析 MySQL 索引优化过程

1、查找由不适当或缺少索引导致的慢sql

通常，在创建数据库或表时，需要将与业务操作相关的SQL提交给DBA审批。同时可以进行慢查询日志分析，抓取慢运行的SQL进行分析，慢查询监控系统用于慢查询收集和分析。在慢查询分析中，执行最差的参数输入，分析了SQL语句谓词的过滤因子、基数、选择率和SQL回表情况。

2、设计索引

索引设计的目标是使查询语句运行得足够快，同时也使表和索引维护足够快。例如，使用与业务无关的自增加字段作为主键，以降低索引维护（如页面拆分和页面合并）的成本，加快性能。

3、创建索引策略

优先为搜索列、排序列和分组列创建索引，必要时添加查询列以创建覆盖索引；

索引应为较小的数据类型（整数类型优于字符类型），字符串可考虑前缀索引；