【MySQL00】【 杂七杂八】

一、前言

最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》，后续会随机将书中部分内容记录下来作为学习笔记，部分内容经过个人删改，因此可能存在错误，如想详细了解相关内容强烈推荐阅读相关书籍。

---

系列文章内容目录：

1. 【MySQL00】【 杂七杂八】
2. 【MySQL01】【 Explain 命令详解】
3. 【MySQL02】【 InnoDB 记录存储结构】
4. 【MySQL03】【 Buffer Pool】
5. 【MySQL04】【 redo 日志】
6. 【MySQL05】【 undo 日志】
7. 【MySQL06】【MVCC】
8. 【MySQL07】【锁】
9. 【MySQL08】【死锁】

---

本篇作为一些补充性内容，会夹杂各方各面的内容，用于记录。

二、MySQL 文件

MySQL数据库和InnoDB存储引擎表的各种类型文件，这些文件有以下这些：

1. 参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
2. 日志文件：用来记录 MySQL实例对某种条件做出响应时写人的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
3. socket 文件：当用 UNIX域套接字方式进行连接时需要的文件。
4. pid 文件：MySQL实例的进程ID文件。
5. MySQL 表结构文件：用来存放MySQL表结构定义文件。
6. 存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。本篇主要介绍与InnoDB有关的存储引擎文件。

1. 参数文件

MySQL 中的参数可以分为 动态参数 和 静态参数 两类。动态参数意味着可以在 MySQL实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读(readonly)的。动态变量可以通过SET命令对动态的参数值进行修改，Set 语法如下：

这里可以看到 global和 session 关键字，它们表明该参数的修改是基于当前会话还是整个实例的生命周期。有些动态参数只能在会话中进行修改，如 autocommit；而有些参数修改完后，在整个实例生命周期中都会生效，如 binlog_cachesize；而有些参数既可以在会话中又可以在整个实例的生命周期内生效，如 readbuffersize。

2. 日志文件

日志文件记录了影响 MySQL数据库的各种类型活动。MySQL数据库中常见的日志文件有:

1. 错误日志（error log）：错误日志文件对 MySQL的启动、运行、关闭过程进行了记录。如下可以定位错误日志目录：

   mysql> show variables like 'log_error';
   +---------------+-----------------------+
   | Variable_name | Value                 |
   +---------------+-----------------------+
   | log_error     | .\LAPTOP-5F5UIHVC.err |
   +---------------+-----------------------+
   1 row in set (0.07 sec)
   

2. 慢查询日志（show query log）：MySQL 会将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。默认情况下 MySQL并不启动慢查询日志功能，相关参数如下：

   # 查看是否启用 慢查询日志
   mysql> show variables like 'slow_query_log';
   +----------------+-------+
   | Variable_name  | Value |
   +----------------+-------+
   | slow_query_log | ON    |
   +----------------+-------+
   1 row in set, 1 warning (0.00 sec)# 慢查询日志条件，查询时间 大于 10s 才会被计入慢查询日志
   mysql> show variables like 'long_query_time';
   +-----------------+-----------+
   | Variable_name   | Value     |
   +-----------------+-----------+
   | long_query_time | 10.000000 |
   +-----------------+-----------+
   1 row in set, 1 warning (0.00 sec)
   

   MySQL 5.1 版本后慢查询日志会放入 mysql 架构下的 slow_log 表中，可以通过该表查看慢日志信息。

3. 查询日志（log）：查询日志记录了所有对 MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为:主机名.log

4. 二进制日志（binlog）：二进制日志记录了对 MySQL,数据库执行更改的所有操作，但是不包括 SELECT和 SHOW 这类操作，因为这类操作对数据本身并没有修改。但若是其他操作即使本身并没有导致数据库发生变化，那么该操作可能也会写人二进制日志，比如 UPDATE 或 DELETE 语句即使没有作用于任匹配何记录，也会被记录。( 查看二进制文件需要通过 MySQL 提供的工具 mysqlbinlog 工具)

   通过配置参数 log-bin[=name]可以启动二进制日志。如果不指定 name,则默认进制日志文件名为主机名，后缀名为二进制日志的序列号，所在路径为数据库所在目录，如下:

   mysql> show variables like 'datadir';
   +---------------+---------------------------------------------+
   | Variable_name | Value                                       |
   +---------------+---------------------------------------------+
   | datadir       | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
   +---------------+---------------------------------------------+
   1 row in set, 1 warning (0.00 sec)
   

   从 MySQL 5.1 版本开始，二进制日志文件增加了 binlog_format 参数，它影响了记录二进制日志的格式。在 MySQL5.1版本之前，没有这个参数,所有二进制文件的格式都是基于SQL语句(statement)级别的，binlog_format 可设的值有三种：

   • STATEMENT：和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑 SOL 语句
   • ROW：二进制日志记录的不再是简单的 SQL语句了，而是记录表的行更改情况。如果设置了 binlogformat 为ROW，可以将InnoDB的事务隔离基本设为 READ COMMITTED，以获得更好的并发性。
   • MIXED：在MIXED格式下，MySQL默认采用 STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用 ROW格式，情况如下：

     • 表的存储引擎为 NDB，这时对表的 DML操作都会以 ROW 格式记录。
     • 使用了 UUIDO、USERO、CURRENT USERO、FOUND ROWSO、ROW_COUNTO等不确定函数。
     • 使用了 INSERT DELAY 语句。
     • 使用了用户定义函数(UDF)。
     • 使用了临时表(temporary table)。

3. 套接字文件

在 UNIX系统下本地连接MySQL可以采用 UNIX域套接字方式，这种方式需要一个套接字(socket)文件。套接字文件可由参数socket控制。一般在/tmp目录下，名为 mysql.sock。如下：

mysql> show variables like 'socket';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| socket        | MySQL |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

4. pid 文件

当 MySQL实例启动时，会将自己的进程ID写入一个文件中–该文件即为 pid 文件。该文件可由参数 pid 6le控制，默认位于数据库目录下，文件名为主机名.pid， 如下：

mysql> show variables like 'pid_file';
+---------------+---------------------------------------------------------+
| Variable_name | Value                                                   |
+---------------+---------------------------------------------------------+
| pid_file      | C:\ProgramData\MySQL\MySQL Server 5.7\Data\KingFish.pid |
+---------------+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

5. 表结构定义文件

因为 MySQL插件式存储引擎的体系结构的关系，MySQL数据的存储是根据表进行的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL都有一个以 frm 为后缀名的文件，这个文件记录了该表的表结构定义。

使用 InnoDB 存储引擎的表底层会对应2个文件在文件夹中进行数据存储，如下：

• .frm 文件（frame）存储表结构。
• .ibd 文件（InnoDB Data）存储表索引+数据。

使用 MyISAM 存储引擎的表底层会对应2个文件在文件夹中进行数据存储。如下：

• .frm 文件（frame）存储表结构。
• .MYD 文件（MY Data）存储表数据。
• .MYI 文件（MY Index）存储表索引。

6. InnoDB 存储引擎文件

InnoDB 存储引擎文件主要包括 表空间文件和重做日志文件，该内容详参【MySQL04】【 redo 日志】。

二、B+Tree 索引排序

在一般情况下，在需要排序的时候我们只能将记录加载到内存中然后再通过一些排序算法在内存中进行排序。有时候查询的结果集可能太大导致无法再内存中进行排序，此时就需要借助磁盘存放中间结果，在排序操作完成后再把排好序的结果集返回给客户端。而在 MySQL 中，这种在内存或磁盘中进行排序的方式统称为文件排序（filesort），但是如果 order by 字句中使用了索引列，就可能省去在内存或磁盘中排序的步骤。

通过 explain 命令 的 Extra 列可以看到是否使用了文件排序。如下：

mysql> explain select * from t1 order by c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.04 sec)

注意如下使用索引排序失效的情况：

1. 使用联合索引进行排序时也需要遵循最左匹配原则。
2. 使用联合索引进行排序时 ASC、DESC 混用时无法使用索引进行排序。
3. 排序列包含多个非同一索引的列也无法使用索引进行排序。
4. 查询列和排序列并不是同一索引列也会导致无法使用索引进行排序。
5. 排序列不能被函数修饰。

三、InnoDB 关键特性

1. 插入缓冲

1.1 Insert Buffer 和 Change Buffer

在 InnoDB 中 主键是行唯一标识符，通常数据按照主键递增顺序插入，因此插入聚簇索引一般是顺序的，不需要磁盘随机读写。并非所有的都是顺序（如果主键用 UUID 或者自己手动插入了指定的id值都不是顺序的）。而对于非聚簇索引来说，插入的顺序性则无法得到保证，如果每次数据更新或插入都随之更新辅助索引，因为辅助索引的离散性，效率会很低。

因此 InnoDB 设计了 Insert Buffer 来处理这种情况：对 辅助索引 的插入或更新操作不是每一次直接插入到索引页的， 先判断插入的辅助索引页是否在缓冲池中，若在直接插入，否则先放入到一个 Insert Buffer 对象中，好似欺骗数据库这个辅助索引已经插入到叶子节点， 再以一定频率和情况进行 Insert Buffer 和 辅助索引页子节点的合并操作，这时候通常可以将多个插入操作合并到一个操作中（因为在一个索引页中），大大提高了辅助索引的插入性能。需要注意Insert Buffer 不仅仅存在在缓冲池中，在物理页（磁盘）上也存在。

Insert Buffer 的实现是 B+Tree ，MySQL 4.1 前每个表有一颗 Insert Buffer B+Tree， MySQL 4.1后公用一个 Insert Buffer B+Tree，存放在共享表空间中，默认 ibdata1中。

在 InnoDB 1.0.x 版本开始引入了Change Buffer，Change Buffer可以认为是 Insert Buffer 升级版。从这个版本开始，InnoDB 可以对 DML 操作都进行缓冲 （Insert Buffer、Delete Buffer、Purge Buffer）。

使用 Insert Buffer 和 Change Buffer 需要满足下面两个条件：

1. 索引是辅助索引
2. 索引不是唯一的 : 因为在插入缓冲时，数据库并不去查找索引页来判断插入记录的唯一性，如果要求唯一则DB需要查找必定要去离散读取确定数据唯一性，这样 插入缓冲就失去了意义。

通过 SHOW ENGINE INNODB STATUS 命令可以查看 Change Buffer 的信息，如下：

... 忽略部分内容
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
# size 代表已经合并记录页的数量, free list len 代表空闲列表的长度, seg size 代表当前 insert buffer的大小为 194 * 16KB,  merges 代表合并的次数，也就是实际读取页的次数。
Ibuf: size 1, free list len 192, seg size 194, 13726 merges
# 合并的具体操作次数
merged operations:insert 4582, delete mark 2511869, delete 1740
discarded operations:insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 77 buffer(s)
Hash table size 276707, node heap has 515 buffer(s)
Hash table size 276707, node heap has 149 buffer(s)
Hash table size 276707, node heap has 101 buffer(s)
Hash table size 276707, node heap has 65 buffer(s)
Hash table size 276707, node heap has 43 buffer(s)
Hash table size 276707, node heap has 83 buffer(s)
Hash table size 276707, node heap has 642 buffer(s)
993.49 hash searches/s, 54.42 non-hash searches/s
---
LOG
---
... 忽略部分内容

1.1 缓冲合并

Insert Buffer B+Tree 合并到 缓冲池中的场景 （这里要注意，这里会将Insert Buffer B+Tree 的数据从磁盘合并到缓冲池中，而缓冲池会根据上面的逻辑进行自动刷新到磁盘）：

1. 辅助索引页被读取到缓冲池时 ： 如当执行Select查询时，需要检查 Insert Buffer Bitmap 页确认该辅助索引页是否有记录存放于 Insert Buffer B+Tree 中，有则将 Insert Buffer B+Tree 中该页的记录插入到该辅助索引页中。
2. Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时 ： Insert Buffer Bitmap 至少保证要有 1/32 页的空间，若插入辅助索引记录时检测到插入记录后的可用空间小于 1/32 页，则会强制进行一个合并操作，即强制读取辅助索引页，将 Insert Buffer B+Tree 中该页的记录及待插入的记录插入到辅助索引页中。
3. Master Thread ：主线程每秒或每十秒进行一次 Merge Insert Buffer操作，不同的是每次进行 merge 操作的页的数量不同。

2. 两次写

Insert Buffer 是性能提升， double write 则是提高数据页的可靠性。

当数据库宕机时，可能 InnoDB 正在写入某个页到表中，而这个页仅写了部分，比如 16KB 的页，只写了前 4KB，之后就发生了宕机，这种情况被称为部分写失效（partial page write），这种情况下，即使通过重做日志页并不一定能恢复数据，因为重做日志时对页的物理操作，如果页本身已经损坏，再重做也无意义。这就是说，在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页再进行重做，即 double write。

double write 由两部分组成，一部分是 内存中的 doublewrite buffer，另一部分是磁盘上共享表空间中连续的128个页（即两个区），大小都是2MB。InnoDB 在对缓冲池的脏页进行刷新时并不直接写磁盘，而是会先通过 memcpy 函数将脏页复制到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次，每次1 MB顺序写入到共享表空间的磁盘上，然后马上调用 fsync 函数同步磁盘，避免缓冲写带来的问题。因为这个过程 doublewrite 是连续的，因此写入时顺序写入，开销不大，在完成 doublewrite 页的写入后，再将 doublewrite buffer 中的页写入各个表空间文件中，此时的写是离散的。

通过两次写机制，当数据库在页写入一半是发生宕机时，则在恢复过程中可以先从 共享表空间中 的 doublewrite 中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

2. 自适应哈希索引

InnoDB 会监控对表上各索引页的查询，如果判断建立哈希索引可以提升速度（B+Tree 树的高度一般是 3-4层，所以需要3-4次查询，而哈希一般情况下时间复杂度为 O(1)，只需要一次查询），则会建立哈希索引，成为自适应哈希索引（Adaptive Hash Index，AHI）。

AHI 是通过缓冲池的 B+Tree 页构建而成，因此建立速度很快，不需要对全表构建哈希。这里的哈希索引是针对热点页建立的，其建立是存在如下要求如下：

1. 对于建立索引的页的连续访问模式 (也就是查询的条件) 必须是一样的。如对于 (a,b) 的联合索引页，其访问模式可以是下面的情况：

   • where a = xxx
   • where a = xxx and b = yyy

   如果交替进行上述两种查询，则不会对该页构建 AHI

2. 以该模式访问了 100次

3. 页通过该模式访问了 N 次，其中 N = 页中记录* (1/16)

3. 异步IO

AIO （Async IO），AIO 除了可并发外，还可以进行 IO Merge 操作，如用户访问页（space, page_no） 为（8，6）、（8，7）、（8，8） 每个页大小为 16KB，同步IO需要进行三次 IO，而 AIO则会通过 （space, page_no)知道这三个页是连续的，AIO 底层会发起一个IO 从求，读取从(8,6) 开始读取 48KB 的页。在 InnoDB 中 read ahead方式的读取、脏页的刷新，即磁盘写入操作全都是由 AIO 完成。

4. 刷新邻接页

Flush Neighbor Page ，其工作原理是 当刷新一个脏页时， InnoDB 会检测该页所在区(extent)的所有页，如果是脏页则一起刷新，这么做的好处是可以通过 AIO 将多个 IO 写入合并为一个 IO 操作。

四、页合并与页分裂

页可以空或者填充满（100%），行记录会按照主键顺序来排列。例如在使用AUTO_INCREMENT时，你会有顺序的ID 1、2、3、4等
页还有另一个重要的属性：MERGE_THRESHOLD。该参数的默认值是50%页的大小，它在InnoDB的合并操作中扮演了很重要的角色。
当你插入数据时，如果数据（大小）能够放的进页中的话，那他们是按顺序将页填满的。

若当前页满，则下一行记录会被插入下一页（NEXT）中。

1. 页合并

页合并是指将两个相邻的索引页面合并成一个更大的页面，减少b+树的层级，提高查询性能。

在InnoDB 中，记录的删除会经历下面两个阶段，所使用的空间不会被回收，而是被标记可重用(详参【MySQL05】【 undo 日志】）

1. delete mark 阶段 : 将记录的 deleted_flag 标志位置为 1，同时修改记录的 trx_id、roll_pointer 的值。在删除语句所在的事务提交之前，被删除的记录一直都处于这种中间状态。
2. purge 阶段：当删除该语句所在的事务提交之后，会有专门的线程来将记录真正的删除掉，这里所谓真正的删除就是将该记录从正常链表中移除，并且加到垃圾链表中（这里是加入到链表的头节点，并修改 PAGE_FREE 指向新删除的记录），除此之外还会修改一些页面属性。

因此在 InnoDB中，当索引页面中的索引记录被删除后，页面可能会变得过于稀疏，这时为了节省空间和提高性能，可能会触发页合并操作。

2. 页分离

页分裂是指将该页面中的一部分索引记录移动到另一个新的页面中，从而为新纪录腾出空间，这样可以保持b+树的平衡和性能。

当我们使用 UUID 作为主键 或指定主键插入记录时， 记录的插入可能是乱序的，这就会导致插入到 B+Tree 时可能是无序的，因此新的记录也可能会插入到老的 B+tree 节点中，如果老的 B+Tree 节点 没有足够的空间分配，则会进行页分裂操作，即会创建一个新的叶子节点，并将老叶子节点的部分记录移动到新的节点中，并将新节点插入到老叶子节点中。

如 B+Tree 存在两个相邻的叶子节点 A，B，并且两个页面都已经没有剩余空间，A，B 节点之间通过双向链表连接，即 A <—> B。A 节点中保存主键为 1，2，3 的记录， B 节点中保存主键为 5，6，7的记录，此时如果新插入一条 主键为 4 的记录，那么该条记录应该保存在A 节点尾部或B节点头部的位置，但是由于 A，B节点没有多余的存储空间，此时会触发页分裂，即会重新创建一个叶子节点 C，并将 A，B 节点中的部分记录移动到 C 节点中，并且 C 节点会插入到 A，B节点中。此时 A、B、C 三个节点的关联关系是 A <—> C <—> B

3. 危害和避免方式

3.1 危害

1. 页分裂和页合并涉及大量数据移动和重组，频繁进行这些操作会增加数据库的消耗，影响数据库整体性能。
2. 页分裂和页合并可能导致b+树索引结构频繁调整，这会影响插入和删除的性能。

3.2 避免方式

1. 主键自增，可以很大程度上避免页分裂。
2. 使用批量插入代替单条插入，这样可以减少页分裂的次数。
3. 频繁物理删除可能导致页面过于稀疏引起页合并，所以一般建议使用逻辑删除。

五、InnoDB 的主键生成策略

在 InnoDB 中每张表都有个主键，如果在建表时没有显示定义主键，在按照下面方式定义：

1. 判断表中是否有非空的唯一索引，如果有，则该列为主键。
2. 当表中有多个非空唯一索引时， InnoDB会选择建表时的第一个定义的非空唯一索引作为主键，这里需要注意：主键的选择是根据定义索引的顺序，而非建表时列的顺序。
3. 如果不存在非空唯一索引，则使用 row_id 作为主键。(row_id 是 InnoDB 为每行记录生成的隐藏字段)

---

这里介绍下 row_id 的赋值方式：

1. 服务器会在内存中维护一个全局变量，每当向这个包含 row_id 隐藏列的表中插入一条记录时，就会把这个全局变量的值当做新纪录的 row_id 列的值，并且把这个全局变量自增1。
2. 每当这个全局变量的值是 256 的倍数是，就会将该变量刷新到系统表空间页号为7的页面中名为 Max Row ID 的属性中。之所以不是每次自增该全局变量就刷新到磁盘是为了避免频繁刷盘
3. 当系统启动时，会将这个 Max Row ID 属性加载到内存中，并将该值加上 256 之后赋值给前面提到的全局变量（因为在上次系统关机后，最新的全局变量值可能还没刷新到磁盘）。

六、参考内容

https://blog.csdn.net/csdn_life18/article/details/135125100
https://blog.csdn.net/m0_61505483/article/details/139169842