SQL Server 自2012以来引入了列存储的概念,至今2016对列存储的支持已经是非常友好了。由于我这边线上环境主要是2014,所以本文是以2014为基础的SQL Server 的列存储的介绍。下面我们主要看一下列存储的发展以及一些原理: 列存储的开发是想要处理超大量数据进行分析计算,于是在SQL Server 2012时,SQL Server 引入了列存储索引,用以显著提供高传统数据仓库类型语句的性能,并在SQL Server 2014中做了进一步加强。列存储会将一个列的数据单独存放在一起,所以主要会有以下两个优点。 1:同一个列中的数据的相似性比较高,因此压缩比例会更高。磁盘操作时,磁盘的IO也会相应的降低。当然,当压缩的数据读取到内存后解压会需要额外的CPU。 2:由于数据是按照列进行存储和读取的,因此如果某些列在访问中并不需要,那么实际的操作时也会不访问这些列,那么磁盘IO会进一步降低。 3:由于数据是按照列进行存储和读取的,大批量的数据聚合访问等会较以往的行存储更快。 对于列存储来说,主要来说就是数据仓库这个使用场景了,微软最近几年也是在这个方面频频发力。对于数据仓库来说,CPU,内存,磁盘都可能称为性能的瓶颈,但是我们指导磁盘的操作来说相比内存和CPU性能是最慢的,而列存储恰恰是对IO的性能提升是很大的,列存储会减少磁盘的IO操作,提升运算的效率,特别是大量数据的聚合。当然如果是一些线上的精确查找等操作,列存储并不是最好的选择。 对于这些性能的提升和存储 空间的优化,主要是和列存储的实现原理是分不开的(由于非聚集列存储的功能比较鸡肋,我们就不介绍了,因为有非聚集列存储的表成为了一个只读表): 1:Clustered columnstore inde – 整个表都按照列存储进行组织,直接替代了传统的堆表或者聚集索引,可以自由的进行增删改操作。 2:聚集列存储索引虽然相对于非聚集列存储索引在column store这块组织架构基本一样,但是它可以进行增删改操作。原因是它多了一块或者多块行存储部分,这部分称之为delta tore。 新插入的数据是直接加载到delta store中的删除操作只是将数据标识为删除,实际的删除需要在rebuild时完成。更新操作会拆分为一个删除操作和一个插入合并完成。 如果一个bulk insert的批次插入的量小于100000,那么数据会加载到delta store中,否则会加载到columnstore中。 当delta store中数据量超过100 0000后,“Tuple mover” 会将其中数据进行归总放置到column store中。贴一个官方图,方便更好的理解聚集列存储: 原理大概看完之后,下面给出SQL Server2014对列存储的改进: ● 支持数据的读和写 其实我在SQL Server 2014列存储的实践当中,还发现有几个不是非常友好的地方 1:SQL Server 2014聚集列存储并不支持视图功能,这个还是比较坑的,因为列存储的主要应用场景就是数据仓库,有很多视图来说要提供报表或者提供给报表部门查询权限,通过视图能够隐藏很多敏感信息,而不支持视图就会很难做决定来具体修改为列存储了 2:SQL Server 2014聚集列存储并不支持alwayson从库的查询 3:SQL Server 2014 12.0.2版本对列存储有漏洞,alwayson日志同步的时候容易造成内部锁争用,影响主从的同步,这点功能我们可能要升级SP1补丁才能解决,我这边从库升级后至今没有出现这个问题,这也是SQL Server 2014列存储的一个BUG吧 以上也算是在生产环境走过的坑,因为考虑不是很周全走了不少路。希望大家能够引以为戒。除了以上几个坑以外,列存储还不支持以下的功能: 在列存储索引中不可使用以下数据类型: 接下来我们看一下列存储的一些实践: 1:创建列存储的表 CREATE TABLE maxiangqian( id [int] NOT NULL, age [int] NOT NULL, sex [tinyint] NOT NULL, name varchar(20)); GO CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON maxiangqian; GO : 2:行聚集索引转换为列存储: CREATE TABLE maxiangqian( id [int] NOT NULL, age [int] NOT NULL, sex [tinyint] NOT NULL, name varchar(20)); GO CREATE CLUSTERED INDEX cl_simple ON maxiangqian (id); GO CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON maxiangqian WITH (DROP_EXISTING = ON); 或者说我们也可以直接删除聚集索引,然后再 效果是一样一样的。 3 将一个堆表转化为列存储表: 第一步就是删除堆表现有的索引,然后创建聚集列存储索引: CREATE TABLE maxiangqian( id [int] NOT NULL, age [int] NOT NULL, sex [tinyint] NOT NULL, name varchar(20)); GO create index pid on maxiagnqian(id)drop index pid on maxiangqian CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON maxiangqian; GO : 上面基本上已经满足你建立列存储的一些功能,下面我们看一下怎么把一个聚集列存储的表转化为普通表: CREATE CLUSTERED INDEX pid ON maxiangqian WITH ( DROP EXISTING = ON ); 或者DROP INDEX cci_Simple ON MyFactTable; OK,我们基本上已经可以知道怎么创建列存储索引了 但是我们指导由于列存储删除的时候只是标记,所以说列存储如果经常更新删除,碎片还是会很大的,下面我们看下怎么消除碎片---重建: CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON maxiagnqian WITH ( DROP_EXISTING = ON ); ALTER INDEX cci_Simple ON maxiangqian REBUILD PARTITION = ALL WITH ( DROP_EXISTING = ON ); 以上两种方式是都可以实现的。 其实对于列存储来说,卧铺,我这边给我比较大的惊喜就是磁盘空间的节约,列存储的压缩比例可以达到10:1甚至15:1,而且相对来说对于我数据仓库一些大批量的聚合操作性能提升。在节省空间又提高性能的情况下,你还有什么理由不选用列存储呢。 |
|