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
操作。 - 避免表锁