在 MySQL 中，“开销”是什么意思，它有什么不好，以及如何修复它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/565997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In MySQL what does "Overhead" mean, what is bad about it, and how to fix it?
提问by johnnietheblack
simple question, but its been nagging me for a while now....
一个简单的问题，但它已经唠叨了我一段时间了......
what is "overhead" in MySQL, and should i be worried?
MySQL 中的“开销”是什么，我应该担心吗？
does clicking "optimize table" fix it for real?
单击“优化表”是否真的修复了它？
采纳答案by Jasper
It appears that the overhead is temporary diskspace that the database used to run some of the queries, so you should only worry if this gets really high.
看起来开销是数据库用来运行某些查询的临时磁盘空间，所以你应该只担心它是否真的很高。
You can compare 'Optimizing the table' with the defragmenting of your hard drive.
您可以将“优化表”与对硬盘进行碎片整理进行比较。
I quote:
我引用：
Every database will, over time, require some form of maintenance to keep it at an optimalperformance level. Purging deleted rows, resequencing, compressing, managing index paths, defragmenting, etc. is what is known as OPTIMIZATION in mysql and other terms in other databases. For example, IBM DB2/400 calls it REORGANIZE PHYSICAL FILE MEMBER.
It's kind of like changing the oil in your car or getting a tune-up. You may think you really don't have to, but by doing so your car runs much better, you get better gas mileage, etc. A car that gets lots of mileage requires tune-ups more often. A database that gets heavy use requires the same. If you are doing a lot of UPDATE and/or DELETE operations, and especially if your tables have variable length columns (VARCHAR, TEXT, etc), you need to keep 'er tuned up.
随着时间的推移，每个数据库都需要某种形式的维护以使其保持最佳性能水平。清除已删除的行、重新排序、压缩、管理索引路径、碎片整理等在 mysql 中称为优化，在其他数据库中称为其他术语。例如，IBM DB2/400 称其为 REORGANIZE PHYSICAL FILE MEMBER。
这有点像更换汽车中的机油或进行调整。你可能认为你真的没有必要，但通过这样做，你的汽车运行得更好，你会获得更好的油耗，等等。获得大量里程的汽车需要更频繁地调整。一个被大量使用的数据库也需要同样的东西。如果您正在执行大量 UPDATE 和/或 DELETE 操作，尤其是当您的表具有可变长度列（VARCHAR、TEXT 等）时，您需要不断调整。
回答by Quassnoi
If you are talking about the thing that phpMyAdmincalls overhead, then it's actual size of a table datafile relative to the ideal size of the same datafile (as if when just restored from backup).
如果您在谈论phpMyAdmin调用的东西overhead，那么它是表数据文件的实际大小相对于同一数据文件的理想大小（就像刚从备份中恢复时一样）。
For performance reasons, MySQLdoes not compact the datafiles after it deletes or updates rows.
出于性能原因，MySQL在删除或更新行后不压缩数据文件。
This overheadis bad for table scan, i. e. when your query needs to run over all table values, it will need to look at more empty space.
这overhead对表扫描不利，即当您的查询需要遍历所有表值时，它将需要查看更多空白空间。
You may get rid of the overheadby running OPTIMIZE TABLEthat will compact your table and indexes.
您可以overhead通过运行OPTIMIZE TABLE将压缩您的表和索引来摆脱。
回答by Shankar Prakash G
Overhead is Data_freeof a table, that is The number of allocated but unused bytes.We can find it by SQL command SHOW TABLE STATUS. It is the free space in allocated size for your table.
开销是表的Data_free，即已分配但未使用的字节数。我们可以通过 SQL 命令SHOW TABLE STATUS找到它。它是为您的表分配大小的可用空间。
回答by Thomas Lindegaard Jensen
Optimize table can be very problematic. For example if the table is used heavily on a site.
优化表可能很成问题。例如，如果该表在站点上大量使用。
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html
After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.<
在删除 MyISAM 或 ARCHIVE 表的大部分内容后，或对具有可变长度行（具有 VARCHAR、VARBINARY、BLOB 或 TEXT 列的表）的 MyISAM 或 ARCHIVE 表进行许多更改之后。删除的行保存在链表中，后续的 INSERT 操作会重用旧的行位置。<
I believe I've confirmed this behaviour. And it would certainly be very useful indeed.
我相信我已经确认了这种行为。它肯定会非常有用。
