Mysql统计库表大小sql整理

素颜马尾好姑娘i 2022-09-09 06:43 127阅读 0赞

1. 查看该数据库实例下所有库大小，得到的结果是以MB为单位

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>table_schema,sum(data_length)/1024/1024&nbsp;</code><code>as</code>&nbsp;<code>data_length,sum(index_length)/1024/1024 \</code> <code>as</code>&nbsp;<code>index_length,sum(data_length+index_length)/1024/1024&nbsp;</code><code>as</code>&nbsp;<code>sum&nbsp;</code><code>from</code>&nbsp;<code>information_schema.tables;</code> <code>+--------------------+---------------+--------------+---------------+</code> <code>| table_schema&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | data_length&nbsp;&nbsp; | index_length | sum&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+--------------------+---------------+--------------+---------------+</code> <code>| information_schema | 2734.92757511 |&nbsp; 86.27539063 | 2821.20296574 |</code> <code>+--------------------+---------------+--------------+---------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

2、查看该实例下各个库大小

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 </td> 
 <td> <code>mysql&gt;&nbsp;&nbsp;</code><code>select</code>&nbsp;<code>table_schema, sum(data_length+index_length)/1024/1024&nbsp;</code><code>as</code>&nbsp;<code>total_mb, \</code> <code>sum(data_length)/1024/1024&nbsp;</code><code>as</code>&nbsp;<code>data_mb, sum(index_length)/1024/1024&nbsp;</code><code>as</code>&nbsp;<code>index_mb, \</code> <code>count(*)&nbsp;</code><code>as</code>&nbsp;<code>tables, curdate()&nbsp;</code><code>as</code>&nbsp;<code>today&nbsp;</code><code>from</code>&nbsp;<code>information_schema.tables&nbsp;</code><code>group</code>&nbsp;<code>by</code>&nbsp;<code>table_schema order&nbsp;</code><code>by</code>&nbsp;<code>2 desc;</code> <code>+--------------------+---------------+---------------+-------------+--------+------------+</code> <code>| table_schema&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | total_mb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | data_mb&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | index_mb&nbsp;&nbsp;&nbsp; | tables | today&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> <code>+--------------------+---------------+---------------+-------------+--------+------------+</code> <code>| data_1234567890&nbsp;&nbsp;&nbsp; | 2820.59610939 | 2734.39689064 | 86.19921875 |&nbsp;&nbsp;&nbsp;&nbsp; 65 | 2015-11-02 |</code> <code>| mysql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 0.60579967 |&nbsp;&nbsp;&nbsp; 0.53744030 |&nbsp; 0.06835938 |&nbsp;&nbsp;&nbsp;&nbsp; 14 | 2015-11-02 |</code> <code>| information_schema |&nbsp;&nbsp;&nbsp; 0.00781250 |&nbsp;&nbsp;&nbsp; 0.00000000 |&nbsp; 0.00781250 |&nbsp;&nbsp;&nbsp;&nbsp; 35 | 2015-11-02 |</code> <code>+--------------------+---------------+---------------+-------------+--------+------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

3、查看单个库的大小

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 </td> 
 <td> <code>mysql&gt;&nbsp;</code><code>select</code>&nbsp;<code>concat(truncate(sum(data_length)/1024/1024,2),</code><code>'mb'</code><code>)&nbsp;</code><code>as</code>&nbsp;<code>data_size, \</code> <code>concat(truncate(sum(max_data_length)/1024/1024,2),</code><code>'mb'</code><code>)&nbsp;</code><code>as</code>&nbsp;<code>max_data_size, \</code> <code>concat(truncate(sum(data_free)/1024/1024,2),</code><code>'mb'</code><code>)&nbsp;</code><code>as</code>&nbsp;<code>data_free, \</code> <code>concat(truncate(sum(index_length)/1024/1024,2),</code><code>'mb'</code><code>)&nbsp;</code><code>as</code>&nbsp;<code>index_size\</code> <code>&nbsp;</code><code>from</code>&nbsp;<code>information_schema.tables&nbsp;</code><code>where</code>&nbsp;<code>table_schema =&nbsp;</code><code>'erongtu_tyb2014'</code><code>;&nbsp;</code> <code>+-----------+------------------+-----------+------------+</code> <code>| data_size | max_data_size&nbsp;&nbsp;&nbsp; | data_free | index_size |</code> <code>+-----------+------------------+-----------+------------+</code> <code>| 2734.40mb | 83483426815.99mb | 14.06mb&nbsp;&nbsp; | 86.19mb&nbsp;&nbsp;&nbsp; |</code> <code>+-----------+------------------+-----------+------------+</code> </td> 
 </tr> 
 </tbody> 
</table>

4、查看单个表的状态

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 </td> 
 <td> <code>mysql&gt; show table status&nbsp;</code><code>from</code>&nbsp;<code>data_1234567890&nbsp;</code><code>where</code>&nbsp;<code>name =&nbsp;</code><code>'data_1234567890_ss'</code>&nbsp;<code>\G</code> <code>*************************** 1. row ***************************</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Name: data_1234567890_ss</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Engine: InnoDB</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Version: 10</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Row_format: Compact</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Rows: 840065</code> <code>&nbsp;</code><code>Avg_row_length: 477</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Data_length: 401473536</code> <code>Max_data_length: 0</code> <code>&nbsp;&nbsp;&nbsp;</code><code>Index_length: 0</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Data_free: 6291456</code> <code>&nbsp;</code><code>Auto_increment: 882251</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Create_time: 2015-09-07 17:24:18</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Update_time: NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Check_time: NULL</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Collation: utf8_general_ci</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Checksum: NULL</code> <code>&nbsp;</code><code>Create_options:</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Comment:</code> <code>1 row&nbsp;</code><code>in</code>&nbsp;<code>set</code>&nbsp;<code>(0.00 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

5、查看单库下所有表的状态

*  作者：[踏雪无痕][Link 1]
 *  出处：[http://www.cnblogs.com/chenpingzhao/][http_www.cnblogs.com_chenpingzhao]

[Link 1]: http://weibo.com/chenpingzhao
[http_www.cnblogs.com_chenpingzhao]: http://www.cnblogs.com/chenpingzhao/