InnoDB存储引擎表空间
46
|
0
|
mysql存储引擎,mysql相关
|
doraqiqi

3377 字
|
17 分钟

1. 行，页，区，段

InnoDB存储引擎采用分层的存储结构，包括段（Segment）、区（Extent）、页（Page）和行（Row）。每一层都有特定的功能和作用，共同保证了数据的高效存储和检索。下面我们详细介绍每一个层次的具体内容。

1.1 行 (Row)

  • 定义：行是数据库表中最基本的存储单位，表示表中的一条记录。
  • 结构：
  • 事务ID（Trx id）：标识最近修改该行的事务ID，用于多版本并发控制（MVCC）。
  • 回滚指针（Roll Pointer）：指向旧版本数据，用于支持事务回滚和MVCC。
  • 列数据（Columns）：存储实际的数据值，每列对应表中的一个字段。
  • 存储位置：行数据存储在页中，每一页可以存储多行数据。
  • 示例：在图中，行显示为一个包含多个字段的结构，其中包括Trx id和Roll Pointer，这些字段用于事务处理和多版本并发控制（MVCC）。

1.2 页 (Page)

  • 定义：页是InnoDB存储引擎中最小的存储单元，每个页的大小为16KB。每页最多存放7992行数据。
  • 类型：
  • 数据页（Data Page）：存储表的数据行。
  • 索引页（Index Page）：存储B+树索引的节点。
  • Undo日志页（Undo Log Page）：存储Undo日志，用于事务回滚和MVCC。
  • 结构：
  • 页头（Page Header）：包含页类型、页ID等元数据。
  • 页数据（Page Data）：实际存储的数据行或索引项。
  • 页尾（Page Trailer）：包含校验信息等。
  • 作用：页用于存储实际的数据或索引项，每个页可以包含多个行。
  • 示例：在图中，页显示为一个包含多个行的矩阵结构，每个小方格代表一行数据。

1.3 区 (Extent)

  • 定义：区是InnoDB表空间中的分配单元，每个区通常包含64个连续的页。
  • 大小：默认情况下，每个区的大小为1MB。
  • 类型：
  • 段区（Segment Extent）：分配给特定段使用的区。
  • 自由区（Free Extent）：尚未分配给任何段的空闲区。
  • 作用：区用于减少存储管理的碎片化，并提高I/O性能。
  • 管理：
  • 空间分配：通过段管理器进行区的分配和释放。
  • 合并和分割：根据使用情况进行区的合并和分割，优化空间利用率。
  • 示例：在图中，区显示为一个包含多个页的矩阵结构，多个区组成了一个段。

1.4 段 (Segment)

  • 定义：段是InnoDB用于存储数据或索引的逻辑结构。每个表的数据和索引分别存储在不同的段中。
  • 类型：
  • 数据段（Data Segment）：存储表的数据行。
  • 索引段（Index Segment）：存储B+树索引的节点。
  • 回滚段（Rollback Segment）：存储Undo日志，用于事务回滚和MVCC。
  • 管理：
  • 段头（Segment Header）：包含段类型、段ID等元数据。
  • 段区列表（Extent List）：记录属于该段的所有区。
  • 作用：段管理多个区，每个段可以包含一个或多个区。
  • 示例：在图中，段显示为一个包含多个区的结构，包括叶节点段（Leaf node segment）、非叶节点段（Non-Leaf node segment）和回滚段（Rollback segment）。

2. 共享表空间和独立表空间

InnoDB存储引擎支持两种表空间模式：共享表空间（System Tablespace）和独立表空间（File-Per-Table Tablespace）。这两种模式在数据存储和管理上有不同的特点和适用场景。

2.1. 共享表空间 (System Tablespace)

定义：共享表空间是InnoDB的默认表空间模式，所有表的数据和索引存储在一个或多个共享表空间文件中（例如ibdata1）。

结构：

  • 单个文件：所有表的数据和索引都存储在同一个或多个文件中。
  • 元数据存储：表空间中还包含元数据、数据字典、双写缓冲区（Doublewrite Buffer）、以及Undo日志。

优点：

  • 管理方便：集中管理所有表的数据和索引文件。
  • 简化备份：可以通过备份少量文件来备份整个数据库。

缺点：

  • 文件膨胀：当表的数据量增加时，共享表空间文件会不断增大，容易导致磁盘空间管理复杂。
  • 碎片问题：删除数据后，文件空间不会自动释放，容易产生碎片。
  • 恢复困难：如果一个共享表空间文件损坏，可能会影响到多个表的数据。

适用场景：适用于小型数据库或数据量较少的应用场景。

2.2. 独立表空间 (File-Per-Table Tablespace)

定义：独立表空间模式下，每个表的数据和索引存储在独立的表空间文件中（例如*.ibd文件）。

结构：

  • 单独文件：每个表有独立的表空间文件，通常以*.ibd为扩展名。
  • 数据和索引：表的数据和索引都存储在这个独立文件中。

优点：

  • 空间管理：删除表时可以直接释放对应的表空间文件，减少碎片。
  • 备份和恢复：可以单独备份和恢复某个表的表空间文件，提高灵活性。
  • 优化性能：在高并发写入场景下，可以减小锁争用，提高性能。

缺点：

  • 文件数量增加：对于大量表的数据库，文件系统管理的文件数量会显著增加。
  • 复杂管理：需要管理更多的文件，可能增加运维复杂度。

适用场景：适用于大中型数据库或需要频繁增删表的应用场景。

2.3. 配置和启用独立表空间

启用独立表空间模式：
在MySQL配置文件my.cnf或my.ini中添加以下配置：

[mysqld]
innodb_file_per_table=1

创建表：
当启用独立表空间模式后，创建新表时会自动在单独的文件中存储数据和索引：

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

总结

  • 共享表空间：适用于小型数据库，管理方便，但文件膨胀和碎片问题可能较严重。
  • 独立表空间：适用于大中型数据库，灵活性高，空间管理更有效，但文件数量增多，需要更复杂的管理。

3. 实验：独立表空间迁移和用mysqldump迁移数据比较

直接导出表结构和数据与拷贝.ibd文件有一些关键区别。下面是两种方法的详细比较：

3.1. 导出表结构和数据

步骤：

  1. 使用mysqldump导出表结构：
mysqldump -u root -p --no-data database_name test_table > test_table_structure.sql
  1. 使用mysqldump导出表数据：
mysqldump -u root -p --no-create-info database_name test_table > test_table_data.sql
  1. 在目标数据库中导入表结构：
mysql -u root -p database_name < test_table_structure.sql
  1. 在目标数据库中导入表数据：
mysql -u root -p database_name < test_table_data.sql

优点：

  • 一致性保障：mysqldump工具会锁表（根据配置，可以是读锁或写锁），确保导出过程中数据的一致性。
  • 灵活性高：可以选择只导出结构或只导出数据，也可以对数据进行筛选。
  • 易用性：导出的文件是标准的SQL脚本，易于阅读和编辑。
  • 跨平台支持：可以在不同的MySQL版本或不同的平台之间迁移数据。

缺点：

  • 导出和导入时间长：对于大表，导出和导入过程可能非常耗时。
  • 性能开销：在导出过程中，尤其是对大表进行全表扫描，会产生较高的I/O和CPU开销。
  • 文件大小大：导出的SQL文件可能非常大，占用大量磁盘空间。

3.2. 复制.ibd文件

步骤：

  1. 确保源表已使用独立表空间（innodb_file_per_table=1）。
  2. 将表结构导出到目标数据库：
mysqldump -u root -p --no-data database_name test_table > test_table_structure.sql
mysql -u root -p database_name < test_table_structure.sql
  1. 在源数据库中锁定表，并且将表的.ibd文件脱机（flush + table discard）：
FLUSH TABLES test_table WITH READ LOCK;
ALTER TABLE test_table DISCARD TABLESPACE;
  1. 复制源数据库的数据目录中的test_table.ibd文件到目标数据库的数据目录中。
  2. 在目标数据库中接收.ibd文件：
ALTER TABLE test_table IMPORT TABLESPACE;
  1. 解锁表：
UNLOCK TABLES;

优点：

  • 速度快：直接复制文件，速度通常比通过mysqldump导出和导入要快得多。
  • 低开销：避免了全表扫描和SQL解析，减少了CPU和I/O开销。
  • 空间节省：直接复制文件，避免了中间的临时文件占用空间。

缺点：

  • 一致性风险：如果在复制过程中未能正确锁定表或未能确保数据的一致性，可能会导致数据损坏。
  • 复杂性高：需要对表进行锁定和脱机操作，并且在操作不当时可能会影响数据库的正常运行。
  • 平台限制：通常只在相同的MySQL版本和相同的平台之间复制文件才可靠。
  • 文件系统依赖：需要确保目标文件系统和源文件系统兼容。

3.3. 两种迁移方法对比

  • 导出表结构和数据适用于需要跨平台或跨MySQL版本迁移的场景，数据一致性有保障，但可能会耗时较长。
  • 复制.ibd文件适用于相同MySQL版本和平台之间的数据迁移，速度快、开销低，但操作复杂且存在一致性风险。

在选择方法时，需要根据具体的使用场景、数据量和对数据一致性的要求来决定。

为了在独立表空间中分离Undo日志，需要对MySQL配置文件进行相关设置，并确保操作步骤正确。以下是具体的设置步骤和配置说明：

4. 分离Undo日志到独立表空间

4.1. 修改MySQL配置文件

在MySQL的配置文件my.cnf或my.ini中添加或修改以下配置项：

[mysqld]
# 启用独立表空间
innodb_file_per_table=1

# 设置Undo表空间数量
innodb_undo_tablespaces=4

# 指定Undo表空间目录
innodb_undo_directory=/var/lib/mysql/undo

# 启用自动收缩Undo日志
innodb_undo_log_truncate=1

# 设置单个Undo日志文件的最大大小
innodb_max_undo_log_size=1G

# 设置Undo日志收缩频率
innodb_purge_rseg_truncate_frequency=128
  • innodb_file_per_table=1：启用独立表空间模式。
  • innodb_undo_tablespaces=4：指定Undo表空间的数量。
  • innodb_undo_directory：指定Undo表空间文件存储的目录。
  • innodb_undo_log_truncate=1：启用自动收缩Undo日志。
  • innodb_max_undo_log_size=1G：设置单个Undo日志文件的最大大小。
  • innodb_purge_rseg_truncate_frequency=128：设置Undo日志收缩的频率。

4.2. 创建Undo表空间目录

确保配置文件中指定的Undo表空间目录存在，并具有适当的权限。

mkdir -p /var/lib/mysql/undo
chown mysql:mysql /var/lib/mysql/undo

4.3. 重启MySQL服务

使配置生效并创建Undo表空间文件。

systemctl restart mysql

4.4. 查看Undo表空间状态

通过以下SQL命令查看Undo表空间的状态，以确保配置正确。

SHOW VARIABLES LIKE 'innodb_undo_tablespaces';

4.5. 监控Undo日志收缩

通过以下命令监控Undo日志的收缩情况，确保Undo日志的分离和自动收缩功能正常工作。

SELECT * FROM information_schema.innodb_tablespaces WHERE SPACE_TYPE='Undo';

5. 实验：在线自动收缩Undo日志

为了测试在线自动收缩Undo日志的功能，可以通过以下步骤进行实验。该实验将演示如何启用自动收缩Undo日志功能，并验证其效果。

5.1. 步骤1：启用自动收缩功能

在MySQL配置文件my.cnf或my.ini中启用自动收缩功能，并设置相关参数：

[mysqld]
# 启用独立表空间
innodb_file_per_table=1

# 设置Undo表空间数量
innodb_undo_tablespaces=4

# 指定Undo表空间目录
innodb_undo_directory=/var/lib/mysql/undo

# 启用自动收缩Undo日志
innodb_undo_log_truncate=1

# 设置单个Undo日志文件的最大大小
innodb_max_undo_log_size=1G

# 设置Undo日志收缩频率
innodb_purge_rseg_truncate_frequency=128

5.2. 步骤2：重启MySQL服务

重启MySQL服务以使配置生效，并创建Undo表空间文件：

systemctl restart mysql

5.3. 步骤3：创建测试表并插入数据

创建一个测试表，并插入大量数据以生成Undo日志：

CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
) ENGINE=InnoDB;

-- 插入大量数据
INSERT INTO test_table (data) VALUES (REPEAT('A', 255));
INSERT INTO test_table (data) SELECT REPEAT('A', 255) FROM test_table;
-- 重复插入，直到生成大量Undo日志

使用循环插入数据，确保生成大量的Undo日志：

for i in {1..10}; do
    mysql -u root -p -e "INSERT INTO test_db.test_table (data) SELECT REPEAT('A', 255) FROM test_db.test_table;"
done

5.4. 步骤4：监控Undo日志文件大小

使用系统命令查看Undo日志文件的大小变化：

ls -lh /var/lib/mysql/undo/

5.5. 步骤5：执行事务并监控收缩过程

在MySQL中执行事务并定期查询Undo日志的状态：

  1. 执行多个事务：
START TRANSACTION;
DELETE FROM test_table WHERE id BETWEEN 1 AND 1000;
COMMIT;

START TRANSACTION;
UPDATE test_table SET data = REPEAT('B', 255) WHERE id BETWEEN 1001 AND 2000;
COMMIT;
  1. 查询Undo日志的状态：
SELECT * FROM information_schema.innodb_tablespaces WHERE SPACE_TYPE='Undo';

5.6. 步骤6：验证自动收缩效果

定期检查Undo日志文件的大小变化，确保自动收缩功能正常工作：

ls -lh /var/lib/mysql/undo/

5.7. 步骤7：清理实验数据

实验完成后，可以删除测试表并清理实验数据：

DROP TABLE test_table;
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇