蓝海豹

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
SQL Server索引进阶第十四篇：索引统计

  索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”
    本系列文章来自Stairway to SQL Server Indexes，然后经过我们团队的理解和整理发布在agilesharp，希望对广大的技术朋友在如何使用索引上有所帮助。

系列文章索目录：

SQL Server索引进阶第一篇：索引介绍
SQL Server索引进阶第二篇：深入非聚集索引
SQL Server索引进阶第三篇：聚集索引
SQL Server索引进阶第四篇：页和区

SQL Server索引进阶第五篇：索引包含列
SQL Server索引进阶第六篇：书签
SQL Server索引进阶第七篇：过滤的索引

SQL Server索引进阶第八篇：唯一索引
SQL Server索引进阶第九篇：解读执行计划
SQL Server索引进阶第十篇：索引的内部结构
SQL Server索引进阶第十一篇：索引碎片分析与解决（上）
SQL Server索引进阶第十一篇：索引碎片分析与解决（中）-碎片发生原理深度剖析
SQL Server索引进阶第十二篇：索引的创建，修改和删除
SQL Server索引进阶第十三篇：Insert,Update,Delete语句
SQL Server索引进阶第十四篇：索引统计
SQL Server索引进阶第十五篇：索引的最佳实践



    在第十篇文章中我们详述了为什么索引需要叶子节点和非叶子节点，我原文是”然而,SQL Server并不知道什么是按字母表排序”，换句话说，SQL Server并不知道“Meyer, Helen”这个条目大概在索引的中间位置。

    但是SQL Server需要知道有关索引中数据分布的情况；正如我们所知，查询的选择性是决定是否使用特定索引的关键。SQL Server存储索引键中的部分值，这部分值就是所谓的索引统计信息，也可以直接叫做统计信息，统计信息是本篇文章要涵盖的主题。
    实际上，统计信息不仅仅是索引列值的采样，统计信息也会采样非索引列，这部分信息的结构和采样索引列的结构是一致的，但由于本系列文章是关于索引的，所以这里仅对统计列进行讨论。
    索引统计有点像汽车中的引擎。当然你知道引擎的原理那是再好不过的，但如果你不懂引擎的原理，那依然需要定期维护引擎。本篇文章讲述索引统计的原理和维护这两方面。

索引统计的结构
    索引统计信息分为三部分:索引头信息，密度信息和数据分布信息，如果想查看这三部分信息，则运行:

  1. DBCC SHOW_STATISTICS (tablename, indexname)
复制代码

而如果要分别查看这三个部分中某一个部分的信息，则运行:

  1. DBCC SHOW_STATISTICS (tablename, indexname) WITH STAT_HEADER
复制代码

或是:

  1. DBCC SHOW_STATISTICS (tablename, indexname) WITH DENSITY_VECTOR
复制代码

或是:

  1. DBCC SHOW_STATISTICS (tablename, indexname) WITH HISTOGRAM
复制代码

在我们开始查看统计信息之前，首先我们来建立一个表和索引来作为示例进行演示。我们的示例表由3列和两个索引组成。两个索引分别是表的前两列和第三列。代码1所示。

  1. USE AdventureWorks;
  3. GO
  7. IF EXISTS (SELECT *
  9. FROM sys.objects
  11. WHERE name = 'HistogramTest' AND type = 'U')
  13. BEGIN
  15. DROP TABLE dbo.HistogramTest
  17. END
  19. GO
  21. CREATE TABLE dbo.HistogramTest
  23. (
  25. Col1 int not null
  27. , Col2 int not null
  29. , Col3 int not null
  31. )
  33. GO
  37. CREATE INDEX IX_HistogramTest
  39. ON dbo.HistogramTest
  41. ( Col1, Col2 )
  43. CREATE INDEX IX_SingleValue
  45. ON dbo.HistogramTest
  47. ( Col3 )
  49. GO
复制代码

代码1.有两个索引的示例表

    表建好之后，让我们来插入一些测试数据，如代码2所示。

  1. SET NOCOUNT ON;
  3. GO
  5. DECLARE @maxLeftColumnValue int = 100;
  7. DECLARE @leftColumnValue int = 0;
  9. DECLARE @middleColumnValue int = 0;
  11. DECLARE @rightColumnValue int = 0;
  13. WHILE @leftColumnValue < @maxLeftColumnValue
  15. BEGIN
  17. SET @leftColumnValue += 1
  19. SET @middleColumnValue = 0
  21. WHILE @middleColumnValue < @leftColumnValue
  23. BEGIN
  25. SET @middleColumnValue += 1
  27. SET @rightColumnValue += 1
  29. INSERT dbo.HistogramTest VALUES ( @leftColumnValue
  31. , @middleColumnValue
  33. , @rightColumnValue )
  35. END
  37. END
  39. GO
复制代码

代码2.插入一些测试数据

    测试数据特点如下:



  • Col1中的数据相同值的行数和其值相同，比如值为1的有1行，值为2的有2行，依此类推
  • 但对于行来说，Col1和Col2作为组合列值是唯一的。所以建立在其上的IX_HistogramTest索引可以看所是唯一索引
  • Col3中的值是唯一的，从1开始不断递增，因此IX_SingleValue也可以看作是唯一索引

    为了演示的准确性，上面的顺序是先创建表，再插入测试数据，然后再创建索引，这样就能保证索引的统计信息是最新的。
    完成上面的步骤之后，运行代码3中所示的查询。可以看到结果和我们上面所说的数据特点一致。
    因为这部分数据是按照Col1/Col2排序的，因此可以看所是IX_Histogram索引的部分数据。

  1. SELECT TOP 20 Col1, Col2, Col3
  3. FROM dbo.HistogramTest
  5. ORDER BY Col1, Col2;
复制代码

代码3.查询语句


图1.查询结果

密度
    另一个有关索引的术语就是密度。密度是衡量索引中数据重复程度的一个术语，和我们之前文章所谓的选择性关系紧密。比如说密度是0.01代表着索引某列中有1/0.01=100个不相同的值。换句话说，平均每一个值出现的概率是1%。密度可以衡量单一列的密度，也可以用于衡量组合列的密度，比如前面例子中的Col1-Col2组合列。
    如果图1的查询结果是整个表，则Col1的密度会是1/6=0.1667,而col3的密度是1/20=0.05
    密度越小，则查询中比较谓词所能匹配的数据越少。则选择性越高。比如Col3的选择性就比Col1高。

统计信息头
执行下面语句:

  1. DBCC SHOW_STATISTICS('dbo.HistogramTest', 'IX_HistogramTest')
  2. WITH STAT_HEADER
复制代码

我们可以看到如图2所示的统计头信息:


图2.统计信息头

头部信息涵盖了如下内容:
Name:索引信息名称。
Updated:统计信息的更新时间。
Rows:索引中包含的条目数。
Steps:步长，代表了数据分布信息的份数。
Density:这个值在SQL Server 2008之后被淘汰。
Average Key Length:平均键的长度.
String Index：用于Like匹配时估计行数
Filter Expression:过滤索引表达式
Unfiltered Rows:索引所在表的条目数，如果是过滤索引，这个值通常更大。

统计信息密度
执行下面语句:

  1. DBCC SHOW_STATISTICS('dbo.HistogramTest', 'IX_HistogramTest')
  2. WITH DENSITY_VECTOR
复制代码

我们可以看到如图3所示的结果:


图3.密度信息

通过图3的密度信息可以看出：
Col1平均的键大小是4字节，包含1/0.01=100个不同的值。
Col1-Col2组合键值的平均大小是8字节，包含1/0.0001980198=5050个不同的值，因为这两列组合起来是唯一的，所以这个5050等于表中的行数。

统计数据分布直方图
执行下面代码后:

  1. DBCC SHOW_STATISTICS('dbo.HistogramTest', 'IX_HistogramTest')
  2. WITH HISTOGRAM
复制代码

我们可以看到如图4所示的信息，我们截取59个步长中的25个，显示在下面：


图4.统计分布直方图的前25个步长信息

数据分布直方图更像是一个5列的表。每一行所存储的信息都可以称为步长。所有的步长信息都取自索引键最左边列的信息。对于我们之前创建的IX_HistogramTest索引，只有COL1的值被采样，其它列在生成数据分布信息的过程中直接被无视。
每一个步长都是统计分布的一个范围，换句话说是一部分连续索引条目的集合。无论表有多大，数据分布直方图不会超过200个步长。每个步长都包含不同大小的索引条目，比如说当前步长包含30个索引条目，下一个步长包含了46个索引条目。
为了更好的理解数据分布直方图，我们定义如下4个术语，并在图5中进行详细阐述。



  • 最索引的最左列是索引键中出现在第一个位置的列，比如我们的例子中，Col1就是左列
  • 步长集就是索引中连续条目的集合.图4中这5列值都是根据索引键中的最左列决定的
  • 步长中的Range_High_key是在步长中最左列的最大值
  • 步长中的更小子集是那些小于步长中最左列最大值的集合

比如说，我们通过图4中部分统计分布图来看上面的概念，如图5所示。


图5.索引中的步长集

    因为上面的例子最左列只有100个不同的值，所以每一个步长仅仅包含2个值或是更少，一会在本文的后续部分，我们用一个更大的例子来展示包含多个值的步长。
图4中的5列的统计分布直方图中包含的值计算方法如下:

RANGE_HI_KEY:
    步长集合中的最左列的最大值。RANGE_HI_KEY是步长的边界，换句话说，不同的步长不和能有相同的RANGE_HI_KEY。
    对于第一个步长来说，RANGE_HI_KEY是最左列最小值,因此第一个步长没有低子集.对于最后一个步长来说，RANGE_HI_KEY是索引最左列的最大值。
    步长按照RANGE_HI_KEY顺序维护和显示。
    我们的例子中，第一个RANGE_HI_KEY的值是1，也就是Col1的最小值。最后一个RANGE_HI_KEY的值是100，也就是Col1的最大值。59个步长中没有一个相同的RANGE_HI_KEY值。
    因为RANGE_HI_KEY是区分步长的一句，因此这个值也可以唯一确定步长，因此步长的RANGE_HI_KEY=20的话，无论这个步长是该索引的第几个步长，我们都可以其为步长20。

EQ_ROWS:

    最左列值等于RANGE_HI_KEY的行数，也就是步长的大子集(Upper subset)的个数。
    在我们的例子中，步长20中有20个等于该步长RANGE_HI_KEY值的条目。这个值和测试数据生成规则推算出来的值一样。

RANGE_ROWS:
    步长小子集（lower subset）中条目的个数
    我们的例子中，步长20代表着最左列的值从18到20（18<步长<=20），其中值为19的就是小子集（lower subset）,值等于19的条目数有19个。

DISTINCT_RANGE_ROWS:
    步长小子集（lower subset）中唯一值的个数。
    我们的例子中，步长20代表着最左列的值从18到20（18<步长<=20），其中值为19的就是小子集（lower subset）,这个值只有1个。

AVG_RANGE_ROWS
     最左值的最小集合中每个唯一值条目个数的平均数，可以使用公式RANGE_ROWS / DISTINCT_RANGE_ROWS进行计算，如果RANGE_ROWS等于0，那么这个值就是1。因为如果RANGE_ROWS等于0的话，这列值毫无意义。

一个大一点的例子
    前面讨论的例子数据量比较小。这也使得我们可以更简单的知道上面所说的各类值的意义。但是对于SQL Server来说，大表比小表更需要统计信息来做正确的查询计划选择。因此，我们将上面的数据量扩大100倍。
    简而言之，我们将生成测试数据的变量值进行修改。
由：

  1. DECLARE @maxLeftColumnValue int = 100;
复制代码

变为:

  1. DECLARE @maxLeftColumnValue int = 1000;
复制代码

我们重新创建表，插入测试数据，并重建了索引。这时表中的数据就编程了505000。
再次运行:

  1. SHOW_STATISTICS('dbo.HistogramTest', 'IX_HistogramTest')
复制代码

可以看到如图6锁示的结果，共有199个步长。


图6.大量数据例子的索引统计

图6中的第二个步长，最左列的值为46，意味着大子集(Upper subset)包含46行（每行的最左列的值都是46），这点可以根据我们前面数据生成算法推出。在小子集(Lower subset)包含1034个条目(2+3+4+44+45),这些数据共有44个不同值(从2到45)。则平均每一个不同最左列的值有23.5行。
所以，当SQL Server解析下面这句时:
WHERE Col1 = 46
SQL Server就会知道500500行中有46行满足这个where条件。
而如果谓语是:
WHERE Col1 = 45
因为这里行不在RANGE_HI_KEY中，所以SQL Server就采用平均值，也就是23.5行。
即使遇到如下谓语:
WHERE Col1 BETWEEN 2 AND 24
SQL Server也能知道将会有517行（大概上面的小子集(Lower subset)中一半的行）。

唯一索引和统计信息
    我们前面所创建的两个索引IX_HistogramTest和IX_SingleValue都符合建立唯一索引的条件，在实际生产环境中它们也应该是被设置成唯一索引。虽然SQL Server可以从统计信息中得知这两个索引中键值是唯一的，但是这是无法保证的。所以仅仅靠索引统计信息是无法保证SQL Server在生成执行计划的过程中确定索引中键值的唯一性。在本系列的第八篇关于唯一索引的文章中，我们已经知道SQL Server是如何利用唯一索引来生成更好的执行计划的，但仅靠索引统计信息不行。
    同样，如果只存在唯一索引而没有统计信息,SQL Server则仅仅知道值是唯一的而不知道值到底是什么。因此为了知道索引中值的范围，SQL Server首先要查看统计信息，比如我们之前创建的IX_SingleValues索引，SQL Server首先要知道下面的信息:

  1. DBCC SHOW_STATISTICS('dbo.HistogramTest', 'IX_SingleValue')
复制代码

信息的结果如图7所示。


图7.SQL Server对于单列唯一索引的统计信息

SQL Server需要在查询过程中得知如下两部分信息:



  • 索引包含500500个条目
  • 这些条目由500500(1+500548+1)个不同值组成，从1到500500

得知了这些信息之后，遇到WHERE子句中不管是=,<,>或是between,SQL Server都可以根据上面的信息快速估计返回的行数。
字符串统计信息
    字符串类型（包括Char,varchar,nchar,nvarchar和ntext类型）和数字类型不同之处在于字符串类型是可以分割的。比如substring函数或是like操作需要SQL Server去匹配字符串的子集。因此，SQL Server需要一些统计信息来得知特定字符串子集的值出现的概率。这就是所谓的字符串统计信息。这个统计信息是在SQL Server 2008之后才有的。
    如果统计头中StringIndex列的值是Yes,则SQL Server为最左列的值生成字符串统计信息，如果最左列的字符串统计信息超过80字节，则只有开始40字节和结束40字节被用来生成统计信息。
    执行DBCC SHOW_STATISTICS是无法找到关于字符串统计的细节的，SQL SERVER也没有其它方式可以展现字符串统计信息。

索引统计信息的维护
    统计信息的用处是巨大的。但如果统计信息过时了，也就是反映不出表中的数据分布了则就没那么拥有了。统计信息过时的原因是SQL Server并不会每次在表中插入和删除信息时就同时更新统计信息。如果更确切的说，SQL Server不会更新统计信息，而是重新生成统计信息。但我们也不希望SQL Server重新生成统计信息的次数超过必须的次数。
    因此我们需要知道什么时候需要重新生成统计信息。

何时更新统计信息
    所幸，SQL Server允许你控制何时重新生成统计信息。
    数据库有一个选项叫做AUTO_UPDATE_STATISTIC,这个选项默认是ON。可以通过ALTER DATABASE语句进行设置。当这个选项设置为ON的时候,表中一定量的数据改变就会引起SQL Server重新生成执行计划，这个“一定量”是由SQL Server控制的，也就是不能通过选项进行控制。
    每当创建或修改索引时，都可以通过STATISTICS_NORECOMPUTE选项来控制索引的统计信息是否会被自动更新。

    如果你想手动更新统计信息，使用下面语句:
  1. UPDATE STATISTICS SchemaName.TableName
复制代码
上面的语句会更新与表相关的所有统计信息，而如果你指向更新特定索引的话，请使用:
  1. UPDATE STATISTICS SchemaName.TableName IndexName
复制代码
除了这种方法之外，sp_updatestats存储过程也可以用来更新统计信息。

通常来说，AUTO_UPDATE_STATISTICS保持默认是不错的选择。不仅可以省去麻烦，还能让SQL Server来管理索引统计信息的生成。
但有些时候你还是想手动更新统计信息，比如下面情况:



  • 你们公司有定期的维护窗口时间，这个时间内你想手动更新统计信息
  • 存在一个很大的表，自动更新统计信息需要达到的阈值太大
  • 索引中含有多个唯一键值，这需要很大的样本数据才能生成有意义的统计信息。默认的样本百分比打不到这个标准
  • 统计信息滞后与表中数据的改变

为了演示上面最后一种情况，并且更好的理解为什么有些时候不用自动更新统计信息。请看代码4中创建的表，这个表中存储借出图书的信息。

  1. CREATE TABLE dbo.Loan
  2. (
  3. MemberNo int not null
  4. , ISBN SysName not null
  5. , DateOut Date not null
  6. , DateDue Date not null
  7. )
  8. GO
复制代码

代码4.Load表

    上面4列分别代表:



  • 借书人的会员ID
  • ISBN号
  • 借出日期
  • 还书日期


    因为所有借出去的书需要在14天内还回来，所以14天内数据是不会被删除的。这里书借出去就插入一条记录，书被换回来则把借书记录删除。在两周内，通常ISBN号的分布几乎不会有太大变化。但还书日期值的分布却有很大变化。
    ISBN号的变化不大是因为很少有哪本书这周不流行，但下周突然流行。因此，ISBN号的数据分布本月和上月一般没有什么太大变化。单个ISBN号肯定有变化，但总体的数据样本不会有太大变化。
    但还书日期列可就不同了,因为这个日期的范围是当前日期+14天，所以数据分布随时会变，并且这列的值不会和过去的值重叠。因此DueDate列的索引需要比ISBN列的索引更新的更加频繁。

最小化更新统计信息带来的影响
    SQL Server还可以允许用户控制更新统计信息时读取索引的数量或百分比。



  • 当索引创建完成后，必须读取所有的索引条目，因此统计信息需要读取所有的索引条目才能生成样本
  • 当更新索引时，如果还需要采样索引的所有条目就有点小题大做了。这会消耗大量的IO，一般这时SQL SERVER只会采样索引的一部分。
  • 默认情况下SQL Server控制采样的大小，但是你也可以设置这个大小。当更新索引时，你可以指定SAMPLE N PERCENT或是SAMPLE n ROWS亦或是FULLSCAN选项。当你指定了SAMPLE N PERCENT或是SAMPPLE N ROWS,这个效果会立刻在接下来的更新操作和以后的自动更新操作中生效。

    为了阐述不是默认全表采样的部分采样过程，我们重新创建了前面的那个500500行的大表，但这次，我们在创建完之后，分别执行:

  1. UPDATE STATISTICS dbo.HistogramTest;
  2. DBCC SHOW_STATISTICS('dbo.HistogramTest', 'IX_HistogramTest');
复制代码

我们看到如图8所示的结果。


图8.采样的统计信息

    虽然图6和图8中的信息貌似相同，但细节上还是不同的。RANGE_ROWS中的实际实际行数被FLOAT类型的估计行数所替代。

总结
    索引统计帮助SQL Sever优化查询。
    索引创建和重建时统计信息会自动生成。
    索引统计信息分为三部分，可以通过DBCC指定进行查看。
    索引统计可以过时，过时的统计信息会导致不精确的查询计划。
    默认情况下，索引统计信息会自动更新，你也可以不允许自动更新，也可以手动更新统计信息。
    通常来说更新统计信息并不需要读取索引的所有叶子节点，而是采样部分索引。你可以通过参数来控制采样数据的百分比。

 

posted on 2012-12-03 11:45  蓝海豹  阅读(471)  评论(0)  编辑  收藏  举报