-
sql语句大全之覆盖索引 临界点
时间过得真快——再过几分钟,你就要完成第2个月的性能调优培训。今天这部分培训我想讲下非聚集索引的更多信息,还有你会碰到它的一些负作用。
上一星期我们讨论了SQL Server里的书签查找,它是非常危险的。在执行计划里SQL Server访问非聚集索引时,额外列必须要从表本身获取时(因为它们不是非聚集索引的一部分),书签查找会发生。如果你想避免书签查找,你可以在SQL Server里定义覆盖索引(Covering Index ) 。我们来看下。
覆盖索引(Covering Index)
在SQL Server里覆盖索引是传统的非聚集索引。唯一的区别是覆盖非聚集索引可以包含给出查询所有需要的列。这就是说使用覆盖索引可以避免书签查找。我们来看一个非常简单的例子。下列的查询会产生书签查找,因为PostalCode列不是非聚集索引IX_Address_StateProvinceID 的一部分,在执行计划里,这个非聚集索引已被使用。
1 SELECT 2 AddressID, 3 PostalCode 4 FROM Person.Address 5 WHERE StateProvinceID = 42 6 GO
这个查询本身产生18个逻辑读。你可以通过定义覆盖非聚集索引,拿掉这个查询的书签查找。就是说,我们需要包含PostalCode 列,在非聚集索引的叶子层。
1 CREATE NONCLUSTERED INDEX idxAddress_StateProvinceID ON 2 Person.Address (StateProvinceID) 3 INCLUDE (PostalCode) 4 GO
当你再次执行这个查询时,从执行计划里你可以看到书签查找已经不见了,SQL Server使用索引查找(非聚集索引)运算符。逻辑读减少为2个。非常显著的性能提升!
唯一你要知道的是,并不是每个书签查找都是非常危险的。我们的目标不是移除每个书签查找,只有坏的才移除。
临界点(Tipping Point)
在一些情况下,当SQL Server对指定查询进行书签查找操作时,它可以决定书签查找太耗资源了(根据必须的逻辑读)。在那个情况下,SQL Server会进行全表扫描,而忽略所有的非合格列。做出这个决定点位置,在SQL Server里被称为临界点(Tipping Point)。临界点就是SQL Server用来决定是进行书签查找还是全表扫描。
临界点躲在你查询需要读取页数的1/4到1/4的某个位置。这和你需要读取的记录数无关(因为记录的大小决定了1页里你可以存放多少记录)。对于这个非常简单的例子,我定义的表里每条记录长度是400 bytes长,这就是在8k的页里可以存放20条记录。另外我在Value列定义了一个非聚集索引。下面的查询使用书签查找返回1061条记录。
1 SELECT * FROM Customers 2 WHERE Value < 1062 3 GO
如果获取更多一条记录,作为特殊情况的下面查询就会临界点上,然后SQL Server就会扫描整个表。
1 SELECT * FROM Customers 2 WHERE Value < 1063 3 GO
2个近乎一样的查询,却有完全不同的执行计划!这在某些情况下会是个巨大的问题,因为你的计划稳定性不再。过去几年我与很多不同客户打交道时,因为这个问题,它们的SQL Server近乎发疯。SQL Server临界点游戏——为什么非聚集索引被忽略!
小结
在这一部分的性能调优培训里,你学习了SQL Server里的覆盖非聚集索引还有临界点。在你学习的4个星期里,索引在SQL Server里可以说是个很神奇的东西!
每个索引在提高你读性能的同时,也会降低你的写性能。在你执行INSERT, UPDATE和DELETE语句时, 每个索引都由SQL Server全权负责维护。因此,你要基于读需求和写工作量来平衡你的索引策略。
接下来的4个星期,我们会聚焦更多SQL Server里的执行计划,你会学到如何读懂和理解执行计划,还有它们如何用来做性能调优。请继续关注,下周见!