在MySQL中使用索引的最大IN子句数量是多少？

Question

在MySQL中使用索引的最大IN子句数量是多少？

9

我目前正在尝试查询一个包含三个in子句的表格，如下：

 SELECT *
 FROM table
 WHERE
     a IN (2884,5320)
   AND 
     b IN ('a', 'b', 'c')
   AND 
     c IN (1, 2, 3)
   AND d='abcd'
   AND date BETWEEN 0 AND 1383177599

表格以index(a, b, c, d, date)

方式索引。

然而，当我在查询上运行解释器时，解释器显示没有适当的索引可用。即使我使用了FORCE INDEX。

如果我将上述IN之一更改为=，例如

SELECT *
FROM table
WHERE
    a = 2884
  AND 
    b IN ('a', 'b', 'c')
  AND 
    c IN (1, 2, 3)
  AND d = 'abcd'
  AND date BETWEEN 0 AND 1383177599

MySQL可以强制使用索引，但如果没有覆盖索引，它会选择另一个非覆盖索引。这种情况不管哪个“IN”被更改为“=”都是一样的。我的问题是：对于一个带有索引的查询，您可以使用in语句的数量吗？我是否遗漏了什么明显的东西？关于该表需要了解几件事：大小约为9 GB，包含大量文本列（JSON字段），但此列不是上述查询列中的任何一列。上面显示的in语句可能会大得多（200-300项）。谢谢！编辑：以下是查询的explain输出（使用“FORCE INDEX”）： 1，“SIMPLE”，“table”，“ALL”，“correct_index”，NULL，NULL，NULL，6977553，“Using where” 其中正确的索引是上面解释的（index(a，b，c，d，date)）。

- cmwright

你可以展示explain的输出吗？ - Ashalynd

是的，在@Ashalynd的问题后添加了Yup。 - cmwright

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

当使用IN等范围谓词时，不能期望利用索引搜索多个列。

即使您有一个多列索引（a，b，c，d，date），左边的列应该是相等谓词（=）的，而最多只能有一个列用于范围谓词。索引中的任何后续列都没有帮助。

例如:

WHERE a = 2884 AND b = 'b' AND c IN (1, 2, 3) AND d = 'abcd'

所以，a和b是等值谓词，c是范围谓词，d是另一个等值谓词。

在查询上运行EXPLAIN，并注意len和ref列指示您仅使用索引的前两列。对于d的条件是通过在前三列的索引中搜索所有找到的行来完成的。

           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: a
          key: a
      key_len: 7              <--- two columns' length
          ref: const,const    <--- only two values for index columns `a` and `b`
         rows: 4
        Extra: Using where; Using index

将c更改为相等谓词允许使用所有四个列进行索引查找：

WHERE a = 2884 AND b = 'b' AND c = 2 AND d = 'abcd'

           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: a
          key: a
      key_len: 25                      <--- four columns' length
          ref: const,const,const,const <--- four values
         rows: 2
        Extra: Using where; Using index

我在我的演示文稿《如何真正设计索引》中更详细地讲述了这个问题。

关于你的评论：

是否有任何方法可以避免不必要的代码重写？

你指出了 只有一个范围谓词 能从索引中受益，但是你仍然可以在 WHERE 子句中使用其他范围谓词，但它们不会从索引中受益。

但这并不是致命伤，因为如果你能用索引来缩小搜索范围 99%，那就算是赢了。然后对匹配的行应用其他表达式是我们可以接受的成本。

优化器将尝试选择最有效的索引，这在很大程度上取决于索引的选择性。然后查询使用索引来缩小搜索范围，只有通过该搜索的子集才会被测试与其他条件匹配。

再看一下你的查询：

... WHERE
 a IN (2884,5320)
AND 
 b IN ('a', 'b', 'c')
AND 
 c IN (1, 2, 3)
AND d='abcd'
AND date BETWEEN 0 AND 1383177599

假设我们知道只有1%的行匹配 C IN（1,2,3），但其他条件平均匹配20-40％的行。

我们可以为相等谓词建立索引，这是可以的。然后我们可以选择另一列用于索引，因为所有其他条件都是范围谓词。我们选择最具选择性的列： C 。因此，最佳索引在（d，c）上，并且必须按该顺序排列。
您的应用程序中可能还有其他查询，WHERE子句中引用不同的列以及要搜索的特定值。因此，我们可能需要使用具有不同列集或甚至以不同顺序相同列的另一个索引。需要创建哪些索引取决于要优化的查询，因此需要多个索引并不罕见。