為什麼我們一般會在自增列或交易時間列上建立聚集索引？

来源:http://www.cnblogs.com/jh-1110/archive/2016/09/24/5902402.html
-Advertisement-
Play Games

http://www.cnblogs.com/lyhabc/p/3533027.html 一般的交易系統裡面我們都會以自增列或交易時間列作為聚集索引列，因為一般這些系統都是寫多讀少 每天的交易數據會不停的插入到資料庫，但是讀取數據就沒有數據插入那麼頻繁 因為這些系統一般是寫多讀少，所以我們會選擇在自 ...


 http://www.cnblogs.com/lyhabc/p/3533027.html

一般的交易系統裡面我們都會以自增列或交易時間列作為聚集索引列，因為一般這些系統都是寫多讀少

每天的交易數據會不停的插入到資料庫，但是讀取數據就沒有數據插入那麼頻繁

因為這些系統一般是寫多讀少，所以我們會選擇在自增列或交易時間列上建立聚集索引

測試

測試環境：SQLSERVER2012 SP1  WINDOWS7 64位

我們來做一個測試，測試腳本如下：

 1 --測試腳本  插入性能
 2 USE [test]
 3 GO
 4 --建表 以transtime為聚集索引列
 5 CREATE TABLE transtable(tranid INT ,transtime DATETIME)
 6 GO
 7 CREATE CLUSTERED INDEX CIX_transtable ON [dbo].[transtable]([transtime])
 8 GO
 9 
10 --建表 以tranid為聚集索引列
11 CREATE TABLE transtable2(tranid INT ,transtime DATETIME)
12 GO
13 CREATE CLUSTERED INDEX CIX_transtable2 ON [dbo].[transtable2]([tranid])
14 GO
15 
16 
17 ----------------------------------------------------------
18 --先插入測試數據，插入的tranid都為基數
19 DECLARE @i INT
20 SET @i = 1
21 WHILE @i <= 1000000
22     BEGIN 
23         INSERT  INTO [dbo].[transtable]
24                 SELECT  @i , GETDATE()
25         SET @i = @i + 2
26     END
27 --------------------------------------
28 DECLARE @i INT
29 SET @i = 1
30 WHILE @i <= 1000000
31     BEGIN 
32         INSERT  INTO [dbo].[transtable2]
33                 SELECT  @i , GETDATE()
34         SET @i = @i + 2
35     END
36 
37 -------------------------------------------

在transtable表上的transtime（交易時間）上建立聚集索引，在transtable2表上的tranid（交易編號）上建立聚集索引

我們分別在兩個表上插入500000條記錄，插入的時候有個特點，就是插入的tranid都是基數

1 SELECT COUNT(*) FROM [dbo].[transtable]
2 SELECT COUNT(*) FROM [dbo].[transtable2]
3 
4 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
5 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid]

我們創建兩個存儲過程，這兩個存儲過程為插入到表數據

 1 --------------------------------------------
 2 --創建兩個存儲過程
 3 CREATE PROC INSERTTranstable
 4 AS
 5     DECLARE @i INT
 6     SET @i = 1
 7     WHILE @i <= 1000
 8         BEGIN 
 9             IF ( @i % 2 = 0 )
10                 BEGIN
11                     INSERT  INTO [dbo].[transtable]
12                             SELECT  @i ,
13                                     GETDATE()
14                     SET @i = @i + 1
15                 END
16             ELSE
17                 BEGIN
18                     SET @i = @i + 1
19                     CONTINUE 
20                 END
21         END
22 ------------------------------------------
23 CREATE PROC INSERTTranstable2
24 AS
25     DECLARE @i INT
26 SET @i = 1
27 WHILE @i <= 1000
28     BEGIN 
29         IF ( @i % 2 = 0 )
30             BEGIN
31                 INSERT  INTO [dbo].[transtable2]
32                         SELECT  @i ,
33                                 GETDATE()
34                 SET @i = @i + 1
35             END
36         ELSE
37             BEGIN
38              SET @i = @i + 1
39                 CONTINUE 
40             END
41     END
42 ----------------------------

 1 --------------------------------------------
 2 --創建兩個存儲過程
 3 CREATE PROC INSERTTranstable
 4 AS
 5     DECLARE @i INT
 6     SET @i = 1
 7     WHILE @i <= 1000
 8         BEGIN 
 9             IF ( @i % 2 = 0 )
10                 BEGIN
11                     INSERT  INTO [dbo].[transtable]
12                             SELECT  @i ,
13                                     GETDATE()
14                     SET @i = @i + 1
15                 END
16             ELSE
17                 BEGIN
18                     SET @i = @i + 1
19                     CONTINUE 
20                 END
21         END
22 ------------------------------------------
23 CREATE PROC INSERTTranstable2
24 AS
25     DECLARE @i INT
26 SET @i = 1
27 WHILE @i <= 1000
28     BEGIN 
29         IF ( @i % 2 = 0 )
30             BEGIN
31                 INSERT  INTO [dbo].[transtable2]
32                         SELECT  @i ,
33                                 GETDATE()
34                 SET @i = @i + 1
35             END
36         ELSE
37             BEGIN
38              SET @i = @i + 1
39                 CONTINUE 
40             END
41     END
42 -----------------------------


測試腳本，測試一下插入到兩個表的時間

 1 測試插入偶數行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @b-@a
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @d-@c

 1 測試插入偶數行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @b-@a
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @d-@c
 1 測試插入偶數行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @b-@a
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @d-@c

驗證一下偶數的交易編號是否已經插入到兩個表中

1 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
2 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 
1 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
2 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid]

我們看一下時間

第一個表

第二個表

很明顯，第一個表比第二個表快，因為的機器的硬碟是固態硬碟，時間差距不是很大，如果是機械硬碟時間差距會大一些，那麼究竟為什麼會造成這種情況呢?

我們用下圖來解析一下

我們先說第二張表

當交易編號為2的那條記錄插入進來的時候，後面的記錄都需要向後移動，以使交易編號從小到大排序，因為聚集索引建立在交易編號列上

這個移動時間是有開銷的，而且每次偶數交易編號插入到表中，每插入一次就移動一次，而當前面的記錄插入到表中的時候移動的記錄數就越多

例如：tranid：2，transtime：2014-1-26 31：22.180插入到表中的時候後面的記錄都需要移動，而tranid：978，transtime：2014-01-26 00:29:10.830

這條記錄插入到表中的時候，後面需要移動的記錄數就沒有那麼多，總之那個開銷挺大的。。。

 

第一張表的情況

因為第一張表是以交易時間為聚集索引列的，所以無論交易編號是多少，記錄都會插入到表的最後，因為後來的記錄的交易時間肯定比前面的記錄的交易時間大

這樣的話，基本上沒有開銷

現實系統中的情況

實際系統中，新生成的要插入到表中的交易編號是有可能小於當前表中的某條記錄的交易編號的，那麼這時候記錄插入到表中就需要移位（如果聚集索引建立在交易編號上）

如果聚集索引建立在交易時間上，那麼新生成的要插入到表中的交易記錄時間肯定會大於當前表中的任何一條交易記錄的時間

（除非人為修改系統時間造成當前時間比資料庫中的某些記錄的交易時間要早）

總結

前公司的資料庫有些表在自增列，有些表在交易時間列上建立了聚集索引，在交易時間列上建立聚集索引個人覺得很正常

因為在查詢的時候按照交易時間來排序《order by 交易時間》，速度上是很快的，但是除了排序之外還有一個作用就是本文所講到的

插入數據到表中的效率問題

個人覺得一般商場管理系統，油站管理系統都是這類型系統   本文的意見純屬我自己的個人意見，並不一定適合您的系統，如果交易時間的選擇性不是太高的話，那麼可能在交易時間或自增列上建立聚集索引就不是太合適了 我們以前的系統的交易時間的選擇性是挺高的，而且通常查詢都需要按照交易時間排序，那麼聚集索引列建立在交易時間上就是比較好了

 

本次實驗用到的完整腳本

1 --測試腳本  插入性能
  2 USE [test]
  3 GO
  4 --建表 以transtime為聚集索引列
  5 CREATE TABLE transtable(tranid INT ,transtime DATETIME)
  6 GO
  7 CREATE CLUSTERED INDEX CIX_transtable ON [dbo].[transtable]([transtime])
  8 GO
  9 
 10 --建表 以tranid為聚集索引列
 11 CREATE TABLE transtable2(tranid INT ,transtime DATETIME)
 12 GO
 13 CREATE CLUSTERED INDEX CIX_transtable2 ON [dbo].[transtable2]([tranid])
 14 GO
 15 
 16 ----------------------------------------------------------
 17 --先插入測試數據，插入的tranid都為基數
 18 DECLARE @i INT
 19 SET @i = 1
 20 WHILE @i <= 1000000
 21     BEGIN 
 22         INSERT  INTO [dbo].[transtable]
 23                 SELECT  @i , GETDATE()
 24         SET @i = @i + 2
 25     END
 26 --------------------------------------
 27 DECLARE @i INT
 28 SET @i = 1
 29 WHILE @i <= 1000000
 30     BEGIN 
 31         INSERT  INTO [dbo].[transtable2]
 32                 SELECT  @i , GETDATE()
 33         SET @i = @i + 2
 34     END
 35 
 36 -------------------------------------------
 37 SELECT COUNT(*) FROM [dbo].[transtable]
 38 SELECT COUNT(*) FROM [dbo].[transtable2]
 39 
 40 SELECT TOP 10 * FROM [dbo].[transtable] ORDER BY [tranid]
 41 SELECT TOP 10 * FROM [dbo].[transtable2] ORDER BY [tranid] 
 42 
 43 --------------------------------------------
 44 --創建兩個存儲過程
 45 CREATE PROC INSERTTranstable
 46 AS
 47     DECLARE @i INT
 48     SET @i = 1
 49     WHILE @i <= 1000
 50         BEGIN 
 51             IF ( @i % 2 = 0 )
 52                 BEGIN
 53                     INSERT  INTO [dbo].[transtable]
 54                             SELECT  @i ,
 55                                     GETDATE()
 56                     SET @i = @i + 1
 57                 END
 58             ELSE
 59                 BEGIN
 60                     SET @i = @i + 1
 61                     CONTINUE 
 62                 END
 63         END
 64 ------------------------------------------
 65 CREATE PROC INSERTTranstable2
 66 AS
 67     DECLARE @i INT
 68 SET @i = 1
 69 WHILE @i <= 1000
 70     BEGIN 
 71         IF ( @i % 2 = 0 )
 72             BEGIN
 73                 INSERT  INTO [dbo].[transtable2]
 74                         SELECT  @i ,
 75                                 GETDATE()
 76                 SET @i = @i + 1
 77             END
 78         ELSE
 79             BEGIN
 80              SET @i = @i + 1
 81                 CONTINUE 
 82             END
 83     END
 84 -----------------------------
 85 
 86 測試插入偶數行的性能
 87 DECLARE @a DATETIME
 88 DECLARE @b DATETIME
 89 SELECT @a=GETDATE()
 90 EXEC INSERTTranstable
 91 SELECT @b=GETDATE()
 92 SELECT @b-@a
 93 --------------------------------------
 94 
 95 DECLARE @c DATETIME
 96 DECLARE @d DATETIME
 97 SELECT @c=GETDATE()
 98 EXEC INSERTTranstable2
 99 SELECT @d=GETDATE()
100 SELECT @d-@c

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

-Advertisement-
Play Games
更多相關文章
  • HIVE簡明教程
    完整PDF下載：《HIVE簡明教程》 前言 Hive是對於數據倉庫進行管理和分析的工具。但是不要被“數據倉庫”這個詞所嚇倒，數據倉庫是很複雜的東西，但是如果你會SQL，就會發現Hive是那麼的簡單，簡單到甚至不用學就可以使用Hive做出業務需求所需要的東西。 但是Hive和SQL畢竟不同，執行原理、 ...
  • 使用SQL-Server創建一個銀行數據管理系統Ⅰ
    使用SQL-Server創建一個銀行數據管理系統Ⅰ 作者聲明： 剛開始寫博客，難免有些不足的地方，再就是本人初涉軟體開發這一行業，是個不折不扣的小白，文章中肯定也會出現一些錯誤的地方，希望發現錯誤的朋友們可以及時的指出來，不足的地方還請各路大神們多多指教，以便本人參考和學習，多謝。 首先，要創建一個 ...
  • 應用Pentaho Data Integration(Kettle) 6.1 進行數據抽取以及指標計算（四、每日指標的計算下）
    新使用的控制項有：根據欄位的值來改變序列、生成隨機值、獲取系統時間、增加序列 ...
  • 應用Pentaho Data Integration(Kettle) 6.1 進行數據抽取以及指標計算（四、每日指標的計算上）
    每日指標的計算： 每日指標計算腳本是本系統大部分指標的基礎，設計十多個計算指標，以及三十多個取數指標，所以計算過程比較複雜，整個過程如下圖所示，下麵挑比較典型的幾種指標的計算詳細介紹。 1）日指標計算——數據輸入（系統計算參數的輸入）： 日指標計算主要是從2個表控制項進行數據輸入，如下圖所示： 因為每 ...
  • 應用Pentaho Data Integration(Kettle) 6.1 進行數據抽取以及指標計算（三、指標計算參數讀取）
    在本系統中，針對電量監測系統的數據計算是最複雜的，其中需要採用多種轉換以及公式計算，並且需要結合系統預置的一些計算參數以及每個台區的一些補充技術參數進行計算，計算的過程分為5大步驟（轉換）： 1、 根據系統時間獲取到電量監測系統瞬時數據的表名（電量監測系統每天都會生成一個以日期命名的新表）。 2、 ...
  • SQL查詢符合條件的記錄的總數
    1. select count(*) from table; //統計元組個數 2. select count(列名) from table; //統計一列中值的個數 3. select count(*) from table where 欄位 = ""; //符合該條件的記錄總數 4. sql_c ...
  • 應用Pentaho Data Integration(Kettle) 6.1 進行數據抽取以及指標計算（二、異構數據抽取）
    三、組織機構轉換（異構抽取） 上述講到的都是一些通用的表抽取作業程式，中間不做任何轉換，所以過程非常簡單和通用，下麵要開始進入需要數據轉換的部分，首先是從設備信息系統獲取組織機構部分，並轉換到mp平臺上，整個過程與之前的大致相同。 在最後一步導入數據之前會做組織機構類型對應，進入第3個轉換程式。 該 ...
  • MYSQL 開發技巧
    主要涉及：JOIN 、JOIN 更新、GROUP BY HAVING 數據查重/去重 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類