MySQL数据库大小查询结果异常，如何获取真实数据量？

我搭建了一个集中式syslog系统，将6台服务器的日志存储至MySQL数据库中，并在PRTG中配置了MySQL传感器监控数据库大小。尽管每秒都有数据写入数据库，但传感器连续多日显示大小始终为672MB。我通过SQL语句直接在数据库中查询，以及通过PhpMyAdmin查看，得到的结果均与PRTG一致，这显然不符合实际情况。

为了监控数据库当前真实大小，我编写了脚本，通过MySQL Dump生成数据库镜像，将文件大小保存至文本文件后，再通过SNMP导入PRTG。

我用于查询数据库大小的SQL语句为：

SELECT table_schema "Syslogging_DB", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

我多日来一直寻找解决办法，希望能获取数据库的真实大小，但未找到针对该问题的答案。恳请有人解释为何information_schema.tables多日来显示的数据库大小始终错误，以及我应使用何种SQL语句来获取数据库的当前真实大小？

我目前的临时解决办法是通过crontab每小时执行一行命令：

mysql -D YOUR_DB -e "analyze table YOUR_TABLE"

为什么information_schema.tables数据不更新？ #

information_schema.tables里的data_length和index_length属于MySQL的表统计信息，并非实时更新。只有当表执行ANALYZE TABLE、ALTER TABLE这类操作，或者数据量变化达到阈值（InnoDB默认是10%）触发自动统计更新时，MySQL才会重新计算并刷新这些数值。如果你的syslog表只是持续写入，没触发上述更新条件，统计信息就会停留在旧值，导致查询结果失真。

获取真实数据库大小的SQL语句 #

方法1：查询物理文件大小（最准确） #

若你的MySQL使用InnoDB引擎且开启了独立表空间（默认innodb_file_per_table=ON），可以直接查询information_schema.FILES获取物理文件的实时大小：

SELECT 
    table_schema AS '数据库名',
    ROUND(SUM(data_length) / 1024 / 1024, 1) AS '数据文件大小(MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 1) AS '索引文件大小(MB)',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS '总大小(MB)'
FROM information_schema.FILES
WHERE table_schema = 'Syslogging_DB' -- 替换为你的数据库名
GROUP BY table_schema;

该语句直接读取InnoDB物理文件的实际占用量，结果实时准确。

方法2：刷新统计后查询 #

如果仍需使用information_schema.tables，可以先手动刷新统计信息再查询：

ANALYZE TABLE YOUR_TABLE; -- 替换为你的表名
SELECT 
    table_schema "Syslogging_DB", 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
WHERE table_schema = 'Syslogging_DB'
GROUP BY table_schema;

这种方式需要主动触发统计更新，适合无法直接查询FILES的场景。

优化临时方案 #

你当前用crontab定时执行ANALYZE TABLE的方法可行，但大表执行该操作会占用资源，可做如下调整：

• 根据业务写入频率调整执行间隔，比如从每小时一次改为每2小时一次
• 确认InnoDB的innodb_stats_auto_recalc参数是否开启（默认开启），它会在表数据变化超过10%时自动刷新统计，减少手动执行的需求

内容的提问来源于stack exchange，提问作者harryIT