SQL-牛客面经八股

1. 什么是索引？索引有哪些分类？

索引(index)是一种用于快速定位和访问数据的数据结构，类似于书籍的目录。

按【数据结构】可分为：B+树索引、哈希(Hash)索引、位图索引、R树索引。
按【功能用途】可分为：主键索引（基于主键字段创建的索引，唯一标识表中的每一行。）、唯一索引（确保索引列的值唯一，但允许有一个空值。）、普通索引（用于快速检索，无特别限制）、全文索引（用于全文搜索，适合大文本字段。）。
按【索引列】可分为：单列索引、联合索引(遵循最左前缀原则)。
按【存储方式】可分为：聚簇索引、非聚簇索引。

2. Hash 索引与 BTree 索引有什么区别？

Hash索引是是键值对索引，通过键的哈希值直接查询存储的值。
而BTree是根据索引字段值的大小关系去有序存储，BTree的查询是不断缩小查找范围，最终得到查找的数据。
两者相比，hash的查找速度更快，但是也没有快很多，这也看BTree层数的高度和数据量的大小，但是BTree更适合范围查找，通过数据的有序性，查询某一段区间的数据会更快。

3. 为什么 MySQL 采用 B+ 树作为索引？

MySQL索引结构是指MySQL中用于提高查询效率的数据结构，常见的能快速搜索的数据结构有：哈希表、二叉排序树、平衡二叉树、红黑树、B树、B+树等

哈希表，用唯一的key查对应的value，时间复杂度O(1)，不支持范围查询和排序
二叉排序树，支持范围查询和排序，顺序插入时退化为链表（链表查询时间复杂度O(n)）
平衡二叉树，通过旋转操作可避免退化为链表，插入删除时频繁的旋转会产生大量的磁盘I/O
红黑树，不追求决定的平衡，减少插入删除时的旋转次数减少c了磁盘I/O，红黑树节点大小固定，存储大量数据时，树高增长过快，查找性能下降（更适合存储少量数据）
B树，是多路平衡排序树，无论是叶子节点还是非叶子节点，都会保存数据，树高但是不会过高，但B树节点既存数据又存索引，导致一个节点中存不了多少索引，要同样保存大量数据，树高增加，性能降低；同时B树进行范围查询时，需要回溯整个数据结构，效率低，会产生随机I/O。所以B树也不行得上B+树
B+树，也是多路平衡排序树，非叶子节点只存索引，不存数据，树高更低，查询性能更好；B+树的数据只存于叶子节点，由双向链表连接，范围查询只需遍历链表，无需回溯整个数据结构性能更好

所以MySQL选用B+树保存索引

4. B+ 树查询数据的全过程？

在B+树中，由叶子节点存储实际的数据行记录，查询数据是层级递归与页内二分+链表遍历的结合，查找过程：

查询从B+树的根节点开始，通过比较目标键值与节点中存储的索引键值（通常采用二分查找），确定数据落在哪个区间，从而选择对应的分支继续向下查找。这一过程逐层递归，从上到下最终定位到目标叶子节点
叶子节点大小为16KB大小，存储的数据行不止一条，对叶子节点中数据行以组的形式划分，利用页目录结构，通过二分查找页目录中的槽，可以快速定位到包含目标数据行的组。
定位到目标组后，通过链表遍历（通常是单向或双向链表）逐条检查数据行，直到找到完全匹配的记录

5. 三层 B+ 树能存多少数据？

假设一个节点的分支因子可以存m个键值，第二层就可以存m平方个键值，第三层可以存m立方层，具体取决于数据的多少

6. 聚簇索引与非聚簇索引的区别？

聚簇索引与非聚簇索引的区别在于数据存储方式和访问效率。
聚簇索引将数据行存储在叶子节点，数据的物理顺序与索引顺序一致，适合范围查询和排序操作；
而非聚簇索引的叶子节点存储的是数据行的指针，数据的物理顺序与索引顺序无关，适合快速查找特定值。
聚簇索引通常只能有一个，而非聚簇索引可以有多个。

7. 联合索引的存储结构与最左前缀原则

联合索引的存储结构是将多个列的值组合在一起形成一个复合键，按照字典序进行排序存储。
最左前缀原则指的是在使用联合索引时，查询条件必须从索引的最左列开始，才能有效利用索引进行加速查询。
只有满足最左前缀的查询条件，才能充分发挥联合索引的性能优势。

8. 在 SELECT IN / 模糊查询中如何使用索引？

在SELECT IN中，需要对IN列表的值和索引列的值进行匹配来加速查询,模糊查询中使用索引需要分情况讨论：

如果是前缀匹配“acs%”，索引可以有效快速定位，加速查询；
如果是后缀匹配“%acs”，需要通过全表索引；
如果是全模糊匹配“%acs%”，则普通索引无效，需要全文检索；

9. 建索引时需注意哪些事项？

给频繁查询的字段建立、不要给非频繁查询的字段建立。
索引不宜建立太多
不要给频繁更新的字段建立
不给低选择性字段建立，如果查询频繁，可以使用复合索引，用高选择性的索引进行复合，提高整体的索引选择性。

10. 如何评估 / 判断索引是否生效？常见索引失效场景有哪些？

使用EXPLAIN命令查看SQL的执行计划，如果key列显示了使用索引的名称，并且type列为ref，range，const等情况，则说明索引生效。
常见的索引失效场景：

对索引列使用函数，计算或者表达式。
发生了隐式类型转换（使用数字查询字符串类索引）。
使用了!=, <>, NOT IN, IS NOT NULL。
使用了以通配符开头的LIKE查询。
复合索引未遵循最左前缀原则。

11. 索引失效后怎么办？

当索引失效时，首先需要分析查询语句，检查是否有不符合索引使用条件的部分，如函数操作、隐式类型转换或不满足最左前缀原则等。
其次，考虑重构查询语句，使其符合索引使用条件。此外，可以通过分析执行计划来识别问题所在，并根据需要调整索引策略或添加新的索引。
最后，定期维护和优化数据库索引，以确保其在不断变化的查询模式下保持有效。

12. 数据库索引重构过程了解吗？

重构过程就是对现有索引进行分析，优化和调整，重建索引恢复其快速查找的性能。实施步骤：

使用分析工具EXPLAIN识别低效，未使用的索引；
评估索引的选择性和覆盖率，确保索引能够有效过滤数据；
删除冗余或者影响性能的索引，添加新的有效索引；
定期维护和优化索引。

13. 数据库为什么使用B+树而不使用红黑树？

两者都是基于树的结构。B+树中的节点包含多个键值，减少树的高度，从而减少了磁盘的I/O次数，提高了查询效率。
此外，B+树的叶子节点，通过链表连接，支持顺序和范围查询。
红黑树每个节点存储1个键值，树高增长过快，查询效率低。

14. 数据库分页的实现方式？

数据库分页通常通过LIMIT和OFFSET子句实现，LIMIT指定返回的记录数，OFFSET指定起始位置，从而实现数据的分段提取。
这种方式简单易用，但在大数据集上性能可能较差。
为提高性能，可以使用基于索引的分页，通过记录上次查询的最后一个主键值来获取下一页数据，避免使用OFFSET。这种方法减少了不必要的记录扫描，提高了分页查询的效率。

15. LIMIT 100000000,10 与 LIMIT 10 性能差异？

在 SQL 查询中，LIMIT子句用于限制返回的记录数，但其性能取决于偏移量的大小：

LIMIT 10
- 直接从结果集的开头获取 10 条记录，速度快，性能较高。
LIMIT 100000000,10
- 需要扫描并跳过前 100,000,000 条记录，然后再获取 10 条记录。这会导致大量的记录扫描和较高的 I/O 开销，性能较差
性能差异原因
- I/O 开销：大偏移量导致大量磁盘读取操作。
- 计算开销：需要处理和跳过大量数据。

16. 如何使用 EXPLAIN 分析查询？关注哪些列？

使用EXPLAIN分析查询时，关键列包括：

id：查询中每个子查询的标识符，值越大优先级越高，表示执行顺序。
select_type：查询的类型，显示查询是简单查询、联合查询还是子查询等。
table：显示查询涉及的表。
type：访问类型，表示 MySQL 如何查找表中的行。常见类型有ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）等。性能从好到差依次为：system>const>eq_ref>ref>range>index>ALL。
possible_keys：查询中可能使用的索引。
key：实际使用的索引。
key_len：使用的索引的长度。
ref：显示索引的哪一列被使用，以及与之比较的常量。
rows：估计需要读取的行数，值越小越好。
Extra：包含额外的信息，如Using index（使用覆盖索引）、Using where（使用 WHERE 过滤）、Using temporary（使用临时表）、Using filesort（使用文件排序）等。

17. COUNT(*)、COUNT(1)、COUNT(列) 有啥区别？

COUNT（*）统计表中所有行的数量，包括NULL值；
COUNT(1) 统计表中所有行的数量，包括NULL值，与COUNT(*)一致，1是一个常量；
COUNT(列）统计表中指定列非NULL值的行的数量。
速度count(*)>count(1)>count(列）

18. SQL 聚合函数有哪些？

SQL 聚合函数包括COUNT、SUM、AVG、MIN、MAX等，用于对数据集进行汇总和统计分析。

19. WHERE 与 HAVING 的区别？

where是在分组前过滤原始数据行，不能使用聚合函数
having是在分组后对分组的数据进行过滤，可以使用聚合函数

20. EXISTS 与 IN 的区别？

工作原理
- EXISTS：检查子查询是否返回至少一行结果，返回布尔值。
- IN：检查主查询的值是否在子查询返回的结果集中。
使用场景
- EXISTS：适合在子查询中使用复杂条件，尤其是关联查询。
- IN：适合在子查询返回单列结果时使用。
性能
- EXISTS：通常在子查询返回大量数据时更高效，因为它在找到第一条匹配记录后就停止。
- IN：在子查询结果较小时性能较好，但在大数据集上可能较慢。
NULL 处理
- IN：如果子查询结果包含NULL，可能导致不期望的结果。
- EXISTS：不受NULL影响。

21. 谈谈 SQL 注入及防范

SQL 注入是一种通过插入恶意 SQL 代码来攻击数据库的技术，防范措施包括使用参数化查询、输入验证和最小权限原则。

22. 将一张表部分数据更新到另一张表的写法？

将表A的部分数据更新到表B的核心方法是使用 UPDATE JOIN 或子查询，通过关联条件锁定目标行，用源表数据覆盖目标表字段，需注意数据库语法差异及性能优化。

23. 如何将行转成列（行列转换）？

行转列（行列转换）通常通过条件聚合（如CASE WHEN或MAX(IF())）或PIVOT操作实现，将多行数据按照某一字段的值展开为多列，常用于报表统计和数据透视。

24. 表之间如何关联？

表关联主要通过外键和JOIN操作实现。常见的关联方式有内连接、左连接、右连接、全连接。

内连接：inner join on ;返回两个表中相匹配的记录。
左连接：left join on; 返回左边的所有记录，以及右表相匹配的记录，没有匹配则为NULL。
右连接：right join on; 返回右表的所有记录，以及右表相匹配的记录，没匹配则为NULL。
全连接：full join on;返回两个表中的所有记录，匹配不到的部分用NULL对齐。

25. INNER / LEFT / RIGHT JOIN 区别？

inner join:内连接。只返回两个表中匹配的记录。
left join:左连接。返回左表的所有记录，以及右表相匹配的记录，不匹配的用NULL表示。返回表的行数大于等于左表行数。
right join:右连接。返回右表的所有记录，以及左表相匹配的记录，不匹配的用NULL表示。返回表的行数大于等于右表行数。

26. 为什么不推荐多表 JOIN？

多表JOIN可能导致复杂查询计划、性能下降和维护困难，尤其在大数据集和不当索引情况下，会显著影响查询效率

27. SQL 调优常见方法有哪些？

合理使用索引

创建高效索引：在查询条件、排序、分组和连接字段上建立合适的索引。
避免冗余和低效索引：定期清理无用或重复的索引，防止写入性能下降。
覆盖索引：让索引包含查询所需的所有字段，减少回表操作。

优化查询语句结构

简化 SQL 语句：避免不必要的嵌套和复杂子查询。
只查需要的字段：用SELECT 字段替代SELECT *，减少数据传输。
合理使用 JOIN：避免多表复杂 JOIN，优先考虑业务需求。

减少数据扫描量

加过滤条件：在 WHERE 子句中尽量多用过滤条件，减少全表扫描。
分区表/分表：将大表拆分为多个小表，提高查询效率。
使用 LIMIT：限制返回结果的数量，避免一次性拉取大量数据。

利用缓存机制

数据库查询缓存：开启并合理配置数据库的查询缓存功能。
应用层缓存：对热点数据在应用层做缓存，减少数据库压力。

分析执行计划

使用 EXPLAIN：分析 SQL 的执行计划，找出性能瓶颈。
根据反馈优化：根据执行计划调整索引和 SQL 结构。

28. 如何监控并优化慢 SQL？

启用慢查询日志：在数据库中启用慢查询日志，记录执行时间超过指定阈值的查询。
分析查询计划：使用EXPLAIN分析慢查询的执行计划，识别性能瓶颈。
优化索引：为慢查询涉及的列创建或优化索引，减少全表扫描。
调整查询结构：使用子查询代替嵌套查询，减少数据处理量。
优化数据库配置：调整数据库参数，如内存分配、连接池大小。
使用缓存。

29. 如何高效批量插入数据？

使用INSERT INTO … VALUES语句，在同一事务中一次插入多行数据，并且在插入之前禁用索引，插入完成之后再重建索引。

30. 大表（千万级）查询 / 维护该怎么办？

优化索引，删除不必要的索引
避免select *
使用游标分页
分表，根据某个字段拆分
多加过滤条件

31. 删除（DELETE）、截断（TRUNCATE）、丢弃（DROP）区别？

DROP 是物理删除，用来删除整张表，包括表结构，且不能回滚。
DELETE 支持行级删除，可以带 WHERE 条件，可以回滚。
TRUNCATE 用于清空表中的所有数据，但会保留表结构，不能回滚。