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

explain select * from employees where hire_date > '1999-06-02';

文章插图

无法使用 B+索引快速查找
B+树索引支持快速查询的基本要素是因为其索引键值是有序存储的，从左到右由小到大，这样就可以在每个层级的节点中快速查并进入下一层级，最终在叶子节点找到对应的值。使用函数会使得 MySQL 无法使用索引进行快速查询，因为对索引字段做函数操作会破坏索引值的有序性，所以优化器选择不使用索引。而查询条件类型不一致其实也是同样的情况，因为其使用了隐式类型转换* 。

模糊匹配和不使用组合索引的首字段作为查询条件均是无法快速定位索引位置从而导致无法使用索引。模糊匹配当查询条件是 lwhere A ike 'a%'，a 是 A 的最左前缀时是可能用上索引的（最左匹配），是否用上最终还是依赖优化器对查询数据量的评估。
回到初始的案例让我们回到文章初的案例，尝试回答下当时提出的 3 个问题。

-- A语句SELECT FStatDate, FMerchantId, FVersion, FBatch, FTradeAmount, FTradeCount FROM T_Mch******Stat_1020 WHERE FStatDate = 20201020AND FVersion = 0AND FMerchantId > 0 ORDER BY FMerchantId ASC LIMIT 0, 8000;-- 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;

SQL 语句 A 的查询条件字段都在主键中，主键索引用到了没？
主键索引其实是有被使用的：索引的范围查询，只是其需要逐条读取和解析所有记录才导致慢查询。
SQL 语句 B 的子查询为什么能够用到索引？

前文中我们介绍了聚集索引，其索引键值就是主键。
两条 SQL 语句的不同之处在于 B 语句的子查询语句的 Select 字段都包含在主键字段中，而 A 语句还有其它字段（例如 FBatch 和 FTradeAmount 等）。这种情况下只凭主键索引的键值就能满足 B 语句的字段要求；A 语句则需要逐条取整行记录进行解析。

前后两条语句执行流程的差异是什么？

SQL 语句 A 的执行过程：

逐条扫描索引表并比较查询条件
遇到符合查询条件的则读取整行数据返回
回到 a 步骤，直至完成所有索引记录的比较
对返回的所有符合条件的记录（完整的记录）进行排序
选取前 8000 条数据返回

SQL 语句 B 的执行过程：

逐条扫描索引表并比较查询条件
遇到符合查询条件的则从索引键中取相关字段值返回
回到 a 步骤，直至完成所有索引记录的比较
对返回的所有符合条件的记录（每条记录只有 3 个主键）进行排序
选取前 8000 条数据返回形成临时表
关联临时表与主表，使用主键相等比较查询 8000 条数据

对比两个 SQL 语句的执行过程，可以发现差异点集中在步骤 2 和步骤 4 。在步骤 2 中 SQL 语句 A 需要随机读取整行数据并解析非常耗资源；步骤 4 涉及 MySQL 的排序算法，这里也会对执行效率有影响，排序效果上看 SQL 语句 B 比 SQL 语句 A 好。

文章插图

名词解释

主键索引

顾名思义该类索引由表的主键组成，从左到右由小到大排序。一个 Innodb 存储表只有一张主键索引表（聚集索引）。

普通索引

最为平常的一种索引，没有特别限制。

唯一索引

该索引的字段不能有相同值，但允许有空值。

组合索引

由多列字段组合而成的索引，往往是为了提升查询效率而设置。
总结在文章开始时介绍了常见的几种索引数据结构，适合静态数据的有序数组、适合 KV 结构的哈希索引及兼顾查询及插入性能的搜索二叉树；然后介绍了 Innodb 的常见索引实现方式 B+树及 Select 语句使用 B+树索引查找记录的执行过程，在这个部分我们了解了几个关键的概念，回表、覆盖索引、最左匹配、索引下推和 MMR；之后还总结了索引的失效场景及背后的原因。最后，我们回到最初的案例，分析出优化前后 SQL 语句在使用索引的差异，进而导致执行效率的差异。