当 WHERE 子句中只有单个列时，SQL Server 是否会使用复合索引？
假设我有一个表：
CREATE TABLE Users (
Id INT IDENTITY (1, 1),
FirstName VARCHAR(40),
LastName VARCHAR(40)
)
查询通常针对 FirstName 或 LastName，但也针对 FirstName 和 < em>姓氏。
如果我在FirstName上创建一个非聚集索引，在LastName上创建另一个非聚集索引，那么我的前两个查询就可以满足。显然，SQL Server 将为另一个查询使用索引交集。
或者，如果我在 (FirstName) 和 (LastName, FirstName) 上有索引，SQL Server 是否可以/是否使用第二个索引来仅对 LastName 进行查询以及对两者进行查询？
SQL Server 复合索引部分是从左到右还是从右到左存储？换句话说：它将把密钥构建为 LastNameFirstName 或 FirstNameLastName？还是可以任意选择一个？
Say I've got a table:
CREATE TABLE Users (
Id INT IDENTITY (1, 1),
FirstName VARCHAR(40),
LastName VARCHAR(40)
)
Queries are usually on FirstName or LastName, but also on FirstName and LastName.
If I create a non-clustered index on FirstName and another on LastName, then my first two queries are catered for. Apparently, SQL Server will use index intersection for the other query.
Alternatively, if I have indexees on (FirstName) and on (LastName, FirstName), can/does SQL Server use the second index for queries on just LastName as well as queries on both?
Does SQL Server store compound index parts left-to-right or right-to-left? In other words: will it build the key as LastNameFirstName or FirstNameLastName? Or is it free to choose one arbitrarily?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，数据库将使用索引（LastName，FirstName）来查询LastName。不过，它不会仅使用此索引来查询 FirstName。
存储位于 B-Tree 中。无论您认为它是从右到左存储还是从左到右存储，都只是一种有用的可视化辅助，与实际的数据存储无关。
Yes, the database will use the index (LastName, FirstName) for queries on LastName. It will not use this index for queries only on FirstName though.
Storage is in a B-Tree. Whether you think of it as being stored right-to-left or left-to-right is just a useful visualization aid, and not related to the actual data storage.
是的，如果您单独查询 LastName，则应使用 (LastName, FirstName) 索引。因此，当单独按姓氏查询或同时按姓氏和名字查询时，都会使用它。
一般准则是确保具有最大选择性的列首先出现在复合索引中，因为这可以在随后的选择性较低的列之前提供最大的好处/缩小结果集范围。
Yes, if you query on LastName alone it should use the (LastName, FirstName) index. So it would be used both when querying by LastName alone, or LastName and FirstName together.
General guideline is to ensure the column with the greatest selectivity appears first in the compound index as this provides the most benefit/narrows the resultset down sooner before the following, less selective columns.
根据您发送的实际查询，即使您仅搜索第二列，也可能会使用两列的复合索引。但是，您不会得到索引查找，但很可能得到索引扫描。这对您来说是否“足够好”，取决于您的具体环境。索引与其说是一门科学，不如说是一门艺术，许多不同的因素会影响您对如何对表进行索引的决定。这始终是一种权衡，因为表上的索引太多与索引太少一样糟糕。确保最关键的查询得到很好的覆盖，然后根据具体情况决定是否值得花费任何额外的索引。
另外，由于尚未提及并且您至少使用的是 SQL Server 2005：让我为非聚集索引添加 INCLUDE 子句。对于任何索引策略来说，它都是一个被忽视但非常有用的补充。
Depending on the actual query you're sending, a composite index on two columns may be used even if you search for the 2nd column only. However, you won't get an index seek, but most likely an index scan. If this is "good enough" for you, depends on your specific environment. Indexing is more of a art than a science and a lot of different factors influence your decision on how to index a table. It's always a trade-off as having too many indices on a table is just as bad as having too few. Make sure that your most crucial queries are covered well and then decide on a case by case basis whether any additional index is worth its cost.
Also, as it hasn't been mentioned yet and provided you're at least on SQL Server 2005: Let me throw in the INCLUDE clause for nonclustered indices. It is an overlooked, but really useful addition to any indexing strategy.