本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
索引（B+ 树 / 覆盖 / 失效 / 优化） · 索引种类与设计

何时不应该建索引？(超低基数、写多读少、表本身很小、列经常更新)

When not to create an index? (Very low cardinality, write-heavy workloads, small

答案语言

考察要点

这道题面试官想看你是否理解索引并非“银弹”，而是一种有代价的优化手段。它重点考察你对索引背后维护成本（写操作开销、存储开销）与查询收益之间权衡的认知能力。一个高分回答需要清晰地阐述在哪些具体场景下，索引的弊大于利，并能从数据库原理层面解释原因。

核心概念

不建索引的决策，本质上是基于对索引维护成本的理解。索引在加速 SELECT 的同时，会拖慢 INSERT、DELETE 以及 UPDATE 操作，并占用额外的存储空间。

核心代价：索引维护

  1. 写操作开销 (Write Overhead)：

    • INSERT：每插入一条新数据，数据库必须将新的索引键值插入到所有相关的 B+ 树索引中。这可能导致 B+ 树的页分裂（Page Split），这是一个相对昂贵的操作，会引起额外的 I/O 和 CPU 消耗。
    • DELETE：删除数据时，不仅要删除表中的数据，还要在索引中将对应的键值标记为删除或直接物理删除，这会产生索引碎片。
    • UPDATE：
      • 如果更新的列不是索引列，那么开销较小，仅需更新数据页。
      • 如果更新的列是索引列，这通常是开销最大的情况。其过程等价于一次索引的 DELETE + 一次索引的 INSERT，极易引发页分裂和性能抖动。

  2. 存储开销 (Storage Overhead)： 每个索引都是一个独立的数据结构（通常是 B+ 树），它需要占用磁盘空间。索引越多、表数据量越大，索引占用的空间就越可观。这不仅增加了存储成本，还会影响备份和恢复的效率。

基于以上成本，以下四类场景通常不建议创建索引：

1. 超低基数（Cardinality）的列

  • 概念：基数是指一列中不重复值的数量。超低基数意味着该列只有很少的几个唯一值，例如：性别（男、女、未知）、订单状态（待支付、已支付、已发货、已完成）。
  • 不建索引原因：索引的选择性（Selectivity）太差。选择性 = Cardinality / Total Rows。当选择性很低时，通过索引筛选出的数据量占总数据量的比例非常高（如 WHERE gender = '男' 可能返回近 50% 的数据）。在这种情况下，MySQL 查询优化器会认为走索引的成本（索引扫描 + 大量回表） 高于 直接全表扫描（Full Table Scan） 的成本，从而放弃使用索引。
  • 数据示例： users 表 (1 亿行) | user_id | gender | | :--- | :--- | | 1 | M | | 2 | F | | 3 | M | | ... | ... | 为 gender 列建索引后，SELECT * FROM users WHERE gender = 'M'; 依然会是全表扫描，索引形同虚设。

2. 写多读少的表

  • 概念：表的写操作（INSERT, UPDATE, DELETE）频率远高于读操作（SELECT）。
  • 不建索引原因：收益与成本严重不匹配。每次写操作都要承担所有相关索引的维护开销，而这些开销带来的查询加速收益却很少被利用。
  • 数据示例： log_events 表，用于记录用户行为日志。
    sql
     1CREATE TABLE log_events (
     2    id BIGINT AUTO_INCREMENT PRIMARY KEY,
     3    event_type VARCHAR(50),
     4    user_id INT,
     5    payload JSON,
     6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     7);
    该表每秒有数万次 INSERT，但可能每天只做几次后台分析查询。为 event_type 或 user_id 建立索引会严重拖慢写入速度。通常这类场景会将数据准实时地同步到专门的分析型数据库（如 ClickHouse, Elasticsearch）中进行查询。

3. 表本身很小

  • 概念：表的数据行数很少，例如只有几百或几千行，整个表的数据和索引可能都装在几个数据页里。
  • 不建索引原因：全表扫描的成本已经足够低。对于小表，CPU 读取内存中几个数据页的速度极快，甚至比通过 B+ 树索引查找（涉及多次指针跳转）再回表（如果需要）还要快。增加索引反而增加了不必要的维护成本和存储空间。
  • 数据示例： app_config 表，存储系统配置项，通常不超过 100 行。对任何列的查询，直接全表扫描即可，无需索引。

4. 经常被更新的列

  • 概念：该列的值会频繁地被 UPDATE 语句修改。
  • 不建索引原因：如核心概念中所述，更新索引列的代价是“删除旧值 + 插入新值”，这会频繁导致 B+ 树的结构调整（页分裂、合并），产生大量 I/O 和锁竞争，严重影响数据库的并发写入性能。
  • 数据示例： orders 表中的 order_status 列，一个订单的状态可能在短时间内从“待支付”变为“已支付”再变为“备货中”。 users 表中的 last_login_time 列，每次用户登录都会更新。 为这些列建立索引，会导致 UPDATE 性能急剧下降。

常见低分回答

  1. 回答过于绝对：“性别列绝对不能建索引”。这种说法不严谨。如果一个查询是 SELECT gender, COUNT(*) FROM users GROUP BY gender;，并且没有其他 WHERE 条件，一个 gender 上的覆盖索引可以实现“索引扫描”，避免扫描主表，性能会很好。关键在于具体问题具体分析。
  2. 只说“写操作会变慢”，但说不清为什么：无法从 B+ 树的页分裂、索引维护（删除旧值、插入新值）等底层原理解释开销来源，显得理解不深。
  3. 混淆短列和低基数列：认为 CHAR(1) 这种短列就不该建索引。列的物理长度与基数是两个概念。一个 CHAR(8) 的列，如果存的是用 UUID 生成的唯一码，其基数就非常高，非常适合建索引。
  4. 忽略了外键（Foreign Key）的特殊性：有人会说“如果一个外键列写操作频繁，就不该建索引”。但对于 InnoDB 引擎，不为外键创建索引，在对父表做更新或删除时，可能会导致对子表的全表扫描来检查引用完整性，甚至产生意想不到的表锁。因此，外键列通常建议创建索引。
  5. 对“小表”的定义模糊不清：只说小表不建索引，但当面试官追问“多小算小表？”时，无法给出一个量化的、有说服力的范围（例如，几千行到一两万行，数据能被 buffer pool 完全缓存）。

工程实践与优化

  • 用数据说话，而不是凭感觉：在决定是否为某个列添加或删除索引时，应基于实际的监控数据。
    • MySQL 8.0+：使用 sys schema 中的 schema_unused_indexes 和 schema_redundant_indexes 视图，可以快速定位无效索引。
    • Performance Schema：开启 setup_instruments 和 setup_consumers 中关于索引使用的监控，可以统计索引的 I/O 等待时间。
    • 慢查询日志 + EXPLAIN：分析慢查询，查看执行计划，确认优化器是否使用了你期望的索引。如果一个索引频繁出现在写操作的慢查询中，但在读操作中很少被使用，就应该考虑删除。
  • 利用覆盖索引处理低基数列：对于低基数列，如果查询只涉及该列或少数几列，可以创建覆盖索引。例如，SELECT status, COUNT(*) FROM orders GROUP BY status;，创建一个 INDEX(status) 就能让查询通过索引完成，避免回表，速度极快。
  • 使用延迟索引或批量处理：对于写多读少的场景（如日志），应用层可以先将数据写入一个无索引的临时表，然后通过定时任务（如 INSERT INTO ... SELECT ...）批量将数据导入到有索引的主表。这样将多次单点写入的索引维护开销，合并为一次大的批量维护开销，整体吞吐量更高。
  • MySQL 8.0 的隐形索引 (Invisible Indexes)：这是一个非常实用的功能。当你不确定一个索引是否可以安全删除时，可以先将其设置为 INVISIBLE。这样优化器就不会再使用它，但索引数据依然在后台维护。观察一段时间（例如一周），如果系统没有出现性能衰退或错误，就可以安全地 DROP 掉这个索引了。

常见追问与延伸题目

  1. 追问：如果一个低基数列（如订单状态）又必须作为高频查询条件，怎么办？

    • 回答要点 1 (创建联合索引)：将这个低基数列和一个高基数列（如 create_time 或 user_id）组合成联合索引。例如，创建 INDEX idx_status_createtime(status, create_time)。对于 WHERE status = 'PAID' AND create_time > '2023-01-01' 这样的查询，联合索引的选择性会大大提高，优化器会倾向于使用它。
    • 回答要点 2 (利用覆盖索引)：如果查询只访问索引包含的列（例如 SELECT id, status FROM orders WHERE status = 'PAID'），可以创建 INDEX idx_status_id(status, id)。即使 status 基数低，查询也可以通过“索引扫描”完成，避免了回表，性能依然很高。
    • 回答要点 3 (业务或架构调整)：如果可能，可以考虑将不同状态的订单分到不同的表中，或者使用分区表。但这属于架构层面的大改动，需要谨慎评估。

  2. 追问：如何发现并清理生产环境中的无用索引？

    • 回答要点 1 (工具先行)：首先，利用数据库自带的工具。在 MySQL 中，查询 sys.schema_unused_indexes 视图是首选，它可以直接列出从未被使用过的索引。
    • 回答要点 2 (流程保障)：建立一个标准操作流程（SOP）：
      1. 识别：通过上述工具或长期（如一个季度）的慢查询日志分析，找出候选的无用索引。
      2. 验证：确认索引不是用于支持 UNIQUE 约束的。UNIQUE 索引即使不用于查询，也对数据完整性至关重要。
      3. 灰度删除：使用 MySQL 8.0 的 ALTER TABLE ... ALTER INDEX ... INVISIBLE; 将索引设为不可见。对于老版本，可以考虑先 RENAME 索引，观察应用是否报错。
      4. 观察：保持不可见状态一个完整的业务周期（如一个月），持续监控系统性能和错误日志。
      5. 执行：确认无影响后，执行 DROP INDEX。

  3. 追问：如果我有一个列，它的值在插入时确定，之后绝不更新，但基数很低，适合建索引吗？

    • 回答要点 1 (看查询模式)：这排除了“频繁更新列”的问题，现在核心矛盾回到了“低基数”上。答案依然取决于查询模式。如果它总是独立作为查询条件（WHERE low_cardinality_col = 'value'），那么索引的价值不大，因为优化器大概率会选择全表扫描。
    • 回答要点 2 (联合与覆盖)：但是，如果它经常与其他列一起被查询，那么作为联合索引的一部分就非常有价值（如上文所述）。或者，如果查询能形成覆盖索引，那么即使基数低，索引也是有益的。这个场景的写成本只有 INSERT 时的一次性开销，没有 UPDATE 的持续代价，因此可以更宽松地考虑为读查询服务。
← Previous
前缀索引的使用场景？长字符串列怎么决定前缀长度？(区分度评估)
Next →
联合索引的列顺序如何选择？等值在前、范围在后的原则？