mysql数据库存储空间与数据大小不一致

阿里云RDS服务器报硬盘磁盘空间不足（100G的磁盘空间），登录后台查看，使用了130G，使用 SELECT file_name, concat(TOTAL_EXTENTS,'M') as 'FIle_size' FROM INFORMATION_SCHEMA.FILES order by TOTAL_EXTENTS DESC 查看，只使用了60G左右。那么还有70G是怎么用了呢？

查看问题：

查询 information_schema.innodb_trx ，看是哪些语句导致的。

原因：

ibdata文件很大，MySQL实例可能会 由于长时间不结束的查询导致 ibdata1 文件过大且无法收缩，导致实例空间满 ，为避免数据丢失，RDS会对实例进行自动锁定，磁盘锁定之后，将无法进行写入 *** 作

解决方案：

重启实例即可

以MySQL 8.0 来说，通过查看 8.0 的官方文档得知，8.0 的临时表空间分为会话临时表空间和全局临时表空间，会话临时表空间存储用户创建的临时表和当 InnoDB 配置为磁盘内部临时表的存储引擎时由优化器创建的内部临时表，当会话断开连接时，其临时表空间将被截断并释放回池中；也就是说，在 8.0 中有一个专门的会话临时表空间，当会话被杀掉后，可以回收磁盘空间；而原来的 ibtmp1 是现在的全局临时表空间，存放的是对用户创建的临时表进行更改的回滚段，在 5.7 中 ibtmp1 存放的是用户创建的临时表和磁盘内部临时表；

也就是在 8.0 和 5.7 中 ibtmp1 的用途发生了变化，5.7 版本临时表的数据存放在 ibtmp1 中，在 8.0 版本中临时表的数据存放在会话临时表空间，如果临时表发生更改，更改的 undo 数据存放在 ibtmp1 中；

实验验证：将之前的查询结果保存成临时表，对应会话是 45 号，通过查看对应字典表，可知 45 号会话使用了 temp_8.ibt 这个表空间，通过把查询保存成临时表，可以用到会话临时表空间，如下图：

下一步杀掉 45 号会话，发现 temp_8.ibt 空间释放了，变为了初始大小，状态为非活动的，证明在 mysql8.0 中可以通过杀掉会话来释放临时表空间。

总结：在 mysql5.7 时，杀掉会话，临时表会释放，但是仅仅是在 ibtmp 文件里标记一下，空间是不会释放回 *** 作系统的。如果要释放空间，需要重启数据库；在 mysql8.0 中可以通过杀掉会话来释放临时表空间。

1. 查看数据库的索引空间大小

在MySQL Workbench中运行以下SQL语句：

— 以GB为单位

SELECT

CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 6), ‘ GB’) AS ‘Total Index Size’

FROM

information_schema.TABLES

WHERE

table_schema LIKE ‘database’

— 以MB为单位

SELECT

CONCAT(ROUND(SUM(index_length)/(1024*1024), 6), ‘ MB’) AS ‘Total Index Size’

FROM

information_schema.TABLES

WHERE

table_schema LIKE ‘database’

其中，database是待查看数据库的名称，例如：lsqdb%。运行结果如下图所示：

2. 查看数据库的数据空间大小

在MySQL Workbench中运行以下SQL语句：

— 以GB为单位

SELECT

CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 6), ‘ GB’) AS ‘Total Data Size’

FROM

information_schema.TABLES

WHERE

table_schema LIKE ‘database’

— 以MB为单位

SELECT

CONCAT(ROUND(SUM(data_length)/(1024*1024), 6), ‘ MB’) AS ‘Total Data Size’

FROM

information_schema.TABLES

WHERE

table_schema LIKE ‘database’

其中，database是待查看数据库的名称，例如：lsqdb%。运行结果如下图所示：

3. 查看数据库中所有表的信息

在MySQL Workbench中运行以下SQL语句，查看数据库中所有表的表名、表行数、数据空间大小、索引空间大小和总大小：

SELECT

CONCAT(table_schema,’.’,table_name) AS ‘Table Name’,

table_rows AS ‘Number of Rows’,

CONCAT(ROUND(data_length/(1024*1024),6),’ MB’) AS ‘Data Size’,

CONCAT(ROUND(index_length/(1024*1024),6),’ MB’) AS ‘Index Size’,

CONCAT(ROUND((data_length+index_length)/(1024*1024),6),’ MB’) AS’Total Size’

FROM

information_schema.TABLES

WHERE

table_schema LIKE ‘database’

其中，database是待查看数据库的名称，例如：lsqdb%。

---