到新公司上班也有一段時(shí)間了，感覺(jué)現(xiàn)在的自己才開(kāi)始慢慢學(xué)習(xí)SQL~

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

聚集索引表插入數(shù)據(jù)和刪除數(shù)據(jù)的方式是怎樣的

一般的交易系統(tǒng)里面我們都會(huì)以自增列或交易時(shí)間列作為聚集索引列，因?yàn)橐话氵@些系統(tǒng)都是寫(xiě)多讀少

每天的交易數(shù)據(jù)會(huì)不停的插入到數(shù)據(jù)庫(kù)，但是讀取數(shù)據(jù)就沒(méi)有數(shù)據(jù)插入那么頻繁

因?yàn)檫@些系統(tǒng)一般是寫(xiě)多讀少，所以我們會(huì)選擇在自增列或交易時(shí)間列上建立聚集索引

測(cè)試

測(cè)試環(huán)境：SQLSERVER2012 SP1 WINDOWS7 64位

我們來(lái)做一個(gè)測(cè)試，測(cè)試腳本如下：

 1 --測(cè)試腳本  插入性能 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 --先插入測(cè)試數(shù)據(jù)，插入的tranid都為基數(shù)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（交易時(shí)間）上建立聚集索引，在transtable2表上的tranid（交易編號(hào)）上建立聚集索引

我們分別在兩個(gè)表上插入500000條記錄，插入的時(shí)候有個(gè)特點(diǎn)，就是插入的tranid都是基數(shù)

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] 

我們創(chuàng)建兩個(gè)存儲(chǔ)過(guò)程，這兩個(gè)存儲(chǔ)過(guò)程為插入到表數(shù)據(jù)

 1 -------------------------------------------- 2 --創(chuàng)建兩個(gè)存儲(chǔ)過(guò)程 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 -----------------------------

測(cè)試腳本，測(cè)試一下插入到兩個(gè)表的時(shí)間

 1 測(cè)試插入偶數(shù)行的性能 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

驗(yàn)證一下偶數(shù)的交易編號(hào)是否已經(jīng)插入到兩個(gè)表中

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

我們看一下時(shí)間

第一個(gè)表

第二個(gè)表

很明顯，第一個(gè)表比第二個(gè)表快，因?yàn)榈臋C(jī)器的硬盤(pán)是固態(tài)硬盤(pán)，時(shí)間差距不是很大，如果是機(jī)械硬盤(pán)時(shí)間差距會(huì)大一些，那么究竟為什麼會(huì)造成這種情況呢?

我們用下圖來(lái)解析一下

我們先說(shuō)第二張表

當(dāng)交易編號(hào)為2的那條記錄插入進(jìn)來(lái)的時(shí)候，后面的記錄都需要向后移動(dòng)，以使交易編號(hào)從小到大排序，因?yàn)榫奂饕⒃诮灰拙幪?hào)列上

這個(gè)移動(dòng)時(shí)間是有開(kāi)銷(xiāo)的，而且每次偶數(shù)交易編號(hào)插入到表中，每插入一次就移動(dòng)一次，而當(dāng)前面的記錄插入到表中的時(shí)候移動(dòng)的記錄數(shù)就越多

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

這條記錄插入到表中的時(shí)候，后面需要移動(dòng)的記錄數(shù)就沒(méi)有那么多，總之那個(gè)開(kāi)銷(xiāo)挺大的。。。

第一張表的情況

因?yàn)榈谝粡埍硎且越灰讜r(shí)間為聚集索引列的，所以無(wú)論交易編號(hào)是多少，記錄都會(huì)插入到表的最后，因?yàn)楹髞?lái)的記錄的交易時(shí)間肯定比前面的記錄的交易時(shí)間大

這樣的話，基本上沒(méi)有開(kāi)銷(xiāo)

現(xiàn)實(shí)系統(tǒng)中的情況

實(shí)際系統(tǒng)中，新生成的要插入到表中的交易編號(hào)是有可能小于當(dāng)前表中的某條記錄的交易編號(hào)的，那么這時(shí)候記錄插入到表中就需要移位（如果聚集索引建立在交易編號(hào)上）

如果聚集索引建立在交易時(shí)間上，那么新生成的要插入到表中的交易記錄時(shí)間肯定會(huì)大于當(dāng)前表中的任何一條交易記錄的時(shí)間

（除非人為修改系統(tǒng)時(shí)間造成當(dāng)前時(shí)間比數(shù)據(jù)庫(kù)中的某些記錄的交易時(shí)間要早）

總結(jié)

前公司的數(shù)據(jù)庫(kù)有些表在自增列，有些表在交易時(shí)間列上建立了聚集索引，在交易時(shí)間列上建立聚集索引個(gè)人覺(jué)得很正常

因?yàn)樵诓樵?xún)的時(shí)候按照交易時(shí)間來(lái)排序《order by 交易時(shí)間》，速度上是很快的，但是除了排序之外還有一個(gè)作用就是本文所講到的

插入數(shù)據(jù)到表中的效率問(wèn)題

本次實(shí)驗(yàn)用到的完整腳本

  1 --測(cè)試腳本  插入性能  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 --先插入測(cè)試數(shù)據(jù)，插入的tranid都為基數(shù) 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 --創(chuàng)建兩個(gè)存儲(chǔ)過(guò)程 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  @