MySQL可以为单个查询使用多个索引吗？ [英] Can MySQL use multiple indexes for a single query?

问题描述

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

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.

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

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可以使用单个查询的多个索引。优化器将确定哪些索引将使查询受益。您可以使用 EXPLAIN 来获取有关MySQL如何执行语句的信息。您可以使用如下提示添加或忽略索引：

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

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

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

以下摘录：

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

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

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

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.3.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可以为单个查询使用多个索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！