怎样调优SQL Server查询
在今天的文章里,我想给你展现下,当你想对特定查询创建索引设计时,如何把你的工作和思考进程转达给查询优化器。下面就一起来探讨一下吧!
有问题的查询
我们来看以下查询:
DECLARE @i INT = 999
SELECT
SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
LineTotal
FROM Sales.SalesOrderDetail
WHERE ProductID < @i
ORDER BY CarrierTrackingNumber
GO
如你所见,这里用了一个本地变量与一个不等于谓语来从Sales.SalesOrderDetail表来获得一些记录。当你履行那个查询,看它的履行计划时,你会发现它有一些严重的问题:
- SQL Server需要扫描Sales.SalesOrderDetail表的全部非聚集索引,由于没有支持的非聚集索引。对这个扫描,查询需要1382个逻辑读,运行时间近800毫秒。
- 查询优化器在查询计划里引入了挑选器(Filter)运算符,它进行逐行比较用来检查符合的行(ProductID < @i)
- 由于ORDER BY CarrierTrackingNumber,在履行计划里一个排序(Sort)运算符被引入。
- 排序运算符蔓延到了TempDb,由于不正确的基数计算(Cardinality Estimation)。用了带了本地变量与不等于谓语的组合,SQL Server从表的基数硬码估计30%的行。在我们的情况里估计行数是36395(121317 * 30%)。实际上查询返回120621行,这意味这排序(Sort)运算符一定要蔓延到TempDb,由于要求的内存授与太小了。
现在我问你——你能改良这个查询么?你的建议是甚么?休息下,想个几分钟。不修改查询本身,你怎么改良这个查询?
我们来调试查询!
固然,我们要做索引相关的调剂来改良。没有支持的非聚集索引,那只能是查询优化器唯一可使用计划来运行我们的查询。但对这个指定查询,甚么是好的非聚集索引呢?一般来讲,我通过看搜索谓语来斟酌可能的非聚集速印。在我们的例子里,搜索谓语以下:
WHERE ProductID < @i
我们要求在ProductID列过滤的行。因此我们想在那个列创建支持的非聚集索引。我们建立索引:
CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
GO
在非聚集索引创建后,我们需要验证下改变,因此我们再次履行刚才的查询代码。结果如何捏?查询优化器并没有使用我们刚创建的非聚集索引!我们在搜索谓语上创建了支持的非聚集索引,查询优化器没有援用它?通常人们对此就无辙了。其实我们可以提示查询优化器来使用非聚集索引,来更好的理解“为何”查询优化器没有自动选择索引:
DECLARE @i INT = 999
SELECT
SalesOrderID,
SalesOrderDetailID,
CarrierTrackingNumber,
OrderQty,
LineTotal
FROM Sales.SalesOrderDetail WITH (INDEX(idx_Test))
WHERE ProductID < @i
ORDER BY CarrierTrackingNumber
GO
当你现在看履行计划时,你会看到以下的野性——一个并行计划:
查询花费了370109个逻辑读!运行时间基本和刚才的一样。这里到底产生了甚么?当你仔细看履行计划,你会发现查询优化器引入了书签查找,由于刚才创建的非聚集索引,对查询来讲,不是一个覆盖非聚集索引。查询超出了所谓的临界点(Tipping Point),由于我们用当前的搜索谓语来取得几近所有行。因此用非聚集索引和书签查找来组合没成心义。
不去想为何查询优化器不选择刚才创建的非聚集索引,我们已把自己的思路表达给了查询优化器本身,通过查询提示进行了询问了查询优化器,为何非聚集索引没被自动选择。如我刚开始说的:我不想斟酌太多。
使用非聚集索引解决这个问题,在非聚集索引的叶子层,我们一定要对从SELECT列表的要求的额外列进行包括。你可以再次看下书签查找来看下在叶子层哪些列当前丢失:
- CarrierTrackingNumber
- OrderQty
- UnitPrice
- UnitDiscountPrice
我们重建那个非聚集索引:
CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)
INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount)
WITH
(
DROP_EXISTING = ON
)
GO
我们已做出了另1个改变,因此我们可以重新运行了查询来验证下。但是这次我们不加查询提示,由于现在查询优化器会自动选择非聚集索引。结果如何捏?当你看履行计划时,索引现在已被选择。
SQL Server现在在非聚集索引上进行了查找操作,但在履行计划里我们还有排序(Sort)运算符。由于基数计算30%的硬编码,排序(Sort)或者要蔓延到TempDb。偶滴神!我们的逻辑读已降到了757,但运行时间或者近800毫秒。你现在应当怎样做?
现在我们可以尝试在非聚集索引的导航结构直接包括CarrierTrackingNumber列。这是SQL Server进行排序运算符的列。当我们在非聚集索引直接加了这列(作为主键),我们就物理排序了那列,因此排序(Sort)运算符应当会消失。作为积极的副作用,也不会蔓延到TempDb。在履行计划里,现在也没有运算符关心毛病的基数计算。因此我们尝试那个假定,再次重建非聚集索引:
CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID)
INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)
WITH
(
DROP_EXISTING = ON
)
GO
从索引定义可以看到,现在我们已对CarrierTrackingNumber和ProductID列的数据物理预排序。当你再次重新履行查询,在你查看履行计划时,你会看到排序(Sort)运算符已消失,SQL Server扫描了非聚集索引的全部叶子层(使用剩余谓语(residual predicate)作为搜索谓语)。
这个履行计划其实不坏!我们只需要763个逻辑读,现在的运行时间已降至600毫秒。和刚才的相比已有25%的改良!但是:查询优化器建议我们一个更好的非聚集索引,通过缺少索引建议(Missing Index Recommendations)!暂且相信下,我们创建建议的非聚集索引:
CREATE NONCLUSTERED INDEX [SQL Server doesn’t care about names, why I should care about names?]
ON [Sales].[SalesOrderDetail] ([ProductID])
INCLUDE ([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[LineTotal])
GO
当你现在重新履行最初的查询,你会发现使人惊讶的事情:查询优化器使用“我们”刚才创建的非聚集索引,缺少索引建议已消失!
你刚刚创建了SQL Server从不使用的索引——除INSERT,UPDATE和DELETE语句,SQL Server都要去保护你的非聚集索引。对你的数据库,你刚创建了“单纯”浪费空间的索引。当另外一方面,你已通过消除丢失索引建议,满足了查询优化器。但这不是目的:目的是创建会被再次使用的索引。
结论:永不相信查询优化器!
小结
今天的文章有点争议性,但我想你向你展现下,但你在创建索引时,查询优化器如何帮助你,还有查询优化器如何愚弄你。因此做出小的调剂,就立即运行你的查询,验证改变非常重要。
本篇文章到此结束,如果您有相关技术方面疑问可以联系我们技术人员远程解决,感谢大家支持本站!
文章来源:丸子建站
文章标题:怎样调优SQL Server查询
https://www.wanzijz.com/view/58435.html