MySql查看数据库及表容量大小并排序

MySql查看数据库及表容量⼤⼩并排序查看所有数据库容量⼤⼩

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 = 'your_table_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 = '指定的库名' 
ORDER BY
    data_length DESC,
    index_length DESC;

查看mysql各表数据存储碎片大小

SELECT
    table_schema AS '库名',
    table_name AS '表名',
    ENGINE AS '存储引擎',
    table_rows AS '行数',
    trim(
        concat(
            round(DATA_LENGTH / 1024 / 1024, 1)
        )
    ) AS '数据大小MB',
    trim(
        round(index_length / 1024 / 1024, 1)
    ) AS '索引大小MB',
    trim(
        round(DATA_FREE / 1024 / 1024, 1)
    ) AS '碎片大小MB'
FROM
    information_schema. TABLES
WHERE
    table_schema NOT IN (
        'information_schema',
        'phpmyadmin',
        'scripts',
        'test',
        'performance_schema',
        'mysql'
    )
AND DATA_FREE / 1024 / 1024 > 1000
ORDER BY
    DATA_FREE DESC;

mysql更新数据碎片刷新

OPTIMIZE TABLE 表名;