关于information_schema.tables查询业务SCHEMA中表数量和表容量不准确问题

前言

那天,在工作中,想在mysql的业务schema中找一张数据量最大的表,图方便,就想通过information_schema.tables中的table_schema去过滤业务schema,查询table_rows最大值的表,但是发现数据量明显不对,怎么数量都是0,多少有点疑惑,所以习惯性的查阅了mysql官方关information_schema.tables的说明,这篇文章记录一下,免得下次还犯这种糊涂事,确实不应该;

以下内容均为工作之外,不涉及工作内容

查询information_schema.tables

use information_schema;

SELECT
	table_name,
	table_rows
FROM
	TABLES
WHERE
	table_schema IN (
		'业务schema'...)
		ORDER BY
			table_rows DESC;

在这里插入图片描述
而实际上:
在这里插入图片描述

关于information_schema.tables

在information_schema数据库中的表都只是只读的,不能进行更新、删除和插入操作,也不能加触发器,因为它们实际只是一个视图,不是基本表,没有关联的文件。
在information_schema数据库中有一张表tables,这张表提供了在数据库表的元信息记录。

  • table_schema: 记录中表所在的数据库名 table_name:记录中的表名;

  • engine:记录中表所使用的存储引擎 table_rows:记录中关于表的粗略行估计;

  • data_length:记录中表的大小(单位字节) index_length:记录中表的索引大小;

查阅mysql官网The INFORMATION_SCHEMA TABLES Table

在这里插入图片描述

TABLE_ROWS

The number of rows. Some storage engines, such as MyISAM, store the
exact count. For other storage engines, such as InnoDB, this value is
an approximation, and may vary from the actual value by as much as 40%
to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate
count.

TABLE_ROWS is NULL for INFORMATION_SCHEMA tables.

For InnoDB tables, the row count is only a rough estimate used in SQL
optimization. (This is also true if the InnoDB table is partitioned.)

英语翻译大概就是这个意思:

行数。一些存储引擎,如MyISAM,存储确切的计数。对于其他存储引擎,如InnoDB,此值是近似值,可能与实际值相差40%至50%。在这种情况下,请使用SELECT
COUNT(*)来获得准确的计数。

对于INFORMATION_SCHEMA表,TABLE_ROWS为NULL。
对于InnoDB表,行数只是SQL优化中使用的粗略估计。(如果InnoDB表是分区的,也是如此。)

那么通过information_schema.tables查询表容量会如何了?

SELECT
	table_name AS TABLE_NAME,
	table_rows AS ROW_NUM,
	concat(
		round(DATA_LENGTH / 1024 / 1024, 2),
		'MB'
	) AS TABLE_DATA_SIZE,
	concat(
		round(INDEX_LENGTH / 1024 / 1024, 2),
		'MB'
	) AS TABLE_INDEX_SIZE,
	concat(
		round(
			(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
			2
		),
		'MB'
	) AS TABLE_TOTAL_SIZE
FROM
	information_schema. TABLES
WHERE
	table_schema = '业务schema'
ORDER BY
	(DATA_LENGTH + INDEX_LENGTH) DESC;

与实际值相差甚多;

为什么会如此了?

原因

默认情况下 mysql 对表进行增删操作时,是不会自动更新 information_schema 库中 tables 表的table_rows 字段的,所以业务schema中这些表从新建到现在没有自动更新,当然数量是0咯!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

偷偷学习被我发现

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值