2024上半年面经

MySQL 面试题

1.什么是内连接、外连接、交叉连接？

内连接：取两张表符合连接条件的记录

外连接：除取两张表符合连接条件的记录外，还取某张表或两张表中不满足连接条件记录

交叉连接：取两张表所有记录的一一对应

2.MySQL 的内连接、左连接、右连接有什么区别？

内连接：返回两张表符合连接条件的记录

左连接：返回左表的所有记录和右表满足连接条件的记录，如果右表没有满足条件的记录，则以 NULL 填充

右连接：返回右表的所有记录和左表满足连接条件的记录，如果左表没有满足条件的记录，则以 NULL 填充

3.数据库的三大范式

三大范式的作用是减少数据冗余，提高数据完整性

第一范式：确保表中每一列是不可分割的

第二范式：在第一范式的基础上，要求数据库表中的每一列和主键直接相关，而不能只与主键的某一部分相关

第三范式：在第二范式的基础上，消除非主键列对主键的传递依赖

4.varchar 与 char 的区别？

• varchar 是不定长字符串，char 是定长字符串
• varchar：插入数据是多长就按照多长存储，char：插入数据长度小于 char 的固定长度就用空格补齐
• varchar 存取速度慢于 char
• varchar 最大长度为 65532，char 最大长度 255

5.blob 和 text 有什么区别？

• blob 存储二进制数据，text 存储大字符串
• blob 没有字符集，text 有字符集，并根据字符集的校对规则对值进行排序和比较

6.DATETIME 和 TIMESTAMP 的异同？

相同：

1. 日期时间存储格式一致
2. 都可存储日期时间
3. 都能存储微妙，小数点后六位

不同：

1. 前者无默认值，后者有
2. 前者无时区信息，后者有
3. 前者占8个字节，后者4个
4. 前者日期范围：1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999，后者日期范围：1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC

7.MySQL 中 in 和 exists 的区别？

1. 查询两张表大小相当，二者差别不大
2. 如果两个表一个是大表一个是小表，则子查询表大的用exits，小的用in
3. 如果查询语句用到了 not in，那么内外表都要全部扫描，不用索引，而 not exits 的子查询能用索引。因此无论表大还是表小，后者性能更高

8.MySQL 里记录货币用什么字段类型比较好？

decimal 和 numric

9.MySQL 怎么存储 emoji？

MySQL 的 utf8 字符集仅支持最多 3 个字节的 UTF-8 字符，但是 emoji 表情（😊）是 4 个字节的 UTF-8 字符，所以在 MySQL 中存储 emoji 表情时，需要使用 utf8mb4 字符集。

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL 8.0 已经默认支持 utf8mb4 字符集，可以通过 SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; 查看。

10.delete、truncate、drop 的区别？

区别	delete	truncate	drop
类型	属于 DML	属于 DDL	属于 DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

11. UNION 与 UNION ALL 的区别？

• UNION 在表连接时会筛掉重复的记录行，UNION ALL 不会
• UNION ALL性能更高

12.count(1)、count(*) 与 count(列名) 的区别？

• count(1)：统计所有行数，但不关注行中的具体列数据，不会忽略 NULL 值
• count(*)：统计所有行数并检查行中的每列是否为 null ，不忽略 NULL 值
• count(列名)：统计那一列所有行，会忽略 NULL 值

13.一条 SQL 查询语句的执行顺序？

1. FROM：对 FROM 子句中的左表和右表执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1
2. ON：对虚拟表 VT1 应用 ON 筛选，只有那些符合的行才被插入虚拟表 VT2 中
3. JOIN：如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1）～步骤 3），直到处理完所有的表为止
4. WHERE：对虚拟表 VT3 应用 WHERE 过滤条件，只有符合的记录才被插入虚拟表 VT4 中
5. GROUP BY：根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5
6. CUBE|ROLLUP：对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6
7. HAVING：对虚拟表 VT6 应用 HAVING 过滤器，只有符合的记录才被插入虚拟表 VT7 中。
8. SELECT：第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中
9. DISTINCT：去除重复数据，产生虚拟表 VT9
10. ORDER BY：将虚拟表 VT9 中的记录按照进行排序操作，产生虚拟表 VT10。11）
11. LIMIT：取出指定行的记录，产生虚拟表 VT11，并返回给查询用户

14.介绍一下 MySQL bin 目录下的可执行文件

• mysql：客户端程序，连接 MySQL 服务器
• mysqldump：MySQL 数据库备份工具，用于创建一个或多个 MySQL 数据库级别的 SQL 转储文件，包含表结构和数据。对数据备份、迁移、恢复非常重要
• mysqladmin：MySQL 管理工具。可创建、删除数据库，查看 MySQL 服务器状态
• mysqlcheck：MySQL 提供的命令行工具，用于检查、修复、分析和优化数据库表，对数据库的维护和性能优化非常有用
• mysqlimport：用于从文本文件中导入数据到数据库表中，非常适合用于批量导入数据。
• mysqlshow：用于显示 MySQL 数据库服务器中的数据库、表、列等信息。
• mysqlbinlog：用于查看 MySQL 二进制日志文件的内容，可以用于恢复数据、查看数据变更等。

15.用过哪些 MySQL 函数？

用过哪些字符串函数来处理文本？

• CONCAT(): 连接两个或多个字符串。
• LENGTH(): 返回字符串的长度。
• SUBSTRING(): 从字符串中提取子字符串。
• REPLACE(): 替换字符串中的某部分。
• LOWER() 和 UPPER(): 分别将字符串转换为小写或大写。
• TRIM(): 去除字符串两侧的空格或其他指定字符。

用过哪些数值函数用过哪些数值函数？

• ABS(): 返回一个数的绝对值。
• CEILING(): 返回大于或等于给定数值的最小整数。
• FLOOR(): 返回小于或等于给定数值的最大整数。
• ROUND(): 四舍五入到指定的小数位数。
• MOD(): 返回除法操作的余数。

用过哪些日期和时间函数？

• NOW(): 返回当前的日期和时间。
• CURDATE(): 返回当前的日期。
• CURTIME(): 返回当前的时间。
• DATE_ADD() 和 DATE_SUB(): 在日期上加上或减去指定的时间间隔。
• DATEDIFF(): 返回两个日期之间的天数。
• DAY(), MONTH(), YEAR(): 分别返回日期的日、月、年部分。****

用过哪些汇总函数？

• SUM(): 计算数值列的总和。
• AVG(): 计算数值列的平均值。
• COUNT(): 计算某列的行数。
• MAX() 和 MIN(): 分别返回列中的最大值和最小值。
• GROUP_CONCAT(): 将多个行值连接为一个字符串。

16.说说 SQL 的隐式数据类型转换？

• 整数和浮点数相加会将整数转为浮点数
• 整数和字符串相加会将整数转为字符串

可以通过显示转换避免这种情况SELECT CAST('1' AS SIGNED INTEGER) + 1; -- 结果为 2

17.MySQL 的基础架构

MySQL 逻辑架构图主要分三层：

• 客户端：最上层的服务并不是 MySQL 所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
• Server 层：大多数 MySQL 的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
• 存储引擎层：第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。Server 层通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

18.一条 SQL 查询语句在 MySQL 中如何执行的？

第一步，客户端发送 SQL 查询语句到 MySQL 服务器。

第二步，MySQL 服务器的连接器开始处理这个请求，跟客户端建立连接、获取权限、管理连接。

第三步，解析器开始对 SQL 语句进行解析，检查语句是否符合 SQL 语法规则，确保引用的数据库、表和列都存在，并处理 SQL 语句中的名称解析和权限验证。

第四步，优化器负责确定 SQL 语句的执行计划，这包括选择使用哪些索引，以及决定表之间的连接顺序等。优化器会尝试找出最高效的方式来执行查询。

第五步，执行器会调用存储引擎的 API 来进行数据的读写。

第六步，MySQL 的存储引擎是插件式的，不同的存储引擎在细节上面有很大不同。例如，InnoDB 是支持事务的，而 MyISAM 是不支持的。之后，会将执行结果返回给客户端

第七步，客户端接收到查询结果，完成这次查询请求。

19.MySQL 有哪些常见存储引擎？

功能	InnoDB	MyISAM	MEMORY
支持事务	Yes	No	No
支持全文索引	Yes	Yes	No
支持 B+树索引	Yes	Yes	Yes
支持哈希索引	Yes	No	Yes
支持外键	Yes	No	No

拓展：

1. InnoDB 支持的哈希索引是自适应的，不能人为干预
2. InnoDB 的最小表空间略小于 10M，最大表空间取决于页面大小

MySQL 哈希索引是什么？它有什么用？

MySQL 哈希索引是一种基于哈希表实现的索引结构，它通过哈希函数将关键字映射为哈希值从而快速定位数据，数据是存储在内存上的而非磁盘

当数据被插入到表中时，数据库会使用哈希函数对索引列（或索引列的组合）的值进行计算，生成一个哈希值。数据库内部维护一个哈希表，每个槽对应一个哈希值范围，当发生哈希冲突时，数据不会直接存在槽中，而是根据具体的策略进行处理。比链地址法，槽会存储指向链表的指针和该记录的哈希值，相同哈希值的数据会存在链表中。

20.MySQL 存储引擎选择

• 正常情况或者需要事务、行级锁就选 InnoDB
• 读情况多，不需要事务，行级锁就用MyISAM
• MEMORY 在内存上创建临时表，所以速度快

21.InnoDB 和 MyISAM 主要有什么区别？

1. InnoDB 支持行级锁、外键、MVCC、事务，MyISAM 不支持
2. InnoDB 支持异常后的崩溃恢复，MyISAM 不支持
3. InnoDB 性能大于 MyISAM
4. InnoDB 为聚簇索引，数据和索引存在一起；MyISAM 为非聚簇索引，即数据和索引分开存储
5. InnoDB 使用缓冲池缓存索引页和数据页，MyISAM 使用键缓存，但仅缓存索引页

22.MySQL 日志文件有哪些？分别介绍下作用？

错误日志：记录 MySQL 服务器运行、启动、停止时出现的问题

慢查询日志：记录执行时间超过 long_query_time 值的所有 SQL 语句。可以用来识别和优化慢 SQL

一般查询日志：记录所有 MySQL 服务器连接信息和 SQL 语句。

二进制日志：记录所有修改数据库状态的 SQL 语句。用于数据备份、主从复制。

重做日志：记录每个对 InnoDB 表的写操作，是物理级别的，不是 SQL 级别。主要用于崩溃恢复。

回滚日志：记录数据被修改前的值。用于事务回滚。

23.binlog 和 redo log 有什么区别？

• redo log（重做日志）
  • 是物理日志，记录的是数据页的物理修改。它属于 MySQL InnoDB 存储引擎的一部分。
  • 用于保证崩溃恢复，在数据库崩溃后可以通过 redo log 恢复未完成的事务，确保数据一致性。
• bin log（二进制日志）
  • 是逻辑日志，记录的是 SQL 语句的执行。它属于 MySQL Server 层的一部分。
  • 用于数据恢复和主从复制，通过 bin log 可以将 SQL 语句重放到从库，实现数据同步。

24.一条更新语句怎么执行的了解吗？

1. 以update test set a = a + 1 where id = 2为例，执行器调用存储引擎找到 ID = 2 这一行，如果 ID = 2 的数据页本来就在内存中，就直接返回执行器 ，否则先从磁盘读入内存，再返回执行器。
2. 执行器拿到数据后把这个值 +1，然后再调用引擎接口写入这行新数据
3. 引擎将这行新数据更新到内存中，将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告诉执行器执行完成了，可随时提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的事务提交接口，引擎把刚刚写入的 redo log 改成提交状态

从上图可以看出，MySQL 在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对 binlog 进行写入，在 InnoDB 内进行 redo log 的写入。

25.bin log 写入时为什么要两阶段提交呢？

26.redo log 怎么刷入磁盘的知道吗？

redo log 的写入不是直接落到磁盘的，而是在内存中创建一片名为 redo log buffer 的连续内存空间，也就是 redo 日志缓冲区

什么时候会刷盘？

1. log buffer 空间不足
2. 事务提交时
3. 后台线程输入
4. 正常服务器关闭
5. 触发 checkpoint 规则

27.慢 SQL 如何定位

• 慢查询日志：开启 MySQL 慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，找出问题的根源。
• 服务监控：可以在业务的基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架过程，对服务运行中的慢 SQL 进行监控和告警。

找到对应的慢 SQL 后，使用 EXPLAIN 命令查看 MySQL 是如何执行 SQL 语句的，这会帮助我们找到问题的根源。

影响 SQL 执行效率的因素有：

①、IO 成本

• 数据量：数据量越大，IO 成本越高。所以要避免 select *；尽量分页查询。
• 数据从哪读取：尽量通过索引加快查询。

②、CPU 成本

• 尽量避免复杂的查询条件，如有必要，考虑对子查询结果进行过滤。
• 尽量缩减计算成本，比如说为排序字段加上索引，提高排序效率；比如说使用 union all 替代 union，减少去重处理。

28.有哪些方式优化 SQL？

通过 MySQL 自带的日志记录 set global slow_query_log = 'ON'，通过 show variables like '%slow_query_log%' 即可查询当前慢日志是否开启，以及存储的路径。通过 set global long_query_time = 3 即可设置慢 SQL 的阈值，3 就是 3 秒，当一个 SQL 执行的时间操作 3 秒，就会被记录到慢日志中。

29.怎么看执行计划 explain，如何理解其中各个字段的含义？

explain select * from students where id =9

• select_type
• type
• key
• possible_keys
• key_len
• rows

①、id 列：查询的标识符。

②、select_type 列：查询的类型。常见的类型有：

• SIMPLE：简单查询，不包含子查询或者 UNION 查询。
• PRIMARY：查询中如果包含子查询，则最外层查询被标记为 PRIMARY。
• SUBQUERY：子查询。
• DERIVED：派生表的 SELECT，FROM 子句的子查询。

③、table 列：查的哪个表。

④、type 列：表示 MySQL 在表中找到目标行的方式，性能从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。

• system，表只有一行，一般是系统表，往往不需要进行磁盘 IO，速度非常快
• const、eq_ref、ref：这些类型表示 MySQL 可以使用索引来查找单个行，其中 const 是最优的，表示查询最多返回一行。
• range：只检索给定范围的行，使用索引来检索。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
• index：遍历索引树读取。
• ALL：全表扫描，效率最低。

⑤、possible_keys 列：可能会用到的索引，但并不一定实际被使用。

⑥、key 列：实际使用的索引。如果为 NULL，则没有使用索引。

⑦、key_len 列：MySQL 决定使用的索引长度（以字节为单位）。当表有多个索引可用时，key_len 字段可以帮助识别哪个索引最有效。通常情况下，更短的 key_len 意味着数据库在比较键值时需要处理更少的数据。

⑧、ref 列：用于与索引列比较的值来源。

• const：表示常量，这个值是在查询中被固定的。例如在 WHERE column = 'value'中。
• 一个或多个列的名称，通常在 JOIN 操作中，表示 JOIN 条件依赖的字段。
• NULL，表示没有使用索引，或者查询使用的是全表扫描。

⑨、rows 列：估算查到结果集需要扫描的数据行数，原则上 rows 越少越好。

⑩、Extra 列：附加信息。

• Using index：表示只利用了索引。
• Using where：表示使用了 WHERE 过滤。
• Using temporary ：表示使用了临时表来存储中间结果。

示例：

30.能简单说一下索引的分类吗？

31.为什么使用索引会加快查询？

数据库文件是存在磁盘上的，磁盘 I/O 是数据库操作中最耗时的部分之一。没有索引时，数据库会进行全表扫描，相当于一行一行找数据。加上索引后，就可以直接跳到索引指示的数据位置，而不必扫描整张表，从而大大减少磁盘 I/O次数。

MySQL 的 InnoDB 存储引擎默认使用 B+ 树作为索引的数据结构，其时间复杂度为 O(logN)。

索引文件相比数据库文件非常小，查到索引后再映射到数据库记录，查询效率就会很高。

32.创建索引有哪些注意点？

①、选择合适的列作为索引

• 经常作为查询条件（WHERE 子句）、排序条件（ORDER BY 子句）、分组条件（GROUP BY 子句）的列是建立索引的好候选。
• 区分度低的字段，不要建索引，例如性别。
• 频繁更新的字段，不要作为主键或者索引
• 不建议用无序的值(例如身份证、UUID )作为索引，当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

②、避免过多的索引

• 每个索引都需要占用额外的磁盘空间。
• 更新表（INSERT、UPDATE、DELETE 操作）时，所有的索引都需要被更新。
• 维护索引文件需要成本；还会导致页分裂，IO 次数增多。

③、利用前缀索引和索引列的顺序

• 对于字符串类型的列，可以考虑使用前缀索引来减少索引大小。
• 在创建复合索引时，应该根据查询条件将最常用作过滤条件的列放在前面。

33.索引哪些情况会失效？

1）使用了联合索引却不符合最左匹配原则

2）索引中使用了运算或函数

比如 select * from user where age + 3 = 20，这样不仅会导致全表扫描，而且要计算 id 值再比较

3）like 或 or 的随意使用

4）随意的字段使用，比如索引字段为 varchar 查询时与 1 比较，这样发生了隐式转换，导致索引失效

5）全表扫描比索引快

6）表中两个不同字段进行比较

7）使用了 !=、<>，当 select * from user where name != 'cong'，当数据量过大时会导致索引失效

8）使用了 is not null 或者 order by，order by 后跟的不是主键或者不是覆盖索引会导致不走索引

34.索引不适合哪些场景？

• 数据表过小
• 频繁更新的列

35.为什么 InnoDB 要使用 B+树作为索引？

一篇文章讲透 MySQL 为什么要用 B+树实现索引

36.那一棵 B+树能存储多少条数据呢？

清幽之地：InnoDB 一棵 B+树可以存放多少行数据？

37.为什么要用 B+ 树，而不用普通二叉树？

为什么不用普通二叉树？

普通二叉树存在退化情况，会退化成链表，相当于全表扫描。平衡二叉树相较于普通二叉树更加稳定，查找速度也更快。

为什么不用平衡二叉树？

因为平衡二叉树一个节点只能存一个键值和数据，但如果是 B+ 树的话，就可以存储更多的节点数据，树的高度就会降低，磁盘 I/O 就会下降。

38.为什么用 B+ 树而不用 B 树呢？

1）更高的查询效率：B+ 树所有值都存在叶子节点，并且叶子节点之间通过指针连接，形成一个链表

2）更高的空间利用率：B+ 树的非叶子节点只存键值，使得非叶子节点有更多的键，从而有更多的分支。导致树的高度变低，磁盘 I/O 次数大大减少。

3）查询效率稳定：B+ 树所有叶子节点深度相同，所有数据查询路径长度相等，保证了每次搜索的稳定性。而在 B 树中，数据可以存储在内部节点，不同的查询可能需要不同深度的搜索。

39.B+ 树索引和 Hash 索引区别是什么？

• B+ 树索引可以进行范围查询，Hash 索引不能。
• B+ 树索引支持联合索引的最左侧原则，Hash 索引不支持。
• B+ 树索引支持 order by 排序，Hash 索引不支持。
• Hash 索引在等值查询上比 B+ 树索引效率更高。
• B+ 树索引可以进行模糊查询，Hash 索引不行。

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

前者数据和索引存在一起，后者数据和索引不存在一起。

举例来说：

• InnoDB 采用的是聚簇索引，如果没有显式定义主键，InnoDB 会选择一个唯一的非空列作为隐式的聚簇索引；如果这样的列也不存在，InnoDB 会自动生成一个隐藏的行 ID 作为聚簇索引。这意味着数据与主键是紧密绑定的，行数据直接存储在索引的叶子节点上。
• MyISAM 采用的是非聚簇索引，表数据存储在一个地方，而索引存储在另一个地方，索引指向数据行的物理位置。

聚簇索引的叶子节点存储键值和数据，非叶子节点存储键值

聚簇索引的优缺点

优点：

• 查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+ 树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
• 对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

• 依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引的优缺点

优点：

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点：

• 依赖于有序的数据:跟聚簇索引一样，非聚簇索引也依赖于有序的数据
• **可能会二次查询(回表)**：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

41.回表了解吗？

当使用非聚簇索引查找数据时，数据库会进行两步操作：

• 查找索引：数据库首先会查找非聚簇索引，找到索引键值对应的索引项。这个索引项包含了数据行在磁盘上的位置信息。
• 读取数据：然后，数据库会根据位置信息，去磁盘上读取相应的数据行。

这个过程也被称为“回表”，因为数据库需要先查找索引，然后再根据索引回到数据表中去查找实际的数据。

42.什么是覆盖索引？

MySQL 的覆盖索引指的是二级索引中包含了查询所需的字段，从而使查询可以仅通过访问二级索引而不需要访问主键索引

43.什么是最左前缀（最左匹配）原则？

最左匹配原则是指在使用联合索引时，查询条件从索引的最左列开始并且不跳过中间的列。

如果一个联合索引包含(c1, c2, c3)，那么它可以支持 c1、c1，c2 和 c1, c2, c3 的查询优化，但不会优化只有 c2 或 c3 的查询。

44.什么是索引下推优化？

索引下推优化（ICP）是一种数据库查询优化技术，旨在减少不必要的数据读取和过滤操作，从而提高查询性能。

通过将部分带有索引的条件交由存储引擎层进行过滤操作，从而减少 IO （本应给 server 层操作，交给存储引擎层操作叫做“下推”），索引下推应用在联合索引上。

当 MySQL 没有使用索引下推，步骤如下：
1)通过索引 age 找到多条主键ID(假设是 1、3、5、6)数据。
2)对每条数据，再通过主键 ID 回表查询完整的数据行[有四个主键 ID 需要回表 4 次]。
3)再将数据交由 Server层，将满足 score 的数据过滤出来得到最终数据行。
当 MySQL使用了索引下推，步骤如下：
1)通过索引 age 找到多条主键ID(假设是 1、3、5、6)数据。
2)因为联合索引内包含 score字段，可直接再存储引擎通过 score>= 80 来进行索引过滤，最终得到的主键 ID 分别是(1、3、6)。
3)再通过主键 ID 进行回表查询出来得到完整数据行[有三个主键 ID 需要回表3 次]。需要注意的是:
1)索引下推在 MySQL5.6及以后的版本支持，InnoD8和 MyISAM 这两个存储引擎都生效。
2)如果查询中引用了子查询索引下推可能不会生效，具体看 explain。
3)使用了函数或表达式索引下推也不能生效，这个和是否能命中索引的条件是一样的。
4)使用了聚簇索引(主键)查询，索引下推也不会生效，因为其是对于非聚簇索引来进行减少回表次数。

45.MySQL 中有哪几种锁，列举一下？

按锁的细粒度划分

• 开销：表锁 > 页锁 > 行锁
• 加锁速度：表锁 > 页锁 > 行锁
• 锁定力度：表锁 > 页锁 > 行锁
• 锁冲突概率：表锁 > 页锁 > 行锁
• 并发度：表锁 < 页锁 < 行锁
• 出现死锁概率：表锁 < 页锁 < 行锁

• 表锁：开销小，加锁快；锁定粒度大，锁冲突概率高，并发度最低；不会出现死锁。
• 行锁：开销大，加锁慢；锁定粒度小，锁冲突概率低，并发高；会出现死锁。
• 页锁：开销和加锁速度位于表锁和行锁之间；锁定粒度介于表锁和行锁之间；并发度一般；会出现死锁

按兼容性如何划分?

• 共享锁（S Lock）,也叫读锁（read lock），相互不阻塞。
• 排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据。

按锁机制划分？

1. 乐观锁
2. 悲观锁

悲观锁和乐观锁的区别？

乐观锁

乐观锁认为每次执行都不会发生冲突，仅需每次进行提交操作时利用标识的比对，确认其他操作没修改过便可提交。

标识通常是一个版本号字段，然后使用 CAS 操作进行比较。

悲观锁

悲观锁认为每次操作都会发生冲突，它会在任何发生冲突的地方加锁，其他操作想要修改都要等其执行完释放锁，再通过争抢锁再进行操作。

适合场景

1）乐观锁适合并发冲突少读多的场景，不用通过加锁只需根据版本号判断是否发生修改，无锁操作，吞吐量高。

-- 假设有一张用户表 users，包含 id、name 和 version 字段
-- 读取数据
SELECT id, name, version FROM users WHERE id = 1;

-- 更新数据时检查版本号
UPDATE users
SET name = 'new_name', version = version + 1
WHERE id = 1 AND version = current_version;

2）悲观适合并发冲突多，写多读少的场景，通过每次加锁的形式来保证数据的安全性，吞吐量较低。

-- 读取数据并加锁
SELECT id, name FROM users WHERE id = 1 FOR UPDATE;

-- 执行更新操作
UPDATE users SET name = 'new_name' WHERE id = 1;

46.说说 InnoDB 里的行锁实现?

1）Record Lock 记录锁

2）Gap Lock 间隙锁

3）Next-key Lock 临键锁

4）Insert Intention Lock 插入意向锁

47.什么是意向锁

意向锁是一个表级锁，不要和插入意向锁搞混。

当我们给一个表加表锁时，我们要判断表中是否有数据行被锁定，以保证加锁能够成功。
假如没有意向锁，我们需要遍历表中所有数据行判断有无行锁。
假如有了意向锁，我们直接获取表的意向锁，如果能获取到就可以直接加行锁，如果不能就不能加行锁。

48.MySQL 遇到过死锁问题吗，你是如何解决的？

MySQL InnoDB 有一个锁监测机制，当发现生死锁时会回退事务从而释放锁，也有锁等待超时参数，当超过这个阈值就会释放锁进行回滚

------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;
*** WE ROLL BACK TRANSACTION (1)

1)避免大事务。大事务占据锁的时间长，将大事务拆分成多个小事务快速释放锁，可降低死锁产生的概率和避免冲突。

2)调整申请锁的顺序。在更新数据的时候要保证获得足够的锁，举个例子:先获取影响范围大的锁，比如说修改操作，先将排他锁获取到，再获取共享锁。或固定顺序访问数据，这样也能避免死锁的情况。

3)更改数据库隔离级别。可重复读比读已提交多了间隙锁和临键锁，利用读已提交替换之可降低死锁的情况。

4)合理建立索引，减少加锁范围。如果命中索引，则会锁对应的行，不然就是全表行都加锁，这样冲突大，死锁的概率就高了。

5)开启死锁检测，适当调整锁等待时长。

49.MySQL 事务的四大特性说一下？

ACID

• 原子性：事务的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务中的操作不能只执行其中一部分。
• 一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致性与业务规则有关，比如银行转账，不论事务成功还是失败，转账双方的总金额应该是不变的。
• 隔离性：多个并发事务之间需要相互隔离，即一个事务的执行不能被其他事务干扰。
• 持久性：一旦事务提交，则其所做的修改将永久保存到数据库中。即使发生系统崩溃，修改的数据也不会丢失。

50.MySQL 是如何保证 ACID 的？

MySQL 通过事务管理和持久性存储机制来确保 ACID

1）原子性：如果事务的所有的操作都成功，则事务被提交；如果事务的某个操作失败了，那么事务就会回滚。

undo log 是 InnoDB 存储引擎来确保事务原子性的关键机制，undo log 记录了事务发生前的数据，如果事务失败，InnoDB 会根据 undo log 回滚数据

2）一致性：事务执行前后数据都是一致的，即使事务失败，也不应该使数据处于一个不一致的状态。

一致性是 ACID 的目的，只有保证了 AID 才能保证 C

3）隔离性：MySQL 使用多种隔离级别来控制事务如何与其他并发事务隔离。InnoDB 使用 MVCC 机制处理并发事务，确保每个事务都由自己的数据版本。

在 MVCC 中，每次更新记录时，都会生成一个新版本。每个版本都会有两个额外的属性：一个是版本的创建时间/事务 ID，一个是版本的过期时间/下个版本的事务 ID。

MVCC 通过提供数据版本来支持事务的隔离性。不同的事务会看到不同版本的数据行，这取决于事务的开始时间和隔离级别。

4）持久性：由 MySQL 的存储引擎通过写入磁盘来确保。即使系统崩溃了，已提交事务的修改也不会丢失。

redo log 是一种物理日志，记录了对数据页的物理修改。当事务进行写操作时，InnoDB 首先会写入 redo log，并不会立即修改数据文件。当系统崩溃时，由于数据被写入 redo log 中，系统在启动时使用这些日志“重做”这些修改，以确保数据的持久性。

MySQL 是如何实现事务的？

主要是通过:锁、Redo Log、Undo Log、MVCC。
MySQL 利用锁(行锁、间隙锁等等)机制，使用数据并发修改的控制，满足事务的隔离性。
Redo Log(重做日志)，它会记录事务对数据库的所有修改，在崩溃时恢复未提交的更改，用来满足事务的持久性。Undo Log(回滚日志)，它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后可以恢复之前的样子。实现原子性和隔离性
MVCC(多版本并发控制)，满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离性。
看到这有小伙伴问一致性呢?
其实事务主要是为了实现一致性，具体是通过 AID，即原子性、隔离性和持久性来达到一致性的目的。

51.事务的隔离级别有哪些？MySQL 的默认隔离级别是什么？

什么是读未提交？

最低的隔离级别。当前事务可以读取其他事务未提交的数据，但会出现“脏读”、“不可重复读”和“幻读”的问题。

什么是读已提交？

当前事务只能读取其他事务已提交的数据，可以避免“脏读”现象。但**”不可重复读”和”幻读”问题**仍然存在。

为什么读已提交会导致数据的不一致？

1. 用户A 查询票数，发现剩余 10张票。
2. 一段时间后，用户A再次查询，票数变为 8张。
3. 用户A试图购买 8张票。
4. 但在提交事务前，另一个用户B 已购买了1张票，导致此时剩余票数变为 7张。
5. 用户A 扣票时，发现票数是 7张，但用户A打算购买8张票，因此会产生错误或冲突。

什么是可重复读？

确保在同一事务中多次读取相同记录的结果是一致的，即使其他事务对这条记录进行了修改，也不会影响到当前事务。

是 MySQL 默认的隔离级别，避免了“脏读”和“不可重复读”，也在很大程度上减少了“幻读”问题。

什么是串行化？

最高的隔离级别，通过强制事务串行执行来避免并发问题，可以解决“脏读”、“不可重复读”和“幻读”问题。

但会导致大量的超时和锁竞争问题。

为什么 MySQL 选择可重复读作为默认隔离级别

1. 如果使用读已提交、读未提交等隔离级别，使用了 statement 格式的 binlog 会导致主从数据库格式不一致。

52.什么是幻读，脏读，不可重复读呢？

• 事务 A、B 交替执行，事务 A 读取到事务 B 未提交的数据，这就是脏读。
• 在一个事务范围内，两次相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。
• 事务 A 查询一个范围的结果集，另一个并发事务 B 往这个范围中插入 / 删除了数据，并静悄悄地提交，然后事务 A 再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

不同的隔离级别，在并发事务下可能会发生的问题：

隔离级别	脏读	不可重复读	幻读
Read Uncommited 读取未提交	是	是	是
Read Commited 读取已提交	否	是	是
Repeatable Read 可重复读	否	否	是
Serialzable 可串行化	否	否	否

53.事务的各个隔离级别都是如何实现的？

读未提交是如何实现的？

不提供任何锁机制来保护读取的数据，允许读取未提交的数据（即脏读）。

读已提交&可重复读是如何实现的？

读已提交和可重复读通过 MVCC 机制中的 ReadView 来实现。

• READ COMMITTED：每次读取数据前都生成一个 ReadView，保证每次读操作都是最新的数据。
• REPEATABLE READ：只在第一次读操作时生成一个 ReadView，后续读操作都使用这个 ReadView，保证事务内读取的数据是一致的。

54.MySQL MVCC 是如何实现的？

MySQL 中的多版本并发控制（MVCC）是通过在数据库引擎层实现的。它的核心思想是在读取操作和写入操作之间提供一种机制，使得读操作可以读取到一致性的数据视图，而不受正在进行的写操作的影响。

MVCC 的实现主要涉及以下两个方面：

1. 版本号或时间戳：MySQL 使用版本号或时间戳来标识数据的版本。每当进行一次数据修改时，都会为修改的数据行创建一个新的版本，并将新版本的数据写入数据库。同时，每个事务都有自己的事务 ID 或时间戳。
2. Read View（读取视图）：在事务开始时，MySQL 会为当前事务创建一个读取视图。读取视图决定了事务能够看到哪些数据版本。该视图保证了事务能够读取到在事务开始之前已经提交的数据版本，但不会看到在事务开始之后提交的数据版本。

具体来说，当一个事务开始时，MySQL 会将当前系统的最新数据版本作为事务的读取视图。在事务执行期间，该读取视图将保持不变。这意味着事务可以读取到在事务开始之前已经提交的数据版本，而不受其他并发事务修改的影响。如果某行数据的版本是在事务开始之后才生成的（即事务开始时尚未提交的数据修改），则该数据版本对当前事务是不可见的。

55.数据库读写分离了解吗？

读写分离的基本原理是将写操作和读操作分散到数据库的不同节点：

读写分离的基本实现：

• 数据库服务器搭建主从集群，一主一从、一主多从都可以。
• 主机负责写操作，从机负责读操作
• 主机通过复制将数据同步到从机，保证每个服务器都存储了所有的业务数据
• 业务服务器将写操作给主机，读操作给从机

56.主从复制原理？

• 在主服务器上，所有更新数据的语句会被记录到二进制日志中。
• 主服务器上的一个线程（二进制日志转储线程）负责读取二进制日志的内容并发送给从服务器。
• 从服务器接收到二进制日志数据后，会将这些数据写入自己的中继日志（Relay Log），中继日志是从服务器上的一个本地存储。
• 从服务器上有一个 SQL 线程会读取中继日志并将数据写入从数据库中。

57.主从同步延迟怎么处理？

主从同步延迟的原因

一个服务器开放 n 个连接给客户端，这样会有大并发的更新操作，但是从服务器读取 binlog 的线程仅有一个，当某个 SQL 在从机执行时间过长导致主机 SQL 大量积压不能同步至从机。导致了主从不一致，也叫主从延迟

主从同步延迟的解决办法

1. 写操作后的读操作指定发给数据库主服务器
2. 读从机失败后再读一次主机
3. 关键业务读写操作全部指向主机，非关键业务采用读写分离

58.你们一般是怎么分库的呢？

①、垂直分库：按照业务模块将不同的表拆分到不同的库中，例如，用户表、订单表、商品表等分到不同的库中。

②、水平分库：按照一定的策略将一个表中的数据拆分到多个库中，例如，按照用户 id 的 hash 值将用户表拆分到不同的库中。

59.水平分表有几种路由方式？

• 范围路由：选取有序的数据列 （例如，整形、时间戳等） 作为路由的条件，不同分段分散到不同的数据库表中。

  

• Hash 路由：选取某个列 （或者某几个列组合也可以） 的值进行 Hash 运算，然后根据 Hash 结果分散到不同的数据库表中。

  

• 配置路由：配置路由就是路由表，用一张独立的表来记录路由信息。同样以订单 id 为例，我们新增一张 order_router 表，这个表包含 orderId和 tableId 两列 , 根据 orderId就可以查询对应的 table_id。

  

60.常用的分库分表中间件有哪些？

• sharding-jdbc
• Mycat

61.那你觉得分库分表会带来什么问题呢？

• 事务问题
• 跨库 join
• 跨节点的 order by、group by、count
• ID 问题
• 数据迁移、容量规划、扩容的等问题

• 事务的问题

使用关系型数据库，有很大一点在于它保证事务完整性。

而分库之后单机事务就用不上了，必须使用分布式事务来解决。

• 跨库 JOIN 问题

在一个库中的时候我们还可以利用 JOIN 来连表查询，而跨库了之后就无法使用 JOIN 了。

此时的解决方案就是在业务代码中进行关联，也就是先把一个表的数据查出来，然后通过得到的结果再去查另一张表，然后利用代码来关联得到最终的结果。

这种方式实现起来稍微比较复杂，不过也是可以接受的。

还有可以适当的冗余一些字段。比如以前的表就存储一个关联 ID，但是业务时常要求返回对应的 Name 或者其他字段。这时候就可以把这些字段冗余到当前表中，来去除需要关联的操作。

还有一种方式就是数据异构，通过 binlog 同步等方式，把需要跨库 join 的数据异构到 ES 等存储结构中，通过 ES 进行查询。

解决办法：

1. 业务代码关联
2. 添加冗余字段
3. 数据异构

从分表的角度来看：

• 跨节点的 count,order by,group by 以及聚合函数问题

只能由业务代码来实现或者用中间件将各表中的数据汇总、排序、分页然后返回。

• 数据迁移，容量规划，扩容等问题

数据的迁移，容量如何规划，未来是否可能再次需要扩容，等等，都是需要考虑的问题。

• ID 问题

数据库表被切分后，不能再依赖数据库自身的主键生成机制，所以需要一些手段来保证全局主键唯一。

1. 还是自增，只不过自增步长设置一下。比如现在有三张表，步长设置为 3，三张表 ID 初始值分别是 1、2、3。这样第一张表的 ID 增长是 1、4、7。第二张表是 2、5、8。第三张表是 3、6、9，这样就不会重复了。
2. UUID，这种最简单，但是不连续的主键插入会导致严重的页分裂，性能比较差。
3. 分布式 ID，比较出名的就是 Twitter 开源的 sonwflake 雪花算法

62.MySQL 中索引一定生效吗，如何排查索引效果？

可以通过 explain 执行计划判断索引是否生效，主要观察以下几点：

1. type（访问类型）：这个属性表示查询使用的访问方法，例如 ALL，Index，range 等。当值为 index 或 range，表示查询用了索引，如果是 ALL 表示是全表扫描
2. key（使用的索引）：这个属性表示查询使用的索引，如果查询使用了索引，则会显示索引名称，如果值为 NULL，表示未使用索引
3. rows（扫描的行数）：这个属性表示查询扫描的行数，即查询返回的行数，需要评估扫描量

63.MySQL 的 B+ 树中查询数据的全过程

数据从根节点起，根据键值大小确定是左子树还是右子树，从上到下最终定位到叶子节点。定位叶子节点后，因为一片叶子有 16k 大小，所以可以存多条记录，叶子节点的实际构造如图所示：

从上图可以知晓，叶子节点有页目录结构，它其实就是一个索引，通过它可以快速找到记录。
页目录分为了多个槽，每个槽都指向对应一个分组内的最大记录，每个分组内都会包含若干条记录。
通过二分查询，利用槽就能直接定位到记录所在的组，从而就能获取到对应的记录。
举个例子，现在有5个槽，如果想查找主键为3的记录，此时的流程是:
1)通过二分得到槽的中间位置，(0+4)/2=2:
2)通过槽定位到第二个分组中的主键为4的记录，4大于 3，因此得知主键3的记录在这个分组中。由于记录是通过单向链表串起来的，因此需要从槽1找到主键2的记录，再往下遍历定位到主键3的记录。
以上就是利用二分查询的定位流程。通过槽可找到对应记录所在的组，或能直接定位到记录，或还需通过链表遍历找到对应的数据。
实际上，每个分组的记录数是有规定的，图中做了省略只画了两条，InnoDB规定:
第一个分组只有一条记录
中间的分组 4-8 条记录
·最后一个分组 1-8 条记录
因此不必担心遍历很长的链表导致性能问题。
这题的重点是先简单提下从根节点遍历到子节点的过程，然后提到叶子节点默认大小为 16KB，所以理论上能存储很多记录，从而引出页目录，再通过二分查找才能对应记录。

64.MySQL 中长事务可能会导致哪些问题？

长事务涉及到加锁，只有在长事务执行完才会释放锁，由于长事务锁了很多数据，如果期间有频繁的 DML 想要操作这些数据就会导致阻塞。连接都阻塞住了，业务线程自然阻塞了，也就是服务线程都在等待数据库响应，从而影响别的服务，甚至产生雪崩。

如何处理长事务？

答案就是将长事务 SQL 拆分多个 SQL，如果没有索引我们可以根据主键值来创造索引。

delete from yes where (id >=233333333 and id<233433333) and create_date > "2020-12-31" and create _date <"2022-01-01";
delete from yes where (id >= 233433333 and id <233533333) and create_date >"2020-12-31" and create_ date <"2022-01-01”;
delete from yes where (id >= 666566666 and id <=666666666) and create date >"2020-12-31" and create date<"2022-01-01";

当然还可以再精确些，通过日期筛选来得到 maxld，这影响不大(不满足条件的 SQL执行很快，不会耗费很多时间)。这样一来，SQL 就满足了分批的操作，且用得上索引。如果哪条语句执行出错，只会回滚小部分数据，我们重新排查下就好了，影响不大。而且拆分 SQL之后还可以并行提高执行效率。当然并行可能有锁竞争的情况，导致个别语句等待超时。不过影响不大，只要机器状态好，执行的快，因为锁竞争导致的等待并不一定会超时，如果个别 SQL超时的话，重新执行就好了。

扩展：删除思路的转换

65.如果 MySQL 中没有 MVCC，会有什么影响

如果没有 MVCC，读写操作就会发生冲突。

1. 如果有一个事务 1 正在执行，此时事务 2 修改了记录 A，但未提交事务。如果事务 1 要读取记录 A，但事务 2 还未提交，所以事务 1 无法读取最新记录 A，如果读取了假如事务 2 回滚就产生脏读了。
2. 如果事务 1 读的旧数据，但数据被事务 2 修改了，那事务 1 读的就是假数据。

如果没有 MVCC，事务 1 只能用锁阻塞等待事务 2 的提交，这种实现叫基于锁的并发控制。如果有了 MVCC，事务 2 修改的记录 A 还未提交，但是记录 A 被修改前的版本还在，此时事务 1 就可以读取之前的版本数据。这样读写就不会阻塞还提高了数据库的性能。

66.MySQL 锁类型有哪些

共享锁和排他锁

在 MySQL 中锁可以分为两大类，分别是 shared (S) locks 和 exclusive (X) locks。

• S锁，称为共享锁，事务在读取记录的时候获取 S 锁，它允许多个事务同时获取 S 锁，互相之间不会冲突。
• X锁，称为独占锁（排他锁），事务在修改记录的时候获取 X 锁，且只允许一个事务获取 X 锁，其它事务需要阻塞等待。

X 锁和 S 锁冲突，S 锁之间冲突，X 锁之间不冲突

SELECT ... LOCK IN SHARE MODE; 这种读取需要对记录上 S 锁。

SELECT ... FOR UPDATE; 需要对记录上 X 锁。

元数据锁

元数据锁也分为读锁和写锁

1）读锁

当一个事务需要读取表的元数据时（如执行 SELECT 操作），会获取读锁。 多个事务可以同时持有读锁，不会互相阻塞。

2）写锁

当一个事务需要修改表的元数据时（如执行 ALTER TABLE 操作），会获取写锁。 写锁会阻塞其他任何读锁和写锁，确保独占访问。

元数据锁的作用

1）防止并发的 DDL 和 DML 操作冲突

• 当一个事务对表进行结构性更改（如 ALTER TABLE），元数据锁（写锁）会阻止其他事务对该表进行操作，直到结构更改完成。
• 当一个事务对表进行数据操作（如 SELECT、INSERT、UPDATE、DELETE），元数据锁（读锁）会阻止其他事务对该表进行结构性更改。

2）保护元数据一致性：

• 确保在执行 DDL 操作（如 CREATE TABLE、DROP TABLE、ALTER TABLE）时，元数据不会被其他事务同时修改。

意向锁

• 共享意向锁
• 独占意向锁

这两个锁都是表级锁，当要对表中某条记录加 S 锁时，先在表上加 IS 锁，表明此时表内有 S 锁。当需要对表中某条记录加 X 锁时，先在表上加个 IX 锁，表明表内有 X 锁。这样操作后，无需遍历表中所有数据查看是否加了锁，直接看表上有无 IX 锁和 IS 锁即可。

比如，此时要上表级别的 S 锁，如果表上没有 IX ，说明表中没有记录有独占锁，其实就可以直接上表级 S 锁。

如果此时要上表级别的 X 锁，如果表上没有 IX 和 IS ，说明表中的所有记录都没加锁，其实就可以直接上表级 X 锁。

记录锁

记录锁顾名思义就是锁住当前的记录，他是作用在索引上的。InnoDB 是由索引的，即使灭没有主键也会创建隐藏的聚簇索引，所以记录锁总是锁定索引记录。

比如，此时一个事务A执行 SELECT* FROM yes WHERE name ='xx'FOR UPDATE;那么 name=xx 这条记录就被锁定了，其他事务无法插入、删除、修改 name=xx 的记录。此时事务A还未提交，另一个事务B要执行 insert into yes(name)values(‘xx’)，此时会被阻塞，这个很好理解。

但是，如果另一个事务C执行了 insert into yes(name)values('aa')，这个语句会被阻塞吗?

如果 name 没索引，就都锁了，因为要根据聚簇索引找对应的记录，但是自己的 name 是不知道的，索引就都锁了。

因此如果有索引，事务 C 就会被阻塞，反之就不会。

间隙锁和临键锁

记录锁是加在记录上的，如果要给不存在记录加锁怎么办？也就是要防止幻读的出现！此时间隙锁就派上用场了。

比如此时有 1、3、5、10 这 4 条记录，数据页中还有两条虚拟记录，分别是 Infimum 和 Supremum。

可以看到记录间都有间隙，而间隙锁锁的就是间隙。

比如我把 3 和 5 之间的间隙锁了，此时要插入 id = 4 的记录，就会被这个间隙锁给阻塞了，这样就避免了幻读的产生！也就实现了锁定未插入的记录的需求！

临键锁就是记录锁 + 间隙锁，间隙锁只能锁定（3, 5）这个区间，而临键锁是一个前开后闭的区间（3, 5]，这样能防止查询 id = 5 的这个幻读。

插入意向锁

插入意向锁也是一类间隙锁，但它不锁间隙而是等待某个间隙。比如上后面举例的插入 id = 4 的事务 C，由于被间隙锁阻塞了，所以事务 C 会生成一个插入意向锁，表明等待这个间隙锁的释放。插入意向锁之间不会阻塞，因为它的目的仅仅是为了等待间隙锁的释放。

如果某个事务没有抢到资源，那也会生成一个锁对象，只是状态是等待的，而当拥有资源的事务释放锁之后，就会寻找正在等待当前资源的锁结构，然后选一个让它获得资源并唤醒对应的事务使之得以执行。

67、MySQL 如何实现读写分离

1）读写分离就是将读写操作就一台机器上剥离开来，将主库压力分担一些到从库。

2）读写分离还有个操作就是主库不建查询索引，从库建查询的索引。因为主库主要负责写和修改操作，建索引的话要不断维护索引，不仅要在聚簇索引上插入，对应的二级的二级索引也得插入，浪费时间。

如何实现？

1、代码封装

在代码层面抽出一个中间层，由中间层实现读写分离和数据库连接。

就是搞一个代理类，对外暴露读写接口，里面封装逻辑，将读操作指向从数据库，写操作指向主数据库。

优点：简单，根据业务定制化变化，随心所欲。

缺点：如果数据库宕机，主从发生变化需要修改代码，对于多语言项目需要给每个语言写一个中间层。

2、使用中间件

由中间件作为客户端和主数据库的连接桥梁，通过 SQL 协议交互，写操作指向主库，读操作指向从库。

68、从 MySQL 获取数据，是从磁盘读取的吗？

MySQL 数据可以从缓存读，也可以从磁盘读

优先读取缓存中的数据

1）查询缓存（MySQL 8.0 已废除）：在 MySQL 8.0 之前，MySQL 提供了查询缓存功能，用于缓存查询结果。如果相同的查询（同一个查询 SQL）再次执行，并且表没有发生任何变化（这个条件比较苛刻，所以后续废除了），则 MySQL 可以直接从查询缓存中返回结果，而无需重新执行查询。具体的实现类似用一个 map 存储了之前的结果，key 是 SQL，value 为结果，SQL 执行的时候，先去这个 map 看看通过 key 是否能找到值，如果找到则直接返回。

2）InnoDB 缓冲池（buffer pool）：这是 InnoDB 存储引擎的核心缓存组件。缓冲池缓存了数据页、索引页和其他相关信息。查询数据时，MySQL 首先在缓冲池中查找，如果找到则直接返回数据，否则从磁盘读取数据页并将其缓存到缓冲池中。

查询缓存和 buffer pool 大致结构关系如下：

拓展下数据页和索引页

InnoDB 存储引擎将表数据和索引以页单位存储，每页通常为 16k。当需要读取某条记录时，MySQL 会加载包含该记录的整个数据页到缓冲池中，从而减少频繁的 I/O 操作。（MySQL 是以页为单位加载数据的，而不是记录行）

其次从磁盘获取

当数据不在内存缓存中时，MySQL 需要从磁盘读取数据。注意是以页为单位从磁盘获取数据，这里还有个额外的知识点，因为以页为单位，使得顺序遍历数据的速度更快，因为后面的数据已经被加载到缓存中了！ 这也符合空间局部性。

拓展 buffer pool 知识

其实 buffer pool 就是内存中的一块缓冲池，我们都知道 mysql 的数据最终是存储在磁盘上的，但是如果读存数据都直接跟磁盘打交道的话，这速度就有点慢了。所以 innodb 自己维护了一个 buffer pool，在读取数据的时候，会把数据加载到缓冲池中，这样下次再获取就不需要从磁盘读了，直接访问内存中的 buffer pool 即可。包括修改也是一样，直接修改内存中的数据，然后到一定时机才会将这些脏数据刷到磁盘上。看到这肯定有小伙伴有疑惑：直接就在内存中修改数据，假设服务器突然宕机了，这个修改不就丢了？别怕，有个 redolog 的存在，它会持久化这些修改，恢复时可以读取 redolog 来还原数据，这个我们后面的面试题再详盘，今天的主角是 buffer pool 哈。回到 buffer pool，其实缓冲池维护的是页数据，也就是说，即使你只想从磁盘中获取一条数据，但是 innodb 也会加载一页的数据到缓冲池中，一页默认是 16k。当然，缓冲池的大小是有限的。按照 mysql 官网所说，在专用服务器上，通常会分配给缓冲池高达 80% 的物理内存，不管分配多少，反正内存大小正常来说肯定不会比磁盘大。也就是说内存放不下全部的数据库数据，那说明缓冲池需要有淘汰机制，淘汰那些不常被访问的数据页。按照这个需求，我们很容易想到 LRU 机制，最近最少使用的页面将被淘汰，即维护一个链表，被访问的页面移动到头部，新加的页面也加到头部，同时根据内存使用情况淘汰尾部的页面。通过这样一个机制来维持内存且尽量让最近访问的数据留在内存中。看起来这个想法不错，但 innodb 的实现并不是朴素的 LRU，而是一种变型的 LRU。

从图中我们可以看出 buffer pool 分为了老年代（old sublist）和新生代（new sublist）。老年代默认占 3/8，当然，可以通过 innodb_old_blocks_pct 参数来调整比例。当有新页面加入 buffer pool 时，插入的位置是老年代的头部，同时新页面在 1s 内再次被访问的话，不会移到新生代，等 1s 后，如果该页面再次被访问才会被移动到新生代这和我们正常了解的 LRU 不太一样，正常了解的 LRU 实现是新页面插入到头部，且老页面只要被访问到就会被移动到头部，这样保证最近访问的数据都留存在头部，淘汰的只会是尾部的数据。那为什么要实现这样改造的 LRU 呢？innodb 有预读机制，简单理解就是读取连续的多个页面后，innodb 认为后面的数据也会被读取，于是异步将这些数据载入 buffer pool 中，但是这只是一个预判，也就是说预读的页面不一定会被访问。所以如果直接将新页面都加到新生代，可能会污染热点数据，但是如果新页面是加到老年代头部，就没有这个问题。同时大量数据的访问，例如不带 where 条件的 select 或者 mysqldump 的操作等，都会导致同等数量的数据页被淘汰，如果简单加到新生代的话，可能会一次性把大量热点数据淘汰了，所以新页面加到老年代头部就没这个问题。那 1s 机制是为了什么呢？这个机制其实也是为了处理大量数据访问的情况，因为基本上大数据扫描之后，可能立马又再次访问，正常这时候需要把页移到新生代了，但等这波操作结束了，后面还有可能再也没有请求访问这些页面了，但因为这波扫描把热点数据都淘汰了，这就不太美丽了。于是乎搞了个时间窗口，新页面在 1s 内的访问，并不会将其移到新生代，这样就不会淘汰热点数据了，然后 1s 后如果这个页面再次被访问，才会被移到新生代，这次访问大概率已经是别的业务请求，也说明这个数据确实可能是热点数据。经过这两个改造， innodb 就解决了预读失效和一次性大批量数据访问的问题。至此，对 buffer pool 的了解就差不多了。

那 1s 机制是为了什么呢？

这个机制其实也是为了处理大量数据访问的情况，因为基本上大数据扫描之后，可能立马又再次访问，正常这时候需要把页移到新生代了，但等这波操作结束了，后面还有可能再也没有请求访问这些页面了，但因为这波扫描把热点数据都淘汰了，这就不太美丽了。

于是乎搞了个时间窗口，新页面在 1s 内的访问，并不会将其移到新生代，这样就不会淘汰热点数据了，然后 1s 后如果这个页面再次被访问，才会被移到新生代，这次访问大概率已经是别的业务请求，也说明这个数据确实可能是热点数据。

经过这两个改造， innodb 就解决了预读失效和一次性大批量数据访问的问题。

69、MySQL 的 Doublewrite Buffer 是什么？它有啥用？

InnoDB 一页是 16k，Linux 内存页是 4k，因此一个 InnoDB 页对应 4 个内存页。假如写入 InnoDB 页时发生了断电，导致只写了一个内存页从而导致部分内存页写入，而为了避免这种情况的发生就引入了 double write。当写入 InnoDB 页时，会将数据拷贝到 double write buffer 中，然后刷盘到 double write files，最后将数据刷盘到本该的文件上。

InnoDB 默认一页是 16k，而 Linux 内存页是 4k，那么一个 InnoDB 页对应 4 个内存页

所以 innodb 的一页数据要刷盘等于需要写四个系统页，假设 innodb一页数据落盘的时候，只写了一个系统页就断电了，那 InnoDB 一页数据就坏了，这就完了，不好恢复了。

即产生了部分页面写问题，因为写 innodb 的一页无法保证原子性，所以引入了 Doublewrite Buffer。

其实就是当 innodb 要将数据落盘的时候，先将页数据拷贝到 Doublewrite Buffer 中，然后 Doublewrite Buffer 再刷盘到 Doublewrite Buffer Files，这时候等于数据已经落盘备份了。然后再将数据页刷盘到本该到文件上。

从这个步骤我们得知，数据是写了两次磁盘，所以这东西叫 double write。

之所以这样操作就是先找个地方暂存这次刷盘的完整数据，如果出现断电这种情况导致的部分页面写而损坏原先的完整页，可以从 Doublewrite Buffer Files 恢复数据。

但虽然是两次写，性能的话也不会低太多，因此数据拷贝到 Doublewrite Buffer 是内存拷贝操作，然后写到 Doublewrite Buffer Files 也是批量写，且是顺序写盘，所以对整体而已，性能损失不会太多。

有了这个 buffer，在崩溃恢复的时候，如果发现页损坏，就可以从 Doublewrite Buffer Files 里面找到页副本，然后恢复即可。

Doublewrite Buffer 工作原理简述：

1）写入 Doublewrite Buffer

当 InnoDB 需要将脏页（dirty page，即已被修改但尚未写入磁盘的页）写入磁盘时，首先将这些数据页写入到 Doublewrite Buffer 中。

2）写入实际数据文件：

将数据页写入 Doublewrite Buffer 和落盘后，InnoDB 将这些数据页从 Doublewrite Buffer 写入到实际的数据文件中（如 .ibd 文件）。

3）如果发生部分的写的情况：

数据页写入一半断电了，在崩溃恢复的时候，InnoDB 会检查 Doublewrite Buffer 中的数据页。如果在系统崩溃前数据页已经成功写入 Doublewrite Buffer，那么这些数据页是完整和一致的。

从 Doublewrite Buffer 中将完整的页重新写入实际数据文件，修复受影响的页。

Doublewrite Buffer 通常在系统表空间文件（ibdata1）中，分为两个 1MB 的区域，共 2MB，每个区域可存储 64 个 16KB 的页。

70、MySQL 中 Log Buffer 是什么？它有啥用？

我们看看 Log Buffer。从上面的图我们可以得知，它是 redo log 做缓冲用的。
redo log 我们都知道是重做日志，用来保证崩溃恢复数据的正确性，innodb 写数据时是先写日志，再写磁盘数据。即 WAL (Write-Ahead Logging)，把数据的随机写入转换成日志的顺序写。
但，即使是顺序写 log ，每次都调用 write 或者 fsync 也是有开销的，毕竟也是系统调用，涉及上下文切换。
于是乎，搞了个 Log Buffer 来缓存 redo log 的写入。
即写 redo log 先写到 Log Buffer 中，等一定时机再写到 redo log 文件里。
我们每次事务可能涉及多个更改，这样就会产生多条redo log，这时会都先写到 Log Bufer 中，等事务提交的时候,-起将这些 redo log 写到文件里。
或者当 Log Buffer 超过总量的一半(默认总量是 16mb)，也会将数据刷到 redo log 文件中。
也有个后台线程，每隔 1s 就会将 Log Buffer 刷到 redo log 文件中。
从上面这些我们得知，Log Buffer 其实就是一个写优化操作，把多次 write 优化成一次 write，一次处理多数据，减少系统调用。

说白了，就是每次写数据都是先写日志，但日志不是立即写入磁盘的而是写如缓冲区里，这样是为了减少 IO 次数。

看到这肯定有小伙伴说，数据先写 Log Buffer 而不刷盘，这数据不会丢吗?
innodb 其实给了个配置，即 innodb_flush log at_trx_commit 来控制 redo log 写盘时机。

• 当值为 0，提交事务不会刷盘到 redo log，需要等每隔一秒的后台线程，将 log buffer 写到操作系统的 cache，并调用 fsync落盘，性能最好，但是可能会丢1s数据。
• 当值为 1，提交事务会将 log buffer 写到操作系统的 cache，并调用 fsync 落盘，保证数据正确，性能最差，这也是默认配置。
• 当值为 2，提交事务会将 log buffer 写到操作系统的 cache，但不调用 fsync，而是等每隔 1s 调用 fsync 落盘，性能折中，如果数据库挂了没事，如果服务器宕机了，会丢1s数据。
  具体如何配置看你的业务了。至此，想必你应该了解 Log Buffer 是干啥用了的吧。

71.为什么不推荐多表 JOIN?

因为 join 多表时（3 个表以上）会对联接的每个表进行扫描、匹配和结合，消耗了大量 CPU 和内存资源。

拓展

select * from mianshiyal straight join mianshiya2 on(mianshiya1.a=mianshiya2.a);。其中mianshiya1 是驱动表，mianshiya2 就是被驱动表，因此 mianshiya1 会被全表扫描，然而再根据其中的 a 字段作为查询条件去 mainshiya2进行搜索，所以要求 a 字段要有索引。

为什么要小表作为驱动表呢？

72.MySQL 中如何解决深度分页问题

limit 900000, 90 这种就属于深度分页

优化的方式有三种：

1）子查询：比如 select * from mianshiya where name =yupi' limit 99999990, 10;，可以优化为

select * from mianshiya where 
name = 'yupi' 
and id > 
(select id from mianshiya where name = 'yupi' order by id limit 99999990，1)
order by id limit 10;

name 有索引的情况下，这样的查询直接扫描 name 的二级索引，二级索引的数据量少，且在子查询中能直接得到 id不需要回表。将子查询得到的id 再去主键索引查询，速度很快，数据量也小。
如果直接扫描主键索引的话，数据量就比较大，因为主键索引包含全部的数据。

或者下面这样也行：

select * from mianshiya 
inner join
(select id from mianshiya where name = 'yupi' order by id limit 99999990，10)
as mianshiya1 on mianshiya.id = mianshiya1.id

2）记录 id

每次分页都返回当前的最大id ，然后下次查询的时候，带上这个id，就可以利用 id > maxid 过滤了。
这种查询仅适合连续查询的情况，如果跳页的话就不生效了。

3）ES

可以用 ES 解决这个问题，但 ES 也有深度分页的问题