问题: 在设计索引时，如何利用区分度（Cardinality）和选择性（Selectivity）选择最优列？在高频写入（INSERT/UPDATE/DELETE）场景下，索引过多会带来哪些维护代价，如何权衡读写性能？

👇 点击展开/折叠 面试背诵模板（建议先背这个） 索引列选择应优先考虑选择性高的列（重复值少、过滤效果好），通过 `SHOW INDEX FROM table` 查看 Cardinality（不重复值估算），选择性 = Cardinality / total_rows，值越接近1越适合建索引。索引维护代价包括：INSERT 需更新所有索引（B+树插入+页分裂），UPDATE 涉及索引列时需删除旧索引条目并插入新条目，DELETE 需从所有索引中删除条目，导致写性能下降和存储空间膨胀。权衡原则：读多写少可多建索引（≤5~6个），写多读少应精简索引，仅保留覆盖高频查询的必须索引；通过慢查询日志定期审查，删除未使用或重复索引。核心准则是用“查询频率 × 查询性能提升”与“写入频率 × 维护开销”进行成本效益权衡。
---

一、索引列选择：区分度与选择性

Cardinality（基数）：索引列中不重复值的估算数量，通过 SHOW INDEX FROM table 查看。Cardinality 越高，说明该列值越分散，索引过滤效果越好。

Selectivity（选择性）：选择性 = Cardinality / 总行数，取值在 (0, 1] 之间。值越接近1，该列作为索引列的效果越好。例如：

  • 性别列：选择性 ≈ 0.5（男女各半），过滤效果差。
  • 身份证号列：选择性 ≈ 1（几乎全唯一），过滤效果极佳。

实践建议：

  • 组合索引中，选择性高的列放在左侧，优先过滤掉最多数据。
  • 若列的选择性 < 0.1（即重复值超过90%），该列单独建索引收益极低，不建议作为独立索引。
  • 对长字符串字段（如 VARCHAR(255)），可考虑前缀索引（CREATE INDEX idx_col ON table(col(10))），牺牲部分区分度换取索引体积缩减。

区分度的统计信息优化：Cardinality 是统计信息，默认通过采样计算（InnoDB 对 N 个页随机采样）。若统计信息不准确，可执行 ANALYZE TABLE 重新计算，帮助优化器选择更优执行计划。

二、索引维护代价（高频写入场景）

INSERT 代价：

  • 每插入一行，需要向该表的所有索引（聚簇索引 + 所有二级索引）分别插入索引条目。
  • 若索引列值无序（如 UUID），B+树页分裂频繁，导致磁盘随机写入和碎片化，吞吐量下降。

UPDATE 代价：

  • 若更新的列未建索引，更新代价较低（仅修改聚簇索引数据页）。
  • 若更新涉及索引列，需先删除旧索引条目（逻辑删除 + 标记），再插入新条目，额外产生两个操作。
  • 若更新主键列，代价极高（主键变更导致整行数据在聚簇索引中移动，且所有二级索引的主键指针需同步更新）。

DELETE 代价：

  • 从聚簇索引中删除行，同时需要从所有二级索引中删除对应的索引条目（标记删除 + 页合并）。
  • 大量删除会产生碎片（页空洞），影响后续插入性能（需执行 OPTIMIZE TABLE 重建表整理空间）。

索引数量对写入性能的影响：

索引数量 写入性能影响 典型场景
0 ~ 3 个 写入性能影响小 日志表、流水表（只读或写多读少）
4 ~ 6 个 写入性能有一定损耗，可接受 普通业务表（读多写少）
> 6 个 写入性能显著下降，需评估是否必要 过度索引的典型信号

三、读写性能权衡策略

决策矩阵：

业务特征 索引策略 理由
读多写少（如报表、详情页） 可多建索引（≤6个），包含覆盖索引 查询性能优先，写入频率低可容忍维护开销
写多读少（如日志、流水） 仅保留主键索引，尽量避免二级索引 写入吞吐量优先，查询可离线或通过其他方式处理
读写均衡（如订单、用户表） 针对高频查询建3~5个索引，定期删除未使用索引 平衡读写性能，动态监控调整

索引瘦身策略：

  • 索引下推/覆盖索引优先：用覆盖索引替代多个单列索引，减少索引数量同时提升查询效率。
  • 删除重复索引：如 (a, b) 和 (a) 重复，保留前者（包含后者功能）。
  • 定期审查未使用索引：通过 performance_schema 或 pt-index-usage 工具，删除长时间未被使用的索引。
  • 分库分表/归档：对历史数据做冷热分离，热表轻量化索引，冷表全量索引用于离线分析。

四、核心总结

  • 优先选择选择性高的列建索引，通过 Cardinality / total_rows 评估过滤效果。
  • 索引数量与写入性能成反比，控制单表索引数量 ≤ 5~6 个是通用准则。
  • 权衡本质是成本效益分析：以“查询频率 × 查询性能提升”与“写入频率 × 维护开销”做量化对比（不需要精确计算，但需有明确的取舍依据）。