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

点击上方蓝字每天学习数据库

【迪B课堂】为腾讯云数据库产品经理迪B哥开设的面向数据库开发者、数据库运维人员、云端运维人员的系列培训课程，旨在帮助大家从入门到精通学习和使用数据库。《我说》为迪B课堂的答疑系列，3分钟帮您解决数据库日常运维过程中的小难题。搜索关注腾讯云数据库官方微信，立得10元云代金券，可移动端一键管理数据库。

本期主题是：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课堂》系列数据库实战课程，关注腾讯云数据库官方微信即可免费获取。

往期推荐

扫描下方二维码关注腾讯云数据库

回复“实战课程”即可学习往期迪B课程

年中薅羊毛，可省18040元

云数据库MySQL年中疯狂折扣中，关注秒杀区预告，新用户只需4.67元/月！即可获得1G内存50G高性能MySQL基础版。更有高可用MySQL,1核1G内存100G SSD盘加送6个月数据迁移服务，秒杀价低至48.64元/月！劲省1504元！点击左下角“阅读原文”速去抢购~

↓↓点“阅读原文”享年中福利

好文和朋友一起看!