查询MySQL表占用空间空间排序

一个数据库运行了很久，向对其进行清理优化，如何能快速知道哪些表占用容量较大呢？

Select  
Concat(table_schema, '.', table_name) As "Name"  
,Concat(Round(table_rows / 1000000, 2), 'M') As "Rows"  
,Concat(Round(data_length / ( 1024 * 1024  ), 2), 'M') As "Row Size"  
 ,Concat(Round(index_length / ( 1024 * 1024 ), 2), 'M') As "Index Size"  
 ,Concat(Round(( data_length + index_length ) / ( 1024 * 1024  ), 2), 'M') As "Total"  
 ,Round(index_length / data_length, 2) "Row / Index Ratio"  
From information_schema.TABLES  
 Order By data_length + index_length DESC  
Limit 20; 

其中Limit 20是查看前20名占用空间较大的表，效果如下：

+------------------------------------------------+---------+------------+------------+------------+-------------------+
| Name                                           | Rows    | Row Size   | Index Size | Total      | Row / Index Ratio |
+------------------------------------------------+---------+------------+------------+------------+-------------------+
| mcmp_event_l.event_launcher_attribute_daily    | 992.65M | 132980.50M | 171929.17M | 304909.67M |              1.29 |
| mcmp_event_v.event_vlock_attribute_daily       | 277.90M | 39546.03M  | 44269.23M  | 83815.27M  |              1.12 |
| mcmp_launcher.theme_thid_summary_daily         | 259.76M | 27497.39M  | 16808.81M  | 44306.20M  |              0.61 |
| mcmp_launcher.theme_new_thid_summary_daily     | 282.85M | 26321.91M  | 17841.27M  | 44163.17M  |              0.68 |
| mcmp_event_l.event_launcher_ver_daily          | 150.33M | 15974.84M  | 27352.84M  | 43327.69M  |              1.71 |
| mcmp_crash.exc_vlock_exception_model_osver     | 0.79M   | 22076.00M  | 0.00M      | 22076.00M  |              0.00 |
| mcmp_launcher.theme_new_thid_summary_daily_sum | 234.67M | 17412.17M  | 3534.38M   | 20946.55M  |              0.20 |
| mcmp_event_v.event_vlock_ver_daily             | 51.24M  | 5931.20M   | 10507.33M  | 16438.53M  |              1.77 |
| mcmp_event_v.event_vlock_ver_daily_copy        | 50.06M  | 5684.45M   | 10077.70M  | 15762.16M  |              1.77 |
| mcmp_crash.exc_launcher_exception_model_osver  | 3.76M   | 11865.00M  | 0.00M      | 11865.00M  |              0.00 |
| mcmp_event_l.event_launcher_attribute_su_daily | 41.70M  | 8022.86M   | 3096.13M   | 11118.98M  |              0.39 |
| mcmp_event_v.event_vlock_attribute_su_daily    | 27.02M  | 5434.39M   | 1335.61M   | 6770.00M   |              0.25 |
| mcmp_launcher.launcher_device_dru_daily_imei   | 48.46M  | 4165.00M   | 0.00M      | 4165.00M   |              0.00 |
| mcmp_support.support_mail_history              | 0.01M   | 2571.52M   | 0.00M      | 2571.52M   |              0.00 |
| columbus_monitor.monitor_mail_history          | 0.01M   | 2314.52M   | 0.00M      | 2314.52M   |              0.00 |
| mobile_interface.mobile_info                   | 5.12M   | 1702.00M   | 0.00M      | 1702.00M   |              0.00 |
| mcmp_f_manager.f_manager_aa_name_addr_pv_daily | 14.20M  | 1251.00M   | 0.00M      | 1251.00M   |              0.00 |
| columbus_monitor.compass_flow                  | 0.02M   | 1064.10M   | 0.00M      | 1064.10M   |              0.00 |
| mcmp_crash.exc_launcher_exception_core_daily   | 5.77M   | 440.00M    | 380.00M    | 820.00M    |              0.86 |
| mcmp_launcher.launcher_channel_ver_daily       | 3.02M   | 501.95M    | 306.06M    | 808.02M    |              0.61 |
+------------------------------------------------+---------+------------+------------+------------+-------------------+
20 rows in set (0.95 sec)

• 本文作者： GaryWu
• 本文链接： https://garywu520.github.io/2018/02/08/查询MySQL占用空间空间排序/
• 版权声明： 本博客所有文章除特别声明外，均采用 MIT 许可协议。转载请注明出处！