1. 全栈程序员必看首页

比较列存储索引与行索引

全栈程序员-用户IM • • 未分类

比较列存储索引与行索引

原因：

 

    之前已经写过一篇关于列存储索引的简介http://www.cnblogs.com/wenBlog/p/4970493.html，很粗糙但是基本阐明了列存储索引的好处。为了更好的理解列存储索引，接下来我们一起通过列存储索引与传统的行存储索引地对比2014中的列存储索引带来了哪些改善。由于已经很多介绍列存储，因此这里我仅就性能的改进进行重点说明。

测试场景

    我创建了5个测试，尽量保证测试环境避免来自外界的重负载进而影响到结果。测试结果基于两个独立的表，分别是:

  • FactTransaction_ColumnStore – 这个表仅有一个聚集列存储索引，由于列存储索引的限制，该表不再有其他索引。
  • FactTransaction_RowStore – 该表将包含一个聚集索引和一个非聚集列存储索引和一个非聚集行存储索引。

    首先我用脚本文件创建表和索引，然后用30m行数据填充到三个表中。由于所有的测试我都制定了最大并行度的hint ，因此可以指定内核数量来查询。

测试1-填充表

   为了更好地测试，一个表由列存储索引构成，而另一个表仅有行存储索引构成。填充数据来自于另一个表’FactTransaction’。

IO 和时间统计

 

Table 'FactTransaction_ColumnStore'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FactTransaction'. Scan count 1, logical reads 73462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (30000000 row(s) affected)

SQL Server Execution Times:  CPU time = 98204 ms,  elapsed time = 109927 ms.

Table ' FactTransaction_RowStore '. Scan count 0, logical reads 98566047, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FactTransaction'. Scan count 1, logical reads 73462, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 (30000000 row(s) affected)

SQL Server Execution Times:  CPU time = 111375 ms,  elapsed time = 129609 ms.

 

观察测试
表名 填充时间 逻辑读
FacTransaction_ColumnStore 1.49 mins 0
FacTransaction_RowStore 2.09 mins 98566047

 

测试2-比较搜索

   注意这里在行存储索引上我指定表的hint，迫使表通过索引查找。

-- Comparing Seek.... 
SET Statistics IO,TIME ON

Select CustomerFK
From [dbo].FactTransaction_RowStore WITH(FORCESEEK)
Where transactionSK = 4000000
OPTION (MAXDOP 1)

Select CustomerFK
From [dbo].FactTransaction_ColumnStore  
Where transactionSK = 4000000
OPTION (MAXDOP 1)

SET Statistics IO,TIME OFF

 

IO 和时间统计

Table 'FactTransaction_RowStore'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

Table 'FactTransaction_ColumnStore'. Scan count 1, logical reads 714, physical reads 0, read-ahead reads 2510, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 83 ms.

 

执行计划

<span>比较列存储索引与行索引</span>

观察测试2

正如上图所示，行存储索引表的索引查找远比列存储索引表查询快的多。这主要归因于2014的sqlserver不支持聚集列存储索引的索引查找。执行计划对比图中一个是索引扫描导致更多的逻辑读，因此导致了性能的下降。

表名 索引类型 逻辑读 运行时间
FacTransaction_ColumnStore Column 714 83 ms
FacTransaction_RowStore Row 3 0 ms

 

Test 3 – Comparing SCAN

   注意这次我指定的hint都是索引扫描，当然列存储索引上优化器默认为索引扫描。

-- Comparing Scan.... 
SET Statistics IO,TIME ON

Select CustomerFK
From [dbo].FactTransaction_RowStore WITH(FORCESCAN)
Where transactionSK = 4000000
OPTION (MAXDOP 1)

Select CustomerFK
From [dbo].FactTransaction_ColumnStore WITH(FORCESCAN)
Where transactionSK = 4000000
OPTION (MAXDOP 1)

SET Statistics IO,TIME OFF

 

IO 和时间统计

Table 'FactTransaction_RowStore'. Scan count 1, logical reads 12704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 32 ms,  elapsed time = 22 ms.

Table 'FactTransaction_ColumnStore'. Scan count 1, logical reads 714, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 2 ms.

 

执行计划

<span>比较列存储索引与行索引</span>

观察测试3

   正如之前提到的，索引扫描列存储要比行存储快，俩个逻辑读和运行时间表明列存储索引在大表扫描上是更优的方式，因此更适合于数据仓库的表。

表名 索引类型 逻辑读 运行时间
FacTransaction_ColumnStore Column 714 2 ms
FacTransaction_RowStore Row 12704 22 ms

 

测试4-聚合查询

    测试行存储表使用基于聚集索引。

SET Statistics IO,TIME ON

Select CustomerFK,BrandFK, Count(*)
From [dbo].[FactTransaction_RowStore] WITH(INDEX=RowStore_FactTransaction)
Group by CustomerFK,BrandFK
OPTION (MAXDOP 4)

 

   测试行存储表，使用CustomerFK 和BrandFK的索引。（覆盖索引）

Select CustomerFK,BrandFK, Count(*)
From [dbo].[FactTransaction_RowStore] WITH(INDEX=RowStore_CustomerFK_BrandFK)
Group by CustomerFK,BrandFK
OPTION (MAXDOP 4)

 

    测试行存储索引使用CustomerFK 和BrandFK的列存储索引（覆盖索引）

Select CustomerFK,BrandFK, Count(*) From [dbo].[FactTransaction_RowStore] WITH(INDEX=ColumnStore_CustomerFK_BrandFK) Group by CustomerFK,BrandFK OPTION (MAXDOP 4)

Test on the columnstore table using the Clustered Index.

Select CustomerFK,BrandFK, Count(*)
From [dbo].[FactTransaction_ColumnStore]
Group by CustomerFK,BrandFK
OPTION (MAXDOP 4)

SET Statistics IO,TIME OFF

 

IO 和时间统计

    使用基于聚集索引查询行存储的表。

Table 'FactTransaction_RowStore'. Scan count 5, logical reads 45977, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:  CPU time = 9516 ms,  elapsed time = 2645 ms.

 

   使用行存储的非聚集索引测试行存储表。（覆盖索引）

Table 'FactTransaction_RowStore'. Scan count 5, logical reads 71204, physical reads 0, read-ahead reads 2160, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:  CPU time = 5343 ms,  elapsed time = 1833 ms.

 

 

   使用非聚集列存储索引测试行存储表。（覆盖索引）





Table 'FactTransaction_RowStore'. Scan count 4, logical reads 785, physical reads 7, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:  CPU time = 141 ms,  elapsed time = 63 ms.





 


    使用聚集索引测试列存储表。




Table 'FactTransaction_ColumnStore'. Scan count 4, logical reads 723, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:  CPU time = 203 ms,  elapsed time = 118 ms.





 


执行计划


<span>比较列存储索引与行索引</span>


观察测试4


 


   这里才是列存储索引开始“闪耀”的地方。两个列存储索引的表查询要比传统的航索引在逻辑读和运行时间上性能好得多。






表名
索引使用
索引类型
逻辑读
运行时间




FacTransaction_ColumnStore
ClusteredColumnStore
Column
717
118




FacTransaction_RowStore
RowStore_FactTransaction
Row
45957
2645




FacTransaction_RowStore
RowStore_CustomerFK_BrandFK
Row
71220
1833




FacTransaction_RowStore
ColumnStore_CustomerFK_BrandFK
Column
782
63






 


测试5-比较更新（数据子集）


   这个测试中，我将更新少于100m行数据，占总数据的30分之一。




SET Statistics IO,TIME ON

Update [dbo].[FactTransaction_ColumnStore]
Set    TransactionAmount = 100
Where  CustomerFK = 112
OPTION (MAXDOP 1)

Update [dbo].[FactTransaction_RowStore]
Set    TransactionAmount = 100
Where  CustomerFK = 112

OPTION (MAXDOP 1)

SET Statistics IO,TIME OFF





 


IO 和时间统计


 




Table 'FactTransaction_ColumnStore'. Scan count 2, logical reads 2020, physical reads 0, read-ahead reads 2598, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(913712 row(s) affected)

SQL Server Execution Times:  CPU time = 27688 ms,  elapsed time = 37638 ms.

Table 'FactTransaction_RowStore'. Scan count 1, logical reads 2800296, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(913712 row(s) affected)

SQL Server Execution Times:  CPU time = 6812 ms,  elapsed time = 6819 ms.





 


执行计划


<span>比较列存储索引与行索引</span>


观察测试5


  在这种情况下 ，列存储索引的表要比行存储的更新慢的多。






表名
索引类型
逻辑读
运行时间




FacTransaction_ColumnStore
Column
2020
37638 ms




FacTransaction_RowStore
Row
2800296
6819 ms






 


    注意对于行存储表逻辑读还是要比行存储的要多很多。这是归因于列存储索引的压缩比率更高，因此占用更少的内存。


总结


    列存储索引（包含聚集和非聚集）提供了大量的优势。但是在数据仓库上使用还是要做好准备工作。一种合适地使用情况是非聚集索引不能被更新且禁用对底层表的更新。如果是巨大且没有分区的表，可能存在一个问题，整个表的索引每次都会被重建，因此如果表是巨大的则禁止使用列存储索引。因此必须要有好的分区策略来支持这种索引。


   有几个应用列存储索引的地方：事实表的聚合、Fast Track Data Warehouse Servers、恰当环境SSAS的Cube…


                                                        
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/119773.html原文链接：https://javaforall.cn

		

			【正版授权，激活自己账号】：
			
			Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛
			
		


		

			【官方授权 正版激活】：
			
			官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...
			
		
                        


                        

                        

                            
 赞 (0)


                                                    


                        

                            

                                                                    

                                                                                
                                            全栈程序员-用户IM全栈程序员-用户IM                                        
                                    

                                                                

                                    

                                                                                     0                                         0                                                                            

                                    
                                    

                                        
                                    

                                

                            

                        

                    

                                        






    
    
    
                                            

                            
相关推荐
  • 
        
    
        
            layui框架和vue哪个好_目前流行的9大前端框架[通俗易懂]        
            
    
        
    
                
    
            
                                 layui框架和vue哪个好_目前流行的9大前端框架[通俗易懂]            
        
    
        
    
            
    layui框架和vue哪个好_目前流行的9大前端框架[通俗易懂]Vue(读音/vjuː/，类似于view)是一套用于构建用户界面的渐进式框架。与其它大型框架不同的是，Vue被设计为可以自底向上逐层应用。Vue的核心库只关注视图层，不仅易于上手，还便于与第三方库或既有项目整合。另一方面，当与现代化的工具链以及各种支持类库结合使用时，Vue也完全能够为复杂的单页应用提供驱动。
    
        
    
        
    
                        
    
                                
                    全栈程序员-用户IM                    全栈程序员-用户IM
                
            
    
                                    2022年6月25日
            
    
                            
    
        
    
    
    

    • 

  • 
        
    
        
            php漏洞 乌云,GitHub – grt1st/wooyun_search: 乌云公开漏洞、知识库搜索 search from wooyun.org…        
            
    
        
    
                
    
            
                                 php漏洞 乌云,GitHub – grt1st/wooyun_search: 乌云公开漏洞、知识库搜索 search from wooyun.org…            
        
    
        
    
            
    php漏洞 乌云,GitHub – grt1st/wooyun_search: 乌云公开漏洞、知识库搜索 search from wooyun.org…wooyun_search乌云公开漏洞、知识库搜索0x00.介绍灵感来源于hanc00l的github项目wooyun_publicwooyun_public基于flask或者tornado，而本项目可以布置在apache、nginx等web服务器上。如果你有wooyun的静态网页数据，那么我们可以开始了！整个项目包括两个部分，第一部分是索引，将网页信息存储进数据库;第二部分是搜索，从数据库中查找…
    
        
    
        
    
                        
    
                                
                    全栈程序员-用户IM                    全栈程序员-用户IM
                
            
    
                                    2022年11月1日
            
    
                            
    
        
    
    
    

    • 

  • 
        
    
        
            打开python 报R6034 错误        
            
    
        
    
                
    
            
                                 打开python 报R6034 错误            
        
    
        
    
            
    打开python 报R6034 错误我只在Python3和python2同时在anaconda3下安装出现的问题，后来移除python2也不起作用，找到了这个方法，解决的问题。6034指的是：”AnapplicationhasmadeanattempttoloadtheCruntimelibraryincorrectly.Pleasecontacttheapplication’ssuppor…
    
        
    
        
    
                        
    
                                
                    全栈程序员-用户IM                    全栈程序员-用户IM
                
            
    
                                    2022年10月24日
            
    
                            
    
        
    
    
    

    • 

  • 


  • 
        
    
        
            java设置响应状态码        
            
    
        
    
                
    
            
                                 java设置响应状态码            
        
    
        
    
            
    java设置响应状态码1.各种状态码设置https://blog.csdn.net/daichangxia/article/details/781393242.设置状态码通过HttpServletResponse的方法进行返回相应的状态码，方法有如下：publicvoidsetStatus(intstatusCode)该方法设置一个任意的状态码。setStatus方法接受一个int（状态码）作为参数。如…
    
        
    
        
    
                        
    
                                
                    全栈程序员-用户IM                    全栈程序员-用户IM
                
            
    
                                    2022年5月12日
            
    
                            
    
        
    
    
    

    • 

  • 
        
    
        
            excel导入mysql代码_EXCEL导入Mysql方法「建议收藏」        
            
    
        
    
                
    
            
                                 excel导入mysql代码_EXCEL导入Mysql方法「建议收藏」            
        
    
        
    
            
    excel导入mysql代码_EXCEL导入Mysql方法「建议收藏」在平时的工作学习中，难免会遇到需要把EXCEL表中的数据导入到MYSQL中，比如要把EXCEL中的数据进行核对，或者要把测试用例导入到TestLink中。本人搜集相关的资料并加以实践总结出了以下几种方法：1.使用PHPExcelParserPro软件，但是这个软件为收费软件；2.可将EXCEL表保存为CSV格式，然后通过phpmyadmin或者SQLyog导入，SQLyog导入的方法为：·将…
    
        
    
        
    
                        
    
                                
                    全栈程序员-用户IM                    全栈程序员-用户IM
                
            
    
                                    2022年9月21日
            
    
                            
    
        
    
    
    

    • 

  • 
        
    
        
            var let和const的区别_const声明        
            
    
        
    
                
    
            
                                 var let和const的区别_const声明            
        
    
        
    
            
    var let和const的区别_const声明1.let命令基本语法ES6新增了let命令，用来声明变量。它的用法类似于var，但是所声明的变量，只在let命令所在的代码块内有效。{leta=1varb=2console
    
        
    
        
    
                        
    
                                
                    全栈程序员-用户IM                    全栈程序员-用户IM
                
            
    
                                    2022年8月7日
            
    
                            
    
        
    
    
    

    • 

                        

                    


    	

		
发表回复
您的电子邮箱地址不会被公开。 






 


	

		

                

                    

                    
            






            

                                                

                                                                                                            

                                        关注全栈程序员社区公众号