概述

今天主要介绍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方面的内容，感兴趣的朋友可以关注下！