[SQL]SQL Server 2005中如何提升记录总数统计的性能

baacloud免费翻墙vpn注册使用

  当我们想统计数据表的记录总数时,我们使用的T-SQL函数count(*) 。如果在一个包含了数百万行的大表中执行这个函数的话,,可以要花很长时间才能返回整个表的记录总数,这导致了查询性能的下降。

  一、常规办法:采用Count ()函数

  每个数据库管理员知道如何使用count(*) 函数。SQL Server在执行这个函数时,为了返回总表的行计数,需要对索引/表进行完整的扫描。因此建议DBA们尽量避免针对整个表使用聚合函数count(*),因为它影响了数据库的性能。

  下面我们来看个AdventureWorks数据库中的例子。

  在查询分析器中执行下面的查询语句:

  useAdventureWorks
  go
  selectcount(*)fromSales.SalesOrderDetail

  查询分析器执行后,显示有121317行。

  当我们点击SQL Server 2005 Management Studio的工具栏上的“显示估计的执行计划”图标时,我们可以得到以下的图表: 

SQL Server 2005中如何提升记录总数统计的性能

  图1:count(*)函数的执行计划

  从右到左来看,我们可以了解到SQL语句的执行过程:

  l 第一步中对整个表进行索引扫描,这是个非常耗时的过程(占81%)。

  l 第二步中应用流聚合也较耗时(占19%)。

  二、新方法:采用 row_count ()自定义函数

  在SQL Server 2005中的两个对象目录视图:sys.partitions和sys.allocation_units包含了这些信息,可以用来获取整个表的总记录数。

  sys.partitions 目录视图

  sys.partitions视图包含了数据库中每个连续分区的所有表和索引。SQL Server 2005中的所有表格和索引,即使他们没有明确划分,也包含这个视图的至少一个分区中。

  数据库中所有表和索引的每个分区在表中各对应一行。即使 SQL Server 2005 中的所有表和索引并未显式分区,也认为它们至少包含一个分区。

  在新的实现方法中,我们用到了sys. partitions 视图中的以下字段:

  

  字段名称

  数据类型

  描述

  partition_id

  bigint

  分区的 ID。在数据库中是唯一的。

  object_id

  int

  此分区所属的对象的 ID。每个表或视图都至少包含一个分区。

  index_id

  Int

  此分区所属的对象内的索引的 ID。

  0表示是堆表中

  1表示是集群表

  rows  

  bigint

  此分区中的大约行数。

sys.allocation_units 目录视图

  sys.allocation_units视图包含了数据库中一个连续为每个分配单元。

  数据库中的每个分配单元都在表中占一行。

  在新的实现方法中,我们用到了sys.allocation_units视图中的以下字段:

  

  字段名称

  数据类型

  描述

  container_id

  bigint

  与分配单元关联的存储容器的 ID。

  如果 type = 1 或 3,则 container_id = sys.partitions.hobt_id。

  如果 type 为 2,则 container_id = sys.partitions.partition_id。

  0 = 标记为要延迟删除的分配单元

  Type

  tinyint

  分配单元的类型。

  0 = 已删除

  1 = 行内数据(所有数据类型,但 LOB 数据类型除外)

  2 = 大型对象 (LOB) 数据(text、ntext、 image、xml、大型值类型以及 CLR 用户定义类型)

  3 = 行溢出数据

在row_count这个新的用户自定义函数中,[sys.partitions]视图与[sys.allocation_units]视图相关联,并按照以下几个条件进行过滤:

  l [sys.allocation_units].type = 1,所读取的行数据,不包含text, ntext, image等类型的大型对象。

  l [sys.partitions].index_id 属于0或者1 : 0表示是堆表中,1表示是集群表。

  l [sys.partitions].row不为空。

  由于row_count这个自定义函数需要在每一个数据库中执行,因此将权限设置为Public。

  用户自定义函数row_count具体描述,见下一节。

  用户自定义函数row_count()代码

  具体如下:

  IFEXISTS(SelectnameFROMdbo.sysobjectsWhereid=Object_id(N'[dbo].[row_count]'))
  DropFUNCTION[dbo].[row_count]
  GO
  CreateFUNCTIONdbo.row_count(@table_namesysname)
  --@table_namewewanttogetcount
  RETURNSbigint
  AS
  BEGIN
  DECLARE@nnbigint--numberofrows
  IF@table_nameISNOTNULL
  BEGIN
  Select@nn=sum(p.rows)
  FROMsys.partitionsp
  LEFTJOINsys.allocation_unitsaONp.partition_id=a.container_id
  Where
  p.index_idin(0,1)
  andp.rowsisnotnull
  anda.type=1
  andp.object_id=object_id(@table_name)
  END
  RETURN(@nn)
  END
  GO

  row_count()自定义函数用法

  调用row_count自定义函数时,需要提供类似schema.[table name]格式的数据表作为输入参数。

  例1:

  useAdventureWorks
  go
  selectdbo.row_count('Sales.SalesOrderDetail')

  查询分析器返回的结果,也是121317行,与count(*)函数的执行结果相同,但速度更快,性能更佳。

  例 2:

  Selecttop5TABLE_SCHEMA,TABLE_NAME,(TABLE_SCHEMA+'.'+TABLE_NAME)'Fulltablename',dbo.row_count(TABLE_SCHEMA+'.'+TABLE_NAME)rows
  FROMINFORMATION_SCHEMA.TABLES
  whereTABLE_TYPE='BASETABLE'
  ORDERBYrowsdesc

  查询语句的执行结果见下表:  

SQL Server 2005中如何提升记录总数统计的性能

  表1 INFORMATION_SCHEMA.TABLES 表的查询结果

  这一功能主要是详细测试增加/删除行、截断表、批量插入/批量删除等操作,并返回精确统计数值。

  三、两种实现方式性能评估

  以下代码通过批处理分别执行count(*)函数和row_count用户自定义函数,来进行性能对比:

  selectdbo.row_count('Sales.SalesOrderDetail')
  go
  selectcount(*)fromSales.SalesOrderDetail
  go

  当审查执行计划时,我们可以发现第一个查询(row_count)的开销占整个批处理开销的7% ,估计子树大小的开销0.03,具体数值如下图所示: 

SQL Server 2005中如何提升记录总数统计的性能

  图2:批处理中row_count的执行计划

  第二个查询(count(*))的开销占整个批处理开销的93% ,估计子树成本是0.37,具体如下图所示:

SQL Server 2005中如何提升记录总数统计的性能

  图3:批处理中count(*)的执行计划

  用户自定义函数row_count( )的性能值是最好的,消耗数据库的资源最少。Count(*)函数与新的用户自定义函数row_count的开销比为93:7,这意味着row_count的性能是count(*)函数性能的十倍以上。

  这些对比数值,会随着表的记录数的变化而变化,但总的来说,上面的对比数据可以证明一点:新的用户自定义函数row_count()的性能,优于count(*)函数。

  如果你需要统计整个表的总记录数,采用这个用户自定义函数,可以有效提升性能。

  四、小结

  内置函数count(*)在获取整个表的记录总数时,非常消耗时间,特别是在数据表中存在数以百万计条记录时,更为明显。

  新的用户定义函数row_count,取决于对象目录视图提供准确的结果,与count(*)函数相比,速度更快,性能更佳。

  这一功能可用于在SQL Server 2005及以上版本 ,因为从SQL Server2005开始,才有这些新的视图。

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏