是否有必要在SQL表中的每个可查询字段组合上有索引以优化性能？ [英] Is it necessary to have an index on every combination of queryable fields in a SQL table to optimize performance?
问题描述
如果我的 User 表有几个可查询的字段（例如DepartmentId，GroupId，RoleId），如果我为每个组合创建索引字段？
If my User table has several fields that are queryable (say DepartmentId, GroupId, RoleId) will it make any speed difference if I create an index for each combination of those fields?
通过可查询，我指的是一个查询屏幕，最终用户可以根据部门，组或角色选择记录，
By "queryable", I'm referring to a query screen where the end user can select records based on Department, Group or Role by selecting from a drop-down.
目前，我有一个关于DepartmentId，GroupId和RoleId的索引。
At the moment, I have a index on DepartmentId, GroupId and RoleId. That's a single non-unique index per field.
如果最终用户选择群组B中的任何人，则SQL如下所示：
If an end user selects "anyone in Group B", the SQL looks like:
select * from User where GroupId = 2
Having an index on GroupId should speed that up.
但是，如果最终用户选择B组和角色C中的任何人，SQL将看起来像this：
But if the end user select "anyone in Group B and in Role C", the SQL would look like this:
select * from User where GroupId = 2 and RoleId = 3
分别对GroupId和RoleId建立索引可能没有什么区别，对吧？
Having indexes on GroupId and RoleId individually may not make any difference, right?
如果我有一个索引跨越两个 GroupId和RoleId。
A better index for that search would be if I had one index spanning both GroupId and RoleId.
但是如果是这样，将需要对可查询字段的每个组合具有索引。因此，我需要所有这些索引：
But if that's the case, than that would mean that I would need to have an index for every combination of queryable fields. So I would need all these indexes:
- DepartmentId
- GroupId
- RoleId
- DepartmentId和GroupId
- DepartmentId和RoleId
- GroupId和RoleId
- 部门Id，GroupId和RoleId
- DepartmentId
- GroupId
- RoleId
- DepartmentId and GroupId
- DepartmentId and RoleId
- GroupId and RoleId
- Department Id, GroupId and RoleId
任何人都可以了解这一点吗？
Can anyone shed some light on this? I'm using MySQL if that makes a difference.
推荐答案
多列索引可用于该列的任何左前缀指数。因此，关于（A，B，C）的索引可以用于（A），（A，B）和（A，B，C）上的查询，但是它不能例如用于）或（B，C）。
A multi-column index can be used for any left prefix of that index. So, an index on (A, B, C) can be used for queries on (A), (A, B) and (A, B, C), but it cannot, for example, be used for queries on (B) or (B, C).
如果所有列都单独索引，MySQL（5.0或更高版本）也可以使用索引合并优化。
If the columns are all indexed individually, MySQL (5.0 or later) may also use Index Merge Optimization.
这篇关于是否有必要在SQL表中的每个可查询字段组合上有索引以优化性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
