MySQL查看库表的大小

MySQL数据库空间使用情况查询

如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话，可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表，这个表主要字段分别是：

TABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小
其他字段请参考MySQL的手册，这几个字段对我们来说最有用。
一个表占用空间的大小，相当于是 数据大小 + 索引大小,

进入information_schema库

USE information_schema;

1.查看MySQL某个库的大小;

MariaDB [information_schema]> select concat(round(((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024),2),"MB") as db_size from information_schema.tables  where table_schema='DB_NAME';   
+------------+
| db_size    |
+------------+
| 11199.94MB |
+------------+
1 row in set (0.26 sec)

2.查看库中每张表的大小以及行数；

MariaDB [information_schema]> SELECT TABLE_NAME as "表名",DATA_LENGTH+INDEX_LENGTH as "数据+索引",TABLE_ROWS as "行数" FROM information_schema.TABLES WHERE TABLE_SCHEMA='DB_NAME';
+-------------------------------+---------------+----------+
| 表名                          | 数据+索引     | 行数     |
+-------------------------------+---------------+----------+
| tbl_aba|         16384 |        5 |
| tbl_abb|   11691622400 | 35265564 |
| tbl_abc|      20529152 |    51291 |
| tbl_abd|       4227072 |    11475 |
| tbl_abe|      17383424 |    41664 |
| tbl_abf|         16384 |        4 |
| tbl_abg|         16384 |       17 |
| tbl_abh|         16384 |       18 |
| tbl_abi|         16384 |       40 |
| tbl_abj|         16384 |        5 |
| tbl_abk|         16384 |        7 |
| tbl_abl|         16384 |       17 |
| tbl_abm|         16384 |       17 |
| tbl_abn|         16384 |       37 |
+-------------------------------+---------------+----------+
20 rows in set (0.22 sec)

3.查看某张表数据数据、索引等信息的量；

MariaDB [information_schema]> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data_size,
    -> concat(round(sum(MAX_DATA_LENGTH/1024/1024),2),'MB') as max_data_size,
    -> concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as index_size,
    -> concat(round(sum(DATA_FREE/1024/1024),2),'MB') as data_free
    -> from TABLES where table_schema='DB_NAME' and table_name='TABLES_NAME';
+-----------+---------------+------------+-----------+
| data_size | max_data_size | index_size | data_free |
+-----------+---------------+------------+-----------+
| 6397.00MB | 0.00MB        | 4753.00MB  | 7.00MB    |
+-----------+---------------+------------+-----------+
1 row in set (0.20 sec)

转载于:https://www.cnblogs.com/baolin2200/p/7568904.html