show variables like '%undo%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_max_undo_log_size | 8589934592 |   
| innodb_undo_directory    | ./         |  
| innodb_undo_log_encrypt  | OFF        |
| innodb_undo_log_truncate | ON         |
| innodb_undo_tablespaces  | 2          |
+--------------------------+------------+

show variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128   |
| innodb_undo_log_truncate             | ON    |
+--------------------------------------+-------+

show variables like '%segment%';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_rollback_segments      | 128       |
| innodb_segment_reserve_factor | 12.500000 |
+-------------------------------+-----------+

innodb_undo_log_truncate			-- 控制是否自動做UNDO的truncate收縮操作,默認為ON,只有為ON時,下面2個參數(shù)才生效
	innodb_max_undo_log_size		-- 控制UNDO做truncate收縮操作的閾值,當(dāng)UNDO達到該值時才出發(fā)收縮操作
	innodb_purge_rseg_truncate_frequency 
		-- Batch UNDO清理的次數(shù),默認最大值128,也就是128次后才會觸發(fā)一次UNDO的truncate,而每次清理的undo page由innodb_purge_batch_size參數(shù)決定,innodb_purge_batch_size默認為300,也就是300*128個UNDO小批次清理后才會觸發(fā)UNDO表空間的truncate(也就是UNDO表空間的收縮)操作

innodb_undo_tablespaces 		  -- 控制生成的UNDO表空間的數(shù)量,默認2個,在8.0對該參數(shù)做了廢棄,但并未提供其他參數(shù)控制UNDO數(shù)量,當(dāng)前依舊可以使用該參數(shù)做UNDO表空間數(shù)量配置,通常建議配置為3(手工收縮UNDO時需要至少3個UNDO表空間)

innodb_rollback_segments			-- UNDO表空間回滾段的數(shù)量,默認為最大值128

-- 可以查看到undo的表空間名稱/文件路徑/初始大小/擴展大小/磁盤文件大小/可用空間及是否啟用的狀態(tài)等
SELECT T1.SPACE AS SPACE_ID,
       T1.NAME AS TABLESPACE_NAME,
       T2.FILE_NAME,
       ROUND(T2.INITIAL_SIZE / 1024 / 1024, 2) AS "INITIAL_SIZE(M)",
       ROUND(T2.AUTOEXTEND_SIZE / 1024 / 1024, 2) AS "AUTOEXTEND_SIZE(M)",
       ROUND(T1.FILE_SIZE / 1024 / 1024, 2) AS "FILE_SIZE_DISK(M)",
       ROUND(T2.DATA_FREE / 1024 / 1024, 2) AS "DATA_FREE(M)",
       T2.STATUS,
       T1.STATE
  FROM INFORMATION_SCHEMA.INNODB_TABLESPACES T1,
       INFORMATION_SCHEMA.FILES              T2
 WHERE T1.SPACE = T2.FILE_ID
   AND T1.ROW_FORMAT = 'Undo';

-- 創(chuàng)建一個新的UNDO表空間
CREATE UNDO TABLESPACE undo_004 ADD DATAFILE 'undo_004.ibu';

-- 可以用前面的命令查看創(chuàng)建后的狀態(tài)

-- 可以將已有的UNDO表示為inactive(也可理解為UNDO表空間收縮)
-- PS:設(shè)置為INACTIVE的表空間的STATE為empty,表示這個表空間不包含任何事務(wù)回滾數(shù)據(jù),且表空間也收縮為默認大小
ALTER UNDO TABLESPACE undo_003 SET INACTIVE;

-- 可以將inactive的UNDO轉(zhuǎn)為active
ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;

-- 可以將inactive的UNDO表空間進行刪除
-- PS:默認以innodb_開頭初始化的undo表空間不可被刪除
DROP UNDO TABLESPACE innodb_undo_001;
ERROR: 3119 (42000): InnoDB: Tablespace names starting with `innodb_` are reserved.

-- 非系統(tǒng)默認的UNDO在inactive后可被刪除
ALTER UNDO TABLESPACE undo_003 SET ACTIVE;
Query OK, 0 rows affected (0.0030 sec)

-- 可以使用以下命令開啟對UNDO的監(jiān)控采集
SET GLOBAL innodb_monitor_enable=module_undo;
SET GLOBAL innodb_monitor_enable=module_purge;

-- 使用該命令查看UNDO truncate的次數(shù)及耗時等信息
SELECT NAME,SUBSYSTEM,COUNT,STATUS,COMMENT 
FROM INFORMATION_SCHEMA.INNODB_METRICS 
WHERE NAME LIKE '%truncate%';

SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 4     |  -- 總共的UNDO表空間數(shù)量
| Innodb_undo_tablespaces_implicit | 2     |  -- 這里implicit其實表示的初始化創(chuàng)建的默認UNDO表空間個數(shù),這種UNDO不可被刪除
| Innodb_undo_tablespaces_explicit | 2     |  -- 這里explicit其實表示手工顯式創(chuàng)建的UNDO表空間的個數(shù)
| Innodb_undo_tablespaces_active   | 4     |	-- 表示處于active的UNDO表空間的個數(shù),可以看到當(dāng)前和total一樣,說明都在使用
+----------------------------------+-------+

select 16*1024/16*128*2;
+------------------+
| 16*1024/16*128*2 |
+------------------+
|      262144.0000 |
+------------------+

select 16*1024/16/2*128*2;
+--------------------+
| 16*1024/16/2*128*2 |
+--------------------+
|    131072.00000000 |
+--------------------+

select 16*1024/16*128;
+----------------+
| 16*1024/16*128 |
+----------------+
|    131072.0000 |
+----------------+

select 16*1024/16/2*128;
+------------------+
| 16*1024/16/2*128 |
+------------------+
|   65536.00000000 |
+------------------+