到新公司上班也有一段時間了，感覺現在的自己才開始慢慢學習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 GO13 CREATE CLUSTERED INDEX CIX_transtable2 ON [dbo].[transtable2]([tranid])14 GO15 16 17 ----------------------------------------------------------18 --先插入測試數據，插入的tranid都為基數19 DECLARE @i INT20 SET @i = 121 WHILE @i <= 100000022     BEGIN 23         INSERT  INTO [dbo].[transtable]24                 SELECT  @i , GETDATE()25         SET @i = @i + 226     END27 --------------------------------------28 DECLARE @i INT29 SET @i = 130 WHILE @i <= 100000031     BEGIN 32         INSERT  INTO [dbo].[transtable2]33                 SELECT  @i , GETDATE()34         SET @i = @i + 235     END36 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                 BEGIN11                     INSERT  INTO [dbo].[transtable]12                             SELECT  @i ,13                                     GETDATE()14                     SET @i = @i + 115                 END16             ELSE17                 BEGIN18                     SET @i = @i + 119                     CONTINUE 20                 END21         END22 ------------------------------------------23 CREATE PROC INSERTTranstable224 AS25     DECLARE @i INT26 SET @i = 127 WHILE @i <= 100028     BEGIN 29         IF ( @i % 2 = 0 )30             BEGIN31                 INSERT  INTO [dbo].[transtable2]32                         SELECT  @i ,33                                 GETDATE()34                 SET @i = @i + 135             END36         ELSE37             BEGIN38              SET @i = @i + 139                 CONTINUE 40             END41     END42 -----------------------------

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

 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 DATETIME11 DECLARE @d DATETIME12 SELECT @c=GETDATE()13 EXEC INSERTTranstable214 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] 

我們看一下時間

第一個表

第二個表

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

我們用下圖來解析一下

我們先說第二張表

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