mysql查询数据表中记录行数及每行空间占用情况

saintyyu

发布于 2021-11-22 09:43:50

2.3K0

发布于 2021-11-22 09:43:50

文章被收录于专栏：IT专栏

use information_schema;
select TABLE_ROWS, concat(round(sum(DATA_LENGTH/1024), 2),'KB') as totalSize, concat(round(sum(DATA_LENGTH/1024), 2),'KB')/TABLE_ROWS as recordSize from TABLES where table_schema='databaseName' and table_name='tableName';

说明：

MySQL的 information_schema 数据库，保存着数据库的容量和使用信息。可查询数据库中每个表占用的空间、表记录的行数。

Tables表的结构：

CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(20) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

主要字段说明：

TABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLE_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小

计算出数据表中每行记录占用空间的目的是什么呢？目的是知道该表在保证查询性能的前提下，单表能存储的行记录的上限。参考博客3中提到一个假设：假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右。在这个假设下得出结论为单表B+树索引层级为3层时，能存储的行记录上限为21,902,400。

由此可知，一张表在保证查询性能的前提下，能存储的记录行数与每行记录的大小有关。本人通过对互联网业务数据进行统计发现，单行记录数据的大小超过1k的并不太多，单行记录的平均大小不到0.5k（0.5k左右），也就是说，能存储的行记录上限在四千万～五千万。

参考博客：

1.https://www.jianshu.com/p/8f086c98d591 mysql查询库大小，表行数，索引大小

2.https://www.cnblogs.com/mr-wuxiansheng/p/7520628.html msyql round函数

3.https://blog.csdn.net/Saintyyu/article/details/100114372 为什么MySQL的索引要使用B+树，而不是其它树？比如B树？

4.https://www.cnblogs.com/guohu/p/10984278.html MySQL查询数据表的auto_increment（自增id）

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2019/09/25 ，如有侵权请联系 cloudcommunity@tencent.com 删除

存储