你的位置:首页 > 数据库

[数据库]为什麽我们一般会在自增列或交易时间列上建立聚集索引?


 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 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 --创建两个存储过程 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 测试插入偶数行的性能 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 测试插入偶数行的性能 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] 
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