mysql - 当订购空集时，SQL 查询变得非常慢

Question

我有一个 SQL 查询，需要花费大量时间来评估，因为它在非常大的数据集上运行。当尝试提高执行时间时，我发现了以下几点:

执行以下查询时，MySQL 服务器花费大量时间(最多 100 秒)

SELECT some_data 
FROM   table 
       INNER JOIN anothertable 
               ON ( table.value = 
                               anothertable.value ) 
WHERE  ( table.parent = 56521 
         AND table.date >= 
             '2016-10-19 08:37:45.606947' ) 
ORDER  BY table.date DESC 
LIMIT  1

所以我猜测是查询的排序部分花费了如此多的执行时间，我手动删除了排序以查看执行中的差异:

SELECT some_data 
FROM   table 
       INNER JOIN anothertable 
               ON ( table.value = 
                               anothertable.value ) 
WHERE  ( table.parent = 56521 
         AND table.date >= 
             '2016-10-19 08:37:45.606947' ) 
LIMIT  1

上面的查询需要 0.45 秒并导致查询集为空。

我得出的结论是，我的查询在评估 WHERE 子句之前对整个数据集进行排序。我应该如何形成查询以防止这种行为？为什么会出现这种行为？

这些是慢查询和快查询的 EXPLAIN 表:

Slow
+----+-------------+-------+------------+--------+------------------------------------------+------------------+---------+------------------------------+------+----------+-------------+
    | id | select_type | table | partitions | type   | possible_keys                            | key              | key_len | ref                          | rows | filtered | Extra       |
    +----+-------------+-------+------------+--------+------------------------------------------+------------------+---------+------------------------------+------+----------+-------------+
    |  1 | SIMPLE      | A     | NULL       | index  | PRIMARY,D4b797d14e515242e7251754c57b7701 | date             | 5       | NULL                         | 1325 |     0.08 | Using where |
    |  1 | SIMPLE      | B     | NULL       | eq_ref | PRIMARY                                  | PRIMARY          | 4       | value                        |    1 |   100.00 | NULL        |
    +----+-------------+-------+------------+--------+------------------------------------------+------------------+---------+------------------------------+------+----------+-------------+

Fast:
     +----+-------------+-------+------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+------+----------+-------+
    | id | select_type | table | partitions | type   | possible_keys                            | key                              | key_len | ref                          | rows | filtered | Extra |
    +----+-------------+-------+------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+------+----------+-------+
    |  1 | SIMPLE      | A     | NULL       | ref    | PRIMARY,D4b797d14e515242e7251754c57b7701 | D4b797d14e515242e7251754c57b7701 | 4       | const                        | 5175 |   100.00 | NULL  |
    |  1 | SIMPLE      | B     | NULL       | eq_ref | PRIMARY                                  | PRIMARY                          | 4       | value                        |    1 |   100.00 | NULL  |
    +----+-------------+-------+------------+--------+------------------------------------------+----------------------------------+---------+------------------------------+------+----------+-------+

Answer 1

MySQL 使用 date 上的索引进行第一个查询。它可以部分评估 where 条件 (table.date >= '2016-10-19 08:37:45.606947')，如果适合，它将读取从您的表(相对较慢)中查看它是否也适合。它可以在找到结果后立即停止(因为 order by 和 limit 1)。

您的第二个查询使用 parent 上的索引(即具有长名称的索引)，查找适合的行，然后从您的表并检查它是否也适合。它必须继续，直到它检查了具有正确 parent 值的所有行(它使用索引找到的)，并且它找到的所有行都必须进行文件排序，并且将返回最新的行.

(我忽略了 MySQL 也必须检查/执行 join，但这在两个查询中都是相同的)。

显然，与您的 parent 条件相比，您有更多的行适合您的 date 条件，因此它必须执行更多相对较慢的表查找，这将需要更长。

在这种情况下。根据您的数据，实际上可能会发生这样的情况:在 date 通过索引检查的第一行已经满足 parent 条件，并且可能会立即停止。如果它使用 parent 上的索引，MySQL 将被迫检查具有 parent 值的所有行，然后进行文件排序。 MySQL 根据一些统计数据决定，值得冒这个风险。嗯，它选错了。

您可以执行以下操作:

• 优化表 `table`(第二个 table 是您的表名)以更新您的统计信息。这有时会有所帮助，但通常不会(因为统计数据非常有限)。
• 强制MySQL使用您知道更好的索引(... FROM table force index (D4b797d14e515242e7251754c57b7701) inner join ...)
• 为您的查询添加完美的索引:复合索引table(parent, date)应该(不计算join的潜在影响)为您提供更快的结果与无序查询不同，MySQL 将自行使用它。