MySQL能否在单个查询中使用多个索引？

Question

想象一下一个有多列的表格，例如，id, a, b, c, d, e。我通常通过id选择，但是客户端应用程序中存在使用各种条件覆盖子集列的多个查询。

当MySQL执行单个表上的查询，涉及多列上的多个WHERE条件时，它是否能够真正利用在不同列上创建的索引？或者唯一使查询快速的方法是为所有可能的查询创建多列索引？

- kolypto

---

你有一个查询的例子吗？ - ZombieCode

@Ekaterina，你好 :) 这个问题涉及到实践和经验的一般性问题，我相信这里不需要具体的查询。但是，如果有必要，我可以举一个例子。 - kolypto

Answer 1

是的，MySQL可以为一个单一查询使用多个索引。优化器将决定哪些索引有利于查询。您可以使用EXPLAIN获取有关MySQL执行语句的信息。您可以使用提示来添加或忽略索引，如下所示：

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

我建议阅读MySQL如何使用索引。

以下是一些摘录：

如果有多个索引可供选择，MySQL通常使用找到最少行的索引。

如果在col1和col2上存在多列索引，则可以直接获取适当的行。如果在col1和col2上存在单列索引，则优化器将尝试使用Index Merge优化（请参见第8.2.1.4节“Index Merge Optimization”），或者通过决定哪个索引查找较少的行并使用该索引来获取行来找到最限制性的索引。

- Kermit

---

哇，那要么是新的东西，要么是我在文档中错过了。谢谢，很有趣 :) 无论如何，对于特定情况而言，使用多列索引应该更具性能优势。 - kolypto

Answer 2

传统上，MySQL在给定查询中每个表引用只能使用一个索引。然而，在MySQL的较新版本中，一种称为"索引合并"（index merge）的操作可以发生，使MySQL能够在一个表中使用多个索引。

http://openquery.com/blog/mysql-50-index-merge-using-multiple-indexes

- Chris Henry

---

我认为你应该将其编辑为："可以使用每个表引用一个索引"。 - ypercubeᵀᴹ

Answer 3

MySQL可以使用索引合并来合并两个索引的结果。但这并不是MySQL真正推荐的方法。如果它优化了查询执行，它会使用两个索引，但这也是提示查询开发人员创建复合索引的一种方式。

索引合并绝不等同于复合索引。 以下是Baron Schwartz书中的摘录：

索引合并策略有时非常有效，但通常实际上是表格索引不良的指示： • 当服务器交叉索引（通常为AND条件）时，这通常意味着您需要一个具有所有相关列的单个索引，而不是必须组合多个索引。 • 当服务器联合索引（通常为OR条件）时，有时算法的缓冲、排序和合并操作会使用大量CPU和内存资源。如果并非所有索引都非常选择性，则扫描将向合并操作返回许多行。

- saurabh

Answer 4

• 查询中的每个SELECT（例如UNION、子查询、派生表等）都是单独优化的。也就是说，每个查询可能使用不同的索引。
• 一个SELECT可以在所谓的“索引合并”中使用多个索引，但这种情况很少出现。
• 当EXPLAIN显示它正在使用Index merge (intersect)时，几乎总是可以通过使用包含交集索引列的复合索引来改进。
• Index merge (union)有时（很少）用于OR。这种情况可能通过重写查询为两个SELECT的UNION来改进。

- Rick James