主页 / dba / 问题 / 13940
Accepted
Sebastian
Asked: 2012-02-27 06:10:17 +0800 CST

MySQL：ORDER BY 没用？

  • 772
CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;

临时表是在脚本的开头创建的，它有一个“id”auto_increment 列（和其他列）。脚本的第 1 部分使用各种 SELECT 填充表。第 2 部分应该以 1000 块为单位处理所有选定的行，但永远不会再次写入该表。

我假设这两个命令是相同的

SELECT FROM tt ORDER BY id LIMIT ?,1000;
SELECT FROM tt LIMIT ?,1000;

但这是真的吗？mySQL 是否有可能在多个 SELECT 上以不同的顺序检索行，即使表没有写在两者之间？

对临时表进行排序是阅读时最长的部分，我很乐意摆脱 ORDER BY 部分，但我不想错过任何记录，因为顺序在 SELECT LIMIT 0,1000 和 SELECT LIMIT 1000 之间发生了变化， 1000。

mysql order-by

3 个回答

  1. 这与 ORDER BY 没有直接关系，但要小心使用 LIMIT N,M 模式。这通常用于分页结果，例如

    ... limit 0,10;
... limit 10,10;
... limit 20,10;

    每次通过 mysql 都会“选择”前 N 行，只将最后 M 行返回给客户端。随着你越来越深入，你选择了越来越多的行，只是被扔掉了。

    简而言之，每个“页面”需要更长的时间来加载。如果您的数据集很小并且查询很好地使用索引，这可能看起来无关紧要，但随着您的成长，效果会变得明显。我觉得将这些约束放在 where 子句中通常会更好。例如

    ... where id between 0 and 10;
... where id between 11 and 20;
... where id between 21 and 30;

    最后，如果您担心对限制的排序会有效地为您创建正确的范围限制，那么这个问题从一开始就完全消除了。

    • 2
  2. Best Answer

    我想这取决于存储引擎。我可以告诉 MyISAM，没有任何顺序的选择将按顺序返回文件中出现的行（即使保存在内存中）。

    当您在写入期间对数据进行排序时，它将在 MyISAM-temptables 中按 id 排序。所以你可以放心地假设它总是会返回正确的结果，只要你不从 myISAM-temp 中删除。

    但新的问题是 - temptable 是以 myIsam 格式创建的吗？我假设将使用默认存储引擎。这个以前是MyISAM，但是在MySQL 5.1以后改成了InnoDB。

    我不知道 innodb 将如何处理未排序的选择。

    - 编辑

    http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

    在大型（磁盘上）临时表中使用 myISAM。这不是故意的行为行按写入的顺序顺序提供，但它是设计（如记录）可靠的，只要 myISAM 表仅附加。

    对于像 InnoDB、BDB 和最终的 MEMORY 这样的任何“哈希表”，这很可能不是真的。但是当内存表转换为“磁盘表”时，当前订单“冻结在磁盘上”，因此再次可靠。

    但我想我们这里还有另一种问题：

    • “真正的”问题似乎是您的“订购依据”感觉太慢了，您想解决这个问题。您偶然知道您的表大小和会话 max_heap_table_size 吗？如果您的表已转换为 myisam，这会提示您。您还可以使用 Index-Hash-method (BTREE, HASH) 来寻找优化。

    • 您是否知道 LIMIT N,M 会变得非常慢，因为它必须选择 N 行，丢弃它们，然后选择 M 行返回？

    • 1

  3. 而不是您的原始查询

    SELECT FROM tt ORDER BY id LIMIT ?,1000;

    您可能想要重构查询 tp 仅对键应用顺序并将键连接回原始表：

    SELECT tt.*
FROM
(
    SELECT id FROM tt
    ORDER BY id LIMIT ?,1000
) ttkeys
LEFT JOIN tt USING (id);

    这样做可以强制 1000 个键只加入原始表。

    我使用LEFT JOIN而不是INNER JOIN因为LEFT JOIN在连接完成后保留键的顺序，而INNER JOIN倾向于撤消子查询中的顺序。

    如果对内部临时表执行此操作有问题，请尝试使用外部临时表，如下所示：

    CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
CREATE TEMPORARY TABLE ttkeys
    SELECT id FROM tt
    ORDER BY id LIMIT 1000
;
ALTER TABLE ttkeys ADD PRIMARY KEY (id);
SELECT tt.* FROM ttkeys
LEFT JOIN tt USING (id);
    • 1

相关问题