公号:码农充电站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,意思就是先写日志,再写磁盘

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

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

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

redo log 的结构

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

在这里插入图片描述

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

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

write poscheckpoint 之间的是还空着的部分,可以用来记录新的操作。如果 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_binloginnodb_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 文件。

writefsync 的时机,是由参数 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_sizeredo 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-logbinlog 不一致的结果,最终造成数据库中的数据不一致的情况。

因此,就有了两阶段提交commit 状态保证 redo logbinlog 都写入成功了,从而保证了数据的一致性。如果一个事务没有 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。

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 会造成数据空洞之外,insertupdate(先 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 ...