当前位置: 澳门新濠3559 > 数据库 > 正文

存储在不同类型的页面里,COUNT(COL)来查询一个表

时间:2019-11-07 13:43来源:数据库
  SQL Server 中数据存储的基本单位是页(Page)。磁盘I/O 操作在页级执行。 源于csdn论坛的一个提问:     在数据库中,很多人员习惯使用SELECT COUNT(*)、SELECTCOUNT(1)、SELECTCOUNT(COL)来

 

SQL Server 中数据存储的基本单位是页(Page)。磁盘I/O 操作在页级执行。

源于csdn论坛的一个提问:

    在数据库中,很多人员习惯使用SELECT COUNT(*)、SELECT COUNT(1)、SELECT COUNT(COL)来查询一个表有多少记录,对于小表,这种SQL的开销倒不是很大,但是对于大表,这种查询表记录数的做法就是一个非常消耗资源了,而且效率很差。下面介绍一下SQL Server、 Oracle、MySQL中如何快速获取表的记录数。

很多时候,我们经常使用sp_spaceused来查看表的空间使用情况,上个月群里有个网友说他使用DELETE删除了数据后,使用sp_spaceused查看,发现该表的分配的空间总量(reserved)与数据使用的空间总量(data)没有变化,当时和他讨论了并分析了一下原因,随手记录了一下这个案例,这个周末刚好有点时间,正好分析整理一下这个案例、分享在这篇文章。如下所示,我们先构造数据,我们的测试案例比较极端,刚刚保证每个页面(page)刚好存储两条记录。如下所示:

SQL Server 读取或写入数据的最小单位就是以8 KB 为单位的页。

图片 1CREATE TABLE TUser ( FName CHAR(8000), FAge INT, FSex bit )
图片 2INSERT INTO TUser
图片 3SELECT '张三',18,1 UNION ALL
图片 4SELECT '李四',20,1 UNION ALL
图片 5SELECT '王五',32,1 UNION ALL
图片 6SELECT '麻子',23,1
图片 7

 

 

 

 
通过一个查询看下扫描的数据页:

SQL SERVER 数据库

 

每页的开头是一个96 B 的页头,用于存储有关页的系统信息。包括页码、页类型、页的可用空间,以及拥有该页

图片 8SET STATISTICS IO ON
图片 9SELECT * FROM TUser
图片 10图片 11/**//*
图片 12(4 行受影响)
图片 13表 'TUser'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
图片 14*/

 

USE Test ;

GO

 

CREATE TABLE space_test

(

    id        INT,

    name    VARCHAR(4000)

)

GO

SET NOCOUNT ON;

Go

DECLARE @Index INT ;

SET @Index = 1;

WHILE @Index <= 10000

BEGIN 

    INSERT INTO dbo.space_test

    SELECT @Index, REPLICATE(N'k', 4000);

 

    SET @Index += 1;

END

的对象的分配单元ID。不同类型的数据,存储在不同类型的页面里。

可以看到,该查询全部数据是扫了四个数据页,也就是说插入的四行数据,一行为一个page.

在SQL Server数据库中, 对象目录视图sys.partitions中有一个字段rows会记录表的记录数。我们以AdventureWorks2014为测试数据库。

 

在正常数据页上,数据行紧接着页的标头按顺序放置。页的末尾是行偏移量表,对于页中的每一行,每个行偏移表都包含一个条目。

 但是我们执行下面的sql,发现index_size为8k:

 

 

每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。

图片 15EXEC sp_spaceused N'TUser'
图片 16图片 17/**//*
图片 18name    rows        reserved    data    index_size    unused
图片 19TUser    4              40 KB        32 KB    8 KB        0 KB
图片 20*/

图片 21

 

------------------此处插入宋大神的注解------------------------

 这是为什么,为什么没有建索引,这里却有一个index_size 8k ?

 

EXEC sp_spaceused 'dbo.space_test'

除了页头占用的空间和行偏移矩阵占用的空间,中间剩下的空间就是给数据行使用的。一个数据行中还存在其他的信息用于表示该行数据,具体的结构是这样的:

 

 

SELECT OBJECT_NAME(object_id)    AS Object_Name, 

       i.name                    AS Index_Name,

       p.rows                    AS Table_Rows

FROM sys.partitions  p

LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND  p.index_id = i.indid  

WHERE

     object_id = OBJECT_ID('TableName') 

 

状态位A        1字节
状态位B         1字节
定长数据类型的长度     2字节
定长数据的内容         具体定长数据字节
列数              2字节
NULL位图       列数/8个字节
变长列的个数     2字节
变长列的偏移矩阵    变长列个数*2个字节
变长列的数据            具体变长数据字节

下面来看看index_size是怎么来的?
首先想到是的

 

 

这些最少占据1+1+2+2+1=7 

图片 22sp_helptext sp_spaceused

图片 23

图片 24


通过查看sp_spaceused的代码,我们找到对于我们这个查询有用的信息代码:

 

 

输出指定的页面,可以使用:DBCC PAGE(<db_id>, <file_id>, <page_id>, <format_id>)

图片 25图片 26 /**//*  
图片 27 ** Now calculate the summary data.   
图片 28 *  Note that LOB Data and Row-overflow Data are counted as Data Pages.  
图片 29 */  
图片 30 SELECT   
图片 31  @reservedpages = SUM (reserved_page_count),  
图片 32  @usedpages = SUM (used_page_count),  
图片 33  @pages = SUM (  
图片 34   CASE  
图片 35    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
图片 36    ELSE lob_used_page_count + row_overflow_used_page_count  
图片 37   END  
图片 38   ),  
图片 39  @rowCount = SUM (  
图片 40   CASE  
图片 41    WHEN (index_id < 2) THEN row_count  
图片 42    ELSE 0  
图片 43   END  
图片 44   )  
图片 45 FROM sys.dm_db_partition_stats  
图片 46 WHERE object_id = @id;  
图片 47  
图片 48图片 49 /**//*  
图片 50 ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table  
图片 51 */  
图片 52 IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204)) > 0   
图片 53 BEGIN  
图片 54图片 55  /**//*  
图片 56  **  Now calculate the summary data. Row counts in these internal tables don't   
图片 57  **  contribute towards row count of original table.    
图片 58  */  
图片 59  SELECT   
图片 60   @reservedpages = @reservedpages + sum(reserved_page_count),  
图片 61   @usedpages = @usedpages + sum(used_page_count)  
图片 62  FROM sys.dm_db_partition_stats p, sys.internal_tables it  
图片 63  WHERE it.parent_id = @id AND it.internal_type IN (202,204) AND p.object_id = it.object_id;  
图片 64 END  
图片 65  
图片 66 SELECT   
图片 67  name = OBJECT_NAME (@id),  
图片 68  rows = convert (char(11), @rowCount),  
图片 69  reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),  
图片 70  data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),  
图片 71  index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),  
图片 72  unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB') 

那么我们还有一些疑问,我们先来看看这些问题吧!

 

Db_id 可以从sp_helpdb 的结果中得到。

通过上面的代码,我们可以基于我们目前的这个情况(堆表,不含有xml和fulltext,所以上面的202/204那段不用管了)提练出index的page如下算法:

 

 

File_id 可以从sp_helpfile 的结果中得到。

图片 73select index_pagecount=sum(used_page_count)-
图片 74sum(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
图片 75from sys.dm_db_partition_stats  
图片 76where object_id = object_id('TUser'); 

   1:没有索引的表是否也可以使用上面脚本?

 

Format_id 是你指定的输出格式。

这个结果是1.再套上index_size的公式:

   2:只有非聚集索引的堆表是否可以使用上面脚本?

图片 77

在运行DBCC PAGE 之前,还需要打开跟踪标志(trace flag)3604

图片 78select index_size = LTRIM (STR ((1) * 8, 15, 0) + ' KB')
图片 79图片 80/**//*
图片 818k
图片 82*/

   3:有多个索引的表,是否记录数会存在不一致的情况?

 

 

这就是8k的算法,从下面联机文档上关于sys.dm_db_partition_stats 的解释,可以分析出这个8k是个IAM page.
used_page_count  bigint
用于分区的总页数。计算方法为 in_row_used_page_count + lob_used_page_count + row_overflow_used_page_count
上面我们算index_page时公式为:
sum(used_page_count)-
sum(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
也就是in_row_used_page_count -in_row_data_page_count
对于这两列,联机文档解释是:

   4:统计信息不准确的表,是否rows也会不准确

 

在 SQL Server 中,行不能跨页,属于同一行的所有字段的数据都要放在同一个页面里。页的最大数据量是8 060 B(8 KB)。

in_row_used_page_count

   5: 分区表的情况又是怎么样?

接下来我们删除了id为偶数的记录。

所以一般数据类型字段所组成的一行,最长加起来不能超过8 KB。

用于存储和管理分区中的行内数据的总页数。该计数包括非叶 B 树页、IAM 页以及 in_row_data_page_count 列包含的全部页。

   6:对象目录视图sys.partitions与sp_spaceused获取的表记录函数是否准确。

 

但是,这一限制不包括Text/Image数据类型字段的数据。

in_row_data_page_count

 

 

这些类型字段的数据会被单独存放在LOB(LargeObject)页面里。

分区中存储行内数据所用的页数。如果分区是堆的一部分,则该值为堆中的数据页数。如果分区是索引的一部分,则该值为叶级别中的页数。(未计入 B 树中非叶页的数目。)以上两种情况都未计入 IAM(索引分配映射)页。

如下所示,我们先构造测试案例:

DELETE dbo.space_test WHERE id %2=0

 

**针对我们目前该表的情况,仅是一个堆表,那么可知前者是包含了IAM页,而后者不含有IAM页,那么sp_spaceused中的index_size在这里就是一个IAM(索引分配映射)页。

IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TAB_ROW')

BEGIN

    DROP TABLE TEST_TAB_ROW;

END

 

IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TAB_ROW')

BEGIN

        CREATE TABLE TEST_TAB_ROW

        (

            ID        INT,

            NAME      CHAR(200)

        )

END

GO

 

SET NOCOUNT ON;

BEGIN TRAN

DECLARE @Index INT =1;

 

WHILE @Index <= 100000

BEGIN

 

    INSERT INTO TEST_TAB_ROW

    VALUES(@Index, NEWID());

 

    SET @Index+=1;

    

    IF (@Index % 5000)  = 0 

       BEGIN

        IF @@TRANCOUNT > 0 

            BEGIN 

                COMMIT;

                BEGIN TRAN

            END

    END

END

 

IF @@TRANCOUNT > 0 

BEGIN 

    COMMIT;

END

GO

 

SQL Server 2005 开始的版本中,提供了varchar(max)、nvarchar(max)、varbinary(max)这样的数据类型。

**其实这个也可以通过DBCC IND看到:
图片 83

 

 

如果包含这一类数据类型字段的行,总长不超过8 KB,数据还是会一起存放在普通数据页面里。

如有错误,欢迎指正。

关于问题1、问题2,都可以使用上面脚本, 如下测试所示:

 

如果总长超过了8 KB,SQL Server 就会把这些字段的数据分开,单独存放在一种叫Row-Overflow(行溢出)的页面里。

 

 

如下截图所示,我们删除了5000条记录,还剩下5000条记录,但是该表的分配的空间总量(reserved)与数据使用的空间总量(data)没有变化,依然是40008KB和40000KB

 

 

SELECT OBJECT_NAME(object_id)    AS Object_Name, 

       i.name                    AS Index_Name,

       p.rows                    AS Table_Rows

FROM sys.partitions  p

LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND  p.index_id = i.indid  

WHERE

     object_id = OBJECT_ID('dbo.TEST_TAB_ROW') 

 

区(Extent)是8 个物理上连续的页的集合,用来有效地管理页。所有页都存储在区中。

 

图片 84

 

• 统一区,由单个对象所有。区中的所有8 页只能由一个对象使用。

 

 

图片 85

• 混合区,最多可由8 个对象共享。区中8 页的每页可由不同的对象所有。但是一页总是只能属于一个对象。

 

在表dbo.TEST_TAB_ROW 上创建非聚集索引后,查询结果如下所示:

 

通常从混合区向新表或索引分配页。当表或索引增长到8 页时,将变成使用统一区进行后续分配。

 

 

 

 

 

CREATE INDEX IX_TEST_TAB_ROW ON TEST_TAB_ROW(ID);

 

DBCC SHOWFILESTATS

 

 

我们删除了一半记录,照理说,数据使用的空间总量(data)应该变化了。那么我使用DBCC SHOWCONTIG('space_test')来看看,如下截图所示,

这个命令能直接从GAM 和SGAM 这样的系统分配页面上面读取区分配信息,直接算出数据库文件里有多少区已被分配。

 

图片 86

你会发现每页的平均可用字节数(Avg. Bytes Free per Page) 和平均页密度(满)(Avg. Page Density (full))出现了变化。如下对比所示:

 

 

 

 

sp_spaceused (+ updateusage才会准确)

 

我们插入500条记录,此时,这个数据量不足以触发统计信息更新,如下所示, Rows Sampled还是1000000

 

Avg. Bytes Free per Page

Avg. Page Density (full)

删除前

62

99.23%

删除后

4077.8

49.62%

DBCC SHOWCONTIG( 慎用,SQL Server 2005 之后可以使用sys.dm_db_index_physical_stats)

DECLARE @Index INT =1;

 

WHILE @Index <= 500

BEGIN

 

    INSERT INTO TEST_TAB_ROW

    VALUES(100000 +@Index, NEWID());

 

    SET @Index+=1;

    

END

 

sys.dm_db_partition_stats

 

 

可以从页面这个级别来分析检查数据库空间大小。

图片 87

图片 88

 

 

 

日志文件不是按页/区来组织的。

如下所示,发现sys.partitions中的记录变成了100500了,可见rows这个值的计算不依赖统计信息。

我们知道sp_spaceused的精确单位是页。它是根据sys.allocation_units 和sys.partitions 这两张管理视图来计算存储空间的。有时候,这两张表可能不能及时反映出数据的准确信息。可以通过updateusage这个参数,要求SQL Server为这个指令更新管理视图里的统计信息。但是这个案例中,sp_spaceused不准确的原因并不是因为这两张表没有及时反映出数据的准确信息,而是实验中按照特殊规律,在每一页都删除一条记录,保留一条记录,导致每一页上释放了接近一半的空间,并且页的填充程度接近50%,从而出现页面碎片化非常严重的情况,而sp_spaceused的精确单位是页,从而导致这种特殊的情况出现。

数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元。虚拟日志单元没有固定大小,

 

 

且一个物理日志文件所包含的虚拟日志单元数不固定。

图片 89

其实这个倒没有必要大惊小怪,这个空间虽然没有释放,但是下次依然可以再次利用,就好比一列火车,每节车厢只载了一半乘客,后面的路程中,可以继续往里面加入新的乘客。如下所示我们插入5000条记录,你会发现页的平均密度变化了,之前空闲的空间被重新利用了。

日志文件每自动增长一次,会至少增加一个虚拟日志单元。

 

 

如果一个日志文件经历了多次小的自动增长,里面的虚拟日志单元数目会比正常的日志文件多很多。

当然,如果你用sp_spaceused,发现这里面的记录也是100500

DECLARE @Index INT ;

SET @Index = 2;

WHILE @Index <= 10000

BEGIN 

    INSERT INTO dbo.space_test

    SELECT @Index, REPLICATE(N'k', 4000);

 

    SET @Index += 2;

END

GO

这种情况会影响到日志文件管理的效率,甚至造成数据库启动要花很长时间。

sp_spaceused 'dbo.TEST_TAB_ROW'

 

 

图片 90

 

事务日志是一种回绕的文件。当创建数据库时,逻辑日志文件从物理日志文件的始端开始。

 

图片 91

新日志记录被添加到逻辑日志的末端,然后向物理日志的末端扩张。

关于问题3:有多个索引的表,是否记录数会存在不一致的情况?

当逻辑日志的末端到达物理日志文件的末端时,新的日志记录将回绕到物理日志文件的始端,继续向后写。

 

 

   答案:个人测试以及统计来看,暂时发现多个索引的情况下,sys.partitions中的rows记录数都是一致的。暂时没有发现不一致的情况,当然也不排除有特殊情况。

DBCC SQLPERF(LOGSPACE)可以查看日志文件的使用情况。

 

 

关于问题5: 分区表的情况又是怎么样?

Tempdb 系统数据库是一个全局资源,可供连接到SQL Server 实例的所有用户使用,在SQL Server2005 中,用于保存下列各项:

 

用户对象

   答案:分区表和普通表没有任何区别。

• 用户定义的表和索引。

 

• 系统表和索引。

关于问题6:对象目录视图sys.partitions与sp_spaceused获取的表记录函数是否准确?

• 全局临时表和索引。

 

• 局部临时表和索引。

   答案:对象目录视图sys.partitions与sp_spaceused获取的表记录数是准确的。

• table 变量。

 

• 表值函数中返回的表。

 

 

ORACLE 数据库

内部对象

 

• 用于游标或假脱机操作以及临时大型对象(LOB)存储的工作表。

在ORACLE数据库中,可以通过DBA_TABLES、ALL_TABLES、USER_TABLES视图查看表的记录数,不过这个值(NUM_ROWS)跟统计信息有很大的关系,有时候统计信息没有更新或采样比例会导致这个值不是很准确。

• 用于哈希联接或哈希聚合操作的工作文件。

SELECT OWNER     , 

       TABLE_NAME, 

       NUM_ROWS  ,

       LAST_ANALYZED

FROM   DBA_TABLES 

WHERE  OWNER = '&OWNER' 

       AND TABLE_NAME = '&TABLE_NAME'; 

 

 

SELECT OWNER,

      TABLE_NAME,

      NUM_ROWS  ,

      LAST_ANALYZED

FROM ALL_TABLES

WHERE OWNER   ='&OWNER'

AND TABLE_NAME='&TABLE_NAME';

 

 

SELECT TABLE_NAME,

       NUM_ROWS  ,

       LAST_ANALYZED

FROM USER_TABLES

WHERE TABLE_NAME='&TABLE_NAME'

• 用于创建或重新生成索引等操作(如果指定了SORT_IN_TEMPDB)的中间排序结果,

 

或者某些GROUP BY、ORDER BY 或UNION 查询的中间排序结果。

更新统计信息后,就能得到准确的行数。所以如果需要得到正确的数据,最好更新目标表的统计信息,进行100%采样分析。对于分区表,那么就需要从dba_tab_partitions里面查询相关数据了。

每个内部对象至少使用9 页:一个IAM 页,一个8 页的区。

 

 

SQL>execute dbms_stats.gather_table_stats(ownname => 'username', tabname =>'tablename', estimate_percent =>100, cascade=>true);

版本存储区

 

• 由使用快照隔离级别或已提交读隔离级别(基于行版本控制)的数据库中的数据修改事务生成的行版本。

MySQL数据库

• 由数据修改事务为实现联机索引操作、多个活动的结果集(MARS)以及AFTER 触发器等功能而生成的行版本。

 

 

   在MySQL中比较特殊,虽然INFORMATION_SCHEMA.TABLES也可以查到表的记录数,但是非常不准确。如下所示,即使使用ANALYZE TABLE更新了统计信息,从INFORMATION_SCHEMA.TABLES中获取的记录依然不准确。

Tempdb 空间使用的一大特点,是只有一部分对象,例如用户创建的临时表、table 变量等,

SELECT TABLE_ROWS 

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME='table_name'

可以用sys.allocation_units 和sys.partitions 这样的管理视图来管理。

 

tempdb 的空间使用是不能用sp_spaceused 来跟踪的。(不准确)

mysql> SELECT TABLE_ROWS 

    -> FROM INFORMATION_SCHEMA.TABLES

    -> WHERE TABLE_NAME='jiraissue'

    -> ;

+------------+

| TABLE_ROWS |

+------------+

|      36487 |

+------------+

1 row in set (0.01 sec)

 

mysql> select count(*) from jiraissue;

+----------+

| count(*) |

+----------+

|    36973 |

+----------+

1 row in set (0.05 sec)

 

mysql> analyze table jiraissue;

+----------------+---------+----------+----------+

| Table          | Op      | Msg_type | Msg_text |

+----------------+---------+----------+----------+

| jira.jiraissue | analyze | status   | OK       |

+----------------+---------+----------+----------+

1 row in set (1.41 sec)

 

mysql> SELECT TABLE_ROWS 

    -> FROM INFORMATION_SCHEMA.TABLES

    -> WHERE TABLE_NAME='jiraissue';

+------------+

| TABLE_ROWS |

+------------+

|      34193 |

+------------+

1 row in set (0.00 sec)

 

mysql> 

 

图片 92

sys.dm_db_file_space_usage这张视图能反映tempdb 在几个大类里的空间使用分布。

图片 93

是用户对象(user_object_reserved_page_count),还是系统对象(internal_object_reserved_page_count)

 

,还是版本存储区(version_store_reserved_page_count)。

如上所示,MySQL这种查询表记录数的方法看来还是有缺陷的。当然如果不是要求非常精确的值,这个方法也是不错的。

 

当然,上面介绍的SQL Server、Oracle、MySQL数据库中的方法,还是有一些局限性的。例如,只能查询整张表的记录数,对于那些查询记录数带有查询条件(WHERE)这类SQL。还是必须使用SELECT COUNT(*)这种方法。

编辑:数据库 本文来源:存储在不同类型的页面里,COUNT(COL)来查询一个表

关键词: