sql 2012 列存储索引疑问

[shuohuameijiang]

--插入实验数据
select * into DemoTab from sys.sysobjects
Go
--再次重复10次插入，之后数据达到了约 200W条
INSERT INTO demoTab SELECT * FROM dbo.DemoTab
GO 10
--在(id,name,crdate) 列上建 列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX CIX_DemoTab ON DBO.DemoTab (id,name,crdate) 
--对比查询索引列 和 非索引列 的IO，和查询时间。
--查询100W条记录
select TOP 1000000 id,name,crdate,xtype from DemoTab     --12s  xtype不在列存储索引中
select TOP 1000000 id,name,crdate FROM DemoTab		 --11s  
--语句1 （查询的列中有非列存储索引列）结果如下：
--(1000000 行受影响)
--表 'DemoTab'。扫描计数 1，逻辑读取 17714 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读0 次。
--本机查询所用时间是：12S

--语句2 （查询的列中都是列存储索引列）结果如下：
(1000000 行受影响)
表 'DemoTab'。扫描计数 1，逻辑读取 89 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0次。
--本机查询所用时间是：11S

只是IO有较大差异，为什么查询所花费的时间都差不多，是什么原因？
如果时间一样，那这个列存储索引有什么意义？
我分数不多，但还是希望高手能解答。

[shuohuameijiang]

坐等高手答疑

[zjcxc]

官方文档是这样说的 https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-2017 为何要使用列存储索引？ 列存储索引可提供极高的数据压缩级别（通常是传统方法的 10 倍），从而明显降低数据仓库存储成本。 此外，对于分析，它们提供的性能比 btree 索引高出一个量级。 它们是数据仓库和分析工作负载的首选数据存储格式。 从 SQL Server 2016 (13.x)开始，可以使用列存储索引对操作工作负荷执行实时分析。 列存储索引速度较快的原因： 列存储来自同一个域的值，并且通常具有相似的值，从而提高了压缩率。 这可以最大程度地减少或消除系统中的 IO 瓶颈，同时大大减少内存占用量。 较高的压缩率通过使用更小的内存中空间提高查询性能。 反过来，因为 SQL Server 可以在内存中执行更多查询和数据操作，因此可以提高查询性能。 批处理执行可同时处理多个行，通常可将查询性能提高 2-4 倍。 查询通常仅从表中选择几列，这减少了从物理介质的总 I/O。

[zjcxc]

所以核心是两点 1。 降低存储成本 这个体现在 IO 开销会降低，这个在你的实验中已经体现出来了 2。 分析效率会提升 你的实验并没有体现分析这个操作，把你的查询改成下面的再试试

select distinct id,name,crdate,xtype from DemoTab     --12s  xtype不在列存储索引中
select distinct id,name,crdate FROM DemoTab         --11s  

[好记忆不如烂笔头abc]

1.大大节省磁盘空间 2.按字段查询扫描节省IO

[shuohuameijiang]

我还指望这个列存储索引能显著增加查询速度呢，没想到做上面的测试，跟不加一个样，还是要花几乎一样的时间，哪为大神知道怎样在1到2秒查询出千万级别的数据的，请赐教～

[吉普赛的歌]

引用 6 楼 shuohuameijiang 的回复:

我还指望这个列存储索引能显著增加查询速度呢，没想到做上面的测试，跟不加一个样，还是要花几乎一样的时间，哪为大神知道怎样在1到2秒查询出千万级别的数据的，请赐教～

没有哪位大神可以， 也没有哪个数据库可以做到。 不要迷信什么软件技术可以做到神通广大， 大部分只是针对某一种特定的场景而设计的功能。 一次性显示一千万条记录， 对计算机和数据库来说那不是技术， 是体力劳动。 所有索引的功能， 都无非是过滤， 减少数据量。万里挑一，而不是万条出万。 你却反其道而行之， 大数据量还来强求速度， 这就勉为其难了。 你按邹大哥的， 把焦点转向分析类功能吧