公号：码农充电站pro

主页：https://codeshellme.github.io

这 5 篇文章是我在学习 MySQL 的过程中，总结的笔记：

  • 第一篇 MySQL 学习笔记1-基础篇
    • 1，关于 SQL
    • 2，一条 SQL 的执行步骤
    • 3，MySQL 存储引擎
    • 4，数据库的基本操作
    • 5，关于自增主键
    • 6，SELECT 语句顺序
    • 7，WHERE 子句
    • 8，DISTINCT 去重
    • 9，关于 COUNT(*) 操作
    • 10，MYSQL 函数
    • 11，GROUP BY 数据分组
    • 12，子查询(嵌套查询)
    • 13，JOIN 连接查询
    • 14，VIEW 视图
    • 15，存储过程
    • 16，临时表
    • 17，MySQL 权限管理
    • 18，Python 操作 MySQL 的库
  • 第二篇 MySQL 学习笔记2-进阶篇-上
    • 19，MySQL 的基础架构
    • 20，数据库缓冲池
    • 21，数据库中的存储结构
    • 22，InnoDB 中表数据的存储
  • 第三篇 MySQL 学习笔记3-进阶篇-中
    • 23，事务处理
    • 24，事务的隔离级别
    • 25，MySQL 中的锁
    • 26，MVCC 多版本并发控制
    • 27，MySQL 传输数据的原理
  • 第四篇 MySQL 学习笔记4-进阶篇-下
    • 28，Join 语句的原理
    • 29，MySQL 如何进行排序
    • 30，MySQL 中 kill 命令的原理
    • 31，MySQL 中的 mysqldump 命令
    • 32，MySQL 主从同步
    • 33，MySQL 主备原理
  • 第五篇 MySQL 学习笔记5-调优篇
    • 34，关于 MySQL 索引
    • 35，定位数据库 SQL 性能问题

19，MySQL 的基础架构

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分：

在这里插入图片描述

MySQL 基础架构：

  • Server 层：
    • 连接器：负责跟客户端建立连接、获取权限、维持和管理连接
      • max_connections 参数，用来控制同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。如果把它改得太大，让更多的连接都可以进来，那么系统的负载可能会加大
      • 可以设置数据库跳过权限验证阶段（不建议使用），使用 –skip-grant-tables 参数启动数据库。在 MySQL 8.0 版本里，如果启用 –skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接
    • 查询缓存：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空
      • 可将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存
      • 对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，如下：
      • select SQL_CACHE * from T where ID=10;
      • MySQL 8.0 及以上版本不再有查询缓存的功能
    • 分析器：分析 SQL 是否合法，表是否存在，列是否存在等
    • 优化器：优化 SQL 语句，比如：
      • 当表里面有多个索引的时候，决定使用哪个索引
      • 或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
    • 执行器：执行 SQL 语句，要先判断你对这个表 T 有没有执行查询的权限
      • 如果没有，就会返回没有权限的错误
      • 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口
    • binlog：归档日志，可用于备份数据，MySQL Server 层自有的日志。
  • 存储引擎层：
    • redo log：重做日志，可用于 crash-safe，是 InnoDB 中特有的日志。

MySQL 的内存架构和磁盘架构

在这里插入图片描述

19.1，重要的日志模块 redo log

如果每次更新操作都要写磁盘，那这样的效率会非常低。

于是就有了 redo log，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（内存） 里面，并更新内存，这个时候更新就完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录从内存更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做（redo log 将随机写磁盘的 IO 消耗，转成了顺序写）。

这种先写 redo log，再更新到磁盘的过程，叫做 WAL 技术，全称是 Write-Ahead Logging，意思就是先写日志，再写磁盘。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么redo log 的大小就是 4GB。

如果在这个过程中，redo log 被写满了，MySQL 就会将其中的内容写到磁盘（而不是等到磁盘空闲的时候再做）。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

redo log 的结构

redo log 是一个循环写的结构。

在这里插入图片描述

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

查看 redo log 的大小

show variables like 'innodb_log_file%'

在这里插入图片描述

redo log 由多个文件组成，每个文件的大小由 innodb_log_file_size 控制（单位是字节），文件的个数由 innodb_log_files_in_group 控制。

innodb_log_file_size 参数设置多大合适呢？

redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。

所以，应该尽量将其设置的大一些，如果是几个 TB 的磁盘，直接将 redo log 设置为 4 个文件、每个文件 1GB 。

数据何时写入 redo log 文件？

对于下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但不会在还没 commit 的时候就直接写到 redo log 文件里。

redo log buffer 是一块内存，用来先存 redo 日志。在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

但是，真正把日志写到 redo log 文件，是在执行 commit 语句的时候。

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，有三种取值：

  • 0 ，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  • 1 ，表示每次事务提交时都将 redo log 直接持久化到磁盘；
  • 2 ，表示每次事务提交时都只是把 redo log 写到 page cache。

另外，InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。因此，一个没有提交的事务的 redo log，也可能被持久化到磁盘。

除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中：

  • 一种是，redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意：由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
  • 另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

通常说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

19.2，重要的日志模块 binlog

binlog 是MySQL Server 层的日志机制，可以用来归档，也可以用来做主备同步。

MySQL 自带的引擎是 MyISAM，并没有 crash-safe 的能力。而 InnoDB 是另一个公司以插件形式引入 MySQL 的， InnoDB 使用 redo log 来实现 crash-safe 能力。

binlog 的写入逻辑：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

在这里插入图片描述

每个线程有自己 binlog cache，但共用同一份 binlog 文件。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

  • sync_binlog=0 ，表示每次提交事务都只 write，不 fsync
  • sync_binlog=1 ，表示每次提交事务都会执行 fsync
  • sync_binlog=N(N>1) ，表示每次提交事务都 write，但累积 N 个事务后才 fsync
    • 对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志

redo log 与 binlog 的区别

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

19.3，重要的日志模块 undo log

undo log 用于事务的回滚。

19.4，MySQL 的更新步骤

对于以下更新操作：

mysql> update T set c=c+1 where ID=2;

MySQL 的更新流程为：

  1. 执行器先找引擎取 ID=2 这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上 1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

在这里插入图片描述

19.5，两阶段提交的必要性

在 MySQL 中有两种日志：

  • redo log：当 MySQL 突然宕机时，用于恢复数据，确保 crash-safe
    • innodb_flush_log_at_trx_commit 参数设置成 1 时，表示每次事务的 redo log 都直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不丢失。
    • datadir 参数是 MySQL 数据的存储目录。
    • innodb_log_buffer_size 是 redo log 在内存中的大小。
    • innodb_log_file_size 参数是一个 redo log 在磁盘的大小。
    • innodb_log_files_in_group 参数是 redo log 的个数。
  • binlog：用于备份数据，集群之间的数据迁移（主从同步）也是使用 binlog。
    • sync_binlog 参数设置成 1 时，表示每次事务的 binlog 都持久化到磁盘，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

MySQL 在写数据时，会先写 redo-log 时，再写 binlog，在这两个写操作之间有可能发生意外宕机，这就会导致 binlog 没有写入内容，从而造成 redo-log 与 binlog 不一致的结果，最终造成数据库中的数据不一致的情况。

因此，就有了两阶段提交，commit 状态保证 redo log 和 binlog 都写入成功了，从而保证了数据的一致性。如果一个事务没有 commit 转态，那就是一个不完整的事务，不是有效事务。

两阶段提交是跨系统维持数据逻辑一致性的常用方案。

19.6，redo log 与脏页

redo log 的三种状态：

在这里插入图片描述

redo log 在内存和磁盘各有一份，当内存中的 redo log 与磁盘中的 redo log 不一致的时候，内存中的 redo log 称为脏页，当脏页被刷（flush）到磁盘后，内存中的redo log 称为干净页。

如何判断一个数据页是否是脏页：

  • 每个数据页头部有LSN，8字节，每次修改都会变大
  • 对比这个LSN跟checkpoint 的LSN，比checkpoint小的一定是干净页
  • 脏页大小 = current LSN - check point LSN

在以下几种情况，MySQL 会将脏页刷到磁盘：

  • 当内存不足时，需要将内存中的一些 redo log 释放掉，这有两种情况：
    • 内存中的 redo log 是脏页：将脏页刷到磁盘
    • 内存中的 redo log 是干净页：直接释放
  • 磁盘上的 redo log 写满了
    • 这时需要将磁盘上的redo log 中的 check point 向前移动（将数据更新到数据文件）
    • 而此时就要确保 check point 向前移动的那一部分数据是正确的数据，这就需要将涉及到的脏页刷到磁盘
    • 当这种情况出现的时候，MySQL 则不能在接收新的更新操作，直到磁盘上的 redo log 有了空闲空间
    • 因此这种情况应该尽量避免，以防影响MySQL 性能
  • MySQL 空闲时，会刷脏页
    • 不会影响MySQL 性能
  • MySQL 关闭之前，会刷脏页
    • 不会影响MySQL 性能

InnoDB 控制脏页的策略：

  • innodb_io_capacity 参数会告诉 InnoDB 磁盘的 IO 能力。
    • 这个值建议设置成磁盘的 IOPS
    • 如果这个值设置的不合适（较小）会导致刷脏页的速度较慢，从而影响MySQL性能
  • innodb_flush_neighbors 参数：刷脏页连坐机制
    • InnoDB 在刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉
    • 而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷
    • innodb_flush_neighbors 值为 1 时，会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
  • innodb_max_dirty_pages_pct 参数是脏页的比例，默认是 75
    • 脏页比例 = 脏页大小 / redo log 大小
    • 当到达比例时，会刷脏页

测试磁盘 IOPS 的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

19.7，组提交 group commit

组提交机制，可以大幅度降低磁盘的 IOPS 消耗。一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。

有两个参数来控制组提交：

  • binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
  • binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync。

19.8，MySQL 中的日志

MySQL 的日志种类非常多，包括：

  • 通用查询日志
  • 错误日志
  • 慢查询日志
  • 二进制日志（binlog）
  • 重做日志（redo log）
  • 回滚日志（undo log）
  • 中继日志（relaylog）
1，通用查询日志

通用查询日志记录了所有用户的连接开始时间和截止时间，以及发给 MySQL 数据库服务器的所有 SQL 指令。

当我们的数据发生异常时，通过通用查询日志，可以清楚的知道当时发生了什么，帮助我们准确定位问题。

相关变量：

mysql> SHOW VARIABLES LIKE '%general%';
+------------------+---------------+
| Variable_name    | Value 		   |
+------------------+---------------+
| general_log      | OFF           | -- 通用查询日志处于关闭状态
| general_log_file | GJTECH-PC.log | -- 通用查询日志文件的名称是 GJTECH-PC.log
+------------------+---------------+
  • general_log：日志是否开启，OFF（未开启，默认），ON（已开启）
    • 一旦开启记录通用查询日志，MySQL 会记录所有的连接起止和相关的 SQL 操作，这样会消耗系统资源并且占用磁盘空间。
    • 我们可以通过手动修改变量的值，在需要的时候开启日志。
  • general_log_file：日志名称

设置变量值：

mysql> SET GLOBAL general_log = 'ON';
mysql> SET @@global.general_log_file = 'H:\mytest.log';

通用查询日志的内容：

2021-04-05T06:39:53.621980Z 28 Connect zhangsan@localhost on using SSL/TLS   -- 账号zhangsan从本地登录
2021-04-05T06:39:53.622085Z 28 Connect Access denied for user 'zhangsan'@'localhost' (using password:NO)  -- 没用密码，连接被拒
2021-04-05T06:40:02.522303Z 29 Connect zhangsan@localhost on using SSL/TLS
2021-04-05T06:40:02.522913Z 29 Query select @@version_comment limit 1
2021-04-05T06:40:14.211511Z 29 Query SELECT * FROM demo.invcount  -- 查询数据
2021-04-05T06:40:37.647625Z 29 Query UPDATE demo.invcount SET plquant = - 5 WHERE itemnumber = 1   -- 更新数据
2021-04-05T06:41:15.047067Z 29 Query SELECT * FROM demo.goodsmaster  -- 查询数据

当日志文件变的非常大时，如果想移除/更换旧的文件，需要先关闭日志，然后再将其打开：

mysql> SET GLOBAL general_log = 'OFF'; -- 关闭通用查询日志

-- 如果需要备份文件，就在此时备份文件

mysql> SET GLOBAL general_log = 'ON';  -- 打开日志，日志文件已被清空
2，错误日志

错误日志记录了 MySQL 服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。

当我们的数据库服务器发生系统故障时，错误日志是发现问题、解决故障的首选。

错误日志默认是开启的。我们可以在 MySQL 的配置文件“my.ini”中配置它：

log-error="GJTECH-PC.err"  -- 错误日志文件名

文件内容示例：

2021-02-28T08:07:07.228880Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.23) starting as process 7652
2021-02-28T08:07:07.270982Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. -- 启动时间
2021-02-28T08:07:08.116433Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.   -- 停止时间
3，慢查询日志

参考这里。

4，二进制日志

二进制日志主要记录数据库的更新事件（比如创建数据表、更新表中的数据、数据更新所花费的时长等信息），它是进行数据恢复和数据复制的利器。

查看二进制日志

查看当前正在写入的二进制日志的名称和当前写入的位置：

mysql> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------+
| GJTECH-PC-bin.000011 | 2207     |              |                  |                  |
+----------------------+----------+--------------+------------------+------------------+

查看所有的二进制日志：

mysql> SHOW BINARY LOGS;
+----------------------+-----------+-----------+
| Log_name             | File_size | Encrypted |
+----------------------+-----------+-----------+
| GJTECH-PC-bin.000005 | 179       | No        |
| GJTECH-PC-bin.000006 | 113316452 | No        |
| GJTECH-PC-bin.000007 | 12125     | No        |
| GJTECH-PC-bin.000008 | 1544      | No        |
| GJTECH-PC-bin.000009 | 207       | No        |
| GJTECH-PC-bin.000010 | 1758      | No        |
| GJTECH-PC-bin.000011 | 2207      | No        |
| GJTECH-PC-bin.000012 | 462       | No        |
+----------------------+-----------+-----------+

查看二进制日志中所有数据的更新事件：

SHOW BINLOG EVENTS IN 二进制文件名;

刷新二进制日志

刷新二进制日志：

FLUSH BINARY LOGS;

这句话的含义是，关闭服务器正在写入的二进制日志文件，并重新打开一个新文件，文件名的后缀在现有的基础上加 1。

恢复数据

们可以用 mysqlbinlog 工具进行数据恢复：

mysqlbinlog --start-positon=xxx --stop-position=yyy 二进制文件名 | mysql -u 用户 -p

这条命令的意思是，执行二进制日志中从位置 xxx 开始，到 yyy 截止的所有数据更新操作。这里的截止位置也可以不写，意思是从位置 xxx 开始，执行二进制文件中的所有数据更新操作。

删除日志

如果我们已经把日志文件保存到了安全的地方，就可以通过下面的 SQL 语句删除所有二进制日志文件，以释放磁盘空间：

mysql> RESET MASTER;
Query OK, 0 rows affected (0.20 sec)

mysql> SHOW BINARY LOGS;
+----------------------+-----------+-----------+
| Log_name             | File_size | Encrypted |
+----------------------+-----------+-----------+
| GJTECH-PC-bin.000001 | 156       | No        |
+----------------------+-----------+-----------+

我们也可以删除比指定二进制日志文件编号小的所有二进制日志文件：

mysql> PURGE MASTER LOGS TO 'GJTECH-PC-bin.000005';
Query OK, 0 rows affected (0.02 sec)

备份二进制日志

数据库 demo 中的全部数据，备份到文件 mybackup.sql中（全量备份）：

# demo 是数据库名称
mysqldump -u root -p demo > mybackup.sql
Enter password: *****

刷新一下日志：

mysql> FLUSH BINARY LOGS;

目的是：产生一个新的二进制日志文件，使这个文件只保存全量数据备份之后的数据更新事件。

从备份恢复数据

创建一个新的数据库：

mysql> CREATE DATABASE demo1;

还原数据库：

# demo1 是数据库名称、
mysql -u root -p demo1 < mybackup.sql
Enter password: *****
5，中继日志

二进制日志还有一个重要的功能，就是在主从服务器的架构中，把主服务器的操作复制到从服务器。而这个操作要借助中继日志一起完成。

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。

然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

中继日志的文件名格式是：从服务器名-relay-bin.序号。

中继日志还有一个索引文件：从服务器名-relay-bin.index，用来定位当前正在使用的中继日志。

6，回滚日志

回滚日志的作用是进行事务回滚。当事务执行的时候，回滚日志中记录了事务中每次数据更新前的状态。当事务需要回滚的时候，可以通过读取回滚日志，恢复到指定的位置。

单个回滚日志的最大存储空间：

mysql> SHOW VARIABLES LIKE '%innodb_max_undo_log_size%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 1073741824 |
+--------------------------+------------+

其它相关变量：

mysql> SHOW VARIABLES LIKE '%innodb_undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | .\    |  -- 回滚日志的存储目录
| innodb_undo_log_encrypt  | OFF   |  -- 表示回滚日志不加密
| innodb_undo_log_truncate | ON    |  -- 回滚日志是否自动截断回收，前提是设置了独立表空间
| innodb_undo_tablespaces  | 2     |  -- 表示回滚日志有自己的独立表空间，而不是在共享表空间ibdata文件中
+--------------------------+-------+
7，重做日志

重做日志是存储在磁盘上的一种日志文件，主要有 2 个作用。

  • 在系统遇到故障的恢复过程中，可以修复被未完成的事务修改的数据。
  • MySQL 为了提高数据存取的效率，减少磁盘操作的频率，对数据的更新操作不会立即写到磁盘上，而是把数据更新先保存在内存中，积累到一定程度，再集中进行磁盘读写操作。
    • 这样就存在一个问题：一旦出现宕机或者停电等异常情况，内存中保存的数据更新操作可能会丢失。
    • 这个时候就可以通过读取重做日志中记录的数据更新操作，把没来得及写到磁盘上的数据更新写到磁盘上，确保数据的完整性。

相关变量：

mysql> SHOW VARIABLES LIKE '%innodb_log_files_in_group%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+

表示有 2 个重做日志文件。

变量 innodb_log_files_in_group 值的取值范围是 1~4，这四个文件分别用于记录不同的操作：

  • 用户创建表的插入操作
  • 用户创建表的更新和删除操作
  • 临时表的插入操作
  • 临时表的更新和删除操作

那为什么 innodb_log_files_in_group 值是 2 呢？这是因为，只执行了表的插入操作和更新删除操作，所以，只用到了 2 个文件。如果还执行了临时表的插入和更新删除的操作，那么这个变量的值就会变成 4，也就是会有 4 个重做日志文件了。

20，数据库缓冲池

数据库缓冲池（buffer pool）用于加快数据的读写性能。

20.1，缓冲池原理

buffer poll 存储的是 idb 文件的叶子节点（页），idb 文件以 B+ 树的结构存储，实际的数据都存储在叶子节点上，一个叶子节点就是一个页，是 16 K。

在这里插入图片描述

buffer pool 的底层数据结构：

  • LRU 链表：缓存的淘汰策略（最近最少使用），缓冲池放满之后会被淘汰数据
    • 该链表分为冷热数据区（可解决全表扫描 select * from t; 这种操作对缓存数据的影响）：
      • 热数据区：占 5/8
      • 冷数据区：占 3/8，由 innodb_old_blocks_pct 参数控制
    • 冷数据区的数据转到热数据区的条件：
      • 页的第二次使用时间与第一次使用时间的时间间隔小于一个值：
      • 参数innodb_old_blocks_time，单位毫秒
  • Free 链表：记录了缓冲区中的空闲区域（页）
  • Flush 链表：记录了被更新过的区域（页），称为脏页
  • 自适应哈希索引也会占用 buffer pool 的空间（34）
    • mysql> show engine innodb status; 命令可以看到 buffer pool 的使用情况

innodb_change_buffering 参数用于设置缓存哪些操作：

  • all：默认值，所有操作
  • none：不要缓存任何操作
  • inserts：缓冲插入操作
  • deletes：缓冲删除标记操作
  • changes：缓冲插入和删除标记操作。
  • purges：缓冲在后台发生的物理删除操作

20.2，缓冲池作用

对于读操作：

  • 缓冲池管理器会尽量将经常使用的数据保存起来；
  • 在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中：
    • 如果存在就直接读取；
    • 如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。
      • 内存读取：如果数据在内存中，会直接从内存中读取，所需时间约 1ms；
      • 磁盘读取：如果数据不在内存中，会从磁盘读取；
        • 随机读取：如果需要查找，时间基本约需 10ms；
        • 顺序读取：如果一个磁盘的吞吐量是 40MB/S，那么对于一个 16KB 大小的页来说，一次可以顺序读取 2560（40MB/16KB）个页，相当于一个页的读取时间为 0.4ms。

对于写操作：

  • 当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。
  • 注意并不是每次发生更新操作，都会立刻进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制将数据回写到磁盘上，这样做的好处就是提升了数据库的整体性能。
    • 当缓冲池不够用时，需要释放掉一些不常用的页，就可以采用强行采用 checkpoint 的方式，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页释放掉。
    • 脏页（dirty page）指的是缓冲池中被修改过的页，与磁盘上的数据页不一致。

20.3，查看与修改缓冲池大小

mysql > show variables like 'innodb_buffer_pool_size';

在这里插入图片描述

可以看到 InnoDB 的缓冲池大小只有 8388608/1024/1024=8MB，我们可以修改缓冲池大小为 128MB，方法如下：

mysql > set global innodb_buffer_pool_size = 134217728;

一般建议将 innodb_buffer_pool_size 设置成可用物理内存的 60%~80%。

在 InnoDB 存储引擎中，可以同时开启多个缓冲池，查看缓冲池的个数：

mysql > show variables like 'innodb_buffer_pool_instances';

在这里插入图片描述

可以看到当前只有一个缓冲池。

说明：

  • 实际上 innodb_buffer_pool_instances 默认情况下为 8，为什么只显示只有一个呢？
  • 原因是，如果想要开启多个缓冲池，首先需要将 innodb_buffer_pool_size 参数设置为大于等于 1GB，这时 innodb_buffer_pool_instances 才会大于 1。

你可以在 MySQL 的配置文件中对 innodb_buffer_pool_size 进行设置，大于等于 1GB，然后再针对 innodb_buffer_pool_instances 参数进行修改。

查看 innodb 整体状态(其中有很多关于 Buffer Pool 的状态)：

> show engine innodb status;

20.4，SQL 的查询成本

可以在执行完一条 SQL 语句后，通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。这个查询成本对应的是 SQL 语句所需要读取的页的数量。

mysql> SHOW STATUS LIKE 'last_query_cost';

例 1：

在这里插入图片描述

例 2：

在这里插入图片描述

需要说明的是，如果 last_query_cost 的值比较大，并一定意味着 SQL 的查询时间较长；因为如果是顺序读取的方式将页面一次性加载到缓冲池中，这是页的读取速度是非常快。

20.5，change buffer

change buffer 主要用于优化（二级索引的）更新操作。

当需要更新一个数据页时：

  • 如果数据页在内存中就直接更新
  • 如果这个数据页没在内存中（注意，只有当数据不在内存中时才会用到 change buffer），InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
    • 在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。（通过这种方式就能保证这个数据逻辑的正确性）

change buffer 在内存和磁盘各有一份，在内存中占用 buffer pool 的空间，在磁盘上占用数据库表空间。innodb_change_buffer_max_size 参数（默认为25，最大设置为50）指定了 change buffer 占用 buffer pool 的百分比。

change buffer 的 merge 操作

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge，下面三种时机会触发 merge 操作：

  • 访问这个数据页会触发 merge
  • 系统有后台线程会定期 merge
  • 在数据库正常关闭（shutdown）的过程中，也会 merge 操作

change buffer 对普通索引与唯一索引的影响

唯一索引的更新不能使用 change buffer，只有普通索引可以使用：

  • 对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。
  • 比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在这条记录，而这必须要将数据页读入内存才能判断。
  • 如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

在更新数据时，如果要更新的目标页不在内存中，InnoDB 的处理流程如下：

  • 对于唯一索引，需要将数据页从磁盘读入内存（较慢），判断到没有冲突，插入这个值，语句执行结束
  • 对于普通索引，则是将更新记录在 change buffer（很快），语句执行就结束了。
    • change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是很明显的。

change buffer 最适用的场景

由上面的分析可知，change buffer 对普通索引（不适用唯一索引）的更新有加速作用，而更新操作也分两种实际场景：

  • 写多读少：
    • change buffere 的 merge 操作是真正进行数据更新的时刻， change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
    • 因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的是账单类、日志类 的系统。
  • 读多写少：
    • 如果一个更新在写入之后马上会做查询，那即使将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。
    • 所以，对于这种业务模式来说，change buffer 反而起到了副作用。
    • 如果所有的更新后面，都马上伴随着对这个记录的查询，那么建议关闭 change buffer。

21，数据库中的存储结构

MySQL 中的数据（记录）是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。

当读一条记录时：

  • 如果记录所在的页在内存中，则直接从内存中获取记录
  • 如果记录所在的页不在内存中，则会将记录所在的页（磁盘中）加载到内存中，然后返回记录

MySQL 中表空间，段，区，页，行的关系：

在这里插入图片描述

  • 区：在 InnoDB 存储引擎中，一个区会分配 64 个连续的页； InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB。
  • 段：段由一个或多个区组成，段中不要求区与区之间是相邻的。
    • 不同类型的数据库对象以不同的段形式存在。
    • 当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
  • 表空间：是一个逻辑容器，一个表空间可以有一个或多个段，但一个段只能属于一个表空间。
    • 数据库由一个或多个表空间组成；
    • 表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
    • 在 InnoDB 中存在两种表空间的类型：共享表空间和独立表空间。
      • 共享表空间：意味着多张表共用一个表空间。
      • 独立表空间：意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。

查看 InnoDB 的表空间类型：

mysql > show variables like 'innodb_file_per_table';

在这里插入图片描述

innodb_file_per_table=ON，这意味着每张表都会单独保存为一个.ibd 文件。

21.1，页结构

查看页的大小：

mysql> show variables like '%innodb_page_size%';

在这里插入图片描述

页结构的示意图（包括 7 个部分）：

在这里插入图片描述

页结构的变化：

在这里插入图片描述

21.2，页分裂与页合并

当一个数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

21.3，从数据页的角度看 B+ 树

一棵 B+ 树按照节点类型可以分成两部分：

  • 叶子节点，B+ 树最底层的节点，用于存储行记录。
  • 非叶子节点，用于存储索引键和页面指针，并不存储行记录本身。

在这里插入图片描述

在一棵 B+ 树中，每个节点都是一个页，每次新建节点的时候，就会申请一个页空间。同一层上的节点之间，通过页的结构构成一个双向的链表（页文件头中的两个指针字段）。

  • 非叶子节点，包括了多个索引行，每个索引行里存储索引键和指向下一层页面的页面指针。
  • 叶子节点，存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表，但是对记录进行查找，则可以通过页目录采用二分查找的方式来进行。

21.4，B+ 树如何检索记录

B+ 树的索引查询行记录，首先是从 B+ 树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。

虽然我们想要查找的，只是一行记录，但是对于磁盘 I/O 来说却需要加载一页的信息，因为页是最小的存储单位。

22，InnoDB 中表数据的存储

一个 InnoDB 表包含两部分，即：表结构定义和数据。

在 MySQL 8.0 以前，表结构是存在以 .frm 为后缀的文件里（占用的空间很少）。而 MySQL 8.0 ，则已允许把表结构定义放在系统数据表中了。

22.1，参数 innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。该行为由 innodb_file_per_table 控制：

  • 值为 OFF 表示，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
  • 值为 ON （默认值）表示，每张表数据存储在一个以 .ibd 为后缀的文件中。
    • 建议将这个值设置为 ON。
    • 因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。
    • 而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

22.2，数据的删除流程

删除数据分为下面几种情况：

  • 使用 drop table 命令，系统就会直接删除表文件。
  • 删除表中的某行数据，MySQL 不会直接从文件中删除数据，而是做一个删除标记。
    • 被标记为删除的空间，可以被重复利用。
    • 如果某一页上的所有数据都被删除，那么整个数据页就可以被复用。
  • 使用 delete 将表中的所有数据删除，该表的所有数据页将被标记为删除，但表文件不会改变。

22.3，数据空洞

由上可知，delete 命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的。

也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

除了 delete 会造成数据空洞之外，insert 和 update（先 delete 再 insert） 也会造成数据空洞。

当 insert 时，如果一个数据页满了，则会导致分页，此时就会造成空洞：

在这里插入图片描述

由上可知，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

22.4，重建表消除空洞

重建表，可以达到消除空洞的目的。

  • 新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。
  • 由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。

在这里插入图片描述

重建表的命令：

# 重建表
alter table table_name engine=InnoDB;

在 MySQL 5.6 之前，重建表的时候不允许插入数据；MySQL 5.6 版本开始引入的 Online DDL，允许在重建表是插入数据。

几个表相关的操作：

  • create table：新建一个表。
  • drop table：删除一个表。
  • alter table：重建表。
  • analyze table：对表的索引信息做重新统计，没有修改数据。
  • optimize table ：相当于 alter + analyze。
  • truncate table：相当于 drop + create。

22.5，表空间

InnoDB 存储引擎的文件格式是 .ibd 文件，数据会按照表空间进行存储，分为：

  • 共享表空间：多个数据表共用一个表空间，同时表空间也会自动分成多个文件存放到磁盘上。
    • 这样做的好处在于单个数据表的大小可以突破文件系统大小的限制，最大可以达到 64TB，也就是 InnoDB 存储引擎表空间的上限。
    • 不足是，多个数据表存放到一起，结构不清晰，不利于数据的找回，同时将所有数据和索引都存放到一个文件中，也会使得共享表空间的文件很大。
  • 独立表空间：每个数据表都有自己的物理文件，也就是 table_name.ibd 的文件，在这个文件中保存了数据表中的数据、索引、表的内部数据字典等信息。
    • 优势在于每张表都相互独立，不会影响到其他数据表，存储结构清晰，利于数据恢复，同时数据表还可以在不同的数据库之间进行迁移。

如果我们之前没有做过全量备份，也没有开启 Binlog，那么我们还可以通过.ibd 文件进行数据恢复，采用独立表空间的方式可以很方便地对数据库进行迁移和分析。

查看表空间的存储方式：

# ON 表示独立表空间，而 OFF 则表示共享表空间。
> show variables like 'innodb_file_per_table';

在这里插入图片描述

22.6，MySQL 中的分区表

创建分区表示例：

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

这个表包含了 1 个 .frm 文件和 4 个 .ibd 文件，每个分区对应一个 .ibd 文件：

在这里插入图片描述

对于引擎层来说，这是 4 个表；对于 Server 层来说，这是 1 个表。

表 t 中初始化插入了两行记录，按照分区规则，这两行记录分别落在 p_2018 和 p_2019 这两个分区上。

分区策略有 range 分区，hash 分区、list 分区等（示例中就是 range 分区）。

注意，使用分区表，不要创建太多的分区。

  • 分区并不是越细越好
  • 分区也不要提前预留太多，在使用之前预先创建即可。
    • 比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。
    • alter table t drop partition ...