战略网页游戏开服表,北京网站seo外包,济南建站公司模板,网页设计制作网站模板免费文章目录 MySQL 8.0 InnoDB Tablespaces之Undo Tablespaces#xff08;UNDO表空间#xff09;Undo Tablespaces#xff08;UNDO表空间#xff09;默认UNDO表空间添加 Undo 表空间查看Undo 相关的信息查看Undo 相关参数变量查看Undo 状态信息通过information_schema.innodb_… 文章目录 MySQL 8.0 InnoDB Tablespaces之Undo TablespacesUNDO表空间Undo TablespacesUNDO表空间默认UNDO表空间添加 Undo 表空间查看Undo 相关的信息查看Undo 相关参数变量查看Undo 状态信息通过information_schema.innodb_tablespaces 查看undo表空间信息通过information_schema.FILES查看undo表空间的数据文件信息undo表空间的状态 参考 【免责声明】文章仅供学习交流观点代表个人与任何公司无关。 编辑|SQL和数据库技术(ID:SQLplusDB) MySQL 8.0 OCP (1Z0-908) 考点概要
MySQL 8.0 OCP (1Z0-908) 考点精析-安装与配置考点1设置系统变量 【MySQL】控制MySQL优化器行为方法之optimizer_switch系统变量 【MySQL】MySQL系统变量system variables列表mysqld --verbose --help的结果例 【MySQL】MySQL系统变量system variables列表SHOW VARIABLES 的结果例 MySQL 8.0 OCP (1Z0-908) 考点精析-备份与恢复考点1MySQL Enterprise Backup概要 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点1sys.statement_analysis视图 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点2系统变量的确认 MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点3EXPLAIN ANALYZE MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点4慢速查询日志slow query log MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点5表连接算法join algorithm MySQL 8.0 OCP (1Z0-908) 考点精析-性能优化考点6MySQL Enterprise Monitor之Query Analyzer MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点1二进制日志文件Binary log MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点5数据字典(Data Dictionary) MySQL 8.0 OCP (1Z0-908) 考点精析-架构考点6InnoDB Tablespaces之系统表空间System Tablespace MySQL 8.0 InnoDB Tablespaces之File-per-table tablespaces单独表空间 MySQL 8.0 InnoDB Tablespaces之General Tablespaces通用表空间/一般表空间
MySQL 8.0 InnoDB Tablespaces之Undo TablespacesUNDO表空间
InnoDB表空间是MySQL中用于存储InnoDB存储引擎表数据和索引的物理文件。 InnoDB表空间根据用途可以分成多种类型
数据表空间: System tablespace系统表空间File-per-table tablespaces单独表空间General tablespaces通用表空间/一般表空间 Undo 表空间临时表空间Temporary table tablespaces
Undo TablespacesUNDO表空间
UNDO日志Undo Log主要用于事务异常时的数据回滚在磁盘上UNDO日志保存在UNDO表空间中。
默认UNDO表空间
MySQL实例初始化时会创建两个默认UNDO表空间默认UNDO表空间会创建在innodb_undo_directory变量定义的位置。 默认UNDO表空间的数据文件命名为undo_001和undo_002在数据字典中定义的对应UNDO表空间名称为innodb_undo_001和innodb_undo_002。
例
mysql show variables like datadir;
--------------------------------
| Variable_name | Value |
--------------------------------
| datadir | /var/lib/mysql/ |
--------------------------------
1 row in set (0.00 sec)mysql show variables like innodb_undo_directory;
------------------------------
| Variable_name | Value |
------------------------------
| innodb_undo_directory | ./ |
------------------------------
1 row in set (0.00 sec)mysql SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE,AUTOEXTEND_SIZE/1024/1024 size_m- FROM INFORMATION_SCHEMA.FILES- WHERE FILE_TYPE LIKE UNDO LOG;
-----------------------------------------------------
| TABLESPACE_NAME | FILE_NAME | FILE_TYPE | size_m |
-----------------------------------------------------
| innodb_undo_001 | ./undo_001 | UNDO LOG | 16.00000000 |
| innodb_undo_002 | ./undo_002 | UNDO LOG | 16.00000000 |
-----------------------------------------------------
2 rows in set (0.01 sec)mysql查看数据文件。
rootmysql-vm:/var/lib/mysql# ls -lh undo*
-rw-r----- 1 mysql mysql 16M Dec 28 17:09 undo_001
-rw-r----- 1 mysql mysql 16M Dec 28 17:09 undo_002
rootmysql-vm:/var/lib/mysql#MySQL 8.0.23开始初始UNDO表空间大小通常为16MBUNDO表空间的扩展大小至少为16MB。
添加 Undo 表空间
由于 Undo 日志会在长时间运行的事务中变得很大创建额外的 Undo 表空间可以避免单个表空间变得太大。MySQL 8.0.14 版本之后可以在运行时使用CREATE UNDO TABLESPACE语法创建额外的UNDO 表空间。
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE file_name.ibu;例
mysql CREATE UNDO TABLESPACE undo_tbs_1 ADD DATAFILE undo_tbs_1.ibu;
Query OK, 0 rows affected (0.26 sec)mysql SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE,AUTOEXTEND_SIZE/1024/1024 size_m- FROM INFORMATION_SCHEMA.FILES- WHERE FILE_TYPE LIKE UNDO LOG;
-----------------------------------------------------------
| TABLESPACE_NAME | FILE_NAME | FILE_TYPE | size_m |
-----------------------------------------------------------
| innodb_undo_001 | ./undo_001 | UNDO LOG | 16.00000000 |
| innodb_undo_002 | ./undo_002 | UNDO LOG | 16.00000000 |
| undo_tbs_1 | ./undo_tbs_1.ibu | UNDO LOG | 16.00000000 |
-----------------------------------------------------------
3 rows in set (0.00 sec)mysql注意创建创建额外的Undo 表空间的数据文件的扩展名必须使用 .ibu 。
查看Undo 相关的信息
查看Undo 相关参数变量
Undo 相关的参数变量主要包括如下内容。
例
mysql show variables like %undo%;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| innodb_max_undo_log_size | 1073741824 |
| innodb_undo_directory | ./ |
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
--------------------------------------回滚段rollback segment相关参数。
mysql show variables like %rollback%- ;
-----------------------------------
| Variable_name | Value |
-----------------------------------
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
-----------------------------------
2 rows in set (0.00 sec)mysqlmysql show variables like innodb_purge_rseg_truncate_frequency;
---------------------------------------------
| Variable_name | Value |
---------------------------------------------
| innodb_purge_rseg_truncate_frequency | 128 |
---------------------------------------------
1 row in set (0.01 sec)
查看Undo 状态信息
通过information_schema.innodb_tablespaces 查看undo表空间信息
mysql select * from information_schema.innodb_tablespaces where name like %undo% \G
*************************** 1. row ***************************SPACE: 4294967279NAME: innodb_undo_001FLAG: 0ROW_FORMAT: UndoPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: UndoFS_BLOCK_SIZE: 4096FILE_SIZE: 16777216ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.32SPACE_VERSION: 1ENCRYPTION: NSTATE: active
*************************** 2. row ***************************SPACE: 4294967278NAME: innodb_undo_002FLAG: 0ROW_FORMAT: UndoPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: UndoFS_BLOCK_SIZE: 4096FILE_SIZE: 16777216ALLOCATED_SIZE: 16777216
AUTOEXTEND_SIZE: 0SERVER_VERSION: 8.0.32SPACE_VERSION: 1ENCRYPTION: NSTATE: active
2 rows in set (0.00 sec)mysql
UNDO表空间的使用状态
mysql SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES- WHERE NAME in (innodb_undo_001,innodb_undo_002)\G
*************************** 1. row ***************************NAME: innodb_undo_001
STATE: active
*************************** 2. row ***************************NAME: innodb_undo_002
STATE: active
2 rows in set (0.00 sec)mysql通过information_schema.FILES查看undo表空间的数据文件信息
mysql SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE FROM INFORMATION_SCHEMA.FILES- WHERE FILE_TYPE LIKE UNDO LOG;
----------------------------------------
| TABLESPACE_NAME | FILE_NAME | FILE_TYPE |
----------------------------------------
| innodb_undo_001 | ./undo_001 | UNDO LOG |
| innodb_undo_002 | ./undo_002 | UNDO LOG |
----------------------------------------
2 rows in set (0.00 sec)mysqlundo表空间的状态
undo表空间的状态相关的信息。
例
mysql SHOW STATUS LIKE %undo%;
-----------------------------------------
| Variable_name | Value |
-----------------------------------------
| Innodb_undo_tablespaces_total | 2 |
| Innodb_undo_tablespaces_implicit | 2 |
| Innodb_undo_tablespaces_explicit | 0 |
| Innodb_undo_tablespaces_active | 2 |
-----------------------------------------
4 rows in set (0.00 sec)mysqlInnodb_undo_tablespaces_total 总的UNDO表空间数量 Innodb_undo_tablespaces_implicit 隐式InnoDB创建的UNDO表空间数量 Innodb_undo_tablespaces_explicit 显式用户创建的UNDO表空间数量 Innodb_undo_tablespaces_active 活动UNDO表空间数量
参考
15.6.3.4 Undo Tablespaces https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html
https://www.alibabacloud.com/blog/an-in-depth-analysis-of-undo-logs-in-innodb_598966
https://blog.csdn.net/wudi53433927/article/details/127206842
15.6.6 Undo Logs https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html
