为什么MySQL数据库中没有空闲空间?

时间：2022-12-06 19:14:09

Recently, I checked the SQL databases I'm using in phpMyAdmin (with WAMP), and they all report no free space. This was not true before, and I'm not sure what caused it to change. The lack of free space is making calls to the databases very, very slow. I've reinstalled WAMP, created a new user for phpMyAdmin, and reimported the databases into the new user, but to no avail.

最近，我检查了在phpMyAdmin(带有WAMP)中使用的SQL数据库，它们都表示没有空闲空间。以前不是这样的，我也不确定是什么导致了它的改变。由于缺乏*空间，对数据库的调用非常、非常缓慢。我重新安装了WAMP，为phpMyAdmin创建了一个新用户，并将数据库重新导入到新用户中，但没有效果。

Here is the query I used to check for space and the result:

下面是我用来检查空格和结果的查询:

SELECT table_schema "beek", SUM( data_length + index_length ) /1024 / 1024 "Data Base Size in MB", SUM( data_free ) /1024 /1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema
LIMIT 0 , 30

beek
Data Base Size in MB
Free Space in MB

-------------------------------------------
beek

Data Base Size in MB 4.11813927

Free Space in MB 0.00000000

-----------------------------------------
information_schema

Data Base Size in MB 0.00976563

Free Space in MB 0.00000000

---------------------------------------
mysql

Data Base Size in MB 0.76406479

Free Space in MB 0.00000000

-------------------------------------
performance_schema

Data Base Size in MB 0.00000000

Free Space in MB 0.00000000

--------------------------------------------

Does anyone know why all of the extra space in these databases has disappeared?

有人知道为什么这些数据库中的所有额外空间都消失了吗?

1 个解决方案

#1

3  

"Free space" for InnoDB tables is reported as the size of extents that have no data.

InnoDB表的“空闲空间”被报告为没有数据的区段的大小。

When a tablespace file grows physically, it grows in a big chunks of pages, called extents. Then you gradually INSERT data to fill the extent.

当表空间文件在物理上增长时，它会在称为区段的大量页面中增长。然后逐渐插入数据以填充范围。

You could have previously queries the information_schema when you had just allocated new extents, but before you inserted data. Then as you insert data, it uses some space in the extent and it's no longer unoccupied (even if it's only partially filled).

您可以在刚刚分配了新区段时(但在插入数据之前)查询information_schema。然后，当您插入数据时，它在区段中使用了一些空间，并且不再是空闲的(即使只是部分填充)。

Extents in the middle of the tablespace file can also be emptied when you do a lot of DELETEs. But then that free space is re-used by later INSERTs.

当您执行大量删除操作时，表空间文件中间的区段也可以被清空。但是之后的插入会重复使用这个*空间。

Either way, having zero unoccupied extents is normal, and it's not a problem. If the file needs to grow physically, InnoDB can do that.

无论哪种方式，拥有零空闲区段都是正常的，这不是问题。如果文件需要在物理上增长，InnoDB可以这样做。

For more details, see:

更多细节,请参阅:

#1

3  

"Free space" for InnoDB tables is reported as the size of extents that have no data.

InnoDB表的“空闲空间”被报告为没有数据的区段的大小。

When a tablespace file grows physically, it grows in a big chunks of pages, called extents. Then you gradually INSERT data to fill the extent.

当表空间文件在物理上增长时，它会在称为区段的大量页面中增长。然后逐渐插入数据以填充范围。

You could have previously queries the information_schema when you had just allocated new extents, but before you inserted data. Then as you insert data, it uses some space in the extent and it's no longer unoccupied (even if it's only partially filled).

您可以在刚刚分配了新区段时(但在插入数据之前)查询information_schema。然后，当您插入数据时，它在区段中使用了一些空间，并且不再是空闲的(即使只是部分填充)。

Extents in the middle of the tablespace file can also be emptied when you do a lot of DELETEs. But then that free space is re-used by later INSERTs.

当您执行大量删除操作时，表空间文件中间的区段也可以被清空。但是之后的插入会重复使用这个*空间。

Either way, having zero unoccupied extents is normal, and it's not a problem. If the file needs to grow physically, InnoDB can do that.

无论哪种方式，拥有零空闲区段都是正常的，这不是问题。如果文件需要在物理上增长，InnoDB可以这样做。

For more details, see:

更多细节,请参阅:

标签：

相关文章