SQL Server如何确定丢失索引请求中的键列顺序？

SQL Server如何确定其缺少的查询计划的索引建议中的关键列的顺序？

#1 楼

当SQL Server为特定的查询计划创建丢失的索引建议时，它将可能的键列分为2组。第一组包含作为EQUALITY谓词一部分的所有推荐列。第二组包含所有建议的列，它们是INEQUALITY谓词的一部分。

在每个组中，根据表的定义，这些列按列的顺序位置排序。 >
（非常感谢Brent Ozar为Stack Overflow数据库构建了一个复制脚本来证明这一点！）

1。创建3个相同的表，但将它们的列以不同的顺序放置。（这里的原因是使用各种列名和数据类型来表明这不会影响缺少索引建议中的列顺序。）

CREATE TABLE dbo.NumberLetterDate (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
fINT INT, fNVARCHAR NVARCHAR(40), fDATE DATETIME, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.LetterDateNumber (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
fNVARCHAR NVARCHAR(40), fDATE DATETIME, fINT INT, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.DateNumberLetter (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fDATE DATETIME, fINT INT, fNVARCHAR NVARCHAR(40), AboutMe NVARCHAR(MAX));
GO

2 。用相同的数据填充表。从“用户”表中获得100,000行并进行实际数据分配。

INSERT INTO dbo.NumberLetterDate(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.LetterDateNumber(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.DateNumberLetter(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO

3。编写需要索引的查询。从3个相等过滤器开始，过滤所有3个字段中的精确值。请注意，所有三个查询都以相同的顺序具有相同的字段：

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

所有三个表都具有完全相同的数据，并且查询是相同的。唯一的区别是字段顺序-这也是我们缺少索引请求的区别：

在执行计划中，缺少索引请求的列顺序与表格中的列顺序完全匹配。例如，在dbo.NumberLetterDate中，数字列是第一列，因此在缺失索引请求中它也是第一列：

在dbo.NumberLetterDate中，缺失索引在fINT上（数字），fLetter（nvarchar），fDate，与表中字段的顺序相同。在dbo.LetterDateNumber上，索引顺序切换到fNVARCHAR，fDATE，fINT
在dbo.DateNumberLetter上，索引顺序切换到fDATE，fINT，fNVARCHAR

对于像这样的单表操作，索引字段的顺序似乎并不取决于选择性，数据类型或查询中的位置。（我让其他人通过更复杂的查询和联接来证明这一点。）

4。混合使用不等式过滤器。例如，在INT字段上，将<> 100作为过滤器：

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

在执行计划中，首先使用相等字段，然后是不相等字段-所以这里，因为这是不等式搜索，所以fINT在所有3个丢失的索引请求中都显示为最后一个：

5。使用3个不等式过滤器。对所有字段（<>）使用相同的搜索：

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);
GO

由于没有相等搜索，因此所有3个字段在缺失索引建议中都具有相同的优先级顺序，并且现在我们回到纯粹按字段顺序排序：

编程黑洞网

SQL Server如何确定丢失索引请求中的键列顺序？

#1 楼