在 MySQL 中查看数据库规模大小（表大小、数据库总大小等），可以使用以下 SQL 查询脚本：

1. 查看所有数据库的总大小（MB）

SELECT 
    table_schema AS "数据库名",
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "总大小(MB)",
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS "数据大小(MB)",
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS "索引大小(MB)"
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

2. 查看当前数据库所有表的大小

SELECT 
    table_name AS "表名",
    ROUND((data_length / 1024 / 1024), 2) AS "数据大小(MB)",
    ROUND((index_length / 1024 / 1024), 2) AS "索引大小(MB)",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "总大小(MB)",
    table_rows AS "行数"
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

3. 查看单个表的大小详情

SELECT 
    table_name AS "表名",
    ROUND(data_length / 1024 / 1024, 2) AS "数据大小(MB)",
    ROUND(index_length / 1024 / 1024, 2) AS "索引大小(MB)",
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS "总大小(MB)",
    table_rows AS "行数",
    AVG_ROW_LENGTH AS "平均行长度(bytes)"
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name'  -- 替换为数据库名
  AND table_name = 'your_table_name';      -- 替换为表名

4. 查看服务器上所有表空间的总大小

SELECT 
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS "总数据量(GB)",
    ROUND(SUM(data_free) / 1024 / 1024 / 1024, 2) AS "未使用空间(GB)"
FROM information_schema.TABLES;

5. 监控表大小增长的简易命令

-- 定期运行（每月/每周）
SELECT
    table_schema AS "数据库名",
    table_name AS "表名",
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS "当前大小(MB)",
    CURDATE() AS "统计日期"
FROM information_schema.TABLES
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');

⚠️ 重要说明：

  1. 所有查询都基于 information_schema 数据库，这是 MySQL 内置的元数据数据库
  2. table_rows 是近似值（对 InnoDB 尤其如此），非精确计数
  3. 需要 SELECT 权限访问 information_schema
  4. 使用 SHOW TABLE STATUS 命令也可获得类似信息（可读性更好但不易过滤）：
    SHOW TABLE STATUS FROM your_database_name;

💡 性能提示：

在大型数据库上查询时，可以添加条件减少返回数据量：

WHERE (data_length + index_length) > 100 * 1024 * 1024  -- 仅显示大于100MB的表

这些查询能帮助您快速了解 MySQL 数据库的存储使用情况，识别大表以便进行优化或清理。