向mysql插表失败_批量插入回滚或插入失败导致的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

> 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操作。

> 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的磁盘空间。

> 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回滚后，产生了碎片。

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

> 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：

# 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杀掉：

> 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

> 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的物理空间。

> 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)

>

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

> 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%。

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

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

> 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。

> set global innodb_monitor_enable=all;

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

> 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表。这可能是一个代价昂贵和漫长的过程，但通常也处理太多页面位于稀疏区域的情况中唯一方法。