mysql从库手动清理部分relay log mysql数据清理

清除MySQL表空间碎片的详细方法

• 1. 背景
• 2. 碎片信息查询
• 3. 碎片整理前后的查询性能比对

• 3.1 碎片整理前
• 3.2 整理碎片
• 3.3 碎片整理后

• 4. 整理表碎片会锁表吗？
• 参考

1. 背景

一张任务表的记录条数达到1.7亿，查询性能差。为提升性能将历史数据抽取到大数据，在MySQL表中删除历史数据，只保留最新3个月的数据（约2300w）。由于删除了大量记录，因此产生了很多表碎片，为了不影响查询性能，并释放表空间，决定进行碎片整理。

mysql version: 5.7.26-log

2. 碎片信息查询

SHOW TABLE STATUS LIKE 'tablename'

Data_free一栏为碎片空间，由于删除了大量数据，碎片空间约为148G，碎片率(碎片空间/总使用空间)约为3.2。

3. 碎片整理前后的查询性能比对

表碎片空间太大会增加访问表时的IO次数，影响MySQL查询性能；而且会占用磁盘空间，影响磁盘空间使用率。
影响磁盘空间使用率比较好理解，但是对查询性能的影响到底有多大却不太好评估，只能具体问题具体分析。
以我们的场景为例，简单做一下验证。

3.1 碎片整理前

当前记录月2300w，createtime、order_type有独立索引，碎片整理前下面的查询语句耗时约300ms。

select * from tablename where createtime > '2022-03-29' and createtime < '2022-03-30' and order_type = 1 limit 100

3.2 整理碎片

执行碎片整理，表使用innodb引擎，整个执行过程约12分钟。如果是主从结构，从库的碎片清理会相对滞后。

alter table tablename engine=InnoDB

3.3 碎片整理后

碎片整理完成后对性能影响不大。
原因分析：

• 因为删除数据时主要是通过主键进行删除，而且删除的都是连续数据，所以对IO性能影响较小，因此对查询性能影响不大。

那么什么情形下会影响查询性能呢？

• 依据MySQL的官方文档，在对二级索引进行无序新增、删除时对查询性能的影响会比较大。

4. 整理表碎片会锁表吗？

DBA给的回复是一般不会，😁
执行过程中确实也不影响数据库数据的正常插入。

参考

14.4 InnoDB Architecture14.12.4 Defragmenting a Table

14.13 InnoDB and Online DDL

14.6.2.1 Clustered and Secondary Indexes

14.6.2.2 The Physical Structure of an InnoDB Index