MySQL 索引全面解析：原理、类型、优化

MySQL 索引是数据库性能优化的核心工具，其本质是帮助 MySQL 高效获取数据的排好序的数据结构。通过将随机 I/O 转化为顺序 I/O，索引能将查询复杂度从全表扫描的 O (n) 降至 O (log n) 级别，大幅减少磁盘 I/O 对性能的损耗。

从底层实现来看，MySQL 主流存储引擎（InnoDB/MyISAM）均采用 B+Tree 作为索引的通用存储结构 —— 这是针对磁盘 I/O 和范围查询场景的最优选择，而哈希索引仅在特定场景下被 InnoDB 自适应采用。从使用视角看，索引分为聚焦于数据结构的逻辑分类（如 B + 树索引、哈希索引）以及聚焦于业务使用场景的物理分类（如聚簇索引、二级索引）。

对于开发者而言，学习 MySQL 索引的核心目标是掌握三方面能力：合理设计索引以规避查询性能瓶颈、有效诊断索引失效问题、针对业务场景持续优化索引性能。对应的知识体系可分为四大核心模块，其完整的学习路径与各模块核心关联逻辑如下：

基础原理层：理解索引的本质、为什么选择 B+Tree 而非其他数据结构，以及 InnoDB 和 MyISAM 这两大主流存储引擎的索引实现差异 —— 这是后续所有索引设计、优化操作的逻辑支撑。
类型特性层：掌握 B+Tree 索引、哈希索引、聚簇索引、二级索引等不同索引类型的特性、适用场景及限制条件，这是精准匹配业务查询场景的前提。
设计与优化层：学习并应用索引创建的黄金原则、针对不同场景的优化策略，以及导致索引失效的典型场景和规避方法。
诊断分析层：学会使用 EXPLAIN、SHOW ENGINE INNODB STATUS 等工具分析索引的实际使用效率，验证索引的生效状态及实际收益。

1 索引基础原理与数据结构

要真正理解索引的使用限制和优化逻辑，必须先从底层数据结构的维度，弄清楚 “MySQL 为什么选择 B+Tree 作为核心索引存储结构”—— 这是索引最核心的设计逻辑，也是绝大多数索引使用规则的底层依据。

1.1 索引的本质与核心价值

索引的官方定义非常精炼：是帮助 MySQL 高效获取数据的有序数据结构。它是数据字典之外，数据库为优化查询性能额外维护的一组数据结构，这组数据结构以引用指向实际数据的方式，让数据库系统可以通过 “缩小数据检索范围” 的核心逻辑提升查询效率。

这里的关键在 “有序”—— 有序键值支持二分定位与范围扫描，这也是索引能大幅降低查询复杂度的根本原因。数据库的性能瓶颈 90% 以上来自磁盘 I/O 操作，而索引的核心价值正是将查询的时间复杂度从全表扫描的 O (n) 降低到 O (log n)，极大减少磁盘访问次数。

索引是有维护成本的：每次 INSERT/UPDATE/DELETE 可能触发索引页分裂、合并与二级索引主键指针更新。只有命中高频查询路径时，收益才覆盖成本。

1.2 为什么是 B+Tree？

索引的底层可选择的数据结构有很多种，比如数组、链表、哈希表、红黑树、B 树等，但 MySQL 最终选择 B+Tree 作为主流存储引擎的索引实现，这是基于磁盘存储特性和主流查询场景的综合技术选型 —— 每一种备选数据结构都有无法规避的短板，只有 B+Tree 完美适配了数据库的核心查询需求。接下来通过对比常用数据结构的特性，分析这一技术选型的背后逻辑。

1.2.1 数据结构选型对比

为了理解 B+Tree 的设计优势，我们需要先排除那些不适合作为数据库索引底层结构的选项，这一过程也能反向佐证 B+Tree 的适配性。

数组：数组的连续存储特性让它具备高效的随机访问能力，但这只是内存级别的优势 —— 在磁盘上维护连续的大空间本身就不可行；更关键的是，其插入、删除操作需要移动大量元素，这会产生极高的维护开销，完全无法适配数据库高并发写入的场景。
链表：链表的随机访问效率极低，必须从头节点开始遍历到目标节点，才能获取后续节点的引用，这会直接退化到全表扫描的 O (n) 时间复杂度，与索引的核心设计目标完全相悖。
哈希表：哈希表在等值查询场景下能达到 O (1) 的时间复杂度，但天然无法支持范围查询 —— 对于 “查找某个区间内的记录” 这类数据库中最主流的场景，哈希表的效率会直接退化到 O (n)，这与数据库的核心查询需求严重冲突。
红黑树：作为一种平衡二叉搜索树，红黑树的查询时间复杂度是 O (log n)，但这只是内存级别的优势；在磁盘存储场景中，随着数据量增长，红黑树的高度会快速增加，这意味着需要更多次磁盘 I/O 才能定位到数据，无法支撑高效的查询，因此也不适合作为索引的存储结构。
B 树：B 树是一种平衡多路搜索树，它将数据存储在所有节点中，这看起来能更快定位数据，但反而严重影响了范围查询的效率：范围查询需要在不同层级的节点间反复跳转，这会产生大量随机 I/O，性能表现远不如顺序遍历磁盘页。

通过上述对比可以发现，只有 B+Tree 的设计能完美适配数据库基于磁盘存储的场景，以及兼顾等值查询、范围查询两大核心查询需求。

1.2.2 B+Tree 的核心优化细节

B+Tree 是 B 树的专属变种，它在 B 树的基础上做了三处关键改进，每一处都是为了直接提升数据库查询的核心性能，且完美适配磁盘存储的特性。这些改进是：

非叶子节点仅存储索引键：这是 B + 树比 B 树更矮的核心设计逻辑。在 B + 树中，非叶子节点不存储任何完整行数据，仅存储索引键和指向子节点的指针；这意味着每个节点能容纳更多的索引键，树的出度（即一个节点能拥有的子节点数量）将大幅提升。树的高度因此被极大降低 —— 在 InnoDB 中，通常只需要 2-3 层树高，就可以容纳数百万甚至上千万级别的数据。这样一来，查询时的磁盘 I/O 次数被控制在 2-3 次，这是提升查询性能的关键，因为磁盘 I/O 的耗时是内存访问的上千倍。
叶子节点存储所有数据并形成有序双向链表：在 B + 树中，所有完整行数据（或二级索引对应的主键值）都统一存储在叶子节点；同时，所有叶子节点被串联成有序的双向链表。这一设计意味着，只要定位到第一个符合条件的叶子节点，后续所有符合条件的记录只需要顺着链表顺序遍历即可获取，完美适配数据库中最频繁的范围查询场景。相比 B 树在范围查询时需要在各层节点间跳转的设计，这一优化将大量随机 I/O 转化为顺序 I/O，性能提升幅度显著。
数据存储层级统一：B + 树的所有叶子节点都位于树的同一层，这意味着任何查询的磁盘访问次数都是稳定的 —— 不会因为查询目标的位置不同，而产生差异极大的 I/O 开销；而 B 树因为数据分散在不同节点，查询性能并不稳定。

1.3 存储引擎的索引实现差异

1.3.1 MyISAM 的非聚簇索引架构

MyISAM 采用非聚簇索引架构，其最核心的特点是索引数据与行数据完全分离，存储在不同的磁盘文件中：

采用该架构的表，其索引数据会存储在名为 表名.MYI 的文件中，而行数据则存储在名为 表名.MYD 的文件中。
无论是主键索引还是普通索引，其叶子节点存储的都是行数据在 MYD 文件中的物理磁盘地址 —— 而非实际的行数据或主键值。
基于这一架构的查询过程是：先在 MYI 文件的索引树中定位到数据对应的磁盘地址，再根据该地址到 MYD 文件中读取实际的行数据。这意味着，所有通过索引的查询，都必须经历一次 “从索引文件跳转至数据文件” 的过程，性能开销相对较大。

这一架构存在天然的性能局限：它无法避免 “回表” 类的额外磁盘访问，必须先读取索引文件，再根据索引中存储的物理地址读取数据文件；而范围查询的性能表现会更差 —— 因为数据在物理磁盘上是分散存储的，顺序遍历索引获取的磁盘地址，反而可能映射到不连续的磁盘数据块，产生大量随机 I/O。

1.3.2 InnoDB 的聚簇索引架构

InnoDB 是 MySQL 5.5 版本后的默认存储引擎，它采用聚簇索引架构 —— 其核心设计是将行数据与主键索引紧密绑定为一个整体：

聚簇索引的核心定义是，主键索引的叶子节点直接存储完整的行数据，也就是说，行数据本身就是主键索引的叶子节点。这意味着，主键索引的树结构与行数据存储在同一个文件中 —— 即 InnoDB 的 表名.ibd 文件。这一设计的好处是，主键查询在 InnoDB 中性能极高：只需要检索一次主键索引树，就能直接定位到完整的行数据，不需要额外的磁盘访问。
InnoDB 的所有二级索引（即非主键索引），其叶子节点存储的是主键值，而非数据的物理地址。这意味着，通过二级索引查询时，需要先在二级索引树中检索到对应的主键值，再拿着这个主键值到主键索引树中检索完整的行数据 —— 这个过程被称为 “回表”。回表操作会额外增加一次索引检索的 I/O 开销，这也是 InnoDB 中二级索引查询的主要性能损耗点。

2 索引类型详解

从逻辑功能和实现方式的维度来看，MySQL 索引有多种类型，不同类型的索引有着完全不同的适用场景和使用限制。只有精准掌握每一种索引的特性，才能在设计索引时做到 “场景与索引精准匹配”。

先给出索引的分类：

mindmap
  root((索引的分类))
    按数据结构分类
      B+tree索引
      Hash索引
      Full-text索引
      空间索引
      
    按物理存储分类
      聚簇索引
      非聚簇索引
    按字段特性分类
      主键索引
      唯一索引
      前缀索引
    按字段个数分类
      单列索引
      联合索引

2.1 B-Tree 索引（B+Tree 索引）

B+Tree 索引是 MySQL 中最核心、最常用的索引类型 —— 除非明确指定其他类型，否则 CREATE INDEX 语句默认创建的都是 B+Tree 索引。它是聚簇索引、二级索引等绝大多数索引的基础实现方式。

2.1.1 结构原理

关于 B+Tree 的结构特性，我们在前一章已经详细分析过。

需要强调的是，在 MySQL 的官方文档和多数技术资料中，为了表述简洁，通常将 “B+Tree 索引” 简称为 “B-Tree 索引”—— 这只是一种简称，并非实际使用 B 树这一数据结构。

2.1.2 适用场景

B+Tree 索引的结构特性，决定了它几乎覆盖了数据库所有的主流查询场景，是使用范围最广的索引类型，也是索引设计时的首选类型。具体来看，其适用场景包括：

等值查询：使用 = 或 IN 操作符的精确匹配查询。
范围查询：使用 >、<、>=、<=、BETWEEN 或 LIKE '模糊匹配前缀%' 操作符的查询。这是 B+Tree 索引的优势场景，因为叶子节点的双向链表可以高效遍历范围內的所有数据。
模糊匹配查询：仅匹配前缀的 LIKE 查询，例如 LIKE '张%'。这是因为 B+Tree 索引的键值是顺序存储的，前缀匹配的条件可以直接转化为范围扫描；反之，LIKE '%xxx' 或 LIKE '%xxx%' 这类后缀匹配或包含匹配的场景，无法利用索引的有序性。
排序与分组操作：当查询的 ORDER BY、GROUP BY 条件中，所有字段都匹配索引的最左前缀顺序时，B+Tree 索引可以直接利用其有序性完成排序和分组，避免性能开销极高的 filesort 文件排序操作。

2.1.3 限制条件

B+Tree 索引并非 “银弹”，也存在一定的使用限制，这是由其数据结构特性决定的：

正如前文提到的，它无法支持 LIKE '%模糊匹配后缀%' 这类以通配符开头的模糊查询 —— 这类场景下，索引的顺序性无法被利用，数据库只能进行全表扫描。
另外，对于哈希索引擅长的内存表场景，B+Tree 索引的等值查询效率相对较低 —— 但这仅是极少数场景，并不影响它在绝大多数场景下的主流地位。
最后，B+Tree 索引的维护成本较高 —— 在高并发写入场景下，插入、删除操作会导致索引树节点分裂、平衡，带来可观的性能开销，这也是后续优化的重点方向。

2.2 哈希索引

哈希索引是基于哈希表实现的索引类型，它与 B+Tree 索引的特性完全互补 ——B+Tree 索引擅长的场景，它不擅长；反之，它只精准适配 B+Tree 索引不覆盖的核心场景。

2.2.1 结构原理

哈希索引的底层实现逻辑是基于哈希表的：索引的存储结构由哈希键值映射表和数据指针两部分组成。当对索引列进行查询时，哈希索引会先对所有索引键值的组合，用哈希函数计算出一个哈希码；然后将这个哈希码映射到哈希表中的对应位置，而哈希表中存储的是指向实际数据行的磁盘指针。

需要注意的是，为了提升性能，哈希索引的哈希表结构必须常驻内存 —— 这也导致它的使用场景受到严格限制。

2.2.2 特性与适用场景

哈希索引的设计目标，就是优化 B+Tree 索引不擅长的场景，其核心特性完全服务于这一目标。它的优势场景非常单一：仅支持等值查询，而且是对热点数据的高频等值查询 —— 在这一场景下，哈希索引的理论时间复杂度能达到 O (1)，性能表现远优于 B+Tree 索引。

不过，哈希索引也有使用前提：它必须基于 B+Tree 索引的热点数据页构建，且只对存储在 Buffer Pool 中的热点数据生效。在实际应用中，哈希索引的典型适用场景是高频热点数据的等值查询，例如 WHERE user_id = 100 这类针对主键或唯一索引的精确匹配查询。

2.2.3 限制条件

哈希索引的使用限制非常严格，这是由其哈希表的底层数据结构特性决定的，也直接导致它无法作为通用索引类型使用。这些限制包括：

仅支持等值查询：这是最核心的限制。哈希表的设计是无序的，哈希码的计算结果与键值的大小顺序完全无关 —— 因此，它不支持任何范围查询、排序操作或分组操作，也无法优化 LIKE 模糊匹配类的查询。
存在哈希冲突风险：当多个不同的索引键值计算出相同的哈希码时，会发生哈希冲突；冲突发生后，数据库需要遍历冲突链中的所有元素，才能定位到目标数据，这会将查询复杂度从 O (1) 退化到 O (n)。
无法使用索引优化排序与分组操作：这一限制同样源于哈希表的无序性，它无法避免 filesort 和临时表的性能开销。
仅支持内存级存储：哈希索引的哈希表结构必须常驻内存，这意味着数据库重启或内存不足时，哈希索引会被清空，需要重新自动构建 —— 这也是它不能作为通用索引类型的核心原因。

2.2.4 MySQL 中哈希索引的实际使用

MySQL 的主流存储引擎对哈希索引的支持度非常有限，这一特性并没有被广泛使用，这也是由哈希索引的使用限制决定的：

Memory 存储引擎：是 MySQL 中唯一能显式创建哈希索引的存储引擎，但它是一种将所有数据存储在内存中的存储引擎，一旦数据库重启，所有数据和哈希索引都会丢失 —— 这意味着它无法承担持久化数据的存储重任，只能作为临时的缓存存储介质。
InnoDB 存储引擎：是 MySQL 中唯一支持哈希索引的存储引擎，但它不支持用户显式创建哈希索引，而是提供了自适应哈希索引（AHI） 。这是一种智能的、自动的优化功能，用户不需要手动创建或配置，完全由 InnoDB 引擎自动监控查询模式、判断是否需要构建 —— 它只会针对那些高频等值查询的 B+Tree 索引页，在内存中自动构建哈希索引结构。
MyISAM 存储引擎：完全不支持哈希索引，没有任何额外的索引优化机制。

2.3 聚簇索引与非聚簇索引

从索引与数据的存储关系区分，索引可以分为聚簇索引和非聚簇索引两大类 —— 这是 InnoDB 和 MyISAM 索引实现的核心差异点，也直接决定了二级索引的使用效率和优化空间。

2.3.1 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种索引与数据的存储方式，其核心逻辑是 “索引即数据，数据即索引”。它的核心定义是：将完整的行数据存储在索引的叶子节点中，数据行与主键索引的叶子节点物理绑定在一起，两者合二为一。

InnoDB 的主键索引就是最典型的聚簇索引，也是 InnoDB 中唯一的聚簇索引 —— 聚簇索引在每张表中只能有一个，因为数据行的物理存储顺序只有一种，必须与索引键值的顺序严格保持一致。

InnoDB 聚簇索引的选择规则

由于聚簇索引在每张表中只能有一个，InnoDB 必须确定哪个索引作为聚簇索引。InnoDB 按照以下优先级顺序，依次选择聚簇索引：

主键（PRIMARY KEY）：如果表定义了主键，InnoDB 会直接将主键作为聚簇索引。这是最优先的选择，也是性能最优的场景 —— 主键通常具有自增、紧凑、有序的特性，与聚簇索引的顺序插入需求完美匹配。正因为如此，InnoDB 强烈建议每张表都显式定义主键。
第一个非空唯一索引（UNIQUE NOT NULL）：如果表没有定义主键，但存在 UNIQUE 约束且所有列均为 NOT NULL 的索引，InnoDB 会选择第一个满足条件的唯一索引作为聚簇索引。需要强调的是，InnoDB 的选择依据是索引定义的先后顺序 —— 即建表语句或 ALTER TABLE 语句中最先声明的那个非空唯一索引，而非按索引名称的字母排序。
隐式生成的 Row ID（DB_ROW_ID）：如果表既没有定义主键，也没有任何非空唯一索引，InnoDB 会自动在内部生成一个 6 字节的自增隐藏列 DB_ROW_ID，并将其作为聚簇索引的键值。这个隐藏列对用户完全不可见 —— 无法通过任何 SQL 语句查询或引用，也无法作为查询条件使用。这一兜底方案存在严重的性能隐患：由于 DB_ROW_ID 是全局共享的（而非表级独立自增），在多张表都没有主键的情况下，不同表的自增序列会互相竞争，导致数据页的频繁分裂和大量随机 I/O。

始终为每张 InnoDB 表显式定义一个自增整型主键。这不仅是让 InnoDB 选择最优聚簇索引的前提，还能避免隐式 DB_ROW_ID 带来的性能隐患，同时也是确保二级索引高效回表的基础 —— 因为二级索引的叶子节点存储的是主键值，短小且有序的自增主键能将二级索引的存储空间和回表开销降到最低。

聚簇索引的优势非常明显：由于行数据的物理存储顺序与主键索引的顺序完全一致，主键查询可以直接定位到完整的行数据，不需要额外的磁盘跳转；同时，范围查询的效率极高，因为范围內的数据在磁盘上是物理连续存储的，可以通过顺序 I/O 一次性读取。

但聚簇索引也存在一定的使用限制，这是由其数据存储的连续性决定的：

插入速度严重依赖于插入顺序 —— 如果采用非顺序的主键（如 UUID），会导致插入操作需要频繁分裂磁盘页，产生大量随机 I/O，性能开销极高；
此外，二级索引的查询需要回表，这会增加二级索引的性能开销；
而且，聚簇索引的维护成本较高 —— 当执行 UPDATE 或 DELETE 操作时，移动数据行需要同步更新所有相关的二级索引的主键值指针，会带来额外的性能损耗。

2.3.2 非聚簇索引

非聚簇索引同样是一种索引与数据的存储关系，其核心逻辑是 “索引与数据分离存储”。在这类索引中，索引文件和数据文件是独立分开存储的；索引的叶子节点存储的是数据行的物理地址（MyISAM）或主键值（InnoDB），而不是完整的行数据。

在 InnoDB 中，所有的二级索引都是非聚簇索引；而在 MyISAM 中，包括主键索引在内的所有索引，都是非聚簇索引。非聚簇索引需要二次查询的特性，决定了它的查询性能整体低于聚簇索引。

2.4 其他索引类型与特性

在实际业务中，还有几种索引类型是索引设计的常用补充。它们的使用场景相对单一，但在适配特定业务场景时，能带来显著的性能收益。

2.4.1 联合索引

联合索引也叫复合索引，是基于表上的多个列创建的索引。

联合索引的设计和使用必须遵循最左前缀原则：因为索引 B+Tree 中的键值顺序，是严格按照索引定义的字段顺序拼接的；

比如字段 a、b 构建联合索引，那么首先按照字段 a 排序，在 a 值相等的情况下，再按照字段 b 排序，所有仅当 a 值相等时，b 才是有序的。

查询条件必须匹配索引的最左 N 个连续字段，才能让联合索引生效；而在使用范围查询时，只有最左等值字段的索引会被利用，范围查询右侧的列将无法被利用。这一原则是联合索引设计和使用的核心依据，后续章节会详细分析其落地细节。

2.4.2 前缀索引

前缀索引是针对字符串类型列的一种特殊索引类型。对于很长的字符串列（比如 VARCHAR(255) 或 TEXT 类型），为了减少索引的存储空间占用、提升索引检索效率，我们可以不索引完整的字符串值，而是选择让索引记录字符串值的前 N 个字符，这就是前缀索引。

前缀索引的核心设计目标，是在区分度和存储成本之间做平衡 —— 它既能大幅降低索引的存储空间占用，又能在有效提升查询性能的前提下，避免全表扫描。但是，前缀索引也存在明显的使用限制：它无法匹配 LIKE '%后缀' 的模糊查询，也不能用于 ORDER BY 和 GROUP BY 场景；同时，前缀长度的选择必须保证区分度 —— 如果区分度不够，索引的效率会大幅降低，甚至与全表扫描无差异。

2.4.3 其他索引类型

此外，还有几种针对特定场景的索引类型，在特殊场景下能起到关键作用：

全文索引（Fulltext Index） ：是针对长文本数据的全文检索场景设计的索引类型，它替代了 LIKE '%关键词%' 这类低效的模糊匹配场景，是目前大数据量下，相对成熟的全文检索官方解决方案。在 InnoDB 和 MyISAM 存储引擎中，全文索引的底层实现都是倒排索引。
空间索引（Spatial Index） ：是针对空间数据类型设计的索引类型，它基于 R-Tree 数据结构实现，是 MySQL 的地理信息系统（GIS）相关函数的核心优化支撑。
唯一索引（Unique Index） ：是一种特殊的 B+Tree 索引，它的核心约束是所有索引键值都必须是唯一的 —— 这意味着，索引列不会有任何重复条目，既可以提升查询效率，又能强制实现业务逻辑的唯一性约束。在 InnoDB 中，创建 UNIQUE 约束时，会自动创建对应的唯一索引；而主键索引本质上就是一种非空的唯一索引。

3 索引设计与创建原则

索引是应用程序设计和开发中最容易被过度使用的领域之一。如果索引设计不合理，或创建了过多无效的索引，反而会严重影响数据库的整体性能 —— 索引并非越多越好，也不是所有字段都适合创建索引。接下来，我们基于 MySQL 的官方文档建议，以及业界的实际优化经验，总结出索引设计的黄金原则，指导开发者判断 “何时该创建索引” 以及 “如何合理创建索引”。

3.1 基础设计原则：选择合适的索引列

索引设计的核心是选对合适的列 —— 这是索引设计的基础，如果列选择错误，后续的任何优化都无法挽回性能损失。在选择索引列时，需要遵循以下四大核心原则。

3.1.1 经常作为查询条件的列

为了让索引生效，索引列必须在查询条件中被使用 —— 这是索引设计的最基础原则。具体来说，只有那些经常出现在 WHERE 子句、JOIN 条件、ORDER BY 或 GROUP BY 子句中的字段，才是索引的候选字段。反之，如果一个字段在业务查询场景中很少作为过滤条件出现，那么为它创建索引的收益将非常有限，甚至完全没有收益。

例如，在一个电商系统的用户表中，如果业务查询场景中，经常使用 WHERE user_id = ? 或 WHERE phone = ? 作为过滤条件，那么 user_id 和 phone 字段就是必须优先考虑创建索引的候选列；但如果一个字段在业务查询场景中从未作为过滤条件出现，比如 last_login_ip 字段仅在用户登录历史记录中被查询，而系统中没有以该字段为过滤条件的查询场景，那么为它创建索引就完全没有必要，反而会增加维护成本。

3.1.2 区分度高的列

索引的核心效能，与索引列的区分度（也称为 “选择性”）直接正相关 —— 区分度越高，索引的效率就越高。区分度的计算公式为：COUNT(DISTINCT 索引列) / COUNT(*)，这个值的理论范围在 0 到 1 之间，越接近 1，说明该列的区分度越高，索引的效率也就越高。

区分度高的列，其过滤性也越强 —— 优化器可以通过这类索引，快速筛选出符合条件的少量数据；而区分度低的列，过滤性也弱，甚至完全没有筛选作用。例如，gender 字段的区分度只有约 0.5，即使建了索引，优化器也很可能最终选择全表扫描而非使用索引；相比之下，phone、email 这类具有业务唯一性的字段，区分度接近或等于 1，是创建索引的最优选择。

对于前缀索引的场景，同样需要遵循这一原则：必须选择合适的前缀长度，保证前缀的区分度足够高。

3.1.3 不经常更新的列

索引是一种 “查询加速、写入减速” 的数据结构 —— 它可以提升查询性能，但会降低数据更新操作（INSERT、UPDATE、DELETE）的性能。这是因为，当对表中的数据进行写入或更新操作时，所有相关的索引数据也需要被同步更新；如果更新操作涉及到索引列的值变更，数据库还需要将索引树中的对应记录删除，重新插入到合适的位置，这会导致索引树的节点分裂、平衡，产生极大的随机 I/O 开销。

因此，对于索引列的选择，需要遵循的核心原则是：优先选择那些不经常更新的列，或更新操作不会修改其值的列；而对于频繁更新的列，或更新操作会频繁修改其值的列，则不适合创建索引。

例如，在一个电商系统的订单表中，user_id、order_id 这类在创建后永远不会被更新的字段，是非常适合作为索引列的；而 order_status 这类会随着订单流转频繁更新的字段，虽然有时会作为查询条件出现，但为了避免过高的索引维护成本，应该尽量避免单独对其创建索引，而是将其作为联合索引的非前缀字段，纳入到其他主流查询场景的联合索引中 —— 这样既能覆盖部分查询场景，又能将维护成本控制在合理范围内。

3.1.4 长度合适的列

由于索引页的存储空间限制，索引字段的长度会直接影响索引的存储效率和维护成本：字段长度越长，单个索引页能存储的索引键数量就越少，索引树的层高就越高，查询时需要读取的磁盘页就越多，性能损耗也就越大。因此，对于长字符串类的列，必须保证长度合适，这也是索引设计的重要补充原则。

这里的 “长度合适” 包含两层含义：首先，优先使用长度较短的类型，比如用 INT 类型存储数值，用 VARCHAR 类型存储字符串，避免使用 TEXT 或 BLOB 这类大文本类型；其次，对于较长的字符串列，应该创建前缀索引 —— 只索引字符串的前 N 个字符，在区分度和存储成本之间找到平衡。

例如，在一个电商系统的商品表中，product_description 字段是 VARCHAR(500) 类型，长度较长，但业务场景中经常需要用它作为模糊匹配条件查询，这时候我们可以创建一个前缀索引，比如 CREATE INDEX idx_product_desc ON product(product_description(100));，通过设置合理的前缀长度，在保证查询效率的前提下，大幅降低索引的存储空间占用和维护开销。

3.2 复合索引设计：最左前缀原则

当查询条件中包含多个列时，创建一个多列联合索引，比多个单列索引更高效 —— 这是因为联合索引可以更好地过滤数据，且能避免优化器选择索引时的混乱问题。而联合索引设计的核心理论基础，就是最左前缀原则—— 这是联合索引设计的铁律，必须严格遵循。

3.2.1 原则内容

最左前缀原则的定义是：联合索引的 B+Tree 中的键值，是严格按照索引定义的字段顺序拼接而成的；查询条件必须匹配索引的最左 N 个连续字段，才能让联合索引生效；且在匹配过程中，不能跳过索引定义中的中间列。

这一原则的核心逻辑是，联合索引的字段顺序决定了 B+Tree 的排序顺序：索引会先按照第一个字段的值进行排序；只有第一个字段的值相等的情况下，才会再按照第二个字段的值进行排序；以此类推。因此，查询条件必须从最左列开始，且连续匹配多个列，才能利用到索引的有序性；如果不包含最左列，或跳过了中间列，联合索引的有序性就无法被充分利用，甚至会直接失效。

3.2.2 字段顺序选择

在设计联合索引时，字段顺序需要遵循 “使用频率 + 区分度 + 查询方式” 的三重维度原则，同时要结合业务查询场景，将最有效的字段放在最左侧，这是最左前缀原则的核心落地依据。具体来说，字段顺序的选择需要遵循以下规则：

高频使用字段优先：将那些在查询条件中使用频率最高的字段，放在联合索引的最左侧 —— 这样才能保证更多的查询场景，能匹配到索引的最左前缀段，从而命中索引。
高区分度字段优先：将区分度高的字段放在前面，将区分度低的字段放在后面 —— 这样可以让索引在查询的前期，快速过滤掉大量无效数据，大幅减少后续需要处理的数据量。
等值查询字段优先：将所有等值查询条件的字段，放在联合索引的前面，将所有范围查询条件的字段，放在联合索引的最后面 —— 这是因为，范围查询会截断其右侧列的索引使用：如果范围查询列放在前面，后面的列将无法被索引利用；而如果将等值条件列放在前面，范围条件列放在后面，索引就可以先利用等值条件，快速筛选出目标数据，再利用范围条件进行高效扫描。

例如，在一个电商系统的订单表中，有一个场景是 “查询某个用户在某个时间点之后的、状态为已支付的所有订单记录”，对应的查询条件是 WHERE user_id = 100 AND order_status = 1 AND create_time > '2024-01-01'。在这个查询条件中，user_id 和 order_status 是等值查询字段，create_time 是范围查询字段。按照上述规则，联合索引的正确顺序应该是 (user_id, order_status, create_time)—— 而如果将范围查询列 create_time 放在 order_status 前面，或者将低区分度的 order_status 放在 user_id 前面，都会导致索引效率大幅降低。

3.2.3 避免冗余索引

冗余索引是指那些被其他索引的最左前缀部分包含、覆盖相同查询场景，且维护成本更高的索引。冗余索引不仅无法提升查询性能，反而会浪费存储空间，并且会大幅降低 INSERT、UPDATE、DELETE 操作的性能 —— 因为表的写入操作需要同步更新所有相关的索引。

在设计联合索引时，需要通过以下步骤规避冗余索引：

先根据业务场景的查询条件，梳理出所有需要覆盖的查询组合；
再为这些查询组合设计少量的联合索引，让每个联合索引都能覆盖一组高频查询场景；
避免重复创建包含相同前缀的索引，例如，如果已经有了联合索引 idx_a_b_c(a, b, c)，就不需要再单独创建索引 idx_a(a) 或 idx_a_b(a, b)—— 因为前者的最左前缀部分，已经可以覆盖后两者的查询场景。

在实际工作中，可以通过 INFORMATION_SCHEMA.STATISTICS 系统表，或第三方的优化工具（如 Percona Toolkit），来识别冗余索引并进行清理。

3.3 不合适创建索引的场景

索引有提升查询效率的正向收益，但也存在降低写入效率的负向维护成本。在某些场景下，创建索引带来的性能收益，可能远低于它带来的维护成本，因此必须谨慎选择。

3.3.1 经常被更新的大表

对于大表而言，数据量越大，创建索引的成本就越高 —— 索引的维护成本会随着数据量的增长而线性上升。如果一张表的写入操作非常频繁，且数据量很大，那么索引的维护开销将非常可观 —— 甚至会严重影响数据库的写入性能。

对于这类表，我们需要控制索引的数量：只对那些核心的、高频的查询场景创建联合索引，且必须优先选择不经常更新的列作为索引字段；对于次要的、低频的查询场景，则应该避免创建索引，而是采用其他优化方式，比如基于时间戳的分表、基于一致性分库的路由，或通过引入搜索引擎来承担部分查询压力。

3.3.2 数据量小的表

对于数据量小的表（比如行数少于 1 万行的表），数据库的全表扫描速度可能比使用索引的速度还要快 —— 这是因为，全表扫描只需要顺序读取少量数据页即可完成，而使用索引的话，需要额外读取索引树的非叶子节点页，会增加磁盘 I/O 的次数。如果表中的数据量很少，甚至可以完全存储在内存中，那么索引带来的性能收益就更低，甚至完全没有收益。

对于这类表，除非是为了适配核心业务的极致性能需求，或者是为了维护业务的唯一性约束（比如唯一索引），否则不需要创建任何索引。

3.3.3 区分度低的列

正如前文提到的，区分度低的列（如性别、状态），其索引的过滤性也很差，优化器通常会选择跳过这类索引，直接进行全表扫描。如果在这类列上创建索引，索引的有效利用率会非常低，而且会浪费存储空间，增加写入操作的维护成本。

对于这类列，正确的处理方式是：不单独对其创建索引，而是将这类列作为联合索引的非前缀字段，纳入到其他主流查询场景的联合索引中 —— 在这类场景中，等值条件的低区分度列，可以在高区分度列过滤数据后，进一步过滤数据，这样既不会影响索引的效率，也能在一定程度上提升查询的性能。

3.3.4 很少在查询条件中使用的列

如果一个列在业务场景的查询条件中很少出现，或者它的存在不影响查询结果的筛选，那么为这个列创建索引的收益将非常有限，甚至完全没有收益 —— 因为索引的主要作用就是过滤数据，而这类列不会对过滤数据起到作用。

例如，在一个电商系统的用户表中，last_login_ip 字段仅在用户登录历史记录中被查询，而系统中没有以该字段为过滤条件的查询场景，那么为这个字段创建索引，就属于典型的无效索引，反而会浪费磁盘空间，增加写入操作的维护成本。只有在该字段作为查询条件出现在高频业务场景中时，才需要考虑将其纳入联合索引。

4 索引优化策略

在理解了索引的创建原则后，接下来需要掌握针对不同场景的索引优化策略 —— 科学的优化可以让索引的收益提升数倍。本章将介绍几种核心的、经过业界验证的优化策略，这些策略都是基于 B+Tree 的结构特性，以及 InnoDB 的索引实现逻辑设计的。

4.1 利用覆盖索引优化回表

覆盖索引是索引优化中非常重要的手段，它的核心价值是通过 “不回表” 的方式，大幅提升二级索引的查询性能。在 InnoDB 中，这是优化二级索引查询的最主要、最有效的手段。

4.1.1 理解回表代价

InnoDB 的二级索引查询需要回表，这是其聚簇索引架构下的固有特性。二级索引的叶子节点仅存储主键值，而如果查询需要返回索引中不包含的字段，数据库必须根据主键值，再到聚簇索引的叶子节点中查找完整的行数据 —— 这个额外的查找过程就是 “回表”。

回表操作会额外增加一次 B+Tree 的检索过程，带来极大的随机 I/O 开销；如果需要返回的数据量很大，回表的成本会更高，甚至会直接抵消索引带来的性能收益。这也是为什么 SELECT * 这类需要返回所有字段的查询，往往性能表现不佳，甚至优化器会选择全表扫描而非使用二级索引的根本原因。

4.1.2 覆盖索引优化逻辑

覆盖索引的核心设计逻辑是，将查询需要使用到的所有字段，全部纳入到联合索引中 —— 形成一个 “覆盖” 查询所需全部字段的联合索引；这样一来，MySQL 就可以直接通过扫描二级索引的叶子节点来获取全部数据，不需要再回表访问聚簇索引。

覆盖索引的使用条件非常简单：它要求一个查询的所有列数据，都可以从二级索引的节点中直接获取。而在实际场景中，覆盖索引的设计往往与联合索引结合在一起 —— 通过将查询所需的所有字段，包含在联合索引的字段列表中，来满足覆盖索引的使用条件。

4.2 利用索引下推减少回表

覆盖索引通过 “不回表” 的方式优化查询性能，但在实际业务中，并非所有查询都能被覆盖索引覆盖 —— 当查询所需的字段超出联合索引的覆盖范围时，回表操作不可避免。索引下推（Index Condition Pushdown，简称 ICP）正是针对这一场景的优化手段：它无法消除回表，但能在回表之前，在存储引擎层提前过滤掉不满足条件的记录，从而减少无效的回表次数，降低查询的整体 I/O 开销。

4.2.1 索引下推的优化背景

在理解索引下推之前，需要先了解不使用 ICP 时，联合索引的查询执行流程 —— 这也是索引下推优化的直接对象。

假设一张用户表 user 上有联合索引 idx_name_age_gender(name, age, gender)，执行以下查询：

SELECT * FROM user WHERE name LIKE '张%' AND gender = 0;

在不支持 ICP 的情况下，查询的执行流程如下：

存储引擎根据联合索引 idx_name_gender_age，定位到第一个满足 name LIKE '张%' 条件的索引记录；
根据最左前缀原则，gender 列的索引无法被利用 —— 存储引擎只能用 name 列进行索引定位；
对每一条满足 name LIKE '张%' 的索引记录，存储引擎立即根据主键值回表，从聚簇索引读取完整行数据。
将完整行数据返回服务器层。
服务器层逐条判断 gender = 0 是否成立，不满足则丢弃。

这个流程的核心问题在于：所有满足 name LIKE '张%' 的记录，无论 gender 是否等于 0，都会触发回表操作。假设有 1000 条记录满足 name LIKE '张%'，但其中只有 10 条满足 gender = 0，那么不使用 ICP 时，存储引擎需要执行 1000 次回表，而其中 990 次都是无效的 —— 这些无效的回表操作带来的随机 I/O 开销，是性能的主要浪费点。

4.2.2 索引下推的优化逻辑

索引下推的核心思路是：将部分本应在服务器层执行的 WHERE 条件，下推到存储引擎层、在回表前完成判断。下推的对象不是联合索引中的"所有列"，而是那些已存在于索引项中、但因最左前缀或范围截断而无法参与索引定位的条件 —— 存储引擎在遍历二级索引的叶子节点时，先根据联合索引中包含的所有列（而非仅限于最左前缀匹配的列）判断记录是否满足 WHERE 条件，只有满足条件的记录才会执行回表操作，不满足条件的记录直接跳过。

以上述查询为例，使用 ICP 后的执行流程变为：

存储引擎根据联合索引 idx_name_age_gender，定位到满足 name LIKE '张%' 条件的索引记录；
关键变化：存储引擎在读取每条索引记录时，直接利用索引中存储的 gender 列值，判断 gender = 0 条件是否成立 —— 只有同时满足 name LIKE '张%' 和 gender = 0 的记录，才会被选中；
对于选中的记录，存储引擎再根据主键值执行回表操作，从聚簇索引中读取完整的行数据；
服务器层收到完整行数据后返回客户端。本例中 WHERE 条件均可由索引项判定，服务器层无需再做额外过滤；若还存在索引未覆盖的条件（如 WHERE name LIKE '张%' AND gender = 0 AND status = 1 且 status 不在索引中），则回表后仍由服务器层过滤 status。

在这个优化流程中，假设 1000 条满足 name LIKE '张%' 的记录中只有 10 条满足 gender = 0，那么存储引擎只需要执行 10 次回表，而非 1000 次 ——回表次数从 1000 次骤降至 10 次，性能提升幅度可达两个数量级。

4.2.3 索引下推的生效条件与限制

索引下推是一种由优化器自动决策的优化策略，开发者无需手动启用或配置。但了解其生效条件和限制，有助于写出能被 ICP 优化的 SQL 语句，以及在排查性能问题时判断 ICP 是否生效。

生效条件：

存储引擎支持：ICP 仅在 InnoDB 和 MyISAM 存储引擎中生效，且仅适用于二级索引（非聚簇索引）。聚簇索引本身包含完整行数据，不存在回表问题，因此 ICP 对聚簇索引无意义。
查询条件中包含联合索引的非最左前缀列：这是 ICP 最典型的适用场景 —— 当 WHERE 条件中存在联合索引的列，但由于最左前缀原则的限制（如范围查询截断、跳过中间列等），这些列无法被用于索引定位时，ICP 可以在索引遍历阶段利用这些列的值进行提前过滤。
查询不能使用覆盖索引：如果查询已经使用了覆盖索引（Extra 列为 Using index），则不会触发 ICP —— 因为覆盖索引本身不需要回表，ICP 减少回表的价值无从体现。
子查询条件不支持：ICP 不适用于子查询的条件，仅适用于 WHERE 子句中的直接条件。
存储函数与触发器条件不支持：ICP 不适用于涉及存储函数或触发器的条件，因为存储引擎层无法执行这些逻辑。

限制条件：

ICP 只能减少回表次数，无法消除回表 —— 如果查询需要返回索引中不包含的字段，回表操作始终存在。要完全消除回表，仍需依赖覆盖索引。
ICP 的优化效果取决于 “能在索引层过滤掉多少不满足条件的记录”—— 如果大部分记录都满足条件，ICP 的收益将非常有限；反之，如果过滤比例很高，ICP 的收益将非常显著。

4.2.4 如何判断索引下推是否生效

在实际排查性能问题时，可以通过 EXPLAIN 命令的输出结果，判断 ICP 是否被优化器选择使用：

如果 Extra 列的值为 Using index condition，表示查询使用了索引下推 —— 存储引擎在遍历二级索引时，会根据索引中包含的所有列进行提前过滤，只对满足条件的记录执行回表。
如果 Extra 列的值为 Using where，表示查询未使用索引下推 —— 存储引擎将所有满足最左前缀条件的索引记录都返回给服务器层，由服务器层进行后续过滤，无效的回表操作没有被减少。
如果 Extra 列的值为 Using index，表示查询使用了覆盖索引，不需要回表，此时 ICP 不适用。

4.2.5 索引下推与覆盖索引的关系

索引下推和覆盖索引是两种不同的回表优化策略，它们的目标一致（减少回表开销），但优化路径不同，适用场景也互补：

覆盖索引：通过 “完全避免回表” 来优化性能，要求查询的所有列都包含在索引中。这是更彻底的优化方案，但对联合索引的字段覆盖度要求较高，并非所有场景都能满足。
索引下推：通过 “减少回表次数” 来优化性能，只要求查询条件中包含联合索引的非最左前缀列即可生效。它的优化力度不如覆盖索引，但适用范围更广 —— 尤其是在查询需要返回索引中不包含的字段（如 SELECT *）时，覆盖索引无法使用，索引下推就成了唯一可用的优化手段。

在实际优化中，两者的优先级关系是：优先使用覆盖索引，当覆盖索引无法满足时，再利用索引下推作为补充。在联合索引的设计中，可以将查询条件中涉及的列纳入索引（覆盖索引 + 索引下推），而将仅需返回但不作为过滤条件的列排除在索引之外（避免索引过宽），兼顾查询性能和索引维护成本。

4.3 优化分页查询

分页查询是 Web 业务系统中非常常见的一个功能，也是最容易出现性能瓶颈的场景 —— 尤其是对于 “深分页” 场景，即偏移量 offset 较大时，常规的分页写法会导致查询性能急剧下降。要优化这类场景的性能，首先需要理解其性能瓶颈的根源。

4.3.1 深分页的性能瓶颈

当使用 LIMIT offset, rows 进行分页查询时，如果 offset 的值很大（比如 LIMIT 10000, 10），数据库会先扫描并丢弃 offset+rows 行中的前 offset 行数据，再返回接下来的 rows 行数据。这一过程的性能开销极大，因为数据库需要读取大量不相关的数据行，这些数据行可能还不在内存中，需要从磁盘重新读取，产生大量的随机 I/O 开销；如果查询语句中没有使用覆盖索引，还会产生大量的回表开销。

例如，对于 SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 10; 这个查询，数据库需要先扫描 10010 行数据，丢弃前 10000 行，再返回最后 10 行；如果符合条件的数据量很大，扫描的行数会更多，性能开销会急剧上升。

4.3.2 优化策略

解决这类场景的性能问题，常见的优化方案有两种，其核心逻辑都是利用索引的有序性，避免扫描并丢弃大量数据，将 “offset 分页” 转化为 “游标分页”：

延迟关联（Deferred Join） ：先通过覆盖索引定位到分页需要的主键值，再关联原表，根据主键值读取需要返回的行数据。这一方案的核心是，先在索引树中找到对应的主键值，再根据主键值进行聚簇索引的查询。
游标分页（Cursor Pagination） ：利用上一页的最后一条数据的唯一索引键值（如主键值）作为条件，将分页查询转化为一个范围查询 —— 这一方案可以完全避免大偏移量的扫描开销，是深分页场景下的最优优化方案。

4.3.3 实战案例

我们通过实际 SQL 来理解这两种方案的落地逻辑。首先是延迟关联的优化方案，对应的 SQL 语句如下：

-- 优化前：LIMIT 偏移量过大，需要扫描大量数据
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 10;

-- 优化后：延迟关联，先通过覆盖索引定位主键，再回表查询
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 10000, 10) t
ON o.id = t.id;

在这个方案中，子查询 SELECT id FROM orders ORDER BY id LIMIT 10000, 10 利用了覆盖索引，只扫描索引树的主键值，不需要回表；然后外部查询根据主键值，通过聚簇索引直接读取完整的行数据，将回表的次数从 10010 次减少到 10 次。

接下来是游标分页的优化方案，对应的 SQL 语句如下：

-- 优化后：使用游标分页，避免大偏移量的扫描开销
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 10;

这一方案的核心逻辑是，将 “基于偏移量的分页”，改为 “基于上一页最后一条数据的主键值的分页”—— 这样一来，数据库可以直接通过聚簇索引，定位到上一页最后一条数据的主键值，然后直接从该位置开始读取接下来的 10 行数据，不需要扫描并丢弃任何数据。

两种优化方案都需要满足的前提是，ORDER BY 的列必须匹配索引的最左前缀顺序 —— 这样才能保证索引的有序性，让优化器能够利用索引来完成定位操作，避免全表扫描。

4.4 优化 ORDER BY 排序与 GROUP BY 分组

排序和分组操作是数据库查询中主要的性能开销来源之一。如果 ORDER BY 或 GROUP BY 语句不能使用索引的有序性，MySQL 就会使用 filesort 文件排序或临时表来完成排序和分组操作 —— 这两种操作的性能开销都极大，会直接拖慢查询的整体性能。

4.4.1 排序的性能瓶颈

要优化排序操作，首先需要理解 filesort 的性能开销来源。当 ORDER BY 条件中的字段顺序，与索引的最左前缀顺序不匹配，或者查询的字段没有被覆盖索引包含时，优化器就会选择使用 filesort 而非索引排序。filesort 的执行过程是：先将查询到的所有需要排序的数据行，存储在内存或磁盘的临时文件中；然后使用快速排序算法，对这些数据进行排序；排序完成后，再将排序后的结果集返回给客户端。

这一过程的性能开销极大，尤其是对于大数据量的排序操作 —— 数据量越大，排序的开销就越高，甚至会导致数据库的内存、磁盘 I/O 等资源耗尽。

4.4.2 优化策略

优化 ORDER BY 和 GROUP BY 的核心逻辑，是让索引的有序性直接覆盖排序分组的需求 —— 让数据库完全不需要使用 filesort 或临时表，直接利用索引的有序性，直接返回排序后的结果集。具体来说，需要满足以下三个前提条件：

字段顺序匹配：ORDER BY 或 GROUP BY 后面的字段顺序，必须严格与联合索引的字段最左前缀顺序一致；且索引的字段顺序和排序方式（ASC/DESC），必须完全匹配 ORDER BY 条件中的所有字段的排序方式。
索引覆盖查询：查询所需的所有字段，都必须被覆盖索引包含 —— 否则，数据库需要回表获取完整的行数据，无法保证数据的顺序性，也就无法利用索引的有序性来完成排序。
非索引列条件不影响：WHERE 条件中，索引列的条件必须是常量条件或范围条件，不能包含 INDEX 列的非前缀字段的条件 —— 否则，优化器无法确定数据的顺序性，无法利用索引的有序性完成排序。

例如，对于一个高频的业务场景，对应的查询 SQL 语句是：SELECT user_id, create_time FROM orders WHERE shop_id = 100 ORDER BY create_time DESC;。这个查询的 WHERE 条件使用了 shop_id 的等值查询，ORDER BY 条件使用了 create_time 的降序排序。

针对这个场景，我们可以设计一个联合索引，将等值条件的 shop_id 放在最左侧，将范围条件的 create_time 放在后面，同时将查询所需的字段纳入到索引中：CREATE INDEX idx_shop_id_create_time_cover ON orders (shop_id, create_time DESC);。这样一来，索引的有序性就可以直接匹配 ORDER BY 的排序规则，数据库可以直接从索引树中按顺序读取数据，不需要再进行额外的排序操作。

4.4.3 补充说明

需要特别注意的是，在 ORDER BY 子句中，多列排序时的升降序规则必须与索引的定义完全匹配 —— 这一匹配规则是严格约束的，否则索引的有序性将无法被利用。

例如，如果联合索引定义为 idx_shop_id_create_time(shop_id ASC, create_time DESC)，那么只有 ORDER BY shop_id ASC, create_time DESC 和 ORDER BY shop_id DESC, create_time ASC 这两种排序方式，才能完全利用索引的有序性；如果查询的 ORDER BY 条件是 shop_id ASC, create_time ASC，或者 shop_id DESC, create_time DESC，则无法利用索引的有序性，数据库将不得不使用 filesort 进行排序。

4.5 优化字符串类查询

字符串类字段的查询是实际业务中非常常见的场景，也是优化的重点区域 —— 如果索引设计不合理，这类查询的性能往往会比较低，甚至导致全表扫描。

4.5.1 前缀索引优化长列

对于字符串类型的列（如 VARCHAR、TEXT），如果字段长度很长，对整个列值创建索引将导致索引的体积过大 —— 这会直接增加索引的维护成本，以及磁盘 I/O 的开销。

对于这类场景，最有效的优化方案是使用前缀索引：不索引完整的字符串值，只索引字段的前 N 个字符，在保证查询效率的前提下，大幅降低索引的存储空间占用和维护成本。前缀索引的核心设计目标，是在区分度和存储成本之间做平衡 —— 它既能大幅降低索引的存储空间占用，又能有效提升查询性能。

但是，前缀索引也存在使用限制：它无法匹配 LIKE '%后缀' 的模糊查询，也不能用于 ORDER BY 和 GROUP BY 场景；且前缀长度的选择，必须保证区分度 —— 如果区分度不够，索引的效率会大幅降低，甚至与全表扫描无差异。

4.5.2 模糊查询优化

对于 LIKE 模糊匹配的查询，有一个关键的性能规则：模糊匹配的前缀不能以通配符 % 开头。如果以通配符 % 开头，将导致索引失效，数据库直接进行全表扫描。

这是因为，B+Tree 的索引键值是按顺序存储的，LIKE 'abc%' 这类前缀匹配的场景，可以直接转化为范围扫描 —— 定位到前缀为 abc 的第一个索引键值，然后顺序遍历后续的索引叶子节点即可；但 LIKE '%abc%' 或 LIKE '%abc' 这类包含匹配或后缀匹配的场景，无法利用索引的有序性，只能进行全表扫描。

对于必须使用后缀匹配或包含匹配的业务场景，我们有两种优化方案：一是将这类查询场景，单独交给 Elasticsearch 等全文搜索引擎来承担 —— 这是最常用的优化方案；二是在不影响业务语义的前提下，将后缀匹配的逻辑反转过来进行存储，比如将 “abc123” 反转存储为 “321cba”，然后使用前缀匹配的方式进行查询。

4.5.3 避免字符串隐式转换

字符串类查询的另一类典型场景是隐式类型转换，这是非常容易被忽略的索引失效场景。当索引列是字符串类型（如 VARCHAR），但查询条件中的值没有使用引号包裹，或者与参数化查询的类型不匹配时，数据库就会自动对索引列的值应用 CAST() 函数，将其转换为另一种类型 —— 这会导致索引完全失效。

例如，card_code 字段是 VARCHAR 类型，且已经建了索引，但查询条件中写了 WHERE card_code = 123456—— 没有给数值加上单引号，这就会触发隐式类型转换，数据库会将 card_code 字段的所有值转换为数值，再与条件值进行比较；而对索引列使用函数操作，会直接导致索引失效。这类场景的规避方法是，保证查询条件的类型，与索引列的类型完全一致 —— 给字符串类型的条件值加上单引号。

4.6 其他优化策略

在实际业务中，还有几类针对特定场景的索引优化策略，虽然适用场景相对单一，但在适配特定业务场景时，能带来显著的性能收益。

4.6.1 选择合适的主键

主键是 InnoDB 聚簇索引的基础，主键的设计是否合理，直接决定了聚簇索引的性能表现。在设计主键时，需要遵循以下两条核心原则：

优先使用自增主键：InnoDB 的聚簇索引架构，决定了主键的插入顺序，直接影响数据页的存储效率和维护成本。如果采用非顺序的主键（如 UUID），会导致插入操作需要频繁分裂磁盘页，产生大量随机 I/O，性能开销极高。因此，应该优先使用自增主键（如 AUTO_INCREMENT）—— 自增主键保证了新插入的行数据，会按顺序追加到当前索引页的末尾，避免了数据页分裂和磁盘碎片的产生，插入性能极高。
避免使用过长的主键：InnoDB 的二级索引，会将主键值作为叶子节点的存储内容 —— 主键越长，二级索引的叶子节点占用的存储空间就越大，二级索引的体积也就越大，这会直接增加磁盘 I/O 的开销，降低查询性能。因此，应该优先使用 INT 或 BIGINT 类型的主键，避免使用过长的字符串类型作为主键。

4.6.2 关闭自适应哈希索引

InnoDB 的自适应哈希索引（AHI），是一种自动的优化功能，旨在提升等值查询的性能。但是，在实际的高并发场景下，比如热点数据的并发查询场景中，AHI 的维护开销，可能超过它带来的性能收益 —— 哈希表的资源竞争，会带来额外的 CPU 开销，甚至会导致整体性能下降。

对于这类场景，可以根据实际的监控数据，决定是否关闭 AHI—— 这一优化方向，需要基于严格的性能测试结果来决策。

4.6.3 优化 `IN` 和 `OR` 条件

对于 IN 和 OR 条件的查询，如果条件列表中的值较多，会导致 range 扫描的范围过大，性能下降；而如果条件列表中的值较少，优化器可以将其转化为常量条件，直接定位到对应的索引键值，性能表现会更好。

这类场景的优化逻辑是，将 IN 或 OR 查询的条件列表，控制在一个合理的范围内；如果条件列表中的值较多，可以考虑将查询条件，转化为一个临时表的连接查询 —— 这样可以让优化器更好地利用索引的有序性，提升查询性能。

5 索引性能分析与优化验证

在创建了索引后，并不意味着高枕无忧了 —— 索引设计是否合理、是否被 MySQL 实际使用、使用效率如何，都需要通过专门的手段来验证。本章将介绍几种核心的索引性能分析工具，以及验证方法，通过这些工具获取的真实数据，来判断索引的工作状态。

5.1 执行计划分析：EXPLAIN

EXPLAIN 是 MySQL 中最重要的索引分析工具 —— 通过在 SELECT 语句前添加 EXPLAIN 关键字，可以模拟优化器生成执行计划的过程，从而定位索引使用的问题根源。通过分析执行计划的结果，可以清晰地看到索引是否被实际使用、被使用的方式、以及查询的扫描范围。

EXPLAIN 的输出结果中，有四列是关键的性能指标，通过这四列的分析结果，可以判断索引的使用效率，以及整个查询的执行质量。这四列是：

type 列：连接类型，衡量查询扫描方式的直接指标，从最优到最差的顺序是：system > const > eq_ref > ref > range > index > ALL。其中 system 表示表仅有一行数据，为系统常量级查询；const 表示通过主键/唯一索引等值匹配；eq_ref 表示联表时通过主键/唯一索引精准匹配单行；ref 表示使用了非唯一索引的等值匹配；range 表示使用了索引的范围扫描；index 表示全索引扫描；最后 ALL 表示全表扫描，这是必须要优化的目标。
possible_keys 列：显示优化器在执行查询时，理论上有哪些索引可以被使用 —— 这些索引是优化器基于查询条件和可选索引列表，初步筛选出的候选索引集合；但在实际执行时，优化器不一定会使用这些候选索引。
key 列：显示 MySQL 在实际执行查询时，最终选择使用的索引名称 —— 如果这一列的值为 NULL，表示优化器没有使用任何索引，而是选择了全表扫描；如果 possible_keys 列有值，但 key 列值为 NULL，则说明索引失效了。
key_len 列：key_len 表示 MySQL 实际使用到的索引字节长度，用来判断联合索引是否用满、是否命中左前缀、是否使用了全部索引字段。
rows 列：显示优化器在统计数据的基础上，估算出的读取行数 —— 这一数值是优化器基于索引的分布统计信息估算出来的，并非实际执行时的真实扫描行数；但它可以直观地反映出查询的扫描范围：如果扫描的行数占表总行数的比例超过了 30%，说明索引的效率不高，需要进一步优化。
filter 列：filtered 是百分比，用于衡量查询条件对索引筛选后结果的过滤效率。filtered 值直观地表明了在存储引擎根据 key（实际使用的索引）找到数据后，还需通过 WHERE 子句中的剩余条件进一步过滤的行数比例。其值越高，意味着WHERE条件与索引选择越精准，过滤效果越好。
Extra 列：包含有关查询的附加信息，是判断索引优化效果的重要指标。如果该列的值为 Using index，表示查询使用了覆盖索引，不需要回表；如果该列的值为 Using where，表示服务器层需要过滤存储引擎层返回的行数据；如果该列的值为 Using filesort 或 Using temporary，表示查询需要进行额外的排序或分组操作，性能开销极大，是必须要优化的目标。

5.2 监控索引的实际使用情况

EXPLAIN 只能分析优化器模拟的执行计划，无法获取索引在实际业务中的真实使用状态 —— 在实际场景中，索引可能因为访问模式、统计信息过时等原因，没有被优化器正确选择，或者使用效率远低于预期。要获取这些真实数据，需要借助专门的监控工具，来跟踪索引的实际使用情况。

5.2.1 监控工具与 SQL

在 MySQL 中，有两个常用的工具集，可以用来监控索引的实际使用情况，获取优化器选择索引的详细信息：

sys 和 performance_schema 系统数据库：这是 MySQL 自带的数据库，包含了丰富的系统视图和统计数据，记录了索引的使用信息、以及整体的性能状态。其中，performance_schema.table_io_waits_summary_by_index_usage 视图，记录了每个索引的 I/O 等待事件统计数据，可以用来判断索引的使用频率和效率；sys.schema_unused_indexes 视图则可以直接找出那些从来没有被使用过的索引，帮助我们清理冗余的索引。
InnoDB 标准监视器（Standard Monitor） ：通过执行 SHOW ENGINE INNODB STATUS 命令，可以获取 InnoDB 存储引擎的内部状态信息，其中包含了自适应哈希索引的使用统计数据、Buffer Pool 的命中情况，以及索引的页分裂情况等关键信息 —— 这些信息可以用来判断索引的维护开销，以及实际的使用效率。

5.2.2 关键监控指标

在分析索引的实际使用情况时，需要重点关注以下三个关键指标，这些指标直接反映了索引的实际使用质量：

索引的使用频率：查询 performance_schema.table_io_waits_summary_by_index_usage 视图，查看每个索引的 COUNT_STAR 列 —— 这一列表示该索引被用于读取操作的总次数。如果一个索引的读取次数为 0，或远低于其他索引的读取次数，说明该索引的实际业务价值较低，甚至完全没有价值，可以考虑删除。
Buffer Pool 的命中率：通过 SHOW ENGINE INNODB STATUS 命令，可以查看 Buffer pool hit rate 的指标值 —— 这一指标表示，从 Buffer Pool 直接读取数据的请求占总请求的比例，是判断索引缓存效率的核心依据。正常情况下，这个值应该在 99% 以上；如果这个值低于 99%，说明内存中缓存的索引数据页太少，需要增加 Buffer Pool 的大小，或者优化索引的设计，减少索引的存储空间占用。
哈希索引的使用情况：同样通过 SHOW ENGINE INNODB STATUS 命令，可以查看 Adaptive hash index 部分的指标值 —— 这部分数据反映了自适应哈希索引的实际使用效率。如果自适应哈希索引的使用量很低，或未命中的比例很高，说明 AHI 并没有起到优化作用，可以考虑将其关闭。

5.3 查看索引元数据：SHOW INDEX

除了通过 EXPLAIN 分析查询的执行计划外，我们还需要直接了解表上索引本身的结构信息 —— 比如索引包含哪些列、索引的类型是什么、是否存在重复值等。SHOW INDEX 命令就是完成这一任务的专用工具，它能帮助我们验证索引的设计是否符合预期，也是诊断索引性能问题的重要辅助手段。

5.3.1 基本语法与输出列解读

SHOW INDEX 的基本语法如下：

SHOW INDEX FROM 表名 [FROM 数据库名];

也可以通过查询 INFORMATION_SCHEMA.STATISTICS 系统表获取相同的索引元数据，适合在脚本化运维场景中使用：

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your database' AND TABLE_NAME = 'your table';

SHOW INDEX 的输出结果中，以下几列是重点关注对象：

Non_unique：标识索引是否允许重复值。值为 0 表示唯一索引（包括主键），值为 1 表示普通索引。通过这一列可以快速确认唯一性约束是否正确生效。
Seq_in_index：索引列在联合索引中的位置序号，从 1 开始。联合索引会为每个列输出一行，通过这一列可以确认联合索引的字段顺序是否与设计一致。
Column_name：索引列的名称。结合 Seq_in_index 列，可以清晰地还原联合索引的完整字段列表和排列顺序。
Collation：索引列的排序方式。A 表示升序（ASC），D 表示降序（DESC），NULL 表示不适用（如哈希索引）。这是验证索引排序规则是否匹配 ORDER BY 条件的关键依据。
Cardinality：索引的基数，即索引中不重复值的估算数量。这个值是 InnoDB 基于采样统计得出的估算值，并非精确值，但它是优化器判断索引选择性的核心依据 —— Cardinality / 表总行数 越接近 1，说明索引的区分度越高，优化器越倾向于选择该索引。如果这个值与实际情况偏差过大，会导致优化器做出错误的索引选择，此时需要执行 ANALYZE TABLE 更新统计信息。
Sub_part：前缀索引的索引长度。如果索引覆盖了完整的列值，该列为 NULL；如果使用了前缀索引（如 VARCHAR(255) 只索引前 20 个字符），则该列显示被索引的前缀字节数。通过这一列可以验证前缀索引的长度设置是否正确。
Index_type：索引的底层实现类型。InnoDB 中最常见的是 BTREE，此外还可能看到 FULLTEXT（全文索引）、SPATIAL（空间索引）等。

5.3.2 典型使用场景

SHOW INDEX 在索引设计和诊断中的典型使用场景包括：

验证联合索引的字段顺序：在创建联合索引后，立即执行 SHOW INDEX 确认 Seq_in_index 和 Column_name 的对应关系是否符合设计预期 —— 这可以避免因建表语句书写失误，导致索引字段顺序错误的问题。
诊断优化器索引选择异常：当 EXPLAIN 显示优化器没有选择预期的高效索引时，可以通过 SHOW INDEX 查看 Cardinality 列的值 —— 如果该值明显偏低（比如区分度很高的列却显示很小的基数），说明统计信息过时，优化器基于错误的统计信息做出了次优选择，需要执行 ANALYZE TABLE 更新统计信息。
确认前缀索引的实际长度：对于前缀索引，通过 Sub_part 列可以确认实际索引的字节数，判断前缀长度是否足够保证区分度。

5.4 识别无效索引与冗余索引

数据库中的无效索引和冗余索引太多，会严重影响写入性能，浪费磁盘空间 —— 这是索引设计中常见的遗留问题。定期识别并清理这类索引，是数据库性能维护的重要任务，也是保证数据库高性能的基础。

5.4.1 无效索引识别

无效索引是指那些从来没有被使用过，或使用概率极低的索引。这类索引无法提升查询性能，反而会降低写入性能。在 MySQL 中，可以通过 sys.schema_unused_indexes 视图，直接找出这类无效索引 —— 这个视图的统计数据，是 MySQL 基于索引的使用记录自动生成的，记录了所有没有被使用过的索引。

需要注意的是，这个视图的结果，仅反映了从上次重启到现在的使用情况；要准确识别无效索引，需要结合业务的完整访问周期（如至少覆盖一个完整的业务高峰时段），再进行判断 —— 避免误删除那些在特定时段，或特定业务场景下才会被使用的索引。

5.4.2 冗余索引识别

冗余索引是指那些被其他索引的最左前缀部分包含、覆盖相同查询场景，且维护成本更高的索引。例如，如果已经有了联合索引 idx_a_b_c(a, b, c)，那么单独创建的索引 idx_a(a) 或 idx_a_b(a, b) 就是冗余索引 —— 因为前者的最左前缀部分，已经可以覆盖后两者的查询场景。

在 MySQL 中，可以通过 sys.schema_redundant_indexes 视图，或第三方的优化工具（如 Percona Toolkit），来识别这类冗余索引。

5.4.3 重复索引识别

重复索引是指在同一个表的同一个列组合上，创建了多个不同名称的索引 —— 这类索引完全多余，没有任何业务价值，反而会浪费磁盘空间，大幅增加写入操作的维护成本。例如，在 shop_id 和 order_status 列上，同时创建了 idx_shop_status 和 idx_status_shop 两个索引 —— 这两个索引覆盖了完全相同的查询场景，但维护成本是双倍的。

这类索引通常是研发或运维人员误操作导致的，可以通过查询 INFORMATION_SCHEMA.STATISTICS 系统表，来识别这类重复索引。

5.5 索引统计信息维护与表空间优化

在识别并清理了无效索引和冗余索引后，还需要关注两个影响索引性能的底层因素：统计信息的准确性和表空间的物理碎片。索引的统计信息直接决定了优化器的索引选择策略，而表空间的碎片化则会影响索引的磁盘 I/O 效率。MySQL 提供了 ANALYZE TABLE 和 OPTIMIZE TABLE 两条命令，分别用于解决这两个问题。

5.5.1 更新索引统计信息：ANALYZE TABLE

为什么需要更新统计信息

InnoDB 的优化器在选择索引时，并非逐行扫描数据来判断哪个索引更高效，而是依赖预先收集的统计信息 —— 其中最核心的指标就是索引的基数（Cardinality），即索引列中不重复值的估算数量。优化器基于 Cardinality 与表总行数的比值，估算索引的选择性，进而计算不同执行计划的成本，最终选择成本最低的方案。

这些统计信息是 InnoDB 通过对索引页的随机采样得出的估算值，而非精确值。在以下场景中，统计信息可能与实际数据分布产生较大偏差：

大批量数据导入后，统计信息尚未更新；
大量数据被删除或更新后，索引的数据分布发生了显著变化；
表的数据量持续增长，但统计信息的采样仍基于旧的数据规模。

统计信息过时会导致优化器做出错误的索引选择 —— 比如选择了区分度低的索引而放弃高效索引，或者直接放弃索引选择全表扫描。这类问题在 EXPLAIN 中的典型表现是：possible_keys 列包含了预期的高效索引，但 key 列却显示优化器选择了其他索引或全表扫描。

基本语法

ANALYZE TABLE 表名 [, 表名 ...];

ANALYZE TABLE 会重新对表的索引进行采样，更新索引的 Cardinality 等统计信息。执行完成后，可以通过 SHOW INDEX 命令查看更新后的 Cardinality 值。

执行注意事项

ANALYZE TABLE 在执行期间会对表加读锁（READ LOCK），不会阻塞读取操作，但会阻塞写入操作。因此，应该在业务低峰期执行，避免影响线上写入性能。
对于大表而言，ANALYZE TABLE 的执行时间可能较长，因为 InnoDB 需要扫描一定数量的索引页来完成采样。InnoDB 默认的采样页数为 20（由参数 innodb_stats_persistent_sample_pages 控制），可以在精度和执行时间之间做权衡 —— 适当增大采样页数可以提升统计信息的准确性，但会延长执行时间。
InnoDB 提供了两种统计信息的存储方式：持久化统计信息（innodb_stats_persistent = ON，默认值）和非持久化统计信息（innodb_stats_persistent = OFF）。持久化统计信息存储在磁盘上，数据库重启后不会丢失；非持久化统计信息存储在内存中，数据库重启后会自动重新计算。生产环境建议使用持久化统计信息，以保证统计信息的稳定性。
在以下场景中，建议主动执行 ANALYZE TABLE：大批量数据导入后、大量数据删除或更新后、发现优化器的索引选择明显异常时。

5.5.2 回收表空间碎片：OPTIMIZE TABLE

为什么需要回收碎片

InnoDB 的聚簇索引架构决定了数据行按主键顺序物理存储。在频繁的 INSERT、UPDATE、DELETE 操作后，数据页中会产生大量碎片空间：

删除操作会在数据页中留下空洞，但 InnoDB 不会自动将空间归还给操作系统；
更新操作如果导致行数据变长，可能使行被移动到新的数据页，原位置留下空洞；
非顺序的主键插入（如 UUID）会导致数据页频繁分裂，产生大量页内碎片。

这些碎片会导致两个核心性能问题：一是索引扫描时需要读取更多的数据页，增加磁盘 I/O 开销 —— 因为有效数据散布在更多的页面中，每个页面中都有大量空闲空间；二是二级索引存储的主键值在物理上是分散的，回表操作会产生更多随机 I/O。

基本语法

OPTIMIZE TABLE 表名 [, 表名 ...];

对于 InnoDB 表，OPTIMIZE TABLE 的实际执行逻辑是重建表（ALTER TABLE ... FORCE）—— 它会创建一个新的表空间，将原表中的数据按主键顺序重新写入，然后删除旧的表空间。重建后的表，数据和索引在物理上是连续紧凑的，碎片空间被完全回收。

执行注意事项

OPTIMIZE TABLE 在执行期间会对表加写锁（WRITE LOCK），会阻塞所有读写操作。因此，必须在业务低峰期执行，或使用在线 DDL 工具（如 gh-ost 或 pt-online-schema-change）来避免锁表。
对于大表，OPTIMIZE TABLE 的执行时间可能非常长，且需要额外的磁盘空间来存储重建后的临时表 —— 确保磁盘剩余空间至少为表大小的 1.5 倍后再执行。
OPTIMIZE TABLE 会自动触发 ANALYZE TABLE，重建完成后会更新索引的统计信息，无需额外执行。
在以下场景中，建议主动执行 OPTIMIZE TABLE：大量数据删除后（尤其是删除了表中大部分数据）、频繁更新导致的表空间膨胀、索引扫描性能明显下降时。可以通过查询 INFORMATION_SCHEMA.TABLES 视图的 DATA_FREE 列，来判断表的碎片空间大小 —— 如果 DATA_FREE 占 DATA_LENGTH 的比例超过 20%，说明碎片化较严重，建议执行优化。

5.6 索引优化验证流程

在完成索引优化后，需要通过科学的验证流程，确认优化的实际收益 —— 这是保证优化效果的关键环节，不能仅依赖开发环境的测试数据。验证的核心指标是优化前后的查询执行时间、扫描行数、返回行数，以及磁盘 I/O 的开销对比。

完整的优化验证流程，分为以下四个核心步骤：

采集优化前的性能基线数据：在优化前，使用 EXPLAIN ANALYZE（MySQL 8.0.18 及以上版本支持）或 PROFILER 工具，获取查询的实际执行时间、磁盘 I/O 的读取量、扫描的行数、Buffer Pool 的命中情况等关键指标数据，作为性能对比的基线数据。
优化索引设计：根据执行计划的分析结果，对索引进行优化，比如新建联合索引、删除冗余索引、调整联合索引的字段顺序、或修改前缀索引的前缀长度。
采集优化后的性能数据并对比：优化完成后，使用同样的工具，再次采集查询的实际执行时间、磁盘 I/O 的读取量、扫描的行数等指标数据；将优化后的这些数据，与之前的性能基线数据进行对比。
确认优化效果：如果查询的执行时间、磁盘 I/O 的读取量、扫描的行数，都下降到了业务可接受的阈值以下，且没有对其他业务查询产生负面影响，就说明优化效果达到了预期；反之，如果这些指标的下降幅度不明显，甚至没有下降，则需要重新分析执行计划，调整索引的设计方案。

6 索引失效场景

在使用索引时，有一些典型的场景，会导致索引失效，或无法充分利用索引的优势 —— 这些场景是实际工作中最容易踩的坑，也是导致慢查询的核心根源。理解这些失效场景的底层逻辑，可以帮助我们写出更高效的 SQL，设计更合理的索引，避免这些典型的性能坑。

6.1 违反最左匹配原则

这是联合索引最常见的失效场景。

联合索引的 B+Tree 中的键值，是严格按照索引定义的字段顺序拼接而成的；查询条件必须匹配索引的最左 N 个连续字段，才能让联合索引生效；如果 WHERE 条件中跳过了前序的列，联合索引的有序性就无法被充分利用，甚至会直接失效。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1
where a=1 and b=2 and c=3
where a=1 and b=2

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

但是注意，因为查询优化器的存在，所以 WHERE 字段的书写顺序并不重要。

6.2 在索引列上使用函数或进行计算

索引的有序性是基于列值本身的 —— 如果在查询条件中，对索引列使用了函数操作，或进行了计算，MySQL 的优化器就无法确定索引键值的顺序，也就无法利用索引的有序性来完成查询；

这类场景的典型案例有两种，分别是对索引列使用内置函数，以及对索引列进行数学计算：

场景 1：对索引列使用内置函数：例如，查询条件是 WHERE YEAR(create_time) = 2024—— 在索引列 create_time 上，使用了 YEAR() 函数，这会导致索引失效。
场景 2：对索引列进行数学计算：例如，查询条件是 WHERE id + 1 = 100—— 在索引列 id 上，进行了加法计算，这同样会导致索引失效。

这类场景的规避方案，是将对列的计算改为对值的计算，保证索引列本身不会被修改，保留索引的有序性。上述两个案例的有效规避方案如下：

对 YEAR(create_time) = 2024 的场景，将查询条件改为 WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'—— 将函数操作，转化为对值的范围查询。
对 id + 1 = 100 的场景，将查询条件改为 WHERE id = 99—— 将加法计算，转化为对值的等值查询。

6.3 模糊查询以通配符 `%` 开头

B+Tree 索引的键值是按顺序存储的，LIKE 'abc%' 这类前缀匹配的场景，可以直接转化为范围扫描；但 LIKE '%abc' 或 LIKE '%abc%' 这类后缀匹配或包含匹配的场景，无法利用索引的有序性 —— 优化器无法确定索引键值的扫描范围，只能进行全表扫描。

这类情况其实本质和「违反最左匹配原则」是类似的。

这类场景的规避方案有两种，分别是优化查询逻辑和使用额外的索引类型，将后缀匹配或包含匹配的场景，转化为前缀匹配的场景：

方案 1：调整查询条件，将后缀匹配转化为前缀匹配：在业务允许的情况下，改变查询逻辑，比如将 LIKE '%张三' 改为 LIKE '张三%'，可以恢复索引的使用。
方案 2：使用全文索引：对于长文本的包含匹配场景，可以创建 FULLTEXT 全文索引，然后使用 MATCH() 函数进行搜索 —— 替代低效的 LIKE 模糊匹配。
方案 3：使用搜索引擎：对于大数据量的后缀匹配或包含匹配场景，可以将这类查询场景，单独交给 Elasticsearch 等全文搜索引擎来承担 —— 这是最常用的优化方案。

6.4 隐式类型转换

当查询条件的数据类型，与索引列的数据类型不匹配时，为了完成数据类型的匹配，MySQL 会自动对索引列的值应用 CAST() 函数 —— 将其转换为另一种类型；而对索引列使用函数操作，会直接破坏索引的有序性，导致索引失效。

这类场景的典型失效案例，是索引列是字符串类型，但查询条件中的值没有使用引号包裹。例如，card_code 字段是 VARCHAR 类型，且已经建了索引，但查询条件中写了 WHERE card_code = 123456—— 没有给数值加上单引号，这就会触发隐式类型转换，MySQL 会将 card_code 字段的所有值转换为数值，再与条件值进行比较；而对索引列使用函数操作，会直接导致索引失效。

6.5 范围查询后的列索引失效

在联合索引中，范围查询列（如 >、<）会截断其右侧列的索引使用 —— 这是因为，范围查询后的列无法保证索引的有序性：联合索引是按照前 N 个列排序的，前一个列用了范围查询后，后面的列的顺序是随机的，优化器无法利用后续列的索引有序性。

比如 WHERE a > 1 and b = 2 这个查询条件，由于联合索引先按照 a 字段的值排序，所以符合 a > 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a > 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的（这个例子中是 4 5 7 6 8 2），所以 b 字段并不会使用到联合索引。

但是对于 WHERE a >= 1 and b = 2 的这种查询条件，b 字段是可以用到联合索引的，因为在 a = 1 的条件下，b 字段是有序的，类似的下面的查询条件中，b 字段都可以用到联合索引：

WHERE a <= 10 AND b = 2
WHERE a BETWEEN 1 AND 10 AND b = 2 等价于 WHERE a >= 1 and a <= 10 and b = 2
WHERE a LIKE 'xx%' and b = 2

6.6 其他索引失效场景

除了上述五类典型的失效场景外，还有一些失效场景，虽然出现概率较低，但也需要在开发过程中注意规避。

6.6.1 优化器放弃使用索引

在某些场景下，虽然索引可以被正常使用，但优化器会认为 “全表扫描比使用索引更快”，从而选择不使用索引，而是进行全表扫描。这类场景的典型情况有两种：

表中的数据量很少，全表扫描的速度比使用索引的速度快；
查询条件使用 OR，但其中一个条件的列没有索引，优化器只能放弃使用索引，进行全表扫描。

6.6.2 否定条件查询

使用否定条件的查询，例如使用 !=、<>、NOT IN、NOT LIKE 等操作符的查询，大部分场景下都无法使用索引，会直接进行全表扫描 —— 这是因为，索引的本质是快速定位到符合条件的少量数据，而否定条件，往往会导致需要扫描大部分数据，甚至全部数据；在这类场景下，优化器会选择全表扫描而非使用索引。

6.6.3 索引的选择性过低

索引的选择性（区分度）是影响索引效率的核心因素。如果索引的选择性很低，比如 gender 或 order_status 这类区分度低的列，即使创建了索引，优化器也很可能最终选择全表扫描而非使用索引 —— 因为低选择性的索引，无法有效过滤数据，使用索引的成本，可能比全表扫描更高。

7 总结

索引是一把 “双刃剑”：正确使用，可以将查询性能提升数倍；但如果使用不当，或设计不合理，它带来的写入性能开销，反而会比它带来的查询性能收益更高，严重影响数据库的整体性能。在设计和优化索引时，需要平衡查询性能和写入性能的收益与成本 —— 这意味着，不能为了优化查询，而过度创建索引，也不能为了节省存储空间，而不创建必要的索引。