MyException - 我的异常网
当前位置:我的异常网» Sql Server » 为什麽小弟我们一般会在自增列或交易时间列上建立聚

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

www.MyException.Cn  网友分享于:2013-09-28  浏览:0次
为什麽我们一般会在自增列或交易时间列上建立聚集索引?

 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 @[email protected]
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @[email protected]

 1 测试插入偶数行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @[email protected]
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @[email protected]
 1 测试插入偶数行的性能
 2 DECLARE @a DATETIME
 3 DECLARE @b DATETIME
 4 SELECT @a=GETDATE()
 5 EXEC INSERTTranstable
 6 SELECT @b=GETDATE()
 7 SELECT @[email protected]
 8 --------------------------------------
 9 
10 DECLARE @c DATETIME
11 DECLARE @d DATETIME
12 SELECT @c=GETDATE()
13 EXEC INSERTTranstable2
14 SELECT @d=GETDATE()
15 SELECT @[email protected]

[email protected]

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 @[email protected]
 93 --------------------------------------
 94 
 95 DECLARE @c DATETIME
 96 DECLARE @d DATETIME
 97 SELECT @c=GETDATE()
 98 EXEC INSERTTranstable2
 99 SELECT @d=GETDATE()
100 SELECT @[email protected]

文章评论

2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
为什么程序员都是夜猫子
为什么程序员都是夜猫子
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
中美印日四国程序员比较
中美印日四国程序员比较
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
漫画:程序员的工作
漫画:程序员的工作
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
每天工作4小时的程序员
每天工作4小时的程序员
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
团队中“技术大拿”并非越多越好
团队中“技术大拿”并非越多越好
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
代码女神横空出世
代码女神横空出世
如何成为一名黑客
如何成为一名黑客
旅行,写作,编程
旅行,写作,编程
老程序员的下场
老程序员的下场
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
程序员都该阅读的书
程序员都该阅读的书
一个程序员的时间管理
一个程序员的时间管理
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
程序员的鄙视链
程序员的鄙视链
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
程序员必看的十大电影
程序员必看的十大电影
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
那些争议最大的编程观点
那些争议最大的编程观点
Java程序员必看电影
Java程序员必看电影
程序员应该关注的一些事儿
程序员应该关注的一些事儿
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
编程语言是女人
编程语言是女人
10个调试和排错的小建议
10个调试和排错的小建议
程序员和编码员之间的区别
程序员和编码员之间的区别
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有