比較列存儲索引與行索引

来源: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
    	   
	
  • 
			        	
    
			    	        
    
				    	        移動開發（一）：使用.NET MAUI開發第一個安卓APP
			            	
    
				        	
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        wpf ToggleButton選中效果和一個登錄界面
			            	
    
				        	
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        彈幕樹洞項目功能新增篇
			            	
    
				        	
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第27篇 sqlserver2022詳細安裝步驟
			            	
    
				        	
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        .NET 開源高性能 MQTT 類庫
			            	
    
				        	
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
			            	
    
				        	
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
			            	
    
				        	
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
			            	
    
				        	
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
			            	
    
				        	
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
    
			         	
    
		            
  • 
			        	
    
			    	        
    
				    	        第28篇 如何.net中實現高效可靠數據同步api
			            	
    
				        	
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
    
			         	
    
		            
    • 										 





		        	
                 	
                 			        

    





    
所有分類