MySQL碎片管理疑问

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，主备机模式，先停同步，如上方法操作备机，然后打开同步，直到同步追上，切换主备。