2万字八股，带你搞定mysql八股

Mysql

1. mysql 中数据排序是怎么实现的

排序过程中，如果排序字段命中索引，则利用索引排序

反之，使用文件排序。

文件排序，如果数据量少则在内存中排序，具体是使用单路排序或者双路排序。如果数据大则利用磁盘文件进行外部排序，一般使用归并排序。

双路排序

有一个叫max_length_for_sort_data参数，默认是 4096 字节，如果 select 列的数据长度超过它，则 Mysql采用row_Id排序，即把 row_id(有主键就是主键)+排序字段放置到sort_buffer中排序。

select a,b,c from t1 where a = ‘面试鸭’ order by b;

单路排序

假设 select 列的数据没有超过 max_length_for_sort_data, 则可以进行单路排序，就是将select的字段都放置到sort_buffer中

文件磁盘临时排序

前面提到，如果查询的数据超过sort_buffer,说明内存放不下了，因此需要利用磁盘文件进行外部排序，一般会使用归并排序，简单理解就是将数据分为很多份文件，单独对文件排序，之后再合并成一个有序的大文件。

利用磁盘排序效率会更低，针对一些情况可以调大sort_buffer_size，避免磁盘临时文件排序。

2. MySql 的 change Buffer 是什么，它有什么作用

Change Buffer 是 Mysql InnnoDB 存储引擎中的一个机制，用于暂存对二级索引的插入和更新操作的变更，而不立即执行这些操作，随后，当 InnoDB 进行合适的条件时(如页被读取或 Flush 操作)，会将这些变更写入到二级索引中。

作用：

• 提高写入性能：通过将对二级索引的变更暂存，可以减少对磁盘的频繁写入，提示插入和更新操作的性能。
• 批量处理：Change Buffer 可以在后续的操作中批量处理这些变量，减少随机写入的开销。

Change Buffer的大小

• Change Buffer 的大小可以通过系统变量 innodb_change_buffer_size 进行配置，默认值为 25% 的 InnoDB 缓存池大小，最大值可以设置为 50%, 合理配置可以提示性能，但过大可能导致内存不足。

3. 详细描述一条 SQL 语句在 Mysql 中的执行过程

1. 先通过连接器校验权限
2. 利用分析器进行 SQL 语句的词法分析和语法分析，构建解析树
3. 使用优化器选择合适的索引和表连接顺序，最终选择一个最佳的执行执行
4. 利用执行器，调用引擎层查询数据，返回结果集给客户端

4. Mysql的存储引擎有哪些，它们之间有什么区别

1. InnoDB(重点):

   • 支持事务，行级锁和外键
   • 提供高并发性能，适用于高负载的 OLTP 应用
   • 数据以聚集索引的方式存储，提高检索效率

2. MyISAM(重点):

   • 不支持事务和外键，使用表级锁。
   • 适合读取多，更新少的场景，如数据仓库
   • 具有较高的读性能和较快的表级锁定

3. Memory:

   • 数据存储在内存中，速度快，但数据在服务器重启后丢失。
   • 适用于临时数据存储或快速排序。

5. Mysql 的索引类型有哪些

从数据结构角度来看，MySQL 索引可以分为以下几类：

• **B+树索引：**通过树形结构存储数据，适用于范围查询（如BETWEEN）和精确查询（如=），支持有序数据的快速查找、排序和聚合操作。是MySQL默认的索引类型，常用于InnoDB和MyISAM引擎。
• **哈希索引：**基于哈希表的结构，适用于等值查询（如=），查询速度非常快，但不支持范围查询（如>、〈）。哈希索引不存储数据的顺序，常用于Memory引擎。
• 倒排索引（Full-Text）：用于全文搜索，将全文分词，通过存储词与文档的映射，支持模糊匹配和关键字搜索。特别适合用于大文本字段，如TEXT类型的列，用于查找包含特定词语的记录。
• **R-树索引：**专为多维空间数据（如地理坐标）设计，适用于空间查询（例如，计算地理位置的最近距离、区域查询等）。常用于存储和查询地理信息系统（GIS）中的空间数据。

从常见的基于 InnoDB B+ 树索引角度来看，可以分为：

• 聚簇索引：InnoDB 中主键索引就是聚簇索引。它基于主键排序存储。之所以叫聚簇索引是因为索引的叶子节点存储完整数据行
  数据。
• **非聚簇索引：**指的是InnoDB中非主键索引的索引，之所以称之为非聚簇是因为这个索引的叶子节点仅保存索引字段和主键的
  **值。**如果要查询完整的数据行中的数据，需要再从聚簇索引即主键索引中通过主键查询，一个表可以有多个非聚簇索引。

从索引性质 的角度来看，可以分为：

• **普通索引：**一般指非主键索引且非唯一索引。
• **主键索引：**表中的每一行数据都有唯一的主键。每个表只能有一个主键索引，且主键值不能为NuLL。InnoDB 中主键索引是聚
  簇索引结构实现的。
• **联合索引：**由多个列组成的索引，适用于多列的查询条件，能够提高包含多个条件的查询的性能。联合索引中的列是按照指定顺
  序排列的。
• **唯一索引：**保证索引列中的值是唯一的，可以有效防止重复数据的插入。唯一索引允许 NULL 值，但一个列中可以有多个 NULL 。
• **全文索引：**用于全文搜索，支持对长文本字段（如TEXT类型）进行关键字查找，支持自然语言处理、模糊匹配等操作。适用于
  需要对文本内容进行复杂搜索的场景。
• 空间索引：用于空间数据（如地图上的经纬度坐标等）查询。通常使用R-树结构，适合多维数据的查询，如区域查询和最近距离查询，主要用于MyISAM和InnoDB存储引|擎中的地理信息数据。

6. Mysql InnoDB 引擎中的聚簇索引和非聚簇索引有什么区别

聚簇索引：

• 索引叶子节点存储的是数据行，可以直接访问完整数据。
• 每个表只能有一个聚簇索引，通常是主键索引，适合范围查询和排序。

非聚簇索引：

• 索引叶子节点存储的是数据行的主键和对应的索引列，需通过主键才能访问完整的数据行。
• 一个表可以有多个非聚簇索引（称之为非主键索引、辅助索引、二级索引I），适用于快速查找特定列的数据。

简单的 B+ 树示例图

主键索引

• 非叶子节点存储主键和页号
• 叶子节点存储完整的数据
• 叶子节点之间有双向链表链接，便于范围查询
• 叶子节点内部有页目录，内部记录时单链表链接，通过页目录二分再遍历即可得到对应记录
• B+ 树只能帮助快速定位到的是页，而不是记录
• 页大小默认是 16k, 是按照主键大小排序的，所以无序的记录插入会因为排序会插入到页中间，又因为容量有限会导致页分裂存储，性能比较差，所以主键要求有序。

非聚簇索引：

• 和主键索引的差别就在于叶子节点仅仅存储索引列和主键，不包含完整行的数据

7. MySql 中回表是什么

“回表” 是指在使用二级索引(非聚簇索引) 作为条件进行查询时，由于二级索引中只存储了索引字段的指和对应的主键值，无法得到其他数据。如果要查询数据行中的其他数据，需要根据主键去聚簇索引查找实际的数据行

1. select * from user where age = 20 (user 包含 name, age, id三个字段。假设 Mysql 优化器确定使用 age 索引)
2. 根据二级索引 (age) 找到 age = 20 的主键键值
3. 由于查询条件是 select *, 需要 name, age, id 三个字段，而 age 索引内没有 name 字段，因此还需要再根据主键去查找整行的数据

回表其实不仅仅只是多查一次，还会带来随机I/0。
因为通过 id 去主键索引查询的时候，id 肯定是不连续的（例如 age 为 20 的人很多，age 索引中年龄是有序的，但是 id 无序，且是不连续的），所以去主键索引频繁查询会造成大量随机 I/O，我们都知道顺序 I/O 查询快，而随机 I/O 慢，所以频繁回表效率很低。
因此，不要因为图方便，在代码里都用select*，从而引发不必要的回表操作。

8. Mysql 索引的最左前缀匹配原则是什么

Mysql 索引的最左前缀匹配原则指的是在使用联合索引，查询条件必须从索引的最左侧开始匹配。如果一个联合索引包含多个列，查询条件必须包含第一个列的条件，然后是第二个列，以此类推。

底层原理：因为联合索引在 B+ 树的排列方式遵循"从左到右"的顺序，例如联合索引(first_name, last_name, age) 会按照(first_name, last_name, age)的顺序在 B+ 树中进行排序。

Mysql 在查找时会优先使用 first_name 作为匹配依据，然后依次使用 last_name 和 age。因此，组合索引能够从左到右依次高效匹配，跳过最左侧字段会导致无法利用该索引。

按照(first_name, last_name, age) 的顺序在 B+ 树中的排列方式(大致的示意图)

9. Mysql 的覆盖索引是什么

Mysql 的覆盖索引 （Covering Index) 是指二级索引中包含了查询所需的所有字段，从而使查询可以仅通过访问二级索引而不需要访问实际的表数据(主键索引)

优点

• 减少I/O操作：因为查询可以直接从索引中获取所有需要的数据，避免了访问实际表的数据页，从而减少了I/O操作。
• 提高查询速：索引比表数据更加紧凑，因此从索引中读取数据比表中读取要快
• 减少内存占用：只需要读取索引页而不是数据表页，可以减少内存占用

create table employees (
	emp_id int primary key,
    first_name varchar(50),
    last_name varchar(50),
    hire_date date,
    department_id int,
    index idx_name_hiredate(last_name, first_name, hire_date)
);

select last_name, first_name, hire_date
from employees
where last_name = '鸭' and first_name= '面试'

select last_name, first_name, hire_date, department_id
from employees
where last_name = '鸭' and first_name = '面试';

10. Mysql 的索引下推是什么

是一种减少回表查询，提高查询效率的技术。它允许 mysql 在使用索引查找数据时，将部分查询条件下推到存储引擎层过滤，从而减少需要从表中读取的数据行，减少了IO(本该由Server层做操作，交由存储引擎层因此叫做’下推’)

1. 索引下推在 Mysql 5.6 及以后的版本支持，InnoDB 和 MyISAM 这两个存储引擎都生效
2. 如果查询中引用了子查询索引下推可能不会生效，具体看 explain
3. 使用了函数或表达式索引下推也不能生效，这个和是否能命中索引的条件是一样的。
4. 使用了聚簇索引(主键)查询，索引下推也不会生效，因为其是对于非聚簇索引来进行减少回表次数。

11. 在 mysql 中建索引时需要注意哪些事项

1. 不能盲目建立索引，索引并不是越多越好，索引会占用空间，且每次修改的时候可能都需要维护索引的数据，消耗资源。
2. 对于字段的值有大量重复的不要建立索引。比如说：性别字段，在这种重复比例很大的数据行中，建立索引也不能提高检索速度。但是也不绝对，例如定时任务的场景，大部分任务都是成功，少部分任务状态是失败的，这时候通过失败状态去查询任务，实际上能过滤大部分成功的任务，效率还是可以的。
3. 对于一些长字段不应该建立索引。比如 text、longtext这种类型字段不应该建立索引l。因为占据的内存大，扫描的时候大量加载至内存中还耗时，使得提升的性能可能不明显，甚至可能还会降低整体的性能，因为别的缓存数据可能因为它被踢出内存，下次查询还需要从磁盘中获取。
4. 当数据表的修改频率远大于查询频率时，应该好好考虑是否需要建立索引。因为建立索引会减慢修改的效率，如果很少的查询较多的修改，则得不偿失。
5. 对于需要频繁作为条件查询的字段应该建立索引l。在where关键词后经常查询的字段，建立索引能提高查询的效率，如果有多个条件经常一起查询，则可以考虑联合索引，减少索引数量。
6. 对经常在 order by、group by、distinct 后面的字段建立索引。这些操作通常需要对结果进行排序、分组或者去重，而索引可以帮助加快这些操作的速度。

12. Mysql 中的索引数量是否越多越好

索引并不是越多越好，因为索引不论从时间还是空间上都是有一定成本的

1）从时间上
每次对表中的数据进行增删改(INSERT、UPDATE或DELETE)的时候，索引也必须被更新，这会增加写入操作的开销。例如删除了一个name为面试鸭的记录，不仅主键索引|上需要修改，如果name字段有索引l，那么name索引|也需要修改，所以索引越多需要修改的地方也就越多，时间开销就大了，并且B+树可能会有页分裂、合并等操作，时间开销就会更大。
还有一点需要注意：MySQL有个查询优化器，它需要分析当前的查询，选择最优的计划，这过程就需要考虑选择哪个索引的查询成本低。如果索引过多，那么会导致优化器耗费更多的时间在选择上，甚至可能因为数据的不准确而选择了次优的索引。
2）从空间上
每建立一个二级索引，都需要新建一个B+树，默认每个数据页都是 16kb，如果数据量很大，索引又很多，占用的空间可不小。

13. 详细叙述 Mysql 的 B+ 树中查询数据的全过程

1. 数据从根节点找起，根据比较数据键值与节点中存储的索引键值，确定数据落在哪个区间，从而确定分支，从上到下最终定位到叶子节点
2. 叶子节点存储实际的数据行记录，但是有一页有16kb大小，存储的数据行不止一条
3. 叶子节点中数据行以组的形式，利用页目录，通过二分查找可以定位到对应的组
4. 定位组后，利用链表遍历就可以找到对应的数据行。

从上图可以知晓，叶子节点有页目录结构，它其实就是一个索引，通过它可以快速找到记录。
页目录分为了多个槽，每个槽都指向对应一个分组内的最大记录，每个分组内都会包含若干条记录。
通过二分查询，利用槽就能直接定位到记录所在的组，从而就能获取到对应的记录。
举个例子，现在有5个槽，如果想查找主键为3的记录，此时的流程是：
1）通过二分得到槽的中间位置，low=θ，high= 4，（0+4)/2=2;
2）通过槽定位到第二个分组中的主键为 4的记录，4大于3，1ow=θ不变，high= 2 ;
3）继续二分（0+2)/2=1;槽1中主键2小于3，low=1，high=2;
4）此时high－low=1，可以确定值在 high 即槽2中，但是槽2只能定位到主键为4的记录，又因为槽之间是挨着的，所以可以
得到槽1的位置，从槽1入手拿到主键2的记录，然后因为记录是通过单向链表串起来的，往下遍历即可定位到主键3的记录。
以上就是利用二分查询的定位流程。通过槽可找到对应记录所在的组，或能直接定位到记录，或还需通过链表遍历找到对应的数据。
实际上，每个分组的记录数是有规定的，图中做了省略只画了两条，InnoDB 规定：

• 第一个分组只有一条记录
• 中间的分组 4-8 条记录
• 最后一个分组 1-8 条记录

因此不必担心遍历很长的链表导致性能问题。
这题的重点是先简单提下从根节点遍历到子节点的过程，然后提到叶子节点默认大小为 16KB，所以理论上能存储很多记录，从而引
出页目录，再通过二分查找才能对应记录。

14 为什么 Mysql 选择使用 B+树作为索引结构

1）高效的查找性能：
B+树是一种自平衡树，每个叶子节点到根节点的路径长度相同，B+树在插入和删除节点时会进行分裂和合并操作，以保持树的平
衡，但它又会有一定的冗余节点，使得删除的时候树结构的变化小，更高效。
查找、插入、删除等操作的时间复杂度为O(log n)，能够保证在大数据量情况下也能有较快的响应时间。
2）树的高度增长不会过快，使得查询磁盘的1/0次数减少：
B+树不像红黑树，数据越多树的高度增长就越快。它是多叉树，非叶子节点仅保存主键或索引值和页面指针，使得每一页能容纳更
多的记录，因此内存中就能存放更多索引，容易命中缓存，使得查询磁盘的I/O次数减少。
3）范围查询能力强：
B+树特别适合范围查询。因为叶子节点通过链表链接，从根节点定位到叶子节点查找到范围的起点之后，只需要顺序扫描链表即可
遍历后续的数据，非常高效。

15 B+树 和 B树 区别

1. B 树每个节点都存储了完整的数据，而 B+ 树非叶子节点仅存储 key 和 指针，完整数据存储在叶子节点。这使得 B+ 树可以在内存中存放更多索引页，减少磁盘查询次数。
2. B+ 树叶子组成了链表，便于区间查找，而 B 树只能每一层遍历查找
3. B+ 树查询时间更平均，稳定，都需要从根节点扫描到叶子节点。而 B 树则在非叶子节点就可能找到对应的数据返回。

16 Mysql 是如何实现事务的

MySQL 主要是通过：锁、RedoLog、UndoLog、MVCC 来实现事务。
MySQL 利用锁（行锁、间隙锁等等）机制，使用数据并发修改的控制，满足事务的隔离性。
Redo Log（重做日志），它会记录事务对数据库的所有修改，当 MySQL 发生宕机或崩溃时，通过重放 redolog 就可以恢复数据，
用来满足事务的持久性。
**Undo Log（回滚日志），**它会记录事务的反向操作，简单地说就是保存数据的历史版本，用于事务的回滚，使得事务执行失败之后
可以恢复之前的样子。实现原子性和隔离性
MVCC（多版本并发控制），满足了非锁定读的需求，提高了并发度，实现了读已提交和可重复读两种隔离级别，实现了事务的隔离
性。

其实事务主要是为了实现一致性，具体是通过AID，即原子性、隔离性和持久性来达到一致性的目的。

17 Mysql 中长事务可能会导致哪些问题

1）长时间的锁竞争，阻塞资源：

• 长事务持有锁的时间较长，容易导致其他事务在尝试获取相同锁时发生阻塞，从而增加系统的等待时间和降低并发性能。
• 业务线程也会因为长时间的数据库请求等待而阻塞，部分业务的阻塞可能还会影响到别的服务，导致产生雪崩，最终使得服务全
  面崩盘，导致非常严重的线上事故。

2）死锁风险：

• 长事务更容易产生死锁，因为多个事务可能在互相等待对方释放锁，导致系统无法继续执行。

3）主从延迟：

• 主库需要长时间执行，然后传输给从库，从库又要重放好久，期间可能有很长一段时间数据是不同步的。

4）回滚导致时间浪费：

• 如果长事务执行很长一段时间，中间突发状况导致抛错，使得事务回滚了，之前做的执行都浪费了。

长事务的 sql 如何处理

18 Mysql 中的 MVCC 是什么

MVCC（Multi-Version ConcurrencyControl，多版本并发控制）是一种并发控制机制，允许多个事务同时读取和写入数据库，而无需互相等待，从而提高数据库的并发性能。
在MVCC中，数据库为每个事务创建一个数据快照。每当数据被修改时，MySQL不会立即覆盖原有数据，而是生成新版本的记录。
每个记录都保留了对应的版本号或时间戳。
多版本之间串联起来就形成了一条版本链，这样不同时刻启动的事务可以无锁地获得不同版本的数据(普通读)。此时读(普通读)写操作不会阻塞。
写操作可以继续写，无非就是会创建新的数据版本（但只有在事务提交后，新版本才会对其他事务可见。未提交的事务修改不会影响其他事务的读取），历史版本记录可供已经启动的事务读取。

Undo Log

Undo Log 是 Mysql InnoDB 中用于支持事务的回滚操作的一种日志机制。它记录了数据修改的历史信息，使得事务失败或需要撤销某些操作时，可以将数据恢复到先前的状态。

实际上 MVCC 所谓的多版本不是真的存储了多个 版本的数据，只是借助 undolog 记录每次写操作的反向操作，所以索引上对应的记录只会有一个版本，即最新版本。只不过可以根据 undolog 中的记录反向操作得到数据的历史版本，所以看起来是多个版本

如果没有 MVCC,系统必须频繁地对读写操作进行加锁来保证数据地正确性，因为增加了锁的获取和释放开销，会导致整体系统响应速度变慢，这种实现叫 LBCC(Lock-Based Concurrent Control)

19.MySql 中的事务隔离级别有哪些

读未提交(READ UNCOMMITTED)

• 这是最低的隔离级别，在该级别下，一个事务可以看到另一个事务尚未提交的数据修改，这可能会导致脏读问题，即读取到其他事务未提交的数据。

读已提交(READ COMMITTED)

• 在这个级别下，一个事务只能看到已经提交的其他事务所做的修改。这可以避免脏读问题，但是可能会引发不可重复读问题，即在同一个事务中，相同的查询可能返回不同的结果。

可重复读(REPEATABLE READ:

• 在这个级别下，确保在一个事务中的多个查询返回的结果是一致的。这可以避免不可重复读问题，但是可能会引发幻读问题，即在同一个事务中，多次查询可能返回不同数量的行(Mysql 默认的隔离级别)

串行化(serializable):

• 并发 SQL 事务在 serializable 隔离级别下的执行被保证是可串行化的。可串行化执行被定义为：并发执行的 SQL 事务的操作，其效果与这些 SQL 事务按某种顺序串行执行的效果相同。串行执行是指每个 SQL 事务在下一个 SQL 事务开始之前完成其全部操作
• 这是最高的隔离级别，在这个级别下，保证事务间的操作结果相当于一个按顺序执行的单线程操作。这可以避免所以的并发问题，但是会大大降低并发性能。

20.Mysql 默认的事务隔离级别是什么，为什么选择这个级别

Mysql 默认的隔离级别是可重复读(Repeatable Read), 即 RR。

原因是为了兼容早期 binlog 的 statement 格式问题，如果是使用读已提交，读未提交等隔离级别，使用了 statement 格式的 binlog 会导致主从(备)数据库不一致问题

21.数据库的脏读，不可重复读和幻读分别是什么

脏读(Dirty Read)

• 一个事务读取到另一个事务未提交的数据。如果该未提交事务最终被回滚，那么第一个事务读取的数据是不一致的(脏的)

不可重复读(Non-repetable Read):

• 在同一事务中，读取同一数据两次，但由于其他事务的提交，读取的结果不同。例如，事务 A 读取了一行数据，事务 B 修改并提交了这行数据，导致事务 A 再次读取时得到不同的值。

幻读 (phantom Read)

• 在同一事务中，执行相同的查询操作，返回的结果集由于其他事务的插入而发生变化。例如，事务 A 查询符合某条件记录，事务 B 插入了新记录并提交，导致事务 A 再次查询时看到不同的记录数量。

几种读与隔离级别的关系

• 读未提交允许脏读。
• 读已提交防止脏读，但可能出现不可重复读。
• 可重复读防止脏读和不可重复读，但人可能出现幻读
• 串行化防止所有三种问题，但性能开销较大

22.Mysql 中有哪些锁类型

1. 行级锁(Row Lock) (重点)

   • 仅对特定的行加锁，允许其他事务并发访问不同的行，适用于高并发场景。

2. 表级锁(Table Lock)(重点)

   • 对整个表加锁，其他事务无法对该表进行任何读写操作，适用于需要保证完整性的小型表

3. 意向锁

   • 一种表锁，用于表示某个事务对某行数据加锁的意图，分为意向共享锁和意向排他锁，主要用于行级锁与表级锁的结合

4. 共享锁（Shared Lock)重点

   • 允许多个事务并发读取同一资源，但不允许修改。只有在释放共享锁后，其他事务才能获得排它锁s

5. 排他锁(Exclusive Lock)

   • 只允许一个事务对资源进行读写，其他事务在获得排他锁之前无法访问该资源

6. 元数据锁(Metadata Lock, MDL):

   • 用于保护数据库对象(如表和索引)的元数据，防止在进行 DDL 操作时其他事务对这些对象进行修改

7. 间隙锁（Gap Lock)

   • 针对索引中两个记录之间的间隙加锁，防止其他事务在这个间隙中插入新记录，以避免幻读。间隙锁不锁具体行，而是锁定行与行之间的空间

8. 临键锁(Next-key Lock)

   • 是行级锁和间隙锁的结合，锁定具体行和其前面的间隙，确保在一个范围内不会出现幻读。常用于支持可重复读的隔离级别

9. 插入意向锁

   • 一种等待间隙的锁，用于指示事务打算在某个间隙中插入纪录，允许其他事务进行共享锁，但在插入时会阻止其他排它锁。

10. 自增锁

    • 在插入自增列时，加锁以保证自增值得唯一性，防止并发插入导致得冲突。通常在插入时被使用，以确保生成得自增 ID 是唯一的

23 Mysql 的乐观锁和悲观锁

悲观锁(Pessimistic Locking):

• 假设会发生冲突，因此在操作数据之前就对数据加锁， 确保其他事务无法访问该数据。常用于对数据一致性要求较高的场景。
• 实现方式：使用行级锁或表级锁，例如可以使用select ... for update或 lock in share mode语句来加锁

乐观锁(Optimistic Locking)

• 假设不会发生冲突，因此在操作数据时不加锁，而是在更新数据时进行版本控制或校验，如果发现数据被其他事务修改，则会拒绝当前事务的修改，需重新尝试。
• 实现方式：通常通过版本号或时间戳来实现，每次更新时检查版本号或时间戳是否一致

select id, name, version from user where id = 1;
update users
set name = `new_name`, version = version + 1
where id = 1 and version = current_version;

乐观锁适合并发冲突少，读多写少的场景，不用通过加锁只需要通过比较字段版本号(或时间戳)是否发生改变的形式，无锁操作，吞吐量较高。

select id, name from users where id = 1 for update;

update users set name = 'new_name' where id = 1;

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

-- 读取数并加锁
select id, name from users where id = 1 for update;

-- 执行更新操作
update user set name = 'new_name' where id = 1;

24 Mysql 中如果发生死锁应该如何解决

手动 kill 发生死锁的语句

• 可以通过命令，手动快速地找出被阻塞的事务及其线程 ID, 然后手动 kill 它，及时释放资源

主动检查与回滚

• Mysql 自带死锁检测机制(innodb_deadlock_detect), 当检测到死锁时，数据库会自动回滚其中一个事务，以解除死锁。通常会回滚事务中持有最少资源的那个
• 也有锁等待超时参数(innodb_lock_wait_timeout)，当获取锁的等待时间或超过阈值时，就释放锁进行回滚

通过 show engine innodb status 来获取死锁日志信息。

常见避免死锁或降低死锁的手段
1）避免大事务。大事务占据锁的时间长，将大事务拆分成多个小事务快速释放锁，可降低死锁产生的概率和避免冲突。
2）调整申请锁的顺序。在更新数据的时候要保证获得足够的锁，举个例子：先获取影响范围大的锁，比如说修改操作，先将排他锁获取到，再获取共享锁。或固定顺序访问数据，这样也能避免死锁的情况。
3）更改数据库隔离级别。可重复读比读已提交多了间隙锁和临键锁，利用读已提交替换之可降低死锁的情况。
4）合理建立索引，减少加锁范围。如果命中索引，则会锁对应的行，不然就是全表行都加锁，这样冲突大，死锁的概率就高了。
5）开启死锁检测，适当调整锁等待时长。

• lock_id，唯一标识每个锁
• lock_mode: 锁的类型(X: 排它锁, S: 共享锁)
• lock_type: 锁的作用对象(如 record 表示行锁)
• lock_table: 被锁定表
• lock_index: 相关索引
• lock_owner: 拥有该锁的事务 ID
• lock_data: 被锁定的数据行的主键值

25 如何使用 MySQL 的explain 语句进行查询分析

explain 主要用来 SQL 分析，它主要的属性详解如下：

• id：查询的执行顺序的标识符，值越大优先级越高。简单查询的 id 通常为 1，复杂查询（如包含子查询或UNION）的id会有多个。
• select_type（重要）：查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询等)
• table：查询的数据表。
• type（重要）：访问类型，如ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。一般来说，性能从好到差的顺序是： const >eq_ref > ref > range >index > ALL。
• possible_keys：可能用到的索引。
• key（重要）：实际用到的索引。
• key_len：用到索引的长度。
• ref：显示索引的哪一列被使用。
• rows（重要）：估计要扫描的行数，值越小越好。
• **filtered：**显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。
• Extra（重要）：额外信息，如Using index（表示使用覆盖索引l）、Using where（表示使用 WHERE条件进行过滤）、Using temporary(表示使用临时表）、Using filesort()（表示需要额外的排序步骤）。

type 详解：

• system：表示查询的表只有一行（系统表）。这是一个特殊的情况，不常见。
• const：表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引，并且是常量比较
• eq_ref：表示对于每个来自前一张表的行，MySQL仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引I的情况下。
• ref:MySQL 使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的（如普通索引）。
• range：表示MySQL会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引I的范围查询中（如BETWEEN、>，，

26 Mysql 中 count(*), count(1) 和 count(字段名) 有什么区别

都是统计行数的聚合函数

1. count(*)会统计表中所有行的数量，包括 null值(不会忽略任何一行数据)。由于只是计算行数，不需要对具体的列进行处理，因此性能通常较高。
2. count(1)和count(*)几乎没差别，也会统计表中所有行的数量，包括null值。
3. count(字段名)会统计指定字段不为 null的行数。这种写法会对指定的字段进行计数，只会统计字段不为null的行

效率

1）count(1)和count(*)效率一致，网上其实众说纷纭，实际上当然得看官网怎么说!

There is no performance difference.没有差异的!

2. count(字段)的查询就是全表扫描(如果对应的字段没有索引，如果有索引则用索引)，正常情况下它还需要判断字段是否是 null 值，因此理论上会比 count(1) 和 count(*)慢。

但是如果字段不为 null, 例如是主键，那么理论上也差不多，而且本质上它们的统计功能不一样，在需要统计 null 的时候，只能用 count(1) 和 count(*)，不需要统计 null 的时候只能用 count(字段),所有也不同太纠结性能问题。

27 Mysql 中的 int(11) 的 11 表示什么

在 mysql 中,int(11)表示显示宽度，并不影响存储的大小或数组范围。具体来说：

• 显示宽度：当使用 zerofill属性时，int(11)表示如果数值的位数少于11位，则会在前面填充零。例如，数值42将显示为 000000000042。不使用 zerofill 属性时，显示结果是 42(前面有九个空格)
• 存储大小：int类型始终占用 4 字节(32)位，可以表示的范围是从-2147483648到2147483647

所以 int(11)中的数字与整数的大小无关，只是显示格式的定义。这个配置主要是为了在查询结果中保持一致的显示格式，方便阅读和理解，但实际业务上基本用不到这个

Mysql 8.0 显示宽度的变化

Mysql 8.0 版本中，整数类型的显示宽度(例如 int(11) 中的 11) 被正式弃用。即定义的显示宽度不再影响任何存储或比较操作，显示宽度的设置被忽略

28 Mysql 中 varchar 和 char 有什么区别

回答重点

char 和 varchar 是两种用于存储字符串的列类型，它俩最大的不同就是一个是固定长度，一个是可变长度。

• char(n): 以固定长度的字符串。mysql 也会在字符串的末尾填充空格以达到指定长度(char 类型的字符串后面有空格的话，innodb会忽略)
• varchar(n): 可变长度的字符串。varchar 列的长度是可变的**，存储的字符串长度与实际数据长度相等，并且在存储数据时会额外增加1到2个字节(字符长度超过 255,则使用两个字节) 用于存储字符串的长度信息。**

理论上来说CHAR会比VARCHAR快，**因为VARCHAR长度不固定，处理需要多一次运算，但是实际上这种运算耗时微乎其微，而固定大小在很多场景下比较浪费空间，**除非存储的字符确认是固定大小或者本身就很短，不然业务上推荐使用VARCHAR。

MySQL计算abc总长度比较长，sort_buffer可能放不下，就会使用双路排序，即sortbuffer里存放需要排序的字段b和id进
行排序，待排完后，再通过id回表查询得到a、b、c字段。这样就多了回表的一步，性能比较差。

如果select字段长度少，那么就可以使用单路排序，即将select的数据都放入到sort_buffer中，排完序后直接返回给客户端。这里计算a、b、c长度依据的就是varchar(n）中的n，所以如果n设置很大，虽然占用空间是动态的，但是会隐性影响排序的性
能。

varchar 支持的最大长度

29 mysql 中如何进行 sql 调优

平时进行 sql 调优，主要是通过观察 慢sql，然后利用 explain 分析查询语句的执行计划，识别性能瓶颈，优化查询语句。

1. 合理设计索引，利用联合索引进行覆盖索引的优化，避免回表的发生，减少一次查询和随机 I/O
2. 避免 select *, 只查询必要的字段
3. 避免在 sql 中进行函数计算等操作，使得无法命中索引
4. 避免使用 %like,导致全表扫描
5. 注意联合索引需满足最左匹配原则
6. 不要对无索引字段进行排序操作
7. 连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描

除此之外，还可以利用缓存来优化，一些变化少或者访问频繁的数据设置到缓存中，减轻数据库的压力，提升查询的效率。还可以通过业务来优化，例如少展示一些不必要的字段，减少多表查询的情况，将列表查询替换换成分页分批查询等等。

慢 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 秒，就会被记录到慢日志中。

30 在 mysql 中避免单点故障

一般会使用主从架构来避免单点故障，主数据库处理写操作，从数据库处理读操作，主数据库故障时可以切换到从数据库。同时会对数据进行定期备份并存储在不同的物理位置，以便在发生故障时能够快速恢复数据。

并且需要建立监控系统，实时监控数据库的健康状态，并在发生故障时及时报告。

主备架构

主备架构就是主机和备机。备机是不干活的，也就是不对外提供服务，只是默默地在同步主机的数据，然后等着某一天主机挂了之后，它取而代之！

切换的话主要有两种方式

• 人工切换，得知主机挂了之后手动把备机切成主机，缺点就是慢
• 利用 keepalived 或者自己写个脚本来作监控，然后自动切换

主从架构

主从架构就是主机和从机。从机和备机的区别在于，它是对外提供服务的，一般而言主从就是读写分离，写请求指派到主机，读请求指派到从机

主主架构

一般情况下都不会有主主架构

当同时有两个写请求达到分别打到两个主库同一张表的时候，则会同时创建一条记录，这条记录的ID是一样的，这样数据同步之后其中有一条就会被覆盖了，这会出问题的。

31 如何在 mysql 中实现读写分离

做法一：代码封装

讲白了就是代码层面抽出一个中间层，由中间层来实现读写分离和数据库连接。

利用个代理类，对外暴露正常的读写接口，里面封装了逻辑，将读操作指向从库的数据源，写操作指向主库的数据源。

• 优点：简单，并且可以根据业务定制变化，随心所欲
• 缺点：如果数据库宕机了，发生主从切换了之后，就得修改配置重启。如果系统是多语言得话，需要为每个语言都实现一个中间代码，重复开发。

做法二：使用中间件

中间件一般是独立部署得系统，客户端与中间件的交互是通过 sql 协议的

所有在客户端看来连接的就是一个数据库，通过 sql 协议交互也可以屏蔽多语言的差异。缺点就是整体架构多一个系统需要维护，并且可能成为性能瓶颈，毕竟交互都需要经过它中转。

官方的： mysql-proxy, 360的Atlas, shardingsphere, mycat等

什么是读写分离

1）读写分离就是读操作和写操作从以前的一台服务器上剥离开来，将主库压力分担一些到从库。本质上是因为访问量太大，主库的压力过大，单机数据库无法支撑并发读写。然后一般而言读的次数远高于写，因此将读操作分发到从库上，这就是常见的读写分离。
2）读写分离还有个操作就是主库不建查询的索引，从库建查询的索引。**因为索引是需要维护的，比如你插入一条数据，不仅要在聚簇索引上面插入，对应的二级索引也得插入，修改也是一样的。**所以将读操作分到从库了之后，可以在主库把查询要用的索引删了，减少写操作对主库的影响。

32 什么是 mysql 的主从同步机制？它是如何实现的

Mysql 的主从同步机制是一种数据复制技术，用于将主数据库(Master) 上的数据同步到一个或多个从数据库(slave)中。主要是通过二进制日志(Binary Log, 简称 binlog) 实现数据的复制。主数据库在执行写操作时，会将这些操作记录到 binlog 中，然后推送给从数据库，从数据库重放对应的日志即可完成复制。

Mysql 主从复制类型

mysql 支持异步复制，同步复制，半同步复制。

• 异步复制：主库不需要等待从库的响应(性能较高，数据一致性低)
• 同步复制: 主库同步等待所有从库确认收到数据(性能差，数据一致性高)
• 半同步复制：主库等待至少一个从库确认收到数据(性能折中，数据一致性较高)

33 如何处理 Mysql 的主从同步延迟

首先需要明确一点延迟是必然存在的，无论怎么优化都无法避免延迟的存在，只能减少延迟的时间

常见解决方式有以下几种：

• **二次查询。**如果从库查不到数据，则再去主库查一遍，由API封装这个逻辑即可，算是一个兜底策略，比较简单。不过等于读的压力又转移到主库身上了，如果有不法分子故意查询必定查不到的查询，这就对主库产生冲击了。
• 强制将写之后立马读的操作转移到主库上。这种属于代码写死了，比如一些写入之后立马查询的操作，就绑定在一起，写死都走主库。不推荐，比较死板。
• **关键业务读写都走主库，**非关键还是读写分离。比如上面我举例的用户注册这种，可以读写主库，这样就不会有登陆报该用户不存在的问题，这种访问量频次应该也不会很多，所以看业务适当调整此类接口。
• **使用缓存，**主库写入后同步到缓存中，这样查询时可以先查询缓存，避免了延迟的问题，不过又引入了缓存数据一致性的问题。
• 除此之外，也可以提一提配置问题，例如主库的配置高，从库的配置太低了，可以提升从库的配置等。如果面试官对MySQL比较熟，可能会追问一些偏DBA侧的问题，例如并行复制等。

34 什么是分库分表？分库分表有哪些类型(或策略)

分库分表是数据库性能优化的一种方法，通过将数据分散在多个数据库或表中，来提高系统的可扩展性，性能和可用性。

1) 水平分表：

1. 将同一张表的数据按行划分，分散到多个表中。例如，可以按用户 ID 的范围将数据分为多个表(如user_1,user_2)

2）垂直分表：

• 将一张表的不同列拆分到多个表中，以减少每张表的字段数量和提高查询效率。例如，用户可以分为基本信息表和详细信息表。

3) 水平分库

• 将相同的表结构复制一份到另一个库中，每个库的表结构是一样的，可以减少单一数据库的读写压力，在大数量的情况下提高读写性能。例如，database,database2

4) 垂直分库

• 将数据分散到不同的数据库实例中。可以根据业务功能或模块进行分库，如将用户数据，订单数据分别存储在不同的数据库中。

分库分表

分库分表的优点

• 提高性能：分库后，通过减少单个数据库的负载，提高读写性能。
• 可扩展性：可以通过增加新的数据库或表来扩展系统
• 容错性：某个表或表的故障不会影响整个系统

分库分表的缺点

• 复杂性：数据的查询，维护和事务管理变得复杂，增加了开发和运维的成本。
• 事务处理：跨库或跨表的事务处理复杂，需要额外的处理机制
• 数据一致性：额外机制来保证数据的一致性和完整性。

35 主导项目中的分库分表，大致的实施流程是？

1）先分析业务需求：

• 确定数据量及增长趋势，评估分库分表的必要性。(需要一定的预判但是不要过度设计)

2）设计分库分表方案

• 选择合适的分库分表策略(水平，垂直，哈希，范围等)，并规范分库分表的结构

3) 实现数据路由

• 根据分库分表策略设计数据路由机制，一般通过应用层代码或数据库中间件来实现，将请求路由到相应的数据库或表

4）数据迁移

• 将现有数据迁移到新的分库分表结构中，可以通过批量导入，ETL 工具或脚本来完成

36 对数据库进行分库分表可能会引发哪些问题

37 从 Mysql 获取数据，是从磁盘读取吗？(buffer pool)

在 mysql 中，获取数据并不总是直接从磁盘读取。mysql 使用缓存机制，比如 InnoDB 存储 InnoDB 存储引擎，会将常用的数据和索引缓存在内存中，以提高读取性能。当查询数据时，系统首先会检查缓存(如缓冲池),如果数据存在内存中，则直接从内存中读取；如果不在，则会从磁盘读取并加载到缓存中。

mysql 中的缓存

MySQL从缓存中读取所指的缓存，实际上包含了两个缓存：
1）查询缓存（MySQL8.0已废除）：在MySQL8.0之前，MySQL提供了查询缓存功能，用于缓存查询结果。如果相同的查询（同一个查询SQL）再次执行，并且表没有发生任何变化（这个条件比较苛刻，所以后续废除了），则MySQL可以直接从查询缓存中返回结果，而无需重新执行查询。具体的实现类似用一个 map 存储了之前的结果，key是SQL，value为结果，SQL执行的时候，先去这个map看看通过key是否能找到值，如果找到则直接返回。
2）InnoDB缓冲池（bufferpool）：这是InnoDB存储引擎的核心缓存组件。缓冲池缓存了数据页、索引顶和其他相关信息。查询数据时，MySQL首先在缓冲池中查找，如果找到则直接返回数据，否则从磁盘读取数据页并将其缓存到缓冲池中。
查询缓存和bufferpool大致的结构关系如下：

数据页和索引页

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

从磁盘读取数据

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

buffer pool 知识

38 Mysql 的 Doublewrite Buffer 是什么，它有什么作用

Mysql 的 Doublewrite Buffer 是 InnoDB 存储引擎中的一个机制，用于确保数据的安全性和一致性**。其作用是将数据首先写入一个内存缓存区(双写缓冲区)，然后再将其写入数据文件。**这种方式可以防止在写入过程中因崩溃或故障导致数据损坏，确保数据的一致性和完整性。

工作原理简述：

• 写入流程：当事务提交时，InnoDB 首先将数据写入 Doublewrite Buffer,再从该缓冲区将数据写入磁盘的实际数据文件。
• 恢复机制：在崩溃恢复时，InnoDB 会使用 Doublewrite Buffer 中的数据来修复损坏的页，保证数据不丢失。

进一步理解 Doublewrite Buffer

39 Mysql中的 Log Buffer 是什么？它有什么作用

Mysql 中的 Log Buffer 是一个内存区域，用于暂时存储事务日志（redo log) 的数据。在 InnoDB 存储引擎中，它的主要作用是提高性能，通过批量写入操作将日志数据从内存中写入磁盘，减少磁盘 I/O 操作的频率

40 为什么在 Mysql 中不推荐使用多表 JOIN

性能问题：

• 多表 JOIN 可能会导致查询性能下降，尤其是在处理大数据集时，JOIN 操作的计算复杂度会显著增加，需要进行大量的数据扫描和匹配，增加了内存和 CPU 的消耗，导致响应时间变长

可读性和维护性

• 多表 JOIN 的查询语句较为复杂，降低了 sql 的可读性和可维护性。复杂的语句可能会增加错误发生的概率，使得后续的调试和优化更加困难

多表 JOIN

这里的多表往往指的是超过三个表才是多表，正常两个表join是没问题的！(但是也需要评估下量级和是否命中索引)
阿里的 Java 规范手册里也有一句话：“超过三个表禁止使用Join”。
这是为什么呢？
其实数据量小都无所谓。但当数据量大的时候，影响就被放大了。如果让数据库来承担这个复杂的关联操作，需要对联接的每个表进行扫描、匹配和组合，消耗大量的CPU和内存资源。让复杂的 关联操作占用了大量的数据库资源，会影响其他查询修改操作。
数据库往往是我们系统的弱点，很多情况下性能瓶颈都在数据库，因此我们需要尽量避免把压力放在数据库上。
所以不推荐多表Join。

41 Mysql 中如何解决深度分页问题

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 再去主键索引查询，速度很快，数据量也小。如果直接扫描主键索引的话，数据量就比较大，因为主键索引包含全部的数据

2）记录 id

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

深度分页

所谓的深度分页是指数据量很大的时候，按照分页访问后面的数据，例如 limit 9999990, 10，这会使得数据库扫描前面的 99999990 条数据，才能得到最终的 10 条数据，大批量的扫描数据会增加数据库的负载，影响性能。

42 如何在 Mysql 中监控和优化慢 sql

可以利用 mysql 自带的 slow_query_log 来监控慢 sql, 它是 mysql 提供的一个日志功能，用于记录执行时间超过特定 阈值的 sql 语句。

对于慢查询，再使用 explain分析执行计划，查看查询的执行顺序，使用的索引，扫描的行数等，以识别潜在的性能瓶颈。优化方案有

• 根据 explain 的结果，检查是否有合适的索引。若缺失索引，则添加 (特别是在 where, join 和 order by 子句中使用的列上)
• 将复杂的JOIN查询拆分成多个简单查询，尽量小表驱动大表
• 避免SELECT*，仅选择需要的字段
• 等等(更多可参考扩展中的SQL调优)

Mysql 慢 sql 配置

在 Mysql 配置文件（通常是 my.cnf 或 my.ini) 中添加或修改以下配置项

[mysqld]
slow_query_log = 1 # 启用慢查询日志
slow_query_lof_file # 指定慢查询日志文件路径
long_query_time = 2.0 # 设置慢查询的阈值时间（单位秒）
log_queries_not_using_indexes = 1 # 记录未使用索引的查询

43 Mysql 中 delete, drop 和 truncate 的区别是什么

1. delete 用于删除行数据，但保留表结构和相关的对象
2. drop 用于完全删除数据库表，包括数据和结构
3. truncate 只删除数据，不会删除表结构和索引等其他结构

性能大小： Drop > truncate > delete

Delete
本质上这个删除其实就是给数据行打个标记，并不实时删除，因此delete之后，空间的大小不会变化。
而且 delete 操作会生成binlog、redolog 和 undolog，所以如果删除全表使用 delete 的话，性能会比较差！但是它可以回滚。
Drop
在InnoDB中，每张表数据内容和索引都存储在一个以.ibd后缀的文件中，drop 就是直接把这个文件给删除了！还有一个.frm 后缀的文件也会被删除，这个文件包含表的元数据和结构定义。文件都删了，所以这个操作无法回滚，表空间会被回收，但是如果表存在系统共享表空间，则不会回收空间。默认创建的表会有独立表空间，把 innodb_file_per_table 的值改为 OFF 后，就会被放到共享表空间中，即统一的 ibdata1文件中。
Truncate
Truncate会对整张表的数据进行删除，且不会记录回滚等日志，所以它无法被回滚。并且主键字段是自增的，使用Truncate删除后自增重新从1开始。

44 Mysql 中 inner join, left join 和 right join 的区别是什么

INNER JOIN:

• 只返回两个表中匹配的行。如果没有匹配，则该行不会出现在结果集中。
• 适用于只关心交集数据的场景。

LEFT JOIN(或LEFT OUTER JOIN）：

• 返回左表中的所有行，即使右表中没有匹配的行。如果右表没有匹配，则结果中的右侧列会显示为NULL。
• 适用于需要保留左表所有数据的场景。

RIGHTJOIN（或RIGHT OUTERJOIN）：

• 返回右表中的所有行，即使左表中没有匹配的行。如果左表没有匹配，则结果中的左侧列会显示为NULL。
• 适用于需要保留右表所有数据的场景。

45 Mysql 中 limit 100000000, 10 和 limit 10的执行速度是否相同？

1）limit 100000000, 10 需要先处理 (通常是读取并跳过) 前 100000000 条记录，然后再获取到需要的10条记录，开销成本很大，因为需要扫描 100000000 数据才能得到后面的 10 条数据，会导致大量的磁盘 I/O

2）limit 10从结果集第一个记录开始扫描直接返回前10条记录

通常面对 limit 100000000,10 这种大分页的情况，可以先使用条件过滤掉，比如使用主键 ID 来进行范围过滤，然后再 limit 10。

select * from uses where id > 100000000 order by id limit = 10;

不过这种方式要运用在有递增关系的列条件上

46 mysql 中 datetime 和 timestamp 类型的区别是什么

存储方式：

• DATETIME：以字符串形式存储，范围为‘1000-01-0100:00:00’到‘9999-12-3123:59:59′，占用8字
• TIMESTAMP:以Unix时间戳形式存储，范围为‘1970-01-0100:00:01’UTC到‘2038-01-1903:14:07’UTC，占用4字
  节。

时区处理：

• DATETIME：不受时区影响，存储的时间是具体的日期和时间，不会进行自动转换。
• TIMESTAMP：受时区影响，存储时会转换为UTC，取出时会根据连接的时区进行转换，适合处理跨时区的数据。

47 数据库的三大范式是什么

第一范式（1NF）：

• 确保每个列的值都是原子值，表中的每个字段只能包含单一的数据项，不允许重复的列和多值字段。

第二范式（2NF）：

• 在满足第一范式的基础上，确保表中的每个非主键字段完全依赖于主键，而不是部分依赖。即，非主键字段必须依赖于整个主键。

第三范式（3NF）：

• 在满足第二范式的基础上，确保非主键字段之间不依赖，即消除传递依赖。所有非主键字段只能依赖于主键，不应相互依赖。

现在业务上的表设计基本都是反范式的。当然不是说完全不尊重范式，而是适当的进行调整。比如业务上经常需要冗余字段，减少连表查询，提升性能，特别是业务量比较大的公司，这种冗余是很有必要的

48 在 mysql 中，你使用过哪些函数

1）字符串函数，用于处理文本数据：

• CONCAT：连接字符串。
• SUBSTRING：提取子字符串。
• LENGTH：返回字符串的长度。
• REPLACE：替换字符串中的子字符串。
• UPPER和LOWER：将字符串转换为大写或小写。
• TRIM：去除字符串两端的空格。
• LEFT和RIGHT：返回字符串左边或右边的字符。

2）数学函数，用于处理数字运算：

• ABS：返回绝对值。
• CEIL和FLOOR：返回大于或等于/小于或等于指定数的最小整数/最大整数。
• MOD：返回除法的余数。
• POWER：返回一个数的指定次幂。

3）日期函数，用于处理日期和时间：

• NOW：返回当前日期和时间。
• DATE_ADD和DATE_SUB：日期加上或减去一个时间间隔。
• DATEDIFF：计算两个日期之间的差异。
• YEAR、MONTH、DAY：提取日期的年份、月份、日期。
• STR_TO_DATE：将字符串转换为日期。

4）聚合函数，用于汇总数据：

• COUNT：计算行数。

• SUM：计算总和。

• AVG：计算平均值。

• MAX和MIN：返回最大值和最小值。

5）条件函数，用于实现条件逻辑：

• IF：条件判断函数。
• IFNULL：返回第一个非NULL值。
• CASE：条件选择函数。

49 Mysql 中 text 类型最大可以存储多长的文本

1. tinytext: 最大长度为 255 字节
2. text: 最大长度为 65535 字节(约 64kb)
3. mediumtext: 16777215(16MB)
4. longtext: 最大长度为 4294967295（4GB)

50 什么是数据库的视图

数据库的视图是一个虚拟表，它并不存储实际的数据，而是通过查询其他表的数据来生成的。视图可以简化复杂的查询，增强数据安全性(限制访问某些数据)以及提供数据的不同表示方式

它可以包含一个或多个表的数据，并且可以对这些数据进行筛选，计算，排序等操作

视图的作用

• **简化复杂查询：**视图可以将复杂的查询封装成一个简单的视图，使得用户在查询数据时更加方便。
• **安全性：**通过视图可以限制用户访问特定的表和列，保护敏感数据。例如，只允许用户查看某些列而不是整个表。
• **数据抽象：**视图提供了一种数据抽象层，用户可以通过视图获取需要的数据，而不必关心底层表的结构和关系。
• **可重用性：**定义一次视图，可以在多个查询中重复使用，减少代码余。

下面是一个实际的视图示例，展示如何创建和使用视图来简化查询。

51 为什么不推荐在 mysql 中直接存储图片，音频，视频等大容量内容

MySQL是关系型数据库，它设计的初衷是高效处理结构化和关系型数据，所以存储大容量的内容本身就不是它的职责所在，因此这方面的能力也不够。
应该将大容量文件存储在文件系统或云服务提供的对象存储服务中，仅在数据库中存储文件的路径或URL即可。

数据库本身性能问题

• 数据库性能：存储和检索大容量的二进制数据（如图片、音频、视频）会显著增加数据库的I/O操作和处理时间，从而影响整体性能。
• 查询性能：大容量的二进制数据会增加数据表的大小，导致查询性能下降。尤其是在涉及表连接或复杂查询时，性能影响更大。

可扩展性问题

文件系统可以通过分布式文件系统（如HadoopHDFS、AmazonS3）更容易地进行扩展，而数据库扩展则复杂得多。

权限控制问题

像文件系统和对象存储系统（如AmazonS3、OSS等）通常提供更灵活的访问控制和权限管理机制，适合处理大容量数据的存储和访问，例如访问的时效性等等，而MySQL不提供这些功能，需要我们应用程序额外开发。

52 相比于 Oracle, Mysql 的优势有哪些

Oracle起步早，结构严谨、高可用、高性能且安全，在传统行业（运输、制造、零售、金融等等）中早早的占据了核心地位。
总结来说Oracle用起来会更省心一些，但是花钱！MySQL相比而言会更操心一些，但是省钱！

53 MySQL中VARCHAR(100)和VARCHAR(10)的区别是什么?

两者的区别就是能存储的字符串长度上限不同，字符数上限是由定义中的括号内的数字决定的。

• VARCHAR(100)最多可以存储100个字符。
• VARCHAR(10)最多可以存储10个字符。

两者存储相同字符串时占用的空间是一样的。除了存储字符本身外，还需要额外的1或2个字节来记录字符串的长度。对于长度小于255的字符串，使用1个字节；对于长度255及以上的字符串，使用2个字节。
虽然存储的空间一样，但是在查询时，即带上SORT、ORDER这些字段时，VARCHAR(10O)字段会使得查询所占用的内存空间更多，因为在排序相关内存分配时，它是按照固定大小的内存块进行保存，VARCHAR(100)的内存块会大于VARCHAR(10)。

54 什么情况下，不推荐为数据库建立索引

1）对于数据量很小的表

• 当表的数据量很小（如几百条记录）时，建立索引并不会显著提高查询性能，反而可能增加管理的复杂性。

  2）频繁更新的表

• 对于频繁进行插入、更新和删除操作的表，索引会导致额外的维护开销，因为每次数据变更时都需要更新索引，这会影响性能。
  3）执行大量的SELECT

• 此时二级索引可能不会显著提升性能，因为需要大量的回表查询，开销大，数据库最终可能会选择走全表扫描。
  4）低选择性字段(高度重复值的列）

• 当索引字段的取值重复度高（如性别字段“男”、“女”），索引的效果不明显，且会增加存储空间的浪费。

• 但是，还有一种场景可以考虑，比如表里任务 status列就2个类型，90%都是1（已完成），10%（待执行）是2，这个场景会频繁查询2（待执行）的任务来执行，此时可以建立索引，毕竟能过滤90%的数据。
  5）低频查询的列

• 对于查询频率极低的字段，建立索引的成本和维护负担可能超过带来的性能提升
  6）长文本字段(非常长的varchar或JSON、BLOB和TEXT类型，这些类型的列通常包含大量数据)

• 数据量大排序时都无法用内存排，只能利用磁盘文件，排序很慢。

• 数据量大，每个页能存放的行数就少，扫描查询可能会涉及大量的I/O。

• 文本字段过大都需要额外blob页存储，每次查询还需要查额外的页，也是随机I/O效率低。

• 这种类型的数据如果有查询需求，不应该放到MySQL中，可以需要采用es等组件来实现查询。

55 Mysql 中 exists 和 in 的区别是什么

基本功能：

• exists 用于判断子查询是否返回任何行，通常用于检查某个条件是否满足
• in 用于检查某个值是否在指定的集合中，可以是一个子查询或静态值列表

性能差异：

• exists 一旦找到匹配行就会立即返回，通常在子查询返回较大数据集时性能更好
• in 通常会评估整个子查询并构建一个值列表，然后再进行匹配，在处理较大数据集时可能性能较差

使用场景

• exists 更适合用于大数据量或复杂逻辑的条件判断
• in 更适合简单的静态值集合或较小的子查询结果

进一步分析 exists 和 in 性能问题

简单总结

• 外层查询表量级小于子查询表，且子表有索引，则用 exists
• 外层查询表量级大于子查询表，且外层有索引，则用 in
• 如果外层和子查询差不多，则都行。

56 write-ahead Logging(WAL)技术，它的优点是什么？mysql 中是否用到 WAL

WAL（Write-AheadLogging）技术是一种数据库事务日志管理技术，它确保在修改真正的数据之前，先将修改记录写入日志。这使得即使系统崩溃，通过日志也能恢复数据。保证了数据的持久性和一致性。
WAL它的核心思想就是先写日志，再写数据，大致执行流程如下：
1）当一个事务开始时，所有对数据库的修改都会先记录到一个日志文件中，而不是直接应用到数据库文件，这些日志记录了数据的变更信息，可以用于恢复数据。
2）当日志记录被安全地写入磁盘后，才会将这些修改应用到数据库文件中。

WAL 优点

• 保持数据一致性：在事务提交前，变更首先记录到日志中，在系统崩溃后，数据库可以通过重做日志中的操作来恢复到崩溃前的状态，确保数据一致性或持久性
• 性能提升：把数据的随机写转换成日志的顺序写，提高了整体性能

Redo Log

在 MySQL InnoDB 存储引擎中，重做日志（Redo Log）就是 WAL 的实现，用于保证事务的持久性和崩溃恢复能力。
InnoDB 重做日志的工作机制如下：
1）当一个事务开始时，所有对数据库的修改首先记录到重做日志缓冲区中。
2）重做日志缓冲区的数据会周期性地刷新到磁盘上的重做日志文件（ib_logfile0和ib_logfile1）。
3）当事务提交时，InnoDB 确保重做日志已写入磁盘，然后将数据页的修改写入数据文件。
4）如果系统崩溃，InnoDB 会在启动时通过重做日志重新应用所有未完成的事务，以恢复数据库到一致状态。

57 你们生产环境的 Mysql 中使用了什么事务隔离级别？为什么

Mysql 数据库的默认隔离级别是 RR (可重复读)，但是很多大公司把隔离级别改成了 RC(读已提交)，主要原因是为了提高并发和降低死锁概率

为了解决幻读的问题 RR 相比 RC 多了间隙锁(gap lock) 和临键锁(next-key lock)。而 RC 中修改数据仅用于行锁，锁定的范围更小，因此相比而言 RC 的开发更高

58 为什么阿里巴巴的 Java手册不推荐使用存储过程

1）可移植性差

• 数据库依赖性：存储过程是在数据库服务器上执行的，通常使用数据库特定的SQL方言和功能，这会导致应用程序的数据库依
  赖性增加，迁移到其他数据库系统时有成本。
• 跨平台问题：不同的数据库系统实现存储过程的方式和支持的功能不完全相同，维护比较复杂。

2）调试困难

• 调试工具有限：相比应用层代码**，数据库层的存储过程缺乏良好的调试工具和环境**。常规的代码调试方法（如设置断点、逐步
  执行）在存储过程中无法直接应用，导致复杂业务场景，不容易定位错误。

3）维护复杂

• 存储过程通常与应用程序代码分离，维护起来需要同时管理数据库层和应用层的逻辑，增加了代码管理的复杂性。

59 如何实现数据库的不停服迁移

迁移想着很简单，不就是把一个库的数据迁移到另一个库吗？
但是实际上有很多细节，在面试中我们可以假装思考下，然后向面试官复述以下几点：

• 首先关注量级，如果是几十万的数据其实直接用代码迁移，简单核对下就结束了。如果数据量大那么才需要好好设计方案。
• 不停服数据迁移需要考虑在线数据的插入和修改，保证数据的一致性。
• 迁移还需要注意回滚，因为一旦发生问题需要及时切换回老库，防止对业务产生影响。

flink-cdc 方案

除了主从同步，代码双写的方案，也可以采用第三方工具。例如 flink-cdc 等工具来进行数据的同步，它的优点方便，且支持异构（比如mysql 同步到pg, es 等等)的数据源

双写方案

大部分数据库迁移都会采用双写方案，例如自建的数据库要迁移到云上的数库这个场景，双写就是同时写入自建的数据库和云上的数据库。

1. 将云上数据库作为自建数据库的从库，进行数据同步(或者可以利用云上能力，比如阿里云的 DTS)
2. 改造业务代码，数据写入操作不仅要写入旧库，同时也要写入新库，这就是所谓的双写，注意这个双写需要加开关，即通过修改配置时打开双写和关闭双写
3. 在业务低峰期，确保数据同步完全一致的时候(即主从不延迟，这个都是有对应的监控的)，关闭同步，同时打开读写开关，此时业务代码读取的还是旧数据库
4. 进行数据核对，数据量很大的场景只能抽样调查（可以利用定时任务写代码进行抽样核对，一旦不一致就告警和记录）
5. 如果确认数据一致，此时可以进行灰度切流，比如1%的用户切到读新的数据库（比如今天访问前1%的用户或者根据用户ID或其他业务字段），如果发现没问题，则可以逐步增加开放的比例，比如5%->20%->50%->100%
6. 继续保留读写，跑个几天（或者更久），确保新库确实没问题了，此时关闭双写，只写新库，这时候迁移就完成了

60 Mysql 数据库的性能优化方法有哪些

SQL 优化

根据慢SQL日志，找出需要优化的一些语句。
常见优化方向：
1）避免SELECT*，只查询必要的字段
2）避免在SQL中进行函数计算等操作，使得无法命中索引
3）避免使用%LIKE，导致全表扫描
4）注意联合索引需满足最左匹配原则
5）不要对无索引字段进行排序操作
6）连表查询需要注意不同字段的字符集是否一致，否则也会导致全表扫描

库表设计优化

1）合理的表结构：比如选择合适的数据类型，例如能用 int 的不要用bigint，还有varchar和 char的选择等等。
2）合理余字段：在适当的情况下进行反规范化设计，冗余部分数据，减少关联查询。
3）索引优化：根据查询频率和条件，创建合适的索引，删除不必要的索引，因为索引的维护也是需要成本的。建议使用EXPLAIN
分析查询执行计划，确认是否用上索引，是否用对索引。
4）分库分表：对于超大规模的数据库系统，可以采用分库分表策略，将数据拆分到多个数据库或表中，提高读写性能和扩展性。

缓冲

数据库始终是有瓶颈的，如果不论怎么优化性能上不去，此时可以考虑用缓存。在部分场景可以使用本地缓存和 Redis 分布式缓存减轻 Mysql 查询压力。需要关注数据库一致性问题

MySQL相关配置优化

可以根据具体的使用场景调整以下参数：

• innodb_buffer_pool_size：增大InnoDB的缓冲池大小，一般设置为物理内存的70-80%。
• query_cache_size：适当调整查询缓存大小。
• max_connections：增加最大连接数。
• table_open_cache：增加打开表的缓存大小。
• thread_cache_size：调整线程缓存大小以减少线程创建的开销。

硬件层面的优化

1）升级硬件：增加服务器的内存、CPU 和存储速度。(现在云厂商都支持不停服直接升级)
2）使用SSD：相比传统的HDD，SSD读取和写入速度更快。

数据库维护

1）定期备份：保证数据安全，防止数据丢失。
2）定期清理：删除不必要的数据和日志，释放存储空间（例如历年的数据可以剥离存档，释放当前表的大小）。
3）重建索引：定期重建索引，保持索引的高效性。
4）分析表和优化表：定期运行ANALYZETABLE和OPTIMIZETABLE，保持表的统计信息更新和碎片整理，使得优化器更加准
确。

62 Mysql 中 InnoDB 存储引擎与 MyISAM 存储引擎的区别是什么

MyISAM

1）MyISAM 是基于 ISAM引擎而来的，支持全文检索，数据压缩，空间函数，不支持事务和行级锁，只有表级别锁，它适用于 OLAP 场景，也就是分析类，基本上都是读取，不会有什么写入动作的场景。

2）MyISAM 的索引也是B+树，只是不像 InnoDB 那种叶子节点会存储完整的数据，MyISAM 的数据是独立于索引单独存储的，所以主键和非主键索引差别不大

3）MyISAM 不支持崩溃后的安全恢复，而 InnoDB 有个 redolog 可以支持安全恢复

4）MyISAM 写入性能差，因为锁的粒度太粗了，不支持行锁，只有表锁，所以写入的时候会对整张表加锁。不过有个并发插入的开关，开启之后当数据中间没有空洞的时候，也就是插入的新数据是从末尾插入时，读取的数据是不会阻塞的

InnoBD (Mysql 默认引擎)

MyISAM 和 InnoDB 适用场景

InnoDB 更适合需要高并发，事务处理和数据完整性保证的场景，例如电商平台，金融系统和社交网络

MyISAM 更适合读操作远多于写操作且对数据完整性要求不高的场景，例如内容管理系统，博客平台和报表管理系统

63 Mysql 的查询优化器如何选择执行计划

1 ) 将 sql 语句解析为解析树

**2）**预处理，包括语法检查，权限验证，查询重写(例如常量表达式计算，子查询展开等)

3）生成多个执行计划，并选择成本最低的执行计划

优化器成本的计算方式

I/O 成本

CPU 成本

数据从磁盘读取到内存后，需要比较、排序等，这些操作需要占用 CPU资源，因此优化器以扫描的行记为成本，一行的成本为 0.2。
因此最终的成本计算就是看扫描 行数*0.2 + 数据长度 / 16 kb=成本
对比所有索引的成本，最终选择最低成本的索引！

扩展查询优化器的优化

1）查询重写

优化器会对查询进行重写，来简化查询动作或消除余查询。
例如：

• 子查询优化：将子查询转换为连接查询，或者将子查询展开（unroll）。
• 常量表达式计算：在预处理阶段计算常量表达式，以简化查询。

2）表连接优化

优化器会评估不同的表连接顺序，并选择成本最低的连接顺序。简单来说优化器会选择行数更少的表优先进行连接，以减少中间结果集的大小。
例如：

• 嵌套循环连接（NestedLoopJoin）：对于小表或带索引的连接使用。
• 哈希连接（HashJoin）：对于大表的连接使用。
• 排序合并连接（Sort-MergeJoin）：对于大表的排序后合并连接使用。

64 什么是数据库的逻辑删除？数据库的物理删除和逻辑删除有什么区别

逻辑删除是一种将数据标记为已删除但实际不会从数据库中移除的删除方式。一般是在表中添加一个表示删除状态的字段，如 is_deleted，默认是 0 表示未删除，1 表示已删除。
物理删除则是直接从数据库中删除记录。一般业务上都是使用逻辑删除，便于后续的数据分析、追溯等。

物理删除的优缺点

优点：

• 节省存储空间：物理删除释放了数据占用的存储空间。

• 查询性能提高：由于数据被彻底删除，查询时不需要过滤已删除的数据。

  缺点：

• 不可恢复：一旦数据被删除，无法恢复，除非有备份。

• 缺乏审计和追溯：无法追溯数据的历史状态和变更记录。

逻辑删除的优缺点

优点：

• 数据可恢复：可以通过更改标记字段的值来恢复数据。
• 支持审计和追溯：保留了数据的历史状态，有助于数据审计和分析。

缺点：

• 占用存储空间：已删除的数据仍然存在，占用存储空间。
• 查询性能影响：查询时需要额外的条件来过滤已删除的数据，可能影响性能。
• 复杂性增加：需要在查询、更新等操作中考虑逻辑删除字段的处理。

逻辑删除与唯一性问题

65 什么是数据库的逻辑外键？数据库的物理外键和逻辑外键各有什么优缺点

逻辑外键是一种在应用程序层面上管理和维护数据完整性的方法，而不是通过数据库本身的外键约束。主要是利用应用程序代码来保证引用的完整性

逻辑外键的优缺点

优点：
灵活性高：应用程序层面控制，可以更灵活地实现复杂的业务逻辑。
性能优化：避免了数据库层面的约束检查，可以在某些情况下提高性能（详细看扩展知识）。
**跨数据库兼容性：**逻辑外键在不同类型的数据库之间更容易迁移。
缺点：
**代码复杂性增加：**需要在应用程序代码中手动实现和维护引用完整性，增加了代码的复杂性和错误的可能性。
**一致性风险：**如果应用程序代码未正确实现引用完整性检查，可能导致数据不一致。
**维护成本高：**逻辑外键需要开发人员持续关注和维护，增加了维护成本。

物理外键的优缺点

优点：
**自动维护引用完整性：**数据库会自动检查和维护外键约束，确保数据的一致性。
**减少应用层复杂性：**开发人员不需要手动管理引用完整性，减少了代码的复杂性和错误的可能性。
数据完整性保障：数据库层面的约束能够更有效地防止非法数据的插入或更新。
缺点：
性能开销：外键约束会增加插入、更新和删除操作的开销，特别是在处理大量数据时。
迁移和复制的复杂性：在进行数据库迁移或复制时，外键约束可能会增加复杂性，需要小心处理。
灵活性较低：物理外键在某些复杂业务逻辑下可能不够灵活，需要更多的手动控制。

public void insertOrder(Order order) {
    if (!customerExists(order.getCustomerId())) {
        trow new IllegalArgumentException("面试鸭用户不存在")
    }
    
    // 插入订单
    orderRepository.save(order);
}

private boolean customerExists(Long customerId) {
    return customerRepository.existsById(customerId);
}

数据库外键业务问题盘点

**1）**无法支持复杂的业务控制

不说其他复杂的情况，就拿删除数据使用逻辑删除的情况，数据库的外键就无法满足级联修改，还是需要开发人员编写业务代码手动控制

**2）**分库分表

数据库的外键约束只能控制一个数据库实例，跨实例的情况下无法满足

数据库外键性能问题盘点

1）级联更新
按照手册举例，如果学生表的 ID 被修改，数据库同时需要改成绩表中的对应的学生 ID，如果还有其他关联表则都需要被修改。
在高并发数据量大的情况下，一次修改会产生意料之外的级联更新使得数据库压力过大，导致系统其他操作数据库的请求阻塞，很可能导致系统全面崩盘。
2）检查维护
还是拿学生成绩举例，因为设置了外键，插入成绩表对应的学生ID时，数据库需要去检查这个学生ID在对应的学生表是否存在，来保证数据外键完整性约束。
除了插入，删除、更新相关外键，数据库都需要去检查数据的完整性，这就产生了性能开销。
3）锁问题
如果多个事务并发修改学生表，那么对应锁定的数据涉及的成绩表也需要被锁定，同理并发修改成绩表，那么对应的学生的表的数据也需要被锁定。
关联外键越多锁定的数据也就越多。锁的数据多除了性能问题，还可能会带来死锁的问题。

66 Mysql 事务的二阶段提交是什么

MySQL 事务的二阶段提交是指在 MySQL 中，为了确保 redo log（重做日志）和 bin log（二进制日志）之间的一致性，使用的一种机制。MySQL 通过二阶段提交来保证在 crash recovery（崩溃恢复）时，不会出现数据丢失或数据不一致的情况。

二阶段提交的两个阶段：

·准备阶段（Prepare Phase）：在事务提交时，MySQL的 InnoDB 引擎会先写入 redo log，并将其状态标记为 prepare，表
示事务已经准备提交但还未真正完成。此时的 redolog 是预提交状态，还未标记为完成提交。
·提交阶段（Commit Phase）：当 redolog 的状态变为 prepare 后，MySQL Server会写入 binlog（记录用户的DML操
作）。binlog 写入成功后，MySQL 会通知 InnoDB，将 redo log 状态改为 commit，完成整个事务的提交过程。

扩展知识

binlog和redolog的区别

redo log:
·重做日志（redo log）是 InnoDB 引擎内部的事务日志，用于记录数据页的物理修改。
·redo log 是固定大小的环形日志，主要用于崩溃恢复。它可以帮助InnoDB在崩溃后通过日志重做未写入数据页的数据修改，
从而确保数据的持久性。
bin log:
·二进制日志（bin log）是 MySQL Server层的日志，用于记录所有数据库的修改操作，包括增删改操作（DML）以及表结构的
修改（DDL）。
·bin log 是追加写入的日志文件，主要用于数据恢复、主从复制、数据备份等场景。它记录的是SQL语句的逻辑修改操作，而非数据页的物理修改。

为什么需要二阶段提交

如果没有二阶段提交：关于这两个日志，要么就是先写完 redo log, 再写 binlog 或者先写 binlog 再写 redo log. 我们呢来分析一下会产生什么后果

1）先写完 redo log, 再写 binlog

写完 redo log 后，Mysql 异常宕机，binlog 还未写入数据。重启后 redo log 记录了，因此可以从 redo log 恢复事务的修改，但是 binlog 并没有本次事务提交的数据。后续通过 binlog 恢复时，本次事务的修改就丢了

2） 先写完 binlog, 再写 redo log

写完 binlog 后， Mysql 异常宕机， redo log 还未写入数据。重启后 因为 redo log 中没有记录，所以无法恢复本次事务的修改，但是 binlog 记录了本次事务提交的数据。后续通过 binlog 恢复的时候，本次事务的修改可以复原，但是这和原库的数据不一致了

如有有二阶段提交，MySQL异常宕机恢复后如何保证数据一致呢？
1）redo log 处于prepare阶段，bin log还未写入，此时MySQL异常宕机。
这个阶段很好理解，由于redo log 还未commit，所以异常恢复后，redolog 中记录的数据也不作数，binlog 内也没有记录数
据，此时数据是一致的。
1）redo log 处于 prepare 阶段，bin log 已写入，但 redo log 还未commit，此时 MysQL异常宕机。
此时仅需对比 redo log 中 prepare的数据和 binlog 中的数据是都一致即可。如果一致，则提交事务。不一致，则回滚事务。

组提交

组提交（Group Commit）是 InnoDB 引擎中的一种优化技术，主要用于优化 redo log 的写入过程。它通过将多个事务的redo log 刷盘操作合并为一次磁盘同步操作，从而减少 fsync 的调用次数，提高 MySQL 在高并发环境下的事务提交效率。
具体工作原理如下：

1. 当多个事务同时提交时，InnoDB 会先将这些事务的redolog 记录写入到日志缓冲区中，但不会立即将每个事务的日志同步到磁盘。
2. 当组提交条件满足时（如等待时间到达、日志缓冲区达到一定大小等），InnoDB 会将多个事务的日志一次性进行磁盘同步（fsync），将这些事务的 redolog 一起持久化到磁盘。

通过组提交的方式，InnoDB 能够减少多次单独的 fsync 调用，从而降低磁盘I/O的压力。
组提交除了能应用到 redo log 中，实际 binlog 的刷盘也可以组提交，但是效果一般，因为它的 write 和 fsync 之间的间隔比较短。
不过可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现 binlog 组提交的效果。

• binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用 fsync;

• binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。

• 除此之外还有—个参数也需要关注一下，即innodb_flush_log_at_trx_commit。这个参数控制了redolog的刷盘策略，是影响组提交效果的重要配置：

• innodb_flush_log_at_trx_commit=1：每个事务提交时都立即将 redo log 刷新到磁盘，最安全，但组提交效果较弱

• innodb_flush_1og_at_trx_commit=2：每个事务提交时仅将日志写入操作系统缓存，定期刷新到磁盘，组提交效果更明显。

• innodb_flush_log_at_trx_commit=e：不在事务提交时刷盘，数据可能丢失，但可以最大化组提交的效果。

67 sql 的执行顺序是什么

68 Mysql 三层 B+ 树能存多少数据

Mysql 的 InnoDB 存储引擎中，B+ 树默认数据页大小为 16kB

参数：

·每个节点页大小为16KB（即16384字节）。
·假设每个数据记录的主键和数据大小为1KB（一般会比这个小，但这里取整方便计算）。
·每个内部节点（非叶子节点）存储的是指向子节点的指针和索引键。

三层 B+ 树的存储计算：

·叶子节点：第三层为叶子节点，每个叶子节点页可存储 16条数据记录（16KB÷1KB）。
·第二层（中间层）：假设每个 指针6字节 和索引键（一般为bigint）的大小为 8字节，那么每个中间节点页可以指向1170个
叶子节点，16KB转成字节需要乘以1024，因此（16＊1024÷（6+8）字节）。
·第一层（根节点）：根节点可以指向1170个中间节点。
由此，三层B+树大致能存储的数据总量为：1170117016=21902400，一棵三层的B+树在MySQL中可以存储大约2000
万条记录。

69 Mysql 在设计表(建表)时需要注意什么

1）选择合适的数据类型：

为字段选择合适的数据类型可以有效减少存储空间，并提高查询效率。例如：
·使用INT 而不是BIGINT，前提是如果数据不会超出INT范围。
·使用VARCHAR而不是TEXT，如果字段长度比较短且可变。
·使用DATE、DATETIME 或TIMESTAMP而不是VARCHAR 来存储日期时间信息。
2）主键与唯一约束
·主键是表的唯一标识符，每个表应该有一个主键。
·如果需要确保某些字段唯一性，可以使用唯一约束（UNIQUE）。
3）索引的设计：索引是提高查询性能的关键。但是设计时应避免过多索引，以免对写操作造成负担。
·对于经常查询的字段（如WHERE、JOIN、ORDER BY中使用的字段）应该创建索引l。
·考虑复合索引，可以将多个列组成一个索引，优化复合查询性能。
·避免在低基数列（如布尔值、性别字段）上创建索引，因为它们不会带来太大的查询性能提升。
4）表的范式化（Normalization）：

规范化（通常遵循到3NF）有助于消除数据余，提高数据一致性，避免数据更新异常。但
在某些场景下，可以选择一定的反规范化来提高查询性能。例如：
·冗余一些常用字段，避免关联表查询，提升性能。
·预留一些扩展字段，例如 extendld 等，便于后续扩展。
·部分格式不可控字段可以设计为json格式，防止频繁变更表结构。
5）外键与关联设计
·虽然在关系型数据的场景下，使用外键（FOREIGNKEY）可以确保数据一致性。但是外键约束会影响性能，所以一般现在互联网公司不使用外键。

70 mysql 插入一条 sql 语句， redo log 记录的是什么

因为 redolog 是物理日志，记录“某页（Page）某位置的数据被修改为某值”。它不记录逻辑操作（如“插入一行"），而是直接记录对页的变更。所以在插入操作中，redolog 记录的是事务在数据页上的修改数据页的插入点、记录的偏移量和插入的实际数据并更新页目录、页头等元数据。

插入操作redolog具体执行流程

1. 修改缓冲页（BufferPool）：数据先写入内存中的缓冲池，而不是直接写入磁盘。
2. 生成redolog：同时生成一条redolog，记录插入对数据页的物理修改细节。
3. 日志先行（Write-AheadLogging,WAL）：redolog先被写入磁盘上的redolog文件。