MySQL 中的默认排序顺序(ALTER TABLE ... ORDER BY ...;)

2021-11-27 00:00:00 sorting mysql alter-table

假设 MySQL 表 (ISAM) 的默认排序通过执行:

Assume that the default ordering of a MySQL-table (ISAM) is changed by executing:

ALTER TABLE tablename ORDER BY columnname ASC;

从现在开始，假设我的查询中没有指定ORDER BY"(即SELECT * FROM tablename哪里......限制 10 个；")?

From now on, am I guaranteed to 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;")?

是否有我应该注意的极端情况?

Are there any corner-cases that I should be aware of?

更新 #1: 非常感谢 Quassnoi，他正确指出 INSERT 和 DELETE 弄乱了顺序.这导致我提出以下额外问题:

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:

更新呢?假设没有对表进行 INSERT 或 DELETE，只有更新 - 排序顺序是否完整?
假设进行了 INSERT 和 DELETE - 我如何再次重建"排序，比如每天一次(在这种特定情况下，表只每天更改一次，因此在更改完成后每天重建它应该仍然可以！).REPAIR TABLE 是否修复它，或者必须添加 do ALTER TABLE ... ORDER BY again?

推荐答案

来自文档:

注意插入和删除后表格不会保持这个顺序

Note that the table does not remain in this order after inserts and deletes

实际上，如果您向该表发出 SELECT ... ORDER BY，ALTER TABLE 选项不会让您免于 filesort>，而是让 filesort 更快.

Actually, if you issue SELECT ... ORDER BY to this table, the option to ALTER TABLE won't spare you of filesort, but instead make filesort much faster.

对一个已经排序的集合进行排序相当于浏览这个集合以确保一切正常.

Sorting an already ordered set is equivalent to browsing this set to ensure everything is OK.

更新呢?假设没有对表进行 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?

如果您的表不包含任何动态字段(例如 VARCHAR 或 'BLOB')，那么很可能 MyISAM 不会移动它更新时.

If your table does not contain any dynamic fields (like VARCHAR or 'BLOB'), then most probably MyISAM will not move it when updating.

不过，如果我正在建造一座核电站或获得报酬的东西，我不会依赖这种行为.

假设进行了 INSERT 和 DELETE - 我如何再次重建"排序，比如说每天一次(在这种特定情况下，表只每天更改一次，因此在更改完成后每天重建它应该仍然可以！).REPAIR TABLE 是否修复它，或者必须添加 do ALTER TABLE ... ORDER BY again?

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?

您需要执行ALTER TABLE ... ORDER BY.

REPAIR 只是修复损坏表的物理结构.

REPAIR just fixes the physical structure of a corrupted table.

相关文章