【MySQL】MySQL的INNODB表空间

INNODB存储引擎的表空间：
一、系统表空间
二、File-Per-Table表空间
三、常规表空间
四、undo表空间
五、临时表空间

一、系统表空间

The system tablespace is the storage area for the InnoDB data dictionary, the doublewrite buffer, the change buffer, and undo logs. It may also contain table and index data if tables are created in the system tablespace rather than file-per-table or general tablespaces.
系统表空间保存innodb数据字典、二次写缓冲、变更缓冲和undo日志；
值得注意的是，即使启用了innodb_file_per_table，指定的每张表的表空间也只存放数据、索引和插入缓冲bitmap页，其它类的数据如undo信息、插入缓冲索引页、系统事务信息、二次写缓冲等还是存放在系统表空间中的。
则引出问题：系统表空间会不断增大，当如何？如何清理undo段？

系统表空间的文件：
| datadir | /mydata/testdb/data/ |
| innodb_data_file_path | ibdata1:12M:autoextend |

二、File-Per-Table表空间

启用如下参数：
innodb_file_per_table=ON
则表会单独存放在一个以表名命令的表空间文件中（table_name.idb）。
A file-per-table tablespace is created in an .idb data file in a schema directory under the MySQL data directory. The .ibd file is named for the table (table_name.ibd).

优势：
（1）file-per-table表空间中的表truncate或drop时会回收磁盘空间给到操作系统，但共享表空间中的表truncate或drop时仅会释放空闲空间给innodb数据使用，而不会回收到操作系统层面。
（2）复制表时更节省空间
（3）file-per-table表空间中的表truncate时其性能更好
（4）更方便管理

劣势：
（1）可能浪费空间；
（2）同步（fsync）要在多个file-per-table表中执行，而非原来的一个系统表空间数据文件中执行，则会增多fsync次数；
（3）需要为每一个单独的表空间分配一个打开文件句柄，如果表空间太多则会影响性能。
（4）增加了文件描述符的需求，每一个数据文件都需要文件描述符；
（5）可能会有更多碎片（但若是对碎片进行管理就会提升性能）；
（6）当删除file-per-table表空间中的表时，对于大型缓冲池而言，扫描缓冲池可能需要几秒时间，而扫描缓冲池会产生一个广泛的内部锁，这可能影响（延迟）其它操作。
（7）file-per-table表空间是按每次4MB的大小来自动扩展的，而不同于系统表空间是由参数innodb_autoextend_increment来控制其增长大小的。
root (none)>show global variables like ‘%innodb_autoextend_increment%’;
±----------------------------±------+
| Variable_name | Value |
±----------------------------±------+
| innodb_autoextend_increment | 64 |
±----------------------------±------+
1 row in set (0.04 sec)

三、常规表空间

用create tablespace生成的共享表空间，同oracle的普通表空间。

四、undo表空间

root my1>show global variables like ‘%undo%’;
±-------------------------±-----------+
| Variable_name | Value |
±-------------------------±-----------+
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 | ----deprecated
| innodb_undo_tablespaces | 0 | ----deprecated
±-------------------------±-----------+
5 rows in set (0.03 sec)

innodb_undo_tablespaces
为0表示：undo使用系统表空间，即ibdata1
不为0表示：使用独立的表空间，一般名称为 undo001，undo002，存放地址的配置项为：innodb_undo_directory，即默认是datadir。

innodb_undo_tablespaces is deprecated and will be removed in a future release.
innodb_undo_logs :
innodb_undo_logs is deprecated and will be removed in a future release.
Defines the number of rollback segments used by InnoDB. The innodb_undo_logs option is an alias for innodb_rollback_segments. For more information, see the description of innodb_rollback_segments.

root my1>show global variables like ‘%rollback%’;
±---------------------------±------+
| Variable_name | Value |
±---------------------------±------+
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
±---------------------------±------+
2 rows in set (0.00 sec)

innodb_rollback_segments ：
One rollback segment is always assigned to the system tablespace, and 32 rollback segments are reserved for use by temporary tables and reside in the temporary tablespace (ibtmp1). To allocate additional rollback segment, innodb_rollback_segments must be set to a value greater than 33. If you configure separate undo tablespaces, the rollback segment in the system tablespace is rendered inactive.
When innodb_rollback_segments is set to 32 or less, InnoDB assigns one rollback segment to the system tablespace and 32 to the temporary tablespace.
When innodb_rollback_segments is set to a value greater than 32, InnoDB assigns one rollback segment to the system tablespace, 32 to the temporary tablespace, and additional rollback segments to undo tablespaces, if present. If undo tablespaces are not present, additional rollback segments are assigned to the system tablespace.
Although you can increase or decrease the number of rollback segments used by InnoDB, the number of rollback segments physically present in the system never decreases. Thus, you might start with a low value and gradually increase it to avoid allocating rollback segments that are not required. The innodb_rollback_segments default and maximum value is 128.
For related information, see Section 14.3, “InnoDB Multi-Versioning”. For information about configuring separate undo tablespaces, see Section 14.6.3.4, “Undo Tablespaces”.

innodb_undo_log_truncate
Property Value
Command-Line Format --innodb-undo-log-truncate[={OFF|ON}]
System Variable innodb_undo_log_truncate
Scope Global
Dynamic Yes
Type Boolean
Default Value OFF
When enabled, undo tablespaces that exceed the threshold value defined by innodb_max_undo_log_size are marked for truncation. Only undo tablespaces can be truncated. Truncating undo logs that reside in the system tablespace is not supported. For truncation to occur, there must be at least two undo tablespaces and two redo-enabled undo logs configured to use undo tablespaces. This means that innodb_undo_tablespaces must be set to a value equal to or greater than 2, and innodb_rollback_segments must set to a value equal to or greater than 35.
The innodb_purge_rseg_truncate_frequency variable can be used to expedite truncation of undo tablespaces.
For more information, see Truncating Undo Tablespaces.

注意：
（1）undo是逻辑日志，所以应用undo也只是将数据库逻辑地恢复到原来的样子，则数据结构和页本身在回滚之后可能不大相同。
（2）事务在undo log segment分配页并写入undo log时会产生redo log，因为undo log也需要持久性的保护。
（3）每个回滚段（undo segment）记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请。
InnoDB支持最大128个回滚段，故其支持同时在线的事务限制为128*1024个。
（4）当事务提交后，InnoDB会做两件事：
i）将undo log放入列表中，以供之后purge操作
ii）判断undo log所在的页是否可以重用，若可以则分配给下个事务使用。
（5）事务提交后不能马上删除undo log及undo log所在的页，因为可能还有其它事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放到一个链表中，是否可以最终删除undo log及undo log所在页由purge线程来判断。

五、临时表空间

见【MySQL】MySQL临时表空间