MySQL 查询使用小于和 ORDER BY DESC

Question

我正在处理相当大的数据集，并尝试根据四个不同数据 block 的每个组合创建一个查询。所有这些碎片加起来形成了惊人的 122,000,000 行。然后，我尝试找到小于特定数量的权重，并按另一个值从最高到最低排序。

我可以使用weight < x没问题。

我可以使用weight < x order by height ASC没问题。

我什至可以使用weight < x order by height DESC当x同时位于上端和下端附近时。但一旦它开始悄悄进入中间，它很快就会从几秒钟上升到几分钟，到“我不会等那么久。”

有什么想法吗？ (名称已更改，但类型未更改)

创建:

CREATE TABLE combinations (
    id bigint(20) unsigned NOT NULL auto_increment,
    up smallint(2) NOT NULL,
    left smallint(2) NOT NULL,
    right smallint(2) NOT NULL,
    down smallint(2) NOT NULL,
    weight decimal(5,1) NOT NULL,
    width smallint(3) NOT NULL,
    forward decimal(6,2) NOT NULL,
    backwards decimal(5,2) NOT NULL,
    in decimal(7,2) NOT NULL,
    out smallint(3) NOT NULL,
    height smallint(3) NOT NULL,
    diameter decimal(7,2) NOT NULL,
    PRIMARY KEY  (id)
);

索引

ALTER TABLE combinations ADD INDEX weight_and_height(weight,height);

查询

SELECT * FROM combinations WHERE weight < 20 ORDER BY height DESC limit 0,5;

解释

| id | select type | table        | type  | possible_keys     | key               | key_len | ref  | rows | extra       |
|  1 | simple      | combinations | index | weight_and_height | weight_and_height | 5       | NULL |   10 | using where |

Answer 1

您的索引仅用于过滤 weight 。步骤如下:

1. 所有包含 weight < x 的行( WHERE ) 被发现(使用任何以 weight 开头的索引)
2. 该集合已排序 ( ORDER BY height ... )
3. 跳过 0 ( OFFSET ) 行；
4. 已传送 5 ( LIMIT ) 行。

潜在成本较高的部分是步骤 1。在您的示例中，“20”可能位于列表的前面。事实上EXPLAIN估计该集合只有 10 行。对于 x 的更大值，步骤 1 需要更长的时间。这是不可避免的。

所有步骤 1 中的行均已处理；因此，步骤 2 的时间也会有所不同。 (5.6 有一个额外的优化，部分结合了步骤 2、3、4。)

你真的在做SELECT * ？例如，如果您只想 SELECT id ，然后INDEX(weight, height, id)运行速度会快很多，因为查询可以完全在索引中执行。

如果您确实需要您提到的查询，那么运行速度会更快:

SELECT c.*
    FROM (
        SELECT id FROM combinations
            WHERE weight < 20 ORDER BY height DESC limit 0,5
         ) ids
    JOIN combinations AS c  USING(id)
    ORDER BY height DESC;

注释:

• 如前所述，子查询是“使用索引”。
• 子查询仅传送 5 行。
• 外部SELECT只需要处理 5 行。
• id被索引(因为它是 PRIMARY KEY )，所以 JOIN效率很高。
• (回复:标题)“小于”和“DESC”并不重要。