概述

今天主要介紹MySQL查看資料庫表容量大小的幾個方法，僅供參考。

1、查看所有資料庫容量大小

SELECT
\ttable_schema AS '資料庫',
\tsum( table_rows ) AS '記錄數',
\tsum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '數據容量(MB)',
\tsum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
\tinformation_schema.TABLES 
GROUP BY
\ttable_schema 
ORDER BY
\tsum( data_length ) DESC,
\tsum( index_length ) DESC;

2、查看所有資料庫各表容量大小

SELECT
\ttable_schema AS '資料庫',
\ttable_name AS '表名',
\ttable_rows AS '記錄數',
\tTRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數據容量(MB)',
\tTRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
\tinformation_schema.TABLES 
ORDER BY
\tdata_length DESC,
\tindex_length DESC;

3、查看指定資料庫容量大小

SELECT
\ttable_schema AS '資料庫',
\tsum( table_rows ) AS '記錄數',
\tsum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '數據容量(MB)',
\tsum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
\tinformation_schema.TABLES 
WHERE
\ttable_schema = 'mysql';

4、查看指定資料庫各表容量大小

SELECT
\ttable_schema AS '資料庫',
\ttable_name AS '表名',
\ttable_rows AS '記錄數',
\tTRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數據容量(MB)',
\tTRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
\tinformation_schema.TABLES 
WHERE
\ttable_schema = 'mysql' 
ORDER BY
\tdata_length DESC,
\tindex_length DESC;

後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下！