为什么列存储能够大幅度提高数据的查询性能? - 悦光阴 - 博客园

标签: | 发表时间:2021-05-22 21:34 | 作者:
出处:https://www.cnblogs.com

传统的存储数据的方式是逐行存储(Row Store),每一个Page存储多行数据,而列存储(Column Store)把数据表中的每一列单独存储在Page集合中,这意味着,Page集合中存储的是某一列的数据,而不是一行的所有列的数据。

列存储索引适合于数据仓库中,主要执行大容量数据加载和只读查询,与传统面向行的存储方式相比,使用列存储索引存储可最多提高 10 倍查询性能 ,与使用非压缩数据大小相比,可提供多达 7 倍数据压缩率 。列存储索引使用用“批处理执行模式”的模式,这与行存储使用的逐行数据读取模式对比,性能大幅提升。

列存储索引主要在下面三个特性上提升查询的性能:

  • 行存储使用逐行处理模式,每次只处理一行数据;而列存储索引使用批处理模式,每次处理一批数据行。
  • 行存储是逐行存储(Row Store),每一个Page存储多行数据,而列存储(Column Store)把数据表中的每一列单独存储在Page集合中,这意味着,Page集合中存储的是某一列的数据,而不是一行中所有列的数据。在读取数据时,行存储把一行的所有列都加载到内存,即使有些列根本不会用到;而列存储只把需要的列加载到内存中,不需要的列不会被加载到内存中。
  • 列存储索引自动对数据进行压缩处理,由于同一行的数据具有很高的相似性,压缩率很高,数据读取更快速。

一般情况下,数据仓库的查询语句只会查询少数几个列的数据,其他列的数据不需要加载到内存中,这就使得列存储特别适合用于数据仓库中。

一,列存储的特点

为什么列存储能够大幅度提高数据的查询性能呢?要回答这个问题,首先必须明白SQL Server引擎是怎样读取数据的。在读取数据时,SQL Server每次都把所需数据所在的整个Page读取到内存中,Page是数据读取的最小单位。如果采用行存储,每一个Page都存储所有列的数据,每行的Size决定了单个Page能够存储的数据行数量。

我们可以粗略计算一下,如果一个数据行有10列,每列的平均Size是10B,一行的Size是100B,那么单个Page最多存储80行(8060B/100B);如果采用列存储模式,那么单个Page可以存储806行(8060B/10B)。就单个Page存储的数据行数量而言,列存储是行存储的10倍,SQL Server引擎把一个Page读取到内存中,能够获取的数据行数量成10倍增加。

因此,采用列存储模式时,每一个Page能够存储更多的数据行。在加载列存储数据时,SQL Server只需要消耗少量的IO,就能把某一列的全部数据加载到缓存中。当从列很多的大表中读取几个列时,相比传统的行存储(Row Store)模式,列存储(Column Store)能够成千上万倍地提高数据的读取速度和查询性能。

二,列存储的物理实现

数据表(堆,B-Tree)以行存储模式存储数据,而列存储索引以列存储模式存储数据,行存储和列存储的示例图:

1,列存储的优点

对于列存储,列C1…C6 存储在不同的Page组中,列存储的有点是:

  • 列存储是把每一列都单独存储在Pages集合中,对于行存储,哪怕只从数据表中选择(select)一列,SQL Server引擎都把整个数据行所在的Page读取到内存中,而使用列存储索引,仅仅需要把select子句指定的列读取到内存,不需要的列不会被读取;因此,如果一个查询请求只需要从少量的几个列中获得数据,列存储能够大幅度提高查询性能;
  • 由于单个数据列的数据冗余度更高,因此同一列的数据更容易被压缩存储,单个Page存储更多的数据;
  • 缓存命中率提高,这是因为同一列的数据被高度压缩,常用的Page被频繁访问而变得异常活跃,Buffer Manager把活跃的数据页缓存到内存中,不常用的Page被换出(Page Out)。
  • 更高级的查询执行技术,列存储模式读取数据使用的是批处理模式(Batch Processing Mode),相对于传统的行处理技术,查询性能更高。

2,列存储模式的物理实现

SQL Server引擎分三步实现列存储:

  • step1,列存储索引先把数据表的所有数据行分组,每个分组也称作行组(Row Groups)。
  • step2,在每个行组中,每列的所有数据行构成一个列段(Column Segment),简称段。
  • step3,对每个段进行压缩处理和编码,每个段都单独存储在列存储索引中。

3,编码和压缩

列存储使用两种编码类型:基于字典(dictionary based)和基于值(value based),使用Vertipaq压缩数据。

字典编码是把唯一值编入字典,每一个唯一值都匹配一个序号,而序号用于索引字典,通过存储序号来压缩数据。如果数据表中存在大量的重复值,那么使用字典编码压缩率高。

值编码用于整数类型,或小数类型,编码的原理是把Value的范围按照比例缩小或增大,并使用一个指数(exponent)来表示比例。如果整数(integer) 或小数(decimal)的值分布集中,那么使用基于值(value-based)编码方法进行压缩非常高效。

列存储索引的物理存储如下图所示:

 

三,列存储索引

SQL Server 2012开始引入列存储模式,用户通过创建列存储索引(Column Store Index)来体验列存储模式带来的性能提升。而列存储模式非常适用于星型连接(Star- Join)类型的聚合查询,所谓星型连接(Star-Join)的聚合查询是指对一个大表(Large Table)和多个小表(Little Table)进行连接,并对Large Table 进行聚合查询。在数据库仓库中,是指事实表和维度表的连接。

在大表上创建列存储索引,SQL Server 引擎将充分使用批处理模式(Batch processing mode)来执行星型查询,获取更高的查询性能。

典型的Star- Join的聚合查询类似于下面的示例脚本:

selectlt.Grouping_Columns,
        AggregationFunction(bt.Columns)fromdbo.LittleTable ltwith(nolock)innerjoindbo.BitTable btwith(nolock)onlt.Int_Col1=bt.Int_col1where....groupbylt.Grouping_Columns

在SQL Server 2012中,只能创建非聚集的列存储索引,由于列存储索引的每一列都有独立的存储空间(Page Set),因此,列存储索引会包含数据表的所有列,这样,每一个数据列都会被索引到。但是,并不是每一列都能获得的相同的性能提升,这是因为,列存储使用的压缩算法对于具有大量重复值的字符或数值的数据,压缩效率更高。对于列存储索引而言,查询性能的提升很大程度上依赖列数据的高度压缩,这会大幅减少存储该列数据所占用的数据页(Data Page),进而大幅减少把数据加载到内存所耗费的内存和时间。

CREATE[NONCLUSTERED]COLUMNSTOREINDEXindex_nameONschema_name . table_name (column[,...n])[WITH ( DROP_EXISTING = { ON | OFF } | MAXDOP = max_degree_of_parallelism )][ON  partition_scheme_name ( column_name )  | filegroup_name]

一旦表上创建了非聚集的列存储索引,基础表就变成只读的(read-only),不能对基础表做任何更新(insert,update,delete 或merge)操作,如果需要修改数据,那么,首先要禁用列存储索引,然后更新数据,最后重建列存储索引:

ALTERINDEXmycolumnstoreindexONmytable DISABLE;--update mytable --ALTERINDEXmycolumnstoreindexonmytable REBUILD

由于创建或重建列存储索引是IO密集型资源,十分耗费内存资源,因此必须在系统空闲的情况下,更新数据。 

四,列存储索引的空间使用

列存储索引首先把数据分组,然后每个行组中的每个列构成一个段(Segment),每段都是单独存储的,列存储索引占用的存储空间的大小是由所有段占用的硬盘空间的加和。

系统视图:sys.column_store_segments 提供每个段的数据信息,每个段都是每个行组中的一列的数据的集合,例如,如果一个列存储索引分为10个行组,每个行组有15个数据列,那么,该视图将返回150个段。

selecti.object_id,object_name(i.object_id)asobject_name,i.nameasindex_name
    ,i.type_descasindex_type
    ,col_name(i.object_id,ic.column_id)asindex_column_name
    ,sum(s.row_count)asrow_count
    ,sum(s.on_disk_size)/1024/1024ason_disk_size_mbfromsys.column_store_segments sinnerjoinsys.partitions pons.partition_id=p.partition_idinnerjoinsys.indexes ionp.object_id=i.object_idandp.index_id=i.index_idinnerjoinsys.index_columns iconi.object_id=ic.object_idandi.index_id=ic.index_idands.column_id=ic.index_column_idgroupbyi.object_id,i.index_id
    ,i.name
    ,i.type_desc
    ,ic.column_idorderbyi.object_id,i.name
    ,index_column_name
View Code

可以看出,列存储索引中每个段占用的硬盘空间是很少的,加载到内存所需要耗费的时间,IO次数和内存资源也是很少的,再配上性能更高的批处理模式,所以,列存储能够大幅度提高数据的查询性能,特别是对星型聚合的查询。

 

 

推荐阅读:

Columnstore Indexes

SQL Server 2012新特性_列存储索引(1)

SQL Server 2012新特性_列存储索引(2)

SQL Server 2012新特性_列存储索引(3)

相关 [数据 性能 光阴] 推荐:

为什么列存储能够大幅度提高数据的查询性能? - 悦光阴 - 博客园

- -
传统的存储数据的方式是逐行存储(Row Store),每一个Page存储多行数据,而列存储(Column Store)把数据表中的每一列单独存储在Page集合中,这意味着,Page集合中存储的是某一列的数据,而不是一行的所有列的数据. 列存储索引适合于数据仓库中,主要执行大容量数据加载和只读查询,与传统面向行的存储方式相比,使用列存储索引存储可最多提高 10 倍查询性能 ,与使用非压缩数据大小相比,可提供多达 7 倍数据压缩率.

DB2数据库性能优化介绍

- - CSDN博客数据库推荐文章
作者:chszs,转载需注明. 博客主页: http://blog.csdn.net/chszs. 前段时间,我从CSDN得到了这本书《DB2数据库性能调整和优化(第2版)》,这是一本介绍DB2数据库性能调优的书籍,此书覆盖了DB2数据库性能调优所需的全部知识和工具,而且还提供了大量的性能调优的实际案例,颇有一种“一书在手,DB2尽在掌握”的豪情.

浅谈MySQL 数据库性能优化

- - BlogJava-qileilove
数据库是 IO 密集型的程序,和其他数据库一样,主要功能就是数据的持久化以及数据的管理. 本文侧重通过优化MySQL 数据库缓存参数如查询缓存,表缓存,. 日志缓存,索引缓存,innodb缓存,插入缓存,以及连接参数等方式来对MySQL数据库进行优化.   这里先引用一句话,从内存中读取一个数据的时间消耗是微秒级别,而从普通硬盘上读取一个数据是在毫秒级别,二者相差3个数量级.

数据库连接池性能比对

- - 企业架构 - ITeye博客
对现有的数据库连接池做调研对比,综合性能,可靠性,稳定性,扩展性等因素选出推荐出最优的数据库连接池 . NOTE: 本文所有测试均是mysql库.    1:性能方面 hikariCP>druid>tomcat-jdbc>dbcp>c3p0. hikariCP的高性能得益于最大限度的避免锁竞争.    2:druid功能最为全面,sql拦截等功能,统计数据较为全面,具有良好的扩展性.

生成MySQL性能数据分析报告的Shell脚本

- EricSheng - MySQLOPS 数据库与运维自动化技术分享
【导读】 MySQL开源产品有非常丰富的分享出来可用的资源,比如本文要介绍的内容:如何每天简单地获得MySQL性能数据的报告,我们就可以利用perl脚本工具mysqlreport生成详细的性能数据分析报告,再借助sendEmail工具以邮件的方式发送出来,从而提高我们的工作效率,上期给大家介绍了SHELL脚本Get_Local_Kpi对我们工作的帮助.

基于SSD的数据库性能优化

- Sungelina - Hello DBA
NOR和NAND都是闪存技术的一种,NOR是Intel公司开发的,它有点类似于内存,允许通过地址直接访问任何一个内存单元,缺点是:密度低(容量小),写入和擦除的速度很慢. NAND是东芝公司开发的,它密度高(容量大),写入和擦除的速度都很快,但是必须通过特定的IO接口经过地址转换之后才可以访问,有些类似于磁盘.

MySQL 数据库性能优化之表结构

- tangfl - Sky.Jian 朝阳的天空
接着上一篇 MySQL 数据库性能优化之缓存参数优化 ,这是 MySQL数据库性能优化专题 系列的第二篇文章:MySQL 数据库性能优化之表结构. 很多人都将 数据库设计范式 作为数据库表结构设计“圣经”,认为只要按照这个范式需求设计,就能让设计出来的表结构足够优化,既能保证性能优异同时还能满足扩展性要求.

MySQL 数据库性能优化之缓存参数优化

- flychen50 - Sky.Jian 朝阳的天空
在平时被问及最多的问题就是关于 MySQL 数据库性能优化方面的问题,所以最近打算写一个MySQL数据库性能优化方面的系列文章,希望对初中级 MySQL DBA 以及其他对 MySQL 性能优化感兴趣的朋友们有所帮助. 这是 MySQL数据库性能优化专题 系列的第一篇文章:MySQL 数据库性能优化之缓存参数优化.

MySQL数据库性能优化之存储引擎选择

- - Sky.Jian 朝阳的天空
MySQL 数据库性能优化之SQL优化,这是  MySQL数据库性能优化专题 系列的第五篇文章:. MySQL数据库性能优化之存储引擎选择. 离上一篇文章已经有很长时间没有更新这个MySQL数据库性能优化专题了,时间太紧加上人之惰性,今天这里将之前就规划好的关于存储引擎选择方面的内容更新出来,希望对大家有所帮助吧.

高性能key-value数据库nessDB介绍

- - NoSQLFan
nessDB是一个小巧、高性能、可嵌入式的key/value存储引擎,使用标准C开发,支持Linux, *BSD, OS X and Solaris等系统,无第三方库依赖. 本文来自nessDB作者@ BohuTANG 的投稿分享,推荐给大家. 同时nessDB还提供一个服务端,支持Redis的 PING, SET, MSET, GET, MGET, DEL, EXISTS, INFO, SHUTDOWN 命令,您可以使用任何一款Redis客户端来连接和操作nessDB.