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

本文介绍了MySQL可以为单个查询使用多个索引吗？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

想象一下有多列的表，比如 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.