17370845950

INSERT/UPDATE/DELETE 会触发所有相关索引同步更新，包括聚簇索引和二级索引；UPDATE 修改索引列时需先删后插，唯一索引因存在性校验导致额外 B+ 树查找，写放大显著；批量写入应删非必要索引再重建，避免前缀索引和大字段索引，合理配置 change buffer。

INSERT/UPDATE/DELETE 会触发索引同步更新

每条写操作执行时，MySQL 不仅要修改聚簇索引（主键）或行数据，还要维护所有相关二级索引。这意味着 INSERT 会为每个二级索引插入一条索引记录；UPDATE 若修改了索引列（如 WHERE 条件字段或 ORDER BY 字段），则旧索引项要删除、新索引项要插入；DELETE 则需从所有索引中移除对应条目。

实际影响取决于索引数量和大小：

• 单表有 5 个二级索引，一次 UPDATE 修改了其中 2 个索引涉及的列 → 至少触发 4 次 B+ 树节点分裂/合并操作（删 + 插各 2 次）
• 索引列值过长（如 VARCHAR(2000) 上建索引）→ 索引页更易填满，频繁页分裂，写放大加剧
• INSERT DELAYED 已被移除（MySQL 8.0+），不要依赖它“缓解”索引写压力

唯一索引的校验开销比普通索引高

唯一索引在写入前必须做存在性校验，这会引发额外的索引查找。即使使用覆盖索引优化，仍需至少一次 B+ 树搜索来确认无冲突。

典型场景下性能差异明显：

• 向带 UNIQUE KEY (email) 的表批量导入 10 万行 → 每行都触发一次 email 索引的等值查找，I/O 和 CPU 开销显著高于非唯一索引
• 复合唯一索引如 UNIQUE (user_id, category)，校验逻辑更复杂，且无法用前缀索引规避（前缀不保证唯一）
• 若业务能接受应用层去重，可考虑去掉唯一约束，改由程序控制，但需承担数据一致性风险

ANALYZE TABLE 和 OPTIMIZE TABLE 的真实作用被高估

ANALYZE TABLE 只更新统计信息（如索引基数 cardinality），不影响索引结构本身；OPTIMIZE TABLE 对 InnoDB 表实际执行的是 ALTER TABLE ... FORCE，即重建表 + 所有索引，耗时长、锁表久，不是日常维护手段。

真正需要关注的是自动维护行为：

• InnoDB 后台线程会异步合并插入缓冲区（change buffer）中的索引变更，但仅适用于**非唯一二级索引**且查询不频繁的场景
• innodb_change_buffer_max_size 默认 25（表示占 buffer pool 最大 25%），调太高可能挤占查询缓存空间
• 定期 SELECT COUNT(*) 或 SHOW INDEX FROM tbl 并不能“刷新”索引效率，只是读取当前状态

批量写入时索引维护的优化边界

批量操作无法绕过索引更新，但可以压缩其单位开销。关键不是“关索引”，而是控制更新节奏与结构设计。

实操建议如下：

• 大批量导入前，用 DROP INDEX 删除非必要二级索引，导入完成再 CREATE INDEX —— 注意：主键无法删除，且重建索引仍是全表扫描+排序过程
• 避免在 TEXT/JSON 列上直接建索引；改用生成列（GENERATED COLUMN）+ 函数索引（MySQL 8.0.13+），例如：

  ALTER TABLE logs ADD COLUMN title_hash VARCHAR(32) STORED AS (MD5(title));
  CREATE INDEX idx_title_hash ON logs(title_hash);

• 写密集场景慎用前缀索引（INDEX(col(10))），因为前缀无法支持排序和范围扫描，反而让优化器放弃使用该索引

索引不是越全越好，每次新增一个二级索引，就等于给每条写语句多加一道同步负担。线上表如果写入 QPS 超过 500，且二级索引数 ≥ 4，就得仔细核对每个索引是否真被 EXPLAIN 用到，而不是只看“查询变快了”。