指示MySQL将WHERE子句应用于前一个WHERE子句返回的行

Question

我有以下疑问：

  SELECT dt_stamp
    FROM claim_notes
   WHERE type_id             = 0
     AND dt_stamp           >= :dt_stamp
     AND DATE( dt_stamp )    = :date
     AND user_id             = :user_id
     AND note             LIKE :click_to_call
   ORDER BY dt_stamp
   LIMIT 1

claim_notes表大约有50万行，因此这个查询的运行速度非常慢，因为它必须搜索未索引的note列(我对此无能为力)。我知道，当应用type_id、dt_stamp和user_id条件时，我将搜索大约60行，而不是50万行。但是MySQL似乎没有按顺序应用这些。我想要做的是看看是否有一种方法可以告诉MySQL只将note LIKE :click_to_call条件应用于满足前面条件的行，这样它就不会搜索符合该条件的所有行。

我的想法是这样的：

  SELECT dt_stamp
    FROM (
            SELECT *
              FROM claim_notes
             WHERE type_id           = 0
               AND dt_stamp         >= :dt_stamp
               AND DATE( dt_stamp )  = :date
               AND user_id           = :user_id
         )
     AND note LIKE :click_to_call
   ORDER BY dt_stamp
   LIMIT 1

这是可行的，而且速度非常快。我只是想知道这是不是正确的方式，或者是否有更正式的方式来处理它。

Answer 1

不要使用派生表解决方案，因为它的性能不佳。让我感到惊讶的是，拥有=和>=运算符的MySQL会首先选择LIKE。

无论如何，我会说您可以尝试在这些字段上添加一些索引，看看会发生什么：

ALTER TABLE claim_notes ADD INDEX(type_id, user_id);
ALTER TABLE claim_notes ADD INDEX(dt_stamp);

后一种索引实际上不会改进对索引的搜索，而是会改进结果的排序。

当然，拥有查询的EXPLAIN会有所帮助。

Answer 2

应该没有必要这样做。如果WHERE子句中有多个用AND分隔的术语，那么MySQL优化器可以处理这个问题。基本上，它知道如何“使用索引应用所有条件，然后将未索引的表达式仅应用于其余行”。

但选择正确的索引是很重要的。多列索引最适合一系列AND术语，而不是单个索引。MySQL可以应用索引交集，但这比使用单个索引查找相同行的效率要低得多。

以下几条逻辑规则适用于创建多列索引：

与非唯一columns.

• Equality条件(>=、IN、BETWEEN、!=等)相比，唯一列上的
• 条件更受欢迎。在索引中用于范围条件的第一列之后，后续列将不会使用索引。
• 大多数情况下，搜索列上函数的结果(例如DATE(dt_stamp))将不会使用索引。在这种情况下，最好存储DATE数据类型，并使用=而不是>=.
• If条件匹配超过表的20%，MySQL可能会决定跳过索引并执行表扫描。

以下是我和我在Percona的同事们举办的一些网络研讨会，以帮助解释索引设计：

• Tools and Techniques for Index Design
• MySQL Indexing: Best Practices
• Advanced MySQL Query Tuning
• Really Large Queries: Advanced Optimization Techniques

您可以免费获得这些网络研讨会的幻灯片，并免费观看录制，但录制需要注册。