开发者社区> dbaer> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server 2012 列存储索引分析(翻译)

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

一、概述

 

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

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

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

image

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

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

image

 

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

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

 

二、创建表

 

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

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) 创建表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

 

image

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

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

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

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

GO

image

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

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

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

 

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

 

(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

image

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

(2) 执行查询语句

select count(*), sum(price*quantity) from sales where date = '20110713'

image

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

 

总结

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

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

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

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL:通过增加索引进行SQL查询优化
【实验】 一次非常有意思的SQL优化经历:从30248.271s到0.001s
0 0
【SQL优化/索引失效的几种情况/FIC/OnlineDDL】
【SQL优化/索引失效的几种情况/FIC/OnlineDDL】
0 0
理论:第三章:索引使用的限制条件,sql优化有哪些,数据同步问题(缓存和数据库),缓存优化
理论:第三章:索引使用的限制条件,sql优化有哪些,数据同步问题(缓存和数据库),缓存优化
0 0
SQL 如何建立索引来加快数据库的查询
SQL 如何建立索引来加快数据库的查询
0 0
sql调优之:字符集不一致导致的索引失效案例
生产上有一段sql,大概几百行,做的是inser into …select ;的操作,但是执行了一个多小时都出不来,正常情况下不会这么久,通过一步步定位,最后发现是其中某一段的join 特别慢导致的,后面对这段sql重点分析发现,这里的表竟然没有走索引,对于几十万以上的表之间的连接,若是没有索引那就是灾难性的,加不加索引就好比一个人力三轮与超跑的区别。
0 0
SQL语句如何强制使用索引
SQL语句如何强制使用索引
0 0
SQL调优指南—智能索引推荐
索引优化通常需要依赖运维或开发人员对数据库引擎内部优化和执行原理的深入理解。为优化体验和降低操作门槛,PolarDB-X推出了基于代价优化器的索引推荐功能,可根据查询语句分析并推荐索引,帮助您降低查询耗时,提升数据库性能。
0 0
MySQL:通过增加索引进行SQL查询优化
【实验】 一次非常有意思的SQL优化经历:从30248.271s到0.001s
0 0
数据库原理与应用(SQL Server)笔记 第五章 索引和视图
数据库原理与应用(SQL Server)笔记 第五章 索引和视图
0 0
测试需要掌握的数据库sql知识(四):面试中sql相关必问的问题:连接查询和索引
测试需要掌握的数据库sql知识(四):面试中sql相关必问的问题:连接查询和索引
0 0
+关注
dbaer
擅长SQL Server、MySQL、云架构部署等
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载