A3-07-01.MySQL中查询数据库或表大小语句

 参考：http://www.ywnds.com/?p=4422

　　在MySQL中可以通过在information_schema数据库中查询有关数据库的很多信息，如果想知道MySQL数据库中每个库或每个表占用的空间、以及表索引占用的空间和表记录的行数的话，可以通过其中的一个TABLES表进行查看，TABLES表中的数据来查看当前某个库的大小，某个表的大小，以及某个表的索引大小和行记录等等。这个表主要字段分别是：

    TABLE_SCHEMA : 数据库名

    TABLE_NAME：表名

    ENGINE：所使用的存储引擎

    TABLE_ROWS：记录数

    DATA_LENGTH：数据大小

    INDEX_LENGTH：索引　　

其他字段请参考MySQL的手册，我们只需要了解这几个就足够了。
1）查看单个数据库的数据大小

SELECT sum(DATA_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';

 2）查看单个数据库的索引大小

SELECT sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';

 3）查看单个数据库的数据加索引大小（等于库占用空间的大小）

SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';

 示例如下，数据加索引大小：

root@localhost [information_schema]>SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='sakila';
+------------------------------------+
| sum(DATA_LENGTH)+sum(INDEX_LENGTH) |
+------------------------------------+
|                            6766592 |
+------------------------------------+
1 row in set (0.02 sec)

--得到的结果是以字节为单位，除1024为K，除1048576为M，如下：
root@localhost [information_schema]>SELECT concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') FROM information_schema.TABLES where TABLE_SCHEMA='sakila';
+--------------------------------------------------------------+
| concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') |
+--------------------------------------------------------------+
| 6.4531M                                                      |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

 4）查看单个表的数据大小

SELECT sum(DATA_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名' AND table_name='表名';

 5）查看单个表的索引大小

SELECT sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名' AND table_name='表名';

 6）查看单个表的数据加索引大小（等于表占用空间的大小）

SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名' AND table_name='表名';

 

示例如下，单个表数据加索引大小：

root@localhost [information_schema]>SELECT concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') FROM information_schema.TABLES where TABLE_SCHEMA='sakila' AND TABLE_NAME='film';
+--------------------------------------------------------------+
| concat((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024),'M') |
+--------------------------------------------------------------+
| 0.2656M                                                      |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

7）查看单个表的行数

root@localhost [information_schema]>SELECT sum(TABLE_ROWS) FROM information_schema.TABLES where TABLE_SCHEMA='sakila' AND table_name='film';
+-----------------+
| sum(TABLE_ROWS) |
+-----------------+
|            1000 |
+-----------------+
1 row in set (0.00 sec)

8）查看表使用的存储引擎

select ENGINE from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='sakila' and table_name='film';

查看库中所有表的大小并排序

root@localhost [information_schema]>SELECT table_schema,table_name,(data_length/1024/1024),(index_length/1024/1024) FROM information_schema.tables where table_schema='sakila' order by data_length;\
+--------------+----------------------------+-------------------------+--------------------------+
| table_schema | table_name                 | (data_length/1024/1024) | (index_length/1024/1024) |
+--------------+----------------------------+-------------------------+--------------------------+
| sakila       | sales_by_store             |                    NULL |                     NULL |
| sakila       | sales_by_film_category     |                    NULL |                     NULL |
| sakila       | customer_list              |                    NULL |                     NULL |
| sakila       | nicer_but_slower_film_list |                    NULL |                     NULL |
| sakila       | actor_info                 |                    NULL |                     NULL |
| sakila       | film_list                  |                    NULL |                     NULL |
| sakila       | staff_list                 |                    NULL |                     NULL |
| sakila       | country                    |              0.01562500 |               0.00000000 |
| sakila       | language                   |              0.01562500 |               0.00000000 |
| sakila       | category                   |              0.01562500 |               0.00000000 |
| sakila       | store                      |              0.01562500 |               0.03125000 |
| sakila       | actor                      |              0.01562500 |               0.01562500 |
| sakila       | city                       |              0.04687500 |               0.01562500 |
| sakila       | film_category              |              0.06250000 |               0.01562500 |
| sakila       | staff                      |              0.06250000 |               0.03125000 |
| sakila       | customer                   |              0.07812500 |               0.04687500 |
| sakila       | address                    |              0.09375000 |               0.01562500 |
| sakila       | inventory                  |              0.17187500 |               0.18750000 |
| sakila       | film_text                  |              0.17187500 |               0.01562500 |
| sakila       | film_actor                 |              0.18750000 |               0.07812500 |
| sakila       | film                       |              0.18750000 |               0.07812500 |
| sakila       | rental                     |              1.51562500 |               1.14062500 |
| sakila       | payment                    |              1.51562500 |               0.60937500 |
+--------------+----------------------------+-------------------------+--------------------------+
23 rows in set (0.01 sec)

其实也可以使用show语法来查看表详细信息。

root@localhost [sakila]>show table status like 'film'\G
*************************** 1. row ***************************
           Name: film
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1000
 Avg_row_length: 196
    Data_length: 196608
Max_data_length: 0
   Index_length: 81920
      Data_free: 0
 Auto_increment: 1001
    Create_time: 2018-08-28 18:26:23
    Update_time: 2018-08-28 18:26:29
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

查出来的跟information_schema.TABLES表中的信息一致。