在 MySQL 中建索引时需要注意哪些事项？

MySQL 建索引注意事项

【核心定义】

在 MySQL 中创建索引时，需要综合考虑查询性能、数据更新开销、存储成本以及索引特性，通过合理的索引设计来优化数据库的整体性能。

【关键要点】

选择性原则：优先为高选择性的列创建索引，即该列不同值的数量与总行数的比例较高，避免为低选择性列（如性别、状态标志）创建独立索引。
最左前缀匹配：联合索引遵循最左前缀原则，查询条件必须包含索引的最左列才能有效利用索引，设计时应将最常用的查询列放在最左侧。
覆盖索引优化：尽量让索引包含查询所需的所有列（覆盖索引），避免回表操作，显著提升查询性能。
避免过度索引：每个索引都会增加写操作（INSERT/UPDATE/DELETE）的开销和存储空间，需要平衡读写比例。
索引列独立：避免在索引列上使用函数或表达式，如 WHERE YEAR(create_time) = 2023 无法使用 create_time 索引。
前缀索引使用：对于 TEXT/BLOB 或很长的 VARCHAR 列，可使用前缀索引减少索引大小，但需评估前缀长度的选择性。
外键自动索引：InnoDB 会自动为外键约束创建索引，无需手动重复创建。

【深度推导/细节】

索引数据结构选择

B+Tree 索引：MySQL 默认索引类型，适用于全键值、键值范围、键前缀查找，支持排序和分组操作。
Hash 索引：仅适用于等值查询，不支持范围查询和排序，Memory 引擎默认使用。
Full-Text 索引：用于全文搜索，基于倒排索引实现。
R-Tree 索引：用于空间数据查询。

联合索引设计逻辑

场景：查询 WHERE a = 1 AND b > 2 ORDER BY c

错误设计：INDEX(a, c, b) - 范围查询 b > 2 后无法使用 c 排序
正确设计：INDEX(a, b, c) - 等值查询 a 后使用 b 过滤，结果已按 c 有序

索引失效的临界情况

数据类型隐式转换：WHERE string_col = 123 会导致索引失效
OR 条件不当：WHERE indexed_col = 1 OR non_indexed_col = 2 会导致全表扫描
LIKE 通配符前置：WHERE col LIKE '%pattern' 无法使用索引

【关联/对比】

MySQL 不同存储引擎索引差异

特性	InnoDB	MyISAM
聚簇索引	主键索引即数据文件	索引与数据分离
辅助索引	存储主键值	存储数据行地址
事务支持	支持	不支持
外键索引	自动创建	不支持

索引 vs 分区

索引：针对查询条件的加速，粒度更细
分区：将表物理分割，适用于数据量极大且有明显分区键的场景
最佳实践：通常先优化索引，数据量极大时再考虑分区

『面试官追问』

Q1：为什么 MySQL 默认使用 B+Tree 而不是 B-Tree 或 Hash？

答：B+Tree 相比 B-Tree 的非叶子节点不存储数据，使得树的高度更低，一次磁盘 I/O 能读取更多索引键。相比 Hash 索引，B+Tree 支持范围查询和排序，更符合数据库的通用查询需求。

Q2：如何判断一个索引是否被使用？

答：可通过以下方式：

EXPLAIN 查看执行计划，观察 key 字段
开启慢查询日志分析
使用 SHOW INDEX FROM table_name 查看索引基数
查询 INFORMATION_SCHEMA.STATISTICS 表

Q3：什么情况下应该删除索引？

答：

索引从未被查询使用（通过性能模式监控）
索引选择性极低（如布尔字段）
存在冗余索引（如已有 (a,b) 索引又创建 (a) 索引）
表数据量很小（全表扫描更快）

Q4：自增主键 vs UUID 主键对索引性能的影响？

答：

自增主键：插入时顺序写入，减少页分裂，聚簇索引效率高
UUID：随机写入导致页分裂频繁，插入性能下降约 30-50%，且索引占用空间更大
建议：无特殊需求优先使用自增主键，分布式场景可考虑雪花算法

Q5：索引下推（ICP）是什么？如何优化查询？

答：索引下推是 MySQL 5.6 引入的优化，将 WHERE 条件的过滤下推到存储引擎层处理，减少回表次数。

-- 假设有索引 (a, b)
WHERE a = 1 AND b LIKE 'test%'
-- 无 ICP：先回表获取所有 a=1 的行，再过滤 b
-- 有 ICP：在索引层直接过滤 b，只回表符合条件的行

【版本差异】

MySQL 5.6：引入索引下推（ICP）、批量键值访问（MRR）
MySQL 5.7：虚拟列索引、表达式索引（函数索引前身）
MySQL 8.0：
- 真正的函数索引：CREATE INDEX idx ON t((JSON_EXTRACT(col, '$.path')))
- 倒序索引：INDEX idx (col DESC)
- 隐藏索引：可标记索引为不可用而不删除，便于测试
- 降序索引优化器支持

【最佳实践总结】

分析查询模式：使用慢查询日志找出高频查询
使用 EXPLAIN 验证：确保索引按预期使用
监控索引使用率：定期清理无用索引
考虑数据分布：均匀分布的数据更适合索引
测试真实负载：在类似生产环境的数据集上测试索引效果
利用复合索引：减少单列索引数量，提高索引利用率

通过系统化的索引设计和持续的监控优化，可以在查询性能和写操作开销之间找到最佳平衡点，从而最大化数据库的整体性能。

# 在 MySQL 中建索引时需要注意哪些事项？