表
8.0 以前 InnoDB表：
ibd ： 数据和索引
frm : 存私有的数据字典信息
ibdataN：系统的数据字典信息
8.0 在数据字典改变
ibd：数据和索引+ 冗余的SDI私有数据字典信息
取消了 ibdata中的系统数据字典信息。
mysql.ibd ---> 整个系统的数据字典，不再放在ibdata1
sdi序列化的数据字典----> 每个表的表空间自行管理json格式的私有数据字典信息，用来替换frm
的。
索引
聚簇索引
InnoDB表，永远存在的一类索引。
构建前提：
1. pk的列会自动作为聚簇索引。
2. 没有pk ,会选择非空UK。
3. 都没有，生成隐藏的ROW_ID(6字节)
功能：
1. IOT，索引组织表。
2. 针对ID条件查询快速找到记录。
结构简述：
leaf ： 按照ID顺序逻辑上有序存储的数据行。在同一个区内的数据页使用物理连续。
on-leaf:
root: 下层节点的ID范围+指针
内部节点：也可以理解为枝节点,下层节点的ID范围+指针
image.png
辅助索引
结构简述：如图
leaf ： “提取” 辅助索引(name)列值+ID列值，根据辅助列(name)值排序，生成叶子节点。
on-leaf:
root: 下层节点的name范围+指针
内部节点：也可以理解为枝节点,下层节点的name范围+指针
查询过程：
索引覆盖 : select id,name from t1 where name='x';
回表查询 : select * from t1 where name='x';
image.png
优化器的算法
ICP MRR SNLJ BNLJ BKA HASH JOIN（8.0）
表空间
System Tablespace(共享表空间)
- ibdata1共享表空间在各个版本的变化
5.5版本：
系统相关：（全局）数据字典信息（表基本结构信息、状态、系统参数、属性..）、UNDO回滚信息
(记录撤销操作)、Double Write buffer信息、临时表信息、change buffer
用户数据： 表数据行、表的索引数据
5.6版本：共享表空间只存储于系统数据，把用户数据独立了。
系统相关：（全局）数据字典信息、UNDO回滚信息、Double Write信息、临时表信息、change
buffer
5.7版本：在5.6基础上，把临时表独立出来，UNDO也可以设定为独立
系统相关：（全局）数据字典信息、UNDO回滚信息、Double Write信息、change buffer
8.0.19版本：在5.7的基础上将UNDO回滚信息默认独立，数据字典不再集中存储了。
系统相关：Double Write信息、change buffer
8.0.20版本:在之前版本基础上，独立 Double Write信息
系统相关：change buffer
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
总结： 对于InnoDB(8.0)表来讲，例如 city表
city.ibd
mysql.ibd
undo
ibdata
redo
- 扩容共享表空间
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend |
+-------------------------+
1 row in set (0.00 sec)
mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
| 64 |
+-------------------------------+
1 row in set (0.00 sec)
参数用途：ibdata1文件，默认初始大小12M，不够用会自动扩展，默认每次扩展64M
设置方式：
错误的方法：
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend
重启数据库报错，查看日志文件
vim /data/3306/data/db01.err
###################
[ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different
size 4864 pages (rounded down to MB) than the 768 pages specified in the
.cnf file!
###################
实际大小：
4864*16K/1024=76M
my.cnf文件设置：
768*16K/1024=12M
正确的方法：
先查看实际大小：
[root@db01 data]# ls -lh ibdata1
-rw-r----- 1 mysql mysql 76M May 6 17:11 ibdata1
配置文件设定为和实际大小一致：
innodb_data_file_path=ibdata1:76M;ibdata2:100M;ibdata3:100M:autoextend
### 模拟在初始化时设置共享表空间（生产建议）
5.7 中建议：设置共享表空间2-3个，大小建议512M或者1G，最后一个定制为自动扩展。
8.0 中建议：设置1个就ok，大小建议512M或者1G
# 清理数据
[root@db01 data]# /etc/init.d/mysqld stop
[root@db01 data]# rm -rf /data/3306/data/*
[root@db01 data]# vim /etc/my.cnf
# 修改
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend
# 重新初始化
[root@db01 data]# mysqld --initialize-insecure --user=mysql --
basedir=/data/app/mysql --datadir=/data/3306/data
# 重启数据库生效
[root@db01 data]# /etc/init.d/mysqld start
File-Per-Table Tablespaces(用户独立表空间)
- 介绍
5.6版本中，针对用户数据，单独的存储管理。存储表的数据行和索引。
通过参数控制：
mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
测试： 共享表空间存储用户数据
mysql> set global innodb_file_per_table=0;
2.利用独立表空间进行快速数据迁移
源端:3306/test/t100w -----> 目标端：3307/test/t100w
1. 锁定源端t100w表
mysql> flush tables test.t100w with read lock;
mysql> show create table test.t100w;
CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2. 目标端创建test库和t100w空表
mysql> create database test charset=utf8mb4;
CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3. 单独删除空的表空间文件
mysql> alter table test.t100w discard tablespace;
4. 拷贝源端ibd文件到目标端目录，并设置权限
[root@db01 test]# cp /data/3306/data/test/t100w.ibd /data/3307/data/test/
[root@db01 test]# chown -R mysql.mysql /data/*
5. 导入表空间
mysql> alter table test.t100w import tablespace;
mysql> select count(*) from test.t100w;
+----------+
| count(*) |
+----------+
| 1000000 |
6. 解锁源端数据表
mysql> unlock tables;
应用场景：
案例1：同学由于不可抗力因素，导致只剩下test库下的ibd 和 frm文件了。（5.6版本）。没有备份
案例2：同学将ibdata1（5.7版本）误rm掉了。导致只剩下test库下的ibd 和 frm文件了。备份坏
的。
Undo tablespace(Undo表空间)
- 介绍
作用： 用来作撤销工作。
存储位置： 5.7版本，默认存储在共享表空间中（ibdataN）。8.0版本以后默认就是独立的
（undo_001-undo_002）。
生产建议： 5.7版本后，将undo手工进行独立。
- undo 表空间管理
1. 如何查看undo的配置参数
SELECT @@innodb_undo_tablespaces; ---->3-5个 #打开独立undo模式，并设置undo的
个数。
SELECT @@innodb_max_undo_log_size; #undo日志的大小，默认1G。
SELECT @@innodb_undo_log_truncate; #开启undo自动回收的机制
（undo_purge）。
SELECT @@innodb_purge_rseg_truncate_frequency; #触发自动回收的条件，单位是检测
次数。
2. 配置undo表空间
#########官方文档说明############
Important
The number of undo tablespaces can only be configured
when initializing a MySQL instance and is fixed for the life of the
instance.
#################################
[root@db01 tmp]# /etc/init.d/mysqld stop
[root@db01 tmp]# rm -rf /data/3306/data/*
vim /etc/my.cnf
3. 添加参数
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
4. 重新初始化数据库生效
[root@db01 data]# mysqld --initialize-insecure --user=mysql --
basedir=/data/app/mysql --datadir=/data/3306/data
5. 启动数据库
[root@db01 data]# /etc/init.d/mysqld start
[root@db01 data]# ll /data/3306/data/undo00*
-rw-r----- 1 mysql mysql 10485760 May 7 15:39 /data/3306/data/undo001
-rw-r----- 1 mysql mysql 10485760 May 7 15:39 /data/3306/data/undo002
-rw-r----- 1 mysql mysql 10485760 May 7 15:39 /data/3306/data/undo003
### 注： 8.0 undo表空间与5.7稍有区别，可参考：
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html
1. 添加UNDO
CREATE UNDO TABLESPACE oldguo ADD DATAFILE 'oldguo.ibu';
2. 查看
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE
FILE_TYPE LIKE 'UNDO LOG';
3. 删除undo
ALTER UNDO TABLESPACE oldguo SET INACTIVE;
DROP UNDO TABLESPACE oldguo;
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE
FILE_TYPE LIKE 'UNDO LOG';
说明： 关于UNDO回收策略
SELECT @@innodb_purge_rseg_truncate_frequency;
tmp tablespace(临时表空间)
1. 作用： 存储临时表。
2. tmp表空间管理：
innodb_temp_data_file_path=ibtmp1:128M;ibtmp2:128M:autoextend:max:500M
重启生效。
3. 建议数据初始化之前设定好，一般2-3个，大小512M-1G。
段
表段
undo段
extent(区)
一个区默认64个连续数据页。默认值是1M空间。
区也可以被称之为“簇”。也是聚簇索引中的分配单元，通常也是read-ahead的单元。
页
默认16KB。
- 通用结构
Fil Header
Page Header
Infimum + Supremum Records
Page Directory
Fil Trailer
image.png
- 数据部分
User Records
Free Space
-
行格式
image.png - 行格式介绍
MySQL目前有4种行格式：Redundant、Compact、Dynamic、Compressed
Version 5.6 已经默认使用 Compact
Version 5.7+ 默认使用Dynamic
- 行格式结构介绍：
1、存储单元为页（page），16KB（16384B）
2、每页至少需要存两条数据
3、每条记录都会有记录头
4、每条记录3个隐藏列（rowId，transactionId，rollPointer）
5、记录按照聚簇索引组织存储
3.格式区别：
变长字段(记录的长度)列表 + NULL列表 + 记录头信息 + 列值
变长字段（记录的长度）列表：采用1-2个字节来表示一个字段的长度
记录头信息：使用5个字节来表示，主要包含：该记录是否被删除，记录类型，下一条记录的相对偏移
量；
隐藏列： rowId，transactionId，rollPointer
Dynamic：
与Compact行格式很像，差异在于页面溢出的处理上；
Compressed：
在于Dynamic使用了压缩算法；
页溢出：
因为每页16KB，至少存储两行，所以每行大概有8KB的数据；抛开记录头信息等，大致每列超过768B就
会产生页溢出；
Compact：
1、会将溢出的数据单独放入一个页；外加20B存储额外页的信息（plus the 20-byte pointer to
the externally stored part）
2、索引可以使用前768B
Dynamic：
1、如果页溢出，则使用20B存储整个列信息(列数据都存储在溢出页上)（with the clustered
index record containing only a 20-byte pointer to the overflow page）
2、可以使用前3072B字符的索引（--innodb-large-prefix决定）
建议：
5.7+ 版本Dynamic。
建议，大字段不要存储到MySQL，最好MongDB相关数据库。
DWB（double write bufffer）
作用：
MySQL，最小IO单元page（16KB），OS中最小的IO单元是block（4KB）
为了防止出现以下问题：
mysqld process crash in the middle of a page write
DWB每次1M，2次写完。数据页再刷盘。
REDO日志
- REDO日志介绍
what?
• DML(8.0+ DDL)操作导致的页面变化，均需要记录Redo日志；
• 大部分为物理日志；
– when?
• 在页面修改完成之后，在脏页刷出磁盘之前，写入Redo日志；
• 日志先行，日志一定比数据页先写回磁盘（WAL）；
• 聚簇索引/二级索引/Undo页面修改，均需要记录Redo日志；
- redo管理
1. 作用: 记录数据页的变化。实现“前进”的功能。WAL（write ahead log），MySQL保证redo优先于数据写入磁盘。
2. 存储位置： 数据路径下，进行轮序覆盖记录日志
ib_logfile0 48M
ib_logfile1 48M
3. 管理：
3.1 查询redo log文件配置
mysql> show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name | Value |
+---------------------------+----------+
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
+---------------------------+----------+
3.2 设置
生产建议：
大小： 512M-4G
组数： 2-4组
vim /etc/my.cnf
# 添加参数：
innodb_log_file_size=100M
innodb_log_files_in_group=3
#重启生效
[root@db01 data]# /etc/init.d/mysqld restart
[root@db01 data]# ll /data/3306/data/ib_logfile*
-rw-r----- 1 mysql mysql 104857600 May 7 16:17 /data/3306/data/ib_logfile0
-rw-r----- 1 mysql mysql 104857600 May 7 16:17 /data/3306/data/ib_logfile1
-rw-r----- 1 mysql mysql 104857600 May 7 16:17 /data/3306/data/ib_logfile2
UNDO日志
– what?
• DML(8.0+ DDL)操作导致的数据记录变化，均需要将记录的前镜像写入Undo日志；
• 逻辑日志；
– when?
• DML(8.0 + DDL) 操作修改聚簇索引前，记录Undo日志(Undo日志，先于Redo日志。)；
• 二级索引记录的修改，不记录Undo日志；
• 注意：Undo页面的修改，同样需要记录Redo日志；
