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