sql-server - 查询中过滤器的顺序应该是什么，以优化查询以使用索引

Question

我没有什么疑问。我从一些同事那里听说，如果我们在下表中创建了复合非聚集索引，那么所有过滤器的顺序应该是索引列的顺序，然后是过滤器。

MyTable (T1, T2, T3, T4, T5)
Non Clustured index(T1, T2) in the order T1 then T2

问题

1. 哪个查询运行速度更快？

2. 索引列的顺序是否会影响表现 ？

3. 应索引的列首先优化查询？

4. 处理a的顺序是什么询问。首先采用哪个过滤器处理查询时？可以从最后一个过滤器开始并继续首先过滤？

查询 1

Select * from MyTable WHERE T1=1 AND T2=2 AND T3=1 
--(Indexing will be used) Fastest as T3 has been included after indexed columns

查询 2

Select * from MyTable WHERE T2=1 AND T1=2 AND T3=1
--(No Indexing will be used)

查询3

Select * from MyTable WHERE T3=1 AND T1=1 AND T2=2
--(Indexing will be used) slower than Query 1 as indexed columns included afterwards

查询 4

Select * from MyTable WHERE T3=1 AND T2=1 AND T1=2
--(No Indexing will be used) slower than Query 2 as indexed columns occurs after non indexed column condition

编辑:

查询 5

Select * from MyTable WHERE T3=1 AND T2=1

查询 6

Select * from MyTable WHERE T3=1 AND T1=1 

查询7

Select * from MyTable WHERE T3=1 AND T2=1 OR T1=2

查询8

SSMS 索引创建中的以下顺序意味着什么，即哪个过滤器应该排在第一位？

当我尝试打乱索引列中显示的列的顺序时。我看到书签查找正在增加，并且正在使用Clustured索引扫描。但是当我使用如图所示的相同顺序时。然后书签查找被删除，并且还得到了索引查找。所以我可以看到索引中的列顺序在这里发挥着一些至关重要的作用，但无法感觉到如何发挥作用。

Answer 1

查询中谓词的顺序没有区别!

考虑复合索引中的列顺序时，通常将其类比为电话簿。这是按(姓氏、名字)排序的。这使得按姓氏查找变得简单，但不能帮助您按名字查找数字。

如果您在(T1，T2)上有索引，则可以使用它来有效地回答

Select * from MyTable WHERE T1=2

或者

Select * from MyTable WHERE T2=1 AND T1=2

但无法有效回答

Select * from MyTable WHERE T2=1

(这并不是说该索引对于该查询总是完全无用。如果该索引与表的大小相比很窄，并且统计数据表明很少有匹配行将返回后，查询优化器可能仍然选择扫描该索引并优先进行键查找，而不是可能需要覆盖更多页面的聚集索引扫描。)

根据评论的示例脚本

CREATE TABLE MyTable  (
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
T1 INT NOT NULL,
T2 INT NOT NULL,
Filler CHAR(8000) NOT NULL /*Just to make sure that the table is very wide!*/);

CREATE NONCLUSTERED INDEX IX ON MyTable (T1,T2);

   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 INSERT INTO MyTable 
 SELECT N, N-1,''
   FROM cteTally
  WHERE N <= 1000;
 
 SET STATISTICS IO ON
 Select * from MyTable  WHERE T2=1
 /*
 Table 'MyTable'. Scan count 1, logical reads 9, physical reads 0, 
 read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 */
 
 /*Force a clustered index scan just to compare...*/
 Select * from MyTable   WITH( INDEX (1) ) WHERE T2=1 
/*
Table 'MyTable'. Scan count 1, logical reads 1005, physical reads 0, 
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*/