MySQL 数据库信息统计

查询表: information_schema.tables

计算公式:

  • 方法一: 单表占用空间大小 = AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH
  • 方法二: 单表占用空间大小 = DATA_LENGTH

示例: 查看 employees 库中 salaries 表的占用空间大小

1
2
3
4
5
6
7
8
mysql> select table_schema,table_name, 
    -> (avg_row_length * table_rows + index_length) / 1024 / 1024 as data_mb
    -> from tables where table_schema='employees' and table_name = 'salaries';
+--------------+------------+-------------+
| table_schema | table_name | data_mb     |
+--------------+------------+-------------+
| employees    | salaries   | 94.74268913 |
+--------------+------------+-------------+
1
mysql> select table_schema,table_name, data_free / 1024 / 1024 as data_free_mb from tables order by data_free_mb limit 10;