MySQL 面试之索引篇

综合

【简单】什么是索引？为什么要使用索引？

“索引”是数据库为了提高查找效率的一种数据结构。

日常生活中，我们可以通过检索目录，来快速定位书本中的内容。索引和数据表，就好比目录和书，想要高效查询数据表，索引至关重要。在数据量小且负载较低时，不恰当的索引对于性能的影响可能还不明显；但随着数据量逐渐增大，性能则会急剧下降。因此，设置合理的索引是数据库查询性能优化的最有效手段。

【简单】索引的优点和缺点是什么？

✅️️️️️️️ 索引的优点：

• 索引大大减少了服务器需要扫描的数据量，从而加快检索速度。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机 I/O 变为顺序 I/O。
• 支持行级锁的数据库，如 InnoDB 会在访问行的时候加锁。使用索引可以减少访问的行数，从而减少锁的竞争，提高并发。
• 唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。

❌ 索引的缺点：

• 创建和维护索引要耗费时间，这会随着数据量的增加而增加。
• 索引需要占用额外的物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立联合索引那么需要的空间就会更大。
• 写操作（INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。

【中等】何时适用索引？何时不适用索引？🌟

• 索引不是越多越好，不要为所有列都创建索引。要考虑到索引的维护代价、空间占用和查询时回表的代价。索引一定是按需创建的，并且要尽可能确保足够轻量。一旦创建了多字段的联合索引，我们要考虑尽可能利用索引本身完成数据查询，减少回表的成本。
• 要考虑删除未使用的索引。
• 尽量的扩展索引，不要新建索引。索引需要占用额外的存储空间；此外，表更新时，需要同步维护索引。索引越多，意味着维护所付出的成本越高，因此，应尽量扩展已有索引，而不是不假思索的新建索引。

✅️️️️ 什么情况适用索引？

• 高频查询的字段：频繁作为 WHERE 条件或 JOIN 条件的字段，应考虑设为索引。
• 频繁用于 ORDER BY、GROUP BY、DISTINCT 的列。将该列作为索引，可以帮助加快这些操作。

❌ 什么情况不适用索引？

• 非常小的表：对于非常小的表，大部分情况下简单的全表扫描更高效。
• 特大型的表：建立和使用索引的代价将随之增长。可以考虑使用分区技术或 Nosql。
• 高频更新的表：表更新时，需要同步维护索引，有额外的开销，会影响性能。
• 低频查询的字段：很少作为 WHERE 条件或 JOIN 条件的字段，建立索引而带来的空间开销和维护成本可能超过查询性能提升所带来的收益。
• 高度重复的字段：若索引字段的重复度高，意味着选择性低（如性别字段只有男和女），索引的效果不明显，且会增加额外的存储空间。
• 长文本的字段：如 TEXT、BLOB 或非常长的 VARCHAR 类型，字段常包含大量数据。
  • 数据量大时，无法用内存排序，只能利用磁盘文件排序，速度很慢。
  • 数据页默认 16KB，存储数据有限，超出范围，需要扫描多次 I/O。
  • 这种类型的数据如果有查询需求，应考虑使用 ES 来进行全文检索。

【中等】哪些情况下，索引会失效？🌟🌟🌟

索引不一定有效。

检查索引是否有效的方法：在查询语句前加 EXPLAIN，查看执行计划。主要观察以下信息：

• type（访问类型）：如果索引有效，一般会显示为 index、range；如果值为 ALL，表示采用全表扫描方式，没有使用索引。
• key（选择的索引）：表示查询时使用的索引。

导致索引失效的情况有：

• 违反最左前缀原则
  • ❌跳过了最左列（如索引 (a,b,c)，但查询 WHERE b=1）
  • ❌中间列被跳过（如 WHERE a=1 AND c=3，c 无法使用索引）
• 对索引使用函数或计算
  • ❌ WHERE YEAR(date_column) = 2023
  • ❌ WHERE column * 2 = 10
  • ❌ WHERE SUBSTRING(name, 1, 3) = 'Tom'
• 数据类型不匹配（隐式类型转换）
  • ❌ WHERE string_column = 123（字符串列用数字比较）
  • ❌ WHERE int_column = '123'（数字列用字符串比较）
• 使用 OR 连接非索引列
  • ❌ WHERE a=1 OR b=2（如果 b 无索引，全表扫描）
  • ✅ WHERE a=1 OR a=2（a 有索引，可以优化）
• 使用范围查询（>、<、BETWEEN、LIKE）后的列失效
  • ✅ WHERE a=1 AND b=2（a, b 都能用索引）
  • ❌ WHERE a>1 AND b=2（a 是范围查询，b 无法用索引）
  • ❌ WHERE name LIKE '%abc'（前导通配符 % 导致索引失效）
• 使用 !=、<>、NOT IN、IS NULL、IS NOT NULL
  • ❌ WHERE status != 'active'
  • ❌ WHERE age NOT IN (18, 20)
  • ❌ WHERE phone IS NULL

【简单】索引有哪些分类？🌟

MySQL 索引可以从以下四个维度来分类：

• 按【数据结构】分类：B+tree 索引、Hash 索引、Full-text 索引
• 按【物理存储】分类：聚簇索引、二级索引（辅助索引）
• 按【字段特性】分类：主键索引、唯一索引 、普通索引、前缀索引
• 按【字段个数】分类：单列索引、联合索引（复合索引、组合索引）

【简单】= 和 in 的顺序对于命中索引是否有影响？🌟

不需要考虑 =、IN 等的顺序，MySQL 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

【示例】如有索引 (a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c、d。

索引数据结构

【简单】索引有哪些常见数据结构？

在 MySQL 中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准。不同存储引擎的索引的数据结构也不相同。下面是 MySQL 常用存储引擎对一些主要索引数据结构的支持：

索引数据结构/存储引擎	InnoDB 引擎	MyISAM 引擎	Memory 引擎
B+ 树索引	✅️️️️️️️	✅️️️️️️️	✅️️️️️️️
Hash 索引	❌	❌	✅️️️️️️️
Full Text 索引	✅️️️️️️️	✅️️️️️️️	❌

MySQL 索引的常见数据结构：

• 哈希索引
  • 因为索引数据结构紧凑，所以查询速度非常快。
  • 只支持等值比较查询 - 包括 =、IN()、<=>；不支持任何范围查询，如 WHERE price > 100。
  • 无法用于排序 - 因为哈希索引数据不是按照索引值顺序存储的。
  • 不支持部分索引匹配查找 - 因为哈希索引时使用索引列的全部内容来进行哈希计算的。如，在数据列 (A,B) 上建立哈希索引，如果查询只有数据列 A，无法使用该索引。
  • 不能用索引中的值来避免读取行 - 因为哈希索引只包含哈希值和行指针，不存储字段，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能影响不大。
  • 哈希索引有可能出现哈希冲突
    • 出现哈希冲突时，必须遍历链表中所有的行指针，逐行比较，直到找到符合条件的行。
    • 如果哈希冲突多的话，维护索引的代价会很高。
• B 树索引
  • 适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。
  • 所有的关键字（可以理解为数据）都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
  • 所有的叶子节点由指针连接。

【中等】为什么 InnoDB 采用 B+ 树索引？🌟🌟

InnoDB 选择 B+ 树，是其在磁盘 I/O 效率、范围查询、稳定性和空间利用率之间取得的最佳平衡。它特别适合处理数据库这种数据量巨大、且查询模式复杂（大量范围查询、排序、分组）的场景。

即使现在 SSD 越来越普及，其随机读写性能远超 HDD，但减少 I/O 次数的核心思想依然没有变（SSD 的随机读写仍慢于顺序读写，且存在写入放大等问题），因此 B+ 树依然是数据库索引的黄金标准。

减少磁盘 I/O 次数，提高查询效率

• 磁盘特性：机械硬盘的随机读写速度很慢，但顺序读写速度快。磁盘读写以“页”（通常是 4KB）为单位。因此，评价索引优劣的关键是每次操作需要访问多少磁盘块（磁盘页）。
• 树的高度低：B+ 树是一个多路平衡搜索树，一个节点可以拥有非常多的子节点（扇出高）。这意味着对于大量数据（如千万级、亿级记录），B+ 树依然能维持非常低矮的高度（通常只有 3-4 层）。
• 结果：查找任何一条记录，最多只需要 3-4 次磁盘 I/O（即从根节点遍历到叶子节点）。这是性能上的巨大优势。

非常适合范围查询

这是 B+ 树相对于 B 树的一个显著优势。

• 数据结构：B+ 树的所有数据记录都存储在叶子节点上，并按键值大小顺序链接成一个双向链表。内部节点（非叶子节点）只存储键值和指向子节点的指针，不存储实际数据。
• 查询过程：
  1. 通过根节点和内部节点快速定位到范围查询的起始点（一次快速的近似查找）。
  2. 到达叶子节点后，只需沿着链表的指针顺序扫描即可，不需要再回溯到上层节点。
• 结果：范围查询（如 WHERE id BETWEEN 100 AND 500）效率极高，几乎是顺序读取，速度非常快。而 B 树进行范围查询则需要在内部节点和叶子节点之间来回移动，效率低下。

查询性能稳定

• 所有查询路径等长：由于 B+ 树是平衡树，从根节点到任何一个叶子节点的路径长度都是完全相同的。这意味着查询任何一条记录（无论是第一条、最后一条还是中间任意一条）的耗时都非常稳定，都是 O(log n) 的时间复杂度。
• 结果：提供了稳定可靠的平均性能，没有性能波动风险。

更高的空间利用率和缓存效率

• 内部节点无数据：因为非叶子节点不存储实际数据行，所以每个节点可以容纳更多的键值和指针（更高的扇出）。这使得树更“胖”，高度更低，进一步减少了 I/O 次数。
• 缓存友好：数据库管理系统（DBMS）有自己的缓冲池（Buffer Pool）。由于非叶子节点更小，在一次磁盘 I/O 中，可以将更多的节点数据加载到内存中。或者，在内存有限的情况下，缓冲池可以缓存更多的上层节点（如根节点和经常访问的中间节点），使得大多数查询可能在 1-2 次 I/O 内完成。

全表扫描更方便

• 如果需要进行全表扫描，B+ 树不需要像 B 树那样对整棵树进行复杂的中序遍历。它只需要对叶子节点组成的链表进行一次简单的顺序遍历即可，效率非常高。

B+ 树 vs B 树

• B+ 树只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+ 树的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
• 另外，B+ 树叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

B+ 树 vs 二叉树

• 对于有 N 个叶子节点的 B+ 树，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。
• 在实际的应用当中， d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+ 树的高度依然维持在 1~3 层左右，也就是说一次数据查询操作只需要做 1~3 次的磁盘 I/O 操作就能查询到目标数据。
• 而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+ 树高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

一言以蔽之，使用 B+ 树，而不是二叉树，是为了减少树的高度，也就是为了减少磁盘 I/O 次数。

B+ 树索引和 Hash 索引的差异

• B+ 树索引支持范围查询；Hash 索引不支持。
• B+ 树索引支持联合索引的最左匹配原则；Hash 索引不支持。
• B+ 树索引支持排序；Hash 索引不支持。
• B+ 树索引支持模糊查询；Hash 索引不支持。
• Hash 索引的等值查询比 B+ 树索引效率高。

综上，Hash 索引的应用场景很苛刻，不适用于绝大多数场景。

【中等】B+ 树索引能存多少数据？🌟

在 InnoDB 存储引擎中，B+ 树默认数据页大小为 16KB。

所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。随着插入 B+ 树索引的记录变多，1 个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2。

非叶子节点可存储的记录数

根节点和中间节点存放的是索引键对，由（索引键、指针）组成。

索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。若主键是 BIGINT 类型，占 8 个字节。也即是说，这样的一个键值对需要 8+6 = 14 字节。

非叶子节点可存储的记录数 = 页大小（16K） / 键值对大小（14） ≈ 1100

叶子节点可存储的记录数

为了方便计算，假设数据记录的平均大小为 1000 字节（实际一般小于这个值），则

叶子节点可存储的记录数 = 页大小（16K） / 记录平均大小（1000） ≈ 16

由此可知，树高度为 2 的 B+ 树索引，有一个根节点，约 1100 个叶子节点。因此，最多能存放的记录数为：

二层 B+ 树记录数 ≈ 1100 * 16 =  17,600

如何推算不同高度的 B+ 树可存储的记录数

综上所述，数据记录的平均大小为 1000 字节，主键为 BIGINT 的表，可以按如下推算其可存储的记录数：

• 高度为 2 的 B+树索引最多能存放约 1100 * 16 = 17,600 条记录（约 1.76 万），查询时只需 2 次 I/O。
• 高度为 3 的 B+树索引最多能存放约 1100 * 1100 * 16 = 19,360,000 条记录（约 2 千万），查询时只需 3 次 I/O。
• 高度为 4 的 B+树索引最多能存放约 1100 * 1100 * 1100 * 16 = 21,296,000,000 条记录（约 200 多亿），查询时只需 4 次 I/O。

优化 B+ 树索引的插入性能：

• 顺序插入（如自增 ID 或时间列）的维护代价小，性能较好。
• 无序插入（如用户昵称）会导致页分裂、旋转等开销较大的操作，影响性能。
• 主键设计应尽量使用顺序值（如自增 ID），以保证高并发场景下的性能。

聚簇索引和非聚簇索引

【中等】聚簇索引和非聚簇索引有什么区别？🌟🌟

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引又被称为聚簇索引（clustered index），其叶子节点存的是整行数据。

• 聚簇表示数据行和相邻的键值紧凑地存储在一起，因为数据紧凑，所以访问快。
• 因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
• InnoDB 的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。

非主键索引又被称为二级索引（secondary index），其叶子节点存的是主键的值。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。

• 如果语句是 select * from T where ID=500，即聚簇索引查询方式，则只需要搜索主键索引树；
• 如果语句是 select * from T where k=5，即非聚簇索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非聚簇索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

显然，主键长度越小，非聚簇索引的叶子节点就越小，非聚簇索引占用的空间也就越小。

【简单】什么是覆盖索引？

覆盖索引是指：二级索引上的信息满足查询所需的所有字段，不需要回表查询聚簇索引上的数据。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

字段特性索引

【简单】AUTO_INCREMENT 列达到最大值时会发生什么？

配置主键

在 MySQL 中，如果表定义的自增 ID 到达上限后，再申请下一个 ID，得到的值不变！因此会导致重复值的错误。

没有配置主键

如果 InnoDB 表中没有配置主键，InnoDB 会自动创建一个不可见的、长度为 6 个字节的 row_id 作为默认主键。

InnoDB 在全局维护一个 dict_sys.row_id 值。每次插入一行数据时，都会获取当前的 dict_sys.row_id 值，并将其加 1。row_id 的范围是 0 到 2^48 - 1。当 row_id 达到上限后，会从 0 开始重新循环。

如果插入的新数据的 row_id 在表中已存在，老数据会被新数据覆盖，且不会产生任何报错。可以通过 gdb 动态修改 dict_sys.row_id 来验证这一行为。

【简单】普通键和唯一键，应该怎么选择？

唯一索引的主要作用是保证数据的唯一性，而普通索引则更灵活。在业务代码保证不会写入重复数据的情况下，普通索引和唯一索引在查询性能上几乎没有差别。

普通索引 在更新操作中性能更优，尤其是在写多读少的场景下，能够利用 change buffer 减少磁盘 I/O。

唯一索引 适用于需要保证数据唯一性的场景，但在更新操作中性能较差，因为它无法使用 change buffer。

在业务允许的情况下，优先选择普通索引，因为它可以利用 change buffer 来提升更新性能。如果业务要求必须保证数据的唯一性，则必须使用唯一索引。

查询过程的性能差异：对于查询操作，普通索引和唯一索引的性能差异微乎其微。唯一索引在找到第一个满足条件的记录后会停止检索，而普通索引需要继续查找下一个记录，但由于数据页的读取方式，这种差异可以忽略不计。

更新过程的性能差异：更新操作中，普通索引可以利用 change buffer 来优化性能，而唯一索引则不能使用 change buffer。

• change buffer 是一种将更新操作缓存在内存中的机制，减少了对磁盘的随机读取，从而提升了更新操作的性能。
• 唯一索引在更新时需要检查唯一性约束，必须将数据页读入内存，增加了磁盘 I/O 的开销。

change buffer 的应用

• change buffer 的数据是持久化的，即使机器掉电重启，change buffer 中的数据也不会丢失，因为它会被写入磁盘。
• change buffer 适用于写多读少的场景，如账单类、日志类系统，因为这些场景下数据页在写入后不会立即被访问，change buffer 可以显著减少磁盘 I/O。
• 对于写后立即查询的场景，change buffer 的效果不明显，甚至可能增加维护成本。

change buffer vs. redo log

• redo log 主要减少随机写磁盘的 I/O 消耗，将随机写转换为顺序写。
• change buffer 主要减少随机读磁盘的 I/O 消耗，通过缓存更新操作来减少磁盘读取。

【中等】为什么不推荐使用外键？

逻辑外键是一种在应用程序层面上管理和维护数据完整性的方法，而不是通过数据库本身的外键约束。主要是利用应用程序代码来保证引用的完整性。

逻辑外键的优缺点：

优点：

• 灵活性高：应用程序层面控制，可以更灵活地实现复杂的业务逻辑。
• 性能优化：避免了数据库层面的约束检查，可以在某些情况下提高性能（详细看扩展知识）。
• 跨数据库兼容性：逻辑外键在不同类型的数据之间更容易迁移。

缺点：

• 代码复杂性增加：需要在应用程序代码中手动实现和维护引用完整性，增加了代码的复杂性和错误的可能性。
• 一致性风险：如果应用程序代码未正确实现引用完整性检查，可能导致数据不一致。
• 维护成本高：逻辑外键需要开发人员持续关注和维护，增加了维护成本。

物理外键的优缺点：

优点：

• 自动维护引用完整性：数据库会自动检查和维护外键约束，确保数据的一致性。
• 减少应用层复杂性：开发人员不需要手动管理引用完整性，减少了代码的复杂性和错误的可能性。
• 数据完整性保障：数据库层面的约束能够更有效地防止非法数据的插入或更新。

缺点：

• 性能开销：外键约束会增加插入、更新和删除操作的开销，特别是在处理大量数据时。
• 迁移和复制的复杂性：在进行数据库迁移或复制时，外键约束可能会增加复杂性，需要小心处理。
• 灵活性较低：物理外键在某些复杂业务逻辑下可能不够灵活，需要更多的手动控制。

【中等】什么是前缀索引？

“前缀索引”是指索引开始的部分字符。对于 BLOB/TEXT 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。

前缀索引的优点是可以大大节约索引空间，从而提高索引效率。

前缀索引的缺点是会降低索引的区分度。此外，order by 无法使用前缀索引，无法把前缀索引用作覆盖索引。

字段个数索引

【中等】什么是索引最左匹配原则？🌟🌟🌟

使用联合索引时，查询条件必须从索引的最左列开始匹配。

其底层原理是，InnoDB 的索引采用 B+ 树数据结构，按字段顺序存储。其存储结构，决定了其查询时，必须遵循从左到右的顺序。

具体来说，最左匹配原则有以下几个要点：

为方便直观的阐述，这里假设有一张表 t，设置了联合索引 (a, b, c)。

• 必须包含最左列：如果查询条件中不包含最左列，则联合索引失效。即查询条件必须包含 a 才能使用索引。
  • ✅ WHERE a=1（能用索引）。
  • ❌ WHERE b=2（不能使用索引，因为跳过了 a）。
• 连续匹配：不能跳过中间列，否则后面的列无法索引。
  • ✅ WHERE a=1 AND b=2（能使用 a, b 两列索引）。
  • ❌ WHERE a=1 AND c=3（只能用到 a，c 无法索引，因为跳过了 b）。值得一提的是，MySQL 5.6 支持了索引下推（InnoDB 和 MyISAM 支持），允许这种情况下，将匹配 a 字段的数据推送到引擎层，由引擎层在这些数据中过滤出匹配 c 字段的数据，以此提升查询效率。
• 遇到范围查询，右侧失效：
  • 遇到 >、<、前缀 LIKE（%xx） 会停止匹配
  • 注意：若遇到 >=、<=、BETWEEN、后缀 LIKE（xx%）这种范围查询，不会停止匹配，因为这些查询包含一个等值判断，可以直接定位到某个数据，向后扫描

最左前缀匹配命中示例

假设有索引 (name, age, city)：

✅ WHERE name='Alice' AND age=25 AND city='Beijing'  -- 完整使用索引
✅ WHERE name='Alice' AND age=25                     -- 使用 name 和 age
✅ WHERE name='Alice'                               -- 仅使用 name
❌ WHERE age=25 AND city='Beijing'                  -- 跳过了 name，无法使用索引
❌ WHERE name='Alice' AND city='Beijing'            -- 只能用到 name，跳过了 age，但可以应用索引下推

综上，优化 SQL 时，应尽量让查询条件符合最左前缀匹配原则，以提高查询效率。

【中等】什么是索引下推？🌟🌟

索引下推是一种减少回表查询、提高查询效率的技术。索引下推主要应用于联合索引。

它允许 MySQL 在使用索引查找数据时，将部分查询条件下推到存储引擎层进行过滤，从而减少需要从表中读取的数据行，减少 IO 操作。

索引下推注意点：

• MySQL 5.6 及以后版本支持索引下推，适用于 InnoDB 和 MyISAM 存储引擎。
• 综上，包含子查询，索引下推可能不会生效。
• 使用函数或表达式时，索引下推不会生效。
• 使用聚簇索引（主键）查询时，索引下推不会生效，因为它主要针对非聚簇索引。

扩展阅读：https://zhuanlan.zhihu.com/p/121084592

参考资料

• 《高性能 MySQL》
• 极客时间教程 - MySQL 实战 45 讲
• 图解 MySQL 介绍