2010数据库技术大会黄卫基于SQL Server 2008构建SOA大型管理软件技术实践

基于SQLServer2008构建SOA大型管理软件技术实践用友软件股份有限公司U9研发中心黄卫2010年3月17日U9业务架构U9技术架构U9在IntelLab的性能测试结果8000并发下系统可健康稳定运行平均响应时间0.18秒，平均事务处理时间2.5秒*根据5台应用服务器的CPU平均利用率进行统计计算得到的结果。要求支持高并发、高性能规模4000张业务表常规应用场景数据库大小为40GB数据库中保存3年以上客户数据U9数据库概要平台框架元数据（3NF-4NF）基础商业组件（3NF）ERP各模块组件（2NF-3NF）技术实践如何选定逻辑主键行版本快照-读已提交数据使用数据压缩减少磁盘I/O消耗包含列索引实现索引覆盖U9分页算法实现计算列在U9系统中的应用表值参数(Table-ValuedParameter)的应用使用XML优化硬编码SQL如何选定逻辑主键（PrimaryKey）无意义整数顺序号（Bigint）优点设置简单空间占用小缺点无法支持分布式数据库应用数据迁移时易引起冲突GUID优点设置简单支持分布式数据库应用缺点空间占用大（十六字节）如何选定逻辑主键（PrimaryKey）U9主键策略U9主键（Bigint）=SiteID（两位数字）+YYYMMDD+顺序号（7位数字）优点空间占用小支持分布式数据库应用存储过程实现主键分配算法CLR实现自治事务内存Low-High算法减少存储过程调用次数行版本快照-读已提交数据在SQLServer2005之前，读操作会被写操作阻塞，只能通过脏读提升系统并发性。SQLServer2005以后只要启用行版本控制，将从Tempdb中直接读取已提交数据的快照而没有任何阻塞。未启用快照和启用快照的事务行为对比：时间Tran1Tran2（未启用快照）Tran2（启用快照）T1begintranT2updatetable1T3selecttable1waiting…selecttable1returncommitteddataT4committranT5returndata使用数据压缩减少磁盘I/O消耗U9的大部分表和索引都使用了SQLServer的页压缩。SQLServer2008支持表和索引的行压缩和页压缩。行压缩主要原理是按照数据的实际大小存储数据，而且对Null和0值作了优化，使它们不占用任何字节页压缩在行压缩的基础上使用前缀压缩和字典压缩使用数据压缩减少磁盘I/O消耗启用页压缩前后U9数据库系统的各项参数性能对比包含列索引实现索引覆盖传统索引限制不允许作为索引键列的数据类型。如：nvarchar(max)等索引键列数最多16个索引键存储空间最大900字节包含列索引将受数据类型和存储空间限制的字段放到include子句中实现索引覆盖例如：selectname,title,addressfromtable1whereuserid=123;包含列索引：createindexIX_1ontable1(userid)include(name,title,address);U9分页算法实现没优化前的分页模式：Select*,rownumberFrom(select*,ROW_NUMBER()over(orderbyA.ID)asrownumberfromMainTableAleftjoinDictTable1asD1on(A.DictID1=D1.ID)leftjoinDictTable2asD2on(A.DictID2=D2.ID)….leftjoinDictTableNasDNon(A.DictIDN=DN.ID))TWhereT.rownumber10000andT.rownumber=10020MainTable中有几十万条记录。性能瓶颈在哪里？！U9分页算法实现优化后的分页模式：WithCTE1As(Select*,rownumberFrom(select*,ROW_NUMBER()over(orderbyA.ID)asrownumberfromMainTableA)TWhereT.rownumber10000andT.rownumber=10020)Select*FromCTE1leftjoinDictTable1asD1on(CTE1.DictID1=D1.ID)leftjoinDictTable2asD2on(CTE1.DictID2=D2.ID)….leftjoinDictTableNasDNon(A.DictIDN=DN.ID)只将需要显示的数据与字典表进行关联！计算列在U9系统中的应用字段过多的业务主键每个U9数据表的业务主键是一个非聚集唯一索引，用以保证数据的唯一性。该索引键是由一个或多个业务字段组成的。在一些允许客户自定义业务的场景中，往往会出现超过SQLServer16个索引键限制的情况。字符过多的业务主键在超过16个索引键的情况下，我们通过持久化计算列将多个业务字段拼成一列，建立唯一性索引保证数据唯一性。但是拼接后的计算列字符较多，可能超出SQLServer索引键长度900字节的限制。计算列在U9系统中的应用解决方案不使用原始拼接的计算列字符串，而是使用字符串的MD5密文。MD5是MessageDigestAlgorithm5的简称，可以将任意长度的字符串转换为一个32个字符的“数字指纹”。这样，计算列字段可以固定定义为varchar(32)，不会再引发索引键长度限制问题。publicstaticstringMD5(stringstr){byte[]s=m.ComputeHash(UnicodeEncoding.UTF8.GetBytes(str.ToUpper(System.Globalization.CultureInfo.InvariantCulture)));returnBitConverter.ToString(s).Replace(-,);;}表值参数(Table-ValuedParameter)的应用TVP的作用表值参数是SQLServer2008中的新参数类型。使用表值参数，可以不必创建临时表或许多参数，即可向Transact-SQL语句或例程（如存储过程或函数）发送多行数据。TVP的优点首次从客户端填充数据时，不获取锁允许在单个例程中包括复杂的业务逻辑减少到服务器的往返表值参数(Table-ValuedParameter)的应用两种批插入方式性能对比：插入方式使用时间（秒）每批记录数循环次数插入总记录数TVP插入6.981,0001010,000ExecuteBatch67.961,0001010,000使用XML优化硬编码SQL存在的问题：U9系统中经常出现形如下例的硬编码SQLselect*fromtable_awhereidin(xxx,xxx,xxx…)性能问题无法重用查询计划由于IDList数目不同，SQL查询计划无法重用。SQL文本过大在一个案例中，我们曾经发现大于3M的SQL，大文本SQL的解析开销甚至会超过执行查询本身。使用XML优化硬编码SQL程序端将ID拼装成XML类型变量privatestaticDataTablegetIDTable(){DataTableIDTable=newDataTable(IDT);for(intj=0;j10;j++){DataRowdr=IDTable.NewRow();dr[0]=10010000000010+j;IDTable.Rows.Add(dr);}returnIDTable;}SqlCommandcmd=newSqlCommand(select*fromtable_awhereidin(select*fromdbo.getIDTable(@IDList);,conn);SqlParameterparam=cmd.Parameters.Add(@IDList,SqlDbType.VarChar);DataTabledt=getIDTable();param.Value=ToXml(dt);SqlDataReaderdr=cmd.ExecuteReader();使用XML优化硬编码SQL数据库端用SQLFunction解析xmlCREATEfunction[dbo].[F_GetIDTable](@IDListnvarchar(max))returns@IDTabletable(IDnvarchar(4000)notnull)asbegindeclare@tasTable(xmlColxml)insertinto@tvalues(cast(@IDListasxml))[e-mail]('@ID','nvarchar(4000)')[e-mail]('//IDT')asT2(item)returnend;