腾讯 WXG 后台开发工程师对 MySQL 索引知识点总结

知其然知其所以然！本文介绍索引的数据结构、查找算法、常见的索引概念和索引失效场景。

什么是索引？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。（百度百科）

索引的目的是提高查找效率，对数据表的值集合进行了排序，并按照一定数据结构进行了存储。

本文将从一个案例开始，从索引的数据结构、分类、关键概念及如何使用索引提高查找效率等方面对索引知识进行总结。

从一个案例开始

现象

业务中有个既存的历史 SQL 语句在运行时会导致 DB 服务器过载，进而导致相关服务阻塞无法及时完成。CPU 监控曲线如下：

从 DB 的 CPU 使用率曲线可以看到业务运行一直处于“亚健康”状态（1），随着业务的增长随时都可能出现问题。这种问题（2）在 11 月 11 日凌晨出现，当时 DB CPU 一直处于 100%高负荷状态，且存在大量的慢查询语句。最终以杀死进程降低 DB 负载、减少业务进程（3）的方式恢复业务。

在 11 月 11 日下午，对该业务的 SQL 语句进行了优化，优化的效果如下。业务运行时的 CPU 使用率峰值有很大的降低（对比图 2 的 1，2，3 可见）；慢查询语句几乎在监控曲线上也无法明显观察到（对比图 3 的 1，2，3 可见）。

分析

表结构

CREATE TABLE T_Mch******Stat (`FStatDate` int unsigned NOT NULL DEFAULT 19700101 COMMENT '统计日期',
`FMerchantId` bigint unsigned NOT NULL DEFAULT 0 COMMENT '商户ID',
`FVersion` int unsigned NOT NULL DEFAULT 0 COMMENT '数据版本号',
`FBatch` bigint unsigned NOT NULL DEFAULT 0 COMMENT '统计批次',
`FTradeAmount` bigint NOT NULL DEFAULT 0 COMMENT '交易金额'
PRIMARY KEY (`FStatDate`,`FMerchantId`,`FVersion`),
INDEX i_FStatDate_FVersion (`FStatDate`,`FVersion`))
DEFAULT CHARSET = utf8 ENGINE = InnoDB;

从建表语句可以知道该表有两个索引：

1. 主键索引，是一个组合索引，由字段 FStateDate、FMerchantId 和 FVersion 组成；
2. 普通索引，是一个组合索引，由字段 FStateDate 和 FVersion 组成；

优化前的 SQL 语句（做了部分裁剪）A：

SELECT SQL_CALC_FOUND_ROWS FStatDate,
    FMerchantId,
    FVersion,
    FBatch,
    FTradeAmount,
    FTradeCount
FROM T_Mch******Stat_1020
WHERE FStatDate = 20201020
    AND FVersion = 0
    AND FMerchantId > 0
ORDER BY FMerchantId ASC LIMIT 0, 8000

对该 SQL 进行 explain 得到如下结果，Extra 字段的值为 using where，说明并没有使用到索引。

优化后的 SQL 语句（做了部分裁剪）B：

SELECT SQL_CALC_FOUND_ROWS a1.FStatDate,
    a1.FMerchantId,
    a1.FVersion,
    FBatch,
    FTradeAmount,
    FTradeCount
FROM T_Mch******Stat_1020 a1, (
    SELECT FStatDate, FMerchantId, FVersion
    FROM T_Mch******Stat_1020
    WHERE FStatDate = 20201020
        AND FVersion = 0
        AND FMerchantId > 0
        ORDER BY FMerchantId ASC LIMIT 0, 8000 ) a2
where a1.FStatDate = a2.FStatDate
    and a1.FVersion = a2.FVersion
    and a1.FMerchantId = a2.FMerchantId;

优化关键步骤为：

• 新增一个子查询，select 字段只有主键字段；

该 SQL 的 explain 结果如下，子查询语句使用了索引，而最终在线上运行结果也证明了优化效果显著。

疑问

优化后的 SQL 语句 B 比原来的 SQL 语句 A 复杂的多（子查询，临时表关联等），怎么效率会提升，违反直觉？有三个疑问：

1. SQL 语句 A 的查询条件字段都在主键中，主键索引用到了没？
2. SQL 语句 B 的子查询为什么能够用到索引？
3. 前后两条语句执行流程的差异是什么？

索引的数据结构

在 MySQL 中，索引是在存储引擎层实现的，而不同的存储引擎根据其业务场景特点会有不同的实现方式。这里会先介绍我们常见的有序数组、Hash 和搜索树，最后看下 Innodb 的引擎支持的 B+树。

有序数组

数组是在任何一本数据结构和算法的书籍都会介绍到的一种重要的数据结构。有序数组如其字面意思，以 Key 的递增顺序保存数据在数组中。非常适合等值查询和范围查询。

ID：1	ID：2	......	ID:N

在 ID 值没有重复的情况下，上述数组按照 ID 的递增顺序进行保存。这个时候如果需要查询特定 ID 值的 name&#x