MySQL 索引详解：B+树原理、使用规则与优化策略

MySQL 索引类型与核心优势

在 MySQL 中，常见的索引结构主要包括 B+Tree（通常简称 B-Tree）和 Hash 索引。理解它们的差异是进行 SQL 优化的基础。

为什么需要索引？

• 减少扫描数据量：索引文件通常比数据表小得多，通过索引定位记录可大幅降低 I/O 次数。
• 加速排序：有序的索引结构（如 B+Tree）允许数据库直接读取有序数据，避免使用临时表进行 file sort。
• I/O 优化：将随机 I/O 转化为顺序 I/O，显著提升磁盘读取效率。

注意：索引并非越多越好。虽然能加速查询，但会降低写入（INSERT/UPDATE/DELETE）速度，因为每次数据变更都需维护索引结构；同时，过多的索引也会增加查询优化器的选择成本。

B+Tree 索引的使用规则与限制

InnoDB 引擎默认使用 B+Tree 索引。它能高效支持等值查询、范围查询以及排序操作，但遵循严格的匹配规则。

1. 全值匹配与最左前缀法则

对于联合索引（例如在 (userId, date) 上创建的索引），查询条件必须从索引的最左边列开始匹配，不能跳过中间的列。

• 有效利用：
  • 仅指定 userId：可以使用索引。
  • 指定 userId 和 date：可以使用索引。
• 无法利用：
• 仅指定 date：由于跳过了左列 userId，无法直接使用联合索引。
• 指定 orderId 和 createTime 的联合索引时，若只查 createTime，同样失效。

2. 精确匹配与部分匹配

• 匹配最左前缀：如果查询条件是列的完整值或前缀（如字符串 LIKE '134%' ），且符合最左原则，可使用索引。
• 范围查询的影响：如果查询中包含某个列的范围查询（如 >, <, BETWEEN），则该列右侧的所有列将无法继续使用该索引进行查找，但右侧列仍可用于排序或覆盖索引检查。

  示例：userId=1 AND createTime > '2016-09-18'

3. 覆盖索引（Covering Index）

当查询所需的列全部包含在索引结构中时，称为“覆盖索引”。此时数据库无需回表查询主键行数据，直接从索引树中获取结果，性能极高。

4. 不支持索引的情况

• NOT IN 和 <> 操作通常会导致全表扫描，无法有效使用 B+Tree 索引。
• 对索引列使用函数或表达式（见下文优化策略）。

Hash 索引的特点与局限

Hash 索引基于哈希表实现，仅在某些存储引擎（如 Memory、NDB）或部分特定配置下可用。它不适合通用场景，主要特点如下：

工作原理

存储引擎为每行数据计算一个哈希码（Hash Code），索引中存储的是该哈希码而非实际数据。

限制条件

• 仅支持等值查询：只能用于 = 条件的精确匹配，不支持 >, < 等范围查询，也不支持部分字符串匹配。
• 无法排序：哈希值是无序的，因此 Hash 索引不能用于 ORDER BY 操作。
• 额外 I/O 开销：查询时需先读取哈希值找到指针，再回表读取完整行数据，通常需要两次读取。
• 哈希冲突：不同数据可能产生相同的哈希值，需要通过链表或桶解决冲突，这在选择性差的字段（如性别字段）上表现极差。

最佳实践

Hash 索引应仅创建在高选择性（Unique Value / Total Rows 比值高）的列上，避免在重复率高的字段上使用。

建立索引的策略与优化技巧

1. 避免在索引列上使用函数或表达式

如果在 WHERE 子句中对索引列进行了计算，索引将失效。

• 错误示例：SELECT * FROM product WHERE to_days(out_date) - to_days(CURDATE()) <= 30;
• 正确改写：SELECT * FROM product WHERE out_date < DATE_ADD(CURDATE(), INTERVAL 30 DAY);
  通过将计算移至比较值的另一侧，保持索引列原始形态。

2. 控制索引大小与前缀索引

• 长度限制：InnoDB 对单列索引长度有限制（传统 InnoDB 约为 767 字节，UTF8MB4 环境下更需谨慎，虽新版有所放宽但仍需注意页大小限制）。
• 前缀索引：对于长字符串字段（如 VARCHAR(255)），不需要对所有字符建立索引。可以创建前缀索引，例如 INDEX idx_name (account(10));。这需要在索引长度与区分度（选择性）之间寻找平衡。

3. 联合索引的列顺序选择

在设计联合索引 (A, B, C) 时，列的顺序至关重要。一般遵循以下优先级：

1. 高选择性优先：能将数据迅速缩小范围的列放在前面。
2. 频繁查询优先：经常出现在 WHERE 子句中的列。
3. 等值查询优先于范围查询：如果 A 列用于等值查询 =，B 列用于范围查询 >，应将 A 放在 B 之前。因为一旦遇到范围查询，后续列的索引过滤能力会减弱。
4. 短列优先：在满足上述条件的基础上，较短的数据类型有助于索引更小，从而容纳更多索引项到内存中。

以上就是 MySQL 索引详细介绍的详细内容，更多关于 MySQL 优化资料的关注本站其它相关文章！