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

1、基于SQL Server 2008构建SOA大型管理软件技术实践,用友软件股份有限公司 U9研发中心 黄卫 2010年3月17日,U9业务架构,U9技术架构,U9在Intel Lab的性能测试结果,8000并发下系统可健康稳定运行 平均响应时间0.18秒，平均事务处理时间2.5秒,*根据5台应用服务器的CPU平均利用率进行统计计算得到的结果。,要求 支持高并发、高性能 规模 4000张业务表 常规应用场景数据库大小为40GB 数据库中保存3年以上客户数据,U9数据库概要,平台框架元数据（3NF-4NF）,基础商业组件（3NF）,ERP各模块组件（2NF-3NF）,技术实践,如何选定逻辑主键 行

2、版本快照-读已提交数据 使用数据压缩减少磁盘I/O消耗 包含列索引实现索引覆盖 U9分页算法实现 计算列在U9系统中的应用 表值参数(Table-Valued Parameter)的应用 使用XML优化硬编码SQL,如何选定逻辑主键（Primary Key）,无意义整数顺序号（Bigint） 优点 设置简单 空间占用小 缺点 无法支持分布式数据库应用 数据迁移时易引起冲突 GUID 优点 设置简单 支持分布式数据库应用 缺点 空间占用大（十六字节）,如何选定逻辑主键（Primary Key）,U9主键策略 U9主键（Bigint）= SiteID（两位数字）+YYYMMDD+顺序号（7位数字）

3、 优点 空间占用小 支持分布式数据库应用 存储过程实现主键分配算法 CLR实现自治事务 内存Low-High算法减少存储过程调用次数,行版本快照-读已提交数据,在SQL Server 2005之前，读操作会被写操作阻塞，只能通过脏读 提升系统并发性。SQL Server 2005以后只要启用行版本控制，将 从Tempdb中直接读取已提交数据的快照而没有任何阻塞。 未启用快照和启用快照的事务行为对比：,使用数据压缩减少磁盘I/O消耗,U9的大部分表和索引都使用了SQL Server的页压缩。 SQL Server 2008 支持表和索引的行压缩和页压缩。 行压缩 主要原理是按照数据的实际大小存储

4、数据，而且对Null和0值作了优化，使它们不占用任何字节 页压缩 在行压缩的基础上使用前缀压缩和字典压缩,使用数据压缩减少磁盘I/O消耗,启用页压缩前后U9数据库系统的各项参数性能对比,包含列索引实现索引覆盖,传统索引限制 不允许作为索引键列的数据类型。如：nvarchar(max)等 索引键列数最多16个 索引键存储空间最大900字节 包含列索引 将受数据类型和存储空间限制的字段放到include子句中实现索引覆盖 例如： select name,title,address from table1 where userid=123; 包含列索引： create index IX_1 on t

5、able1(userid) include(name,title,address);,U9分页算法实现,没优化前的分页模式： Select *,rownumber From (select *, ROW_NUMBER() over (order by A.ID) as rownumber from MainTable A left join DictTable1 as D1 on (A.DictID1=D1.ID) left join DictTable2 as D2 on (A.DictID2=D2.ID) . left join DictTableN as DN on (A.DictIDN

6、=DN.ID) ) T Where T. rownumber 10000 and T. rownumber=10020 MainTable中有几十万条记录。 性能瓶颈在哪里？！,U9分页算法实现,优化后的分页模式： With CTE1 As( Select *,rownumber From (select *, ROW_NUMBER() over (order by A.ID) as rownumber from MainTable A ) T Where T. rownumber 10000 and T. rownumber=10020 ) Select * From CTE1 left j

7、oin DictTable1 as D1 on (CTE1.DictID1=D1.ID) left join DictTable2 as D2 on (CTE1.DictID2=D2.ID) . left join DictTableN as DN on (A.DictIDN=DN.ID) 只将需要显示的数据与字典表进行关联！,计算列在U9系统中的应用,字段过多的业务主键 每个U9数据表的业务主键是一个非聚集唯一索引，用以保证数据的唯一性。该索引键是由一个或多个业务字段组成的。在一些允许客户自定义业务的场景中，往往会出现超过SQL Server 16个索引键限制的情况。 字符过多的业务主键 在

8、超过16个索引键的情况下，我们通过持久化计算列将多个业务字段拼成一列，建立唯一性索引保证数据唯一性。但是拼接后的计算列字符较多，可能超出SQL Server索引键长度900字节的限制。,计算列在U9系统中的应用,解决方案 不使用原始拼接的计算列字符串，而是使用字符串的MD5密文。MD5是Message Digest Algorithm 5的简称，可以将任意长度的字符串转换为一个32个字符的“数字指纹”。这样，计算列字段可以固定定义为varchar(32)，不会再引发索引键长度限制问题。 public static string MD5(string str) byte s = m.Comput

9、eHash(UnicodeEncoding.UTF8. GetBytes(str.ToUpper(System.Globalization.CultureInfo.InvariantCulture); return BitConverter.ToString(s).Replace(-,); ,表值参数(Table-Valued Parameter)的应用,TVP的作用 表值参数是 SQL Server 2008 中的新参数类型。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据。 TVP的优点 首次从客户端填充数据时，不获取

10、锁 允许在单个例程中包括复杂的业务逻辑 减少到服务器的往返,表值参数(Table-Valued Parameter)的应用,两种批插入方式性能对比：,使用XML优化硬编码SQL,存在的问题： U9系统中经常出现形如下例的硬编码SQL select * from table_a where id in (xxx,xxx,xxx) 性能问题 无法重用查询计划 由于IDList数目不同，SQL查询计划无法重用。 SQL文本过大 在一个案例中，我们曾经发现大于3M的SQL，大文本SQL的解析开销甚至会超过执行查询本身。,使用XML优化硬编码SQL,程序端将ID拼装成XML类型变量 private st

11、atic DataTable getIDTable() DataTable IDTable = new DataTable(IDT); for (int j = 0; j 10; j+) DataRow dr = IDTable.NewRow(); dr0 = 10010000000010 + j; IDTable.Rows.Add(dr); return IDTable; SqlCommand cmd = new SqlCommand(select * from table_a where id in (select * from dbo.getIDTable(IDList);, conn)

12、; SqlParameter param = cmd.Parameters.Add(IDList, SqlDbType.VarChar); DataTable dt = getIDTable(); param.Value = ToXml(dt); SqlDataReader dr = cmd.ExecuteReader();,使用XML优化硬编码SQL,数据库端用SQL Function解析xml CREATE function dbo.F_GetIDTable(IDList nvarchar(max) returns IDTable table (ID nvarchar(4000) not null) as begin dec