持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第25天，点击查看活动详情
InnoDB 磁盘结构
InnoDB磁盘主要包含Tablespaces，InnoDB Data Dictionary、Doublewrite Buffer、redo log和Undo Logs。
- Tablespaces: 表空间分为系统表空间（ibdata1文件）、临时表空间、常规表空间、Undo表空间以及file-per-table表空间。系统表空间又包括双写缓冲区（Doublewrite Buffer）、Change Buffer等
- Doublewrite buffer：innodb 将数据页写到文件之前存放的位置。8.0.20版本之前，doublewrite buffer存放在InnoDB系统表空间中，8.0.20版本后存放在doublewrite中
- Redo log：存储的是log buffer刷到磁盘的数据
- Undo log：存在于global临时表空间中，用于事务的回滚
1. 表空间 ( Tablespaces )
表空间（Tablespaces) : 用于存储表结构和数据, InnoDB表空间类型包括系统表空间、File-Per-Table表空间，常规表空间，Undo表空间，临时表空间等。
-
系统(共享)表空间（The System Tablespace）
-
共享表空间: 包含InnoDB数据字典，Doublewrite Buffer，Change Buffer，Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。
-
物理文件查看
[root@localhost ~]# cd /var/lib/mysql [root@localhost mysql]# ll ibdata* -rw-r----- 1 mysql mysql 79691776 1月 25 06:42 ibdata1 -
系统表空间是一个共享的表空间因为它是被多个表共享的。该空间的数据文件通过参数
innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)。mysql> show variables like '%innodb_data_file_path%'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+ 1 row in set (0.38 sec)
-
-
独立表空间（File-Per-Table Tablespaces）
-
默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当
innodb_file_per_table选项开启时，表将被创建于表空间中。否则，innodb将被创建于系统表空间中。mysql> show variables like '%innodb_file_per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.14 sec) -
每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于数据库目录中。
[root@localhost test1]# cd /var/lib/mysql/test1/ [root@localhost test1]# ll total 112 -rw-r----- 1 mysql mysql 8582 Dec 27 19:17 a1.frm -rw-r----- 1 mysql mysql 98304 Dec 27 19:21 a1.ibd -rw-r----- 1 mysql mysql 67 Dec 9 23:21 db.opt
-
-
两种表空间的优缺点
-
共享表空间
- 优点：可以将表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同的文件上）。数据和文件放在一起方便管理。
- 缺点：所有的数据和索引存放到一个文件中，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
-
-
独立表空间
-
优点：
- 每个表都有自已独立的表空间,每个表的数据和索引都会存在自已的表空间中。
- 可以实现单表在不同的数据库中移动(复制File-per-table表空间的对应表的数据文件到其他mysql数据库实例的表空间下，实现表的导入迁移)。
- 空间可以回收（在独立表空间下，删除或者清空表后，存储空间会立刻返回给操作系统。而在共享表空间下，表空间数据文件的大小不会缩小）
-
缺点：
- 单表增加过大，如超过100个G。
-
-
通用表空间（General Tablespaces）
- MySQL 5.7开始支持通用表空间管理功能,类似于系统表空间，也是共享表空间，可以存储多个表的数据。
- 通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间(自定义存储路径)，其可以容纳多张表，且其支持所有的行格式。
- 相比File-per-table表空间，通用表空间由于多表共享表空间，消耗的内存会更少一点，具有潜在的内存优势。（占用的磁盘空间会更小）
-
撤销表空间（Undo Tablespaces）
-
撤销表空间，用来保存回滚日志，即undo logs, undo Log 的数据默认在系统表空间ibdata1文件中
-
可以通过
innodb_undo_directory属性 查看回滚表空间的位置。默认路径是mysql的数据存储路径。mysql> show variables like 'innodb_undo_directory'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_undo_directory | ./ | +-----------------------+-------+ -
InnoDB使用的undo表空间由
innodb_undo_tablespaces配置选项控制，设置undo独立表空间个数，范围为0-128， 默认为0，0表示不开启独立undo表空间 ,且 undo日志存储在ibdata1文件中。mysql> show variables like '%innodb_undo_tablespace%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_undo_tablespaces | 0 | +-------------------------+-------+ 1 row in set (0.01 sec)什么时候需要来设置这个参数 ?
当DB写压力较大时，可以设置独立undo表空间，把undo从 ibdata文件中分离开来，指定
innodb_undo_directory目录存放，可以制定到高速磁盘上，加快undo log 的读写性能。 -
undo日志使用共享表空间存在的问题
因为共享表空间不会自动收缩,即使事务关闭,undo log也会一直占用空间, 所以可能会出现因为大事物而导致ibdata1文件过大的问题.
MySQL5.7中引入了一个新的参数
innodb_undo_log_truncate表示是否开启自动收缩undolog的表空间的操作。如果配置为
ON，并且配置了2个或2个以上的undolog表空间数据文件，当某一个日志文件大小超过设置的最大值之后，就会自动的收缩表空间数据文件。在回收表空间数据文件的时候，被回收的表空间数据文件会临时下线，为了保证undolog一直有地方可以写，此时要保证至少还有1个undolog日志文件是在线的。这就是要求
innodb_undo_tablespaces>=2的根本原因 -
8.0 对于undo log存储的进一步优化
从MySQL8.0版本开始，MySQL默认对undo进行了分离操作，也就是说，不需要在初始化中手动配置参数，默认会在datadir目录下生成两个undo表空间文件undo_001 和 undo002 并且可以在线的增加和删除undo表空间文件, 进行动态扩容和收缩.
查询undo log信息
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 | +-----------------+------------+ -- 文件位置 /var/lib/mysql [root@localhost mysql]# ll undo* -rw-r-----. 1 mysql mysql 16777216 12月 31 00:21 undo_001 -rw-r-----. 1 mysql mysql 16777216 12月 31 00:21 undo_002
-
-
临时表空间（Temporary Tablespaces）
-
用户创建的临时表和磁盘内部临时表创建于共享临时表空间中。MySQL 5.7起，开始采用独立的临时表空间,命名ibtmp1文件，初始化12M，且默认无上限。
-
全局临时表空间默认是数据目录的ibtmp1文件，所有临时表共享,可以通过
innodb_temp_data_file_path属性指定临时表空间的位置。mysql> select @@innodb_temp_data_file_path; +------------------------------+ | @@innodb_temp_data_file_path | +------------------------------+ | ibtmp1:12M:autoextend | +------------------------------+ 1 row in set (0.00 sec)需要注意的是: 临时表空间最好是设置最大增长限制,否则可能会导致 ibtmp1文件过大,占用过多的磁盘空间.
-- 设置了上限的大小，当数据文件达到最大大小时，查询将失败，并显示一条错误消息，表明表已满，查询不能往下执行，避免 ibtmp1 过大 (需要生成临时表的SQL无法被执行,一般这种SQL效率也比较低，可借此机会进行优化) innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M -
tmp_table_size参数配置内部内存临时表的大小。mysql> set global tmp_table_size=16*1024*1024; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'tmp_table_size'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.00 sec)注: 如果内存中的临时表超出限制，MySQL自动将其转换为磁盘上的MyISAM表tmp_table_size最大值是18446744073709551615
-
如何监控临时表与临时表空间使用情况
mysql> show status like '%tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 6 | | Created_tmp_tables | 11 | +-------------------------+-------+ 3 rows in set (0.00 sec)建议Created_tmp_disk_tables / Created_tmp_tables不要超过25%。如果Created_tmp_disk_tables数量很大，查看是否有很多慢sql，是否有很多使用临时表的语句。加大tmp_table_size的值。
还可以选择择机重启实例，释放 ibtmp1 文件，和 ibdata1 不同，ibtmp1 重启时会被重新初始化而 ibdata1 则不可以
-
