MySQL统计数据库和表大小

• 查看该数据库实例下所有库大小（单位:MB）

mysql> select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024   as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;
+--------------------+-------------+--------------+-------------+
| table_schema       | data_length | index_length | sum         |
+--------------------+-------------+--------------+-------------+
| information_schema | 13.09417629 |   1.83886719 | 14.93304348 |
+--------------------+-------------+--------------+-------------+

• 查询MySQL数据库里面的所有数据库各自占用大小

mysql> select table_schema, sum(data_length+index_length)/1024/1024 as total_mb, sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb, count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2 desc;
+--------------------+-------------+------------+------------+--------+------------+
| table_schema       | total_mb    | data_mb    | index_mb   | tables | today      |
+--------------------+-------------+------------+------------+--------+------------+
| weijing_co         | 10.15625000 | 9.21875000 | 0.93750000 |     23 | 2020-05-17 |
| iweijing_cn        |  3.87500000 | 3.07812500 | 0.79687500 |     23 | 2020-05-17 |
| mysql              |  0.89202785 | 0.79730129 | 0.09472656 |     28 | 2020-05-17 |
| information_schema |  0.00976563 | 0.00000000 | 0.00976563 |     59 | 2020-05-17 |
| performance_schema |  0.00000000 | 0.00000000 | 0.00000000 |     52 | 2020-05-17 |
+--------------------+-------------+------------+------------+--------+------------+

• 查询MySQL数据库里面的单个数据库占用大小

mysql> SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size, CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size FROM information_schema.tables WHERE TABLE_SCHEMA = 'weijing_co';
+-----------+--------------------+-----------+------------+
| data_size | max_data_size      | data_free | index_size |
+-----------+--------------------+-----------+------------+
| 9019.79MB | 1677453164543.99MB | 0.00MB    | 626.09MB   |
+-----------+--------------------+-----------+------------+

• 查看单个表的状态

mysql> show table status from 'weijing_co' where name = 'users'\G;
*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 49152
      Data_free: 0
 Auto_increment: 4
    Create_time: 2020-03-20 16:43:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_unicode_520_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

• MySQL数据库里面的单个数据库所有表各自占用大小

mysql> select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA = 'weijing_co' group by TABLE_NAME order by data_length desc;
+------------------------------+-----------+------------+
| TABLE_NAME                   | data_size | index_size |
+------------------------------+-----------+------------+
| posts                        | 997.50 MB | 0.12 MB    |
| options                      | 31.10 MB  | 0.03 MB    |
| meta                         | 40.20 MB  | 0.14 MB    |
| indexable                    | 50.07 MB  | 0.04 MB    |
| comments                     | 0.01 MB   | 0.07 MB    |
| users                        | 0.01 MB   | 0.04 MB    |
| terms                        | 0.01 MB   | 0.03 MB    |
+------------------------------+-----------+------------+

• 查询MySQL数据库里面的单个数据库指定表占用大小

mysql> SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size, CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size FROM information_schema.tables WHERE TABLE_NAME = 'wp_posts';
+-----------+---------------+-----------+------------+
| data_size | max_data_size | data_free | index_size |
+-----------+---------------+-----------+------------+
| 9.01MB    | 0.00MB        | 8.00MB    | 0.18MB     |
+-----------+---------------+-----------+------------+
1 row in set (0.00 sec)