转载请联系后台技术指南针公众号。1、MySQL磁盘告警是一周前上报的。大白早早的来到了公司，照例泡了一杯枸杞水，然后看了看数据库的磁盘。哼！超级库中bighero表的磁盘使用率为85%，即将达到告警设置的阈值。喝了一口养生枸杞水后，大白决定把它盘起来，因为盘不能在过年的时候爆炸，不然出大问题。分析了bighero表中的数据，发现有几百万条数据已经不再使用了。可以直接删掉，干就干，操作猛如虎。半个多小时后，删除脚本写完，自测OK。考虑到白天算是高峰期，我决定晚上回家执行脚本，因为怕忘记给手机设置闹钟提醒。忙碌了一天，晚上11点30分回到家。并简单地清理干净。闹钟提醒大白数据要被删除了，我就连上了准备开始。为了安全起见，删除脚本添加了一个几毫秒的休眠，并启动了nohup脚本。看了日志，已经正常启动了。看了一会时间还早?，回来一看剧本执行完了，完美！资料删了，睡觉！2、为什么磁盘还是满的？第二天，大白和往常一样去小区附近的菜市场买了一个3块钱的蛋糕，边吃边等车。坐了3次地铁，终于到了公司。我有一杯枸杞水，我准备喝了！呃？昨晚删了，磁盘使用率竟然是86%。稳大神，分析应该是MySQL并没有真正清理这部分数据，而是误删除了。这种误删行为在linux中并不少见，属于常规操作，被认为是一种战略思想，因此断定mysql也有可能这样做。谷歌一下，文章挺多的。看了几篇文章，找到一个查看分片信息的命令：SELECT*from(SELECTCONCAT(table_schema,'.',table_name)AS'table_name',table_rowsAS'NumberofRows',CONCAT(ROUND(data_length/(1024*1024),6),'M')AS'data_size',CONCAT(ROUND(index_length/(1024*1024),6),'M')AS'index_size',CONCAT(ROUND(data_free/(1024*1024),6),'M')AS'data_free',ENGINEas'engine'FROMinformation_schema.TABLESWHEREtable_schema=#{库名})tORDERBYdata_freeDESC;data_size：数据的大小index_size：索引的大小data_free：数据正在使用engine中的保留空间：表引擎的名称其中data_free表示磁盘碎片的大小，也就是我们需要的地方排除。于是我修改了上面的sql语句并执行了。果然bighero表的data_free有20GB那么多。找到原因了，大白准备手动清理一下。3、在谷歌搜索磁盘清理神器后，发现不同的MySQL存储引擎有不同的清理方式。SHOWENGINES;//查看引擎命令MySQL有多种存储引擎，常用的有MyISAM和InnoDB，而大白的库使用的是InnoDB引擎，不过我们先简单了解一下这两个的特点。3.1MyISAM引擎MyISAM是在ISAM存储引擎的基础上进行扩展的。支持B-tree/FullText/R-tree索引类型；锁级别为表锁，表锁的优点是开销小，加锁速度快；缺点是锁粒度大，锁脉冲发生概率高，容纳并发能力低。该引擎适用于基于查询的业务；该引擎不支持事务，也不支持外键；BLOB和TEXT列可以被索引；它强调快速读取操作，例如它存储在表中的行数，只需要直接读取保存的值而不需要全表扫描。3.2InnoDB引擎支持事务，支持回滚，支持外键；支持Hash/B-tree索引类型；锁级别为行锁，行锁的优点是适合高并发频繁修改表，高并发性能优于MyISAM；系统消耗很大，索引不仅会缓存自己，还会缓存数据。与MyISAM相比，需要更多的内存；3.3可以选择运行InnoDB引擎的操作命令包括：??OPTIMIZETABLEtablenameALTERTABLEtablenameENGINE=INNODB实际上，在运行上述清理命令时，MySQL会锁定表，清理的数据越大，需要的时间就越长。因此，这个手术必须在夜深人静的时候进行。OPTIMIZETABLE命令重组表和索引的物理存储以减少存储空间的使用。ALTERTABLE表名ENGINE=Innodb;命令看似废话，实则将碎片重组。它实际执行的是一个空的ALTER命令，它将重建整个表并删除未使用的空白空间。嗯，大致找到了命令，但是半夜还是要操作。这确实很不道德，但也没办法。和往常一样，我11:30回到家，洗漱完毕，开始打扫卫生。我还是有点紧张。等了一会，看到OK了。刷了一下显示器，磁盘使用率已经降到了80%以下。您可以安心入睡，松一口气。4、为什么MySQL会出现碎片？下面以InnoDB存储引擎为例，看看为什么会出现碎片。当删除一些行时，这些行只是被标记为“已删除”，并没有从索引中物理删除，所以空间并没有真正被释放和回收。大量的随机删除操作会造成不连续的空白。在插入数据的时候，这些空格会先用到，但肯定不会全部用到，就会出现数据碎片。大量的UPDATE操作，Innodb的最小物理存储分配单位是page，UPDATE在更新变长数据时也可能会造成pagesplits，频繁的pagesplits，pages会变得稀疏无规律地填充，最终会出现碎片，对于比如原来的长度是255字节，修改后是128字节，那么128字节左右可能会有漏洞，不能100%利用。因为清理碎片需要加表锁，对业务会有影响，MySQL官方建议不要每小时或每天进行碎片整理。一般来说，根据实际情况，每周只需要整理一次碎片，甚至更长时间。就是这样！明天就要开始工作了，今天就好好过吧。
