xml地图|网站地图|网站标签 [设为首页] [加入收藏]
索引阐述系列三,分页编号的另一种方式
分类:数据库

一.   概述

  这一节来详细介绍堆组织,通过讲解堆的结构,堆与非聚集索引的关系,堆的应用场景,堆与聚集索引的存储空间占用,堆的页拆分现象,最后堆的使用建议 ,这几个维度来描述堆组织。在sqlserver里,表有二种组织方式,在表上没有创建聚集索引时,表就是堆组织, 有聚集索引就是B树组织。无论哪种组织方式,都可以在表上建多个非聚集索引。表的组织方式也称为HOBT。

  之所以称为堆,是因为它的数据不按任何顺序进行组织,而是按分区组对数据进行组织。 在一个堆中。用于保存数据之间的关系的唯一结构是索引分配映射(IAM , index allocation map)的位图页,上一章节中有说过页文件类型。

  IAM位图页有指向数据页的指针,如果一个IAM不足以覆盖所有页,将维护一个IAM页的链,在查询数据时,先使用IAM页来遍历分配单元的数据。

  堆结构在数据插入没有更改时是有存储顺序的,但一改动如修改删除,结构就会发生变化, 因为没有特定的顺序来维护数据, 所以在新增表中的行时,可以保存到任何数据页上。

  Sql server内部使用文件页(PFS, Page Free Space)可用空间页,PFS位图来跟踪数据页中的可用空间,  以便可以快速找到有足够空间能容纳新行的页面,如果没有则分配一个新数据页面。

1.1 堆组织结构

  在堆组织中对于一个select查询,首先查询IAM页,然后根据IAM页提供的信息,遍历每个区,把区内符合条件下的数据页返回,在堆中查询从上到下依次是Heap-->IAM-->区-->数据页。如下图所示:

图片 1

1.2 堆上的非聚集索引

  非聚集索引也可以结构化为一颗B树,与聚集索引类似,唯一区别就是非聚集索引的叶子层只包含索引键列和指向数据行的指针(行定位符)。如果是在堆上建立非聚集索引,则指针指向堆结构中的数据行

  在堆中非聚集索引都有一个相对应的partition, 在这个partition下都有一个连接指向Root page根,在叶子层有会一个连接(文件号,页号,行号)指向真正的数据,真正的数据还是以堆结构存放的。在堆上建立的非聚集索引查询从上到下依次是Heap-->Root根-->root index中间层-->叶节点(文件号,页号,行号)-->数据页。如下图所示:

图片 2

今天看书讲T-SQL,看到了UNBOUNDED PRECEDING,就想比对下ROW_NUMBER()的运行速度。

 

二. 堆应用场景

  堆最常用的现象就是使用临时表,一般都很少会主动加clustered primary关键词,很多时间临时对象的应用也没有必要使用聚集索引。但如果临时表在会话里需要使用多次条件查询,排序 等操作,聚集索引则少一部分开销。下面演示下:  

--创建临时表堆
CREATE TABLE #tempWithHeap([SID] INT, model VARCHAR(50))
--插入数据
INSERT INTO #tempWithHeap
SELECT [sid],model FROM dbo.Product WHERE UpByMemberID=3000
--查询
SELECT Product.* FROM Product 
JOIN #tempWithHeap 
ON #tempWithHeap.[SID] = dbo.Product.[SID]

  下图在执行计划里能看到临时表是表扫描方式

图片 3

--创建临时表聚集
CREATE TABLE #tempWithCLUSTERED([SID] INT PRIMARY KEY CLUSTERED, model VARCHAR(50))
--插入
INSERT INTO #tempWithCLUSTERED
SELECT [sid],model FROM dbo.Product WHERE UpByMemberID=3000
--查询
SELECT Product.* FROM Product 
JOIN #tempWithCLUSTERED 
ON #tempWithCLUSTERED.[SID] = dbo.Product.[SID]

  下图在执行计划里能看到临时表是聚集索引扫描方式

  图片 4

  下面来演示堆和索引在排序下不同的执行计划

--临时表堆上排序
SELECT Product.SID FROM Product JOIN #tempWithHeap
ON #tempWithHeap.SID=Product.SID
ORDER BY #tempWithHeap.SID

  在下图执行计划中排序显示开销15%

图片 5

--临时表聚集索引上排序
SELECT Product.SID FROM Product JOIN #tempWithCLUSTERED
ON #tempWithCLUSTERED.SID=Product.SID
ORDER BY #tempWithCLUSTERED.SID

  在下图执行计划中排序开销没有

图片 6

sql及相关的结果如下,数据库中的数据有5W+。

Preface

三.堆上的页拆分

   堆上的页拆分叫Forwarded records,是指更新数据后,原有页面空间大小已经无法存放该数据,sql server 会把这个数据移到堆中的新数据页里,并在新旧页中分别添加一个指针,标识这个数据在新旧页中的位置,从旧页指向新页的指针叫Forwarded records pointer 存放于旧页中, 从新页指向旧页的指针叫作back pointer 存放于新页中。

  下面来演示下页拆分现象

--这里定义一个堆表,使用变长字段2500
CREATE TABLE HeapForwardedRecords
(
    ID  INT IDENTITY(1,1),
    DATA VARCHAR(2500)  
)
--插入数据,这里data字段插入2000,插入24条
INSERT INTO HeapForwardedRecords(data)
SELECT TOP 24 REPLICATE('X',2000) FROM sys.objects

--查看碎片信息
select OBJECT_NAME(object_id),object_id,
index_type_desc,page_count,record_count,
forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('HeapForwardedRecords')  ,null,null,'Detailed')

  下图显示:共6页,24条数据,页拆分0条。 (一行数据2000字节,一页存储4行, 24行共6页)

图片 7

  下面将data字段存储的2000字节,修改为2500字节,每页4行更新二行,原来一页存储4行(4*2000<8060),现更新后就是(2*2000 +2*2500)>8060字节,原页就只能存储三行,这时堆上的页就会拆分。

--更新数据,12行受影响
UPDATE HeapForwardedRecords SET DATA=REPLICATE('X',2500)
WHERE ID%2=0

  再次查看碎片信息,发现原来6页存储变为了9页, forwarded_record_count是指页拆分次数(是指向另一个数据位置的指针的记录数,在更新过程中,如果在原始位置存储的空间不足,将会出现此状态) 如下图:

图片 8

 

  总结:通过sys.dm_db_index_physical_stats 我们可以查询到碎片信息,page count的页数越多,内存消耗就越多。 要整理碎片可以重建聚集索引。若要减少堆的区碎片,请对表创建聚集索引,然后删除该索引。更多碎片信息查看

如下图:forwarded_record_count为0了 

图片 9

ROW_NUMBER():

 

四.堆存储结构对空间使用的影响 

 4.1 等量数据的存储方式,使用DBCC SHOWCONFIG来查看

  下面演示表结构相同情况下在堆组织和聚集索引组织二种方式, 存储等量数据,来查看空间的占用。

--堆表
CREATE TABLE [dbo].[ProductWithDeap](
    [SID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [nvarchar](100) NULL,
    [Brand] [nvarchar](100) NULL,
    [UpdateTime] [datetime] NULL,
    [UpByMemberID] [int] NULL,
    [UpByMemberName] [nvarchar](200) NULL)
  ON [PRIMARY]
--插入表堆数据(60703 行)
INSERT INTO  ProductWithDeap(Model,Brand,UpdateTime,UpByMemberID,UpByMemberName) 
SELECT Model,Brand,UpdateTime,UpByMemberID,UpByMemberName FROM dbo.Product 
WHERE  UpByMemberID=3000

--聚集索引
CREATE TABLE [dbo].[ProductWithClustered](
    [SID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [nvarchar](100) NOT NULL,
    [Brand] [nvarchar](100) NULL,
    [UpdateTime] [datetime] NULL,
    [UpByMemberID] [int] NULL,
    [UpByMemberName] [nvarchar](200) NULL,
 CONSTRAINT [PK_ProductWithClustered] PRIMARY KEY CLUSTERED 
(
    [SID]  ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
--插入表聚集数据(60703 行)
INSERT INTO  ProductWithClustered(Model,Brand,UpdateTime,UpByMemberID,UpByMemberName) 
SELECT Model,Brand,UpdateTime,UpByMemberID,UpByMemberName FROM dbo.Product 
WHERE  UpByMemberID=3000

图片 10

存储方式 使用页面数量 使用区数量
堆组织  517  69
聚集索引  518  66

4.2 删除数据后,对空间的释放情况

  delete  from ProductWithDeap

       delete from ProductWithclustered

图片 11

存储方式 剩余空间数量 剩余区数量
堆组织  50  11
聚集索引  1  1

  使用delete后我们发现,建立堆组织的空间不会马上释放掉,聚集索引能很好的释放空间,但也存在1页未释放,如果完全释放使用truncate table。

      总结:当我们考虑表是用堆组织还是用聚集索引时,通过上面的演示我们知道,聚集索引的叶子层就是数据本身,并不会因为建立聚集索引而消耗过多的空间(注意非聚集索引会占用空间,不管是建立在堆组织上还是聚集索引上),而且能够更好的管理数据和空间的释放。除非特殊情况(后面有选择堆的理由)

SET STATISTICS TIME ON

SELECT
    ROW_NUMBER() OVER(ORDER BY Id DESC) rn,Id
FROM
    dbo.T_MyCourse

    I've demontstrated several InnoDB locking cases in my previous blog.I'm gonna do the rest tests about InnoDB locks.

五.堆的使用建议

  5.1堆需要考虑点

            过多的产生forwarded records 来维护堆表,产生额外的io操作。

       5.2 堆选择理由

              高频率的增删操作。

              键值经常改变,特别在索引上的位置改变。

              插入大量数据列到表中。

              主键值并不自增或者唯一。

运行结果

 

图片 12

Procedure

UNBOUNDED PRECEDING

 

SET STATISTICS TIME ON

SELECT
    SUM(1) OVER(ORDER BY Id DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) rn,Id
FROM
    dbo.T_MyCourse

Test table information and the relevant variables.

运行结果

 1 zlm@192.168.56.100:3306 [zlm]>show create table t1G
 2 *************************** 1. row ***************************
 3        Table: t1
 4 Create Table: CREATE TABLE `t1` (
 5   `c1` int(10) unsigned NOT NULL DEFAULT '0',
 6   `c2` int(10) unsigned NOT NULL DEFAULT '0',
 7   `c3` int(10) unsigned NOT NULL DEFAULT '0',
 8   `c4` int(10) unsigned NOT NULL DEFAULT '0',
 9   PRIMARY KEY (`c1`),
10   KEY `c2` (`c2`)
11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
12 1 row in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [zlm]>select * from t1;
15 +----+----+----+----+
16 | c1 | c2 | c3 | c4 |
17 +----+----+----+----+
18 |  0 |  0 |  0 |  0 |
19 |  1 |  1 |  1 |  0 |
20 |  3 |  3 |  3 |  0 |
21 |  4 |  2 |  2 |  0 |
22 |  6 |  2 |  5 |  0 |
23 |  8 |  6 |  6 |  0 |
24 | 10 |  4 |  4 |  0 |
25 +----+----+----+----+
26 8 rows in set (0.01 sec)
27 
28 zlm@192.168.56.100:3306 [zlm]>select @@transaction_isolation;
29 +-------------------------+
30 | @@transaction_isolation |
31 +-------------------------+
32 | REPEATABLE-READ         |
33 +-------------------------+
34 1 row in set (0.00 sec)
35 
36 zlm@192.168.56.100:3306 [(none)]>show variables like 'innodb_status_output_locks';
37 +----------------------------+-------+
38 | Variable_name              | Value |
39 +----------------------------+-------+
40 | innodb_status_output_locks | ON    |
41 +----------------------------+-------+
42 1 row in set (0.00 sec)

图片 13

 

通过运行之后,看到结果,使用微软官方提供的方法进行编号排序,速度明显的提高。

Test 1: session1 update while session2 insert.

不过我使用上述方法进行分页获取数据的时候结果又有点不一样。

 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 2 rows affected (0.00 sec)
 6 Rows matched: 2  Changed: 2  Warnings: 0
 7 
 8 +----+----+----+----+
 9 | c1 | c2 | c3 | c4 |
10 +----+----+----+----+
11 | 10 |  4 |  4 | 20 |
12 |  8 |  6 |  6 | 20 |
13 +----+----+----+----+
14 2 rows in set (0.00 sec)
15 
16 //Lock information of session1.
17 TABLE LOCK table `zlm`.`t1` trx id 2997544 lock mode IX
18 RECORD LOCKS space id 178 page no 4 n bits 80 index c2 of table `zlm`.`t1` trx id 2997544 lock_mode X
19 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
20  0: len 8; hex 73757072656d756d; asc supremum;;
21 
22 Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
23  0: len 4; hex 00000006; asc     ;;
24  1: len 4; hex 00000008; asc     ;;
25 
26 Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
27  0: len 4; hex 00000004; asc     ;;
28  1: len 4; hex 0000000a; asc     ;;
29 
30 RECORD LOCKS space id 178 page no 3 n bits 80 index PRIMARY of table `zlm`.`t1` trx id 2997544 lock_mode X locks rec but not gap
31 Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
32  0: len 4; hex 00000008; asc     ;;
33  1: len 6; hex 0000002dbd28; asc    - (;;
34  2: len 7; hex 3a0000012727bb; asc :   '' ;;
35  3: len 4; hex 00000006; asc     ;;
36  4: len 4; hex 00000006; asc     ;;
37  5: len 4; hex 00000014; asc     ;;
38 
39 Record lock, heap no 8 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
40  0: len 4; hex 0000000a; asc     ;;
41  1: len 6; hex 0000002dbd28; asc    - (;;
42  2: len 7; hex 3a000001272799; asc :   '' ;;
43  3: len 4; hex 00000004; asc     ;;
44  4: len 4; hex 00000004; asc     ;;
45  5: len 4; hex 00000014; asc     ;;
46 
47 //Session2:
48 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,5,10,10;
49 Query OK, 0 rows affected (0.00 sec)
50 
51 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
52 
53 //Session2 was block because of the gap lock(c2>=4 hold a supremum lock) which was holed by session 1.The value c2=5 which session2 want to insert is conficted with the range lock.

分页获取数据:

 

ROW_NUMBER() 分页获取数据:

Test 2: session1 update while session2 insert.

SET STATISTICS TIME ON

SELECT
    *
FROM
    (
    SELECT
        ROW_NUMBER() OVER(ORDER BY Id DESC) rn,Id
    FROM
        dbo.T_MyCourse
    )a
WHERE
    a.rn BETWEEN 55 AND 444
 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;update t1 set c4=20 where c2>=4;select * from t1 where c2>=4;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 Query OK, 2 rows affected (0.00 sec)
 6 Rows matched: 2  Changed: 2  Warnings: 0
 7 
 8 +----+----+----+----+
 9 | c1 | c2 | c3 | c4 |
10 +----+----+----+----+
11 | 10 |  4 |  4 | 20 |
12 |  8 |  6 |  6 | 20 |
13 +----+----+----+----+
14 2 rows in set (0.00 sec)
15 
16 //Session2:
17 zlm@192.168.56.100:3306 [zlm]>begin;insert into t1 select 7,2,10,10;
18 Query OK, 0 rows affected (0.00 sec)
19 
20 Query OK, 1 row affected (0.00 sec)
21 Records: 1  Duplicates: 0  Warnings: 0
22 
23 //This time the transaction in session2 was committed immediately.The value c2=2 didn't conflict with the range lock in session1.

图片 14

 

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:索引阐述系列三,分页编号的另一种方式

上一篇:sqlserver存储过程专题,PIVOT与用法解释 下一篇:常见命令语句,mysqldump备份表中有大字段失败的
猜你喜欢
热门排行
精彩图文