《小白学习MySQL - MySQL会不会受到“高水位”的影响？》曾提到了MySQL中数据删除的空间清理和文件释放的问题。碰巧看到姚老师这篇文章，《MySQL表空间碎片整理方法》，学习一下。

MySQL数据库中的表在进行了多次delete、update和insert后，表空间会出现碎片。定期进行表空间整理，消除碎片可以提高访问表空间的性能。

检查表空间碎片

下面这个实验用于验证进行表空间整理后对性能的影响，首先检查这个有100万记录表的大小，

mysql> analyze table sbtest1;+----------------+---------+----------+-----------------------------+| Table| Op| Msg_type | Msg_text|+----------------+---------+----------+-----------------------------+| sbtest.sbtest1 | analyze | status | Table is already up to date |+----------------+---------+----------+-----------------------------+1 row in set (0.06 sec)mysql> show table status like 'sbtest1'\G*************************** 1. row ***************************Name: sbtest1Engine: MyISAMVersion: 10Row_format: FixedRows: 1000000Avg_row_length: 729Data_length: 729000000Max_data_length: 205195258022068223Index_length: 20457472Data_free: 0Auto_increment: 1000001Create_time: -05-31 18:54:22Update_time: -05-31 18:54:43Check_time: -05-31 18:55:05Collation: utf8mb4_0900_ai_ciChecksum: NULLCreate_options: Comment: 1 row in set (0.00 sec)mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*-rw-r----- 1 mysql mysql 729000000 May 31 08:24 /var/lib/mysql/sbtest/sbtest1.MYD-rw-r----- 1 mysql mysql 20457472 May 31 08:25 /var/lib/mysql/sbtest/sbtest1.MYI

命令show table status和从OS层看到的数据文件大小一致，这时的Data_free为零。

删除这个表三分之二的记录，

mysql>deletefromsbtest1whereid%3<>0;QueryOK,666667rowsaffected(51.72sec)

重新收集这个表的统计信息后再查看表的状态，

mysql> analyze table sbtest1;+----------------+---------+----------+----------+| Table| Op| Msg_type | Msg_text |+----------------+---------+----------+----------+| sbtest.sbtest1 | analyze | status | OK |+----------------+---------+----------+----------+1 row in set (0.13 sec)mysql> show table status like 'sbtest1'\G*************************** 1. row ***************************Name: sbtest1Engine: MyISAMVersion: 10Row_format: FixedRows: 333333Avg_row_length: 729Data_length: 729000000Max_data_length: 205195258022068223Index_length: 20457472Data_free: 486000243Auto_increment: 1000001Create_time: -05-31 18:54:22Update_time: -05-31 19:03:59Check_time: -05-31 18:55:05Collation: utf8mb4_0900_ai_ciChecksum: NULLCreate_options: Comment: 1 row in set (0.01 sec)mysql> select 486000243/729000000;+---------------------+| 486000243/729000000 |+---------------------+| 0.6667 |+---------------------+1 row in set (0.00 sec)mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*-rw-r----- 1 mysql mysql 729000000 May 31 08:33 /var/lib/mysql/sbtest/sbtest1.MYD-rw-r----- 1 mysql mysql 20457472 May 31 08:34 /var/lib/mysql/sbtest/sbtest1.MYI

发现这个表中的三分之二的记录已经被删除，但数据文件的大小还和原来一样。因为被删除的记录只是被标记成删除，他们占用的存储空间并没有被释放。

进行全表扫描，看看性能，

mysql> select count(*) from sbtest1 where c<>'aaa';+----------+| count(*) |+----------+| 333333 |+----------+1 row in set (0.82 sec)

发现这个全表扫描SQL用时0.82秒，查看sys.session视图中的last_statement_latency可以看到一样的用时。

整理表空间与性能提升

进行表空间整理，

mysql> alter table sbtest1 force;Query OK, 333333 rows affected (10.73 sec)Records: 333333 Duplicates: 0 Warnings: 0mysql> analyze table sbtest1;+----------------+---------+----------+-----------------------------+| Table| Op| Msg_type | Msg_text|+----------------+---------+----------+-----------------------------+| sbtest.sbtest1 | analyze | status | Table is already up to date |+----------------+---------+----------+-----------------------------+1 row in set (0.04 sec)mysql> show table status like 'sbtest1'\G*************************** 1. row ***************************Name: sbtest1Engine: MyISAMVersion: 10Row_format: FixedRows: 333333Avg_row_length: 729Data_length: 242999757Max_data_length: 205195258022068223Index_length: 6820864Data_free: 0Auto_increment: 1000001Create_time: -05-31 19:10:35Update_time: -05-31 19:10:41Check_time: -05-31 19:10:45Collation: utf8mb4_0900_ai_ciChecksum: NULLCreate_options: Comment: 1 row in set (0.48 sec)mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*-rw-r----- 1 mysql mysql 242999757 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYD-rw-r----- 1 mysql mysql 6820864 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYI

经过整理后，硬盘空间占用剩下原来的三分之一，Data_free又变成零，被删除的记录的硬盘空间都释放了。

再次执行全表扫描的SQL语句，

mysql> select count(*) from sbtest1 where c<>'aaa';+----------+| count(*) |+----------+| 333333 |+----------+1 row in set (0.29 sec)

发现执行速度也提高到大约原来的三倍。这里使用的是MyISAM表进行测试，如果用InnoDB表，速度的提高没有这么明显，因为InnoDB的数据会缓存到InnoDB缓存中，MyISAM表的数据MySQL不进行缓存，OS可能会缓存，因此要得到准确的测试结果，在Linux系统上每次测试前要使用下面的命令释放系统的缓存，

# echo 3 > /proc/sys/vm/drop_caches

使用alter table force进行表空间整理和OPTIMIZE TABLE命令的作用一样，这个命令适用于InnoDB , MyISAM和ARCHIVE三种引擎的表。但对于InnoDB的表，不支持OPTIMIZE TABLE命令，可以用alter table sbtest1 engine=innodb代替，例如，

mysql> OPTIMIZE TABLE sbtest2;+----------------+----------+----------+-------------------------------------------------------------------+| Table| Op | Msg_type | Msg_text |+----------------+----------+----------+-------------------------------------------------------------------+| sbtest.sbtest2 | optimize | note| Table does not support optimize, doing recreate + analyze instead || sbtest.sbtest2 | optimize | status | OK |+----------------+----------+----------+-------------------------------------------------------------------+2 rows in set (1 min 25.24 sec)mysql> alter table sbtest2 engine=innodb;Query OK, 0 rows affected (1 min 3.06 sec)Records: 0 Duplicates: 0 Warnings: 0

使用mysqlcheck进行批量表空间优化

下面的命令可以找出表空间中可释放空间超过10M的最大10个表，

mysql> select table_name,round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 10 order by data_free_mb desc limit 10;+------------+----------------+--------------+| TABLE_NAME | data_length_mb | data_free_mb |+------------+----------------+--------------+| sbtest2 | 232 |174 |+------------+----------------+--------------+1 row in set (0.02 sec)

可以使用MySQL自带的工具mysqlcheck的-o选项进行表空间优化，这个工具适合于在脚本中进行批量处理，可以被Linux中的crontab或Windows中的计划任务调用。

对单个表进行表空间优化的例子如下，

$ mysqlcheck -o sbtest sbtest1

也可以使用下面的命令对某个数据库中的所有表进行表空间优化，

$ mysqlcheck -o sbtest

还可以对整个实例中对所有数据库进行表空间优化，

$ mysqlcheck -o --all-databases

与其要考虑怎么删除数据和回收空间，不如在设计之初，就考虑到表的数据删除策略，根据业务需求，存储有用的数据。

小白学习MySQL，

《小白学习MySQL - 大小写敏感问题解惑》

《小白学习MySQL - only_full_group_by的校验规则》

《小白学习MySQL - max_allowed_packet》

《小白学习MySQL - mysqldump保证数据一致性的参数差异》

《小白学习MySQL - 查询会锁表？》

《小白学习MySQL - 索引键长度限制的问题》

《小白学习MySQL - MySQL会不会受到“高水位”的影响？》

《小白学习MySQL - 数据库软件和初始化安装》

《小白学习MySQL - 闲聊聊》

近期更新的文章：

《Oracle字符串类型扩容隐患》

《最近碰到的几个问题》

《PG逻辑复制的REPLICA IDENTITY设置》

《Java List集合转数组的两种重载方法》

《Oracle、SQL Server和MySQL的隐式转换异同》

文章分类和索引：

《公众号800篇文章分类和索引》