一、 是否需要冗余列
现在一些项目的数据库设计中,为了提高查询速度,把基本表的一些列也放到了数据表里,导致数据冗余。例如在热表的数据库里,原始数据表Measure_Heat里加了如房间号,单元号,楼号,小区,户主姓名,户主编号等列。以下分析其性能。
测试步骤:
1. 建立相同的表(不包含冗余列,如房间号,单元号,楼号,小区,户主姓名,户主编号,冗余列从Measure_Cjd内Join获取)两个表索引相同。
2. 把Measure_Heat里的数据原封不动插入到新表内,共5326375行
SELECT [id]
,[表地址]
,[上次抄表热量]
,[当前热量]
,[热功率]
,[瞬时流量]
,[累计流量]
,[供水温度]
,[回水温度]
,[温差]
,[累计工作时间]
,[实时时间]
,[采集时间]
,[单价]
,[通讯状态]
,[室温]
,[设定室温]
,[阀门状态]
INTO [HeatMeasure_Weifang].[dbo].[Measure_heat_test]
FROM [HeatMeasure_Weifang].[dbo].[Measure_heat]
测试项
1. 查看两个表占用硬盘大小
原表:大小1260M
新表:915M(缩小比例为(1260-915)/1260= 0.274,也就是缩小了超过四分之一的大小)
2. 查询性能
a. 查询前清除缓存
--查询旧表
DBCC DROPCLEANBUFFERS --关闭缓存,从缓冲池中删除所有缓冲区
DBCC FREEPROCCACHE --关闭缓存,从过程缓冲区删除所有元素
select top 10 * from Measure_heat
结果:
--查询新表
DBCC DROPCLEANBUFFERS --关闭缓存,从缓冲池中删除所有缓冲区
DBCC FREEPROCCACHE --关闭缓存,从过程缓冲区删除所有元素
select top 10 a.*,
b.社区编号,b.楼房编号,b.楼层,b.单元编号,b.房间号, b.户主编号,b.户主姓名
from Measure_heat_Test a left join measure_cjd b
on a.表地址=b.表地址
可见,冗余列在查询时(清空数据库缓存的情况下),的确会提高查询速度(63毫秒VS911毫秒)
b. 不清除缓存,原表0毫秒
新表:13毫秒
3. 插入性能(插入一万条数据批量)
旧表:
insert into Measure_heat
select top 10000 [表地址]
,[上次抄表热量]
,[当前热量]
,[热功率]
,[瞬时流量]
,[累计流量]
,[供水温度]
,[回水温度]
,[温差]
,[累计工作时间]
,[实时时间]
,[采集时间]
,[单价]
,[通讯状态]
,[社区编号]
,[楼房编号]
,[楼层]
,[单元编号]
,[房间号]
,[户主编号]
,[户主姓名]
,[室温]
,[设定室温]
,[阀门状态]
from Measure_heat
新表
insert into Measure_heat_Test
select top 10000 [表地址]
,[上次抄表热量]
,[当前热量]
,[热功率]
,[瞬时流量]
,[累计流量]
,[供水温度]
,[回水温度]
,[温差]
,[累计工作时间]
,[实时时间]
,[采集时间]
,[单价]
,[通讯状态]
,[室温]
,[设定室温]
,[阀门状态]
from Measure_heat_Test
结论:
a. 数据冗余对于查询单表速度是有很大优势的,是多表join的速度的10倍以上,性能不是一个数量级。道理也可以理解,多表join是会扫描多个表,性能肯定有损耗。
b. 除了性能优势,包含冗余列在数据存储空间方面多了27%的硬盘空间。
c. 插入性能,同时插入1w条数据,都在500ms左右,差别不大。
d. 如果把上图Measure_cjd的基本数据,到到缓存内,新表查询应该会有比原表更高的性能。
二、列的类型和范围,比如一些范围小的属性int字段尽量用smallint 或者tinyint,节省磁盘空间和数据跨页的可能行。
比如一下几列,都可以用smallint
节省空间(节省空间(1327-1152)/1327=0.132,也就是节约了10%的空间)
统计使用页数,参考()
SELECT Object_name(i.object_id) AS objectName,
i.[name] AS indexName,
Sum(a.total_pages) AS totalPages,
Sum(a.used_pages) AS usedPages,
Sum(a.data_pages) AS dataPages,
( Sum(a.total_pages) * 8 ) / 1024 AS totalSpaceMB,
( Sum(a.used_pages) * 8 ) / 1024 AS usedSpaceMB,
( Sum(a.data_pages) * 8 ) / 1024 AS dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE i.object_id = Object_id('dbo.Measure_heat')
AND i.index_id <= 1
GROUP BY i.object_id,
i.index_id,
i.[name]
结论,虽然数据行数相同,但是页数减少了。
三、尽量不要允许NULL,除非必要,可以用NOT NULL+DEFAULT代替。
如性别
CREATE TABLE "dbo"."Test"
(
id int PRIMARY KEY,
sex bit DEFAULT (1),
name varchar(40),
age int DEFAULT ((1)),
)