1. 为什么需要关注MySQL中各库表的大小日常维护MySQL数据库时我经常被问到类似的问题某个业务库到底占了多大空间哪些表是真正的大头磁盘快满了应该先从哪个表下手清理这些问题用肉眼看不出来靠直觉猜更不靠谱。说实话很多开发者和刚入门的DBA都遇到过这种困境——明明连上数据库了却不知道该执行哪条命令才能看到库和表的真实大小。工作几年下来我发现只要把SQL命令写对这些问题十几秒就能解决而且不需要任何额外工具mysql命令行里全都能搞定。这篇文章围绕一个非常具体的场景展开使用SQL命令查看MySQL中各个业务库的大小以及每个库中对应表的大小。我会把查询原理、完整SQL语句、参数含义、常见误区和排查经验全部写清楚。适合刚接触数据库运维的开发者、需要做容量规划的DBA以及那些被老板追问“数据库还能撑多久”的苦逼打工人。先说一下信息系统的组成。MySQL里数据是分层存放的第一层是数据库实例第二层是数据库也叫schema业务上叫库第三层是表。每个库下面挂若干张表每张表由若干行记录组成。所以看大小这件事本质上就是看这三层的空间占用情况。MySQL的数据存在磁盘上通过information_schema这个系统库可以拿到所有库表的元数据其中就包括我们需要的表数据大小和索引大小。2. 查看所有业务库的大小的两条核心SQL2.1 基于information_schema.tables的通用统计MySQL从5.0版本开始就提供了information_schema.tables视图里面每一行对应一张表关键字段有三个TABLE_SCHEMA库名、TABLE_NAME表名、TABLE_ROWS估算行数。要统计每个库的总大小核心就是用TABLE_SCHEMA做GROUP BY分组然后把DATA_LENGTH和INDEX_LENGTH加起来求和。直接上SQL这是我日常用得最频繁的一条SELECT TABLE_SCHEMA AS 业务库, ROUND(SUM(DATA_LENGTH) / 1024 / 1024, 2) AS 数据大小(MB), ROUND(SUM(INDEX_LENGTH) / 1024 / 1024, 2) AS 索引大小(MB), ROUND(SUM(DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS 总大小(MB), COUNT(*) AS 表数量 FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN (mysql, information_schema, performance_schema, sys) GROUP BY TABLE_SCHEMA ORDER BY 总大小(MB) DESC;执行效果大概是这样的业务库数据大小(MB)索引大小(MB)总大小(MB)表数量order_db10240.503072.2513312.75156user_db8192.002048.0010240.0089product_db5120.751024.006144.75234这里我故意用反引号把别名包起来了因为有中文别名。实测在MySQL 5.7和8.0版本上这条SQL都能直接跑通只是8.0版本对information_schema做了性能优化查询速度更快。2.2 一条更简洁的库大小查询如果只需要知道每个库总共占了多少空间不需要拆分成数据大小和索引大小可以把SQL精简成这样SELECT TABLE_SCHEMA AS 数据库名, ROUND(SUM(DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS 总大小(MB) FROM information_schema.tables GROUP BY TABLE_SCHEMA ORDER BY SUM(DATA_LENGTH INDEX_LENGTH) DESC;这条SQL的执行逻辑很简单先算出每张表的数据加索引总字节数按库分组累加再除以两次1024换算成MB单位最后按字节数倒序排列。为什么排序用SUM(DATA_LENGTH INDEX_LENGTH)而不是用别名因为MySQL在GROUP BY之后对别名做ORDER BY在某些版本下会有兼容问题直接用原表达式最稳妥避免不必要的报错。要注意的是这条SQL会把mysql、information_schema这些系统库也统计进去。系统库的大小一般不大但如果想看纯业务库建议加上排除条件。我一般保持注释掉的排除条件在SQL文件的最后一行需要的时候去掉注释就能用。2.3 参数说明DATA_LENGTH和INDEX_LENGTH这两个字段是理解库表大小查询的核心。DATA_LENGTH代表表数据部分占用的字节数对MyISAM引擎来说是数据文件的大小对InnoDB引擎来说则是聚簇索引主键索引所占空间。INDEX_LENGTH代表所有二级索引占用的字节数不包含主键索引因为主键索引已经算在数据部分里了。有个容易混淆的点DATA_LENGTH的名字看起来像是“数据长度”但它不是表的行数也不是某个字段的长度而是整张表的数据存储空间。想要行数要看TABLE_ROWS。不过TABLE_ROWS是估算值在InnoDB引擎下尤其不准只能用于粗略了解数据规模不能作为精确统计依据。MySQL 8.0之后增加了information_schema.innodb_tablespaces和mysql.ibd等文件级别的元数据但日常统计库表大小information_schema.tables仍然是最方便、最通用的途径。3. 查看单库内每张表大小的SQL语句3.1 完整版字段齐全便于深度分析知道了哪个库占用空间大下一步自然要定位这个库里到底哪几张表是大户。此时需要把视角从TABLE_SCHEMA层级下移到TABLE_NAME层级。下面的SQL可以查看指定库这里以某个业务库为例中每张表的大小按总大小倒序SELECT TABLE_NAME AS 表名, ENGINE AS 存储引擎, TABLE_ROWS 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) FROM information_schema.tables WHERE TABLE_SCHEMA order_db ORDER BY (DATA_LENGTH INDEX_LENGTH) DESC LIMIT 50;这里加了LIMIT 50因为业务库通常有成百上千张表只需要关心排名靠前的大表。加了ENGINE字段是因为不同存储引擎的空间构成完全不同看到ENGINE字段能快速区分哪些是InnoDB表、哪些是MyISAM表或者临时表。3.2 极简版只看表名和总大小如果只需要给老板或同事快速列一个大表清单下面的极简版更实用SELECT TABLE_NAME AS 表名, ROUND((DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS 大小(MB) FROM information_schema.tables WHERE TABLE_SCHEMA order_db ORDER BY (DATA_LENGTH INDEX_LENGTH) DESC LIMIT 20;两条SQL的区别仅在于输出列的多少查询逻辑相同。我通常建议日常巡检用简版做容量规划分析用完整版。3.3 动态库名写法避免每条SQL都改库名有时候需要连续查多个库的表大小每次手动改WHERE条件里的库名很麻烦。可以把库名定义成一个变量一次赋值多次使用SET target_schema order_db; SELECT TABLE_NAME AS 表名, ROUND((DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS 总大小(MB) FROM information_schema.tables WHERE TABLE_SCHEMA target_schema ORDER BY (DATA_LENGTH INDEX_LENGTH) DESC LIMIT 50;如果你用客户端工具连接数据库比如各类图形化管理工具还可以把库名做成一个筛选条件每次只改筛选值即可原理和上面这条SQL是一样的。4. 进阶需求查看单表大小与分区表大小4.1 单张表的详细大小排查具体问题或者做表结构优化时经常需要精确查看某一张表的大小。这时不需要GROUP BY直接指定库名和表名即可SELECT TABLE_NAME AS 表名, ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 数据大小(MB), ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 索引大小(MB), ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS 数据大小(GB), ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS 索引大小(GB), TABLE_ROWS AS 估算行数 FROM information_schema.tables WHERE TABLE_SCHEMA order_db AND TABLE_NAME t_order_detail;执行后会得到类似这样的结果位置数据大小(MB)索引大小(MB)估算行数表t_order_detail40960.008192.0082345670看到这个结果如果需要进一步确认物理文件的真实大小还可以执行下面的系统命令查看实际文件情况。需要注意这条命令需要在操作系统层面执行不是在MySQL命令行里执行。ls -lh /data/mysql/data/order_db/如果能看到类似t_order_detail.ibd的文件文件的字节数会非常接近SQL统计的结果。之所以说是接近是因为InnoDB表在频繁写入删除后物理文件可能比逻辑统计值大一些这是碎片导致的正常现象。4.2 分区表的大小统计如果表使用了分区功能information_schema.tables里会把每个分区显示为一行TABLE_NAME字段相同但有一个额外的PARTITION_NAME字段。统计分区表总大小时如果直接用GROUP BY TABLE_NAME多个分区会被合并计算这其实是正确的但如果你想看每个分区分别占多大空间就需要在查询时带上分区信息。查看某张分区表各分区大小的SQL如下SELECT PARTITION_NAME AS 分区名, TABLE_ROWS AS 估算行数, ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 数据大小(MB) FROM information_schema.partitions WHERE TABLE_SCHEMA order_db AND TABLE_NAME t_order_log ORDER BY PARTITION_ORDINAL_POSITION;这个查询最典型的应用场景是按时间分区的日志表想知道哪些时间段的数据量最大或者确认某些历史分区是否该归档、清理。用分区名和大小一对照决策很快。4.3 按库中所有表的碎片情况统计除了大小还有一个和空间相关的指标是碎片率。MySQL表在频繁更新删除之后会产生碎片碎片率高的表即使逻辑大小不大物理文件却可能很大。统计碎片率可以用DATA_FREE字段SELECT TABLE_NAME, ROUND(DATA_FREE / 1024 / 1024, 2) AS 碎片大小(MB), ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 表数据大小(MB), ROUND(DATA_FREE / (DATA_LENGTH INDEX_LENGTH) * 100, 2) AS 碎片率(%) FROM information_schema.tables WHERE TABLE_SCHEMA order_db ORDER BY DATA_FREE DESC LIMIT 20;碎片率超过30%的表可以考虑执行OPTIMIZE TABLE来整理空间不过对于大表来说这个操作会锁表且耗时较长要安排在维护窗口期执行。这个进阶查询是检查库表大小之后很自然的下一步。5. 实操案例从零开始诊断数据库空间增长5.1 问题背景与排查思路某业务系统上线几个月后监控报警提示磁盘使用率超过85%。运维同事初步怀疑是MySQL的数据文件增长过快但不确定是哪个库、哪张表导致的。因为库有十几个表加起来超过两千张逐个人工去看不现实。排查思路分四步走第一步确认确实是MySQL占用了大量磁盘排除日志文件和其他程序的影响第二步查看所有业务库的大小排名定位大库第三步进入大库查看表大小排名定位大表第四步分析大表的增长趋势和碎片情况确定原因5.2 通过SQL快速定位执行第一步的库大小统计SQL之后结果非常明确业务库总大小(GB)表数量analytics_db486.2045log_db210.8512user_db150.1288分析型数据库占了大头这通常意味着其中有几张统计宽表数据量巨大。继续深入这个库查看表排名表名数据大小(GB)索引大小(GB)估算行数event_click_detail405.3052.0112.5亿event_exposure_detail36.228.023.1亿user_profile_snapshot22.555.508000万问题一下明朗了。event_click_detail这一张表就占了整个库的80%以上而这又和业务特点相符——埋点点击事件明细表只增不改数据天然只进不出增长迅速。再进一步查看这张表的碎片率和历史数据分布发现存在大量超过保留周期的过期数据。最终给出的优化方案是按日期对这张表做分区定期清理过期分区并对保留数据做压缩处理。这个诊断过程全程只靠前面几条SQL命令没有借助任何外部监控平台。5.3 从大小查询到清理动作这里补充一个很多人容易忽略的操作删除数据后空间并没有直接释放给操作系统。对InnoDB表来说大量DELETE之后物理文件还是那么大只是标记为空闲页。真正要回收空间需要执行OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB。所以在大表问题上常见的处理路径是用SQL查出最大的几张表确认数据保留策略把过期数据DELETE掉在维护窗口执行OPTIMIZE TABLE整理碎片确认物理文件大小降下来当然对于特别大的表直接OPTIMIZE可能会占用大量IO和磁盘临时空间更稳妥的做法是使用在线DDL工具或者做表重建。这些都是空间诊断之后的延伸操作但知道这个完整链路才能算是真的把库表大小这个问题彻底吃透。6. 常见问题与踩坑实录6.1 TABLE_ROWS数值不准确别被估算值带偏InnoDB引擎的TABLE_ROWS是采样估算值不是精确值。同一个表可能估算出来8000万行实际count(*)数出来是9500万行偏差甚至超过20%。对比统计信息和实际值的SQL如下SELECT TABLE_ROWS AS 估算行数 FROM information_schema.tables WHERE TABLE_SCHEMA user_db AND TABLE_NAME t_user; SELECT COUNT(*) AS 实际行数 FROM user_db.t_user;COUNT(*)在千万级大表上执行会非常慢甚至可能锁住线上业务生产环境慎用。从这个角度说TABLE_ROWS虽然不准但好在它快适合做快速判断。记住它的定位用于趋势判断和相对比较不用于绝对值统计。6.2 大小统计结果和实际文件对不上一种情况是统计出来的大小比物理文件小这通常是因为Data Free和碎片还没算进去。例如一张表逻辑数据是10G物理文件是12G那多出来的2G基本就是碎片和未使用的页。另一种情况是统计出来的大小比物理文件大我遇到过几次原因是曾经有过大批量导入或在线DDL旧的表文件还没有被完整清理。如果确认发生过类似操作可以查一下information_schema.innodb_tablespaces里的文件信息和操作系统ls命令做对比。不管是哪种偏差只要偏差比例在合理范围内技术上都算正常不必过度纠结。6.3 DROP大表时IO卡死建议先建硬链接清理大表空间时一定要留一个心眼。直接执行DROP TABLE命令InnoDB需要在内部删除对应的.ibd文件几GB甚至几百GB的文件删除过程会把磁盘IO拉满线上业务可能因此出现卡顿甚至间歇性中断。稳妥的做法是在操作系统层面先对.ibd文件创建硬链接然后进MySQL执行DROP TABLE再在操作系统层面慢慢地用coreutils工具或分批删除方式释放空间。这套操作比较细节用到的具体命令因操作系统而异我在这里不展开写具体命令但思路值得记下来先别让SQL直接删大文件通过硬链接解除关联之后再慢慢删。6.4 查询information_schema慢需要加条件information_schema.tables在表数量非常多的情况下全表扫描会比较慢特别是在5.7版本中更明显。如果执行一条未加WHERE条件的查询可能需要十几秒才能返回。解决办法很简单查询时必须引入TABLE_SCHEMA过滤条件能极大缩小扫描范围尽量使用MySQL 8.0以上版本8.0对information_schema相关查询做了较多优化大库统计可以放在非高峰时段执行避开白天高负载期间6.5 权限问题导致查询结果为空有些账号只有某几个库的权限执行information_schema的查询时结果集可能自动过滤掉没有权限的库表返回的内容看起来不完整。这是MySQL的权限设计特性不是bug。解决方式两种要么用管理员账号执行要么确认自己需要查询的库确实有权限。另外还要注意如果在Navicat这类工具里执行查询由于连接用户权限限制可能看不到mysql库下的表也会影响最终统计结果。遇到这种情况换个有权限的账号再跑一次就是。7. 自动化监控脚本一劳永逸的空间巡检方案手动拼SQL固然能解决问题但每次都去敲一遍确实繁琐。我习惯把核心查询封装成一个巡检脚本每次需要时直接执行输出一份清晰的空间报告。7.1 每天定时输出各库大小快照可以在Linux服务器上写一个简单的Shell脚本每天凌晨执行一次把结果追加到日志文件里。脚本逻辑大致是#!/bin/bash MYSQL_CMDmysql -u监控账号 -p密码 -h 127.0.0.1 LOG_FILE/var/log/mysql_space_report.log DATE_STR$(date %Y-%m-%d) echo $DATE_STR $LOG_FILE $MYSQL_CMD -e SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS 总大小(GB) FROM information_schema.tables GROUP BY TABLE_SCHEMA ORDER BY SUM(DATA_LENGTH INDEX_LENGTH) DESC; $LOG_FILE这个脚本的核心价值在于形成历史数据可以观察到哪个库在一个月内增长最快为容量规划提供数据支撑。单纯的当前快照只能看到现状历史趋势才能看出问题。7.2 表大小变动的对比思路如果想知道某张表一周内增长了多少可以每周保存一次表大小快照然后对比两次记录中的DATA_LENGTH差值。实际做法一般是一张统计表CREATE TABLE IF NOT EXISTS table_size_history ( stat_date DATE, table_schema VARCHAR(64), table_name VARCHAR(64), data_length BIGINT, index_length BIGINT, PRIMARY KEY (stat_date, table_schema, table_name) );每周把information_schema.tables里的数据INSERT到这张历史表里之后就能用一条简单的SQL查询增长率SELECT a.table_name, ROUND((b.data_length - a.data_length) / 1024 / 1024 / 1024, 2) AS 周增长(GB) FROM table_size_history a LEFT JOIN table_size_history b ON a.table_schema b.table_schema AND a.table_name b.table_name WHERE a.stat_date 2024-06-01 AND b.stat_date 2024-06-08 ORDER BY (b.data_length - a.data_length) DESC LIMIT 20;这套方案适合中小规模业务不会给数据库带来明显负担。数据量特别大的环境可以考虑用专门的监控系统来采集原理本质上是一样的只是传输和存储方式更优化。8. 补充经验如何选择统计周期与关注重点在日常运维中库表大小统计的频率需要根据业务特点来确定。对稳定运行的业务系统一周一次足够对活动促销期间的业务可能需要每天一次。不管频率如何有对比才有意义所以我个人强烈建议保留历史快照不要只看即时数据。关注重点方面我觉得有几个维度非常值得参考总库大小是否接近磁盘容量的某个警戒线比如80%单表大小超过50GB的大表有哪些这些表是否有合理的数据归档策略增长特别快的表和业务行为能否对应上比如是否上线了新功能导致写入量暴增碎片率高的表是否需要安排维护窗口整理这些判断全都可以基于information_schema的查询结果来做不需要额外安装复杂组件。配合这个思路前面那些SQL就不只是“查看大小”的工具而是一整套空间管理方法的基础。最后再说一点个人经验数据库空间问题的核心往往不是“变大”本身而是“无计划地变大”。只要掌握了定期统计、主动对比、及时归档这三板斧大多数空间故障都可以提前预防。而这一切的第一步就是从会用SQL查看库表大小开始。
