文章出處

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

到新公司上班也有一段時間了，感覺現在的自己才開始慢慢學習SQL~

看這篇文章之前，大家可以先看一下我之前寫的一篇文章

聚集索引表插入數據和刪除數據的方式是怎樣的

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

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

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

測試

測試環境：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 -----------------------------

View Code

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

 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

View Code

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

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

View Code

我們看一下時間

第一個表

第二個表

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

我們用下圖來解析一下

我們先說第二張表

當交易編號為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