MySQL碎片管理疑问

win1027

我有一张不断增长的innodb表，已经做了哈希分区，目前两亿多数据，对该表只有delete、insert操作，会产生部分碎片，目前需要规划下碎片怎么管理清理，请问各位大侠对于大数据量的数据碎片一般是怎么处理？

另网上查资料，大部分都如 http://pengbotao.cn/mysql-suipian-youhua.html 这样说，但我试验下对于6千万数据
ALTER TABLE table_name ENGINE = Innodb; 语句耗时1个多小时，所以对于两亿多的数据来说，现网操作感觉有点不切实际

win1027

<高性能mysql>中的8.4.3 主动-被动模式下的主-主复制，可能适用于这种耗时长的操作，其他有实践操作的可以说下经验处理

kider

当然两亿条数据在现网操作实在是太不切实际了。

那么探讨一下：
经过一段时间的运行，大量的增删会给表产生碎片。

如何判断：正如文章中所提示，我们不仅仅只看DATA_FREE，更多看看实际数据库总体大小情况，然后跟实际数据库文件在磁盘的大小来对比。设置一个百分比，如果超过我们的设置，认为碎片过多，需要优化。

1. 数据库大小：
   
2. SELECT count(*) TABLES,table_schema,
   
3. concat(round(sum(table_rows)/1000000,2),'M') rows,
   
4. concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
   
5. concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
   
6. concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
   
7. round(sum(index_length)/sum(data_length),2) idxfrac
   
8. FROM information_schema.TABLES
   
9. GROUP BY table_schema
   
10. ORDER BY sum(data_length+index_length) DESC LIMIT 10;
    

复制代码

当然也可以正对某个表大小做对比，单独dump出某个表，然后和数据库中的统计做对比：

1. 某数据库中表大小统计:
   
2. SELECT TABLE_NAME,
   
3. CONCAT(ROUND(table_rows/1000000,2),'M') ROWS,
   
4. CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') DATA,
   
5. CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') idx,
   
6. CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
   
7. ROUND(index_length/data_length,2) idxfrac
   
8. FROM information_schema.TABLES
   
9. WHERE table_schema LIKE 'YOUDB'
   
10. ORDER BY data_length+index_length DESC LIMIT 5;
    

复制代码

优化碎片：
好了，如果对比数据差异较大，那么就碎片量也相对较大，就需要优化了。
只说说对单个大表吧：
用dump出来，重建新表，再导入或load进数据。这个要比直接“ALTER TABLE foo ENGINE=InnoDB;”快很多。
当然此过程中需要暂停服务。
那么如何可以不暂停服务呢？做个Replication，主备机模式，先停同步，如上方法操作备机，然后打开同步，直到同步追上，切换主备。