精华志-京华志出品-基于SQL-Server-2008构建SOA大型管理软件技术实践.ppt

基于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中直接读取已提交数据的快照而没有任何阻塞 未启用快照和启用快照的事务行为对比 使用数据压缩减少磁盘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 rownumber 10000andT rownumber 10020MainTable中有几十万条记录 性能瓶颈在哪里 U9分页算法实现 优化后的分页模式 WithCTE1As Select rownumberFrom select ROW NUMBER over orderbyA ID asrownumberfromMainTableA TWhereT rownumber 10000andT 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 的应用 两种批插入方式性能对比 使用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 j 10 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 asbegi