未加星标

【DBA精华系列】SQL Server 2012列存储索引技术

字体大小 | |
[数据库(综合) 所属分类 数据库(综合) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

title: SQL Server 2012列存储索引技术

author: 风移 摘要

MS SQL Server 2012首次引入了列存储索引(Columnstore Index)来加速数据分析(OLAP)和数据仓库(Data Warehouse)场景的查询,它主要是通过将数据按列压缩存储的方式来减少查询对磁盘IOPS开销和CPU开销,最终达到提升查询效率,降低响应时间的目的。当然,列存储索引也不是一把万能的钥匙,在SQL Server 2012版本中它有诸多非常严苛限制条件。

这篇文章会从以下几个方面来介绍列存储索引:

列存储索引所涉及到的基本概念

列存储索引的结构

列存储索引对查询性能的影响

MS SQL Server 2012上列存储索引的限制

解决列存储索引表只读问题

概念

首先让我们来看看列存储索引涉及到的几个关键的概念。

列存储技术

列存储技术背后的核心思想并不是微软首创的,早在上20世纪70年代,基于列的存储系统就与传统的行存储数据库管理系统一同出现了。微软数据库产品首次尝试从传统的行存储结构转变为面向列的存储方案是在SQL Server2012这个产品,以期望这种方案能够以最低限度的额外工作量换取更高的性能。

列存储索引

MS SQL Server列存储索引是使用列式数据格式(称为列存储)压缩存储、检索和管理数据的技术。它主要目标是将尽量多的数据加载至内存中,在进行数据处理时,使用访问内存的方式来替换从磁盘中读取数据。这种处理方式有两大优点,一是速度更快,二是硬盘的IOPS(每秒读写次数)消耗更低。

列存储索引压缩

数据压缩对于MS SQL Server来说已经不是什么新鲜玩意儿了,SQL Server支持数据库备份压缩,数据行压缩和数据页压缩,当然列存储索引压缩默认是开启的并且不允许禁用。相对于传统按行存储的结构来说,列存储索引这种按列来存储的特殊结构来说,压缩更加有效。这是因为表中的相同列数据属性相同,存储的数据也非常相近,有可能还有非常多的重复值,因此数据压缩比例更高,压缩效率更快,更少的磁盘I/O开销,数据读取需要更少的内存,相同内存中可以存储更多的数据。

使用下面语句,我们可以发现本文的测试表dbo.SalesOrder的列存储索引NCSIX_ALL_Columns压缩算法是COLUMNSTORE。

USE ColumnStoreDB GO SELECT DISTINCT table_name = object_name(part.object_id) ,ix.name ,part.data_compression_desc FROM sys.partitions as part INNER JOIN sys.indexes as ix ON part.object_id = ix.object_id AND part.index_id = ix.index_id WHERE part.object_id = object_id('dbo.SalesOrder','U') AND ix.name = 'NCSIX_ALL_Columns'

结果如下:


【DBA精华系列】SQL Server 2012列存储索引技术
Column Segment and Row Group

在列存储索引中,SQL Server引入了两个全新的概念:Column Segment和Row Group。

Column Segment:是SQL Server列存储索引最基本的存储单元,列存储索引的每一列数据会被划分为一个或者多个Column Segment。它是一组经过压缩后物理存储在相同存储介质的列值。

Row Group:是一组同时被压缩成列存储格式的行,每一个Row Group中包含了每个列的一个Column Segment。Row Group定义了每一个Column Segment的列值。

以上的解释还是非常抽象和难于理解,做一个图,我们就很好理解什么Column Segment和Row GROUP了。


【DBA精华系列】SQL Server 2012列存储索引技术
Batch Mode Processing

在SQL Server OLAP的场景中,做BI类分析型查询语句往往需要扫描非常大量的数据记录数。Batch Mode Processing 是SQL Server新的查询处理算法,专门设计来高效地处理大量数据的批处理算法,以加快统计分析类查询的效率。其实这个算法的原理实现非常简单,SQL Server有一个专门的系统视图sys.column_store_segments来存放列存储索引的每个列的每个Segments的最小值和最大值,当SQL Server执行Batch Mode Processing查询时,只需要和查询筛选条件对比,就可以知道对应的Segment是否包含满足条件的数据,从而可以实现快速的跳过哪些不满足条件的Segment。由于每个Segment中包含成千上万条记录,所以SQL Server筛选出满足条件的效率非常高,因此大大节约了磁盘I/O和因此带来的CPU开销。这种跳过不满足条件Segment的算法专业术语叫Segment Elimination。

USE ColumnStoreDB GO SELECT table_name = object_name(part.object_id) ,col.name ,seg.segment_id ,seg.min_data_id ,seg.max_data_id ,seg.row_count FROM sys.partitions as part INNER JOIN sys.column_store_segments as seg ON part.partition_id = seg.partition_id INNER JOIN sys.columns as col ON part.object_id = col.object_id AND seg.column_id = col.column_id WHERE part.object_id = object_id('dbo.SalesOrder','U') AND seg.column_id = 1 ORDER BY seg.segment_id

结果如下:


【DBA精华系列】SQL Server 2012列存储索引技术
行列存储结构对比

其实在列存储索引引入SQL Server之前,SQL Server的索引我们通常不叫行存储索引,而是叫B-Tree索引,因为SQL Server的行存储索引是按照B-Tree结构来组织的。这一节我们来对比基于行存储和基于列存储的结构差异。

行存储结构

在传统的基于行存储的结构中,表中每一行数据会存储在一起。如果用户需要其中的某个或者某几个字段的数据,SQL Server系统必须先将满足条件的记录所有字段的值载入内存中,然后再从中筛选出用户需要的列。换句话说,用户的查询语句横向筛选是通过索引(传统的B-Tree索引)来快速完成,而纵向列的筛选由于基于行存储的设计而浪费了许多的系统性能,这些性能浪费包括载入过多列数据导致的内存浪费,磁盘I/O资源的浪费开销,和因此而带来的CPU开销。那就让我们看看基于行存储的结构图:


【DBA精华系列】SQL Server 2012列存储索引技术
列存储结构

为了解决行存储结构导致资源浪费和多余开销,从MS SQL Server 2012开始,微软引入了基于列存储的新结构,具体使用在列存储索引这个方面。列存储结构是将数据按列来存储,每一列的数据存放在一起。这样当用户在执行查询的时候,可以快速拿到这一列的所有数据,而不会浪费多余的IO资源和相应的CPU开销。除了存储结构的变化外,微软还对列存储索引默认启用了数据压缩功能,进一步减少了IO开销。列存储索引的结构如下:


【DBA精华系列】SQL Server 2012列存储索引技术
行列存储结构对比

以上是比较理论的认知,稍显抽象,让我们来看一个典型的例子,从具体的例子详细分析基于行存储和基于列存储的数据获取方式上的差异。

首先,让我们来创建测试环境数据库ColumnStoreDB,所需要使用到的表dbo.AutoType和dbo.SalesOrder,以及相应的数据初始化。为了照顾到后面《解决列存储索引表只读问题》章节,我将dbo.SalesOrder创建为分区表。

-- Create testing database IF DB_ID('ColumnStoreDB') IS NULL CREATE DATABASE ColumnStoreDB; GO USE ColumnStoreDB GO IF OBJECT_ID('dbo.AutoType', 'U') IS NOT NULL BEGIN DROP TABLE dbo.AutoType END GO -- create demo table autoType CREATE TABLE dbo.AutoType ( AutoID INT IDENTITY(101,1) NOT NULL PRIMARY KEY, Make VARCHAR(20) NOT NULL, Model VARCHAR(20) NOT NULL, Color VARCHAR(15) NOT NULL, ModelYear SMALLINT NOT NULL ); -- data init INSERT INTO dbo.AutoType SELECT 'Ford', 'Explorer', 'white', 2003 UNION ALL SELECT 'Satum', 'Lon', 'blue', 2003 UNION ALL SELECT 'Lexus', 'GX460', 'gray', 2010 UNION ALL SELECT 'Honda', 'CRV', 'blue', 2007 UNION ALL SELECT 'Subaru', 'Legacy', 'green', 2008 UNION ALL SELECT 'Honda', 'Civic', 'red', 1996 UNION ALL SELECT 'Nissan', 'Sentra', 'silver', 2012 UNION ALL SELECT 'Chevrolet', 'Tahoe', 'green', 1995 UNION ALL SELECT 'Toyota', 'Celica', 'red', 1992 UNION ALL SELECT 'BMW', 'X5', 'gray', 2002 UNION ALL SELECT 'Subaru', 'Impreze', 'silver', 2011 UNION ALL SELECT 'Volkswagen', 'Jetta', 'black', 1995 UNION ALL SELECT 'Chevrolet', 'Impala', 'red', 2008 UNION ALL SELECT 'Jeep', 'Liberty', 'gray', 2012 UNION ALL SELECT 'Dodge', 'Dakota', 'blue', 2000 ; -- Create PARTITION FUNCTION & SCHEMA CREATE PARTITION FUNCTION pf_SalesYear (datetime) AS RANGE LEFT FOR VALUES ('2013-01-01 00:00', '2014-01-01 00:00', '2015-01-01 00:00', '2016-01-01 00:00', '2017-01-01 00:00', '2018-01-01 00:00') ; GO CREATE PARTITION scheme ps_SalesYear AS PARTITION pf_SalesYear ALL TO ([PRIMARY]) ; GO -- create demo table SalesOrder IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL BEGIN DROP TABLE dbo.SalesOrder END GO CREATE TABLE dbo.SalesOrder ( OrderID INT NOT NULL ,AutoID INT NOT NULL ,UserID INT NOT NULL ,OrderQty INT NOT NULL ,Price DECIMAL(8,2) NOT NULL ,UnitPrice AS Price * OrderQty ,OrderDate DATETIME NOT NULL ) ON ps_SalesYear(OrderDate); -- data init for 5 M records. ;WITH a AS ( SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a) ), RoundData AS( SELECT TOP(5000000) OrderID = ROW_NUMBER() OVER (ORDER BY a.a) ,AutoIDRound = abs(checksum(newid())) ,Price = a.a * b.a * 10000 ,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h ) INSERT INTO dbo.SalesOrder(OrderID, AutoID, UserID, OrderQty, Price, OrderDate) SELECT OrderID ,AutoID = cast(ROUND((13 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 101), 0) as int) ,UserID = cast(ROUND((500 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 10000), 0) as int) ,OrderQty ,Price = cast(Price AS DECIMAL(8,2)) ,OrderDate = dateadd(day, -cast(ROUND((1099 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 1), 0) as int) ,'2017-01-10') FROM RoundData; GO

假如目前用户需要获取所有汽车的制造商和相应的制造年份,即查询语句如下:

SELECT Make, ModelYear FROM dbo.AutoType;

那么,对于传统的基于行存储的结构,SQL Server会首先将AutoType这个表所有的数据页(这里假设占用了3 页)载入SQL Server内存缓存中,然后筛选出两个必须的列返回给用户。换句话来说,在返回用户必须的两个字段之前,用户必须等待3个Pages的数据全部加载进入内存(这3个页中包含了无用的其他三个字段),如此势必导致磁盘I/O,内存使用量和CPU开销的浪费,最终必然导致用户的执行时间会被拉长。

相反的,对于列存储结构而言,列中数据是按列式存储的(一个Column Segment只包含某一个列的数据),当用户提交查询以后,系统可以很快的拿到这两个列的值,而无需去获取其他三个多余字段的值。

以上文字描述,可以形象为如下的结构图,图的左上角为行存储结构,图的右上角为列存储结构,图的左下角是行存储结构载入内存的过程,图的右下角是用户需要的最终结果。


【DBA精华系列】SQL Server 2012列存储索引技术

基于以上的分析,我们清楚的知道了基于列存储的结构和数据压缩功能为SQL Server执行查询语句大大节约了I/O消耗和因此而产生的CPU开销。

查询性能影响 基于上一节对列存储索引的特殊存储结构的分析,我们很清楚的知道列存储索引在执行查询过程中节约IOPS的同时,对数据仓库类统计查询语句性能有了非常大的性能提升,当然这里面也避免不了

本文数据库(综合)相关术语:系统安全软件

主题: SQL数据CPUQt算法SQL ServerTI硬盘微软常大
分页:12
转载请注明
本文标题:【DBA精华系列】SQL Server 2012列存储索引技术
本站链接:http://www.codesec.net/view/531224.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(综合) | 评论(0) | 阅读(16)