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 上，优化器将尝试使用索引合并优化(参见第 8.2.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.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.
相关文章