MySQL 可以为单个查询使用多个索引吗？

Can MySQL use multiple indexes for a single query?

提问by kolypto

Imagine a table with multiple columns, say, id, a, b, c, d, e. I usually select by id, however, there are multiple queries in the client app that uses various conditions over subsets of the columns.

想象一个有多个列的表，比如，id, a, b, c, d, e。我通常选择 by id，但是，客户端应用程序中有多个查询对列的子集使用各种条件。

When MySQL executes a query on a single table with multiple WHERE conditions on multiple columns, can it really make use of indexes created on different columns? Or the only way to make it fast is to create multi-column indexes for all possible queries?

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

回答by Kermit

Yes, MySQL can use multiple index for a single query. The optimizer will determine which indexes will benefit the query. You can use EXPLAINto obtain information about how MySQL executes a statement. You can add or ignore indexes using hints like so:

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

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

I would suggest reading up on how MySQL uses indexes.

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

Just a few excerpts:

仅摘录几段：

If there is a choice between multiple indexes, MySQL normally uses the index that finds the smallest number of rows.

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer will attempt to use the Index Merge optimization (see Section 8.2.1.4, “Index Merge Optimization”), or attempt to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows.

如果在多个索引之间进行选择，MySQL 通常使用找到最少行数的索引。

如果 col1 和 col2 上存在多列索引，则可以直接获取相应的行。如果 col1 和 col2 上存在单独的单列索引，则优化器将尝试使用索引合并优化（请参阅第 8.2.1.4 节，“索引合并优化”），或者通过决定哪个索引找到较少的索引来尝试找到最严格的索引行并使用该索引来获取行。

回答by Chris Henry

Classically, MySQL can use one index per table reference in a given query. However, in more recent versions of MySQL, an operation called an index mergecan take place and allow MySQL to use more than one index per table.

传统上，MySQL 可以在给定查询中的每个表引用使用一个索引。但是，在更新的 MySQL 版本中，可以执行称为 an 的操作，index merge并允许 MySQL 对每个表使用多个索引。

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

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

回答by saurabh

Mysql can use index merge to merge the results of two indexes. But this is not really the preferred way of mysql. It will use two indexes if that optimizes the query execution. but this is also a hint for the query developer to create a composite index.

Mysql 可以使用索引合并来合并两个索引的结果。但这并不是mysql的首选方式。如果优化查询执行，它将使用两个索引。但这也是查询开发人员创建复合索引的提示。

An index merge is by no means equivalent to a composite index. here is an excerpt from Baron Schwartz book -

索引合并绝不等同于复合索引。这是 Baron Schwartz 书中的摘录 -

The index merge strategy sometimes works very well, but it's more common for it to actually be an indication of a poorly indexed table:

? When the server intersects indexes (usually for AND conditions), it usually means that you need a single index with all the relevant columns, not multiple indexes that have to be combined.
? When the server unions indexes (usually for OR conditions), sometimes the algorithm's buffering, sorting, and merging operations use lots of CPU and memory resources. This is especially true if not all of the indexes are very selective, so the scans return lots of rows to the merge operation.

索引合并策略有时效果很好，但更常见的是它实际上表明索引表不佳：

? 当服务器与索引相交时（通常用于 AND 条件），通常意味着您需要一个包含所有相关列的索引，而不是多个必须组合的索引。
? 当服务器联合索引（通常用于 OR 条件）时，有时算法的缓冲、排序和合并操作会使用大量 CPU 和内存资源。如果并非所有索引都非常有选择性，则尤其如此，因此扫描将大量行返回给合并操作。

回答by Rick James

• Each SELECTin a query -- think UNION, subquery, derived table, etc -- is optimized separately. That is each might use different indexes.
• One SELECTcanuse multiple indexes in what it calls "index merge". This is rarelyused.
• When the EXPLAINsays it is using Index merge (intersect), then it can almost always be improved by using a composite index containing the columns used by the intersected indexes.
• Index merge (union)is sometimes (rarely) used for an OR. Such can perhapsbe improved by rewriting the query to be the UNIONof two SELECTs.

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