如果查询索引列的子集，是否会使用索引? [英] Will indexes get used if you query a subset of the columns of the index?

问题描述

因此，请考虑以下表格:

表:

• id(整数，主键)
• c1
• c2
• c3
• c4
• c5

假设我使用的查询在运行时有所不同(例如，一个网站，其中的复选框表示是否要在特定字段上进行搜索)，

(使用一些x, y, z作为值)

• SELECT * FROM table WHERE c1 = 1 AND c2 = 2
• SELECT * FROM table WHERE c1 = 1 AND c2 = 2 AND c3 = x AND c4 = y
• SELECT * FROM table WHERE c1 = 2 AND c2 = 3
• SELECT * FROM table WHERE c1 = 2 AND c2 = 3 AND c5 = z

问题:

1. 索引对(c1, c2, c3, c4, c5)的影响是什么?在几个查询中将如何使用它?
2. 在此表上创建最佳索引是什么?

解决方案

MySQL文档很好地解释了多列索引的工作方式 解决方案

MySQL documentation does a good job of explaining how multi-column indexes work here.

In general, the index can be used for a where clause when some number of column on the leftmost side of the index have equality conditions. Your where clauses use the following columns:

• c1, c2
• c1, c2, c3, c4
• c1, c2
• c1, c2, c5

Any index starting with columns c1 and c2 would normally be used for these queries. MySQL can apply other conditions to using the index, such as selectivity. That is, if c1 and c2 have constant values (for instance) then using an index will not benefit the query.

To optimize all these combinations, you can create two indexes: c1, c2, c3, c4 and c2, c1, c5. The reason for swapping c1 and c2 in the second index is so you could handle queries where the condition is on c2 but not c1, as well as the reverse.

这篇关于如果查询索引列的子集，是否会使用索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！