求知 文章 文库 Lib 视频 iProcess 课程 认证 咨询 工具 讲座 Modeler   Code  
会员   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
     
   
分享到
SQL Server datetime数据类型设计与优化误区
 

发布于2013-3-11

 

摘要:如果是SQL Server 2005,那么请你使用smalldatetime吧,数据能节约一半,虽然查询的时候看起来没什么改变;如果你是SQL Server 2008,那么请你使用date吧

一、场景

在SQL Server 2005中,有一个表TestDatetime,其中Dates这个字段的数据类型是datetime,如果你看到表的记录如下图所示,你最先想到的是什么呢?

(图1:数据列表)

你看到这些数据,是不是觉得这样的设计既浪费了存储空间,又使得这个列的索引增大,查询起来更慢,你也想使用一些其它的数据类型来代替这个datetime吧?

其实大家都是这么想的,这个方向是100%正确的,但是在写这篇文章以前,我进入了两个误区:(如果你中了下面的两个误区,那么请你看看这篇文章吧。)

误区一:把Dates字段的datetime数据类型换成smalldatetime,这样数据就由:‘2009-04-09 00:00:00.000’变为‘2009-04-09 00:00:00’,这个看起来没有减少多少存储空间哦。

误区二:把Dates字段的datetime数据类型换成char(10),这样数据就由:‘2009-04-09 00:00:00.000’变为‘2009-04-09’,这好像能减少很多存储空间哦。

二、分析

在SQL Server 2005版本中保存日期的数据类型只有两种:datetime、smalldatetime,但是在SQL Server 2008版本中新增了一些日期数据类型:time、date、smalldatetime、datetime、datetime2、datetimeoffset,其中的date类型就能满足我们场景中的需求了,如果你幸运的在使用SQL Server 2008的话,那么恭喜你,请使用date数据类型吧。

但是我就比较可悲一点了,在使用SQL Server 2005的前提下,我进入了误区一、误区二。其实这也是因为自己忽略了一下基础性的东西,如果知道不同数据类型的存储空间大小,也许就很轻易的避免这样低级的错误了。

其实你查看表TestDatetime中的Dates字段的时候,看到查询结果中的:“-”、“:”只是用于显示的,并不是真实保存的时候就这样格式的。

datetime占用8个字节,前4个字节存储base date(即1900年1月1日)之前或之后的天数,后4个字节存储午夜后的毫秒数。值范围:1753-01-01 到 9999-12-31。

smalldatetime占用4个字节,前2个字节存储base date(1900年1月1日)之后的天数。后2个字节存储午夜后的分钟数。值范围:1900-01-01 到 2079-06-06。

date占用3个字节,它比smalldatetime的前2个字节多了1字节,所以值的范围更广了。值范围:0001-01-01 到 9999-12-31。

所以,如果你使用char(10)来保存截断的日期,那么你的存储空间反而更大了。

结论: 如果是SQL Server 2005,那么请你使用smalldatetime吧,数据能节约一半,虽然查询的时候看起来没什么改变;如果你是SQL Server 2008,那么请你使用date吧,虽然3个字节跟4个字节没有多大的差距,但是从设计上和逻辑清晰度上都有很大的提升,而且差距有些时候并不是1个字节的问题,比如当表数据量达到几个亿的时候,还是有差别的,又或者一条记录可能因为差1个字节就刚刚好给8060字节的页瓜分,这些都不容忽视的。

三、测试

下面我们就从数据存储的大小、索引存储的大小、索引使用时候的速度这几个方面进行测试:(这里只测试数据类型:,,数据的内容都是一样的)

( 一) 测试前奏

1. 创建三种数据类型char(10)、datetime、smalldatetime的表;(表结构如下面SQL)

CREATE TABLE [dbo].[TestDatetime](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Dates] [datetime] NULL, CONSTRAINT [PK_TestDatetime] PRIMARY KEY CLUSTERED

( [Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]

2. 插入相同记录到三个表中;(这里插入1210000条记录)

3. 为[Dates]字段创建索引;(在创建索引的时候可以设置填充因子为100%)

4. 查看索引属性中的索引碎片信息,查看表数据和索引占用的空间,测试[Dates]字段索引的查询效率;

(二) 测试结果

1. 数据存储大小

(图2:数据空间对比)

2. 索引存储信息:

(图3:char(10))

(图4:datetime)

(图5:smalldatetime)

3. 索引查询的情况:

多次执行,SQL Server执行时间为:[char(10)] 大部分在43~59徘徊,偶尔出现小于10的;[datetime]平均在1~2毫秒;[smalldatetime]均在1毫秒;而且大家会发现 [smalldatetime]有其它的9次逻辑读取变为8次了。

--[TestChar10]

SQL Server 分析和编译时间:

CPU 时间= 0 毫秒,占用时间= 1 毫秒。

2200 行受影响)

表'TestChar10'。扫描计数1,逻辑读取9 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

SQL Server 执行时间:

CPU 时间= 0 毫秒,占用时间= 59 毫秒。

SQL Server 执行时间:

CPU 时间= 0 毫秒,占用时间= 1 毫秒。

--[TestDatetime]

SQL Server 分析和编译时间:

CPU 时间= 0 毫秒,占用时间= 1 毫秒。

(2200 行受影响)

表'TestDatetime'。扫描计数1,逻辑读取9 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

SQL Server 执行时间:

CPU 时间= 0 毫秒,占用时间= 2 毫秒。

SQL Server 执行时间:

CPU 时间= 0 毫秒,占用时间= 1 毫秒。

--[TestSmalldatetime]

SQL Server 分析和编译时间:

CPU 时间= 0 毫秒,占用时间= 1 毫秒。

(2200 行受影响)

表'TestSmalldatetime'。扫描计数1,逻辑读取8 次,物理读取0 次,预读0 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

SQL Server 执行时间:

CPU 时间= 0 毫秒,占用时间= 1 毫秒。

SQL Server 执行时间:

CPU 时间= 0 毫秒,占用时间= 1 毫秒。

--SQL Server 2008新数据类型

1. SELECT

2. CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'

3. ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'

4. ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS

5. 'smalldatetime'

6. ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'

7. ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2'

8. ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetime

 
分享到
 
 
     


MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


资料:中国移动深度数据挖掘
不错的资料:数据库设计
分享:Oracle数据库高级技术
资料:数据仓库与数据挖掘
求:数据库架构方面的资料


数据库设计


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号