统计mysql说有表的大小_MySQL统计数据库和表大小

MySQL统计数据库的schema和table时，可以通过以下SQL命令进行查询。

查看该数据库实例下所有库大小(单位: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)