《MySQL 索引：底层原理、常见问题及优化实践》

关于 MySQL 索引，以下是更深入的补充内容，涵盖底层原理、常见问题及优化实践，帮助你更全面理解和应用索引：

一、索引的数据结构

MySQL 索引主要基于两种数据结构实现，不同存储引擎选择不同结构：

1. B + 树索引（最常用）

• 结构特点：非叶子节点存储索引键和指向子节点的指针，叶子节点存储数据（InnoDB）或数据指针（MyISAM）。所有叶子节点通过双向链表连接，便于范围查询（如BETWEEN、ORDER BY）。
• 适用场景：全值匹配（如WHERE col = 'xxx'）、范围查询、排序、分组（ORDER BY/GROUP BY）。
• 为什么 MySQL 选择 B + 树？相比二叉树，层级更少，查询效率更稳定（时间复杂度接近O(logN)）。叶子节点有序连接，范围查询更高效。

2. 哈希索引（Memory 引擎默认）

• 结构特点：通过哈希表实现，索引键与数据地址通过哈希函数映射。
• 适用场景：等值查询（如WHERE col = 'xxx'）极快，但不支持范围查询、排序。
• 限制：InnoDB 仅在内存临时表中自动使用哈希索引（如ORDER BY + GROUP BY场景），无法手动创建。

3. 全文索引（Full-Text Index）

• 适用场景：针对TEXT/VARCHAR类型的全文搜索（如MATCH AGAINST），基于倒排索引实现。
• 注意：MySQL 5.6 + 版本 InnoDB 支持全文索引，早期版本仅 MyISAM 支持。

二、索引失效的常见场景

即使创建了索引，某些操作可能导致索引无法被使用，需特别注意：

1. 对索引列使用函数或表达式

2. 隐式类型转换

3. 使用OR连接非索引列

4. like 以通配符开头

5. 组合索引未遵循最左前缀原则

• 组合索引(a, b, c)支持的查询模式：a、a+b、a+b+c、a+c（仅 a 使用索引，c 不生效）。

6. 数据分布不均（索引选择性低）

• 若某列值重复率极高（如status列只有0和1），索引可能不如全表扫描高效。
• 可通过SELECT COUNT(DISTINCT col)/COUNT(*) AS 选择性评估，选择性接近 1 时索引效果好。

三、索引优化实践

1. 使用EXPLAIN分析查询计划

• 关键字段：type：连接类型，最优为const（主键命中），最差为ALL（全表扫描）。key：实际使用的索引名称，若为NULL则未使用索引。rows：预估扫描的行数，越小越好。

2. 覆盖索引（Index Covering）

• 若查询所需字段都在索引中，无需回表查询数据，提升效率。

3. 避免冗余索引

• 若已有组合索引(a, b)，无需单独为a创建索引（组合索引已包含最左列）。

4. 批量操作时禁用索引

• 插入 / 更新大量数据前，先删除索引，操作完成后重建，减少索引更新开销：

5. 关注慢查询日志

• 开启慢查询日志（slow_query_log），定位未使用索引的慢查询，针对性优化。

四、不同存储引擎的索引差异

1. InnoDB

• 聚簇索引（Clustered Index）：主键索引的叶子节点直接存储行数据，辅助索引（非主键索引）的叶子节点存储主键值，查询时需通过主键回表。
• 必须有主键：若未显式定义主键，InnoDB 会自动生成隐藏主键（6字节长整型）。

2. MyISAM

• 非聚簇索引：所有索引的叶子节点均存储数据指针（指向磁盘物理地址），主键索引与辅助索引结构一致。
• 允许无主键：可通过INDEX指定非主键索引为主索引。

五、索引设计原则总结

1. 优先为高频查询字段建索引：如WHERE、JOIN、ORDER BY、GROUP BY涉及的列。
2. 组合索引按查询频率排序：将高频条件列放在组合索引左侧（最左前缀原则）。
3. 避免过度索引：每个表的索引数建议不超过 5 个，索引会增加写操作成本和存储空间。
4. 定期维护索引：通过ANALYZE TABLE更新统计信息，让优化器生成更优执行计划。

通过合理设计索引，可显著提升数据库查询性能，但需结合具体业务场景权衡利弊。遇到索引不生效问题时，优先通过EXPLAIN分析执行计划，再针对性调整查询或索引结构。