主页 / dba / 问题 / 5160
Accepted
Mokus
Asked: 2011-08-30 02:28:05 +0800 CST

按指定列对表进行预排序

  • 772

我有一张桌子，我总是通过订购一列来获取数据。就我而言，我按datenum. 但是，由于排序，选择非常慢。

是否可以在之后订购桌子datenum。此列被定义为唯一的。如果定义为索引，选择会更快吗？

mysql innodb

2 个回答

  1. Best Answer
    • 表没有自然的、隐含的或内在的排序
    • 任何订单仅由最外层的 ORDER BY 保证

    如果这很慢，那么您可以

    • 使索引覆盖（唯一约束已经是索引）
    • 使其成为聚集索引（其性质涵盖）
    • 5

  2. 可以对表执行 ORDER BY。

    ALTER TABLE ... ORDER BY {columns} ;

    根据 MySQL 文档

    • ORDER BY 使您能够创建具有特定顺序的行的新表。请注意，插入和删除后，表不会保持此顺序。此选项主要在您知道大部分时间以特定顺序查询行时很有用。通过在对表进行重大更改后使用此选项，您可能可以获得更高的性能。在某些情况下，如果表按您稍后要对其排序的列进行排序，则可能会使 MySQL 更容易排序。

    • ORDER BY 语法允许为排序指定一个或多个列名，每个列名可选地后跟 ASC 或 DESC 以分别指示升序或降序排序。默认为升序。只允许列名作为排序条件；不允许任意表达。

    • ORDER BY 对于包含用户定义的聚集索引（PRIMARY KEY 或 NOT NULL UNIQUE 索引）的 InnoDB 表没有意义。如果存在这样的索引，InnoDB 总是根据这样的索引对表行进行排序。

    • 注意：在分区表上使用时，ALTER TABLE ... ORDER BY 仅对每个分区内的行进行排序。

    由于 InnoDB 执行双索引查找（一个针对 PRIMARY KEY，然后一个针对内部聚集索引gen_clust_index），因此在 InnoDB 表上运行 ORDER BY 确实不会为您带来任何性能提升。

    恕我直言，如果 PRIMARY KEY 有一些令人讨厌的东西，比如 8 列

    • 对于 MyISAM，您将获得出色的查找和范围搜索性能
    • 对于 InnoDB，您充其量只会得到非常边缘的结果（如果有的话）。

    注意：理论上，如果您执行以下操作，您可能会在 InnoDB 表（称为 mytable）中看到非常非常小的性能差异：

    CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
SELECT * FROM mytable
ORDER BY {primary-key columns};
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

    这将按 rowid 顺序和主键顺序对 gen_clust_index（内部集群索引）进行排序。但是，执行这种 rowid 的内部排序就像把铅变成金一样。时间、精力和能量处理远远超过了任何看似购买但从未实现的性能优势，因为每个主键查找也会导致 rowid 查找。长话短说：除非您只使用 MyISAM，否则不要这样做。

    这是我对 PRIMARY KEY 中的列使用 ORDER BY 的标准

    • PRIMARY KEY 中有 4 列或更多列
    • 对于具有 n 列的 PRIMARY KEY，列 1 .. n-1 在 WHERE 和 GROUP BY 子句中应该是静态的，而列 n 将用作查询中的范围列
    • 仅使用 MyISAM 存储引擎！！！

    警告

    我提到 8 列 PRIMARY KEY 的原因是什么？我以前的雇主每月从外部供应商那里发送数十 GB 的只读数据。我在那里学习了 ALTER TABLE...ORDER BY 并将它用于那些 MyISAM 表。由于针对 PRIMARY KEY 进行了很多范围查询，因此查询时间令所有人震惊，包括我自己。

    • 5

相关问题