澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

基础知识和实现方式,列存储索引分析

2019-11-04 作者:数据库网络   |   浏览(58)

SQL Server In-Memory OLTP Internals for SQL Server 2016

一、概述

 

列存储索引是SQL Server 2012中为提高数据查询的性能而引入的一个新特性,顾名思义,数据以列的方式存储在页中,不同于聚集索引、非聚集索引及堆表等以行为单位的方式存储。因为它并不要求存储的列必须唯一,因此它可以通过压缩将重复的列合并,从而减少查询时的磁盘IO,提高效率。

为了分析列存储索引,我们先看看B树或堆中的数据的存储方式,如下图,在page1上,数据是按照行的方式存储数据的,假设一行有10列,那么在该页上,实际的存储也会以每行10列的方式存储,如下图中的C1到C10。

假设我们执行select c1,c2 from table时,数据库会读取整个page1,显然,从C3到C10并不是我们想要的数据,但因为数据库每次读的最小单位是一页,因此这些不得不都加载到内存中。如果数据页多时,必然要消耗更过的IO和内存。

澳门新萄京官方网站 1

如果是列存储索引,数据按列的方式存储在一个页面中,如下图,page1中只存储表中C1列,page2只存储c2列,以此类推,page10存储c10列。

假设我们执行select c1,c2 from table时,结果会怎样呢?数据库只会读page1和page2,至于page3到page10因为没有对应的数据,数据库不会去读这些页,也不会加载到内存中,相比行存储而言,减少了磁盘IO和优化了内存的使用。

澳门新萄京官方网站 2

 

下文做了一个技术验证,用来分析列存储索引的查询性能。

思路:做两张一模一样的分区表(分区表可以更好的展示效果),含1000000行数据,然后给其中一张表(sales2)建立聚集索引,另一张表(sales)建列存储索引,最后来对比这两张表的查询性能。

 

SQL Server 2016新特性:列存储索引新特性

1、什么是表分区?
SQL Server使用三种不同类型的文件存储数据,它们分别是.mdf、.ndf和.ldf。主要数据存储在
.mdf文件中,比如表,索引,存储过程等。.ndf文件也用于储存这些数据。.ldf文件用于存储操作日志。
表默认存储在.mdf文件中。更新表时SQL Server会对表锁。那么其它的操作必须等待正在更新操作完成。
如果一个表很大,那么无论是查询操作还是更新操作,性能都会很差。如果将表分别存储在物理上独立但逻辑上连续的
分区中,那么SQL Server可以大幅优化查询操作和更新操作的性能。

这份白皮书是在上一份《SQL Server In-Memory OLTP Internals Overview》基础上的,很多东西都是一样的不再介绍,只介绍不相同的部分。

二、创建表

 

先做两张相同的表,创建的语句如下:

create partition function pf (date) as range left for values

('20110712', '20110713', '20110714', '20110715', '20110716');

go

create partition scheme ps as partition pf all to ([PRIMARY]);

go

create table sales (

[id] int not null identity (1,1),

[date] date not null,

itemid smallint not null,

price money not null,

quantity numeric(18,4) not null)

on ps([date]);

go

declare @i int = 0;

begin transaction;

while @i < 1000000

begin

declare @date date = dateadd(day, @i /250000.00, '20110712');

insert into sales2 ([date], itemid, price, quantity)

values (@date, rand()*10000, rand()*100 100, rand()* 10.000 1);

基础知识和实现方式,列存储索引分析。set @i = 1;

if @i % 10000 = 0

begin

raiserror (N'Inserted %d', 0, 1, @i);

commit;

begin tran;

end

end

commit;

GO

 

  1. 行存储表可以有一个可更新的列存储索引,之前非聚集的列存储索引是只读的。
  2. 非聚集的列存储索引支持筛选条件。
  3. 在内存优化表中可以有一个列存储索引,可以在创建表的时候创建,也可以在之后的alter table语句上创建。之前内存优化表不支持列存储索引。
  4. 聚集的列存储索引可以有多个非聚集行存储索引,之前列存储索引不支持非聚集索引。
  5. 支持在聚集列存储索引上加入主键和外键约束,约束使用btree索引。
  6. 列聚集索引有一个压缩延迟选项,让事务复合收到的影响最小。

2、如何实现表分区?
1)确定分区策略,即划分数据的逻辑规则。比如将整形值小于100的数据放在一个分区上,将大于等于100的数据放在第二个
分区上,等等。
SQL Server中的使用分区函数来实现分区策略:

行和索引存储

三、查询含聚集键的表

 

(1) 创建表sales2的聚集键

CREATE CLUSTERED INDEX Clu_sales2_index ON sales2(date,price,quantity) on ps([date]);

查看表的存储信息

select * from sys.system_internals_partitions p

where p.object_id = object_id('sales2');

select au.* from sys.system_internals_allocation_units au

join sys.system_internals_partitions p

on p.partition_id = au.container_id

where p.object_id = object_id('sales2');

GO

 

澳门新萄京官方网站 3

该表一共有6个分区,其中只有4个分区有数据,每个分区250000行,已使用1089页,。

(2) 执行查询语句 (注意清掉缓存)

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT COUNT(*),SUM(price*quantity) FROM sales2 WHERE date='20110713';

GO

澳门新萄京官方网站 4

我们可以看到,在这个查询中,一共有1089次逻辑读(等于该表每个分区中的已使用页数),CPU时间为62毫秒,占用时间为261毫秒。

备注:CPU时间,执行语句的时间;

占用时间,从磁盘读取数据开始到完全处理使用的时间。

 

 

CREATE PARTITION FUNCTION PF_Order(DATETIME)
AS RANGE RIGHT FOR VALUES
('20090101','20100101','20110101')

Range索引

Range索引在2014的时候还是不支持的。Range index 使用bwtree数据结构。Bwtree和btree一样有叶子结点和中间节点。最重要的不通点是,bwtree page指针是一个逻辑的page id,而不是物理的page no。PID表示mapping table 上的位置,mapping table把PID和物理内存地址关联。Bwtree的index page是从来不更新的,而是增加一个新的,然后让mapping table的相同PID指向一个不同的物理内存地址。

具体的bwtree的算法可以看:http://www.cnblogs.com/Amaranthus/p/4375331.html

四、查询含列存储索引的表

 

(1) 创建表sales的列存储索引

create columnstore index cs_sales_price on sales ([date], price, quantity) on ps([date]);

查看表的存储信息:

select * from sys.system_internals_partitions p

where p.object_id = object_id('sales')

and index_id = 2;

select au.* from sys.system_internals_allocation_units au

join sys.system_internals_partitions p

on p.partition_id = au.container_id

where p.object_id = object_id('sales')

and index_id = 2;

GO

澳门新萄京官方网站 5

在建有列存储索引后,表的行数并没有改变,每个分区依然还是250000行,但页面数明显减少,且页的类型由原来的IN_ROW_DATA变成了LOB_DATA。

(2) 执行查询语句

select count(*), sum(price*基础知识和实现方式,列存储索引分析。quantity) from sales where date = '20110713'

澳门新萄京官方网站 6

在这个查询中,一共有363次逻辑读(等于该表每个分区),CPU时间为93毫秒,占用时间为191毫秒。

兼容级别为120,130的数据库性能:

2)确定分区存储架构,即划分数据的物理存储规则。最终分区可以存储在主文件组,即.mdf文件中,也可以存储在非主文件组,
即.ndf文件中。
SQL Server中的使用分区架构来实现分区存储架构:

列存储索引

 

  1. 列存储索引支持读提交快照隔离级别和快照隔离级别。这样可以让查询保持事务一致性,并且不加锁
  2. 列存储索引不需要通过重建索引来清理删除的行,可以通过alter table……reorganize语句来清理
  3. 列存储索引可以在alwayson readable secondary replica中访问。
  4. 为了提升性能,SQLServer在表扫描的时候会为小于8字节的列计算最大,最新,合计,计数,平均值聚合会被pushdown。
  5. 在比较字符串类型的时候,条件会被pushdown来提高查询速度。

CREATE PARTITION SCHEME PS_Order
AS PARTITION PF_Order
TO ([FG1],[FG2],[FG3],[FG4])

列存储索引基本结构

SQL Server 2016内存优化表支持聚集的列存储索引。列存储索引是高复合的索引,并不是由行来组织,而是用列来组织的。行被分为多个组,一个组最多可以有2^20行,然后把某一列的数据放入行组中,不会去管剩下的行。

每个行组,SQL Server都会使用Vertipaq压缩算法,重新编码和排列行组中的顺序来打到最有的压缩效果。每个行组中的列都是独立保存的,这个结构称之为段(segment),每个段都是一个LOB,保存在LOB的分配段元中。段是数据读写的基本单元,如图,表示吧一组多个索引列转化为几个段

澳门新萄京官方网站 7

上图中,表被分为3个行组,每个行组有4个段,一共有12个段。

为了支持聚集行存储索引的更新,有2个额外的结构。一个独立的内部表(deleted rows table DRT)。顾名思义是用来做被删除行的bitmap,用来保存所有已经删除的行的rowid。新行加入会被保存在一个堆中,Delta Store。当行数达到一定行数(通常是2^20或者10万行),SQL Server会吧这些行转化为新的压缩的行组。

内存优化表中聚集列存储索引和内存优化表的非聚集索引是分开保存的,是数据的一个副本。实际上,内存优化表的聚集列存储索引你可以理解为,保存了所有列的非聚集列存储索引。因为数据是高效压缩的,因此开销比较少。因为类存储索引可以压缩到原始数据的10%,因此开销也只有10%。

所有的类存储索引段都是在内存中的。为了恢复的目的,每个行组在内存优化文件组中都保存成一个独立的文件类型为LARGE DATA,在文件中对于某个行组,所有的段都是存放在一起的。SQL Server也维护了一个指针,指向每个段并且可以访问这个段,特别是访问了部分列的时候。这个部分会在下面CHECKPOINT FILES的时候介绍。新的行会被以列存储索引保存,但是并不会马上加入到压缩行组中,新的行只能使用内存优化表的其他索引来访问。如图,新的行和整个表分开维护的。你可以认为这些行是“delta rowgroup”和磁盘表的Delta Store类似,但是这些行是内存优化表的一部分,但是不是技术上的列存储索引的一部分。实际上是课件的delta rowgroup

澳门新萄京官方网站 8

内存优化表中的列存储索引只能在interop模式下由优化器进行选择。查询使用类存储索引可以并发并且对于高性能有很多好处。原生编译过程是不会使用列存储索引的,并且所有的查询都不会并发执行。若一个SQL Server 2016的内存优化表有聚集列存储索引,那么就有2个varheap,一个用于压缩行组,另外一个用来保存新行,可以让SQL Server快速识别哪些行还没有进入压缩段,这些行也在可见的delta rowgroup中。

有2个后台线程每2分钟执行一次,用来检查delta rowgroup中的行。注意这些行包含最新插入的,和update的,在内存优化表update就是delete insert。如果这些行数超过10万那么就有下面2个操作:

  1. 行会被复制到一个或者多个行组,每个段都会被压缩转化变成聚集列存储索引的一部分。
  2. 行会从特定的内存分配器移到常规的内存存储。

SQL Server并不会是实际统计行数,而是使用评估。没有行组的行数可以超过1048576.如果超过有10万行,那么就会创建另外一个行组。如果小于10万行那么这些行还是会被留在原来的地方。

因为最新插入的行会被频繁更新,或者会被删除,想要延迟对最新行的压缩,可以设置一个等待量。当内存优化表有聚集列存储索引,那么就可以增加一个COMPRESSION_DELAY的参数,指定新行必须在delta rowgroup中呆多久。只有超过参数的行数超过10万才会被压缩到常规的列存储索引行组中。

当行被转换到压缩rowgroup之后,所有删除的行都会被放到Delete Rows表中,和磁盘表的聚集列存储索引。当行多的时候查询会很没有效率。这种情况下重组列存储索引并没有什么用,除非删除并且重建索引。一旦rowgroup中90%的行被删除,剩下的10%会自动被插入到未压缩的varheap,在内存优化表的Delta rowgroup中。Rowgroup的存储会被进行垃圾回收。

Note:
前面提到的,如果内存优化表有任何LOB或者溢出列,列存储索引不能在上面被创建。因为最大的行不能超过8060字节。另外一旦内存优化表有一个列存储索引,就不能使用alter table操作。需要先删除列存储索引,alter**,然后再创建列存储索引。

以下是创建内存优化表的脚本,有2个索引,一个range索引一个列存储索引,然后查询内存消费。并且设置COMPRESSION_DELAY为60分钟。

USE master;

GO

SET NOCOUNT ON;

GO

澳门新萄京官方网站,DROP DATABASE IF EXISTS IMDB;

GO

CREATE DATABASE IMDB;

GO

ALTER DATABASE IMDB

    ADD FILEGROUP IMDB_mod_FG

    CONTAINS MEMORY_OPTIMIZED_DATA;

GO

ALTER DATABASE IMDB

    ADD FILE

总结

从两次查询的结果来看,无论是逻辑读的次数和占用时间,在列存储索引的表中执行查询明显要快于聚集索引的表。

而且,从两种表的存储结构中可以看到,列存储索引占用的页面数量较聚集索引的少,这也印证了列存储索引的压缩功能。

备注:通过两次查询,我们看到两者的CPU时间差距不是很大,相反聚集索引占用的时间更小,考虑到列存储实际上是压缩存储,我认为在一张小表或者简单的表中,对列存储索引差查询或许会占用多的CPU时间,因为查询时需要解压(我没有具体验证过),因此列存储索引在小表中的优势主要体现在IO和空间上,实际上列存储索引的对象往往是含有大数据量的表,数据量越大,其优势体现越明显。

说明:准确的说本文并不是原创,文章是从如下地址翻译过来,然后结合自己的实践,增加了一些自己的理解。

 

3)将表创建在分区存储架构上,并指定使用哪一列做为划分数据的依据。注意做为划分数据依据的列必须在聚集索引中
在更新数据时,SQL Server首先使用列的数据做为参数调用分区函数确定应该将该数据存储在哪个文件组(逻辑分区),
然后将数据写入文件组对应的物理文件中。

(   NAME

'IMDB_mod' ,

                

兼容级别为130的数据库性能:

SQL Server中创建分区表:

FILENAME

'c:HKDataIMDB_mod'

             )

    TO FILEGROUP IMDB_mod_FG;

GO

USE IMDB;

GO

DROP TABLE IF EXISTS dbo.OrderDetailsBig;

GO

CREATE TABLE dbo.OrderDetailsBig

    (

        OrderID INT NOT NULL ,

        ProductID INT NOT NULL ,

        UnitPrice MONEY NOT NULL ,

        Quantity SMALLINT NOT NULL ,

        Discount REAL NOT NULL INDEX IX_OrderID NONCLUSTERED HASH ( OrderID )

                                   WITH

  1. 新的batch模式支持一下操作:
    • SORT
    • 多个聚合函数
    • Windows 聚合函数
    • Windows用户定义的聚合
    • windows聚合分析函数
  2. 单线程查询可以运行在batch mode
  3. 当访问的数据是行存储或者列存储,内存优化表查询可以在InterOp模式下并行

CREATE TABLE dbo.[Order]
(
OrderID BIGINT IDENTITY(1,1),
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Order_OrderID_OrderDate PRIMARY KEY CLUSTERED(OrderID,OrderDate)
)
ON PS_Order(OrderDate)

(BUCKET_COUNT

20000000) ,

        INDEX IX_ProductID NONCLUSTERED ( ProductID ) ,

        CONSTRAINT PK_Order_Details

            PRIMARY KEY NONCLUSTERED

                (

                    OrderID ,

                    ProductID

                ) ,

        INDEX clcsi_OrderDetailsBig CLUSTERED COLUMNSTORE

            WITH

 

 

(COMPRESSION_DELAY

60)

    )

WITH (

支持:

下面是详细的示例代码:

MEMORY_OPTIMIZED

ON,

These system views are new for columnstore:

--表分区测试代码
USE [master]
GO

DURABILITY

SCHEMA_AND_DATA );

GO

SELECT OBJECT_NAME(c.object_id) AS table_name ,

       a.xtp_object_id ,

       a.type_desc ,

       minor_id ,

       memory_consumer_id AS consumer_id ,

       memory_consumer_type_desc AS consumer_type_desc ,

       memory_consumer_desc AS consumer_desc ,

       CONVERT(NUMERIC(10, 2), allocated_bytes / 1024. / 1024) AS allocated_MB ,

       CONVERT(NUMERIC(10, 2), used_bytes / 1024. / 1024) AS used_MB

FROM   sys.memory_optimized_tables_internal_attributes a

       JOIN sys.dm_db_xtp_memory_consumers c ON

- sys.column_store_row_groups (Transact-SQL)

--创建具有多个文件组的测试数据库
IF EXISTS(SELECT 1 FROM sys.databases WHERE [name]=N'TestTablePartitionDB')
BEGIN
    DROP DATABASE [TestTablePartitionDB]
END

a.object_id

c.object_id

                                                AND

- sys.dm_column_store_object_pool (Transact-SQL)

CREATE DATABASE [TestTablePartitionDB]
ON PRIMARY
(
Name='PrimaryFG',
FileName='D:TestTablePartitionTestTablePartitionDB.mdf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG1]
(
Name='FG1',
FileName='D:TestTablePartitionFG1.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG2]
(
Name='FG2',
FileName='D:TestTablePartitionFG2.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG3]
(
Name='FG3',
FileName='D:TestTablePartitionFG3.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG4]
(
Name='FG4',
FileName='D:TestTablePartitionFG4.ndf',
Size=5,
FileGrowth=1
)

a.xtp_object_id

c.xtp_object_id

       LEFT JOIN sys.indexes i ON

- sys.dm_db_column_store_row_group_operational_stats (Transact-SQL)

USE TestTablePartitionDB
GO

c.object_id

i.object_id

                                  AND

- sys.dm_db_column_store_row_group_physical_stats (Transact-SQL)

--创建分区函数
CREATE PARTITION FUNCTION PF_Order(DATETIME)
AS RANGE LEFT FOR VALUES
('20090101','20100101','20110101')

c.index_id

i.index_id;

返回的结果:

澳门新萄京官方网站 9

上图,显示表自己有6行。有一个内存消费者用于压缩rowgroup(HKCS_COMPRESSED消费者),2个用于range index,1个用于hash index,2个用于表的行存储(rowstore)(这个和白皮书中说的不同),行存储中其中一个是为了表中的行,第二个是delta rowgroup。每个有列存储索引的表都有4个内部表,xtp_object_id都不相同。每个内部表为了访问方便至少有一个索引用于数据访问。四个内部表:ROW_GROUP_INFO_TABLE( hash索引),SEGMENTS_TABLE( 2个hash索引),DICTIONARIES_TABLE( hash 索引),DELETED_ROW_TABLE( hash索引)。(这些内部表的细节白皮书没有介绍)

除了看内存消费者之外,另外一个要检查的DMV是sys.dm_db_column_store_row_group_ physical_stats,这个视图不单单是显示了每个COMPRESSED并且OPEN的rowgroup的行数。你可以用一下脚本查看:

BEGIN TRAN;

DECLARE

- sys.dm_db_index_operational_stats (Transact-SQL)

--创建分区架构
CREATE PARTITION SCHEME PS_Order
AS PARTITION PF_Order
TO([FG1],[FG2],[FG3],[FG4])

@i INT

0;

WHILE ( @i < 10000000 )

    BEGIN

        INSERT INTO dbo.OrderDetailsBig

        VALUES ( @i, @i % 1000000, @i % 57, @i % 10, 0.5 );

        SET @i = @i 1;

        IF ( @i %

- sys.dm_db_index_physical_stats (Transact-SQL)

--创建分区表
CREATE TABLE dbo.[Order]
(
OrderID BIGINT IDENTITY(1,1),
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Order_OrderID_OrderDate PRIMARY KEY CLUSTERED(OrderID,OrderDate)
)
ON PS_Order(OrderDate)

264

0 )

            BEGIN

                COMMIT TRAN;

                BEGIN TRAN;

            END;

    END;

COMMIT TRAN;

SELECT   row_group_id ,

         state_desc ,

         total_rows ,

         trim_reason_desc

FROM     sys.dm_db_column_store_row_group_physical_stats

- sys.internal_partitions (Transact-SQL)

--测试数据的逻辑分区是哪个
SELECT $PARTITION.PF_Order('20090101')

WHERE    object_id

OBJECT_ID('dbo.OrderDetailsBig')

ORDER BY row_group_id;

GO

澳门新萄京官方网站 10

可以通过time_reason_desc字段可以查看为什么rowgroup的行会少于1048576行。如果没有小于1048576那么就显示NO_TRIM。因为OPEN的rowgroup是不压缩的,因此为null,若为STATS_MISMATCH表示行太少,若为SPILLOVER表示有移除导致。

 

--测试分区表的分区情况
SELECT partition_number,rows
FROM sys.partitions
WHERE [object_id]=object_id(N'dbo.[Order]')

These in-memory OLTP-based DMVs contain updates for columnstore:

--Drop index MyTable_IXC on MyTable with (Move To [Data Partition Scheme] (ID) )

- sys.dm_db_xtp_hash_index_stats (Transact-SQL)

- sys.dm_db_xtp_index_stats (Transact-SQL)

- sys.dm_db_xtp_memory_consumers (Transact-SQL)

- sys.dm_db_xtp_nonclustered_index_stats (Transact-SQL)

- sys.dm_db_xtp_object_stats (Transact-SQL)

- sys.dm_db_xtp_table_memory_stats (Transact-SQL)

 

限制:

  1. 当btree索引定义在聚集列存储索引时,MERGE被禁用。
  2. 对于内存优化表,列存储索引必须包含所有的列,也不能使用筛选。
  3. 对于内存优化表,列存储索引只能在InterOp模式下运行,并且支持并发查询。

 

 

     

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:基础知识和实现方式,列存储索引分析

关键词: