看这篇就够了！MySQL 索引知识点超全总结 _MySQL

文章插图

作者：fanili，腾讯 WXG 后台开发工程师

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

什么是索引？在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。（百度百科）
索引的目的是提高查找效率，对数据表的值集合进行了排序，并按照一定数据结构进行了存储。
本文将从一个案例开始，从索引的数据结构、分类、关键概念及如何使用索引提高查找效率等方面对索引知识进行总结。
从一个案例开始现象业务中有个既存的历史 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;

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

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

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

SELECT SQL_CALC_FOUND_ROWS FStatDate,FMerchantId,FVersion,FBatch,FTradeAmount,FTradeCountFROM T_Mch******Stat_1020WHERE FStatDate = 20201020AND FVersion = 0AND FMerchantId > 0ORDER 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,FTradeCountFROM T_Mch******Stat_1020 a1, (SELECT FStatDate, FMerchantId, FVersionFROM T_Mch******Stat_1020WHERE FStatDate = 20201020AND FVersion = 0AND FMerchantId > 0ORDER BY FMerchantId ASC LIMIT 0, 8000 ) a2where a1.FStatDate = a2.FStatDateand a1.FVersion = a2.FVersionand a1.FMerchantId = a2.FMerchantId;

优化关键步骤为：