MySQL 索引技术指南
索引简介
索引是数据库中用于提升查询效率的重要数据结构，类似于书籍的目录，能够快速定位所需内容。在数据量较小时，索引的影响可能不明显，但随着数据量增加，合理的索引设计成为优化查询性能的关键。
索引的优缺点
优点：
- 减少服务器扫描的数据量，加快检索速度。
- 避免排序和临时表的使用。
- 将随机 I/O 转换为顺序 I/O。
- 减少锁竞争，提升并发性能（支持行级锁的引擎如 InnoDB）。
- 确保数据唯一性并优化查询性能。
缺点：
- 创建和维护索引耗费时间，随数据量增加而增加。
- 占用额外物理空间，尤其是组合索引。
- 写操作（如 INSERT、UPDATE、DELETE）需更新索引，降低写性能。
基本规则：
- 避免过多索引，按需创建。
- 避免冗余和重复索引。
- 删除未使用的索引。
- 扩展现有索引而非新建。
- 为频繁用于 WHERE 条件的列添加索引。
何时使用索引
适用场景：
- 字段具有唯一性约束（如用户名）。
- 频繁用于 WHERE 或 JOIN 条件。
- 常用于 GROUP BY 或 ORDER BY。
- DISTINCT 字段。
不适用场景：
- 频繁写操作。
- 很少用于条件查询的字段。
- 小型表（全表扫描更高效）。
- 超大型表（维护代价高，可考虑分区或 NoSQL）。
索引的数据结构
MySQL 的索引在存储引擎层实现，不同引擎支持的索引数据结构各异：
| 数据结构 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree 索引 | ✔️ | ✔️ | ✔️ |
| Hash 索引 | ❌ | ❌ | ✔️ |
| Full Text 索引 | ✔️ | ✔️ | ❌ |
有序数组
有序数组支持二分查找，查询复杂度为 O(log n)，适合等值和范围查询。但其固定大小和插入/删除操作的高代价（O(n)）使其不适合作为索引。
哈希索引
哈希索引基于哈希表，通过哈希函数将键映射为数组下标，实现 O(1) 的查询效率，仅适用于等值查询。
优点：
- 查询速度快，结构紧凑。
缺点：
- 不支持范围查询和模糊查询。
- 无法用于排序。
- 不支持联合索引的最左原则。
- 哈希冲突增加维护代价。
适用场景： Memory 引擎中特定等值查询场景。
B+ 树索引
B+ 树是 MySQL 最常用的索引类型，数据按顺序存储，支持 ORDER BY 和 GROUP BY，且部分查询可仅依赖索引完成。
二叉搜索树
二叉搜索树查询复杂度为 O(log n)，但深度过大时，磁盘 I/O 成本高昂。
B+ 树特性
B+ 树是多路搜索树，降低树高，减少 I/O：
- 所有数据存储在叶子节点，非叶子节点仅存键值。
- 叶子节点通过指针连接，支持顺序访问。
聚簇索引与非聚簇索引
- 聚簇索引： 主键索引，叶子节点存整行数据，一个表只能有一个（如 InnoDB）。
- 非聚簇索引： 二级索引，叶子节点存主键值，查询需“回表”。
查询区别：
- 聚簇索引查询仅遍历主键树。
- 非聚簇索引需先查索引树，再回表。
主键选择：
- 有主键时，默认为主键。
- 无主键时，选择第一个非 NULL 唯一列。
- 否则，生成隐式自增 ID。
全文索引
全文索引用于关键词搜索，基于倒排索引实现，适用于 MyISAM 和 InnoDB（5.6.4+），配合 MATCH AGAINST 使用。
空间数据索引
MyISAM 支持 R-Tree 索引，适用于地理数据，支持多维度查询。
索引类型
主键索引
唯一且非空，通常在建表时定义：
CREATE TABLE user (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);
唯一索引
确保值唯一，可为组合索引：
CREATE UNIQUE INDEX uniq_name ON user(name);
普通索引
无特殊限制：
CREATE INDEX idx_name ON user(name);
前缀索引
针对长字符列，仅索引前缀：
CREATE INDEX idx_name ON user(name(10));
优点： 节省空间。
缺点： 降低区分度，无法用于排序或覆盖索引。
长度选择：
SELECT COUNT(DISTINCT LEFT(name, 6)) / COUNT(*) AS selectivity FROM user;
选择区分度损失小于 5% 的长度。
索引优化策略
基本原则
- 按需创建索引，避免过多。
- 避免冗余索引。
- 删除无用索引。
- 扩展现有索引。
覆盖索引
索引包含查询所需字段，避免回表：
SELECT ID FROM T WHERE k BETWEEN 3 AND 5;
减少搜索次数，提升性能。
最左匹配原则
联合索引按最左前缀匹配，列顺序影响命中率，选择性高的列优先。
使用索引排序
索引顺序满足排序需求，避免额外排序操作。
= 和 IN 的优化
MySQL 自动调整 = 和 IN 的顺序以匹配索引。
索引失效场景
- 左模糊匹配： LIKE %xx 无法使用 B+ 树前缀比较。
- 函数/表达式： 对索引列操作导致无法匹配原始值。
- 隐式类型转换： 如 CAST 导致函数失效。
- 不遵循最左原则： 联合索引未从最左列开始。
- 判空： NULL 判断不利用索引。
- OR 条件含非索引列： 导致全表扫描。
示例：
SELECT comment_id FROM product_comment WHERE comment_id = 900001 OR comment_text = 'text';
若 comment_text 无索引，则全表扫描；创建索引后使用 index merge 优化。
结语
合理设计和使用索引是 MySQL 性能优化的核心。通过理解数据结构、索引类型及优化策略，可显著提升查询效率，同时避免常见失效场景。