MySQL - 表空间碎片整理方法

逃离我推掉我的手 2024-03-24 22:44 74阅读 0赞

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

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

mysql> show table status like 'sbtest1'\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Name: sbtest1  
Engine: MyISAM  
Version: 10  
Row\_format: Fixed  
Rows: 1000000  
Avg\_row\_length: 729  
Data\_length: 729000000  
Max\_data\_length: 205195258022068223  
Index\_length: 20457472  
Data\_free: 0  
Auto\_increment: 1000001  
Create\_time: 2021-05-31 18:54:22  
Update\_time: 2021-05-31 18:54:43  
Check\_time: 2021-05-31 18:55:05  
Collation: utf8mb4\_0900\_ai\_ci  
Checksum: NULL  
Create\_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> delete from sbtest1 where id%3<>0;  
Query OK, 666667 rows affected (51.72 sec)  
  
重新收集这个表的统计信息后再查看表的状态，

mysql> show table status like 'sbtest1'\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Name: sbtest1  
Engine: MyISAM  
Version: 10  
Row\_format: Fixed  
Rows: 333333  
Avg\_row\_length: 729  
Data\_length: 729000000  
Max\_data\_length: 205195258022068223  
Index\_length: 20457472  
Data\_free: 486000243  
Auto\_increment: 1000001  
Create\_time: 2021-05-31 18:54:22  
Update\_time: 2021-05-31 19:03:59  
Check\_time: 2021-05-31 18:55:05  
Collation: utf8mb4\_0900\_ai\_ci  
Checksum: NULL  
Create\_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: 0

mysql> show table status like 'sbtest1'\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Name: sbtest1  
Engine: MyISAM  
Version: 10  
Row\_format: Fixed  
Rows: 333333  
Avg\_row\_length: 729  
Data\_length: 242999757  
Max\_data\_length: 205195258022068223  
Index\_length: 6820864  
Data\_free: 0  
Auto\_increment: 1000001  
Create\_time: 2021-05-31 19:10:35  
Update\_time: 2021-05-31 19:10:41  
Check\_time: 2021-05-31 19:10:45  
Collation: utf8mb4\_0900\_ai\_ci  
Checksum: NULL  
Create\_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> alter table sbtest2 engine=innodb;  
Query OK, 0 rows affected (1 min 3.06 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
  
使用mysqlcheck进行批量表空间优化

\#列出所有已经产生碎片的表  
select table\_schema, table\_name, data\_free, engine  
from information\_schema.tables  
where table\_schema not in ('sys', 'mysql', 'performance\_schema', 'information\_schema', 'test')  
and data\_free > 0;

下面的命令可以找出表空间中可释放空间超过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)

**查看表占空间和记录数**

select table\_schema,table\_name, concat(round((data\_length+index\_length)/1024/1024/1024,2),‘G’) as tablesize\_gb, table\_rows from information\_schema.tables where table\_schema=‘admin’ order by tablesize\_gb desc limit 5;

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

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

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

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

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

根据\*\*\*\*热计费项目生产环境上，ibd文件异常大，借机梳理表碎片清理和表空间收缩的知识点

![9bc72b35239cb7105ee80e4e857dee32.png][]

1、碎片清理的好处  
降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率  
  
OPTIMIZE TABLE table\_name;对myisam表有用,对innodb也有用，系统会自动把它转ALTER TABLE table\_name ENGINE = Innodb; 这是因为optimize table的本质，就是alter table，所以不管myisam引擎还是innodb引擎都可以使用OPTIMIZE TABLE回收表空间.  
  
注意:  
1)、表空间管理的类型：共享表空间ibdata1(5.5默认)和独立表空间\*.ibd文件(5.6默认，即innodb\_file\_per\_table=ON)  
2)、每个表的表空间存放的是该表自己的数据和索引；  
3)、drop table自动回收表空间，删除大量数据后可以通过alter table xx engine = innodb;回收空间;  
  
2、关于OPTIMIZE的一些用法(标红的为应用场景)和描述  
OPTIMIZE TABLE tbl\_name \[, tbl\_name\] ...  
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，即delete或update,则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。  
  
3、碎片产生的原因  
删除时留白, 插入时尝试使用留白空间，当删除后并未将所有留空的都插入数据,既可以认为未被立即使用的留空就是碎片；  
  
备注：  
1) MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可;  
2) OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可;  
3) 在OPTIMIZE TABLE 运行过程中，MySQL会锁定表;  
4) 默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE;  
  
4、查相关碎片的命令  
\-- 列出所有已经产生碎片的表  
select table\_schema db, table\_name, round(data\_free/1024/1024, 2) data\_free, engine,table\_rows, round((data\_length+index\_length)/1024/1024,2) length  
from information\_schema.tables where table\_schema not in ('information\_schema', 'mysql') and data\_free > 0;  
  
\-- 查询并计算碎片率  
select table\_schema db, table\_name, engine,table\_rows, round(data\_free/1024/1024, 2) data\_free\_M, round((data\_length+index\_length)/1024/1024,2) length\_M , round(data\_free/(data\_free + data\_length+index\_length),2) rate  
from information\_schema.tables where table\_schema not in ('information\_schema', 'mysql') and data\_free > 0 order by data\_free\_M desc ,rate desc;

\--查看某张表的碎片率  
mysql> show table status like 't\_exception\_log202005';  
\+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+  
| Name | Engine | Version | Row\_format | Rows | Avg\_row\_length | Data\_length | Max\_data\_length | Index\_length | Data\_free | Auto\_increment | Create\_time | Update\_time | Check\_time | Collation | Checksum | Create\_options | Comment |  
\+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+  
| t\_exception\_log202005 | InnoDB | 10 | Dynamic | 61360 | 18294 | 1122566144 | 0 | 0 | 6291456 | NULL | 2020-07-31 01:56:57 | NULL | NULL | utf8\_general\_ci | NULL | | 异常信息日志表 |  
\+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+-----------------------+  
  
查询结果中：  
Data\_length : 数据的大小  
Index\_length : 代表索引的数量  
Data\_free : 代表碎片数量（指占用page的大小）  
  
  
5、本地模拟\*.ibd文件缩小  
5.1 创建新表frag\_test  
create table frag\_test (id int auto\_increment primary key, c1 varchar(64));  
5.2 利用存储过程插入数据  
mysql> delimiter $$  
mysql> create procedure insert\_frag\_test(IN START INT(10),IN max\_num INT(10))  
\-> begin  
\-> declare i int default 0;  
\-> set autocommit = 0;  
\-> repeat  
\-> set i = i + 1;  
\-> insert into frag\_test(id,c1) values((START+i),"this is a test i");  
\-> until i = max\_num  
\-> end repeat;  
\-> commit;  
\-> end $$  
mysql> delimiter ;  
mysql> call insert\_frag\_test(1, 2000000);  
mysql> call insert\_frag\_test(2000002, 10000000);  
ERROR 1534 (HY000): Writing one row to the row-based binary log failed  
  
这是因为内存不足导致的binlog cache size不够不能写入binlog，导致语句无法执行  
在配置文件中调整binlog\_cache\_size和max\_binlog\_cache\_size参数的值，改大一点  
查看参数：  
mysql> show variables like '%binlog\_cache\_size%';  
\+-----------------------+-----------+  
| Variable\_name | Value |  
\+-----------------------+-----------+  
| binlog\_cache\_size | 4194304 |  
| max\_binlog\_cache\_size | 536870912 |  
\+-----------------------+-----------+  
mysql> set global binlog\_cache\_size=157810688;  
  
5.3 查看ibd文件以及碎片量  
\[mysql@db1 test01\]\# du -sh \*  
4.0K db.opt  
12K frag\_test.frm  
308M frag\_test.ibd  
12K t1.frm  
96K t1.ibd  
mysql> show table status like 'frag\_test';  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
| Name | Engine | Version | Row\_format | Rows | Avg\_row\_length | Data\_length | Max\_data\_length | Index\_length | Data\_free | Auto\_increment | Create\_time | Update\_time | Check\_time | Collation | Checksum | Create\_options | Comment |  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
| frag\_test | InnoDB | 10 | Dynamic | 7086834 | 41 | 295469056 | 0 | 0 | 5242880 | 7104392 | 2020-10-16 10:15:43 | 2020-10-16 10:49:22 | NULL | utf8\_general\_ci | NULL | | |  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+---------  
  
5.4 删除数据，查看ibd文件大小以及碎片量  
mysql> delete from frag\_test where id =20;  
mysql> delete from frag\_test where id > 24 and id < 30;  
mysql> delete from frag\_test where id > 200 and id < 230;  
mysql> delete from frag\_test where id > 2220 and id < 2560;  
mysql> delete from frag\_test where id > 30000 and id < 50000;  
  
mysql> show table status like 'frag\_test';  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
| Name | Engine | Version | Row\_format | Rows | Avg\_row\_length | Data\_length | Max\_data\_length | Index\_length | Data\_free | Auto\_increment | Create\_time | Update\_time | Check\_time | Collation | Checksum | Create\_options | Comment |  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
| frag\_test | InnoDB | 10 | Dynamic | 7066457 | 41 | 295469056 | 0 | 0 | 227540992 | 7104392 | 2020-10-16 10:15:43 | 2020-10-16 10:49:22 | NULL | utf8\_general\_ci | NULL | | |  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
1 row in set (0.00 sec)  
在删除数据的过程中看到frag\_test.ibd文件大小并没有变化  
  
5.5、执行优化  
mysql> optimize table frag\_test;  
查看ibd文件，明显缩小  
\[mysql@db1 test01\]\# du -sh \*  
4.0K db.opt  
12K frag\_test.frm  
104M frag\_test.ibd  
12K t1.frm  
96K t1.ibd  
mysql> show table status like 'frag\_test';  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+  
| Name | Engine | Version | Row\_format | Rows | Avg\_row\_length | Data\_length | Max\_data\_length | Index\_length | Data\_free | Auto\_increment | Create\_time | Update\_time | Check\_time | Collation | Checksum | Create\_options | Comment |  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+  
| frag\_test | InnoDB | 10 | Dynamic | 1993875 | 50 | 100253696 | 0 | 0 | 4194304 | 7104392 | 2020-10-16 14:00:30 | NULL | NULL | utf8\_general\_ci | NULL | | |  
\+-----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+  
发现Data\_length和Data\_free都有变小  
  
5.6 删除剩余所有的数据  
目前的数据量  
mysql> select count(\*) from frag\_test;  
\+----------+  
| count(\*) |  
\+----------+  
| 1999002 |  
\+----------+  
1 row in set (0.29 sec)  
全部删除mysql> delete from frag\_test;  
全部删除后ibd文件依旧是104M  
通过mysql> show table status like 'frag\_test';  
\+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
| Name | Engine | Version | Row\_format | Rows | Avg\_row\_length | Data\_length | Max\_data\_length | Index\_length | Data\_free | Auto\_increment | Create\_time | Update\_time | Check\_time | Collation | Checksum | Create\_options | Comment |  
\+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
| frag\_test | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 103809024 | 7104392 | 2020-10-16 14:00:30 | 2020-10-16 14:06:54 | NULL | utf8\_general\_ci | NULL | | |  
\+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+  
1 row in set (0.00 sec)  
发现Data\_length变小和Data\_free都有变大  
执行mysql> optimize table frag\_test;  
查看ibd文件  
\[root@db1 test01\]\# du -sh \*  
4.0K db.opt  
12K frag\_test.frm  
96K frag\_test.ibd  
12K t1.frm  
96K t1.ibd  
  
执行mysql> show table status like 'frag\_test';  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Name: frag\_test  
Engine: InnoDB  
Version: 10  
Row\_format: Dynamic  
Rows: 0  
Avg\_row\_length: 0  
Data\_length: 16384  
Max\_data\_length: 0  
Index\_length: 0  
Data\_free: 0  
Auto\_increment: 7104392  
  
结论：  
1）碎片清理可以使用optimize table table\_name,手动触发数据页合并；  
2）optimize table执行过程中会锁表，会产生临时表，占用一定的空间，会影响主从延迟；  
  
补充：  
数据页合并有自动触发和手动触发;  
手动触发：optimize table  
自动触发：依赖于数据页合并临界值（MERGE\_THRESHOLD）；  
MySQL InnoDB 表数据页或者二级索引页（简称数据页或者索引页）的合并与分裂对 InnoDB 表整体性能影响很大；数据页的这类操作越多，对 InnoDB 表数据写入的影响越大。  
MySQL 提供了一个数据页合并临界值（MERGE\_THRESHOLD），在某些场景下，可以人为介入，减少数据页的合并与分裂。在 InnoDB 表里，每个数据页默认16K 大小，默认 MERGE\_THRESHOLD 值为 50，取值范围从 1 到 50，默认值即是最大值。也就是当页面记录数占比小于 50% 时，MySQL 会把这页和相邻的页面进行合并，保证数据页的紧凑，避免太多浪费。

注意：

1、DELETE执行时，当达到阈值后page会合并，被合并的page会并标记为空闲页，留着以后用，并不会释放。所以DELETE后，ibd是不会变小的(truncate会变小)。使用optimze，等于表重构，所以ibd会变小。  
2、对于Innodb 引擎，一般optimze是在开始和结束阶段需要元数据锁，中间阶段是可以进行DML操作的。

[9bc72b35239cb7105ee80e4e857dee32.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/3fec22b2028b499d9694255ea5b664a2.png