【迪B课堂】MySQL表空间碎片产生原因和优化

本期主题是：MySQL表空间碎片产生原因和优化

视频核心信息：

在使用MySQL数据库的过程当中，以下两种场景是大家经常遇到的：

第一，随着业务体量的增大，MySQL数据库可用空间越来越小。解决方法通常有：1. 扩容，利用更大的磁盘空间扩容，或者使用分布式架构；2. 清理不用的数据，尽量不用delete操作以减少数据碎片的产生。第二，优化MySQL时可能发现表中数据只有几千行，即使是全表扫的操作，也与实际打印出来的MySQL的执行时间相差很远。

这两种情况都与MySQL表空间碎片化有关。那么我们如何优化？本期视频就MySQL中MySQL表空间碎片产生原因和优化做了详细分析。

1. MySQL表空间碎片的产生原因

例如，每当删除一行内容，该段空间就会变为空白，被留空。而在一段时间内的大量删除，会使留空的空间变得比存储列表内容所使用的空间更大。当执行插入撮作时，MySQL会尝试使用空白空间。但如果某个空白空间一直没有被大小合适的数据占用，就形成了碎片。当MySQL对进行扫描时，扫描的对象实际是列表的容量需求上限，碎片越多，就会影响查询的性能。

2. MySQL表空间碎片的优化

首先查看某个表的碎片大小，并列出所有已经产生碎片的表。对于优化表碎片，不同数据库引擎有不同的方式。例如MylSAM表，通过optimize table 表名的方式；lnnoDB表，通过alter table 表名 engine=lnnoDB的方式进行优化。

引申一下optimize table的操作，在MySQL5.5和5.6中，实际上执行的过程是，首先创建一张新的临时表，把旧表锁住，禁止插入删除只允许读写，接着把数据不断从旧表拷贝到新临时表，拷贝完成后，进行瞬间rename操作，再删除旧表。因为清除碎片操作会暂时锁表，数据量越大，耗费时间越长。建议：1. 清除操作首先使用percona的工具进行在线优化。2. 清除碎片定期选在业务低峰时进行。

作者简介：

刘迪，网名迪B哥，腾讯云数据库产品经理，北航特聘导师，中国计算机行业协会开源数据库专业委员会副会长，数据库领域技术专家。拥有视频、游戏、金融、服务中台等业务场景的数据库架构设计和优化分析经验，曾主要负责腾讯视频、腾讯网、腾讯新闻、腾讯体育等多款产品的MySQL、MongoDB、Redis数据库架构设计和运维工作，对外推出了《迪B课堂》系列数据库实战课程，关注腾讯云数据库官方微信即可免费获取。

文章转自公众号：腾讯云数据库