MySQL 中的默认排序顺序（ALTER TABLE ... ORDER BY ...;）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/725717/
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
Default sort-ordering in MySQL (ALTER TABLE ... ORDER BY ...;)
提问by knorv
Assume that the default ordering of a MySQL-table (ISAM) is changed by executing:
假设通过执行以下命令更改 MySQL 表 (ISAM) 的默认顺序：
ALTER TABLE tablename ORDER BY columnname ASC;
From now on, am I guaranteedto obtain records retrieved from the table in the order of "columnname ASC" assuming no "ORDER BY" is specified in my queries (i.e. "SELECT * FROM tablename WHERE ... LIMIT 10;")?
从现在开始，假设我的查询中没有指定“ORDER BY”（即“SELECT * FROM tablename WHERE ... LIMIT 10;”），我是否可以保证按照“columnname ASC”的顺序从表中检索记录？
Are there any corner-cases that I should be aware of?
有没有我应该注意的极端情况？
Update #1:Thanks a lot to Quassnoi who correctly pointed out that INSERTs and DELETEs messes up the ordering. This leads me to the following to extra questions:
更新 #1：非常感谢 Quassnoi，他正确指出 INSERT 和 DELETE 弄乱了顺序。这导致我提出以下额外问题：
- What about UPDATEs? Assume that no INSERTs or DELETEs are made to the table, but only updates - will the sort order be intact?
- Assume that INSERTs and DELETEs are made - how do I "rebuild" the sorting again, say once a day (in this specific case the table only changes daily, so rebuilding it daily after the changes are done should still be OK!). Does REPAIR TABLE fix it, or must add do ALTER TABLE ... ORDER BY again?
- 更新呢？假设没有对表进行 INSERT 或 DELETE 操作，只有更新 - 排序顺序是否完整？
- 假设进行了 INSERT 和 DELETE - 我如何再次“重建”排序，比如说每天一次（在这种特定情况下，表只每天更改一次，因此在更改完成后每天重建它应该仍然可以！）。REPAIR TABLE 是否修复它，或者必须添加 do ALTER TABLE ... ORDER BY ？
回答by Quassnoi
From documentation:
从文档：
Note that the table does not remain in this order after inserts and deletes
注意插入和删除后表不会保持这个顺序
Actually, if you issue SELECT ... ORDER BYto this table, the option to ALTER TABLEwon't spare you of filesort, but instead make filesortmuch faster.
实际上，如果您SELECT ... ORDER BY向此表发出，则选项ALTER TABLE不会让您免于filesort，而是使filesort速度更快。
Sorting an already ordered set is equivalent to browsing this set to ensure everything is OK.
对一个已经排序的集合进行排序相当于浏览这个集合以确保一切正常。
What about UPDATEs? Assume that no INSERTs or DELETEs are made to the table, but only updates - will the sort order be intact?
更新呢？假设没有对表进行 INSERT 或 DELETE 操作，只有更新 - 排序顺序是否完整？
If your table does not contain any dynamic fields (like VARCHARor 'BLOB'), then most probablyMyISAMwill not move it when updating.
如果您的表不包含任何动态字段（如VARCHAR或“BLOB”），那么更新时很可能MyISAM不会移动它。
I would not rely on this behavior, though, if I were building a nuclear power plant or something I get paid for.
但是，如果我正在建造核电站或我获得报酬的东西，我不会依赖这种行为。
Assume that INSERTs and DELETEs are made - how do I "rebuild" the sorting again, say once a day (in this specific case the table only changes daily, so rebuilding it daily after the changes are done should still be OK!). Does REPAIR TABLE fix it, or must add do ALTER TABLE ... ORDER BY again?
假设进行了 INSERT 和 DELETE - 我如何再次“重建”排序，比如说每天一次（在这种特定情况下，表只每天更改一次，因此在更改完成后每天重建它应该仍然可以！）。REPAIR TABLE 是否修复它，或者必须添加 do ALTER TABLE ... ORDER BY ？
You'll need to do ALTER TABLE ... ORDER BY.
你需要做ALTER TABLE ... ORDER BY。
REPAIRjust fixes the physical structure of a corrupted table.
REPAIR只是修复损坏表的物理结构。
回答by carlo
Physically ordering a column can save loads of IO. It's perfectly legitimate method used in advanced systems to speed query time. You just need to reorg the data every now and then so its stays clustered. Just because some folks haven't heard of it doesn't mean it doesn't exist. - 25 year advanced DB design veteran.
对列进行物理排序可以节省 IO 负载。这是在高级系统中用于加快查询时间的完全合法的方法。您只需要时不时地重新组织数据，使其保持集群状态。仅仅因为有些人没有听说过它并不意味着它不存在。- 25 年高级 DB 设计资深人士。
