mysql innodb 碎片_innodb表碎片处理

+-------+-------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+-------------------------------------------------------------------------------------------------------------------------+

| tb | CREATE TABLE `tb` (

`id` int(11) DEFAULT NULL,

`name` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from ht.tb limit 3;

+------+---------+

| id | name |

+------+---------+

| 1 | qdds |

| 2 | horizon |

| 3 | beijing |

+------+---------+

3 rows in set (0.36 sec)

构造大表

CREATE PROCEDURE dowhile()

BEGIN

DECLARE v1 INT DEFAULT 10000;

WHILE v1 > 0 DO

insert into ht.tb select * from tb;

commit;

SET v1 = v1 - 1;

END WHILE;

END;

call dowhile();

查看表文件大小及行数

mysql> select a.FILE_NAME,a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 from information_schema.FILES a where a.FILE_ID=288;

+-------------+----------------------------------------------+

| FILE_NAME | a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 |

+-------------+----------------------------------------------+

| ./ht/tb.ibd | 1.593750000000 |

+-------------+----------------------------------------------+

1 row in set (0.00 sec)

mysql> select count(1) from ht.tb;

+----------+

| count(1) |

+----------+

| 25165824 |

+----------+

1 row in set (27.22 sec)

删除id=2的行

mysql> delete from ht.tb where id=2;

Query OK, 8388608 rows affected (2 min 2.45 sec)

mysql> select count(1) from ht.tb;

+----------+

| count(1) |

+----------+

| 16777216 |

+----------+

1 row in set (33.04 sec)

mysql> select a.FILE_NAME,a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 from information_schema.FILES a where a.FILE_ID=288;

+-------------+----------------------------------------------+

| FILE_NAME | a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 |

+-------------+----------------------------------------------+

| ./ht/tb.ibd | 1.593750000000 |

+-------------+----------------------------------------------+

1 row in set (0.00 sec)

mysql> select table_schema,table_name,data_free,data_length,index_length,table_rows,CREATE_TIME FROM information_schema.TABLES where TABLE_NAME='tb';

+--------------+------------+-----------+-------------+--------------+------------+---------------------+

| table_schema | table_name | data_free | data_length | index_length | table_rows | CREATE_TIME |

+--------------+------------+-----------+-------------+--------------+------------+---------------------+

| ht | tb | 694157312 | 990904320 | 0 | 16252952 | 2018-08-20 23:51:18 |

+--------------+------------+-----------+-------------+--------------+------------+---------------------+

从上面可知表空间文件大小没变，空间没释放

优化下表在查看

mysql> optimize table ht.tb;

+-------+----------+----------+-------------------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+-------+----------+----------+-------------------------------------------------------------------+

| ht.tb | optimize | note | Table does not support optimize, doing recreate + analyze instead |

| ht.tb | optimize | status | OK |

+-------+----------+----------+-------------------------------------------------------------------+

2 rows in set (1 min 29.57 sec)

mysql> select table_schema,table_name,data_free,data_length,index_length,table_rows,CREATE_TIME FROM information_schema.TABLES where TABLE_NAME='tb';

+--------------+------------+-----------+-------------+--------------+------------+---------------------+

| table_schema | table_name | data_free | data_length | index_length | table_rows | CREATE_TIME |

+--------------+------------+-----------+-------------+--------------+------------+---------------------+

| ht | tb | 4194304 | 746569728 | 0 | 16739684 | 2018-08-21 00:52:36 |

+--------------+------------+-----------+-------------+--------------+------------+---------------------+

1 row in set (0.00 sec)

mysql> select a.FILE_ID,a.FILE_NAME,a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 from information_schema.FILES a where a.FILE_ID=291;

+---------+-------------+----------------------------------------------+

| FILE_ID | FILE_NAME | a.TOTAL_EXTENTS*a.EXTENT_SIZE/1024/1024/1024 |

+---------+-------------+----------------------------------------------+

| 291 | ./ht/tb.ibd | 0.710937500000 |

+---------+-------------+----------------------------------------------+

1 row in set (0.00 sec)

优化以后和预计的表空间大小(删除1/3数据，文件减小1/3)一致；从data_free来看也缩小很多；

optimize table在操作的准备阶段和提交阶段期间短暂地进行独占表锁定，对于InnoDB表，OPTIMIZE TABLE映射到 ALTER TABLE ... FORCE，重建表以更新索引统计信息并释放聚簇索引中未使用的空间；

查询information_schema.FILES 的FILE_ID 和 information_schema.TABLES的CREATE_TIME 可知OPTIMIZE TABLE相当于表删除重建