How to query the capacity of database by Mysql

This article mainly shows you how Mysql query database capacity size, content simple and easy to understand, I hope you can learn, after learning will definitely have a harvest, the following let Xiaobian take you to see it.

Query the total size of all databases

The method is as follows:

mysql> use information_schema;mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;+-----------+| data |+-----------+| 3052.76MB |+-----------+1 row in set (0.02 sec)

Count all the database data

Amount of data per table =AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

SELECTSUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mbFROM information_schema.TABLES

Count the size of each library:

SELECTtable_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mbFROM information_schema.TABLES group by table_schema;

The second case: check the size of the specified database, for example: database test, as follows:

mysql> use information_schema;mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='test';+----------+| data |+----------+| 142.84MB |+----------+1 row in set (0.00 sec)

1. View all databases by size

selectable_schema as 'database', sum(table_rows) as ' number of records', sum(truncate(data_length/1024/1024, 2)) as 'data capacity (MB)',sum(truncate(index_length/1024/1024, 2)) as 'index capacity (MB)'from information_schema.tablesgroup by table_schema order by sum(data_length) desc, sum(index_length) desc;

2. View all database table sizes

selectable_schema as 'database', table_name as ' table name', table_rows as 'number of records', truncate(data_length/1024/1024, 2) as ' data capacity (MB)',truncate(index_length/1024/1024, 2) as ' index capacity (MB)'from information_schema. tableorder by data_length desc, index_length desc;

3. View specified database capacity size

Example: selectable_schema as 'database', sum(table_rows) as ' number of records', sum(truncate(data_length/1024/1024, 2)) as 'data capacity (MB)',sum(truncate(index_length/1024/1024, 2)) as 'index capacity (MB)'from information_schema. tables where table_schema='mysql';

4. View the table capacity of the specified database

Example: Check the capacity size of each table in mysql library selecttable_schema as 'database', table_name as ' table name', table_rows as 'number of records', truncate(data_length/1024/1024,2) as ' data capacity (MB)',truncate(index_length/1024/1024,2) as ' index capacity (MB)'from information_schema. tables where table_schema='mysql'order by data_length desc, index_length desc;

off-topic method

Directly use shell command to count the size of mysql data directory (note that only library, not database log size)

Remarks:

data_length: store data size

data_length/1024/1024: Convert bytes to MB

round(sum(data_length/1024/1024),2): take two decimal places

concat(round(sum(data_length/1024/1024),2),'MB '): appends MB to the result

The above is about Mysql how to query the size of the database content, if you have learned knowledge or skills, you can share it to let more people see.