查询数据库表大小

以MySQL数据库为例，计算查询数据库中各数据库表的大小；

import pymysql


db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'password',
    'database': 'mysql',
    'charset': 'utf8',
    'cursorclass': pymysql.cursors.DictCursor  # 查询结果以字典形式返回
}

conn = pymysql.connect(**db_config)
cursor = conn.cursor()

def check_all_size():
    """
    计算了MySQL服务器上所有数据库的总数据大小，包括数据和索引。
    总计会显示在第一行，总和列下，可以了解整个MySQL服务器上的数据占用情况。

    :return:
    """
    check_all_db_sql = """
    select
        '总和' as '数据库',
        CONCAT(ROUND(SUM(table_rows) / 1000000, 2), 'M') as '行数',
        CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2), 'GB') as '数据大小',
        CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2), 'GB') as '索引大小',
        CONCAT(ROUND((SUM(data_length) + SUM(index_length)) / (1024 * 1024 * 1024), 2), 'GB') as '总大小'
    from
        information_schema.TABLES
    where
        table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
    """

    cursor.execute(check_all_db_sql)
    for r in cursor.fetchall():
        print(r)

def check_db_size():
    """
    查看 MySQL 连接下所有数据库的数据大小,
    使用了GROUP BY 按数据库名进行分组，
    使用SUM计算每个数据库的总数据长度、总索引长度和总大小：

    :return:
    """
    db_size_sql = """
    select
        table_schema as '数据库',
        sum(data_length) / (1024 * 1024 * 1024) as '数据大小(GB)',
        sum(index_length) / (1024 * 1024 * 1024) as '索引大小(GB)',
        sum(data_length + index_length) / (1024 * 1024 * 1024) as '总大小(GB)'
    from 
        information_schema.tables
    where 
        table_schema not in ('information_schema', 'performance_schema', 'mysql', 'sys')
    group by table_schema
    order by '总大小(GB)' DESC;
    """
    cursor.execute(db_size_sql)
    for r in cursor.fetchall():
        print(r)

def check_single_db_size():
    """
    查看单个MySQL数据库的数据大小
    :return:
    """
    single_db_sql = """
    select
        table_schema as '数据库',
        table_name as '表名',
        concat(round(table_rows / 1000000, 2), 'M') as '行数',
        concat(round(data_length / (1024 * 1024 * 1024), 2), 'GB') as '数据大小',
        concat(round(index_length / (1024 * 1024 * 1024), 2), 'GB') as '索引大小',
        concat(round((data_length + index_length) / (1024 * 1024 * 1024), 2), 'GB') as '总大小'
    from information_schema.tables
    where
        table_schema not in ('information_schema', 'performance_schema', 'mysql', 'sys')
        and table_schema = '查询的数据库名'
    order by
        data_length + index_length desc;
    """
    cursor.execute(single_db_sql)
    for r in cursor.fetchall():
        print(r)

def check_all_tables():
    all_tables_sql = """
    SELECT
        '总和' AS `数据库`,
        '' AS `表名`,
        CONCAT(ROUND(SUM(table_rows) / 1000000, 2), 'M') AS `行数`,
        CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2), 'GB') AS `数据大小`,
        CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2), 'GB') AS `索引大小`,
        CONCAT(ROUND((SUM(data_length) + SUM(index_length)) / (1024 * 1024 * 1024), 2), 'GB') AS `总大小`
    FROM
        information_schema.TABLES
    WHERE
        table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
    UNION
    -- 显示每个表的数据大小
    SELECT
        table_schema AS `数据库`,
        table_name AS `表名`,
        CONCAT(ROUND(table_rows / 1000000, 2), 'M') AS `行数`,
        CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 2), 'GB') AS `表数据大小`,
        CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 2), 'GB') AS `索引大小`,
        CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 2), 'GB') AS `表总大小`
    FROM
        information_schema.TABLES
    WHERE
        table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
    ORDER BY
        `总大小` DESC; -- 修改ORDER BY子句
    """
    cursor.execute(all_tables_sql)
    for r in cursor.fetchall():
        print(r)

if __name__ == "__main__":
    # check_all_size()
    # check_db_size()
    # check_single_db_size()
    check_all_tables()
    pass

参考：在MySQL中查看数据库和表的数据大小_mysql 查询数据库中所有表得大小的数据-CSDN博客