实战性能优化方案

实战性能优化方案 天善问答，由天善讲师团、智囊团长期驻扎，一站式解决你在工作和学习中遇到各类BI技术问题,网址： 实战性能优化方案1 影响性能因素1.系统硬件资源2.网络系统3.操作系统4.数据库管理系统5.数据库设计6.应用程序设计工作负载 当我们进行性能优化的时候，需要从多个方面多个角度综合考虑。当前ERP系统的性能差的直接体现是数据库服务器IO问题，这个问题涉及到硬件资源方面、服务器配置方面、物理设计方面以及数据库逻辑设计方面。我们可以从以下几个方面进行优化处理。1. 数据分区2. 服务器配置3. 索引优化4. 缩小日志文件5. 优化分析工具6. 数据库高可用性7. 数据仓库 与 OLAP 报表开发1.1 软件方面入手1.1.1 数据分区 分区的概述 微软推出的SQL Server 2005数据库，它的数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使用户可以构建和管理用于业务的高可用和高性能的数据应用程序。其数据分区功能能够改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。大型数据库是指无法按照预期方式运行的数据库，或者运行成本或维护成本超出预定维护要求或预算要求的数据库。 用户的活动或维护操作性能严重下降，则可以认为表非常大。通过更好的设计和分区实现，通常可以避免或最大程度地减少这种情况的发生。另外当表中的不同行集拥有不同的使用模式时，具有不同访问模式的表也可能会影响性能和可用性。尽管使用模式并不总是在变化(尽管这也不是进行分区的必要条件)，但在使用模式发生变化时，通过分区可以进一步改善管理、性能和可用性。以销售表为例，当前月份的数据可能是可读写的，但以往月份的数据(通常占表数据的大部分)是只读的。在数据使用发生变化的类似情况下，或在维护成本随着在表中读写数据的次数增加而变得异常庞大的情况下，表响应用户请求的能力可能会受到影响。相应地，这也限制了服务器的可用性和可伸缩性。通过分区，数据从OLTP到OLAP 的数据加载时间从原来的几分钟或几个小时缩短到几秒钟，查询效率也就提高了。 分区的原理对表的分区，只是数据对应物理存储的空间分布分离，而不是把一张表分成多张表，从用户操作角度来说，它依然是一张表。如我们把订单表按照月份分区，如下图，上图中，根据日期对订单表进行分区，每个月份的数据存放到一个分区中。而实际操作数据中，订单表与订单明细表往往同时操作或关联操作，未来提高关联访问性能，我们可以把订单明细表和订单表使用相同的分区方案，也就是同一个月份的数据，订单明细表和订单表的数据存放在同一个文件组中。如下图： 分区类型根据ERP当前的数据情况来看，我们需要采用三种不同方式的分区类型，一种是大型表分区， 再一种是频繁表分区，还一种是模块表分区：大型分区如进销存流水数据，随着时间的推移这类表数据会越来越多，根据日期来进行分区，根据粗糙估计数据量每个月大小和逐月递增的速度来确定1个月一个区还是3个月一个区等。频繁表分区如果某些表尽管不是很大，但需要进行大用户频繁操作读写的，我们可以对这类表把它从整个库中的文件中分离出来成为单独的分区文件。比如商品库存表SPKCB，该表访问读写操作比较频繁，而且需要定期对它进行清除导入的工作。我们可以把此表的数据存储从库的主文件PRIMARY中独立出来设置成单独的一个文件（一个文件组）。模块表分区所有大的模块划分成不同的区：如生产系统分销系统财务系统人事系统会员系统订货会系统决策支持系统其中决策支持系统和多账套的查询系统以及分销系统中综合查询部分，需要在数据仓库系统中实现统计和挖掘。 在分销系统中有些相对比较对立模块的，也将独立文件组分区，比如分销系统中的仓库管理、代理管理、渠道管理、批发管理、进货管理、商店配货、零售管理等。每个模块独立一个文件组。 分区实现步骤确定分区条件和分区数目确定对表是否分区创建一个或多个文件组创建分区函数创建分区分配方案创建分区表创建相关索引保持一般表YN说明分区理由 分区的实现方法:表分区分为水平分区和垂直分区。水平分区将表以行记录来分。每个分区包含的列数相同，但是行更少。例如，可以将一个包含十亿行的表水平分区成 12 个区，每个区表示特定年份内一个月的数据。任何需要特定月份数据的查询只需引用相应月份的区。而垂直分区则是将原始表分成多个只包含较少列的区。下面具体描述分区过程中的实现方法。创建文件组 建立分区表先要创建文件组，而创建多个文件组主要是为了获得好的 I/O 平衡。一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。每个文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用。为了更好地管理数据（例如，为了获得更精确的备份控制），对分区表应进行设计，以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 ALTER DATABASE，添加逻辑文件组名：ALTER DATABASE 库名 ADD FILEGROUP 文件组名1创建文件组后，再使用 ALTER DATABASE 将文件添加到该文件组中：ALTER DATABASE 库名 ADD FILE ( NAME = N文件标识1, FILENAME = N 文件路径文件名 , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP 文件组名1类似的建立其他文件组，最好把每一个存储数据的文件放在不同的磁盘驱动器里。创建分区函数创建分区表必须先确定分区的功能机制，表进行分区的标准是通过分区函数来决定的。创建数据分区函数有RANGE “LEFT | / RIGHT”两种选择。代表每个边界值在局部的哪一边。例如存在四个分区，则定义三个边界点值，并指定每个值是第一个分区的上边界 (LEFT) 还是第二个分区的下边界 (RIGHT)1。代码如下：CREATE PARTITION FUNCTION 分区函数名(数据类型)AS RANGE RIGHT FOR VALUES (20070101, 20070601, 20080101) 半年一个分区，20070101之前的数据在第一个区， 20070101,与20070601之间的数据在第二个区，20070601与20080101之间的数据在第三个区，20080101之后的数据在第四个区。创建分区方案创建分区函数后，必须将其与分区方案相关联，以便将分区指向至特定的文件组。就是定义实际存放数据的媒体与各数据块的对应关系。多个数据表可以共用相同的数据分区函数，一般不共用相同的数据分区方案。可以通过不同的分区方案，使用相同的分区函数，使不同的数据表有相同的分区条件，但存放在不同的媒介上。创建分区方案的代码如下：CREATE PARTITION SCHEME 分区方案名 AS PARTITION 分区函数名 TO (文件组名1, 文件组名2, 文件组名3,。 文件组名n) 创建分区表建立好分区函数和分区方案后，就可以创建分区表了。分区表是通过定义分区键值和分区方案相联系的。插入记录时，SQL SERVER会根据分区键值的不同，通过分区函数的定义将数据放到相应的分区。从而把分区函数、分区方案和分区表三者有机的结合起来。创建分区表的代码如下：CREATE TABLE 表名( ID int IDENTITY(1,1) NOT NULL,字段名 类型 条件,。) ON 分区方案名(分区字段)分区字段是表中的字段之一。 维护分区分区的维护主要设计分区的添加、减少、合并和在分区间转换。可以通过ALTER PARTITION FUNCTION的选项SPLIT，MERGE和ALTER TABLE的选项SWITCH来实现。SPLIT会多增加一个分区，而MEGRE会合并或者减少分区，SWITCH则是逻辑地在组间转换分区。 由于分区函数中要求是指定分区数目的，但当前设置并不一定满足未来的发展变化，后期可以通过数据库提供的分区变更方法来动态的维护分区。 分区前后性能对比我们模拟生产2650万数据，存储空间占用约5G的单表进行性能对比，测试环境为IBM PC服务器，CPU 2.7G*4、内存16G、硬盘 80G*4，系统平台为Windows Server 2003，数据库SQL Server 2005 SP2。测试结果如下表对海量数据的处理一直是一个令人头痛的问题。分离的技术是所有设计者们首先考虑的问题，不管是分离应用程序功能还是分离数据访问，如果加以了合理规划，都能十分有效的解决大数据表的运行效率低和维护成本高等问题。SQL Server 2005新增的表分区功能，可以对数据进行合理分区，当用户在访问部分数据时，SQL Server最佳化引擎可以根据数据的实体存放，找出最佳的执行方案。1.1.2 索引优化（1）针对性索引 针对不同的客户不同的查询特点，有针对性做一些索引设计（2） 索引分区当前的ERP系统中，所有的索引都存储在库的主文件中，整个库有大量的索引，其在库主文件中占据不小的空间比例，如果该表已经做了表分区的，那么我们可以对该表的相关索引也把它归入到相同的分区中，特别是对立一个文件组的表，它的索引也全部加入到该文件组中。1.2 硬件和其他技术方面入手1.2.1 服务器配置: 服务器使用多磁盘多CPU或磁盘raid0模式。如果多硬盘或多CPU的话，将更加提高数据分区的特长，前面也提到过，数据分区的时候最好把不同的文件组的数据存放于不同的磁盘，同时配置多CPU，这样数据库能够并行处理不同分区的数据。这将大大提高多用户并发性的查询性能。1.2.2 缩小日志文件当前ERP系统在有些客户的服务器中的日志文件到达几十个G，甚至几百个G， 大文件日志的操作一样会影响整体操作性能。对此我们作如下优化：尾日志备份：对日志文件每间隔一段时间就备份一次，比如每2个小时备份一次，这样能够保持日志文件总是处理比较小的空间。由于目前ERP系统客户实际使用的并发数不超过1000个，小文件的日志备份几个毫秒到几百毫秒之间就可以完成，频繁备份并不对系统查询性能产生影响。3天前备份的文件自动删除，以减少磁盘空间。1.2.3 优化分析工具:（1）Performance工具对CPU负载分析，内存负载分析，IO性能分析，找出基准点。（2）使用 数据库查询分析器，跟踪SQL执行步骤，优化改进sql语句，甚至在性能查看器中监测SQLSERVER内存（3）使用数据库引擎优化顾问进行数据库优化。它能分析在一个或多个数据库中运行的工作负荷的性能效果，能提出改进物理设计结构的建议。1.2.4 数据库的高可用性我们使用数据库镜像保障我们数据库的高可用性，保障数据的安全性。但数据库镜像功能不能提高数据库的查询分析性能。数据库镜像维护一个数据库的两个副本，驻留在不同位置的计算机上数据库实例中。其中一个服务器实例使数据库服务于客户端（“主体服务器”），另一个服务器实例则根据镜像会话的配置和状态，充当热备用或温备用服务器（“镜像服务器”）。同步数据库镜像会话时，数据库镜像提供热备用服务器，可支持在已提交事务不丢失数据的情况下进行快速故障转移。异步会话时，镜像服务器通常用作备用服务器。我们在当前ERP系统中，使用异步镜像模式，同时在镜像服务器中建立快照，这样能够分流一部分的查询分析报表的并发性。以提高系统分析的部分性能。这一功能需要客户提供单独的一台同等性能服务器作为“镜像服务器”。1.2.5 数据仓库和OLAP报表开发数据仓库建设的目的：使用数据仓库的建设，分离业务操作型数据和分析挖掘使用的数据，提高分析报表的查询性能。使用数据仓库的建设，能够灵活展现各种条件的报表分析。数据仓库建设的原因：当前ERP系统中的查询性能比较慢的报表，如仓库销存对比数据分析、渠道销存对比数据分析，查询执行时调用sql过程步骤很多，表的关联查询很多，还生成临时表等方式，效率很低。通过数据仓库的建设，将数据也分析维度和分析指标的事实数据的模式能够快速展现报表。数据仓库的数据源：对于需要在线联机分析的报表，我们采用数据仓库建设的模式进行，比如多账套数据，历史不变的记录数据，如流水账数据，零售、批发历史记录数据，在决策支持系统中需要分析挖掘的数据。 这类数据我们都将根据数据仓库模型有针对性抽取，使用sqlserver2005中的SQL Server Business Intelligence Development Studio (简称SSIS) 进行 ETL 开发。数据仓库建设时效性：在数据仓库建设过程，我们采用数据集市的模式先后逐步建立分析模型，优先建立性能较差的分析报表模型，使客户能及时有效使用分析报表。数据仓库架构报表/OLAP/数据挖掘数据集市多维数据集DW（数据仓库）ETL（源数据的清洗、转换、加载）其他配置数据工作库n工作库2ERP工作库1数据仓库模型：下图为数据仓库建设的模型举例，以销售收入作为分析指标一个事实表分析模型，围绕日期、区域、货品、渠道或客户或供应商、还可以再加上仓库等，可以多角度同时分析。开发平台与工具开发平台与工具使用微软商业智能解决方案的开发平台：包括BI平台、BI终端工具、BI门户和BI应用四部分。2 参考文献:2.1 软件方面：1.备份数据库和清除垃圾数据。 2.清理删除日志 如下面的使用查找： DBCC SHRINKDATABASE(NJOEONE_DW) -收缩特定数据库 DBCC SHRINKFILE (NJOEONE_BSERP_DW , 0, TRUNCATEONLY) -收缩相关数据库的指定数据文件或日志文件大小。 DBCC SHRINKFILE (NJOEONE_BSERP_DW_log , 1024) EXEC sp_updatestats3. DBCC检查数据库数据完整性。DBCC(DataBase Consistency Checker）是一组用于验证SQL Server数据库完整性的程序。4.适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。 (表分区,对于系统性能的提高，首先要在软件上面做文章，比如增加一个索引可以将查询速度提高好几倍，但是软件是有瓶颈的，其瓶颈就是硬件，因为硬盘的转速时有限的，所以数据库表空间应该建立在单独的硬盘上（而不是同一个硬盘的不同分区）。这样子硬盘并行操作，提高转速。从而达到目的)5.可以用DBCC DBREINDEX语句或者SQL SERVER维护计划设定定时进行索引重建，索引重建的目的是提高索引的效能。6.可以用语句UPDATE STATISTICS或者SQL SERVER维护计划设定定时进行索引统计信息的更新，其目的是使得统计信息更能反映实际情况，从而使得优化器选择更合适的索引。7.可以用DBCC CHECKDB或者DBCC CHECKTABLE语句检查数据库表和索引是否有问题，这两个语句也能修复一般的问题。 8.分布式分区视图可用于实现数据库服务器联合体9.把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.10.纵向、横向分割表，减少表的尺寸(sp_spaceuse) 11.根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段12.用sp_configure query governor cost limit或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。 SET LOCKTIME设置锁的时间 13.重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。2.2 硬件方面：1.升级硬件。2.提高网速。3.扩大服务器的内存。4.增加服务器的CPU个数。5.DBServer和ApplictServer分离。2.3 SQL语句优化背后有一套完整的理论和逻辑1：确认是否因为做了物理I/O而导致性能不佳2：确认是否因为编译时间过长而导致性能不佳3：确认sql server是否正确估计了每一步的cast，选择查询计划，4：检查表结构和语句逻辑，确认是否有优化空间，提交执行速度。为系统、程序和临时文件配备一个专用磁盘，如果确是进行很多修改工作，将更新日志和事务日志放在专用磁盘上。读吞吐量读吞吐量的大小取决于磁盘读取数据的速度，而磁盘读取的速度又取决于数据文件在磁盘中的位置。因此，位于不同盘符上不同数据库文件的读取速度都不相同。3 数据库优化方案数据库优化方案主要分为以下几个步骤：（1）数据库分区（2）索引优化（3）数据库镜像快照 实现读写分离（4）数据仓库建设实现跨年整合数据综合统计和数据挖掘本次性能优化测试主要是数据库分区。这是整个优化方案的第一个步骤。也是最重要最优先考虑的步骤。数据库分区能够使ERP各个系统各个模块的数据从物理存储上分离开来，减少多用户并行操作不同模块的相互影响。另外还对tempdb临时数据库文件收缩，执行定期收缩。3.1 数据库分区方案数据库分区只是对表数据物理存储上的分离，并不改变数据库逻辑表结构。对应应用程序来说，分区的前后没有区别。根据九牧王ERP当前的数据情况来看，我们需要采用三种不同方式的分区类型。一种是模块数据分区，再一种是大型表分区，还一种是频繁表分区。3.1.1 模块表分区模块表分区就是根据ERP各个系统、或同一系统的各个模块进行数据存储物理分离，但其逻辑表结构保持原来不变。九牧王使用的ERP中主要是分销系统数据量比较大，处理的业务比较多。其他系统相对较少使用，或者数据量很少。另外全局数据各个系统都需要用到，而且访问比较频繁，独立文件组有利于提供查询性能，我们使用如下的系统数据存储划分：其他系统其中“其他系统”除了这里列举的几个部分外的所有系统，包括生产系统、会员系统、订货会系统、决策支持系统等。其中分销系统下面还需要做详细模块划分。由于当前系统数据量最大在分销系统，所以我们重点处理分销系统的数据分区，在分销系统中有些相对比较对立模块的，也将独立文件组分区，比如分销系统中的仓库管理、代理管理、渠道管理、批发管理、进货管理、商店配货、零售管理等。每个模块独立一个文件组。3.1.2 大型表分区分销系统除了对各个大模块数据分区外，还需要对一些数据量非常大的业务单据表进行独立文件组分区。比如：商店配货单商品明细表、仓库盘点单明细表、零售销货单商品明细表等，由于考虑这些具体的业务关联性比较紧密，关联查询比较多，对表再进一步分区就很难确定分区条件。所以暂时就一类单（包含主表和明细表）一个文件组。如下图目前对大数据量的表进行独立的文件组分区。对于这些大表，我们后续还可以对它进行按月份进行分区。但需要进一步测试。3.1.3 频繁表表分区如果某些表尽管不是很大，但需要进行大用户频繁操作读写的，我们可以对这类表把它从整个库中的文件中分离出来成为单独的分区文件。如果这类表用户操作时候常常按照一定的条件进行操作的话，我们就可以根据条件进行表分区。比如商品库存表SPKCB，该表访问读写操作比较频繁，而且需要定期对它进行清除导入的工作。操作时常常按照不同的仓库来修改数据和查询，我们可以根据仓库代码进行表分区。按照仓库代码的首字母把这张表分成27个区。Z文件组=Z3.2 分区的详细设置3.2.1 模块文件组 系统日志 系统全局表 订货会表订货会系统有很多表，但订货会商品明细表这张表相当大，所以我把他单独独立文件组出来。 分销模块表以下按照模块分销子模块分组，每个子模块组合成一个文件组。3.2.2 表单文件组以下表中，是以表单主表及其子表明细表为一个单元的文件组。3.2.3 商品库存表文件组商品库存表SPKCB 根据 仓库代码 CKDM字段首字母的值大小进行分区。每个文件组一个文件。另外 SPKCB开头除SPKCB外所有表（SPKCB%）都存放于S_SPKCB文件组中。同时中间表HHLSDJL也存放在S_SPKCB文件组中。3.2.4 其他文件组以上列举的定义了文件以外，其他所有表都单独列出存放于文件组“O_OTHERS”中。3.3 索引分区每一个模块或子模块在分区后，所在的表对应的索引也同样存储到对应的分区中。但商品库存表的索引存在S_SPKCB_9文件组中。3.4 Tempdb库文件收缩业务数据查询或操作时，需要处理的临时数据比较多，Tempdb的数据量就会比较大，使用数据库收缩脚本，并定时执行脚本进行收缩，收缩效果明显。4 数据库优化工作总结4.1 优化成果 本次在九牧王的数据库性能优化工作效果比较明细。客户使用查询统计的时候，现在所花的时间比以前缩短了很多，做单的时间也比以前快了，ERP系统的死锁现在已经很少出现。客户的抱怨系统缓慢的情况已经很少听到了。本次优化测试从单点登录测试的情况来看，整体平均提升5倍以上的速度。但是从多用户并行角度来说，测试的具体指标数据就无法正确得到了。只能看客户的使用情况来反馈信息。4.2 数据库优化的工作内容4.2.1 熟悉客户软硬件环境深入熟悉了客户的操作系统环境和硬件环境，包括测试机、正式业务机。熟悉客户BSERP运行情况，业务操作的性能情况，数据库死锁情况。实际客户反映什么操作都慢，并不是哪些综合统计才慢。4.2.2 数据库备份还原数据库优化前，做了备份。以防意外情况数据丢失。 我们在数据库优化实施后，正式的业务机上备份后发现缩小了10G的空间，然后我们分析原因，并进行还原测试。 还原后数据正常，也就是优化后备份的实际存储空间也大大缩小。4.2.3 硬盘RAID分布规划设计业务生产机子windows SERVER 操作系统，IBM服务器，整个生产机是由两台独立服务器组合一个操作系统。共享硬件资源。每台服务器是有6个硬盘，4个CPU，每个CPU4核。每台服务器的6个硬盘做成1个raid ，一共2个raid。而目前数据库只利用了一个raid。4.2.4 分区设计数据库分析主要根据以下几个方面，一个模块划分、一个是大型表划分，一个频繁表划分。分区的方法，一个独立文件组，一个是表分区。模块划分属于独立文件组的方法。大型表划分可以采用独立文件组方法也可以采用表分区，对于有比较明确的独立查询条件的时候，我们可以根据这个条件划分数据物理存储，进行表分区；而对于复杂多变的查询条件，使用表分区就不一定适合，那就直接使用独立文件组。表分区的设计需要分析业务应用，分析查询条件，分析相关关系。比较费时费力。4.2.5 分区脚本编写表分区脚本的编写涉及到文件、文件组、分区函数、分区方案、表主键、聚集索引、分区类别、分区字段、分区字段类型、分区的初始化大小、增长大小等。 每制定一个分区，都需要详细考虑每个方面。4.2.6 分区性能测试分区后需要对数据库性能测试比较，需要作如下的测试过程：稳定性测试数据正确性测试 优化前查询测试优化后查询测试优化前后测试对比4.2.7 索引优化测试本次测试对数据库进行了多角度的索引优化的分区，考虑主索引与次索引的关系，以及查询条件的关系。由于ERP系统的表查询条件多样可变，比较复杂，难以确定性的最佳索引。 测试时调整了一些索引，虽然对单表查询提高的查询速度，但对于综合统计时，并没有提高查询速度。4.2.8 临时库脚本收缩编写临时库收缩脚本，并设定每晚执行能有效清理一些临时表过时数据，起到一定的作用。4.2.9 数据库快照测试使用数据库镜像快照时，能够使数据库分析和操作分离，但是快照更新时，应用程序数据库连接会断开，需要应用程序连接重新建立连接。目前我们erp程序不会自动连接，所以不适合使用镜像快照。4.3 数据库分区方案数据库分区只是对表数据物理存储上的分离，并不改变数据库逻辑表结构。对应应用程序来说，分区的前后没有区别。根据九牧王ERP当前的数据情况来看，我们需要采用三种不同方式的分区类型。一种是模块数据分区，再一种是大型表分区，还一种是频繁表分区。4.3.1 模块表分区模块表分区就是根据ERP各个系统、或同一系统的各个模块进行数据存储物理分离，但其逻辑表结构保持原来不变。九牧王使用的ERP中主要是分销系统数据量比较大，处理的业务比较多。其他系统相对较少使用，或者数据量很少。另外全局数据各个系统都需要用到，而且访问比较频繁，独立文件组有利于提供查询性能，我们使用如下的系统数据存储划分：其中“其他系统”除了这里列举的几个部分外的所有系统，包括生产系统、会员系统、订货会系统、决策支持系统等。其中分销系统下面还需要做详细模块划分。由于当前系统数据量最大在分销系统，所以我们重点处理分销系统的数据分区，在分销系统中有些相对比较对立模块的，也将独立文件组分区，比如分销系统中的仓库管理、代理管理、渠道管理、批发管理、进货管理、商店配货、零售管理等。每个模块独立一个文件组。4.3.2 大型表分区分销系统除了对各个大模块数据分区外，还需要对一些数据量非常大的业务单据表进行独立文件组分区。比如：商店配货单商品明细表、仓库盘点单明细表、零售销货单商品明细表等，由于考虑这些具体的业务关联性比较紧密，关联查询比较多，对表再进一步分区就很难确定分区条件。所以暂时就一类单（包含主表和明细表）一个文件组。如下图目前对大数据量的表进行独立的文件组分区。4.3.3 频繁表表分区如果某些表尽管不是很大，但需要进行大用户频繁操作读写的，我们可以对这类表把它从整个库中的文件中分离出来成为单独的分区文件。如果这类表用户操作时候常常按照一定的条件进行操作的话，我们就可以根据条件进行表分区。比如商品库存表SPKCB，该表访问读写操作比较频繁，而且需要定期对它进行清除导入的工作。操作时常常按照不同的仓库来修改数据和查询，我们可以根据仓库代码进行表分区。按照仓库代码的首字母把这张表分成27个区。4.3.4 主表明细表组合分区根据当前有些明细表非常庞大，我们考虑通过主表的单据编号根据一定的规则来生成的方式，根据单据编号作为分区条件，进行表分区。如商店配货单，以渠道代码作为单据编号的组合。4.4 优化前后测试对比前面部署了新的分区数据库后，我们可以对分区前和分区后的数据库分别进行测试。 对抽样测试了几个数据统计，测试结果如下表：5 数据库优化测试报告5.1 数据库性能优化方案数据库测试方案主要分为以下几个步骤：（1）数据库分区（2）索引优化（3）数据库镜像快照实现读写分离（4）数据仓库建设实现跨年整合数据综合统计和数据挖掘本次性能优化测试主要是数据库分区。这是整个优化方案的第一个步骤。也是最重要最优先考虑的步骤。数据库分区能够使ERP各个系统各个模块的数据从物理存储上分离开来，减少多用户并行操作不同模块的相互影响。另外还对tempdb数据库临时文件收缩，执行定期收缩。5.2 数据库分区方案数据库分区只是对表数据物理存储上的分离，并不改变数据库逻辑表结构。对应应用程序来说，分区的前后没有区别。根据九牧王ERP当前的数据情况来看，我们需要采用三种不同方式的分区类型。一种是模块数据分区，再一种是大型表分区，还一种是频繁表分区。5.3 数据库优化测试环境九牧王信息管理中心提供测试服务器配置如下：服务器：IBM PC服务器 XSERIES_3550 , Intel(R) Xeon(R) CPU E5335 2G，4G内存。操作系统：WINDOWS 2003 r2 Enterprise x64 sp2 ,硬盘：2个硬盘 ，每个72G，做了raid0 磁盘划分。 划分成C盘和D盘。C盘 30G做系统盘，安装操作系统和数据库，D盘 106G 做数据盘。存储分区前和分区后的数据库数据。5.4 数据库优化部署步骤1.在测试机服务器上部署业务数据库（假定库名为OLDBSERP），部署方法：使用数据库备份还原的方式。2.在测试机服务器上部署BSERP服务程序3.生成新的分区数据库：对OLDERP数据库做一次整个库的脚本生成。生成分区文件组、分区文件、所有表结构、索引、存储过程、视图。所有脚本。对生成的脚本进行分区配置，重新编写分区脚本和索引脚本。存为create.sql完成脚本后在测试机服务器上执行部署分区数据库（假定新的库名为NEWERP）4.关闭NEWERP数据库的触发器、关闭外键约束5.从业务数据库抽取数据到分区数据库 ：执行insert.sql ，进行抽取 （注意替换 源目标的库名 如： OLDBSERP.dbo.）6.开启NEWERP数据库的触发器和启用外键约束7.重新启动测试服务器8.配置BSERP服务程序先后分别连接分区前和分区后的数据库，进行测试对比。6 优化总思路6.1 分区思路根据ERP当前的数据情况来看，我们需要采用三种不同方式的分区类型，一种是模块表分区，再一种是大型表分区，还一种是频繁表分区：a)模块表分区所有大的模块划分成不同的区：如生产系统分销系统财务系统人事系统会员系统订货会系统决策支持系统其中决策支持系统和多账套的查询系统以及分销系统中部分综合查询，需要在数据仓库系统中实现统计分析。 由于当前系统数据量最大在分销系统，所以我们重点处理分销系统的数据分区，在分销系统中有些相对比较对立模块的，也将独立文件组分区，比如分销系统中的仓库管理、代理管理、渠道管理、批发管理、进货管理、商店配货、零售管理等。每个模块独立一个文件组。 b)大型表分区如进销存流水数据，随着时间的推移这类表数据会越来越多，根据日期来进行分区，根据粗糙估计数据量每个月大小和逐月递增的速度来确定1个月一个区还是3个月一个区等。 c)频繁表分区如果某些表尽管不是很大，但需要进行大用户频繁操作读写的，我们可以对这类表把它从整个库中的文件中分离出来成为单独的分区文件。比如商品库存表SPKCB，该表访问读写操作比较频繁，而且需要定期对它进行清除导入的工作。我们可以把此表的数据存储从库的主文件PRIMARY中独立出来设置成单独的一个文件组。 或者根据仓库代码进行表分区。6.2 分销系统模块分销系统大类分区划分仓库管理代理管理渠道管理批发管理进货管理商店配货零售管理分销中间表基础表这些子模块都作为单独分区，但是我们对每个模块中的具体有些特大的表单独列出来进行独立分区。2.仓库管理以下表都是仓库管理的表，将作为整个子模块分区。分区文件组名定为CKGL .CBTZD成本调整单CBTZD_SDCBTZDMXCBTZDMX_SDKWTZD库位调整单KWTZDMPKWTZDMXYSEND商品移仓通知单YSENDMPYSENDMXYCJRD商品移仓申请单YCJRDMPYCJRDMXSPYCD商品移仓单SPYCDMISPYCDMPSPYCDMXYSCYD验收差异单YSCYDMXSPPKD商品装箱单SPPKDMPSPPKDMXPMCXD配码拆箱单PMCXDMPPMCXDMXPMPAC配码录入单PMPACMXPMZXDPMZXDMPPMZXDMXTZBGD调整变更单TZBGDMPTZBGDMX下表商品移仓通知单的主表和明细表，其数据量比较大，有将近1G的数据，对其对立分区。CKPDD商品移仓通知单CKPDDMICKPDDMPCKPDDMX下表商品移仓库移库单的主表和明细表，其数据量比较大，有约1.8G的数据，对其对立分区。CKYKD仓库移库单CKYKDMICKYKDMPCKYKDMXCKYKDMX2下表仓库衔接单的主表和明细表，其数据量比较大，有约0.5G的数据，对其对立分区。CKXJD仓库衔接单CKXJDMX下表仓库调整单的主表和明细表，其数据量比较大，有约0.4G的数据，对其对立分区。CKTZD仓库调整单CKTZDMICKTZDMPCKTZDMX6.3 全局表全局表主要分类两个大类的分区文件组，一个是系统日志 XTRZB，另外其他全局表分为一个文件组，定义为GLOBAL分区大类表名描述1系统日志XTRZB系统日志记录表2全局表BIZHONG币种信息定义表BSGROUP用户组信息定义表BSGROUP_QX用户组权限主信息定义表BSGROUP_QX2用户组权限明细信息定义表BSUSER用户信息定义表BSUSER_GROUP用户和用户组关联关系定义表USERCK用户