不同主键设置对查询性能的影响分析
两张表字段一样,数据量一样,不同在于主键的设置上。一个ProjectID对应多个TicketNo,TicketNo唯一,任意字母和数字的组合。
主要的频率很高的查询类似:select * from dbo.EmptyTicket where projectid = 243 and statuscode = 0
执行如下语句
set statistics io on
set statistics time on
set statistics profile on
select * from dbo.EmptyTicket where projectid = 243 and statuscode = 0
select * from dbo.EmptyTicket2 where projectid = 243 and statuscode = 0
结果如下:
表 'EmptyTicket'。扫描计数 1,逻辑读取 8 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'EmptyTicket2'。扫描计数 1,逻辑读取 214 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
结论:
可见已ProjectID和TicketNo作为的复合主键,效率更高。这是因为以ProjectID为主键的第一个字段,其数据的存储是按ProjectID的值来依次存放的,读取特定一个ProjectID的数据,sqlserver只需很少的几次读取就可以查出。反之,如果以TicketNo作为主键,其存放顺序是按TicketNo的值依次存放,如果去特定ProjectID时,必定要扫描整个表,才能得到所需数据。也许你会说,可以加个ProjectID的非聚集索引,但是又会增加额外的索引存储空间,由于这时的数据是离散分布在表中的,还是要花费不少时间才能读取完全部数据。
在设计表结构时,一定要弄清此表的主要用途,并以此进行相应的设计,尤其是索引的设计,这对后期的性能会带来非常重大的影响。
附:有关Statistics IO的输出项说明