本文出处:http://www.cnblogs.com/wy123/p/6677073.html
在做数据统计类数据库设计的时候,在考虑数据存储的时候,经常会遇到逻辑上同一个 BusinessID 对应多个数据点的情况,比如工资表中的员工 ID 以及各项工资信息,财务表中的各个报表 Id 和多个数据点之间的信息面对这种情况,如何来设计表结构,是横表,还是竖表,各有那些优缺点,本文将做一个粗浅的分析。
横标和竖表的表现形式
日常生活中也有很多类似的例子,先用一个 Excel 画一个例子,比如工资表这么做就是 "横表",特点是,一个 ID 对应所有的值信息,以行 Key-Value1-Value2-Value3 的方式存储
如下是竖表(纵表),特点是每行仅存储该 ID 的某一个类别字段的值,以行的方式存储 Key-Value 的方式存储
横标和竖表的设计示例
下面通过一个具体的例子来说明横标和竖表的一些特点
- --横标
- CREATE TABLE HorizontalTable
- (
- Id int identity(1,1),
- BusinessId varchar(50) ,
- CategoryVal1 varchar(20) ,
- CategoryVal2 decimal(20,5) ,
- CategoryVal3 datetime ,
- CategoryVal4 varchar(20) ,
- CategoryVal5 varchar(20) ,
- CategoryVal6 varchar(20)
- )
- insert intoHorizontalTablevalues('BH000001','value1',89.12,'20170406','abc4','abc5','abc6')
- insert intoHorizontalTablevalues('BH000002','value2',99.11,'20170407','abc4','abc5','abc6')
- --竖表
- CREATE TABLE VerticalTable
- (
- Id int identity(1,1),
- BusinessId varchar(50),
- CategoryKey varchar(20),
- Val varchar(20)
- )
- insert intoVerticalTablevalues('BH000001','CategoryKey1','values1')
- insert intoVerticalTablevalues('BH000001','CategoryKey2',89.12)
- insert intoVerticalTablevalues('BH000001','CategoryKey3','20170406')
- insert intoVerticalTablevalues('BH000001','CategoryKey4','abc4')
- insert intoVerticalTablevalues('BH000001','CategoryKey5','ab5')
- insert intoVerticalTablevalues('BH000001','CategoryKey6','ab6')
- insert intoVerticalTablevalues('BH000002','CategoryKey1','values2')
- insert intoVerticalTablevalues('BH000002','CategoryKey2',99.12)
- insert intoVerticalTablevalues('BH000002','CategoryKey3','20170407')
- insert intoVerticalTablevalues('BH000002','CategoryKey4','abc4')
- insert intoVerticalTablevalues('BH000002','CategoryKey5','abc5')
- insert intoVerticalTablevalues('BH000002','CategoryKey6','abc6')
横表中的数据:
竖表中的数据
可能实际应用中,要比这个示例中的情况更加复杂,那么在设计表结构的时候,如何选择横标或者竖表?首先来看横标的特点
对于横表 1,同一个 Key 值对应的列是固定的,比如,比如 HorizontalTable 中有 6 个字段 2,各个字段的值是自由的,比如 HorizontalTable 中的 CategoryVal1 是 varchar 类型的,CategoryVal2 是 decimal 的 3,表中并不存储描述性字段本身(相比纵表) 4,相比竖表,存储同样多的数据,行数要少对于竖表 1,同一个 Key 值对应的列是动态的,因为是按照行存储的,可以存储成 Key1—Value1,Key1—Value2,Key1—Value3 的方式存储 2,字段的类型是固定的, 但是类似是要兼容的,不能有个性化的字段,比如 VerticalTable 中的 CategoryKey+Val,因为固定了这么一个字段 3,表中需要存储描述字段本身(相比横标),要根据 BusinessKey 值的不同,重复存储 CategoryKey 4,相比横表,存储同样多的数据,行数要多
综上可以看出,横标的优点:横标的有点事显示的较为清晰直观,同时在字段的选择上更为科学合理,具体的字段可以根据具体情况划分字段类型,横标的缺点:不方便扩展和公用,也就是说设计了一张横标,只能在固定的某一种特定的相对不变的场景下使用, 比如加字段,或者类似的业务想公用一张横表,都有局限
竖表的优点:最大的特点是可以灵活扩展存储的内容,同时具有一定的公用性 因为竖表的存储结构不受字段个数的限制,可以存储具有一定共性的业务数据。竖表的缺点:竖表的字段类型要兼容,比如横标可以根据具体的值设计成 varchar,decimal,datetime 等, 横标为了兼容以上字段类型,只能设计成 varchar 的,可能会浪费一定的空间
横标和竖表主要考虑的是扩展性和共同性,对于显示方式问题,个人认为倒是问题不大,无非是行转列和列转行的问题如下是一个将上述设计的横表转竖表和竖表转横标的示例,也不复杂,因此说,显示的问题不是大问题
- select * from HorizontalTable
- --列转行;WITH HorizontalCET
- AS
- (
- SELECT Id,BusinessId,CategoryVal1,
- cast(CategoryVal2as varchar(20))as CategoryVal2,
- cast(CategoryVal3as varchar(20))as CategoryVal3,
- CategoryVal4,
- CategoryVal5
- FROM HorizontalTable
- )
- SELECT Id,BusinessId,ColumnName,ColumnVal
- FROM HorizontalCET
- UNPIVOT (ColumnVal FORColumnNameIN
- (CategoryVal1,
- CategoryVal2,
- CategoryVal3,
- CategoryVal4,
- CategoryVal5)
- ) tmp
- --列转行
- select * from VerticalTable
- SELECT * FROM
- (
- select BusinessId ,
- CategoryKey,
- Val
- from VerticalTable
- )t
- PIVOT( MIN(Val)FORCategoryKeyIN (CategoryKey1,
- CategoryKey2,
- CategoryKey3,
- CategoryKey4,
- CategoryKey5,
- CategoryKey6)
- )a
关于横表和竖表的性能问题
关于性能问题,很难一概而论,还要结合具体的情况作分析,比如查询方式,查询数据了,索引结构等等都有一定的关系。表面上看,竖表存储了大量冗余的数据,浪费了一定量的磁盘空间是事实,但是极端情况下横表也有可能造成极大的空间浪费了解 SQL Server 的同学肯定知道,SQL Server 中正常来来说是行存储,一行数据不能跨页存储(当然 forwarded 存储方式的数据除外,有机会说这个),SQL Server 的最小存储单位是页(Page),一个页的大小是 8kb,除去 page 信息固定占用的空间之外是 8060 个字节,每一行固定的一行数据除了数据自身占用的空间外,至少(不是一定,表结构越复杂占用的额外空间越大)还要占用 1+1+2+2+1=7 个字节
对于宽表,一旦字段长度达到一定的程度,比如每行长度为 800 个字节,理论上将,在一个 page 上,存储 9 行记录之后,还剩余 800 字节的空间(具体剩余多少跟表结构有关,这里只是举例说明),对不起,第十行数据来了已经存不进去了,只能新开页面分配存储空间,这样,当前这个页面就浪费了 800 字节的存储空间反观竖表,因为存储的数据行都非常短,即便发生上述情况,也只会浪费很少的一点数据空间(小于一行数据的空间)极端情况下会更加有意思,参考这个 http://www.cnblogs.com/studyzy/archive/2008/11/27/1342003.html
有上述可见,对于横表和竖表,不管是设计上还是存储上,优点和缺点都是看站在哪个角度来看的, 从一个角度来看是有点,从另外一个角度看就可能会变成缺点,只有舍弃一部分,根据实际情况权衡之后做出取舍。凡事无绝对,适合即可。
总结:
本文从适应场景、存储、性能等方面粗浅第分析了表设计时候横标和竖表的特点和优缺点,具体设计的时候可综合考虑,做出合理的选择。另外,本文肯定还有没有预计或者说想到的情况以及评估方向,也希望有想法的同学补充,谢谢。
来源: http://www.cnblogs.com/wy123/p/6677073.html