比較列存儲索引與行索引

来源:http://www.cnblogs.com/wenBlog/archive/2016/07/18/5682024.html
-Advertisement-
Play Games

原因： 之前已經寫過一篇關於列存儲索引的簡介http://www.cnblogs.com/wenBlog/p/4970493.html，很粗糙但是基本闡明瞭列存儲索引的好處。為了更好的理解列存儲索引，接下來我們一起通過列存儲索引與傳統的行存儲索引地對比2014中的列存儲索引帶來了哪些改善。由於已經很 ...


原因：

 

    之前已經寫過一篇關於列存儲索引的簡介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.

 

執行計劃

觀察測試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.

 

執行計劃

觀察測試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.




 


執行計劃




觀察測試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.




 


執行計劃




觀察測試5


  在這種情況下 ，列存儲索引的表要比行存儲的更新慢的多。






表名
索引類型
邏輯讀
運行時間




FacTransaction_ColumnStore
Column
2020
37638 ms




FacTransaction_RowStore
Row
2800296
6819 ms






 


    註意對於行存儲表邏輯讀還是要比行存儲的要多很多。這是歸因於列存儲索引的壓縮比率更高，因此占用更少的記憶體。


總結


    列存儲索引（包含聚集和非聚集）提供了大量的優勢。但是在數據倉庫上使用還是要做好準備工作。一種合適地使用情況是非聚集索引不能被更新且禁用對底層表的更新。如果是巨大且沒有分區的表，可能存在一個問題，整個表的索引每次都會被重建，因此如果表是巨大的則禁止使用列存儲索引。因此必須要有好的分區策略來支持這種索引。


   有幾個應用列存儲索引的地方：事實表的聚合、Fast Track Data Warehouse Servers、恰當環境SSAS的Cube…



              
	   




	

	
您的分享是我們最大的動力!




			 	

	
	

    


-Advertisement-
Play Games
     


	


    

    

    




	       	  

              
更多相關文章  

              

                   
    


				
  • 
				
    
			    	
    
				    	狀態欄動畫切換效果
			    	
    
					
    狀態欄動畫切換效果 效果 源碼 https://github.com/YouXianMing/iOS-Project-Examples 中的 StatusBarAnimation ...
    
				
    
		    
  • 
				
    
			    	
    
				    	二、iOS開發中常用的單例（持續更新）
			    	
    
					
    1、UIApplication(應用程式實例) 獲取方式：[UIApplication sharedApplication] 詳細：http://www.cnblogs.com/hissia/p/5678518.html 2、NSNotificationCenter(消息中心) 獲取方式：[NSNo ...
    
				
    
		    
  • 
				
    
			    	
    
				    	user_tab_columns和user_tab_cols的區別
			    	
    
					
    user_tab_columns來源於user_tab_cols，user_tab_cols where hidden_column='NO'，引自：http://blog.csdn.net/gumengkai/article/details/50823140 user_all_tables 是 u ...
    
				
    
		    
  • 
				
    
			    	
    
				    	我想知道為啥會產生兩條相同的班級
			    	
    
					
    我執行下邊的sql語句 得到下麵結果 誰能告訴我那一步沒有去重嗎？ ...
    
				
    
		    
  • 
				
    
			    	
    
				    	實踐和感悟 - scala向下轉型和減少窮舉
			    	
    
					
    工作中的問題總結： 問題一：scala 之向下轉型 引言:假如在複雜的業務邏輯中，變數的類型不能確認，只能給個介面類型，這樣數據類型推導不會錯誤，但是後面要使用實現類的類型時，你卻發現轉不過來了？ 對於這樣的一個問題，scala可以這樣解決： 首先建造一個介面，People： 這樣定義了一個介面，接 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	mysql-sql高級應用
			    	
    
					
    sql語言進階 典型操作 order by - select * from play_list order by createtime; - select * from play_list order by bookedcount desc,createtime asc; order by 語句用於 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	資料庫設計--實踐
			    	
    
					
    目標與目的 學習目標： 掌握日常資料庫建表操作 瞭解表設計的規範 何為表結構設計： 表結構設計需要在正式進行開發之前完成 根據產品需求將複雜的業務模型抽象出來 項目分析 實踐課程模型： 類似電商類的網站 用戶： 核心業務 非核心業務 核心業務： 交易相關 訂單詳情，訂單 商品相關 商品類別，商品 非 ...
    
				
    
		    
  • 
				
    
			    	
    
				    	對於資料庫的想法
			    	
    
					
    memcatch 做單純的日活數據，或者靜態數據緩存 redis 做日活數據？ mongodb 不建索引，用以插入數據？ mongodb 建立索引，用以緩存用於讀取的歷史不活躍數據？ mysql 數據持久化存儲 應該建立一個資料庫CRUD框架，然後對外是封裝好的？ 總覺得已經有搭配好的框架了。 ...
    
				
    
		    
    • 
				 
		  
  
                  

              

          



		
        

        
        

            
 







    
一周排行

    

         
    

 
    -Advertisement-
    Play Games
    	   
	
  • 
			        	
    
			    	        
    
				    	        C#自增運算符詳解：++i與i++的區別及應用場景
			            	
    
				        	
    概述：在C#中，++i和i++都是自增運算符，其中++i先增加值再返回，而i++先返回值再增加。應用場景根據需求選擇，首碼適合先增後用，尾碼適合先用後增。詳細示例提供清晰的代碼演示這兩者的操作時機和實際應用。 在C#中，++i 和 i++ 都是自增運算符，但它們在操作上有細微的差異，主要體現在操作的 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET 版本
			            	
    
				        	
    上次發佈了：Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET Core 版本，今天計劃準備壓測一下 .NET 版本，來測試並記錄一下 Taurus.MVC 框架在 .NET 版本的性能，以便後續持續優化改進。
為了方便對比，本文章的電腦環境和測試思路，儘量和... ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        一文讀懂.NET WebAPI中FromRoute、FromQuery、FromBody的關鍵角色與用法
			            	
    
				        	
    .NET WebAPI作為一種構建RESTful服務的強大工具，為開發者提供了便捷的方式來定義、處理HTTP請求並返迴響應。在設計API介面時，正確地接收和解析客戶端發送的數據至關重要。.NET WebAPI提供了一系列特性，如[FromRoute]、[FromQuery]和[FromBody]，用 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        WPF/C#實現圖像濾鏡優化方案：打造炫目視覺體驗！
			            	
    
				        	
    原因：我之所以想做這個項目，是因為在之前查找關於C#/WPF相關資料時，我發現講解圖像濾鏡的資源非常稀缺。此外，我註意到許多現有的開源庫主要基於CPU進行圖像渲染。這種方式在處理大量圖像時，會導致CPU的渲染負擔過重。因此，我將在下文中介紹如何通過GPU渲染來有效實現圖像的各種濾鏡效果。 生成的效果 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Bogus 實戰：使用 Bogus 和 EFCore 生成模擬數據和種子數據【完整教程】
			            	
    
				        	
    引言 上一章我們介紹了在xUnit單元測試中用xUnit.DependencyInject來使用依賴註入，上一章我們的Sample.Repository倉儲層有一個批量註入的介面沒有做單元測試，今天用這個示例來演示一下如何用Bogus創建模擬數據 ，和 EFCore 的種子數據生成 Bogus 的優 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        WPF隨筆收錄-實時繪製心率曲線
			            	
    
				        	
    一、前言 在自己的項目中，涉及到實時心率曲線的繪製，項目上的曲線繪製，一般很難找到能直接用的第三方庫，而且有些還是定製化的功能，所以還是自己繪製比較方便。很多人一聽到自己畫就害怕，感覺很難，今天就分享一個完整的實時心率數據繪製心率曲線圖的例子；之前的博客也分享給DrawingVisual繪製曲線的方 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        wpf程式修改啟動入口註意事項
			            	
    
				        	
    如果你在自定義的 Main 方法中直接使用 App 類並啟動應用程式，但發現 App.xaml 中定義的資源沒有被正確載入，那麼問題可能在於如何正確配置 App.xaml 與你的 App 類的交互。 確保 App.xaml 文件中的 x:Class 屬性正確指向你的 App 類。這樣，當你創建 Ap ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        記一次 .NET某爐膛鍋爐檢測系統 崩潰分析
			            	
    
				        	
    一：背景 1. 講故事 上個月有個朋友在微信上找到我，說他們的軟體在客戶那邊隔幾天就要崩潰一次，一直都沒有找到原因，讓我幫忙看下怎麼回事，確實工控類的軟體環境複雜難搞，朋友手上有一個崩潰的dump，剛好丟給我來分析一下。 二：WinDbg分析 1. 程式為什麼會崩潰 windbg 有一個厲害之處在於 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        一個.NET內置依賴註入的小型強化版
			            	
    
				        	
    前言 .NET生態中有許多依賴註入容器。在大多數情況下，微軟提供的內置容器在易用性和性能方面都非常優秀。外加ASP.NET Core預設使用內置容器，使用很方便。 但是筆者在使用中一直有一個頭疼的問題：服務工廠無法提供請求的服務類型相關的信息。這在一般情況下並沒有影響，但是內置容器支持註冊開放泛型服 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        WPF隨筆收錄-DataGrid固定右側列
			            	
    
				        	
    一、前言 在項目開發過程中，DataGrid是經常使用到的一個數據展示控制項，而通常表格的最後一列是作為操作列存在，比如會有編輯、刪除等功能按鈕。但WPF的原始DataGrid中，預設只支持固定左側列，這跟大家習慣性操作列放最後不符，今天就來介紹一種簡單的方式實現固定右側列。（這裡的實現方式參考的大佬 ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類