[每周一更]-(第122期)：模拟面试｜数据库面试思路解析
10｜数据库索引：为什么 MySQL 用 B+ 树而不用 B 树？
-
为什么 MySQL 用 B+ 树而不用 B 树？
-
什么是覆盖索引？
-
什么是聚簇索引/非聚簇索引？
-
什么是哈希索引？MySQL InnoDB 引擎怎么创建一个哈希索引？
-
什么回表？如何避免回表？
-
树的高度和查询性能是什么关系？
-
什么是索引最左匹配原则？
-
范围查询、Like 之类的查询怎么影响数据库使用索引？
-
索引是不是越多越好？
-
使用索引有什么代价？
-
如何选择合适的索引列？组合索引里面怎么确定列的顺序？状态类的列是否适合作为索引的列？
-
为什么 MySQL 使用 B+ 树作为索引的数据结构？为什么不用 B 树？为什么不用红黑树？为什么不用二叉平衡树？为什么不用跳表？
-
NULL 对索引有什么影响？
-
唯一索引是否允许多个NULL 值？
1.为什么 MySQL 用 B+ 树而不用 B 树？
- B+ 树节点只有索引，而数据存储在叶子节点，并通过指针连接各叶子节点，便于范围查询、顺序扫描和批量读取。相比之下，B 树的数据分布在所有节点上，范围查询效率较低。
- MySQL 使用 B+ 树而不是 B 树是因为 B+ 树将所有数据都保存在叶子节点，并将这些节点按顺序连接。这使得 B+ 树适合范围查询和顺序扫描，尤其是批量读取时可以减少磁盘 I/O 次数，提高查询性能。B 树则将数据存储在所有节点中，没有叶子节点间的顺序连接，不适合数据库的常见查询模式。
2.什么是覆盖索引？
- 覆盖索引指的是在查询时，只需要从索引中获取数据而不必回表，因为查询所需的字段已包含在索引中。这可以减少 I/O 操作，提升查询效率。
3.什么是聚簇索引/非聚簇索引？
- 聚簇索引将数据和索引存储在一起。主键是聚簇索引，通过主键可以快速找到数据行。InnoDB 使用主键作为聚簇索引。
- 非聚簇索引则将索引和数据分离存储，索引中保存了指向数据行的地址。查询时需要先查找索引，再通过索引指向数据行位置。查询非聚簇索引数据通常需要额外的“回表”操作。
4.什么是哈希索引？MySQL InnoDB 引擎怎么创建一个哈希索引？
- 哈希索引通过哈希函数来加速等值查询（
=）。InnoDB 自适应哈希索引自动为高频查询生成哈希索引，但用户不能手动创建。
5.什么是回表？如何避免回表？
- 回表指索引未包含查询的所有字段，导致数据库需从表中再次查找完整数据。可以使用覆盖索引或调整查询字段避免回表。
6.树的高度和查询性能是什么关系？
- 树的高度越低，查询性能越高。因为 B+ 树每层节点包含多个分支（多叉树），一般 2-3 层即可覆盖大数据量，通过较少的磁盘读取完成查询，保证查询高效。
7.什么是索引最左匹配原则？
- 对于组合索引（如
(a, b, c)），最左匹配原则是优先使用最左边的字段；即a可以用索引，a, b可以用，b, c则无法单独利用索引。
8.范围查询、Like 之类的查询怎么影响数据库使用索引？
- 范围查询（
<、<=、>、>=、BETWEEN）会停止组合索引的最左匹配原则，且范围查询后面的字段无法利用索引。 LIKE查询中，只有前缀匹配（LIKE 'abc%'）能用索引，前缀带%（LIKE '%abc'）则无法利用索引。
9.索引是不是越多越好？
- 不是。索引会增加存储开销和更新成本，维护过多索引会影响写入和更新性能。因此应根据查询需求合理创建索引。
10.使用索引有什么代价？
- 索引占用存储空间，更新或插入数据时需要维护索引，导致写入性能下降。
11.如何选择合适的索引列？组合索引里面怎么确定列的顺序？状态类的列是否适合作为索引的列？
-
根据查询频率、过滤性选择列。组合索引中的顺序按选择性高的字段优先排列，确保常用查询能有效利用索引。
-
状态类列如性别、状态等通常选择性低（例如只有 0 和 1），适合作为复合索引的一部分，但不适合单独作为索引列。
12.为什么 MySQL 使用 B+ 树作为索引的数据结构？为什么不用 B 树？为什么不用红黑树？为什么不用二叉平衡树？为什么不用跳表？
- B+ 树的叶子节点链表结构有利于范围查询；多叉树的低树高适合存储和查询大数据。
- 红黑树、二叉平衡树、跳表在大数据场景下的树高和随机 I/O 操作较多，性能不如 B+ 树。
13.NULL 对索引有什么影响？
- 对于 B+ 树索引，
NULL值可以索引，但可能影响查询优化器的选择，特别在 WHERE 子句有多条件查询时，尽量避免索引字段中存储NULL值。
14.唯一索引是否允许多个 NULL 值？
- 是的，唯一索引允许多个
NULL，因为NULL被认为是未知值，两个NULL不相等。
11｜SQL 优化：如何发现 SQL 中的问题？
- 请你解释一下 EXPALIN 命令。
- 你有优化过 SQL 吗？具体是怎么优化的？
- 你有没有优化过索引？怎么优化的？
- 怎么优化 COUNT 查询？
- 怎么优化 ORDER BY？
- 怎么优化 LIMIT OFFSET 查询？
- 为什么要尽量把条件写到 WHERE 而不是写到 HAVING 里面？
- 怎么给一张表添加新的索引/修改表结构？如果我的数据量很大呢？
- USE INDEX/FORCE INDEX/IGNORE INDEX 有什么效果？
1.请解释一下 EXPLAIN 命令。
EXPLAIN命令可以帮助查看 SQL 语句的执行计划，包括如何选择索引、扫描方式、行数预估等信息。它提供关键字段如type、key、rows、extra，用于分析查询效率，找出性能瓶颈。
2.你有优化过 SQL 吗？具体是怎么优化的？
- SQL 优化常见方法包括：创建合适的索引、避免全表扫描、使用覆盖索引、减少子查询、优化
JOIN语句、将频繁查询的结果缓存等。例如，将子查询替换为JOIN，或通过覆盖索引来减少回表查询。
3.你有没有优化过索引？怎么优化的？
- 索引优化包括创建合适的单列索引或组合索引，调整组合索引的列顺序，避免冗余索引，删除不常用或选择性低的索引等。此外，使用 EXPLAIN 查看索引的使用情况，有助于识别低效或无效索引。
4.怎么优化 COUNT 查询？
-
优化
COUNT查询可以使用：
- 使用统计缓存表来存储记录数。
- 当仅需检查数据存在时，用
COUNT(1)或EXISTS来替代COUNT(*)。 - 对于大数据量表，避免用
COUNT直接查询整个表的行数，可以分表、分区或统计汇总表来提高效率。
5.怎么优化 ORDER BY？
- ORDER BY 可以通过索引优化，比如对排序字段创建索引。对于大数据量的 ORDER BY，可以使用 LIMIT 限制返回的行数，或借助覆盖索引（比如
ORDER BY使用索引字段且符合最左前缀原则），避免排序操作耗时过长。
6.怎么优化 LIMIT OFFSET 查询？
- LIMIT OFFSET 查询性能差的原因是数据库必须扫描和丢弃 OFFSET 前的数据。优化方法包括：
- 使用覆盖索引优化。
- 用
JOIN查询来实现分页，将上次查询的最大 ID 作为起始点，这样避免了大量偏移数据扫描。
7.为什么要尽量把条件写到 WHERE 而不是写到 HAVING 里面？
WHERE在数据聚合前过滤数据，效率更高，而HAVING在聚合后进行过滤，适合用于聚合函数的筛选。尽量将不涉及聚合的条件放在WHERE，减少处理的数据量，加快查询速度。
8.怎么给一张表添加新的索引/修改表结构？如果我的数据量很大呢？
- 对大表添加索引或修改结构可以使用在线索引创建工具或分批操作，如 Percona 提供的
pt-online-schema-change。避免直接在生产环境大表上添加索引，否则会导致锁表和性能下降。
9.USE INDEX/FORCE INDEX/IGNORE INDEX 有什么效果？
- USE INDEX 指示查询使用指定索引。
- FORCE INDEX 强制查询使用指定索引，即使优化器认为其他方式更好。
- IGNORE INDEX 忽略指定索引，从而让优化器选择其他索引或进行全表扫描。
12｜数据库锁：明明有行锁，怎么突然就加了表锁？
- 什么是行锁、表锁？什么时候加表锁？怎么避免？
- 什么是乐观锁？怎么在 MySQL 里面实现一个乐观锁？
- 什么是意向锁？可以举一个例子吗？
- 什么是共享锁和排它锁？它们有什么特性？
- 什么是两阶段加锁？
- 什么是记录锁、间隙锁和临键锁？
- RC 级别有间隙锁和临键锁吗？
- MySQL 是怎么在 RR 级别下解决幻读的？
- 什么情况下会加临键锁？什么情况下会加间隙锁？什么时候加记录锁？
- 唯一索引和普通索引会怎么影响锁？
- 你遇到过什么死锁问题吗？怎么排查的？最终又是怎么解决的？
- 你有没有优化过锁？怎么优化的？
1.什么是行锁、表锁？什么时候加表锁？怎么避免？
- 行锁：针对单行数据的锁，粒度小，适合并发场景；主要用于
UPDATE、DELETE操作。 - 表锁：锁住整张表，适合低并发、大批量操作。MySQL 自动加表锁的情况一般包括：当未命中索引时的
DELETE、UPDATE或执行ALTER操作。 - 避免表锁