MySql一些有用的知识点[2]

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 本文字数： 18k 阅读时长 ≈ 1:05

摘要

  • MySql知识点介绍: binlog相关、事务及其ACID属性、并发事务处理带来的问题、事务隔离级别、MVCC多版本并发控制机制、InnoDB Buffer Pool、MyISAM Key Buffer、InnoDB记录存储结构和索引页结构、InnoDB表空间(TableSpace)、InnoDB元数据表、redo log 和 undo log、数据库锁、慢查询，等等

  • 本文基于mysql-8.0.30，https://dev.mysql.com/doc/refman/8.0/en/

binlog相关

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select）,其以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的，所以binlog内记录的是事务提交成功后的内容。

  • 查看binlog状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# 查看bin‐log是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------------------+
| Variable_name                   | Value                                              |
+---------------------------------+----------------------------------------------------+
| log_bin                         | ON                                                 |
| log_bin_basename                | /usr/local/soft/mysql8/datas/mysql/mysql-bin       |
| log_bin_index                   | /usr/local/soft/mysql8/datas/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                                |
| log_bin_use_v1_row_events       | OFF                                                |
| sql_log_bin                     | ON                                                 |
+---------------------------------+----------------------------------------------------+

# 查看最后一个bin‐log日志的相关信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |   138605 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# 会多一个最新的bin‐log日志
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 清空所有的bin‐log日志
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

  • binlog数据恢复

1
2
# 查看binlog日志，重点关注begin和commit之间的内容，
mysqlbinlog --no-defaults /usr/local/soft/mysql8/datas/mysql/mysql-bin.000001

如下为查看binlog日志的内容片段，end_log_pos后面的就是Position的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN
/*!*/;
# at 311
#220928  8:22:15 server id 3306  end_log_pos 367 CRC32 0x4f3bcf2a 	Table_map: `test`.`film` mapped to number 186
# at 367
#220928  8:22:15 server id 3306  end_log_pos 412 CRC32 0x3fc87530 	Write_rows: table id 186 flags: STMT_END_F

BINLOG '
NwQ0YxPqDAAAOAAAAG8BAAAAALoAAAAAAAEABHRlc3QABGZpbG0AAgMPAh4AAgEBAAIBISrPO08=
NwQ0Yx7qDAAALQAAAJwBAAAAALoAAAAAAAEAAgAC/wAEAAAABHRlc3Qwdcg/
'/*!*/;
# at 412
#220928  8:22:15 server id 3306  end_log_pos 443 CRC32 0x0712832c 	Xid = 17662
COMMIT/*!*/;

常用的恢复方法

1
2
3
4
5
6
7
8
# 恢复全部数据
mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p test(数据库名)

# 恢复指定位置数据
mysqlbinlog --no-defaults --start-position="367" --stop-position="443" mysql-bin.000001 | mysql -uroot -p test(数据库)

# 恢复指定时间段数据
mysqlbinlog --no-defaults --stop-date= "2022‐09‐28 08:22:15" --start-date= "2022‐09‐28 08:22:15" mysql-bin.000001 | mysql -uroot -p test(数据库)

一个数据恢复的小示例

  • 假设数据库每天都有全量备份

1
mysqldump -uroot -ppassword -B dbName > $bakpath/dbName_$(date +%F).sql

  • 突然某天数据库出现异常，比如关闭后无法重启，此时可以通过全量备份和binlog日志进行数据恢复

  • 先将binlog日志备份

1
cp /usr/local/soft/mysql8/datas/mysql/mysql-bin.* $bakpath/

  • 清空mysql的datadir目录

1
rm -rf /usr/local/soft/mysql8/datas/mysql/*

  • 重新初始化数据库并启动

1
2
mysqld --user=mysql --initialize-insecure
systemctl start mysqld

  • 导入全量备份，假设查看备份完成时间是 2022-11-10 12:30:30

1
mysql < $bakpath/dbName_$(date +%F).sql

  • 导入binlog数据

1
2
# 这里就从备份完成时间开始导入，实际上这个时间并不准确，因为备份文件中并没有记录最后的position，所以很难比较出准确的时间或者position
mysqlbinlog --no-defaults --start-date="2022-11-10 12:30:30" $bakpath/mysql-bin.* | mysql dbName

注意
如果数据库开启了GTID，需要先关闭再导入binlog数据，否则不能导入binlog数据
临时关闭方式: 更改 GTID_MODE 状态顺序为 ON<->ON_PERMISSIVE<->OFF_PERMISSIVE<->OFF ，需要按照顺序依次改变。

1
2
3
4
5
6
7
8
# 查看 GTID_MODE 当前状态
show global variables like 'gtid_mode';
# 修改GTID_MODE 状态为 ON_PERMISSIVE
set @@GLOBAL.GTID_MODE=ON_PERMISSIVE;
# 修改GTID_MODE 状态为 OFF_PERMISSIVE
set @@GLOBAL.GTID_MODE=OFF_PERMISSIVE;
# 修改GTID_MODE 状态为 OFF
set @@GLOBAL.GTID_MODE=OFF;

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。

  • 原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。

  • 一致性(Consistent) ：指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。事务内查询的数据在当前事务内必须保持一致而不会受到其它事务对数据修改的影响。

  • 隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

  • 持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

提示
总的来说，MySQL中事务的原子性是通过undo log来实现的，事务的持久性性是通过redo log来实现的，事务的隔离性是通过读写锁+MVCC来实现的。事务的一致性通过原子性、隔离性、持久性来保证。

也就是说 ACID 四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现 AID 三大特性，才有可能实现一致性。

同时一致性也需要应用程序的支持，应用程序在事务里故意写出违反约束的代码，一致性还是无法保证的，例如，转账代码里从 A 账户扣钱而不给 B 账户加钱，那一致性还是无法保证。

在事务的具体实现机制上，MySQL采用的是WAL(Write-ahead logging，预写式日志)机制来实现的。这也是是当今的主流方案。

在使用 WAL 的系统中，所有的修改都先被写入到日志中，然后再被应用到系统中。通常包含 redo 和 undo 两部分信息。关于redo日志 和 undo日志，下文有介绍。

  • 开启只读事务：START TRANSACTION READ ONLY

    • 在只读事务中不可以对普通的表（其他事务也能访问到的表）进行增、删、改操作，但可以对用户临时表做增、删、改操作。
    • 对于只读事务来说，只有在它第一次对某个用户创建的临时表CREATE TEMPORARY TABLE执行增、删、改操作时才会为这个事务分配一个事务id，否则的话是不分配事务id。

  • 开启读写事务：START TRANSACTION READ WRITE或者 BEGIN、START TRANSACTION

    • 在读写事务中可以对表执行增删改查操作。
    • 对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务id，否则的话也是不分配事务id的
    • 有的时候虽然我们开启了一个读写事务，但是在这个事务中全是查询语句，并没有执行增、删、改的语句，那也就意味着这个事务并不会被分配一个事务id

事务Id分配策略
服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务id时，就会把该变量的值当作事务id分配给该事务，并且把该变量自增 1。
每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为 Max Trx ID 的属性处，这个属性占用 8 个字节的存储空间。
当系统下一次重新启动时，会将上边提到的 Max Trx ID 属性加载到内存中，将该值加上 256 之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于 Max Trx ID 属性值）。
这样就可以保证整个系统中分配的事务id值是一个递增的数字。先被分配id的事务得到的是较小的事务id，后被分配id的事务得到的是较大的事务id。

并发事务处理带来的问题

  • 更新丢失(Lost Update)或脏写

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。

  • 脏读（Dirty Reads）

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；
这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

  • 不可重读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
一句话：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

  • 幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

事务隔离级别

  • 脏读、不可重复读和幻读,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

隔离级别 脏读 不可重复读 幻读
读未提交(READ-UNCOMMITTED) 可能 可能 可能
读已提交(READ-COMMITTED) 不可能 可能 可能
可重复读(REPEATABLE-READ) 不可能 不可能 可能
可串行化(SERIALIZABLE) 不可能 不可能 不可能

  • 数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

  • 不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

  • Mysql默认的事务隔离级别是可重复读(REPEATABLE-READ)

1
2
3
4
5
6
7
8
9
# 查看当前数据库的事务隔离级别，mysql8之前使用`tx_isolation`
mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+----------------+
# 设置事务隔离级别
mysql> set transaction_isolation='READ-COMMITTED';

MVCC多版本并发控制机制

  • Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制

  • 保证了事务的隔离性

  • MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

undo日志版本链

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

read-view

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句， 事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

版本链比对规则

    1. 如果查询结果中row的 trx_id<min_id，表示这个版本是已提交的事务生成的，这个数据是可见的
    1. 如果查询结果中row的 trx_id>max_id，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）
    1. 如果查询结果中row的 min_id <=trx_id<= max_id，那就包括两种情况

a. 若 row 的 trx_id 在read-view视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；
b. 若 row 的 trx_id 不在read-view视图数组中，表示这个版本是已经提交了的事务生成的，可见。

    1. 对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

InnoDB Buffer Pool

  • InnoDB为了缓存磁盘中的页，在MySQL服务器启动的时候就向操作系统申请了一片连续的内存，这片内存叫做Buffer Pool（中文名是缓冲池）,默认情况下Buffer Pool只有128M大小。

  • innodb_buffer_pool_size规定了系统将多少内存用作InnoDB的索引缓存

1
2
3
4
5
6
7
# 查看buffer pool设置大小，推荐设置为内存的60%，最大不要超过75%，比如这里设置了1G，单位是字节
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+

  • Buffer Pool中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是16KB。

  • 为了更好的管理这些在Buffer Pool中的缓存页，InnoDB为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息，当然还有一些别的控制信息。

  • 每个缓存页对应的控制信息占用的内存大小是相同的，我们称为控制块。控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，Buffer Pool的初始化过程，就是把申请到的Buffer Pool的内存空间划分成若干对控制块和缓存页的过程。其中控制块被存放到 Buffer Pool 的前边，缓存页被存放到 Buffer Pool 后边

  • free链表：与空闲的缓存页一一对应的控制块会组成一个free链表，用于表示哪些缓冲页可用，每当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的free链表节点从链表中移除，表示该缓存页已经被使用了。

  • 缓存页的哈希处理

    • 当我们需要访问某个页中的数据时，就会把该页从磁盘加载到Buffer Pool中，如果该页已经在Buffer Pool中的话直接使用就可以了。
    • 那么问题也就来了，我们怎么知道该页在不在Buffer Pool中呢？难不成需要依次遍历Buffer Pool中各个缓存页么？
    • 可以用表空间号 + 页号作为key，缓存页作为value创建一个哈希表，在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从free链表中选一个空闲的缓存页，然后把 磁盘中对应的页加载到该缓存页的位置。

  • flush链表：因为频繁的往磁盘中写数据会严重的影响程序的性能，所以并不是修改了某个页面的数据就立刻刷新到磁盘上，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要在未来某个时间被刷新到磁盘上的，所以也叫flush链表。链表的构造和free链表差不多。

  • LRU链表：Buffer Pool没有多于的空间时，需要基于LRU算法将旧的缓存页从Buffer Pool中移除，然后再把新的页放进来。当我们需要访问某个页时，如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页对应的控制块作为节点塞到LRU链表的头部。如果该页已经缓存在Buffer Pool中，则直接把该页对应的控制块移动到LRU链表的头部。所以当Buffer Pool中的空闲缓存页使用完时，到LRU链表的尾部找些缓存页淘汰就行了。

    • 实际上LRU链表的规则并不是这么简单，因为mysql支持预读，即读取一个数据页时会同时读取其附近的多个页面（这些页可能用不到），或者执行了大表的扫描全表的查询语句（使用频率偏低），这样就有可能淘汰掉Buffer Pool中那些高频访问的页。

    • 解决方法是把这个LRU链表按照一定比例分成两截

      • 一部分存储使用频率非常高的缓存页，所以这一部分链表也叫做热数据，或者称young区域。
      • 另一部分存储使用频率不是很高的缓存页，所以这一部分链表也叫做冷数据，或者称old区域。
      • 可以通过查看系统变量innodb_old_blocks_pct的值来确定old区域在LRU链表中所占的比例
      1
      2
      3
      4
      5
      6
      mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
      +-----------------------+-------+
      | Variable_name         | Value |
      +-----------------------+-------+
      | innodb_old_blocks_pct | 37    |
      +-----------------------+-------+

    • InnoDB规定，当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部，该缓存页再次被访问到时才会把该页放到young区域的头部，前提是这两次访问的时间间隔大于系统变量innodb_old_blocks_time设置的时间间隔，默认1000毫秒

    1
    2
    3
    4
    5
    6
    mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_time';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_old_blocks_time | 1000  |
    +------------------------+-------+
    • 对于young区域的缓存页来说，我们每次访问一个缓存页就要把它移动到LRU链表的头部，这样频繁的对LRU链表进行节点移动操作也会拖慢速度？为了解决这个问题，MySQL规定只有被访问的缓存页位于young区域的1/4的后边，才会被移动到LRU链表头部，这样就 可以降低调整LRU链表的频率，从而提升性能。

刷新脏页到磁盘
mysql后台有专门的线程每隔一段时间负责把脏页刷新到磁盘，这样可以不影响用户线程处理正常的请求。主要有两种刷新路径：

  • 1、从LRU链表的冷数据中刷新一部分页面到磁盘。
  • 2、从flush链表中刷新一部分页面到磁盘。

  • 每个控制块大约占用缓存页大小的5%，而我们设置的innodb_buffer_pool_size并不包含这部分控制块占用的内存空间大小，也就是说InnoDB在为Buffer Pool向操作系统申请连续的内存空间时，这片连续的内存空间一般会比innodb_buffer_pool_size的值大5%左右，这也是为什么不建议设置innodb_buffer_pool_size超过内存的75%。

  • InnoDB增删改查都是直接操作这个buffer pool，并顺序写入undo log和redo log，如果直接操作硬盘就是随机写，效率会非常低

  • InnoDB引擎数据更新执行顺序

1.加载要查询或修改的硬盘数据所在的页到buffer pool，加载时可能会同时加载相邻的页
2.将要修改的数据旧值写入undo log文件，如果事务提交失败，可以用undo日志里的数据进行回滚
3.更新buffer pool内存中的数据
4.写入redo log buffer(内存)
5.提交事务时写入redo log文件，用于在异常情况（如事务提交成功，但buffer pool里的数据尚未写入磁盘，此时宕机）下恢复buffer pool内存中的数据
6.写入bin log文件，主要用于恢复数据库磁盘里的数据
7.bin log文件记录成功后写入commit标记到redo log文件，保证redo与binlog数据一致性
8.buffer pool内存中的数据随机写入磁盘，独立线程每隔一段时间就会刷盘，如果buffer pool写满了也会采用LRU等算法将内存数据写入磁盘

多个Buffer Pool实例

  • Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer Pool中的各种链表都需要加锁处理，在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。
    所以在Buffer Pool特别大的时候，我们可以把它们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。

  • 我们可以在服务器启动的时候通过设置innodb_buffer_pool_instances的值来修改Buffer Pool实例的个数，默认8，最大值是64。

1
2
3
4
5
6
mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 8     |
+------------------------------+-------+

  • 每个Buffer Pool实例实际占内存空间: innodb_buffer_pool_size/innodb_buffer_pool_instances

  • innodb_buffer_pool_size（默认128M）的值小于1G的时候设置多个实例是无效的，InnoDB会默认把innodb_buffer_pool_instances的值修改为1。

  • 最佳的innodb_buffer_pool_instances的数量是，innodb_buffer_pool_size除以innodb_buffer_pool_instances，可以让每个Buffer Pool实例达到1个G

  • mysql服务启动时（故障后重启），会先将redo log中的内容加载到Buffer Pool中，然后在读取undo log进行事务回滚，以此达到重启前的状态

查看Buffer Pool的状态信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SHOW ENGINE INNODB STATUS\G
………………
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 1009326
Buffer pool size   65530
Free buffers       63832
Database pages     1689
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1542, created 161, written 352
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1689, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

返回值说明
Total memory allocated：代表Buffer Pool向操作系统申请的连续内存空间大小，包括全部控制块、缓存页、以及碎片的大小。
Dictionary memory allocated：为数据字典信息分配的内存空间大小，注意这个内存空间和Buffer Pool没啥关系，不包括在Total memory allocated中。
Buffer pool size：代表该Buffer Pool可以容纳多少缓存页，注意，单位是页！
Free buffers：代表当前Buffer Pool还有多少空闲缓存页，也就是free链表中还有多少个节点。
Database pages：代表LRU链表中的页的数量，包含young和old两个区域的节点数量。
Old database pages：代表LRU链表old区域的节点数量。
Modified db pages：代表脏页数量，也就是flush链表中节点的数量。
Pending reads：正在等待从磁盘上加载到Buffer Pool中的页面数量。
当准备从磁盘中加载某个页面时，会先为这个页面在Buffer Pool中分配一个缓存页以及它对应的控制块，然后把这个控制块添加到LRU的old区域的头部，但是这个时候真正的磁盘页并没有被加载进来，Pending reads的值会跟着加1。
Pending writes LRU：即将从LRU链表中刷新到磁盘中的页面数量。
Pending writes flush list：即将从flush链表中刷新到磁盘中的页面数量。
Pending writes single page：即将以单个页面的形式刷新到磁盘中的页面数量。
Pages made young：代表LRU链表中曾经从old区域移动到young区域头部的节点数量。
Page made not young：在将innodb_old_blocks_time设置的值大于0时，首次访问或者后续访问某个处在old区域的节点时由于不符合时间间隔的限制而不能将其移动到young区域头部时，Page made not young的值会加1。
youngs/s：代表每秒从old区域被移动到young区域头部的节点数量。
non-youngs/s：代表每秒由于不满足时间限制而不能从old区域移动到young区域头部的节点数量。
Pages read、created、written：代表读取，创建，写入了多少页。后边跟着读取、创建、写入的速率。
Buffer pool hit rate：表示在过去某段时间，平均访问1000次页面，有多少次该页面已经被缓存到Buffer Pool了。
young-making rate：表示在过去某段时间，平均访问1000次页面，有多少次访问使页面移动到young区域的头部了。
not (young-making rate)：表示在过去某段时间，平均访问1000次页面，有多少次访问没有使页面移动到young区域的头部。
LRU len：代表LRU链表中节点的数量。
unzip_LRU：代表unzip_LRU链表中节点的数量。
I/O sum：最近50s读取磁盘页的总数。
I/O cur：现在正在读取的磁盘页数量。
I/O unzip sum：最近50s解压的页面数量。
I/O unzip cur：正在解压的页面数量。

MyISAM Key Buffer

  • key_buffer_size规定了系统将多少内存用作MyISAM的索引缓存

1
2
# 查看key buffer设置大小
mysql> show variables like 'key_buffer_size';

  • MyISAM增删改成都是直接操作这个Key Buffer

  • Key Buffer只存放索引，对于数据是读取数据文件

  • 如果一个读请求到达，能从Key Buffer中找到数据，那么就不再访问myi文件，直接根据data域去找对应的数据

  • 如果在Key Buffer中找不到，则读取myi中的对应File Block放入Key Buffer的LRU链的头部，并从Key Buffer返回数据

  • 当我们从“.MYI〞文件中读入File Block到Key Buffer中的Cache Block时候，如果整个Key Buffer中己经没有空闲的Cache Block可以使用的话，将会通过Mysql实现的LRU相关算法将某些Cache Blocl清除出夫，让新进来的File Block有地方待。

InnoDB记录存储结构和索引页结构

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。

而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？
InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。
InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式，MySQL5.7以后的默认行格式是Dynamic。

我们可以在创建或修改表的语句中指定行格式： CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
查看行格式SHOW TABLE STATUS LIKE 'table_name'\G

记录行格式

  • Redundant: MySQL5.0之前用的一种行格式，不予深究

  • Compact: MySQL 5.1 开始，默认的行记录格式为 Compact

    • 变长字段长度列表: 存储当前记录中所有变成字段的长度
      • VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，拥有这些数据类型的字段称为变长字段，变长字段中存储多少字节的数据是不固定的，所以在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。
      • 如果该可变字段允许存储的最大字节数超过255字节并且真实存储的字节数超过127字节，则使用2个字节，否则使用1个字节。
    • NULL值列表: 记录当前记录中允许值为NULL的字段是否为NULL
      • 表中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表。
      • 每个允许存储NULL的列对应一个二进制位，二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。
    • 记录头信息: 由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思。
      • 预留位1 1 没有使用
      • 预留位2 1 没有使用
      • delete_mask 1 标记该记录是否被删除
      • min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
      • n_owned 4 表示当前记录拥有的记录数
      • heap_no 13 表示当前记录在页的位置信息
      • record_type 3 表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
      • next_record 16 表示下一条记录的相对位置
    • DB_ROW_ID(row_id)：非必须，6字节，表示行ID，唯一标识一条记录（在没有自定义主键以及Unique键的情况下才会添加该列）
    • DB_TRX_ID：必须，6字节，表示事务ID
    • DB_ROLL_PTR：必须，7字节，表示回滚指针，指向记录对应的 undo 日志位置

  • Dynamic: MySQL5.7以后的默认行格式，和Compact行格式挺像，只不过在处理行溢出数据时有所不同

  • Compressed: 与Dynamic很像，不同的一点是Compressed行格式会采用压缩算法对页面进行压缩，以节省空间

数据溢出

如果我们定义一个表，表中只有一个VARCHAR字段，如下： CREATE TABLE test_varchar( c VARCHAR(60000) ) 然后往这个字段插入60000个字符，会发生什么？

前边说过，MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。

而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的情况。

在Compact和Redundant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前768个字节的数据，然后把剩余的数据分散存储在几个其他的页中，

记录的真实数据处用20个字节存储指向这些页的地址。这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。
Dynamic和Compressed行格式，不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

索引页格式

  • Mysql把存放记录的页称为索引页，也可以理解为是数据页。

  • 一个InnoDB数据页的存储空间大致被划分成了7个部分

英文名称 中文含义 所占空间 备注
File Header 文件头部 38字节 页的一些通用信息
Page Header 页面头部 56字节 数据页专有的一些信息
Infimum + Supremum 最小记录和最大记录 26字节 两个虚拟的行记录
User Records 用户记录 大小不确定 实际存储的行记录内容
Free Space 空闲空间 大小不确定 页中尚未使用的空间
Page Directory 页面目录 大小不确定 页中的某些记录的相对位置
File Trailer 文件尾部 8字节 校验页是否完整

  • User Records

    • 我们自己存储的记录会按照我们指定的行格式存储到User Records部分。

    • 但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，

    • 当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。

    • 当前记录被删除时，则会修改记录头信息中的delete_mask为1，也就是说被删除的记录还在页中，还在真实的磁盘上。

    • 这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗。

    • 所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

    • 同时我们插入的记录在会记录自己在本页中的位置，写入了记录头信息中heap_no部分。 heap_no值为0和1的记录是InnoDB自动给每个页增加的两个记录，称为伪记录或者虚拟记录。

    • 这两个伪记录一个代表最小记录，一个代表最大记录，这两条存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分。

    • 记录头信息中next_record记录了从当前记录的真实数据到下一条记录的真实数据的地址偏移量。这其实是个链表，可以通过一条记录找到它的下一条记录。

    • 但是需要注意注意再注意的一点是，下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。

    • 而且规定 Infimum记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是Supremum记录（也就是最大记录）。

  • Page Directory

    • Page Directory主要是解决记录链表的查找问题。如果我们想根据主键值查找页中的某条记录该咋办？

    • 按链表查找的办法：从Infimum记录（最小记录）开始，沿着链表一直往后找，总会找到或者找不到。但是时间复杂度不低。

    • InnoDB的改进是，为页中的记录再制作了一个目录，他们的制作过程是这样的：

      • 1、将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
      • 2、每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
      • 3、将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录，页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。
      • 4、每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

    • 这样，一个数据页中查找指定主键值的记录的过程分为两步：

      • 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。
      • 通过记录的next_record属性遍历该槽所在的组中的各个记录。

  • Page Header

    InnoDB为了能得到一个数据页中存储的记录的状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，特意在页中定义了一个叫Page Header的部分，它是页结构的第二部分，这个部分占用固定的56个字节，专门存储各种状态信息。

  • File Header

    • File Header针对各种类型的页都通用，也就是说不同类型的页都会以File Header作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，

    • 比方说页的类型，这个页的编号是多少，它的上一个页、下一个页是谁，页的校验和等等，这个部分占用固定的38个字节。

    • 页的类型有很多种，包括Undo日志页、段信息节点、Insert Buffer空闲列表、Insert Buffer位图、系统页、事务系统数据、表空间头部信息、扩展描述页、溢出页(存储变长字段的大数据)、以及我们正在讲的索引页，等等。

    • 同时通过上一个页、下一个页建立一个双向链表把许许多多的页就串联起来，而无需这些页在物理上真正连着。

    • 但是并不是所有类型的页都有上一个和下一个页的属性，数据页是有这两个属性的，所以所有的数据页其实是一个双向链表。

  • File Trailer

    • InnoDB存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。

    • 但是在同步了一半的时候断电了咋办？为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），InnoDB每个页的尾部都加了一个File Trailer部分，这个部分由8个字节组成，可以分成2个小部分：

      • 前4个字节代表页的校验和
        • 这个部分是和File Header中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。
      • 后4个字节代表页面被最后修改时对应的日志序列位置（LSN），这个也和校验页的完整性有关。

    • 这个File Trailer与File Header类似，都是所有类型的页通用的。

InnoDB表空间(TableSpace)

  • 系统表空间(System Tablespace): 对应着文件系统中一个或多个实际文件，一般是ibdata1，表空间 ID（Space ID）是0

  • 独立表空间(File-Per-Table Tablespaces): 对应着文件系统中一个名称为dbname/tablename.ibd的实际文件

  • 临时表空间(Temporary Tablespaces): 对应着文件系统中一个或多个实际文件，一般是ibtmp1和#innodb_temp/temp_数字.ibt

  • 通用表空间(General Tablespaces): 允许多个表存储数据的共享表空间

  • Undo Tablespaces: 对应着文件系统中的undo_001和undo_002

  • 关于表空间的详细说明可以参考mysql官方文档

可以查看information_schema.INNODB_TABLESPACES获取表空间信息,information_schema.INNODB_DATAFILES获取表空间对应文件系统的文件路径信息

表空间与页的关系

双写缓冲区/双写机制

  • 双写缓冲区/双写机制是InnoDB的三大特性之一，还有两个是Buffer Pool、自适应Hash索引。

  • 它是一种特殊文件flush技术，带给InnoDB存储引擎的是数据页的可靠性。它的作用是，在把页写到数据文件之前，InnoDB先把它们写到一个叫doublewrite buffer（双写缓冲 区）的连续区域内，在写doublewrite buffer完成后，InnoDB才会把页写到数据文件的适当的位置。如果在写页的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。

  • 所以，虽然叫双写缓冲区，但是这个缓冲区不仅在内存中有，更多的是属于MySQL的系统表空间，属于磁盘文件的一部分。

  • 正常的情况下, MySQL写数据页时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个页数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。

  • doublewrite buffer的作用有两个:

    • 提高innodb把缓存的数据写到硬盘这个过程的安全性；
    • innodb的事务日志不需要包含所有数据的前后映像,而是二进制变化量，这可以节省大量的IO。

  • doublewrite buffer与Redo Log的区别: Redo Log日志中记录的是对页的物理操作，而doublewrite buffer记录的是页面的全量记录

InnoDB元数据表

  • information_schema数据库

mysql8 mysql5 说明
INNODB_TABLES INNODB_SYS_TABLES 整个InnoDB存储引擎中所有的表的信息
INNODB_COLUMNS INNODB_SYS_COLUMNS 整个InnoDB存储引擎中所有的列的信息
INNODB_INDEXES INNODB_SYS_INDEXES 整个InnoDB存储引擎中所有的索引的信息
INNODB_FIELDS INNODB_SYS_FIELDS 整个InnoDB存储引擎中所有的索引对应的列的信息
INNODB_FOREIGN INNODB_SYS_FOREIGN 整个InnoDB存储引擎中所有的外键的信息
INNODB_FOREIGN_COLS INNODB_SYS_FOREIGN_COLS 整个InnoDB存储引擎中所有的外键对应列的信息
INNODB_TABLESPACES INNODB_SYS_TABLESPACES 整个InnoDB存储引擎中所有的表空间信息
INNODB_DATAFILES INNODB_SYS_DATAFILES 整个InnoDB存储引擎中所有的表空间对应文件系统的文件路径信息
INNODB_VIRTUAL INNODB_SYS_VIRTUAL 整个InnoDB存储引擎中所有的虚拟生成列的信息

redo log 和 undo log

  • 前文关于事务的介绍中，我们提到过，mysql的Innodb引擎在事务的具体实现机制上采用的是WAL(Write-ahead logging，预写 式日志)机制来实现的，具体来说就是 redo log 和 undo log。

  • 在机器掉电重启之后Mysql系统需要知道之前的操作是成功了，还是只有部分成功或者是失败了(为了恢复状态)。如果使用了WAL，那么在重启之后系统可以通过比较日志和系统状态来决定是继续完成操作还是撤销操作。

redo log

redo log 称为重做日志，每当有操作时，在数据变更之前（此时事务并没有提交，所以可能需要回滚，回滚是通过undo log记录的）将操作写入 redo log， 这样当发生掉电之类的情况时系统可以在重启后继续操作。
redo log 用来在系统 Crash 重启之类的情况时修复数据，以此来保证事务的持久性
binlog 记录完成后会在 redo log中加入一个commit标记，表示redo与binlog数据一致

redo log的作用
我们知道，InnoDB的增删改查都是在Buffer Pool完成的，如果我们只在内存的 Buffer Pool 中修改了页面，假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交了的事务对数据库中所做的更改也就跟着丢失了，这是我们所不能忍受的。那么如何保证这个持久性呢？

我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。
所以我们其实没有必要在每次事务提交时就把该事务在内存中修改过的全部页面刷新到磁盘，因为这样做效率太低(还是那个原则，把随机写改为顺序写)，我们只需要把修改了哪些东西记录一下就好，
比方说某个事务将系统表空间中的第 100号 页面中偏移量为 1000 处的那个字节的值 1 改成 2 ，我们只需要记录一下：
将第 0 号表空间的 100 号页面的偏移量为 1000 处的值更新为 2。
这样我们在事务提交时，把上述内容刷新到磁盘中，即使之后系统崩溃了，重启之后只要按照上述内容所记录的步骤重新更新一下数据页，那么该事务对数据库中所做的修改又可以被恢复出来，也就意味着满足持久性的要求。

理论上，redo log的容量足够恢复因掉电等原因导致Buffer Pool没有及时刷入磁盘的内容。否则redo log因容量不够而被覆盖，而此时Buffer Pool尚未刷入磁盘，就不能保证事务的持久性了

这样做与如下好处：
1、redo 日志占用的空间非常小，存储表空间ID、页号、偏移量 以及 需要更新的值 所需的存储空间是很小的。
2、redo 日志是顺序写入磁盘的，在执行事务的过程中，每执行一条语句，就可能产生若干条 redo 日志，这些日志是按照产生的顺序写入磁盘的，也就是使用 顺序IO。

redo日志格式

InnoDB 们针对事务对数据库的不同修改场景定义了多种类型的redo日志，但是绝大部分类型的 redo 日志都有下边这种通用的结构：
type：该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志。
space ID：表空间 ID。
page number：页号。
data：该条 redo 日志的具体内容。

  • type类型有很多种，根据type的不同，redo日志格式中还会增加其它结构

    • 比如简单的类型中会包含修改位置的偏移量或者修改数据的长度，等等，如修改单条记录
    • 复杂一些的类型，如表中包含多少个索引，那么执行一条insert语句就会修改非常多的页面(聚簇索引和二级索引对应的B+树)，针对某一棵 B+树来说，既可能更新叶子节点页面，也可能更新非叶子节点页面，也可能创建新的页面（在该记录插入的叶子节点的剩余空间比较少，不足以存放该记录时，会进行页面的分裂，在非叶子节点页面中添加目录项记录）。在语句执行过程中，INSERT 语句对所有页面的修改都得保存到 redo 日志中去。这个实现起来是非常麻烦的，我们这里不做详细说明。

  • redo日志格式类型非常多，如果不是为了写一个解析 redo 日志的工具或者自己开发一套 redo 日志系统的话，那就不需要去研究 InnoDB 中的 redo 日志具体格式。只要记住：redo 日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来。

redo log buffer

写入 redo 日志时也不能直接直接写到磁盘上，
实际上在服务器启动时就向操作系统申请了一大片称之为 redo log buffer 的连续内存空间，翻译成中文就是 redo 日志缓冲区，
我们也可以简称为 log buffer。

我们可以通过启动参数 innodb_log_buffer_size 来指定 log buffer 的大小，该启动参数的默认值为 16MB。

1
2
3
4
5
6
mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

  • redo log buffer 何时刷入磁盘

    • 1、log buffer 空间不足时

      log buffer 的大小是有限的（通过系统变量innodb_log_buffer_size 指定），如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。InnoDB 认为如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

    • 2、事务提交时

      我们前边说过之所以使用 redo 日志主要是因为它占用的空间少，还是顺序写，在事务提交时可以不把修改过的 Buffer Pool 页面刷新到磁盘，但是为了保证持久性，必须要把修改这些页面对应的 redo 日志刷新到磁盘。

    • 3、后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo 日志到磁盘。

    • 4、正常关闭服务器时等等。

    • 5、MySQL8.0.30之前的版本，redo日志的大小由两个变量控制

      • 1）innodb_log_files_in_group：REDO 日志磁盘上的文件个数，默认为2。文件名称：ib_logfile0,ib_logfile1
      • 2）innodb_log_file_size：REDO 日志磁盘上单个文件的大小，默认为48M。
      • 3）当前的日志大小为单个48M，两个组，也就是一共96M。

    • 6、MySQL8.0.30引入了一个新特性：动态调整redo日志的大小，默认redo日志文件位于datadir下的一个目录#innodb_redo下，redo log 文件存放的位置由参数 innodb_log_group_home_dir 控制，这里要注意，该目录下必须创建#innodb_redo目录，否则会启动失败

      • redo动态日志总大小通过参数innodb_redo_log_capacity设置，默认100M，最大重做日志容量为 128GB，InnoDB维护了32个redo日志文件，每个文件的大小是1/32 * innodb_redo_log_capacity
      • 该版本以及之后的版本，参数 innodb_log_file_size 和 innodb_log_files_in_group 已经被废弃
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      mysql> show variables like 'innodb_redo_log_capacity';
      +--------------------------+-----------+
      | Variable_name            | Value     |
      +--------------------------+-----------+
      | innodb_redo_log_capacity | 104857600 |
      +--------------------------+-----------+

      # 设置为2G
      mysql> set persist innodb_redo_log_capacity=2*1024*1024*1024;

      # 查看redo log是否resize成功
      mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_resize_status';
      +-------------------------------+-------+
      | Variable_name                 | Value |
      +-------------------------------+-------+
      | Innodb_redo_log_resize_status | OK    |
      +-------------------------------+-------+

      # 查看redo log resize后的值
      mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_capacity_resized';
      +----------------------------------+-----------+
      | Variable_name                    | Value     |
      +----------------------------------+-----------+
      | Innodb_redo_log_capacity_resized | 2147483648 |
      +----------------------------------+-----------+

      • 有两种类型的redo日志文件：

        • ordinary：指被使用的redo日志文件，命名规则是：#ib_redoN，这里的N是日志文件号
        • spare：指等待被使用的redo日志文件，命名规则是：#ib_redoN_tmp，这里的N是日志文件号

      • 可以通过查询 performance_schema.innodb_redo_log_files 来查看有关活动重做日志文件的信息

      1
      2
      3
      4
      5
      6
      mysql> SELECT FILE_ID, START_LSN, END_LSN, SIZE_IN_BYTES, IS_FULL, CONSUMER_LEVEL  FROM performance_schema.innodb_redo_log_files;
      +---------+-----------+-----------+---------------+---------+----------------+
      | FILE_ID | START_LSN | END_LSN   | SIZE_IN_BYTES | IS_FULL | CONSUMER_LEVEL |
      +---------+-----------+-----------+---------------+---------+----------------+
      |      21 | 348829696 | 352104448 |       3276800 |       0 |              0 |
      +---------+-----------+-----------+---------------+---------+----------------+
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      [root@ip-10-250-0-214 #innodb_redo]# ll
      总用量 102400
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo21
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo22_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo23_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo24_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo25_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo26_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo27_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo28_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo29_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo30_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo31_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo32_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo33_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo34_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo35_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo36_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo37_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo38_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo39_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo40_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo41_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo42_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo43_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo44_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo45_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo46_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo47_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo48_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo49_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo50_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo51_tmp
      -rw-r----- 1 mysql mysql 3276800 10月 13 07:50 #ib_redo52_tmp

  • innodb_flush_log_at_trx_commit 该变量有 3 个可选的值：
    0：当该系统变量值为0时，表示在事务提交时不立即向磁盘中同步redo日志，这个任务是交给后台线程做的。这样很明显会加快请求处理速度，但是如果事务提交后服务器挂了，后台线程没有及时将 redo 日志刷新到磁盘，那么该事务对页面的修改会丢失。
    1：当该系统变量值为 1 时，表示在事务提交时需要将 redo 日志同步到磁盘，可以保证事务的持久性。1 也是 innodb_flush_log_at_trx_commit 的默认值。
    2：当该系统变量值为 2 时，表示在事务提交时需要将 redo 日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。这种情况下如果数据库挂了，操作系统没挂的话，事务的持久性还是可以保证的，但是操作系统也挂了的话，那就不能保证持久性了

  • 崩溃后的恢复为什么不用 binlog？

    1、这两者使用方式不一样
    binlog 会记录表所有更改操作，包括更新删除数据，更改表结构等等，主要用于人工恢复数据，而 redo log 对于我们是不可见的，它是 InnoDB 用于保证crash-safe 能力的，也就是在事务提交后 MySQL 崩溃的话，可以保证事务的持久性，即事务提交后其更改是永久性的。
    一句话概括：binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性。
    2、redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
    3、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”，恢复的速度更快；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2这的 c 字段加 1 ” ；
    4、redo log 是“循环写”的日志文件，redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。
    5、最重要的是，当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经入表(写入磁盘)，哪些数据还没有。

    比如，binlog 记录了两条日志：

    给 ID=2 这一行的 c 字段加 1
    给 ID=2 这一行的 c 字段加 1
    在记录 1 入表后，记录 2 未入表时，数据库 crash。重启后，只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘，哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 ID=2 这行数据来说，都不对。

    但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。

undo log

undo log 称为撤销日志，当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态。
undo log 用来保证事务的原子性

为了实现事务的原子性，InnoDB 存储引擎在实际进行增、删、改一条记录时，都需要先把对应的 undo 日志记下来。一般每对一条记录做一次改动，就对应着一条 undo 日志，但在某些更新记录的操作中，也可能会对应着 2 条 undo 日志。

一个事务在执行过程中可能新增、删除、更新若干条记录，也就是说需要记录很多条对应的 undo 日志，这些 undo 日志会被从 0 开始编号，也就是说根据生成的顺序分别被称为第 0 号 undo 日志、第 1 号 undo 日志、…、第 n 号 undo日志等，这个编号也被称之为 undo NO。

我们前边说明表空间的时候说过，表空间其实是由许许多多的页面构成的，页面默认大小为 16KB。这些页面有不同的类型，其中有一种称之为FIL_PAGE_UNDO_LOG 类型的页面是专门用来存储 undo 日志的。也就是说 Undo page 跟储存的数据和索引的页等是类似的。
FIL_PAGE_UNDO_LOG 页面可以从系统表空间中分配，也可以从一种专门存放undo日志的表空间，也就是所谓的 undo tablespace 中分配。

  • undo log 与更新操作对应的日志记录条数

    • insert: 记录1条日志，主要是把这条记录的主键信息记上，回滚这个插入操作时把这条记录删除就好了
    • delete: 记录1条日志，undo log日志类型为 TRX_UNDO_DEL_MARK_REC ，将记录的 delete_mask 值被设置为 1，回滚这个删除操作时改回0就好了
    • update: 分两种情况，更新记录主键与不更新记录主键
      • 更新记录主键: 在对该记录进行 delete mark 操作前，会记录一条类型为 TRX_UNDO_DEL_MARK_REC 的 undo 日志；之后插入新记录时，会记录一条类型为 TRX_UNDO_INSERT_REC 的 undo 日志，也就是说每对一条记录的主键值做改动时，会记录 2 条 undo 日志。
      • 不更新记录主键: 记录1条日志，类型为 TRX_UNDO_UPD_EXIST_REC

Redo 日志和 Undo 日志的关系

数据库崩溃重启后，需要先从 redo log 中把未落盘的脏页数据恢复回来，重新写入磁盘，保证用户的数据不丢失。当然，在崩溃恢复中还需要把未提交的事务进行回滚操作。由于回滚操作需要 undo log 日志支持，undo log 日志的完整性和可靠性需要 redo log 日志来保证，所以数据库崩溃需要先做 redo log 数据恢复，然后做 undo log 回滚。

事务进行过程中，每次 sql 语句执行，都会记录 undo log 和 redo log，然后更新数据形成脏页。事务执行 COMMIT 操作时，会将本事务相关的所有 redo log 进行落盘，只有所有的 redo log 落盘成功，才算 COMMIT 成功。然后内存中的 undo log 和脏页按照同样的规则进行落盘。如果此时发生崩溃，则只使用 redo log 恢复数据。

知识面拓展
Commit Logging 和 Shadow Paging
事务的日志类型的实现除了 WAL(Write-ahead logging，预写式日志)外，还有Commit Logging(提交日志)，这种方式只有在日志记录全部都安全落盘，数据库在日志中看到代表事务成功提交的“提交记录”(Commit Record)后，才会根据日志上的信息对真正的数据进行修改，修改完成后，再在日志中加入一条“结束记录”(End Record)表示事务已完成持久化。两者的区别是，WAL 允许在事务提交之前，提前写入变动数据，而 Commit Logging 则不行。阿里的 OceanBase 则是使用的 Commit Logging 来实现事务。

实现事务的原子性和持久性除日志外，还有另外一种称为Shadow Paging”(有中文资料翻译为“影子分页”)的事务实现机制，常用的轻量级数据库 SQLite Version 3 采用的事务机制就是 Shadow Paging。
Shadow Paging 的大体思路是对数据的变动会写到硬盘的数据中，但并不是直接就地修改原先的数据，而是先将数据复制一份副本，保留原数据，修改副本数据。在事务过程中，被修改的数据会同时存在两份，一份是修改前的数据，一份是修改后的数据，这也是“影子”(Shadow)这个名字的由来。当事务成功提交，所有数据的修改都成功持久化之后，最后一步是去修改数据的引用指针，将引用从原数据改为新复制出来修改后的副本，最后的“修改指针”这个操作将被 认为是原子操作，现代磁盘的写操作可以认为在硬件上保证了不会出现“改了半个值”的现象。所以 Shadow Paging 也可以保证原子性和持久性。Shadow Paging 实现事务要比 Commit Logging 更加简单，但涉及隔离性与并发锁时，Shadow Paging 实现的事务并发能力就相对有限，因此在高性能的数据库中应用不多。

数据库锁

  • 从性能上分为乐观锁(用版本对比来实现)和悲观锁

  • 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁[Shared]）：针对同一份数据，多个读操作可以同时进行而不会互相影响。读锁可以认为没有加锁，可读但不可写，当写锁锁住数据时，读锁也会不可获取。
写锁（排它锁，X锁[eXclusive]）：当前写操作没有完成前，它会阻断其他写锁和读锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 给表加读锁
# 当前session和其他session都可以读该表,当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
mysql> lock table actor read;

# 给表加写锁
# 当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞
mysql> lock table actor write;

# 查看哪些表上加了锁
mysql> show open tables;
# 查看指定表
# In_use=1表示被加了写锁
mysql> show open tables like 'actor';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | actor |      1 |           0 |
+----------+-------+--------+-------------+

# 解锁
mysql> unlock tables;

对MyISAM表的读操作(加读锁) ,不会阻寒其他进程对同一表的读请求,但会阻赛对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
对MylSAM表的写操作(加写锁) ,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作

  • 从对数据操作的粒度分，分为表锁和行锁

1
2
# 行锁for update，这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交
select * from actor where id = 10 for update;

注意行锁的查询条件必须走索引，否则会升级为表锁
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

  • 行锁分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度（等待总时长）
Innodb_row_lock_time_avg: 每次等待所花平均时间（等待平均时长）
Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数（等待总次数）

  • MyISAM不支持事务且只支持表锁，在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

  • InnoDB支持事务和行锁，在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

  • 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

  • 间隙锁(Gap Lock): 锁的就是两个值之间的空隙，用于解决幻读。间隙锁是在可重复读隔离级别下才会生效。

如account表的主键id是不连续的（1，2，3，10，20），那么间隙就有id为 (3,10)，(10,20)，(20,正无穷) 这三个区间
在Session_1下面执行 update account set age = 10 where id > 8 and id <18;
则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。
注意这里锁住的最后一条记录不是id=18，而是18所在的间隙区间都会锁住。
尽可能减少检索条件范围，避免间隙锁

  • 临键锁(Next-key Locks): 是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

  • 查看锁等待详细信息

1
mysql> show engine innodb status\G;

慢查询

  • 什么是慢查询
    慢查询日志，顾名思义，就是查询花费大量时间的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

  • 开启慢查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
mysql> show VARIABLES like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

# 开启慢查询日志
mysql> set GLOBAL slow_query_log=1;

# 默认阈值是10秒，超过这个阈值就会记录慢查询日志，可以根据需要进行修改
mysql> show VARIABLES like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

# 如果运行的SQL语句没有使用索引，则MySQL数据库也可以将这条SQL语句记录到慢查询日志文件，默认关闭
mysql> show VARIABLES like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

# 产生的慢查询日志，可以指定输出的位置，通过参数log_output来控制，可以输出到[TABLE][FILE][FILE,TABLE]，默认FILE，如果指定TABLE，则会记录在mysql.slow_log表中
mysql> show VARIABLES like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

# 生成的日志文件默认在datadir指定的目录下，也可以自己设置
mysql> show VARIABLES like 'slow_query_log_file';
+---------------------+-------------------------------------------------------------+
| Variable_name       | Value                                                       |
+---------------------+-------------------------------------------------------------+
| slow_query_log_file | /usr/local/soft/mysql8/datas/mysql/ip-10-250-0-214-slow.log |
+---------------------+-------------------------------------------------------------+

  • 慢查询日志格式

1
2
3
4
5
6
7
8
“Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
“User@Host: root[root] @ localhost [] Id: 3”：用户名 、用户的IP信息、线程ID号
“Query_time: 0.000495”：执行花费的时长【单位：秒】
“Lock_time: 0.000170”：执行获得锁的时长
“Rows_sent”：获得的结果行数
“Rows_examined”：扫描的数据行数
“SET timestamp”：这SQL执行的具体时间
最后一行：执行的SQL语句

  • 慢查询分析mysqldumpslow

1
2
3
4
5
6
7
8
mysqldumpslow -s r -t 10 /usr/local/soft/mysql8/datas/mysql/ip-10-250-0-214-slow.log

# 参数说明：
-s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
  c:总次数 t:总时间 l:锁的时间 r:获得的结果行数
  at,al,ar :指t,l,r平均数 【例如：at = 总时间/总次数】
-t NUM just show the top n queries：仅显示前n条查询
-g PATTERN grep: only consider stmts that include this string：通过grep来筛选语句。