前言
在实际应用中，为了更好的对数据库的存储空间大小的变化，我们需要掌握如何统计出当前数据库的相关信息。
在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条件值需要改为想要查询的数据库名称。
评论区