为只有一个匹配的Between查询索引SQL？ [英] Indexing SQL for Between query with only one match?

问题描述

我们有一个包含超过两百万行的表，其中所有对它的查询都是使用 Column1 和 Column2 。此外，只有一个可能的结果。例如......

We have a table with more than two million rows where all queries against it will be a Between lookup using Column1 and Column2. Also, there will only be one possible result. For example...

Col1     Col2
1        5
6        10
11       15

select * from table1 where 8 between Col1 and Col2

我目前有一个 Col1 和 Col2 上的唯一聚簇索引。到目前为止，我一直无法弄清楚如何进一步调整查询和索引以最小化处理的行。执行计划目前报告在找到一个且只有正确答案时处理的成本几乎为0.5和113k。

I currently have an unique clustered index on Col1 and Col2. So far I have been unable to figure out how to further tune the query and the indexes to minimize the rows handled. The execution plan currently reports cost of almost 0.5 and 113k rows handled when locating the one and only correct answer.

我可以忽略哪些选项？

根据要求，当前执行计划中的一些细节：

As requested, some details from the current execution plan:

Operation
 Clustered Index Seek
Predicate
 CONVERT_IMPLICIT(bigint,[@2],0)<=[Col2]
Seek Predicate
 Seek Keys[1]: End: Col1 <= Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))

推荐答案

我想我找到了答案。我必须首先在Col1上创建一个Unique Clustered Index，然后在Col2上创建一个Unique Unclustered Index。然后必须更新查询以强制查找每个索引。

I think I have found the answer. I had to start by creating an Unique Clustered Index on Col1, then create an Unique Unclustered Index on Col2. The query then had to be updated to force lookups on each Index.

select * from table1 where Col1 = 
    (select max(Col1) from table1 where Col1 <= 8)
and Col2 = 
    (select min(Col2) from table1 where Col2 >= 8)

执行计划现在报告0.0098成本和1行处理。

Execution plan now reports 0.0098 cost and 1 row handled.

这篇关于为只有一个匹配的Between查询索引SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！