你的位置:首页 > 软件开发 > 数据库 > 为什麽我们一般会在自增列或交易时间列上建立聚集索引?

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

发布时间:2016-09-24 09:00:10
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 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 -------------------------------------------

验证一下偶数的交易编号是否已经插入到两个表中

为什麽我们一般会在自增列或交易时间列上建立聚集索引?
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 交易时间》,速度上是很快的,但是除了排序之外还有一个作用就是本文所讲到的

插入数据到表中的效率问题

个人觉得一般商场管理系统,油站管理系统都是这类型系统

原标题:为什麽我们一般会在自增列或交易时间列上建立聚集索引?

关键词:时间

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。