索引是否与“IN”一起使用？条款 [英] Do indexes work with "IN" clause

问题描述

如果我有这样的查询：

Select EmployeeId 
From Employee 
Where EmployeeTypeId IN (1,2,3)

我在上有一个索引EmployeeTypeId 字段，SQL服务器是否仍然使用该索引？

and I have an index on the EmployeeTypeId field, does SQL server still use that index?

推荐答案

是的，这是对的。如果您的employee表有10,000条记录，并且只有5条记录在（1,2,3）中有employetyypeID，那么它很可能会使用索引来获取记录。但是，如果它发现9,000条记录的employeeIDType在（1,2,3）中，那么它很可能只是进行表扫描以获得相应的EmployeeID，因为它只是运行整个表而不是转到索引树的每个分支并单独查看记录。

Yeah, that's right. If your employee table has 10,000 records, and only 5 records have employeetypeID in (1,2,3), then it will most likely use the index to fetch the records. However, if it finds that 9,000 records have the employeeIDType in (1,2,3), then it would most likely just do a table scan to get the corresponding EmployeeIDs, as it's faster just to run through the whole table than to go to each branch of the index tree and look at the records individually.

SQL Server做了很多事情来尝试优化查询的运行方式。但是，有时它得不到正确的答案。如果您知道SQL Server没有使用索引，通过查看查询分析器中的执行计划，您可以告诉查询引擎使用特定索引，并对您的查询进行以下更改。

SQL Server does a lot of stuff to try and optimize how the queries run. However, sometimes it doesn't get the right answer. If you know that SQL Server isn't using the index, by looking at the execution plan in query analyzer, you can tell the query engine to use a specific index with the following change to your query.

Select EmployeeId From Employee WITH (Index(Index_EmployeeTypeId )) Where EmployeeTypeId IN (1,2,3)

假设您在EmployeeTypeId字段上的索引名为Index_EmployeeTypeId。

Assuming the index you have on the EmployeeTypeId field is named Index_EmployeeTypeId.

这篇关于索引是否与“IN”一起使用？条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！