技术分享|在磁盘上查找MySQL表的大小

由于复杂的原因，在MySQL中查询磁盘上表的实际大小并非易事。
MySQL支持多种存储引擎，它们可能以不同的方式存储数据。
例如，InnoDB存储引擎在MySQL5.7中提供了三种“基本”格式，包括row_formats和两种可压缩类型。
这使得问题变得更加复杂。
在MySQL5.7中，INFORMATION_SCHEMA.TABLES中的数据长度（data_length）和索引长度（index_length）用于估计表大小，但这可能不准确。
MySQL并不实时维护这个值，而是定期刷新它。
该图表显示数据长度和索引长度更新不一致，并且在服务器启动时刷新统计信息可能会对查询计划产生不稳定的影响。
为了获得更准确的实时信息，可以通过INNODB_SYS_TABLESPACES表查看表空间信息。
该时间表实时更新，无需特殊配置。
与INFORMATION_SCHEMA.TABLES中的数据不同，INNODB_SYS_TABLESPACES表还处理InnoDB的页面压缩功能，正确显示文件大小和分配大小之间的差异。
使用不同类型的InnoDB压缩会影响INFORMATION_SCHEMA中显示的信息。
对于传统的InnoDB压缩（InnoDB表压缩），data_length和index_length表示压缩数据的大小。
当使用MySQL5.7中新的InnoDB压缩（InnoDB页压缩）时，显示的值对应于文件大小，而不是分配大小。
总之，查询MySQL中某个表占用的磁盘空间并不是一件容易的事。
为了获得准确的实时信息，应查看INNODB_SYS_TABLESPACES表的实际文件大小。
了解不同的InnoDB压缩方法如何影响结果也很重要。

查看数据库中有哪些表空间

1、系统表空间在MySQL数据目录中有一个名为ibdata1的文件，该文件可以存储一张或多张表。
92327512M-rw-r-----1mysqlmysql12MMarch1810:42ibdata1该文件是MySQL系统表空间文件。
默认为1，只需在配置文件my.cnf中定义即可。
innodb_data_file_path=ibdata1:200M;ibdata2:200M:autoextend:max:800M系统表空间不仅可以是文件系统组成的文件，还可以是非文件系统组成的磁盘块的定义，比如裸设备，也很简单innodb_data_file_path=/dev/nvme0n1p1:3Gnewraw;/dev/nvme0n1p2:2Gnewraw系统表空间的内容是什么？具体内容包括：doublewriterbuffer、changebuffer、数据字典（MySQL8.0之前）、表数据和表索引。
那么为什么目前主流版本的MySQL不默认有系统表空间呢？当我们审视原因时，系统表空间存在三大缺点：原因一：磁盘空间无法自动收缩，造成空间的巨大浪费。
即使它包含的所有表都被删除，该空间也不会自动释放。
2、简单表空间简单表空间与系统表空间不同每个表空间与表一一对应，每个表都有自己的表空间。
具体来说，它在磁盘上显示为后缀为.ibd的文件。
例如表t1，对应的表空间文件为t1.ibd91710796K-rw-r-----1mysqlmysql96KMarch1816:13如何在特定表上使用t1.ibd单表空间？有两种方法：方法一：在配置文件中启用。
在配置文件中启用单表空间，并设置参数innodb_filer_per_table，使得当前数据库下的所有表默认启用单表空间。
innodb_file_per_table=1另外，还可以在建表时直接指定单个表空间mysql>createtablet1(idint,r1char(36))tablespaceinnodb_file_per_table;QueryOK,0rowsaffected(0.04sec.)另外解决了系统表空间的几个缺点前面提到过，单个表空间还具有其他优点：1.truncate操作比处理任何其他表空间要快根据使用场景指定在不同的磁盘目录中，例如日志表放在较慢的磁盘上，需要频繁随机读取的表放在SSD上等。
mysql>createtableytt_dedicated(idint)datadirectory='/var/lib/mysql-files';QueryOK,0rowsaffected(0.04sec)3.可以使用optimizetable来收缩或重建经常增删改查的表。
大致流程如下：创建与原表相同的表结构和数据文件，将真实数据复制到临时文件中，删除原表定义和数据文件，最后将临时文件的名称改为相同的和原来的表一样。
3、通用表空间通用表空间最早出现在MySQLCluster中，也就是NDB引擎。
从MySQL5.7开始引入了InnoDB引擎。
通用表空间，例如系统表空间，也是共享表空间。
每个表空间可以包含一个或多个表，这意味着通用表空间与表之间存在一对多的关系。