MySQL查询优化-加入？

Question

一个供您所有MySQL专家使用的技巧:-)

我有以下查询：

SELECT o.*, p.name, p.amount, p.quantity 
FROM orders o, products p 
WHERE o.id = p.order_id AND o.total != '0.00' AND DATE(o.timestamp) BETWEEN '2012-01-01' AND '2012-01-31' 
ORDER BY o.timestamp ASC

• 订单表= 80,900行
• 产品表= 125,389行
• o.id和p.order_id已建立索引

该查询大约需要6秒钟才能完成-太长了。我正在寻找一种优化它的方法，可能使用临时表或其他类型的联接。恐怕我对这两个概念的理解还很有限。

谁能建议我优化此查询的方法？

Answer 1

首先，我将使用其他样式的语法。
ANSI-92已经有20年的睡眠时间了，许多RDBMS实际上建议不要使用您曾经使用的符号。在这种情况下，这不会有所作为，但是出于多种原因，这确实是一个很好的做法
（我会让您调查一下并自己做出决定） 。

最终答案和示例语法：

SELECT
  o.*, p.name, p.amount, p.quantity  
FROM
  orders
INNER JOIN
  products
    ON orders.id = products.order_id 
WHERE
      orders.timestamp >= '2012-01-01'
  AND orders.timestamp <  '2012-02-01'
  AND orders.total     != '0.00' 
ORDER BY
  orders.timestamp ASC

由于该orders表是您进行初始过滤时使用的表，因此这是一个开始进行优化的好地方。

与DATE(o.timestamp) BETWEEN x AND y您一起成功获取一月份的所有日期和时间。但这需要在表中的
每一行
DATE()上调用函数 （类似于RBAR的含义） 。RDBMS无法 看透 此功能， 只是知道
如何避免浪费时间。相反，我们需要进行优化，方法是重新排列数学，以使我们不需要要过滤的字段上的函数。 __orders __

    orders.timestamp >= '2012-01-01'
AND orders.timestamp <  '2012-02-01'

此版本使优化程序可以 知道
您想要一组彼此连续的日期。这称为范围搜寻。它可以使用索引非常快速地找到适合该范围的第一个记录和最后一个记录，然后挑选出介于两者之间的每条记录。这样就避免了检查所有不适合的记录，甚至避免了检查范围中间的所有记录。只需要寻找边界。

假定所有记录按日期排序，并且优化器可以看到该记录。为此，您需要一个索引。考虑到这一点，似乎有两个基本覆盖索引，你可以使用：
- (id, timestamp)
-(timestamp, id)

首先是我看到人们使用最多的东西。但这迫使优化器分别timestamp对每个对象进行范围搜索id。而且由于每种id可能性都有不同的timestamp价值，因此您一无所获。

第二个索引是我推荐的。

现在，优化器可以非常快地完成查询的这一部分。

SELECT
  o.*
FROM
  orders
WHERE
      orders.timestamp >= '2012-01-01'
  AND orders.timestamp <  '2012-02-01'
ORDER BY
  orders.timestamp ASC

碰巧的是，即使ORDER BY使用建议的索引进行了优化。您已经按照想要输出数据的顺序进行了操作。加入后无需重新排序所有内容。

然后，为了满足total != '0.00'要求，仍会检查范围内的每一行。但是您已经将范围缩小得太多，以至于这可能很好。
（我不会去到它，但你可能会发现它无法使用索引在MySQL优化该 并 的timestamp范围寻道）。

然后，您就加入了。这已经通过您已有的索引进行了优化(products.order_id)。对于上面摘录中选择的每条记录，优化器都可以进行索引查找并非常快速地识别出匹配的记录。

所有这些都假定在绝大多数情况下，每个订单行都有一个或多个产品行。例如，如果只有极少数几个订单具有任何产品行，则首先选择感兴趣的产品行可能会更快；本质上看是以相反顺序发生的联接。

优化器实际上是为您做出决定的，但是要知道这样做是很方便的，然后提供您估计对它最有用的索引。

您可以检查说明计划，以查看是否正在使用索引。如果不是，您的帮助尝试将被忽略。可能是因为数据的统计数据暗示了连接的不同顺序更好。如果是这样，则可以提供索引来代替该连接顺序。