当心批量插入失败或者回滚时带来的 MySQL 表碎片,通常,DBA 都了解使用 DELETE 语句会产生表碎片。在大多数情况下,当执行大量的删除时,DBA 总会重新构建表以回收磁盘空间。但是,您是否认为只有删除才会导致表碎片?(答案:并不是)。
在这篇博文中,我将解释插入如何会带来碎片。
在讨论这个主题之前,我们需要了解 MySQL,有两种碎片:
- 在表中的 InnoDB 页完全空闲引起的碎片。
- InnoDB 页未填充满(页中还有一些空闲空间)引起的碎片。
主要有三种由插入引起的碎片场景:
- 插入,然后回滚
- 插入语句失败
- 页分裂引起的碎片
测试环境
我创建了自己的测试环境来测试这些案例。
- DB:Percona 版分支
- Table:frag,ins_frag,frag_page_spl
- 表大小:2G
场景 1:插入后回滚
首先,我创建了一个新表”ins_flag”。然后我开启一个事务(使用 BEGIN),如下所示开始拷贝”frag”表中数据到”ins_flag”中。
mysql> create table ins_frag like frag;
Query OK, 0 rows affected (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into ins_frag select * from frag;
Query OK, 47521280 rows affected (3 min 7.45 sec)
Records: 47521280 Duplicates: 0 Warnings: 0
#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 02:43 frag.ibd
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 03:00 ins_frag.ibd
如上所示,您可以看到已经执行了插入,但是我还没有提交或者回滚插入操作。您注意到 2 张表都已经占用 2G 磁盘空间。
现在我将回滚插入操作。
mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
| 47521280 |
+----------+
1 row in set (1.87 sec)
mysql> rollback;
Query OK, 0 rows affected (5 min 45.21 sec)
mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 02:43 frag.ibd
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 03:09 ins_frag.ibd
当插入回滚后,”ins_frag”表仍然占有相同的 2GB 的磁盘空间。让我们在 MySQL 客户端看看碎片空间。
mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 * 1024 ), 2), 'G') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='percona' and table_name='ins_frag';
+----------+----------+-------+----------+
| DATABASE | TABLE. | TOTAL | DATAFREE |
+----------+----------+-------+----------+
| percona | ins_frag | 0.00G | 1.96G |
+----------+----------+-------+----------+
1 row in set (0.01 sec)
这清楚的显示了插入之后回滚会产生碎片。我们需要重建表来回收磁盘空间。
mysql> alter table ins_frag engine=innodb;
Query OK, 0 rows affected (2.63 sec)
Records: 0 Duplicates: 0 Warnings: 0
#Linux shell
sakthi-3.2# ls -lrth
total 4131040
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 02:43 frag.ibd
-rw-r----- 1 _mysql _mysql 112K Jun 17 03:11 ins_frag.ibd
场景 2: 插入语句失败
在会话 1 中,我将在事务中执行相同的插入语句。但是这次我会在会话 2 中中断并杀掉这个插入语句。
会话 1
#Linux shell
sakthi-3.2# ls -lrth
total 4131040
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 02:43 frag.ibd
-rw-r----- 1 _mysql _mysql 112K Jun 17 04:02 ins_frag.ibd
#MySQL shell
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into ins_frag select * from frag; #is running
会话 2
mysql> pager grep -i insert ; show processlist;
PAGER set to 'grep -i insert'
| 33 | root | localhost | percona | Query | 14 | executing | insert into ins_frag select * from frag |
4 rows in set (0.00 sec)
mysql> kill 33;
Query OK, 0 rows affected (0.00 sec)
插入中断并失败了。在会话 1 查看:
mysql> insert into ins_frag select * from frag;
ERROR 2013 (HY000): Lost connection to MySQL server during query
#Linux shell
sakthi-3.2# ls -lrth
total 4591616
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 02:43 frag.ibd
-rw-r----- 1 _mysql _mysql 212M Jun 17 04:21 ins_frag.ibd
#MySQL shell
mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.10 sec)
插入并未完成,表中无数据。但是仍然,这个表的 ibd 文件已经涨到 212M。通过 MySQL 客户端查看表空间碎片。
mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='percona' and table_name='ins_frag';
+----------+----------+---------+----------+
| DATABASE | TABLE | TOTAL | DATAFREE |
+----------+----------+---------+----------+
| percona | ins_frag | 0.03M | 210.56M |
+----------+----------+---------+----------+
1 row in set (0.01 sec)
表中有碎片,需要重建表回收这些空间。
mysql> alter table ins_frag engine='innodb';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
#Linux shell
sakthi-3.2# ls -lrth
total 4131040
-rw-r----- 1 _mysql _mysql 2.0G Jun 17 02:43 frag.ibd
-rw-r----- 1 _mysql _mysql 112K Jun 17 04:32 ins_frag.ibd
场景 3: 页分裂引起的碎片
我们知道,InnoDB 记录存储在 InnoDB 页中。默认情况下,每个页大小是 16K,但是您可以选择更改页大小。
如果 InnoDB 页没有足够的空间容纳新的记录或索引条目,它将被分成 2 页,每页约占 50%。这意味着,即使对表只有插入,没有回滚和删除,最终也可能只有平均 75%的页利用率——因此这种页内部损失为 25%。
当按排序建立索引,它们会有更多的拥塞,如果表很多插入到索引中随机位置,就会导致页分裂。
参阅 Marco Tusa 写的博客 InnoDB Page Merging and Page Splitting,详细介绍了页分裂和 InnoDB 页结构/操作。
为了实验,我创建了一个具有排序索引的表(降序)
mysql> show create table frag_page_splG
*************************** 1. row ***************************
Table: frag_page_spl
Create Table: CREATE TABLE `frag_page_spl` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
`messages` varchar(600) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_spl` (`messages` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.07 sec)
我们可以通过表 INFORMATION_SCHEMA.INNODB_METRICS 监控页分裂情况。对此,您需要启用 InnoDB monitor。
mysql> SET GLOBAL innodb_monitor_enable=all;
Query OK, 0 rows affected (0.09 sec)
我写了一个 6 个并发随机插入的脚本。脚本执行结束后:
mysql> select name,count,type,status,comment from information_schema.innodb_metrics where name like '%index_page_spl%'G
*************************** 1. row ***************************
name: index_page_splits
count: 52186
type: counter
status: enabled
comment: Number of index page splits
1 row in set (0.05 sec)
mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='percona' and table_name='frag_page_spl';
+----------+---------------+----------+----------+
| DATABASE | TABLE. | TOTAL | DATAFREE |
+----------+---------------+----------+----------+
| percona | frag_page_spl | 2667.55M | 127.92M |
+----------+---------------+----------+----------+
1 row in set (0.00 sec)
从指标上看,我们看到页分裂次数在增加。输出显示有 52186 次页分裂,产生了 127.92MB 的碎片。
一旦发生页分裂,唯一的方法是将创建的页降至合并阈值之下。当这种情况发生时,InnoDB 通过合并操作将数据从分裂的页中移出。对表和特定的索引合并阈值是可配置的。
另一种重新组织数据的方法是 OPTIMIZE TABLE。这是一个非常重和漫长的过程,但通常这是解决过多页比较稀疏的唯一方法。
总结
- 前面两种情况很少见。因为大多数应用程序都不会设计在表中写入大量数据。
- 在执行批量插入时(INSERT INTO SELECT * FROM, 加载 mysqldump 的数据, INSERT with huge data 等)需要注意这些问题。
- 碎片占用的磁盘空间始终是可重用的。