InnoDB体系结构---物理存储结构

8.0版本之前

ibd : 数据和索引
frm : 存私有的数据字典信息
ibdataN:系统的数据字典信息

8.0版本

ibd:数据、索引和 冗余的SDI私有数据字典信息
取消了 ibdata中的系统数据字典信息。
mysql.ibd ---> 整个系统的数据字典,不再放在ibdata1
sdi序列化的数据字典----> 每个表的表空间自行管理json格式的私有数据字典信息,用来替换frm的。

对于非InnoDB的表,会有一个xxx.sdi的文件存储数据字典信息

从.idb中获取数据字典信息

[root@db data_3306]# ibd2sdi world/t1.ibd 
["ibd2sdi"
,
{
    "type": 1,
    "id": 568,
    "object":
        {
    "mysqld_version_id": 80018,
    "dd_version": 80017,
    "sdi_version": 80016,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "t1",
        "mysql_version_id": 80018,
        "created": 20200625053725,
        "last_altered": 20200625053725,
        "hidden": 1,
        "options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "id",
                "type": 4,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": true,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 1,
                "char_length": 11,

表空间

System Tablespace(共享表空间)

存储方式

ibdata1~ibdataN, 5.5版本默认的表空间类型。

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

共享表空间的管理

扩容

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

设置方法

错误的设置方法

[root@db data_3306]# vim /etc/my.cnf 
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend

重启数据库报错,查看日志文件

[root@db data_3306]# 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;
# 利用独立表空间进行快速数据迁移
源端:3306/test/t100w -----> 目标端:3307/test/t100w

1. 锁定源端t100w表
mysql> lock tables test.t100w read;

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. 单独删除空的表空间文件(保留t100w的frm,ibdata1中关于t100w的系统数据) 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;

Undo tablespace 撤销表空间

1. 作用: 用来作撤销工作。
2. 存储位置: 5.7版本,默认存储在共享表空间中(ibdataN)。8.0版本以后默认就是独立的
(undo_001-undo_002)。
3. 生产建议: 5.7版本后,将undo手工进行独立。
4. undo 表空间管理
4.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; #触发自动回收的条件,单位是检测
次数。

undo表空间的文件位置由变量innodb_undo_directory定义。默认为数据目录,文件的名称为 undo_001undo_002

撤消表空间数据文件的初始大小取决于该 innodb_page_size值。对于默认的16KB页面大小,初始撤消表空间文件大小为10MiB。对于4KB,8KB,32KB和64KB页面大小,初始撤消表空间文件大小分别为7MiB,8MiB,20MiB和40MiB。

配置undo表空间 8.0.14版本之前

[root@db01 tmp]# /etc/init.d/mysqld stop
[root@db01 tmp]# rm -rf /data/3306/data/*
vim /etc/my.cnf
# 添加参数
innodb_undo_tablespaces=3
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32
# 重新初始化数据库生效
[root@db01 data]# mysqld --initialize-insecure --user=mysql --
basedir=/data/app/mysql --datadir=/data/3306/data
# 启动数据库
[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.14版本之后配置undo表空间

之前必须在初始化mysql的时候定义好undo表空间。

8.0.14版本后,可以在运行时使用CREATE UNDO TABLESPACE语句动态创建undo表空间

mysql> CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';
Query OK, 0 rows affected (0.51 sec)


[root@db data_3306]# ll -h|grep file_name.ibu 
-rw-r----- 1 mysql mysql  10M Jul  4 01:09 file_name.ibu

文件名必须以 .ibu结尾。不允许指定相对路径

mysql> CREATE UNDO TABLESPACE undo_1 ADD DATAFILE 'school/file_name.ibu';
ERROR 3121 (HY000): The ADD DATAFILE filepath for an UNDO TABLESPACE cannot be a relative path.

mysql> CREATE UNDO TABLESPACE undo_1 ADD DATAFILE 'file_name';
ERROR 3121 (HY000): The ADD DATAFILE filepath must end with '.ibu'.

注意,在主从环境中,主服务器和每个从服务器必须具有自己的撤消表空间文件目录。将撤消表空间文件的创建复制到公共目录会导致文件名冲突。

查看undo表空间

mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+-----------------+
| TABLESPACE_NAME | FILE_NAME       |
+-----------------+-----------------+
| innodb_undo_001 | ./undo_001      |
| innodb_undo_002 | ./undo_002      |
| tablespace_name | ./file_name.ibu |
+-----------------+-----------------+
3 rows in set (0.01 sec)

删除undo表空间

mysql> ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> drop undo tablespace tablespace_name;
Query OK, 0 rows affected (0.00 sec)

undo表空间的回收策略

purge线程负责清除和截断undo表空间。默认情况下,purge线程查找undo表空间,以在每次清除调用被执行128次后截断一次,由参数innodb_purge_rseg_truncate_frequency控制,默认128次

mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+
1 row in set (0.00 sec)

临时表空间

1. 作用: 存储临时表。
2. 管理:
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M
重启生效。
3. 建议数据初始化之前设定好,一般2-3个,大小512M-1G

double write bufffer双写日志

在 InnoDB将页面写入InnoDB数据文件中的适当位置之前,先从缓冲池中刷新页面 。如果在页面写入过程中发生操作系统,存储子系统或mysqld进程崩溃,则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到页面的完好副本。

双写文件每次写入1M,默认开启两个文件

在MySQL 8.0.20之前,doublewrite缓冲区存储区位于InnoDB系统表空间中。从MySQL 8.0.20开始,doublewrite缓冲区存储区位于doublewrite文件中。

doublewrite控制参数

  • innodb_doublewrite 变量控制是否启用双写缓冲区,默认开启
mysql> select @@innodb_doublewrite;
+----------------------+
| @@innodb_doublewrite |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
  • 数据写入doublewrite buffer中的方法
mysql> select @@innodb_flush_method;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: *** NONE ***

+-----------------------+
| @@innodb_flush_method |
+-----------------------+
| fsync                 |
+-----------------------+
1 row in set (0.00 sec)

REDO日志

重做日志是基于磁盘的数据结构,在崩溃恢复期间用于纠正不完整事务写入的数据。在正常操作期间,重做日志对更改表数据的请求进行编码,在初始化期间以及接受连接之前,会自动重播未完成意外关闭之前未完成更新数据文件的修改。

什么时候写redo log

在页面修改完成之后,在脏页刷出磁盘之前,写入Redo日志;
日志先行,日志一定比数据页先写回磁盘(WAL);
聚簇索引/二级索引/Undo页面修改,均需要记录Redo日志;

作用

记录数据页的变化。实现“前进”的功能。WAL(write ahead log),MySQL保证redo优先于数据写入磁盘。

存储位置

数据目录下,进行轮序覆盖记录日志

-rw-r----- 1 mysql mysql  48M Jul  4 01:09 ib_logfile0
-rw-r----- 1 mysql mysql  48M Jun 22 02:32 ib_logfile1

管理redo

查询文件配置

mysql> show variables like '%innodb_log_file%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
+---------------------------+----------+
2 rows in set (0.01 sec)

生产建议设置:

  • 大小: 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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 



 

上一篇:还不懂mysql的undo log和mvcc?算我输!


下一篇:7. 事务