侧边栏壁纸
博主头像
叩钉壹刻博主等级

7分技术,3分管理,2分运气

  • 累计撰写 28 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

MySQL数据库数据统计

鹿心肺语
2024-02-24 / 0 评论 / 0 点赞 / 51 阅读 / 5421 字

前言

在实际应用中,为了更好的对数据库的存储空间大小的变化,我们需要掌握如何统计出当前数据库的相关信息。

在MySQL的information_schema.tables表中,记录了关于数据库表在数据库中的信息。我们首先了解tables表的表结构:

表字段字段含义
TABLE_CATALOG表所属目录的名称。该值始终为 def。
TABLE_SCHEMA表所属模式(数据库)的名称。
TABLE_NAME表的名称。
TABLE_TYPE表 BASE TABLE、视图 VIEW 或 INFORMATION_SCHEMA 表 SYSTEM VIEW。
TABLES 表不列出临时表。
ENGINE表的存储引擎。对于分区表,ENGINE 显示所有分区使用的存储引擎名称。
VERSION此列未使用。在 MySQL 8.0 中删除 .frm 文件后,此列现在报告的硬编码值为 10,这是 MySQL 5.7 中最后使用的 .frm 文件版本。
ROW_FORMAT行存储格式(固定、动态、压缩、冗余、紧凑)。对于 MyISAM 表,动态格式与 myisamchk -dvv 报告的打包格式相对应。
TABLE_ROWS行数。某些存储引擎(如 MyISAM)会存储精确的计数。对于 InnoDB 等其他存储引擎,该值只是一个近似值,可能与实际值相差 40% 到 50%。在这种情况下,请使用 SELECT COUNT(*) 获取准确的计数。
对于 INFORMATION_SCHEMA 表,TABLE_ROWS 为空。
对于 InnoDB 表,行数只是用于 SQL 优化的粗略估计值。(如果 InnoDB 表是分区表,情况也是如此)。
AVG_ROW_LENGTH平均每行长度。
DATA_LENGTH对于 MyISAM,DATA_LENGTH 是数据文件的长度,单位为字节。
对于 InnoDB,DATA_LENGTH 是为聚簇索引分配的大致空间大小,单位为字节。具体来说,它是以页面为单位的聚类索引大小乘以 InnoDB 页面大小。
INDEX_LENGTH对于 MyISAM,INDEX_LENGTH 是索引文件的长度,单位为字节。
对于 InnoDB,INDEX_LENGTH 是为非聚类索引分配的大致空间大小,单位为字节。具体来说,它是以页面为单位的非聚类索引大小之和乘以 InnoDB 页面大小。
DATA_FREE已分配但未使用的字节数。
AUTO_INCREMENT下一个 AUTO_INCREMENT 值。
CREATE_TIME创建表的时间。
UPDATE_TIME表最后一次更新的时间。对于某些存储引擎,该值为空。
CHECK_TIME最后一次检查表的时间。并非所有存储引擎都会更新这个时间,在这种情况下,该值始终为空。
对于 InnoDB 分区表,CHECK_TIME 始终为空。
TABLE_COLLATION表格默认校对方式。输出结果不会明确列出表格默认字符集,但校对名称以字符集名称开头。
CHECKSUM实时校验和值,如果有的话。
CREATE_OPTIONS与 CREATE TABLE 一起使用的额外选项。
对于分区表,CREATE_OPTIONS 显示 partitioned。
TABLE_COMMENT创建表格时使用的注释,或者 MySQL 无法访问表格信息的原因。

对于该表的补充说明:

  • 对于 NDB 表,该语句的输出会显示 AVG_ROW_LENGTH 和 DATA_LENGTH 列的适当值,但不考虑 BLOB 列。
  • 对于 NDB 表,DATA_LENGTH 仅包括存储在主内存中的数据;MAX_DATA_LENGTH 和 DATA_FREE 列适用于磁盘数据。
  • 对于 NDB 群集磁盘数据表,MAX_DATA_LENGTH 显示为磁盘数据表或片段的磁盘部分分配的空间。(内存数据资源使用情况由 DATA_LENGTH 列报告)。
  • 对于 MEMORY 表,DATA_LENGTH、MAX_DATA_LENGTH 和 INDEX_LENGTH 值近似于实际分配的内存量。分配算法会保留大量内存,以减少分配操作的次数。
  • 对于视图,除了 TABLE_NAME 表示视图名称、CREATE_TIME 表示创建时间、TABLE_COMMENT 表示 VIEW 外,大多数 TABLES 列都是 0 或 NULL。

根据此表的信息,我们下面将开始进行数据库的相关统计。

1. 统计数据库总容量大小

需要用到的表字段有:DATA_LENGTH 和 INDEX_LENGTH。

SELECT
	SUM(data_length + index_length) / 1024 / 1024 AS `总大小(MB)`
FROM
	information_schema.TABLES
WHERE
	table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

注意:SQL语句中的where条件过滤了MySQL的系统表。

2. 统计每个数据库的总容量大小

需要用到的表字段有:TABLE_SCHEMA、DATA_LENGTH 和 INDEX_LENGTH。

SELECT
	table_schema AS '数据库',
	SUM(data_length + index_length) / 1024 / 1024 AS '总大小(MB)'
FROM
	information_schema.TABLES
WHERE
	table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY
	table_schema
ORDER BY
	SUM(data_length + index_length) DESC;

注意:SQL语句中的where条件过滤了MySQL的系统表。

3. 统计每个数据库的信息

需要用到的字段有:TABLE_SCHEMA、TABLE_ROWS、DATA_LENGTH 和 INDEX_LENGTH。

SELECT
	table_schema as '数据库',
	SUM(table_rows) as '记录数',
	SUM(TRUNCATE(data_length / 1024 / 1024, 2)) as '数据容量(MB)',
	SUM(TRUNCATE(index_length / 1024 / 1024, 2)) as '索引容量(MB)'
FROM
	information_schema.tables
WHERE
	table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY
	table_schema
ORDER BY
	SUM(data_length) desc,
	SUM(index_length) desc;

注意:SQL语句中的where条件过滤了MySQL的系统表。

4. 统计某个数据库下所有表的信息

需要用到的字段有:TABLE_SCHEMA、TABLE_NAME、TABLE_ROWS、DATA_LENGTH 和 INDEX_LENGTH。

SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)',
	TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)',
	TRUNCATE((data_length + index_length)/1024/1024, 2) AS '总容量(MB)'
FROM
	information_schema.TABLES
WHERE
	table_schema = 'mydatabase'
ORDER BY
	(data_length + index_length) DESC;

注意:SQL语句中的where条件值需要改为想要查询的数据库名称。

0

评论区