批量插入回滚或插入失败导致的MySQL表碎片

一般大家都知道，delete操作可以引起表碎片问题。但引起表碎片的并不仅仅只有delete操作。这里将演示一下由insert操作引起的表碎片。

 

在MySQL中，有两种碎片：

1.表中的页是完全空闲的

2.表中的页没有被完全填充，还有部分空闲空间

 

有三种情况下，insert操作会导致表碎片：

1.带有rollback的insert

2.失败了的insert操作

3.页分裂导致的碎片

 

测试环境：Server version: 5.7.23-log MySQL Community Server (GPL)

表：frag、ins_frag、frag_page_spl

 

案例1：带有rollback的insert

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

> create table ins_frag like frag; Query OK, 0 rows affected (0.01 sec)   > begin; Query OK, 0 rows affected (0.00 sec)   > insert into ins_frag select * from frag; Query OK, 5964924 rows affected (2 min 23.04 sec) Records: 5964924  Duplicates: 0  Warnings: 0   >   # ll -ltrh |grep frag -rw-r----- 1 mysql mysql  29K Nov  8 09:38 frag.frm -rw-r----- 1 mysql mysql  29K Nov  8 09:41 ins_frag.frm -rw-r----- 1 mysql mysql 2.3G Nov  8 09:46 frag.ibd -rw-r----- 1 mysql mysql 2.3G Nov  8 09:48 ins_frag.ibd

到此，已经执行了insert操作，但是还没有提交或回滚insert操作。表占用了2.3G的磁盘空间。

现在，开始回滚上面的insert操作。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

> select count(*) from ins_frag; +----------+ | count(*) | +----------+ |  5964924 | +----------+ 1 row in set (1.08 sec)   > rollback; Query OK, 0 rows affected (2 min 27.15 sec)   > select count(*) from ins_frag; +----------+ | count(*) | +----------+ |        0 | +----------+ 1 row in set (0.00 sec)   >   # ll -ltrh |grep frag -rw-r----- 1 mysql mysql  29K Nov  8 09:38 frag.frm -rw-r----- 1 mysql mysql  29K Nov  8 09:41 ins_frag.frm -rw-r----- 1 mysql mysql 2.3G Nov  8 09:46 frag.ibd -rw-r----- 1 mysql mysql 2.3G Nov  8 10:01 ins_frag.ibd

回滚结束后，表ins_frag仍然占用了2.3GB的磁盘空间。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

> 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_NAME = 'ins_frag'; +----------+----------+-------+----------+ | DATABASE | TABLE    | TOTAL | DATAFREE | +----------+----------+-------+----------+ | ysoap    | ins_frag | 2.18G | 2.23G    | +----------+----------+-------+----------+ 1 row in set (0.00 sec)   >

说明insert回滚后，产生了碎片。
现在我们重构这个表，释放空间。

?

1 2 3 4 5 6 7 8 9 10 11

> alter table ins_frag engine=innodb; Query OK, 0 rows affected (0.03 sec) Records: 0  Duplicates: 0  Warnings: 0   >   # ll -ltrh |grep frag -rw-r----- 1 mysql mysql  29K Nov  8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov  8 09:46 frag.ibd -rw-r----- 1 mysql mysql  29K Nov  8 10:16 ins_frag.frm -rw-r----- 1 mysql mysql 128K Nov  8 10:16 ins_frag.ibd

　　

案例2：失败的insert操作
在会话1中，开启一个事务执行insert操作。但是会在会话2中kill掉会话1。

会话1：

?

1 2 3 4 5 6 7 8 9 10

# ll -ltrh |grep frag -rw-r----- 1 mysql mysql  29K Nov  8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov  8 09:46 frag.ibd -rw-r----- 1 mysql mysql  29K Nov  8 10:16 ins_frag.frm -rw-r----- 1 mysql mysql 128K Nov  8 10:16 ins_frag.ibd   > begin; Query OK, 0 rows affected (0.00 sec)   > insert into ins_frag select * from frag; #运行过程中

会话2将会话1杀掉：

?

1 2 3 4 5 6 7 8 9

> pager grep -i insert ; show processlist; PAGER set to 'grep -i insert' | 1603454 | root    | localhost          | ysoap | Query            |       5 | Sending data         | insert into ins_frag select * from frag | 16 rows in set (0.00 sec)   > kill 1603454; Query OK, 0 rows affected (0.00 sec)   >

　　

回到会话1

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

> insert into ins_frag select * from frag; ERROR 2013 (HY000): Lost connection to MySQL server during query   > select count(*) from ins_frag; +----------+ | count(*) | +----------+ |        0 | +----------+ 1 row in set (0.00 sec)   >   # ll -ltrh |grep frag -rw-r----- 1 mysql mysql  29K Nov  8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov  8 09:46 frag.ibd -rw-r----- 1 mysql mysql  29K Nov  8 10:16 ins_frag.frm -rw-r----- 1 mysql mysql 1.2G Nov  8 10:28 ins_frag.ibd

insert操作被中途kill了。表中没有数据。但是，磁盘上还是占用了1.2GB的物理空间。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

> 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_NAME = 'ins_frag'; +----------+----------+-------+----------+ | DATABASE | TABLE    | TOTAL | DATAFREE | +----------+----------+-------+----------+ | ysoap    | ins_frag | 1.04G | 1.15G    | +----------+----------+-------+----------+ 1 row in set (0.01 sec)   >

　　

现在我们再次重构这个表，释放空间。

?

1 2 3 4 5 6 7 8 9 10 11

> alter table ins_frag engine='innodb'; Query OK, 0 rows affected (0.02 sec) Records: 0  Duplicates: 0  Warnings: 0   >   # ll -ltrh |grep frag -rw-r----- 1 mysql mysql  29K Nov  8 09:38 frag.frm -rw-r----- 1 mysql mysql 2.3G Nov  8 09:46 frag.ibd -rw-r----- 1 mysql mysql  29K Nov  8 10:34 ins_frag.frm -rw-r----- 1 mysql mysql 128K Nov  8 10:34 ins_frag.ibd

 

案例3：页分裂导致的碎片
在内部，InnoDB记录存储在InnoDB页中。默认情况下，每个页大小是16K，但是可以选择更改页面大小。

如果InnoDB页没有足够的空间容纳新的记录或索引条目，它将被分成两个页面，每个页大约有50%的空间是满的。这意味着，即使对于只有插入的工作负载，没有回滚或删除，最终也可能只有75%的平均页面利用率——因此这种内部页面碎片的损失为25%。

索引是通过排序构建的，如果表有很多插入到索引中的随机位置，就会导致页分割。

为了做一个实验，我创建了一个具有排序索引(降序)的表：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

> show create table frag_page_spl\G *************************** 1. row ***************************        Table: frag_page_spl Create Table: CREATE TABLE `frag_page_spl` (   `ID` varchar(64) NOT NULL,   `TYPE` varchar(255) DEFAULT NULL,   `TIME` datetime(3) NOT NULL,   `USER_ID` varchar(255) DEFAULT NULL,   `TASK_ID` varchar(64) DEFAULT NULL,   `PINST_ID` varchar(64) DEFAULT NULL,   `ACTION` varchar(255) DEFAULT NULL,   `MESSAGE` varchar(4000) DEFAULT NULL,   `FULL_MSG` longblob,   PRIMARY KEY (`ID`),   KEY `idx_pinstid` (`PINST_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)

可以通过INFORMATION_SCHEMA.INNODB_METRICS监控表的页分裂活动。不过需要开启innodb monitor。

?

1	> set global innodb_monitor_enable=all;

创建6个并发线程，随机插入数据，完成后查看：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

> 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_name='frag_page_spl'; +----------+---------------+----------+----------+ | DATABASE | TABLE.        | TOTAL    | DATAFREE | +----------+---------------+----------+----------+ | percona  | frag_page_spl | 2667.55M | 127.92M  | +----------+---------------+----------+----------+ 1 row in set (0.00 sec)

　　

从innodb_metrics来看，我们可以看到页面分裂计数器增加了。输出显示发生了52186个页分割操作，创建了127.92 MB的碎片。

一旦创建了页分裂，惟一的方法就是将创建的页面降至合并阈值以下。当这种情况发生时，InnoDB通过合并操作将数据从分裂页面中移出。MERGE_THRESHOLD对于表和特定的索引是可配置的。

另一种重新组织数据的方法是optimize表。这可能是一个代价昂贵和漫长的过程，但通常也处理太多页面位于稀疏区域的情况中唯一方法。