【面试】MySQL

1、数据库三范式

1. 表中的每一列都是不可再分的原子值，每一行都是唯一的。

不符合

学生ID | 课程
------ | --------
1      | 数学, 物理

符合

学生ID | 课程
------ | --------
1      | 数学
1      | 物理

2. 在满足1NF的基础上，消除部分函数依赖，确保非主键字段完全依赖于主键。

不符合

学生ID | 课程ID | 课程名称 | 成绩
------ | ------- | -------- | -----
1      | 101     | 数学     | 90

符合

学生ID | 课程ID | 成绩
------ | ------- | -----
1      | 101     | 90

课程ID | 课程名称
------- | --------
101     | 数学

3. 在满足2NF的基础上，消除传递依赖，确保非主键字段之间没有依赖关系。

不符合

学生ID | 学生姓名 | 学院ID | 学院名称
------ | -------- | ------ | --------
1      | 张三     | 10     | 计算机

符合

学生ID | 学生姓名 | 学院ID
------ | -------- | ------
1      | 张三     | 10

学院ID | 学院名称
------ | --------
10     | 计算机

2、什么是关系型数据库，什么是非关系型数据库

1. 关系型数据库是基于关系模型（表结构）的数据库，数据以行和列的形式存储在表中，表与表之间通过关系（如主键和外键）连接。
2. 非关系型数据库是一种不基于关系模型的数据库，数据存储方式灵活，可以是键值对、文档、列族或图结构。

3、什么是数据库存储引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足
企业内大多数需要处理大量数据的应用程序的要求。

查看看某个表用了什么引擎：show create table 表名，在显示结果里参数engine后面的就表示该表当前用的存储
引擎。

MySQL的存储引擎是基于表的还是基于数据库的

表

MySQL的数据存储一定是基于硬盘的吗

不是的，MySQL也可以基于内存的，即MySQL的内存表技术。它允许将数据和索引存储在内存中，从而提高了检
索速度和修改数据的效率。优点包括具有快速响应的查询性能和节约硬盘存储空间。此外，使用内存表还可以实现
更高的复杂性，从而提高了MySQL的整体性能。

创建内存表与创建普通表一样，使用CREATE TABLE语句，但需要将存储擎设置为：ENGINE=MEMORY

InnoDB 与 MyISAM 的区别

MySQL 5.5以后的版本开始将InnoDB作为默认的存储引擎，之前的版本都是MyISAM。关于MyISAM和InnoDB的区别，我总结为以下5个方面：

1. MyISAM不支持外键，InnoDB支持。
2. MyISAM不支持事务，而InnoDB支持ACID特性的事务处理。
3. MyISAM只支持表锁，而innoDB支持行级锁，删除插入的时候只需要锁定操作行就行。
4. MyISAM不支持外键，而InoDB支持外键。
5. MyISAM 不支持持数据库异常崩溃后的安全恢复，而 InnoDB 支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

比如如果需要支持事务，那必须要选择InnoDB。如果大部分的表操作都是查询，可以选择MyISAM。

4、MySQL5.x和8.0有什么区别

1. 功能增强，MySQL 8.0 支持窗口函数（如 ROW_NUMBER()、RANK()），方便复杂数据分析，支持隐藏索引，方便测试索引对查询性能的影响，支持降序索引，优化排序查询性能。
2. 安全性提升，MySQL 8.0 默认使用 caching_sha2_password 插件，提供更强的密码加密，MySQL 5.x 默认使用 mysql_native_password 插件，加密强度较低。
3. 字符集和排序规则，MySQL8，默认字符集改为 utf8mb4，支持更广泛的 Unicode 字符（如表情符号）。MySQL 5.x 默认字符集为 latin1，需手动更改为 utf8mb4。

5、char 和 varchar 的区别

char （0-255）是一个定长字段，假如申请了 char(10)的空间，那么无论实际存储多少内容，该字段都占用 10 个字符，而 varchar（0-65535） 是变长的，也就是说申请的只是最大长度，占用的空间为实际字符长度+1，最后一个字符存储使用了多长的空间。

在检索效率上来讲，char>varchar，因此在使用中，如果确定某个字段的值的长度，可以使用 char，否则应该尽量使用 varchar。

例如，存储身份证号（固定长度）、存诸订单号（可变长度）、存储国家编码（固定长度），这些都适合用char。

6、in 和 exists 的区别

in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。

• 如果查询的两个表大小相当，那么用in和exists差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
• notin 和not exists:如果査询语句使用了not in，那么内外表都进行全表扫描，没有用到索引:而notextsts的子查询依然能用到表上的索引。所以无论那个表大，用notexists都比not in要快。

7、MySQL 时间类型数据存储建议

datetime 不分时区，不会受到时区的影响
timestamp 分时区，会受到时区的影响

8、drop、delete 与 truncate 区别

• drop（丢弃数据）：drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
• truncate（清空数据）：truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
• delete（删除数据）：delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。

执行速度不同

一般来说：drop > truncate > delete

9、一条 Sql 的执行顺序

• FROM 和 JOIN：
  • 首先确定数据源，加载表数据。
  • 根据连接条件执行表连接（如 INNER JOIN、LEFT JOIN）。
• WHERE：
  • 对加载的数据进行过滤，只保留符合条件的行。
• GROUP BY：
  • 将过滤后的数据按指定列分组。
• HAVING：
  • 对分组后的结果进行过滤。
• SELECT：
  • 选择需要返回的列。
• DISTINCT：
  • 去除重复行。
• ORDER BY：
  • 对最终结果进行排序。
• LIMIT / OFFSET：
  • 返回指定数量的行

10、MySQL 执行查询的过程

select * from users where age = '18' and name = 'y';

① 使用连接器，通过客户端/服务器通信协议与MySQL建立连接。并查询是否有权限。
② Mysql8.0之前检查是否开启缓存，开启了Query Cache且命中完全相同的SQL语句，则将查询结果直接返回给客户端。
③ 由解析器（分析器）进行语法分析和语义分析，并生成解析树。

• 第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
• 第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

④ 由优化器生成执行计划。根据索引看看是否可以优化，优化器的作用就是它认为的最优的执行方案去执行，比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。
⑤ 执行器来执行SQL语句，这里具体的执行会操作MSQL的存储引擎来执行SQL语句，根据存储引擎类型，得到查询结果。若开启了Query Cache则缓存，否则直接返回。

11、MySQL 事务的四大特性以及实现原理

1. 原子性：事务是一个不可分割的操作单元，事务中的所有操作要么全部成功，要么全部失败回滚。

MySQL 使用 Undo Log（回滚日志） 实现原子性，当事务执行时，所有修改操作都会记录到 Undo Log 中。如果事务失败或回滚，MySQL 会根据 Undo Log 将数据恢复到事务开始前的状态。

2. 一致性：事务执行前后，数据库必须保持一致性状态，即数据满足预定义的约束（如主键、外键、唯一性等）。

一致性由数据库的约束和事务的原子性、隔离性、持久性共同保证。

• MySQL 通过以下机制实现一致性
  • 约束检查：如主键、外键、唯一性约束等。
  • 事务的 ACID 特性：确保事务执行过程中数据始终处于一致状态。

3. 隔离性：多个并发事务之间相互隔离，一个事务的操作不会被其他事务干扰。

MySQL 使用 锁机制 和 多版本并发控制（MVCC, Multi-Version Concurrency Control） 实现隔离性。

• 锁机制：
  • 行锁、表锁等用于控制并发访问。
  • 例如，写操作会加排他锁（X Lock），防止其他事务读写。
• MVCC：
  • 通过为每行数据维护多个版本来实现非阻塞读。
  • 读操作读取事务开始时的快照，写操作创建新版本。
• MySQL 提供了四种隔离级别：
  • 读未提交（Read Uncommitted）：最低隔离级别，可能读到未提交的数据。
  • 读已提交（Read Committed）：只能读到已提交的数据。
  • 可重复读（Repeatable Read）：MySQL 默认隔离级别，确保事务内多次读取结果一致。
  • 串行化（Serializable）：最高隔离级别，完全串行执行事务。

4. 持久性：事务提交后，对数据的修改是永久性的，即使系统崩溃也不会丢失。

MySQL 使用 Redo Log（重做日志） 实现持久性。当事务提交时，所有修改操作会先写入 Redo Log，然后再写入磁盘。如果系统崩溃，MySQL 可以通过 Redo Log 恢复未写入磁盘的数据。

12、什么是脏读、不可重复读、幻读

1. 脏读：读到了其他事务还没有提交的数据。
2. 不可重复读：对某数据进行读取过程中，有其他事务对数据进行了修改(UPDATE、DELET)，导致第二次读取的结果不同。
3. 幻读：事务在做范围查询过程中，有另外一个事务对范围内新增或删除了记录(NSERT、DELETE)，导致范围查询的结果条数不一致。

13、常见的日志都有什么用

1. slow query log（慢查询日志）

慢查询日志记录了执行时间超过 long_query_time （默认是10s，通常设置为1s）的所有查询语句，在解决 SQL 慢查询（SQL执行时间过长）问题的时候经常会用到。

找到慢SQL是优化SQL语句性能的第一步，然后再用EXPLAIN命令可以对慢SQL进行分析，获取执行计划的相关信息。

你可以通过show variables like "slow_query_log"命令来查看慢查询日志是否开启，默认是关闭的。

2. binlog（二进制日志）

binlog是MySQL用于记录数据库中的所有DDL语句和DML语句的一种二进制日志。它记录了所有对数据库结构和数据的修改操作，如INSERT、UPDATE和DELETES等。binlog主要用来对数据库进行数据备份、灾难恢复和数据复制等操作。binlog的格式分为基于语句的格式和基于行的格式。

你可以使用show binary logs命令查看所有二进制日志列表

binlog的格式有哪几种

一共有3种类型二进制记录方式：

• Statement模式：每一条会修改数据的sql都会被记录在binlog中，如inserts，updates，deletes。
• Row模式（推荐）：每一行的具体变更事件都会被记录在binlog中。
• Mixed模式：Statement模式和Row模式的混合。默认使用Statement模式，少数特殊具体场景自
  动切换到RoW模式。

1. statement，记录的是 SQL 的原文。不需要记录每一行的变化，减少了binlog 日志量，节约了 IO，提高性能。由于 sql 的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
2. row，不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如 alter table)，因此这种模式的文件保存的信息太多，日志量太大。
3. mixed，一种折中的方案，普通操作使用 statement 记录，当无法使用 statement 的时候使用 row。

binlog主要用来做什么

binlog最主要的应用场景是主从复制，主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

• 主库将数据库中数据的变化写入到binlog
• 从库连接主库
• 从库会创建一个I/O线程向主库请求更新的binlog
• 主库会创建一个binlog dump线程来发送binlog，从库中的/O线程负责接收
• 从库的I/O线程将接收的binlog写入到relay log中。
• 从库的SQL线程读取relay log同步数据本地（也就是再执行一遍SQL)。

拓展一下：

不知道大家有没有使用过阿里开源的一个叫做 canal 的工具。这个工具可以帮助我们实现 MySQL 和其他数据源比如 Elasticsearch 或者另外一台 MySQL 数据库之间的数据同步。很显然，这个工具的底层原理肯定也是依赖 binlog。canal 的原理就是模拟 MySQL 主从复制的过程，解析 binlog 将数据同步到其他的数据源。

另外，像咱们常用的分布式缓存组件 Redis 也是通过主从复制实现的读写分离。

3. redo log（重做日志）

Redo Log是MySQL用于实现崩溃恢复和数据持久性的一种机制。MySQL会将事务做了什么改动到Redo Log中。当系统崩溃或者发生异常情况时，MySQL会利用Redo Log中的记录信息来进行恢复操作，将事务所做的修改持久化到磁盘中。

4. undo log（徹销日志）

Undo Log则用于在事务回滚或系统崩溃时撤销（回滚）事务所做的修改。当一个事务执行完成后，MySQL会将事务修改前的数据记录到Undo Log中。如果事务需要回滚，则会从Undo Log中找到相应的记录来撤销事务所做的修改。另外，Undo Log还支持MVCC(多版本并发控制)机制，用于在并发事务执行时提供一定的隔离性。

14、什么是 MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制） 是一种用于实现数据库并发控制的机制。它通过维护数据的多个版本来实现非阻塞读操作，从而提高数据库的并发性能。MVCC 是许多现代数据库系统（如 MySQL、PostgreSQL、Oracle 等）的核心特性之一。

1. MVCC 的核心思想

• 数据版本化：
  • 每次对数据进行修改时，不会直接覆盖原有数据，而是创建一个新的版本。
  • 旧版本的数据仍然保留，供其他事务读取。
• 非阻塞读：
  • 读操作不会阻塞写操作，写操作也不会阻塞读操作。
  • 读操作读取的是事务开始时的数据快照，而不是最新的数据。

2. MVCC 的工作原理

MVCC 通过以下机制实现并发控制：

（1）数据版本链

• 每行数据会维护一个版本链，每个版本包含以下信息：
  • 数据内容。
  • 事务 ID（创建该版本的事务 ID）。
  • 版本创建时间（或事务开始时间）。
  • 版本删除时间（或事务提交时间）。
• 示例
  • 假设有一行数据 id=1, balance=100，经过多次更新后，版本链如下：

Version 1: balance=100 (事务ID=10, 创建时间=T1)
Version 2: balance=150 (事务ID=20, 创建时间=T2)
Version 3: balance=200 (事务ID=30, 创建时间=T3)

（2）事务可见性规则

• 每个事务在开始时会被分配一个唯一的事务 ID。
• 事务只能看到以下数据版本：
  • 版本创建时间早于当前事务的开始时间。
  • 版本删除时间晚于当前事务的开始时间（或未删除）。
• 示例：
  • 假设事务 A 的开始时间是 T2，它只能看到 Version 1 和 Version 2，而看不到 Version 3。

（3）读操作

• 读操作会读取事务开始时的数据快照。
• 通过版本链找到符合可见性规则的数据版本。
• 示例：
  • 事务 A 在 T2 开始，读取 id=1 的数据时，会返回 balance=150（Version 2）。

（4）写操作

• 写操作会创建新的数据版本，而不是直接修改旧版本。
• 旧版本仍然保留，供其他事务读取。
• 示例：
  • 事务 B 在 T3 更新 id=1 的数据，将 balance 从 150 改为 200，会创建 Version 3。

（5）清理旧版本

• 当某个数据版本不再被任何事务需要时，可以被清理（称为 垃圾回收）。
• 示例：
  • 如果所有事务都完成了对 Version 1 的访问，Version 1 可以被删除。

MVCC 在 MySQL 中的实现

• MySQL 的 InnoDB 存储引擎使用 MVCC 来实现事务的隔离性。
• 实现机制：
  • 每行数据包含两个隐藏字段：① DB_TRX_ID：最近修改该行数据的事务 ID。② DB_ROLL_PTR：指向 Undo Log 的指针，用于找到旧版本数据。
  • Undo Log：用于存储旧版本数据，支持回滚和一致性读。
  • Read View：每个事务在开始时创建一个 Read View，用于判断哪些数据版本对当前事务可见。

示例

id | balance
---|--------
1  | 100

• 事务操作
  • 事务 A（事务 ID=10）在 T1 开始，读取 id=1 的数据，看到 balance=100。
  • 事务 B（事务 ID=20）在 T2 开始，更新 id=1 的数据，将 balance 改为 150。
  • 事务 A 在 T3 再次读取 id=1 的数据，仍然看到 balance=100（可重复读）。
  • 事务 C（事务 ID=30）在 T4 开始，读取 id=1 的数据，看到 balance=150（读已提交）。

15、InnoDB的锁机制

1、按锁的级别划分，可分为共享锁、排他锁

• 共享锁就是多个事务只能读数据不能改数据。
• 对于排他锁的理解可能就有些差别，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。
• mysql InnoDB引擎默认的修改数据语句，update，delete，insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。
• 加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

共享锁

又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

用法

在查询语句后面增加 LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁。

SELECT ... LOCK IN SHARE MODE

验证

打开一个查询窗口，进行共享锁测试，给ID等于100的记录添加共享锁，SQL如下：

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE

在加了共享锁后，打开一个新的查询窗口，进行共享锁测试

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE;-- 使用共享锁 查询到数据
SELECT * FROM yang WHERE id = 100 FOR UPDATE; -- 1205 - 使用排它锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100;-- 不加锁 查询到数据

排它锁

又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

用法

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁

SELECT ... FOR UPDATE

验证

打开一个查询窗口，进行共享锁测试，给ID等于100的记录添加排它锁，SQL如下：

SELECT * FROM yang WHERE id = 100 FOR UPDATE

在加了排它锁后，打开一个新的查询窗口，进行排它锁测试

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE;-- 使用共享锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100 FOR UPDATE; -- 1205 - 使用排它锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100;-- 不加锁 查询到数据

2、按锁的粒度划分，可分为表级锁、行级锁

表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问。

特点： 粒度大，加锁简单，容易冲突

行锁

行锁是对所有行级别锁的一个统称，比如下面说的记录锁、间隙锁、临键锁都是属于行锁， 行锁是指加锁的时候锁住的是表的某一行或多行记录，多个事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问。

特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。

记录锁

Record Lock，翻译成记录锁，是加在索引记录上的锁。。 锁定的是某一行一级，比如

SELECT * FROM yang WHERE id = 1 FOR UPDATE

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

• 锁住一条记录。
• id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
• 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会变成成临键锁。

需要特别注意的是，记录锁锁定的是索引记录。即使表没有定义索引，InnoDB也会创建一个隐藏的聚集索引，并使用这个索来锁定记录。

间隙锁

它是行锁中的一种，它锁定的是一个范围区间的索引。锁定的是记录与记录之间的空隙，间隙锁只阻塞插入操作，解决幻读问题。

• 锁定一个区间，左开右开。

临键锁

临键锁也属于行锁的一种，并且它是 INNODB 的行锁默认算法，临键锁记录锁与间隙锁的并集，是mysql加锁的基本单位。

• 记录锁 + 间隙锁锁定的区间，左开右闭。

案例说明

id	yang	c
0	0	0
5	5	5
10	10	10
15	15	15

以上表，id为主键，yang为普通索引，c为普通列。表名为ep

接下来我们将场景分为唯一索引等值查询、唯一索引范围查询、普通索引等值查询以及普通索引范围查询来分析下mysql如何加锁（数据库默认隔离级别下）

① 唯一索引等值查询（如果记录存在加临建锁，然后会退化为记录锁，该记录不存在加临建锁，然后会退化为间隙锁）

update ep set c = 1 where id = 5 唯一索引等值查询记录存在加临建锁(0,5]然后退化为记录锁5
update ep set c = 1 where id = 7 唯一索引等值查询记录不存在加临建锁(5,10]然后退化为间隙锁(5,10) 

② 唯一索引范围查询（范围内的查询语句，会产生间隙锁）

update ep set c = 1 where id >= 5 and id < 71.先来看语句查询条件的前半部分 id >= 5，因此，这条语句最开始要找的第一行是 id = 5，结合加锁的两个核心，需要加上
临建锁(0,5]。又由于 id 是唯一索引，且 id = 5 的这行记录是存在的，因此会退化成记录锁，也就是只会对 id = 5 这一行加锁。2.再来看语句查询条件的后半部分id < 7，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 10 这一行停下来，然后加临建锁(5, 10]，重点来了，但由于 id = 10不满足 id < 7，因此会退化成间隙锁，加锁范围变为(5, 10)。所以，上述语句在主键 id 上的最终的加锁范围是 Record Lockid = 5 以及 Gap Lock (5, 10)。

③ 普通索引等值查询（如果记录存在，除了会加临建锁外，还额外加间隙锁，也就是会加两把锁，如果记录不存在，只会加临建锁，然后会退化为间隙锁，也就是只会加一把锁）

update ep set c = 1 where c = 5 普通索引等值查询记录存在加临建锁 (0,5]，又因为是非唯一索引等值查询，且查询的
记录 a= 5 是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是 (5,10)。
update ep set c = 1 where c = 7 普通索引等值查询记录不存在加临建锁(5,10]，但是由于查询的记录 a = 7 是不存在的，因此会退化为间隙锁，然后退化为间隙锁(5,10) 

④ 普通索引范围查询（普通索引范围查询， 不会退化为间隙锁和记录锁）

update ep set c = 1 where c < 11 普通索引上的 (0,15] 临键锁
update ep set c = 1 where c >= 10 普通索引上的 (5,10] 临键锁 (10,~] 临键锁
update ep set c = 1 where c >= 10 and c < 11 普通索引上的 (5,15] 临键锁先来看语句查询条件的前半部分 a >= 10，因此，这条语句最开始要找的第一行是 a = 10，结合加锁的两个核心，需要加上临建锁(5,10]。再来看语句查询条件的后半部分 a < 11，由于是范围查找，就会继续往后找第一个不满足条件的记录，也就是会找到 id = 15 这一行停下来，然后加临建锁 (10, 15]。所以，上述语句在普通索引 a 上的最终的加锁范围是 Next-key Lock (5, 10] 和 (10, 15]，也就是 (5, 15]。

3、基于锁的状态分类（意向共享锁与意向排它锁）

意向共享锁

意向共享(IS)锁：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排它锁

意向排他(IX)锁：事务有意向对表中的某些行加排他锁（X锁）

 -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。SELECT column FROM table ... FOR UPDATE;

• 意向共享锁（IS）和 意向排他锁（IX）都是表锁。
• 意向锁是一种 不与行级锁冲突的表级锁。
• 意向锁是 InnoDB自动加的， 不需用户干预。

16、乐观锁与悲观锁如何实现

在MySQL中，悲观锁是需要依靠数据库提供的锁机制实现的，在InnoDB引擎中，要使用悲观锁，需要先关闭
MySQL数据库的自动提交属性，然后通过select…for update来进行加锁。

//0.开始事务
begin;
//1.查询出商品信息
select quantity from items where id = 1 for update;
//2.修改商品quantity为2
update items set quantity = 2 where id = 1;
//3.提交事务
commit;

MySQL中的乐观锁主要通过CAS的机制来实现，一般通过version版本号来实现。

//查询出商品信息，quantity=3
select quantity from items where id = 1
//根据商品信息生成订单
//修改商品quantity为2
update items set quantity = 2 where id = 1 and quantity = 3;

17、什么是死锁，怎么解决

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率;
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率;

如果业务处理不好可以用分布式事务锁或者使用乐观锁

18、索引有哪些优缺点？索引有哪几种类型

优点 ：

• 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量），这也是创建索引的最主要的原因。
• 通过创建唯一性索引或者主键索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

创建索引的三种方式

• 在执行 CREATE TABLE 时创索引
• 使用 ALTER TABLE 命令创索引

 ALTER TABLE table_name ADD INDEX index_name (column)

• 使用 CREATE INDEX 命令创索引

CREATE INDEX index_name ON table_name (column)

19、InnoDB为什么使用B+树实现索引

1. B+树是一棵平衡树，每个叶子节点到根节点的路径长度相同，查找效率较高。
2. B+的所有关键字都在叶子节点上，因此范围查询时只需要遍历一扁叶子节点即可。
3. B+树的叶子节点都按照关键字大小顺序存放，因此可以快速地支持按照关键字大小进行排序。
4. B+树的非叶子节点不存储实际数据，因此可以存储更多的索引数据。
5. B+树的非叶子节点使用指针连接子节点，因此可以快速地支持范围查询和倒序查询。
6. B+树的加叶子节点之间通过双向链表链接，方便进行范围查询。

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

1. B+树可以进行范围查询，Hash 索引不能。
2. B+树支持联合索引的最左侧原则，Hash 索引不支持。
3. B+树支持 order by 排序，Hash 索引不支持。
4. Hash 索引在等值查询上比 B+树效率更高。
5. B+树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

20、什么是聚簇索引和非聚簇索

聚簇索引，简单点理解就是将数据与索引放到了一起，找到索引也就找到了数据。也就是说，对于聚簇索引来说，他的非叶子节点上存储的是索引字段的值，而他的叶子节点上存储的是这条记录的整行数据。

非聚簇索引，就是将数据与索引分开存储，叶子节点包含索引字段值及指向数据页数据行的逻辑指针。

没有创建主键怎么办

我们知道，Innodb中的聚簇索引是按照每张表的主键构造一个B+树，那么不知道大家有没有想过这个问题，如果
我们在表结构中没有定义主键，那怎么办呢？

其实，数据库中的每行记录中，除了保存了我们自己定义的一些字段以外，还有一些重要的db row id字段，其实他就是一个数据库帮我添加的隐藏主键，如果我们没有给这个表创建主键，会选择一个不为空的唯一索引来作为
聚簇索引，但是如果没有合适的唯一索引，那么会以这个隐藏主键来创建聚簇索引。

21、什么是回表，怎么减少回表的次数

那么，当我们根据非聚簇索引查询的时候，会先通过非聚簇索引查到主键的值，之后，还需要再通过主键的值再进
行一次查询才能得到我们要查询的数据。而这个过程就叫做回表。

所以，在InnoDB中，使用主键查询的时候，是效率更高的，因为这个过程不需要回表。另外，依赖覆盖索引、索引下推等技术，我们也可以通过优化索引结构以及SQL语句减少回表的次数。

覆盖索引

覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算上覆盖索引了。

例如（覆盖索引）

select id，age from yang where age = 48;--age非聚集索引上带有主键值，不需要回表

例如（覆盖索引）

select age from yang where age = 48;--SQL用到的索引列数据，覆盖了查询结果的列，不需要回表

回到idx_age索引树，你可以发现查询选项id和age都在叶子节点上了。因此，可以直接提供查询结果啦，根本就不需要再回表了。

例如（覆盖索引失效）

select id，age，sex from yang where age = 48;--sex需要回表查询出来

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

select * from tuser where name like '张%' and age=10;

索引下推

在MySQL 5.6之前，存储引擎根据通过联合索引找到name like ‘张%’ 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。可以看到只回表了一次。

22、什么是最左前缀匹配

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

CREATE INDEX index_age_name_sex ON yang (AGE,NAME,SEX)

如：（AGE， NAME，SEX）是一个联合索引，支持（AGE）（AGE， NAME）（AGE， NAME，SEX）查找。

EXPLAIN SELECT * FROM YANG WHERE AGE = 43--索引生效
EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND NAME = '1'--索引生效
EXPLAIN SELECT * FROM YANG WHERE AGE = 46 AND NAME = '1' AND SEX = 0--索引生效

那么（AGE，SEX）索引会不会生效呢，也是会生效的但是只有AGE走了索引

EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND SEX= 0--索引生效

那么（SEX，AGE，NAME）索引会不会生效呢，也是会生效的，这个属于匹配查询（where子句搜索条件顺序调换不影响索引使用，因为查询优化器会自动优化查询顺序 ），顺序可以颠倒

EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND NAME = '1' AND SEX = 0--索引生效
EXPLAIN SELECT * FROM YANG WHERE SEX = 0 AND NAME = '1' AND AGE = 43--索引生效
EXPLAIN SELECT * FROM YANG WHERE NAME = '1' AND SEX = 0 AND AGE = 43--索引生效

还有一种带有范围查询的时候，例如

select * from table where AGE = 1 and NAME > '2' and SEX = 0 这种类型的也只会有AGE与NAME走索引，SEX不会走

如图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。

因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态! 什么意思呢？

从全局来看，b的值为1，2，1，4，1，2，是无序的，因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1,4也是有序状态。 因此，你执行a = 1 and b = 2是a，b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。

综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

所以根据联合索引的最左匹配原则，我们在构建联合索引的时候，要把区分度高的字段，放在最左侧。

MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，但是在MySQL 8.0出现了索引跳跃扫描。

23、设计索引的时候有哪些原侧

1. 考虑查询的频率和效率：在决定创建索引之前，需要分析查询频率和效率。对于频繁查询的列，可以创建索引
   来加速查询，但对于不经常查询或者数据量较少的列，可以不创建索引。
2. 选择适合的索引类型：MySQL提供了多种索引类型，如B+Tree索引、哈希索引和全文索引等。不同类型的索
   引适用于不同的查询操作，需要根据实际情况选择适合的索引类型。
3. 考虑区分度：尽量不要选择区分度不高的字段作为索引，比如性别。但是也并不绝对，对于一些数据倾斜比较
   严重的字段，虽然区分度不高，但是如果有索引，查询占比少的数据时效率也会提升。
4. 考虑联合索引：联合索引是将多个列组合在一起创建的索引。当多个列一起被频繁查询时，可以考虑创建联合
   索引。
5. 考虑索引覆盖：联合索引可以通过索引覆盖而避免回表查询，可以大大提升效率，对于频繁的查询，可以考虑
   将select后面的字段和where后面的条件放在一起创建联合索引。
6. 避免创建过多的索引：创建过多的索引会占用大量的磁盘空间，影响写入性能。并且在数据新增和删除时也需
   要对索引进行维护。所以在创建索引时，需要仔细考虑需要索引的列，避免创建过多的索引。
7. 避免使用过长的索引：索引列的长度越长，索引效率越低。在创建索引时，需要选择长度合适的列作为索引
   列。
8. 合适的索引长度：虽然索引不建议太长，但是也要合理设置，如果设置的太短，比如身份证号，但是只把前面
   6位作为索引，那么可能会导致大量锁冲突。

是可以感知到的，但是数据覆盖我们可能过了很久才能发现。

24、MySQL 索引使用有哪些注意事项呢

• 被频繁更新的字段应该慎重建立索引
  • 虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了，因为数据修改索引树也要修改。
• 限制每张表上的索引数量
  • 首先占用空间，索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。
• 尽可能的考虑建立联合索引而不是单列索引
  • 如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。
• 考虑索引覆盖
  • 联合索引可以通过索引覆盖而避免回表查询，可以大大提升效率，对于频繁的查询，可以考虑将select后面的字段和where后面的条件放在一起创建联合索引。
• 注意避免冗余索引
  • 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name，city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。
• 索引哪些情况会失效
  • OR引起索引失效（OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的字段都加上索引，索引就不会失效）。
  • LIKE模糊查询导致索引失效（但是并不是所有LIKE查询都会失效，只有在查询时字段最左侧加%和左右侧都加%才会导致索引失效）。
    • 幸运的是在MySQL5.7.6之后，新增了虚拟列功能，为一个列建立一个虚拟列，并为虚拟列建立索引，在查询时where中like条件改为虚拟列，就可以使用索引了。
    • ALTER TABLE yang ADD COLUMN v_bbb VARCHAR(50) GENERATED ALWAYS AS (REVERSE(b)) VIRTUAL;
    • ALTER TABLE yang ADD INDEX v_bbb(v_bbb);
  • 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则索引可能失效。
    • 隐式类型转换规则：当索引字段是字符串和数字进行比较的时候，默认是字符串类型转换成数字类型。
    • 例如 SELECT * FROM YANG WHERE ID = '1000' 如果id是数字类型，那么类型不一致，就会变为
    • SELECT * FROM YANG WHERE ID = 1000 此时索引还是生效的，但是如果是 SELECT * FROM YANG WHERE ID = 1000 如果id是字符串类型，那么类型不一致，就会变为 SELECT * FROM YANG WHERE cast(ID unsigned int) = 1000 此时索引字段用到了函数。
  • 在索引列上使用内置函数和运算，索引不一定失效。
    • 索引查询方式有两种：① 从B+树根节点进行树搜索 ② 遍历叶子节点（双向链表）从第一个节点开始。
    • 看是否需要回表，不回表类似 SELECT COUNT(*) FROM YANG WHERE MONTH(Y) = 2 查询成本低，就会走索引。
    • 看是否需要回表，回表类似 SELECT * FROM YANG WHERE MONTH(Y) = 2 查需成本高，就会不走索引。
  • 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
  • 索引字段上使用is null， is not null，可能导致索引失（走和不走索引是和数据量或者和其他元素有关系）。
  • 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效（需要看条件，比如数据量，mysql在执行的时候会判断走索引的成本和全表扫描的成本，然后选择成本小的那个）。
  • mysql估计使用全表扫描要比使用索引快，则不使用索引。
  • 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

25、数据库加密后怎么做模糊查询

1. 保留部分明文

• 可以将其拆分为前缀和后缀：
  • 前缀（明文）：name_prefix（如 “Joh”）。
  • 后缀（加密）：name_encrypted（如加密后的 “n Doe”）。

查询时对 name_prefix 使用 LIKE 查询：

SELECT * FROM users WHERE name_prefix LIKE 'Joh%';

2. 使用可搜索加密（Searchable Encryption）

• 使用特殊的加密算法（如可搜索对称加密，SSE）对数据进行加密，同时支持模糊查询。

3. 使用数据库的加密函数

• 在数据库中存储加密数据，查询时对输入的关键字进行加密，然后进行匹配。适用于精确查询，但对模糊查询支持有限。

SELECT * FROM users WHERE encrypted_name = AES_ENCRYPT('John', 'key');

4. 使用分词和索引

• 对需要模糊查询的字段进行分词，生成索引。
• 加密存储原始数据，同时存储分词索引。
• 查询时对输入的关键字进行分词，匹配索引。

name_index:
user_id | token
--------|------
1       | Joh
1       | ohn

SELECT * FROM users WHERE user_id IN (SELECT user_id FROM name_index WHERE token LIKE 'Joh%'
);

26、COUNT(1)、COUNT(*) 和 COUNT(列名) 都用于统计行数

27、limit 0，100 和 limit 10000000，100 一样吗

典型的深度分页的问题。

MySQL的limit m n工作原理就是先读取前面m+n条记录，然后抛弃前m条，然后返回后面n条数据，所以m越大，偏移量越大，性能就越差。

所以，limit 10000000 100要比limit 0 100的性能差的多，因为他要先读取10000100条数据，然后再抛弃前面的
10000000条。

28、SQL语句如何实现insertOrUpdate的功能

假设有一个student表，包含id、name和age三列，其中id是主键。现在要插入一条数据，如果该数据的主键已经存在，则更新该数据的姓名和年龄，否则插入该数据。

INSERT INTO student (id,name,age) VALUES (1,'Alice',20) ON DUPLICATE KEY UPDATE name='Alice', age=20;

29、SQL执行计划分析的时候，要关注哪些信息

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

1. id：SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。
   • id相同，由上到下依次执行
   • id不同，值越大优先级越高，越先执行
   • id为null，最后执行
2. select type：操作的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY、UNION等。不同类型的操作会影响查询的执行效率。
   • SIMPLE：简单查询，不包含 UNION 或者子查询。
   • PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
   • SUBQUERY：子查询中的第一个 SELECT。
   • UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
   • DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
   • UNION RESULT：UNION 查询的结果
3. table：当前操作所涉及的表。
4. partitions：当前操作所涉及的分区。
5. type：表示查询时所使用的索引类型，包括ALL、index、range、ref、eq_ref、const等。
   • system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
   • const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
   • eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
   • ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
   • range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
   • index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
   • ALL：全表扫描。
6. possible keys：表示可能被查询优化器选择使用的索引。
7. key：表示查询优化器选择使用的索引。
8. key len：表示索引的长度。索引的长度越短，查询时的效率越高。

• 字符串
  • char(n)：n字节长度
  • varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
• 数值类型
  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节
• 时间类型
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
• 如果字段允许为 NULL，需要1字节记录是否为NULL

9. ref：表示连接操作所使用的索引。
10. rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。
11. filtered：表示比操作过滤掉的行数占扫描行数的百分比。该值越大，表示查询结果越准确。
12. Extra：这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

30、什么是buffer pool

Buffer Pool（缓冲池） 是数据库管理系统（DBMS）中的一种核心内存结构，用于缓存磁盘上的数据页，以减少磁盘 I/O 操作，从而提高数据库的读写性能。它是数据库性能优化的关键组件之一，尤其在 InnoDB 存储引擎（MySQL 的默认存储引擎）中扮演着重要角色。

• Buffer Pool 的作用
  • 缓存数据页：Buffer Pool 将磁盘上的数据页（如表数据、索引数据）缓存到内存中，避免频繁访问磁盘。
  • 加速读操作：如果数据页已经在 Buffer Pool 中，直接返回内存中的数据，无需访问磁盘。
  • 加速写操作：先将数据写入 Buffer Pool，后续通过后台线程刷新到磁盘。
  • 减少磁盘 I/O：通过缓存机制，减少对磁盘的直接访问，提升数据库性能。

Buffer Pool 是一个内存区域，通常由多个页（Page）组成，每个页的大小与磁盘上的数据页大小一致（如 InnoDB 默认页大小为 16KB）。

• Buffer Pool 的工作流程

（1）读操作

• 当查询需要读取某个数据页时，首先检查 Buffer Pool 中是否已经缓存了该页。
• 如果命中缓存（Buffer Pool 中存在该页），直接返回内存中的数据。
• 如果未命中缓存（Buffer Pool 中不存在该页），从磁盘读取该页并加载到 Buffer Pool 中。

（2）写操作

• 当修改某个数据页时，首先检查 Buffer Pool 中是否已经缓存了该页。
• 如果命中缓存，直接修改内存中的数据页，并将其标记为脏页（Dirty Page）。
• 如果未命中缓存，从磁盘读取该页到 Buffer Pool 中，然后进行修改。
• 脏页会通过后台线程（如 InnoDB 的 Checkpoint 机制）刷新到磁盘。

（3）缓存淘汰

• 当 Buffer Pool 空间不足时，使用 LRU 算法淘汰最近最少使用的页。
• 如果淘汰的页是脏页，需要先将其刷新到磁盘。
  

31、什么是InnoDB的叶分裂和叶合并

在 InnoDB 存储引擎中，叶分裂（Leaf Split） 和 叶合并（Leaf Merge） 是与 B+ 树索引结构相关的两种重要操作。它们用于维护索引的平衡性和高效性，确保索引在数据插入、删除和更新时能够保持最佳性能。

什么是叶分裂？

当向 B+ 树的叶子节点插入数据时，如果叶子节点已满（达到最大容量），则需要将其分裂为两个节点，以容纳新的数据。这个过程称为 叶分裂。

假设叶子节点的最大容量为 4 个键值，当前节点为 [10, 20, 30, 40]，插入新键值 25：

1. 节点已满，触发叶分裂。
2. 分裂为两个节点：[10, 20] 和 [25, 30, 40]。
3. 更新父节点，插入新键值 25。

什么是叶合并？

当从 B+ 树的叶子节点删除数据时，如果叶子节点的数据量过少（低于最小容量），则需要将其与相邻节点合并，以保持 B+ 树的平衡性。这个过程称为 叶合并。

假设叶子节点的最小容量为 2 个键值，当前节点为 [10, 20]，删除键值 10：

1. 节点数据量低于最小容量，触发叶合并。
2. 与相邻节点 [25, 30, 40] 合并，形成新节点 [20, 25, 30, 40]。
3. 更新父节点，删除键值 20。

32、MySQL 查询缓存

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用

my.cnf 加入以下配置，重启 MySQL 开启查询缓存

query_cache_type=1
query_cache_size=600000

MySQL 执行以下命令也可以开启查询缓存

set global  query_cache_type=1;
set global  query_cache_size=600000;

如上，开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。

查询缓存不命中的情况：

1. 任何两个查询在任何字符上的不同都会导致缓存不命中。
2. 如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果也不会被缓存。
3. 缓存建立之后，MySQL 的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启查询缓存要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十 MB 比较合适。此外，还可以通过 sql_cache 和 sql_no_cache 来控制某个查询语句是否需要缓存：

select sql_no_cache count(*) from usr;

取消查询缓存原因

1. 频繁失效：查询缓存是以表级别为单位进行管理的，这意味着如果任何表中的数据发生变化，与该表相关的所
   有查询缓存都将被清除。这导致了缓存的频繁失效，减少了其效用。
2. 内存开销：查询缓存需要占用大量内存来存储查询文本和结果集，这对于具有大量查询和数据的数据库来说，
   会导致内存开销问题。
3. 不一致性：有时查询结果可能会因为数据库中的数据更改而不再与缓存的结果匹配，这可能导致不一致性的问
   题。
4. 查询分布不均匀：在某些情况下，查询缓存可能会导致性能下降，因为它不能很好地应对不均匀的查询分布。

33、如何进行SQL调优

SQL 调优是提升数据库查询性能的关键步骤，通常涉及优化查询语句、索引设计、数据库配置等方面。以下是 SQL 调优的详细步骤和常用技巧：

1. 分析性能瓶颈

• 使用执行计划：
  • 通过 EXPLAIN 或 EXPLAIN ANALYZE 查看查询的执行计划，了解查询的执行步骤和开销。
  • 是否使用了索引（type 字段）。
  • 扫描的行数（rows 字段）。
  • 排序或临时表的使用（Extra 字段）。

EXPLAIN SELECT * FROM employees WHERE department_id = 101;

• 监控慢查询
  • 使用数据库的慢查询日志（如 MySQL 的 slow_query_log）找出执行时间较长的查询。
• 性能分析工具
  • 使用工具（如 MySQL 的 Performance Schema、pt-query-digest）分析查询性能。

2. 优化查询语句

• 查询需要的列信息即可，少用 * 代替列信息。

SELECT id, name FROM employees;

• 减少子查询。

行子查询时，mysql需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。子查询的结果集存储的临时表不存在索引，所以查询性能会受到一定的影响。对于返回结果集比较大的子查询，其对查询性能的影响也越来越大。在mysql中，可以使用连接（join）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。如果使用索引的话，性能要更好。

-- 子查询
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
-- 改写为 JOIN
SELECT e.* FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.location = 'New York';

• 避免使用 OR

-- 使用 OR
SELECT * FROM employees WHERE department_id = 101 OR department_id = 102;
-- 改写为 IN
SELECT * FROM employees WHERE department_id IN (101, 102);

• 对于分页查询，避免使用 OFFSET，改用基于游标的分页

-- 低效
SELECT * FROM employees LIMIT 10 OFFSET 1000;
-- 高效
SELECT * FROM employees WHERE id > 1000 LIMIT 10;

3. 优化索引

• 避免过多索引，索引会增加写操作的开销，避免为不常用的列添加索引。
• 使用覆盖索引，如果索引包含查询所需的所有列，可以避免回表操作。

CREATE INDEX idx_covering ON employees(department_id, name);

• 复合索引的顺序：复合索引的列顺序应与查询条件匹配

-- 查询条件
SELECT * FROM employees WHERE department_id = 101 AND name = 'Alice';
-- 复合索引
CREATE INDEX idx_department_name ON employees(department_id, name);

• 索引失效的问题

一般是先通过执行计划分析是否走了索引，以及所走的索引是否符合预期，如果因为索引设计的不合理、或者索引失效导致的，那么就可以修改索引，或者修改SQL语句。

4. 优化表结构

• 分区表：
  • 对大表进行分区（如按时间、范围），减少查询扫描的数据量。

CREATE TABLE employees (id INT,name VARCHAR(100),hire_date DATE
) PARTITION BY RANGE (YEAR(hire_date)) (PARTITION p0 VALUES LESS THAN (2020),PARTITION p1 VALUES LESS THAN (2021),PARTITION p2 VALUES LESS THAN (2022)
);

• 数据类型优化：
  • 使用合适的数据类型（如 INT 代替 VARCHAR 存储数字），减少存储空间和计算开销。

5. 优化数据库配置

• 调整缓冲区大小：
  • 增加数据库的缓冲区大小（如 innodb_buffer_pool_size），提高数据缓存命中率。
• 调整连接数：
  • 根据并发请求量调整最大连接数（如 max_connections）。
• 启用查询缓存：
  • 对于读多写少的场景，可以启用查询缓存（如 MySQL 的 query_cache）。

6. 分库分表

• 垂直分库：
  • 将不同业务模块的数据存储到不同的数据库中。
• 水平分表：
  • 将大表按规则拆分到多个小表中（如按用户 ID 哈希分表）。`

34、什么是读写分离

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

1. 读写分离会带来什么问题

读写分离对于提升数据库的并发非常有效，但是，同时也会引来一个问题：主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的 主从同步延迟 。

解决方法

1. 本地缓存标记

流程

1)用户A发起写请求，更新了主库，并在客户端设置标记，过期时间，如：cookies2)用户A再发起读请求时，带上这个本地标记在后端3)后端在处理请求时，获取请求传过来的数据，看有没有这个标记(如：cookies)4)有这个业务标记，走主库；没有走从库。

这个方案就保证了用户A的读请求肯定是数据一致的，而且没有性能问题，因为标记是本地客户端传过去的。

但有写小伙伴就会问那其他用户在本地客户端是没有这个标记的，他们走的就是从库了。那其他用户不就看不到这个数据了吗？说的对，其他用户是看不到，但看不到的时间很短，过个1～10秒就能够看到。还是那句话，脱离业务的方案是耍流氓。(推荐)

2. 如何实现读写分离

不论是使用哪一种读写分离具体的实现方案，想要实现读写分离一般包含如下几步：

① 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
② 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
③ 系统将写请求交给主数据库处理，读请求交给从数据库处理。

落实到项目本身的话，常用的方式有两种：

• 代理方式

我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。

提供类似功能的中间件有 MySQL Router（官方）、Atlas（基于 MySQL Proxy）、Maxscale、MyCat。

• 组件方式

在这种方式中，我们可以通过引入第三方组件来帮助我们读写请求。

这也是我比较推荐的一种方式。这种方式目前在各种互联网公司中用的最多的，相关的实际的案例也非常多。如果你要采用这种方式的话，推荐使用 sharding-jdbc ，直接引入 jar 包即可使用，非常方便。同时，也节省了很多运维的成本。

3. 主从复制原理是什么

MySQL binlog(binary log 即二进制日志文件) 主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)。因此，我们根据主库的 MySQL binlog 日志就能够将主库的数据同步到从库中。

5. 主库将数据库中数据的变化写入到 binlog。
6. 从库连接主库从库会创建一个 I/O 线程向主库请求更新的 binlog。
7. 主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收。
8. 从库的 I/O 线程将接收的 binlog 写入到 relay log 中。
9. 从库的 SQL 线程读取 relay log 同步数据本地（也就是再执行一遍 SQL ）。

拓展一下：

不知道大家有没有使用过阿里开源的一个叫做 canal 的工具。这个工具可以帮助我们实现 MySQL 和其他数据源比如 Elasticsearch 或者另外一台 MySQL 数据库之间的数据同步。很显然，这个工具的底层原理肯定也是依赖 binlog。canal 的原理就是模拟 MySQL 主从复制的过程，解析 binlog 将数据同步到其他的数据源。

另外，像咱们常用的分布式缓存组件 Redis 也是通过主从复制实现的读写分离。

35、什么是分库分表

1. 什么是分库

分库就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

• 垂直分库：就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

举个例子：说你将数据库中的用户表、订单表和商品表分别单独拆分为用户数据库、订单数据库和商品数据库。

• 水平分库 ：是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。

举个例子：订单表数据量太大，你对订单表进行了水平切分（水平分表），然后将切分后的 2 张订单表分别放在两个不同的数据库。

2. 什么是分表

分表就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

• 垂直分表 ：是对数据表列的拆分，把一张列比较多的表拆分为多张表。

举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。

• 水平分表：是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。

举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

3. 什么情况下需要分库分表

• 需要分库

分库主要解决的是并发量大的问题。因为并发量一旦上来了，因为数据库的连接数是有限的，虽然可以调整，但是也不是无限调整的。所以，当你的数据库连接数不足了的时候，就需要考虑分库了，通过增加数据库实例的方式来提供更多的可用数据库链接，从而提升系统的并发度。

比较典型的分库的场景就是我们在做微服务拆分的时候，就会按照业务边界，把各个业务的数据从一个单一的数据库中拆分开，分表把订单、物流、商品、会员等单独放到单独的数据库中。

1. 容量

我们给数据库实例分配的磁盘容量是固定的，数据量持续的大幅增长，用不了多久单机的容量就会承载不了这么多数据，解决办法简单粗暴，加容量！

2. 连接数

单机的容量可以随意扩展，但数据库的连接数却是有限的，在高并发场景下多个业务同时对一个数据库操作，很容易将连接数耗尽导致too many connections报错，导致后续数据库无法正常访问。

可以通过max_connections查看MySQL最大连接数。

show variables like '%max_connections%'

将原本单数据库按不同业务拆分成订单库、物流库、积分库等不仅可以有效分摊数据库读写压力，也提高了系统容错性。

• 需要分表

分库主要解决的是并发量大的问题，那分表其实主要解决的是数据量大的问题。假如你的单表数据量非常大，因为并发不高，数据量连接可能还够，但是存储和查询的性能遇到了瓶颈了，你做了很多优化之后还是无法提升效率的时候，就需要考虑做分表了。

通过将数据拆分到多张表中，来减少单表的数据量，从而提升查询速度。一般我们认为，单表行数超过 500 万行或者单表容量超过 2GB之后，才需要考虑做分库分表了，小于这个数据量，遇到性能问题先建议大家通过其他优化来解决。

1. 数据量

导致数据库查询慢的原因有很多，SQL没命中索引、like扫全表、用了函数计算，这些都可以通过优化手段解决，可唯独数据量大是MySQL无法通过自身优化解决的。慢的根本原因是InnoDB存储引擎，聚簇索引结构的 B+tree 层级变高，磁盘IO变多查询性能变慢。

36、常见的分片算法有哪些

分片（Sharding） 是一种将数据分布到多个数据库或表中的技术，用于解决单表数据量过大、性能下降的问题。分片的核心是 分片算法，它决定了数据如何分布到不同的分片中。以下是常见的分片算法及其特点：

1. 范围分片（Range Sharding）

• 原理：
  • 根据某个字段的范围（如 ID、时间）将数据分布到不同的分片。
  • 例如，ID 从 1 到 1000 的数据存储在分片 1，ID 从 1001 到 2000 的数据存储在分片 2。
• 优点：
  • 实现简单，易于理解和维护。
  • 适合范围查询（如查询某段时间的数据）。
• 缺点：
  • 可能导致数据分布不均匀（热点问题）。
  • 扩展性较差，新增分片时需要重新分配数据。
• 示例：
  • 按用户 ID 范围分片：

分片1：1-1000
分片2：1001-2000
分片3：2001-3000

2. 哈希分片（Hash Sharding）

• 原理：
  • 对某个字段（如 ID）进行哈希计算，将哈希值映射到不同的分片。
  • 例如，hash(id) % N，其中 N 是分片数量。
• 优点：
  • 数据分布均匀，避免热点问题。
  • 扩展性较好，新增分片时只需重新哈希部分数据。
• 缺点：
  • 范围查询效率低，因为数据分散在各个分片中。
  • 哈希冲突可能导致数据分布不均匀。
• 示例：
  • 对用户 ID 进行哈希分片：

hash(id) % 3 = 0 → 分片1
hash(id) % 3 = 1 → 分片2
hash(id) % 3 = 2 → 分片3

3. 一致性哈希（Consistent Hashing）

• 原理：
  • 将分片和数据映射到一个哈希环上，通过顺时针查找确定数据所属的分片。
  • 新增或删除分片时，只需重新分配部分数据。
• 优点：
  • 扩展性好，新增或删除分片时影响较小。
  • 数据分布均匀。
• 缺点：
  • 实现复杂。
  • 范围查询效率低。
• 示例：
  • 将分片和数据映射到哈希环上：

分片1：0-1000
分片2：1001-2000
分片3：2001-3000

4. 列表分片（List Sharding）

• 原理：
  • 根据某个字段的取值列表将数据分布到不同的分片。
  • 例如，将地区为“北京”的数据存储在分片 1，地区为“上海”的数据存储在分片 2。
• 优点：
  • 灵活，可以根据业务需求自定义分片规则。
  • 适合离散值的分片。
• 缺点：
  • 数据分布可能不均匀。
  • 扩展性较差，新增分片时需要重新分配数据。
• 示例：
  • 按地区分片：

分片1：北京
分片2：上海
分片3：广州

5. 复合分片（Composite Sharding）

• 原理：
  • 结合多种分片算法（如范围分片 + 哈希分片）进行数据分布。
  • 例如，先按范围分片，再对每个范围进行哈希分片。
• 优点：
  • 灵活性高，可以根据业务需求设计复杂的分片规则。
  • 数据分布更均匀。
• 缺点：
  • 实现复杂。
  • 维护成本高。
• 示例：
  • 先按用户 ID 范围分片，再对每个范围进行哈希分片：

分片1：1-1000 → hash(id) % 2
分片2：1001-2000 → hash(id) % 2

6. 地理位置分片（Geo Sharding）

• 原理：
  • 根据数据的地理位置（如经纬度）将数据分布到不同的分片。
  • 例如，将某个区域的数据存储在离该区域最近的分片。
• 优点：
  • 适合地理位置相关的业务（如地图、物流）。
  • 减少跨区域查询的延迟。
• 缺点：
  • 数据分布可能不均匀。
  • 实现复杂。
• 示例：
  • 按城市分片：

分片1：北京
分片2：上海
分片3：广州

7. 动态分片（Dynamic Sharding）

• 原理：
  • 根据数据的实时状态（如负载、数据量）动态调整分片规则。
  • 例如，当某个分片的数据量过大时，自动将其拆分为多个分片。
• 优点：
  • 自动化程度高，适应性强。
  • 数据分布均匀。
• 缺点：
  • 实现复杂。
  • 需要额外的监控和调度系统。
• 示例：
  • 根据数据量动态调整分片：

分片1：数据量 < 1000
分片2：数据量 >= 1000

37、分库分表会带来什么问题

了解了上边分库分表的拆分方式不难发现，相比于拆分前的单库单表，系统的数据存储架构演变到现在已经变得非常复杂。看几个具有代表性的问题，比如：

事务问题

分库分表后，假设两个表在不同的数据库，那么本地事务已经无效啦，需要使用分布式事务了。

跨库关联

同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。

分布式ID

据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID，或者使用雪花算法生成分布式ID。

排序问题

跨节点的 count，order by，group by以及聚合函数等问题：可以分别在各个节点上得到结果后在应用程序端进行合并。

分页问题

• 方案1：在个节点查到对应结果后，在代码端汇聚再分页。
• 方案2：把分页交给前端，前端传来pageSize和pageNo，在各个数据库节点都执行分页，然后汇聚总数量前端。这样缺点就是会造成空查，如果分页需要排序，也不好搞。

38、分库分表中间件

• cobar
• Mycat
• Sharding-JDBC
• Atlas
• TDDL（淘宝）
• vitess

ShardingSphere 项目（包括 Sharding-JDBC、Sharding-Proxy 和 Sharding-Sidecar）是当当捐入 Apache 的，目前主要由京东数科的一些巨佬维护。

ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。

另外，ShardingSphere 的生态体系完善，社区活跃，文档完善，更新和发布比较频繁。

39、分库分表后数据怎么迁移

分库分表之后，我们如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

比较简单同时也是非常常用的方案就是停机迁移，写个脚本老库的数据写到新库中。比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。然后，你写一个脚本将老库的数据都同步到新库中。

如果你不想停机迁移数据的话，也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景，实现起来要稍微麻烦一些。具体原理是这样的：

• 我们对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。这样就能保证，咱们新库里的数据是最新的。
• 在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。
• 重复上一步的操作，直到老库和新库的数据一致为止。

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助上面提到的数据库同步工具 Canal 做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

40、MYSQL 的主从延迟，你怎么解决

读写分离对于提升数据库的并发非常有效，但是，同时也会引来一个问题：主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。这也就是我们经常说的 主从同步延迟 。

解决方法

1. 本地缓存标记

流程

1)用户A发起写请求，更新了主库，并在客户端设置标记，过期时间，如：cookies2)用户A再发起读请求时，带上这个本地标记在后端3)后端在处理请求时，获取请求传过来的数据，看有没有这个标记(如：cookies)4)有这个业务标记，走主库；没有走从库。

这个方案就保证了用户A的读请求肯定是数据一致的，而且没有性能问题，因为标记是本地客户端传过去的。

但有写小伙伴就会问那其他用户在本地客户端是没有这个标记的，他们走的就是从库了。那其他用户不就看不到这个数据了吗？说的对，其他用户是看不到，但看不到的时间很短，过个1～10秒就能够看到。还是那句话，脱离业务的方案是耍流氓。