高频更新字段建索引会加剧锁竞争，因每次更新需两次B+树操作；联合索引中将其置前更差；冗余索引和TEXT前缀索引同样徒增写开销；ADD INDEX隐含高成本，需显式指定ALGORITHM=INPLACE。
高频UPDATE/DELETE字段上建索引会放大锁竞争
只要字段被频繁修改（比如 status、updated_at、retry_count），它出现在任何索引里都会显著拖慢写入——因为每次更新都得先删旧索引项、再插新索引项，InnoDB 实际执行的是两次 B+ 树操作。
常见错误现象：Lock wait timeout exceeded、大量 Waiting for table metadata lock 等待、主从延迟突增。
- 联合索引中把高频更新字段放在前面（如
(status, user_id)）比放后面更糟，前缀变化导致整条索引路径重定位 - 单列索引
INDEX(status)和联合索引INDEX(user_id, status)同时存在时，前者几乎无查询收益，但写开销 100% 白给 - 用
SELECT * FROM information_schema.STATISTICS WHERE table_name = 't_order' AND column_name = 'status'快速定位所有含该字段的索引
TEXT/VARCHAR大字段前缀索引实际加重DML负担
很多人以为加前缀能省空间、减开销，但 MySQL 更新这类索引时仍需重新计算前缀值并刷新整个叶子节点，且因节点存储不紧凑，更容易触发页分裂。
使用场景：日志表、商品描述表、用户评论表等含长文本字段的表。
-
INDEX(content(255))对写入压力和全字段索引INDEX(content)差别极小，但查询能力大幅缩水 - 真正有效的替代方案是：改用生成列 + 哈希摘要索引，例如
content_hash CHAR(32) AS (MD5(content)) STORED，再建INDEX(content_hash) - 避免在 JSON 字段上直接建索引；若必须查 JSON 内容，优先用
JSON_EXTRACT生成虚拟列再索引
ALTER TABLE ADD INDEX期间的隐性锁与重建成本
哪怕只是加一个索引，MySQL 默认会走 ALGORITHM=COPY（尤其在老版本或未显式指定算法时），相当于重建整张表+全部已有索引——索引越多，耗时越长，锁表时间越不可控。
实测案例：12 个索引的订单表（2.3 亿行），执行 ADD INDEX idx_user_status (user_id, status) 耗时 47 分钟，期间写入堆积，应用超时熔断。
- 务必在
ALTER TABLE中显式声明ALGORITHM=INPLACE, LOCK=NONE（需满足引擎与字段类型限制） - 提前用
EXPLAIN FORMAT=TREE验证目标语句是否真走新索引，避免“建了却不用”的假优化 - 上线前在影子库跑一次完整 DDL + 压测，重点关注
Innodb_row_lock_time_avg和Handler_read_next指标波动
冗余索引让写入成本线性叠加却不提升查询效率
索引不是越多越好，而是越精越好。两个索引 (a, b) 和 (a) 并存时，写入要维护两份，但优化器在绝大多数情况下只会选前者——后者纯属冗余。
容易踩的坑：开发为不同 WHERE 条件各自建单列索引，没考虑联合索引的最左匹配；DBA 交接时未清理历史测试索引。
- 用这条 SQL 找出疑似冗余的索引：
SELECT s1.TABLE_NAME, s1.INDEX_NAME as redundant, s2.INDEX_NAME as dominant FROM information_schema.STATISTICS s1 JOIN information_schema.STATISTICS s2 ON s1.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s1.TABLE_NAME = s2.TABLE_NAME AND s1.SEQ_IN_INDEX = 1 AND s2.SEQ_IN_INDEX = 1 AND s1.COLUMN_NAME = s2.COLUMN_NAME WHERE s1.INDEX_NAME != s2.INDEX_NAME AND s1.CARDINALITY - 删除前先用
pt-index-usage或 Performance Schema 的table_io_waits_summary_by_index_usage确认该索引近 7 天是否被任何查询用过 - 单表二级索引总数建议硬性卡在 5–6 个以内；超过这个数，每多一个，INSERT 延迟大概率增加 15%～30%
真实线上环境里，索引的“写代价”往往在压测阶段被忽略，直到大促期间订单表批量更新开始超时，才回过头查 SHOW ENGINE INNODB STATUS 里满屏的 lock struct(s)。最麻烦的不是建错索引，而是建了不敢删——因为没人敢确认哪个查询还依赖它。
