mysql 5.7.x版本查看某张表、库的大小 思路方案说明

合集 - 使用数据库相关(10)

1.数据库 校验名称唯一性，用于新增和修改功能2024-11-032.java实现“数据平滑升级”2024-11-033.mysql编写sql脚本：要求表没有主键，但是想查询没有相同值的时候才进行插入2024-11-034.MySql5.6版本开启慢SQL功能-本次采用永久生效方式2024-11-035.开启慢SQL设置long_query_time=0.1为啥会统计的sql却存在小于100毫秒的sql2024-11-036.SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型2024-11-037.Mybatis使用注解方式实现批量更新、批量新增2024-11-03

8.mysql 5.7.x版本查看某张表、库的大小 思路方案说明2024-11-03

9.定时任务频繁插入数据导致锁表问题 -＞ 查询mysql进程2024-11-0310.postgresql和mysql中的limit使用方法2024-11-03

收起

@

目录

• 摘要
• （推荐）第一种方案：查询information_schema.TABLES的字段DATA_LENGTH
  • 使用场景
  • 优点：网上大多案例都是查询系统表的字段DATA_LENGTH去计算大小
  • 缺点：DATA_LENGTH不都是实时更新的，由表引擎决定，同时DATA_LENGTH是压缩后的字节大小（及大小经过压缩后计算得到DATA_LENGTH）
• 第二种方案:查询information_schema.INNODB_SYS_TABLESPACES的字段FILE_SIZE
  • 使用场景
  • 优点：针对表引擎为INNODB情况下能够实时更新并获取表大小
  • 缺点：FILE_SIZE字段值大小为未经过压缩的表大小
• （不推荐）第三种方案：禁用 innodb_stats_persistent=OFF 并启用 innodb_stats_on_metadata=ON

摘要

本人项目使用场景：预统计某表总大小+表今日新增大小，其中今日新增每10s刷新一次，想要的效果是表不断怼数据情况下，今日新增展示功能能动态滚动，及做到近实时查询当下表大小。
本文MYSQL版本5.7.x，本文提供两种解决方案查询某张表大小，并对每种方案使用限制条件及场景进行解析说明
如果MYSQL版本为8.x版本，则查看解决方案（本人没验证过，不清楚是否可行，需自己实践验证）：https://blog.csdn.net/londa/article/details/90480266

（推荐）第一种方案：查询information_schema.TABLES的字段DATA_LENGTH

information_schema为系统表，其中TABLES表作为视图可以查看库表字段等等信息，但是它是只读的，不能进行更新、删除和插入等操作，也不能加触发器，因为它们实际只是一个视图，不是基本表，没有关联的文件。
具体案例可百度或者查看举例地址：https://www.cnblogs.com/Knight7971/p/9963299.html

使用场景

查询表大小对实时性要求不高情况下可使用，因为DATA_LENGTH字段不都是实时触发更新，表的引擎不同更新状况不同，表引擎为MYISAM会动态实时更新，表引擎为INNODB则定期更新，据说更新条件为达到表大小10%插入量才会触发更新DATA_LENGTH字段。
具体可查看官网：https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.7/en/information-schema-tables-table.html

优点：网上大多案例都是查询系统表的字段DATA_LENGTH去计算大小

缺点：DATA_LENGTH不都是实时更新的，由表引擎决定，同时DATA_LENGTH是压缩后的字节大小（及大小经过压缩后计算得到DATA_LENGTH）

第二种方案:查询information_schema.INNODB_SYS_TABLESPACES的字段FILE_SIZE

本方案参考文档：https://zhuanlan.zhihu.com/p/147269069

使用场景

网上说该方案是针对表引擎为INNODB情况下实时更新并获取表大小，该方案弥补了第一种方案表大小无法做到实时更新的场景，但是FILE_SIZE字段记录的是未经压缩的表大小，上面的DATA_LENGTH是经过压缩后的表大小，这样存在的问题就是，当表数据比较大的时候两者值会有很大的大小差距，所以要考虑清楚用哪个。
具体可查看官网：https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.7/en/information-schema-innodb-sys-tablespaces-table.html

优点：针对表引擎为INNODB情况下能够实时更新并获取表大小

缺点：FILE_SIZE字段值大小为未经过压缩的表大小

（不推荐）第三种方案：禁用 innodb_stats_persistent=OFF 并启用 innodb_stats_on_metadata=ON

该方案参考文档：https://blog.csdn.net/csd753111111/article/details/100428647

网上方案网上说：

mysql命令为：show global variables like '%stats%';
结果为：

要修改某个属性可使用命令：set global innodb_stats_on_metadata=off;
说明：这种方案弊端较大，使用需谨慎。