Mysql-如何查找表的大小

1.mysql 如何查找表的大小

1.1 切换到information_schema数据库，查询tables表

use information_schema

1.2 查询所有数据的大小

mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2), 'MB') table_size from tables; 
+-------------+
| table_size  |
+-------------+
| 202546.00MB |
+-------------+
1 row in set (30.95 sec)

1.3 查看指定数据库实例的大小，比如说数据库MC_ANN

mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2), 'MB') as  table_size from TABLES where table_schema='MC_ANN; 
+------------+
| table_size |
+------------+
| 89659.23MB |
+------------+
1 row in set (10.53 sec)

1.4 查看指定数据库的表的大小，比如说数据库 MC_ANN中的app_m_info表

mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as  table_size
    -> from TABLES where table_schema='MC_ANN'
    -> and table_name='app_m_info';
+------------+
| table_size |
+------------+
| 3337.00MB  |
+------------+
1 row in set (0.31 sec)

1.5 查询每一个表的大小

select table_name, concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data 
from TABLES where table_schema='MC_ANN‘ group by table_name with rollup;

 

 

小知识，随记之。