How to view the size of space occupied by a table in MySQL

1. Use SQL query to view table space

1.1 Query the size of all tables (including data and indexes)

SELECT 
    table_schema AS 'Database Name',
    table_name AS 'Table name',
    ROUND(data_length/1024/1024, 2) AS 'Data size(MB)',
    ROUND(index_length/1024/1024, 2) AS 'Index size(MB)',
    ROUND((data_length + index_length)/1024/1024, 2) AS 'Total size(MB)',
    table_rows AS 'Number of rows'
FROM 
    information_schema.TABLES 
WHERE 
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY 
    (data_length + index_length) DESC;

SELECT round(data_length/1024/1024, 2) AS 'Data size(MB)', round(index_length/1024/1024, 2) AS 'Index size(MB)', round((data_length + index_length)/1024/1024, 2) AS 'Total size(MB)'
FROM information_schema.TABLES 
where table_name = 'Table name';

1.2 Query the table size of a specific database

SELECT 
    table_name AS 'Table name',
    ROUND(data_length/1024/1024, 2) AS 'Data size(MB)',
    ROUND(index_length/1024/1024, 2) AS 'Index size(MB)',
    ROUND((data_length + index_length)/1024/1024, 2) AS 'Total size(MB)',
    table_rows AS 'Number of rows'
FROM 
    information_schema.TABLES 
WHERE 
    table_schema = 'Your database name'
ORDER BY 
    (data_length + index_length) DESC;

1.3 Query the detailed spatial information of a single table

SELECT 
    table_name AS 'Table name',
    engine AS 'Storage Engine',
    ROUND(data_length/1024/1024, 2) AS 'Data size(MB)',
    ROUND(index_length/1024/1024, 2) AS 'Index size(MB)',
    ROUND((data_length + index_length)/1024/1024, 2) AS 'Total size(MB)',
    ROUND(data_free/1024/1024, 2) AS 'Fragmented space(MB)',
    table_rows AS 'Number of rows',
    avg_row_length AS 'Average row length(byte)',
    create_time AS 'Creation time',
    update_time AS 'Update time'
FROM 
    information_schema.TABLES 
WHERE 
    table_schema = 'Your database name'
    AND table_name = 'Your table name';

2. Use command line tools to view table space

2.1 Query using mysql client

mysql -uusername -ppassword -e "SELECT table_name AS 'Table name', \
ROUND(data_length/1024/1024,2) AS 'Data size(MB)', \
ROUND(index_length/1024/1024,2) AS 'Index size(MB)', \
ROUND((data_length+index_length)/1024/1024,2) AS 'Total size(MB)' \
FROM information_schema.TABLES \
WHERE table_schema='Your database name' \
ORDER BY (data_length+index_length) DESC;"

2.2 Check physical file size (suitable for MyISAM/InnoDB)

# Switch to MySQL data directorycd /var/lib/mysql/Your database name/

# Check file sizels -lh *.ibd *.frm *.MYD *.MYI

# Calculate the total sizedu -sh ./*

3. Check the space usage details of InnoDB table

3.1 View the InnoDB tablespace status

SHOW TABLE STATUS FROM Your database name LIKE 'Your table name'\G

3.2 Check the status of the InnoDB engine (including buffer pool and other information)

SHOW ENGINE INNODB STATUS\G

3.3 Query InnoDB tablespace file information

SELECT 
    FILE_NAME, 
    TABLESPACE_NAME, 
    ENGINE, 
    TOTAL_EXTENTS, 
    EXTENT_SIZE, 
    INITIAL_SIZE, 
    MAXIMUM_SIZE 
FROM 
    INFORMATION_SCHEMA.FILES 
WHERE 
    FILE_TYPE = 'DATAFILE';

4. Advanced spatial analysis tools

4.1 Using pt-diskstats (Percona toolkit)

pt-diskstats --devices=/var/lib/mysql

4.2 Using pt-mysql-summary (Percona toolkit)

pt-mysql-summary --user=username --password=password

4.3 Using mysqldumpslow to analyze tablespace growth

mysqldumpslow -s t /var/log/mysql/

5. Space optimization related query

5.1 Find severely fragmented tables

SELECT 
    table_schema AS 'database',
    table_name AS 'Table name',
    ROUND(data_free/1024/1024, 2) AS 'Fragmented space(MB)',
    ROUND((data_length + index_length)/1024/1024, 2) AS 'Total size(MB)',
    ROUND((data_free/(data_length + index_length + data_free))*100, 2) AS 'Fragment rate(%)'
FROM 
    information_schema.TABLES 
WHERE 
    table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
    AND data_free &gt; 0
ORDER BY 
    data_free DESC
LIMIT 10;

5.2 View automatic tablespace expansion settings

SELECT 
    table_name, 
    engine, 
    row_format, 
    create_options 
FROM 
    information_schema.TABLES 
WHERE 
    table_schema = 'Your database name';

6. Things to note

Permission requirements: The corresponding permission is required for querying information_schema

Data Accuracy: table_rows is the estimate, especially for InnoDB tables

Storage engine differences:

• InnoDB table data is stored in .ibd files (independent table space) or in shared table space

• MyISAM table data is stored in .MYD file and index is stored in .MYI file

Temporary tablespace: Temporary tables and tables using memory engines are not displayed in disk usage statistics

Binary logs and transaction logs: These log files take up space but are not included in tablespace statistics

7. Example of automated monitoring scripts

#!/bin/bash

# MySQL tablespace monitoring scriptDB_USER="username"
DB_PASS="password"
DB_NAME="Database Name"
OUTPUT_FILE="/tmp/mysql_table_sizes_$(date +%Y%m%d).csv"

echo "Table name,Data size(MB),Index size(MB),Total size(MB),Number of rows,Fragmented space(MB)" &gt; $OUTPUT_FILE

mysql -u$DB_USER -p$DB_PASS -e "SELECT \
    CONCAT(table_name, ',', \
    ROUND(data_length/1024/1024, 2), ',', \
    ROUND(index_length/1024/1024, 2), ',', \
    ROUND((data_length + index_length)/1024/1024, 2), ',', \
    table_rows, ',', \
    ROUND(data_free/1024/1024, 2)) \
FROM information_schema.TABLES \
WHERE table_schema = '$DB_NAME' \
ORDER BY (data_length + index_length) DESC;" &gt;&gt; $OUTPUT_FILE

echo "Report generated: $OUTPUT_FILE"

Through the above methods, you can fully understand the space usage of each table in the MySQL database and provide data support for database optimization and maintenance.

This is the article about how MySQL view the size of space occupied by a certain table. For more information about MySQL viewing table space, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!