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

标签：后端、MySQL、数据库

🏛 业务架构师

建索引的注意事项我按设计、编写、运维三段列——每条都对应真实教训：

设计期

从查询出发：拉慢查询日志与调用量统计，为高频查询建索引，不为想象中的查询建索引；
联合索引列序：等值列在前、范围列在后、排序列尽量吃索引；利用最左前缀复用索引，(a,b,c) 覆盖 (a)、(a,b)，减少索引总数；
选择性优先：低选择性列（性别、状态）单独建索引意义小，与高选择性列组合或干脆不建；
主键用趋势递增（自增/雪花）——避免随机主键的页分裂，大表的写入性能一半是主键设计决定的；
控制数量：交易表索引要克制（写放大的税），一般 ≤5 个。

编写期

索引列上不做函数/运算/隐式类型转换（字符串列用数字查是高频事故）；
长字符串用前缀索引省空间；
覆盖索引服务高频列表页（配合禁 select *）。

运维期

上线前 EXPLAIN 卡点（流程资产，比任何技巧都值钱）；
定期审计：未使用索引下线（写税白交）、碎片整理、统计信息更新（analyze table）防优化器误判；
DDL 走变更流程：大表加索引用在线 DDL/gh-ost，避开高峰，警惕 MDL 锁引发的整表阻塞。

一个原则收尾：索引是用写性能换读性能的资产，有建就有养、有建就有审——把索引当财务资产管理（记台账、定期盘点、淘汰坏账），而不是建完就忘。

🏗 系统架构师

我按"设计原则 → 具体规范"组织：

设计原则：

最左前缀与查询对齐：索引按真实 SQL 反推列序（等值列在前、范围列在后、区分度高在前），联合索引优先于多个单列索引；
主键规范：自增或趋势递增（防页分裂），禁止业务敏感大字段做主键；
索引数量收敛：单表 ≤5 个，联合 ≤4 列，避免冗余（(a,b) 已含 (a)）；
覆盖高频查询：热点 SQL 尽量覆盖索引免回表，但不为长尾查询堆索引。

写法规范：

禁止索引列参与运算/函数/隐式类型转换（varchar 传数字）；前导 % 模糊；
不用 select *（列裁剪 + 覆盖索引机会）；
深分页改延迟关联/游标；
or/union all、join 小表驱动大表且关联列有索引。

变更与治理流程：

索引变更走工单评审 + 从库 explain 验证 + 低峰执行；大表 DDL 用 gh-ost/pt-osc（在线改表不锁主）；
先隐形后删除：8.0 用 invisible index 观察无退化再 drop；
冗余/未使用索引定期清理（sys.schema_unused_indexes）。

容量视角：建索引前算写入放大账——写多读少的表、低频查询宁可不加；索引是给"读"买单的，付不起就换缓存/从库方案。

💻 全栈开发专家

我的建索引 checklist（踩坑总结版）：

1. 列选择：

区分度高的列：count(distinct col)/count(*) 接近 1 优先；性别/状态这类低区分度列单独建索引基本无效；
联合索引顺序：等值条件在前、范围条件在后（范围后面的列失效）；频繁单独查询的列放前面；
前缀索引处理长字符串：INDEX(email(12))，用区分度曲线找拐点，但注意它不支持覆盖索引和 ORDER BY。

2. 写法配合（建了也要能走）：

索引列上不用函数/运算、避免隐式类型转换（字符串列条件必须带引号）；
前导模糊 like '%x' 失效；
尽量设计覆盖索引减少回表。

3. 数量与维护成本：

单表 5~6 个以内，冗余索引清理（已有 (a,b) 就别再建 (a)）；
频繁更新的列少建索引（写放大）；写多读少的表（日志表）克制建索引。

4. 主键规范：自增主键（顺序写不页分裂），避免长 UUID；主键要短（二级索引叶子都存主键）。

5. 上线操作安全：

大表 DDL 用 gh-ost / pt-online-schema-change（8.0 部分操作 INSTANT，加列秒级）；
低峰执行，观察主从延迟；
上线前 EXPLAIN 验证、慢日志回归。

6. 验证闭环：加索引必须带着"为哪条 SQL 服务、预期扫描行数降多少"来评审，一周后用慢日志验证效果——索引是负债（维护成本），有收益才建。