前言

在mysql中有一个默认的数据表information_schema，information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。

再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面，所以请勿删改此表。

代码

• 切换数据库

use information_schema;

• 查看数据库使用大小

SELECT
    concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS DATA 
FROM
TABLES 
WHERE
    table_schema = 'DB_Name';

• 查看表使用大小

SELECT
    concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS DATA 
FROM
TABLES 
WHERE
    table_schema = 'DB_Name' 
    AND table_name = 'Table_Name';

• 查看所有数据库容量大小

SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '数据容量(MB)',
    sum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
GROUP BY
    table_schema 
ORDER BY
    sum( data_length ) DESC,
    sum( index_length ) DESC;

• 查看所有数据表容量大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
ORDER BY
    data_length DESC,
    index_length DESC;

• 查看指定数据库容量大小

SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum( TRUNCATE ( data_length / 1024 / 1024, 2 ) ) AS '数据容量(MB)',
    sum( TRUNCATE ( index_length / 1024 / 1024, 2 ) ) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'DB_Name';

• 查看指定数据库各表容量大小

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
WHERE
    table_schema = 'DB_Name' 
ORDER BY
    data_length DESC,
    index_length DESC;