Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to view the space occupied by tables in MySQL. Many people may not know much about it. In order to let everyone know more, Xiaobian summarized the following contents for everyone. I hope everyone can gain something according to this article.

code

1, switch database

use information_schema;

2. Check the database usage size

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’DB_Name’ ;

3. Check the table usage size

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’DB_Name’ and table_name=’Table_Name’;

Find one online, pro-test available:

MySQL comes with its own management library: information_schema

Data_length,index_length

Your own database name: dbname

Your own table name: tablename

mysql> use information_schema; Database changed mysql> select data_length,index_length -> from tables where -> table_schema='dbname' -> and table_name = 'tablename'; +-------------+--------------+ | data_length | index_length | +-------------+--------------+ | 166379520 | 235782144 | +-------------+--------------+ row in set (0.02 sec)mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB -> from tables where -> table_schema='dbname' -> and table_name = 'tablename'; +----------------+-----------------+ | data_length_MB | index_length_MB | +----------------+-----------------+ | 158.67MB | 224.86MB | +----------------+-----------------+ row in set (0.03 sec)

1. View all database sizes

select table_schema as 'database', sum(table_rows) as ' number', 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 group by table_schema order by sum(data_length) desc, sum(index_length) desc; ``` ### 2. View all database table capacity ``sql select table_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 order by data_length desc, index_length desc;

3. View specified database capacity size

Example: Check the size of mysql library

select table_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 of each table in mysql library

select table_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 'MB' from information_schema.tables where table_schema='mysql' order by data_length desc, index_length desc; select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB from tables where table_schema='passport' and table_name='tb_user_info';

-- 569.98MB 141.98MB

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB from tables where table_schema='passport_v2' and table_name='tb_user_info';

-- 2128.94MB 285.00MB

After reading the above, do you have any further understanding of how to check the table footprint in MySQL? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.