MySQL 如何整理表空间碎片

MySQL 在进行大量的 insert，update，delete 之后，表可能出现碎片，从表面上来看，我们可以会发现数据库占用的空间要比我们真实的数据大小要大很多，从性能的角度来说，表空间碎片会影响数据库性能，这是因为 当 MySQL 进行数据扫描时，扫描的大小是表的容量需求上限，也就是数据被写入的区域中处于峰值的部分。

对于 delete 操作来说，删除数据时只是在数据行打上 delete Mark 的标签，当下次进行插入时，这部分空间可以重用，但是如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;

1.使用 show table status 的命令进行查看：

mysql> show table status like 'sbtest4'\G*************************** 1. row ***************************
           Name: sbtest4
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 826772
 Avg_row_length: 1850
    Data_length: 1529921536
Max_data_length: 0
   Index_length: 0
      Data_free: 14680064
 Auto_increment: 10850668
    Create_time: 2022-03-15 15:28:30
    Update_time: 2022-03-15 16:05:27
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.02 sec)

其中 data free 就表示表空间碎片

2.查看表空间的碎片比率

mysql> SELECT ENGINE,
concat( TABLE_SCHEMA, '.', TABLE_NAME ) AS table_name,
round( DATA_LENGTH / 1024 / 1024, 2 ) AS data_length,
round( INDEX_LENGTH / 1024 / 1024, 2 ) AS index_length,
round( DATA_FREE / 1024 / 1024, 2 ) AS data_free,
(data_free /(index_length + data_length )) AS frag_ratio 
FROM
	information_schema.TABLES 
WHERE
	DATA_FREE > 0 
ORDER BY
	frag_ratio ASC 
	LIMIT 2;
+--------+--------------------+-------------+--------------+-----------+------------+
| ENGINE | table_name         | data_length | index_length | data_free | frag_ratio |
+--------+--------------------+-------------+--------------+-----------+------------+
| InnoDB | insert_test.t_user |     2573.00 |      2243.92 |      7.00 |     0.0015 |
| InnoDB | sbtest.sbtest4     |     1459.05 |         0.00 |     14.00 |     0.0096 |
+--------+--------------------+-------------+--------------+-----------+------------+
2 rows in set (0.05 sec)

可以使用 alter table 或 OPTIMIZE TABLE 命令来进行表空间碎片的整理，从官方文档上来看，执行表空间碎片的整理有如下好处[1]：

OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table

mysql> optimize table sbtest.sbtest4;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| sbtest.sbtest4 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest4 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1.68 sec)

mysql> alter table sbtest.sbtest4 engine = innodb;
Query OK, 0 rows affected (1.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.使用存储过程批量进行表碎片清理

DELIMITER $$

CREATE PROCEDURE `optimize_tables_proc`(
 IN db_name varchar(255))
BEGIN
  declare cnt int default 0;
  declare i int default 0;
  select count(*) as total from information_schema.tables where table_schema = db_name into cnt;
  while i < cnt
  do
    set @stmt = concat('select table_name from information_schema.tables where table_schema = ''',db_name,''' order by table_name asc limit ',i,',1 into @tb_name');
    prepare s1 from @stmt;
    execute s1;
    drop prepare s1;
    set @stmt = '';
    set @stmt = concat('optimize table ',db_name,'.',@tb_name);
    prepare s1 from @stmt;
    execute s1;
    drop prepare s1;
    set @stmt = '';
    set i = i + 1;
  end while;
  flush tables;
END$$

DELIMITER ;

调用示例如下：sbtest为数据库的名称

mysql> call optimize_tables_proc('sbtest');
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| sbtest.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| sbtest.sbtest1 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

+----------------+----------+----------+-------------------------------------------------------------------+
| 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 (0.70 sec)

3.使用 mysqlcheck 原生工具进行表空间优化

(base) [root@ip-10-0-0-22 ~]# mysqlcheck -h111.62.xxx.xxx -urudonx -pxxxx -o sbtest
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
sbtest.sbtest1
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
sbtest.sbtest2
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

上述的案例相当于对整个数据库做表空间优化，还可以对某张表做表空间优化，可以使用如下命令：

mysqlcheck -o sbtest sbtest1

注意：如上整理表空间的命令，建议您在数据库可维护的时间窗口内执行，避免对业务造成影响。

[1] https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

如果您有其他问题，欢迎您联系火山引擎技术支持服务