哎呀，磁盘又满了？MySQL空间问题如何解决？案例分析有妙招吗？

真的是每次一到周五或者节假日这破服务器就给我搞事情。这不报警短信又来了磁盘空间不足。某环境自上线以来, 空间使用越来越多. 总是扩空间也不是办法啊. 于是只能看能不能从数据库层面来释放一部分空间了. 说实话， 这活儿干得多了真的有点怀疑人生，为什么大家都不爱惜磁盘空间呢，哈基米！？

我就纳闷了这数据也没涨多少啊，怎么空间就没了？难道是有人在里面存了什么不可告人的秘密？开玩笑的，肯定是哪里出了问题。MySQL空间问题如何解决？案例分析有妙招吗？这不仅仅是个技术问题，简直是个生存问题。我得赶紧看看，不然老板又要拿着刀站在我身后了，离了大谱。。

第一步：看看那些没用的索引占地方

先说说我得看看是不是那些乱七八糟的索引把空间给占满了。很多时候， 开发为了性能，这里加个索引，那里加个索引，后来啊后来业务逻辑变了索引也不用了就扔在那儿吃灰。这简直就是浪费资源嘛，翻车了。！

查询出来发现有上万个未使用的索引... 数据库启动时间已经超过半年, 说明这部分索引超过半年未使用, 是可以释放的. 看到这个数字我都惊呆了上万个！这得多少空间啊？这要是能释放出来估计又能撑好几个月，总体来看...。

我们都曾是... 然后我赶紧写了个SQL去查一下具体有多大：

-- 查询未使用的索引的大小明细SELECT     _schema,    _name,    _name,    _value * @@innodb_page_size / 1024 / 1024 AS IDX_SIZE_MBFROM    _unused_indexes a        JOIN    _index_stats b ON _schema = _name        AND _name = _name        AND _name = _nameWHERE    _name = 'size';

跑出来的后来啊， 哎哟，还真不少。虽然删除这部分索引不会释放空间, 但是后续对该表的数据更改操作,会先使用这释放出来的那部分空间. 那我们就来统计下这部分空间有多大吧. 这就好比你虽然把房间里的垃圾扔出去了 但是垃圾袋还在房间里占着地儿， 我当场石化。 不过至少下次扔垃圾的时候，你可以直接用这个袋子，不用再拿新的了。这逻辑，是不是有点绕？没办法，MySQL就是这么设计的。

我们查询stat_name=size的就是索引的page数量, 再乘上@@innodb_page_size就是索引大小了. 其实吧information_就是这么计算的. 也就是说可能存在未使用的索引的, 然后我们查询下_unused_indexes看下未使用的索引. 这统计信息准得离谱.... 虽然有时候我也怀疑它的准确性，但大部分时候还是能信的，说实话...。

这部分表很多其实都没得主键, 所以还涉及到主键的新增, 而主键的新增就涉及到表的重建了, 那么索引那部分空间就能释放出来了. 可能会有小伙伴问: 添加主键的话, 空间会不会增加啊? 答:不会, 甚至会减少, 主要原因是没得索引的时候, mysql会自动新增个rowid来作为主键, 而人工指定主键的话, 就不会有这个rowid字段, 所以空间就会减少下来. 这点很多人不知道， 以为加主键肯定会占空间，其实不然哦。

泰酷辣！ 其实看对应的表名就能猜出来这部分表是应用程序自动建的, 索引应该也是自动建的, 而建这部分索引的时候并没有考虑后续是否使用... 这就是典型的“只管生，不管养”啊。开发同学，长点心吧！

到底怎么算大小的？

为了搞清楚到底占了多少空间，我又去翻了翻那些系统表。其实我们看下information_的表结构就知道了. 那个结构，啧啧，复杂得要命。不过为了空间，拼了，恳请大家...。

我们可以查询_index_stats表看对应索引的大小, 虽然是统计信息, 但误差不会太大. 我这里没得碎片问题, 实际场景可能还有碎片问题,需要注意下. 碎片这东西， 当冤大头了。 就像磁盘里的黑洞，不知不觉就把你的空间给吞了。

再啰嗦一句, 上面的_index_stats中的信息我们我们也可以使用ibd2sql_web去验证. 该工具可以在浏览器上查看ibd的结构, 表中描述为Number of leaf pages in index的page其实吧是PAGE_LEVEL=1的PAGE, 其实该算为LEAF PAGE的.... 这工具挺好用的， 我给跪了。 推荐大家试试，虽然我也没怎么用明白，反正看着挺专业的。

我是深有体会。 我们登录数据库查询下, 数据量和索引量的比较. 实际环境查询出来 数据和索引大小差不多, data_free大体上为空. 这说明啥？说明数据本身和索引一样大！这索引建得也太疯狂了吧？

SELECT     ROUND / 1024 / 1024 / 1024, 2) AS DATA_GB,    ROUND / 1024 / 1024 / 1024,            2) AS INDEX_GB,    ROUND / 1024 / 1024 / 1024, 2) AS FREE_GBFROM    information_WHERE    table_schema NOT IN ;

生产环境不方便截图, 我模拟的环境, 量太小,不好看.... 大家脑补一下那个满屏红字的报警界面吧，是不是很刺激，一句话。？

如果要总和的话, 改为sum即可. 这SQL很简单，就不多解释了懂的都懂。

那些乱七八糟的临时表

除了索引，还有一个大坑，就是临时表。特别是MySQL 5.7那个ibtmp1文件，简直就是个无底洞。本文讨论了MySQL 5.7中临时表文件ibtmp1的管理问题,包括如何设置临时表目录、 ibtmp1文件占用磁盘空间的原因及解决办法,以及可能影响ibtmp1文件大小的SQL语句类型.1 mysql5.7已经可以设置单独的临时表目录,默认放置在数据目录,文件名为ibtmp1.，我裂开了。

这玩意儿一旦涨起来除非重启，否则基本不会缩回去。你说气人不气人？1 mysql5.7已经可以设置单独的临时表目录,默认放置在数据目录,文件名为ibtmp1.注意 1 select查询语句会先应用tmp_table_szie的量,在ibtmp1中申请占用空间.2 select union all语句已进行了优化,不会利用临时表空间了.

开倒车。 我们看了一下,是主要原因是空间在短时间内,被撑爆了.和知数堂的徐晨亮聊聊一下,说这个可能和mysql的几种排序方式有关,建议我可以具体分析一下。. 排序、去重、分组，这些操作要是没走好索引，统统都要去临时表里转一圈。数据量一大，磁盘瞬间就爆了。

醉了... MySQL Binlog日志文件占用过多,可以在mysql命令行上使用PURGE BINARY LOGS BEFORE '2023-06-26 10:00:00'或者PURGE BINARY LOGS TO 'mysql-bin.010'来临时清理本地binlog日志,释放磁盘空间,或者临时修改实例expire_logs_days参数。.半连接、 去重、不走索引的order by等操作,会创建临时表,如果涉及的数据量过多,可能导致临时表空间过大。DDL操作重建表空间时,如果表特别大,创建索引排序时产生的临时文...

所以啊， SQL优化真的很重要，别光想着加索引，还得看看SQL写得烂不烂，我坚信...。

OOM的惨痛教训

说到空间问题，不光是磁盘，内存空间也是个大问题。我就遇到过这种奇葩事。背景:某机器内存256G,安装2实例mysql,每个buffer_pool各106G,总计212G;某套DB晚上10:00左右迁移到该环境,第2天早上10:00左右收到OOM kill短信,因swap空间不足一个Mysql实例被强制kill;该实例mysqld进程没有被彻底清除,而是变成了....背景:某机器内存256G,安装2实例mysql,每个buffer_pool各106G,总计212G;某套DB晚上10:00左右迁移到该环境,第2天早上10:00左右收到OOM kill短信,因swap空间不足一个Mysql实例被强制...

这配置，看着挺豪华吧？256G内存啊！后来啊呢？照样崩。为什么？主要原因是没留余地啊！操作系统不需要内存吗？其他进程不需要内存吗？把Buffer Pool设这么大，不出问题才怪。背景:某机器内存256G,安装2实例 他破防了。 MySQL,每个 buffer_pool各106G,总计212G; 某套DB晚上10:00左右迁移到该环境,第2天早上10:00左右收到OOM kill短信,因swap空间不足一个Mysql实例被强制kill...

本文描述了作者在遇到MySQL服务器内存使用率持续增长的问题时,进行了详细的分析过程.经过排查,官方建议关闭并行复制以解决问题。. 内存这东西，就像海绵里的水，挤一挤总会有的，但是如果你把海绵塞满了石头，那就真的挤不出来了。

工具大乱炖：到底该信谁？

为了排查这些问题，我也试了不少工具。市面上工具一大堆，有的好用，有的难用。我随便整理了个表格，大家凑合着看吧，反正也没啥权威性，就是个人体验。

容我插一句... 哈哈, 扯远了. 我们回到刚才的问题: 查看未使用的索引的大小. 其实工具只是辅助，关键还是得靠人脑去分析。别指望工具能帮你解决所有问题，它只能告诉你哪里有问题，怎么解决还得靠自己。

Binlog也是个大头

有时候磁盘满了 不一定是数据或者索引的问题，可能是日志没删。MySQL Binlog日志文件占用过多,可以在mysql命令行上使用PURGE BINARY LOGS BEFORE '2023-06-26 10:00:00'或者PURGE BINARY LOGS TO 'mysql-bin.010'来临时清理本地binlog日志,释放磁盘空间,或者临时修改实例expire_logs_days参数。.

我就见过好几次 主要原因是expire_logs_days没设置好，或者设置得太大了后来啊Binlog把磁盘写满了。这时候MySQL直接就挂了连登录都登不上去。2020-02-19 10:06:23 18723 Disk is full writing '/data/mysql_ 换位思考... 3306/mysql-bin.002348' . Waiting for someone to free space... 2020-02-19 10:06:23 18723 Retry in 60 secs. Message reprinted in 600 secs 20...

补救一下。 看到这个报错，是不是很绝望？No space left on device，这简直是DBA的噩梦。这时候只能硬着头皮去删文件，或者手动Purge日志。Linux操作系统部署的mysql数据库服务器平时“/ 目录占用很低,但每个小时都会出现一次根目录空间占用高的情况,本文系从OS侧对异常事件进行分析排查的过程.实际操作在问题时段没有抓取到目录空间有异常.# lsof |grep delete |sort -nrk 8 zabbix_ag 6594 zabbix 2w REG 253,0 761 50695552 /var/log/zabbix/zabbix_agentd.log-20210916 zabbix_ag 6594 zabbix 1w REG 253,0 761 50695552 /var/log/zabbix/zabbix_agentd.log-20210916 salt-mi...

有时候明明df看磁盘还有空间， 但是就是报No space left，这时候多半是inode满了或者是有文件被删除了但是进程还占用着。这种问题排查起来最烦人了。

一下 别再让我加班了

啰啰嗦嗦说了这么多，其实MySQL空间问题，无非就是那几个方面：数据太多、索引太多、日志太多、 我算是看透了。 临时表太多。解决思路也很简单：删数据、删索引、删日志、优化SQL、重建表。

主键索引不算在索引大小里面, 而是算在数据大小里面的. 这点大家要记住别算错了账。然后把上面的后来啊发给开发,让他们去做即可. 哈哈，这才是最关键的一步。锅甩出去，我就轻松了，哎，对！。

实不相瞒... 空间不足引起了下面可能发生的mysql问题.MySQL在线故障深度解析:典型案例与排查策略.复制链接分享到 QQ分享到新浪微博扫一扫. 希望大家以后遇到这种问题，能少走点弯路。别像我一样，每次都要折腾半天。

说起来... 再说说 再送大家一个统计未使用索引总大小的SQL，拿走不谢：

-- 查询未使用的索引的大小之和SELECT round/1024/1024/1024,2) as IDX_SIZE_GBFROM    _unused_indexes a        JOIN    _index_stats b ON _schema = _name        AND _name = _name        AND _name = _nameWHERE    _name = 'size';

代码语言：sql

不靠谱。 好了不说了我要去喝杯咖啡压压惊。这MySQL空间问题，真是让人头秃。希望下次别再让我遇到这种“妙招”了我只想安安静静地写写SQL，不想天天跟磁盘空间较劲。各位开发大佬，写代码的时候手下留情，别再乱建索引了拜托了！