主键索引、唯一索引、普通索引、联合索引、前缀索引各自语义？

考察要点

这道题面试官想看你是否深刻理解 InnoDB 存储引擎下不同索引类型的物理实现、逻辑约束和性能权衡。核心考察点包括：

1. 数据组织方式：是否理解聚簇索引（Clustered Index）和二级索引（Secondary Index）的根本区别，以及它如何影响数据存储和查询效率（如“回表”）。
2. 约束与特性：是否清楚每种索引在唯一性（Uniqueness）、非空性（NULLability）上的约束。
3. 设计与优化：是否懂得在实际场景中如何选择合适的索引类型，特别是联合索引的“最左前缀原则”和前缀索引的“选择性”权衡。

核心概念

在 MySQL 的 InnoDB 存储引擎中，所有索引都是 B+ 树结构，但根据其定义和存储方式，可分为以下几种：

1. 主键索引 (Primary Key Index)

主键索引是一种特殊的唯一索引，它不允许有空值。在 InnoDB 中，它也是聚簇索引。

• 语义与约束：

  • 唯一性：列值必须唯一。
  • 非空性：列值不能为 NULL。
  • 唯一标识：一张表只能有一个主键，用于唯一标识一行数据。

• 物理存储 (InnoDB)：

  • 主键索引的 B+ 树的叶子节点存储了完整的行数据。
  • 数据本身就是按照主键顺序物理存储的（或逻辑上连续的页链表）。
  • 这种“索引即数据，数据即索引”的结构就是聚簇索引。
  mermaid

  ⧉⛶

   1graph TD
   2    subgraph 主键索引 (聚簇索引)
   3        A[根节点<br/>(id 范围)] --> B[中间节点<br/>(id 范围)];
   4        B --> C1[叶子节点: id=1, name='...', age='...'];
   5        B --> C2[叶子节点: id=2, name='...', age='...'];
   6        B --> C3[叶子节点: id=3, name='...', age='...'];
   7        C1 -- 链接 --> C2;
   8        C2 -- 链接 --> C3;
   9    end
  10    style C1 fill:#f9f,stroke:#333,stroke-width:2px
  11    style C2 fill:#f9f,stroke:#333,stroke-width:2px
  12    style C3 fill:#f9f,stroke:#333,stroke-width:2px

  上图叶子节点直接包含了整行数据。

2. 唯一索引 (Unique Index)

唯一索引保证了索引列的值必须唯一，但允许有空值。

• 语义与约束：

  • 唯一性：列值必须唯一（NULL 除外）。
  • 可空性：可以包含多个 NULL 值（因为在数据库中 NULL 不等于 NULL）。

• 物理存储 (InnoDB)：

  • 唯一索引是二级索引（非聚簇索引）。
  • B+ 树的叶子节点存储的是“索引列的值 + 对应行的主键值”。
  • 如果通过唯一索引查询非索引列的数据，需要先在唯一索引树中找到主键，再用主键去主键索引树中查找完整的行数据，这个过程称为回表 (Back-to-table lookup)。

3. 普通索引 (Normal Index / Secondary Index)

最基本的索引类型，没有唯一性或非空性的约束，其唯一任务是加快对数据的访问速度。

• 语义与约束：

  • 无特殊约束：允许列值重复，也允许为 NULL。

• 物理存储 (InnoDB)：

  • 与唯一索引一样，是二级索引。
  • 叶子节点存储“索引列的值 + 对应行的主键值”。
  • 同样存在回表问题。
  mermaid

  ⧉⛶

   1graph TD
   2    subgraph 普通索引 (二级索引, 以 name 列为例)
   3        A[根节点<br/>(name 范围)] --> B[中间节点<br/>(name 范围)];
   4        B --> C1[叶子节点: name='Alice', 主键id=10];
   5        B --> C2[叶子节点: name='Bob', 主键id=5];
   6        B --> C3[叶子节点: name='Bob', 主键id=23];
   7        C1 -- 链接 --> C2;
   8        C2 -- 链接 --> C3;
   9    end
  10    subgraph 主键索引 (聚簇索引)
  11        D[叶子节点: id=5, name='Bob', age=30, ...];
  12        E[叶子节点: id=10, name='Alice', age=25, ...];
  13        F[叶子节点: id=23, name='Bob', age=40, ...];
  14    end
  15    C2 -->|回表 (Lookup)| D;

  上图通过 name='Bob' 查到主键 id=5，再用 id=5 去主键索引中找到完整数据。

4. 联合索引 (Composite / Compound Index)

联合索引是同时在多个列上建立的索引。

• 语义与约束：

  • 最左前缀原则 (Leftmost Prefix Principle)：查询必须从索引的最左边的列开始，并且不能跳过中间的列。
  • 例如，在 (col1, col2, col3) 上建立联合索引，以下查询可以用到该索引：
    • WHERE col1 = 'a'
    • WHERE col1 = 'a' AND col2 = 'b'
    • WHERE col1 = 'a' AND col2 = 'b' AND col3 = 'c'
    • WHERE col1 = 'a' AND col3 = 'c' (仅 col1 部分能用上索引)
  • 以下查询无法完全利用该索引：
    • WHERE col2 = 'b'
    • WHERE col3 = 'c'
    • WHERE col2 = 'b' AND col3 = 'c'

• 物理存储 (InnoDB)：

  • 也是二级索引。B+ 树节点中，键值按 (col1, col2, col3) 的顺序进行排序。

5. 前缀索引 (Prefix Index)

前缀索引只索引字符类型（如 VARCHAR, TEXT）数据的前N个字符，而不是整个字符串。

• 语义与约束：

  • 目的：主要用于在很长的字符串列上创建索引，以节省索引空间和提高查询效率。
  • 创建语法：CREATE INDEX idx_name ON table_name (column_name(N));

• 性能权衡：

  • 优点：索引更小，一个数据页能存放更多索引项，查询更快。
  • 缺点：
    • 选择性降低：前缀可能不唯一，导致扫描更多行。
    • 功能限制：无法用于 ORDER BY 和 GROUP BY，也无法实现覆盖索引 (Covering Index)，因为索引中只包含部分列数据。

常见低分回答

1. “主键索引就是非空的唯一索引”：这个回答忽略了 InnoDB 中最核心的区别——主键索引是聚簇索引，叶子节点存的是整行数据，而唯一索引是二级索引，叶子节点存的是主键值。这个物理结构差异导致了性能上的巨大不同。
2. “联合索引 (a, b)，WHERE b=5 也能用上索引”：这是对“最左前缀原则”的典型误解。B+ 树是按 (a, b) 整体排序的，如果不知道 a 的值，无法在树中进行有效的查找，只能全索引扫描（index scan），效率远低于索引定位（index seek/range）。
3. “给 TEXT 字段建索引时，直接 CREATE INDEX ... ON t(text_col)”：直接在长文本或二进制大字段上创建完整索引，会导致索引文件异常庞大，严重影响写入性能和内存使用。正确的做法是使用前缀索引，或考虑使用全文索引（Full-Text Index）。
4. “回表就是多查了一次，影响不大”：低估了回表的代价。在高并发场景下，大量的随机 I/O 会急剧降低数据库的吞吐量。一次回表相当于多一次 B+ 树的查找，如果查询返回大量数据，将产生大量的离散读，性能会非常差。
5. 混淆 COUNT(普通索引列) 和 COUNT(*)：认为两者等价。实际上，COUNT(*) 会被 MySQL 优化器智能选择一个最小的可用索引去统计行数。而 COUNT(列) 只会统计该列值不为 NULL 的行数，且如果该列没有索引，会导致全表扫描。

工程实践与优化

• 索引设计原则：在满足查询需求的前提下，尽量保持索引的小巧。优先使用联合索引覆盖多个查询场景，而不是为每个查询建一个单独的索引。写操作频繁的表，索引不宜过多。
• 联合索引列顺序：将**选择性高（基数大，Cardinality 大）**的列放在前面，同时考虑查询条件中等值查询的列优先于范围查询的列。例如 WHERE a = 10 AND b > 20，(a, b) 的顺序优于 (b, a)。
• 前缀索引的选择性：如何确定前缀长度 N？可以通过计算不同长度前缀的“选择性”来决定。选择性越接近 1 越好。
  sql

  ⧉⛶

   1-- 计算选择性，目标是让这个比率尽量接近 1
   2SELECT COUNT(DISTINCT LEFT(long_text_col, 10)) / COUNT(DISTINCT long_text_col) AS selectivity;

  通常选择一个使选择性超过 0.9 或 0.95 且增长缓慢的最小 N 值。
• 利用覆盖索引避免回表：如果一个查询所需的所有列（SELECT 和 WHERE 中）都恰好在一个索引中，那么 InnoDB 无需回表，直接从该索引的叶子节点获取数据返回。这是非常重要的优化手段。
  sql

  ⧉⛶

   1-- users 表有联合索引 (name, age)
   2-- 这个查询就是覆盖索引，Extra 中会显示 "Using index"
   3EXPLAIN SELECT name, age FROM users WHERE name = 'Alice';

• 执行计划分析：熟练使用 EXPLAIN 命令。关注 type (目标是 ref, range, eq_ref，避免 index, ALL)，key (实际使用的索引)，rows (预估扫描行数)，以及 Extra 字段（Using index, Using where, Using filesort）。MySQL 8.0+ 的 EXPLAIN ANALYZE 提供了更精确的执行耗时信息。

常见追问与延伸题目

1. 追问：如果让你设计一个用户表，主键你会用自增 ID 还是业务相关的 UUID？为什么？

   • 回答要点 1 (推荐自增 ID)：在 InnoDB 中，强烈推荐使用自增的整型作为主键。因为主键是聚簇索引，自增主键保证了新插入的数据总是在 B+ 树的末端，可以有效避免页分裂 (Page Split)，减少了数据移动，写入性能最好。同时，整型主键相比字符串（如 UUID）更紧凑，使得二级索引的叶子节点更小，整体索引体积也更小。
   • 回答要点 2 (UUID 的问题)：使用无序的 UUID 作为主键是灾难性的。每次插入都是随机位置，会导致大量的页分裂和数据碎片，写入性能极差。二级索引也会因为主键值过长而变得臃肿。如果必须用 UUID，应使用有序 UUID（如 UUIDv1 调整版或 UUIDv7），它结合了时间和空间唯一性，保证了趋势递增，性能远好于随机 UUID。

2. 追问：联合索引 (a, b, c)，查询 WHERE a > 10 AND b = 20 能否使用这个索引？效果如何？

   • 回答要点 1 (能否使用)：能使用。根据最左前缀原则，它会使用索引 a 的部分来执行范围查找 (a > 10)。
   • 回答要点 2 (效果如何)：对于 a 列，它走了范围扫描 (range scan)。一旦 a 的范围确定，MySQL 无法再利用索引的 b 和 c 部分进行精确定位，因为在 a 值变化的范围内，b 是无序的。MySQL 只能在 a > 10 的结果集上，再通过 WHERE 条件过滤 b = 20 的数据。在 EXPLAIN 的 Extra 字段会看到 Using where。

3. 追问：MySQL 5.7 和 8.0 在索引使用上有什么值得注意的区别？

   • 回答要点 1 (降序索引)：MySQL 8.0 才真正支持降序索引 (Descending Indexes)。在 5.7 及之前，CREATE INDEX ... (col DESC) 语法虽然能执行，但实际上并不会创建真正的降序索引，查询时会反向扫描。8.0 之后，可以为混合排序场景 ORDER BY a ASC, b DESC 创建 (a ASC, b DESC) 索引，从而避免文件排序 (filesort)。
   • 回答要点 2 (函数索引)：MySQL 8.0 引入了函数索引 (Functional Indexes)。可以直接对表达式或函数结果创建索引，如 CREATE INDEX idx_year ON t (YEAR(create_time))。这使得 WHERE YEAR(create_time) = 2023 这样的查询也能走索引，而在 5.7 中，对列使用函数会导致索引失效。
   • 回答要点 3 (隐藏索引)：MySQL 8.0 增加了隐藏/不可见索引 (Invisible Indexes)。可以先将索引设置为 INVISIBLE，让它对查询优化器不可见，从而安全地评估删除该索引对性能的影响。如果没问题，再 DROP；如果有问题，再改回 VISIBLE 即可，无需重建。