数据库索引对于提升查询性能至关重要,但有时由于不当的实践,索引并未得到有效利用。有时即使创建了适当的索引,SQL服务器也无法在期望的列上使用索引查找。这时索引就被忽略了。理解SARGable表达式的概念可以提升查询性能。SARG是“Search ARGumentable”的缩写。根据维基百科的定义,SARGable是指在关系数据库中,如果数据库管理系统引擎能够利用索引来加速查询的执行,那么查询中的一个条件(或谓词)就被称为SARGable。这个术语来源于“Search ARGument ABLE”的缩写。
如果查询能够充分利用可用的索引,那么这个查询就被称为SARGable查询。在这种情况下,查询使用索引查找操作。
索引查找:SQL服务器直接使用索引的B树结构来获取匹配的记录。它速度快,是处理大量数据表的首选。
索引扫描:SQL服务器扫描/读取表中的所有记录以返回所需的行。这速度慢。但对于需要获取所有记录的小数据量表,这个过程是可以接受的。
通常,当在WHERE子句中包含一个函数/操作时,查询就变成了非SARGable的。一些通常(但并非总是)阻止查询优化器使用有用索引进行搜索的非SARGable搜索参数包括“IS NULL”,“<>”,“!=”,“!>”,“!<”,“NOT”,“NOT EXISTS”,“NOT IN”,“NOT LIKE”,以及“LIKE ‘%test’”。
完成查询后,总是检查查询的执行计划,以查看查询是否使用了可用的索引。
让创建一个测试表来演示SARGable查询。
CREATE TABLE SargableDemo (
[ID] INT IDENTITY(1,1) NOT NULL,
[DealerName] NVARCHAR(100) NULL,
[OrderID] INT NOT NULL,
[Date] Datetime not null
);
让向表中插入几行数据。
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('Toyata', 11, GETDATE());
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('Toyata', 12, DATEADD(YEAR, 1, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('Maruti', 13, DATEADD(DAY, 1, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('Ford', 1, DATEADD(YEAR, 2, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('Toyata', 21, DATEADD(DAY, 21, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('BMW', 41, DATEADD(DAY, 4, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('Toyata', 51, DATEADD(YEAR, 5, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('FORD', 71, DATEADD(DAY, 7, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('Toyata', 81, DATEADD(DAY, 9, GETDATE()));
INSERT INTO SargableDemo(DealerName, OrderID, [Date]) VALUES ('BMW', 91, DATEADD(YEAR, 2, GETDATE()));
通配符比较:让在DealerName列上创建一个索引。
CREATE NONCLUSTERED INDEX IX_SargableDemo_DealerName ON SargableDemo(DealerName);
GO
想要找到所有名为“Toyota”的经销商。以下是非SARGable和SARGable的查询方式。
非SARGable:
SELECT DealerName FROM SargableDemo WHERE DealerName Like '%Toyota'
执行计划在这里。这里忽略了DealerName列上的索引,导致索引扫描。
SARGable:
SELECT DealerName FROM SargableDemo WHERE DealerName Like 'Toyota%'
执行计划在这里。这里使用了DealerName列上的索引,导致索引查找。
算术运算符:让用算术运算符做一个演示。
让在OrderID列上创建一个索引。
CREATE NONCLUSTERED INDEX IX_SargableDemo_OrderID ON SargableDemo(OrderID);
GO
这里将在WHERE子句中包含一个算术运算符。以下是非SARGable和SARGable的查询方式。
非SARGable:
SELECT OrderID FROM SargableDemo WHERE OrderID * 3 = 33000
执行计划在这里。这里忽略了OrderID列上的索引,导致索引扫描。
SARGable:
SELECT OrderID FROM SargableDemo WHERE OrderID = 33000/3
执行计划在这里。这里使用了OrderID列上的索引,导致索引查找。
类似地,使用ABS()函数会使查询非SARGable。
标量函数:让用YEAR()函数做一个演示。
让在Date列上创建一个索引。
CREATE NONCLUSTERED INDEX IX_SargableDemo_Date ON SargableDemo([Date]);
GO
想要找到所有在特定年份下的记录。以下是非SARGable和SARGable的查询方式。
非SARGable:
SELECT [Date] FROM SargableDemo WHERE YEAR([Date]) = 2014
执行计划在这里。这里忽略了Date列上的索引,导致索引扫描。
SARGable:
SELECT [Date] FROM SargableDemo WHERE [Date] >= '01-01-2014' AND [Date] < '01-01-2015'
执行计划在这里。这里使用了Date列上的索引,导致索引查找。
这里有一些更多的例子:
非SARGable:
SELECT ...
WHERE isNull(FullName, 'Jitendra') = 'Jitendra'
SARGable:
SELECT ...
WHERE ((FullName = 'Jitendra') OR (FullName IS NULL))
非SARGable:
SELECT ...
WHERE SUBSTRING(FullName, 4) = 'Jite'
SARGable:
SELECT ...
WHERE FullName LIKE 'Jite%'
非SARGable:
SELECT DealerName FROM SargableDemo WHERE UPPER(DealerName) LIKE 'FORD'
SQL服务器默认是不区分大小写的,所以在这里使用UPPER()和LOWER()函数是不好的。
SARGable:
SELECT DealerName FROM SargableDemo WHERE DealerName LIKE 'FORD'
要点:
通过编写非SARGable函数/操作的逆函数/操作,在WHERE条件中,可以提高查询的性能。为了避免索引扫描并提高查询性能,请尝试使WHERE子句成为SARGable。在实施上述提示后,检查查询的执行计划,以充分利用索引。