数据库的性能优化解决方案.doc

数据库优化是一个很广的范围,涉及到的东西比较多,并且每个特定的数据库,其具体的优化过程也是不一样的.因为优化的很大一部分最终都要跟具体的数据库系统细节打交道,在此不可能针对所有的数据库都一一详细阐述,如果那样,恐怕写几本书都写不完.只能针对一些比较通用的,经常用到的的东西进行一个讨论,一般情况下,数据库的优化指的就是查询性能的优化(虽然严格上来说不应该是这样的),让数据库对查询的响应尽可能的快.仅对数据库系统本身而言,影响到查询性能的因素从理论上来讲，包括数据库参数设置(其实就是通过参数控制数据库系统的内存,i/o,缓存，备份等一些管理性的东西),索引,分区,sql语句.数据库参数设置本身是一个很复杂的东西，分区则主要是针对大数据量的情况下，它分散了数据文件的分布，减少磁盘竞争，使效率得到提升。 每种数据库或多或少都有一些自己特定的索引,如oracle除了常规索引之外还有反向索引,位图索引,函数索引,应用程序域索引等等,能够让用户对数据的逻辑组织有着更为精确的控制,而sqlserver没有这么多的索引,大体来说,sqlserver的索引分为两种:聚集索引和非聚集索引.在分区方面,oracle和sqlserver比较相似,不过sqlserver的分区更为繁琐一些,但随着sqlserver的版本越来越高,其分区操作也趋向于简洁.sql语句优化则基本上比较独立，目前的一些数据库系统处理sql的机制都比较类似，因为sql本身就是一个标准。这三种将会在下面作一个详细的讨论.本讨论建立在sqlserver上,因为目前部门的很多系统的数据库用到的是sqlserver,虽然oracle会给与我们更多的可探讨的范围.2:测试数据库的建立 因为要讨论索引,分区,sql等,因此有必要建立一个数据库,不然只是泛泛而谈,我在sqlserver2000上建立了一个名为ipanel的数据库,该数据库只有一张表,名为person,person的定义如下:CREATE TABLE dbo.person (id bigint NOT NULL , -记录的idname varchar (10) COLLATE Chinese_PRC_CI_AS NULL ,-姓名age int NULL ,-年龄addr varchar (50) COLLATE Chinese_PRC_CI_AS NULL ,-地址sex char (10) COLLATE Chinese_PRC_CI_AS NULL ,-性别dept varchar (50) COLLATE Chinese_PRC_CI_AS NULL ,-部门pos varchar (50) COLLATE Chinese_PRC_CI_AS NULL ,-邮编tel char (15) COLLATE Chinese_PRC_CI_AS NULL ,-电话fax char (15) COLLATE Chinese_PRC_CI_AS NULL ,-传真emdate datetime NULL -入职日期) ON PRIMARY ONPRIMARY表示该表建在系统的默认文件组上，在sqlserver里，文件组的概念就相当于oracle的表空间，是一种逻辑概念,它包含了数据文件，所谓数据文件，当然就是存储数据的文件.默认情况下，sqlserver会在默认的路径建立文件组和初始的数据文件，如果用户在建立数据库或表的时候没有指定文件组，则用默认的。数据文件,日志文件,参数文件是所有数据库系统最主要的文件,oracle还有控制文件,在很多的专业书籍里面,从数据库系统的物理结构上来讲,数据库就是指的静态的数据文件,数据库系统或者数据库实例指的是一组进程,如日志进程,数据缓冲进程，网络监听进程等,这些进程作用在各种文件上面。不说了,扯远了.建了一个数据插入的存储过程:CREATE PROCEDURE initPerson start int, end int , -起始条数，结束条数name varchar(10),age int, -姓名，年龄addr varchar(10),sex char(2), -地址，性别dept varchar(20),emdate varchar(10 -部门，入职日期ASdeclare id intset id=startwhile id20 and colume、=、 和 dateadd(day,+1,getdate()用时：12376毫秒（12秒） 在主键上建立聚集索引，在emdate上没有索引：select id,name,dept,emdate from person where emdatedateadd(day,+1,getdate()用时：21296毫秒（21秒） 在主键上建立非聚集索引，在emdate上建立非聚集索引：select id,name,dept,emdate from person where emdatedateadd(day,+1,getdate()用时：11590毫秒（12秒） 在主键上建立非聚集索引，在emdate上建立聚集索引：select id,name,dept,emdate from person where emdatedateadd(day,+1,getdate()andemdate2007-06-01查询速度：1664毫秒 select id,name,dept,emdate from personwhere emdate2007-06-01 and name=王小雪查询速度：1640毫秒 select gid,fariqi,neibuyonghu,title from personwhere name=王小雪查询速度：5920毫秒从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。3.4 其他索引经验总结1:用聚合索引比用不是聚合索引的主键速度快下面是实例语句：（都是提取25万条数据）select id,name,dept,emdate from person where emdate=2007-06-04使用时间：906毫秒select id,name,dept,emdate from person where id=100000使用时间：1153毫秒这里，用聚合索引比用不是聚合索引的主键速度略快一些。2:用聚合索引比用一般的主键作order by时速度快，特别是小数据量时select id,name,dept,emdate from person order by emdate用时：17856 (约18秒)select id,name,dept,emdate from person order by id用时：44046 (约45秒)这里可以看到，用聚集索引比用一般的主键作order by时，速度几乎快了2.5倍。事实上，有的资料说小数据量情况下，用聚集索引排序列比非聚集索引作为排序列快，10万以上，则二者的速度差别不明显。但据当前200万条数据情况来看，在大数据量的情况下，这个结论依然成立。3:使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比比例减少，而无论聚合索引使用了多少个select id,name,dept,emdate from person whereemdate=2007-06-04 00:00:00.000用时：1123毫秒（提取10万条）select id,name,dept,emdate from person whereemdate=2007-06-04 00:00:00.000用时：1843毫秒（提取20万条）select id,name,dept,emdate from person whereemdate=2007-06-09 00:00:00.000用时：4500毫秒（提取45万条）从以上统计的数据看来，这个规律基本上是正确的其他注意事项 “水可载舟，亦可覆舟”，索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥。在实际的开发中，会遇到很多意想不到的情况，最好是多测试一些方案，找出哪种方案效率最高、最为有效。4:SQL语句改善 一个sql语句大约要经过三个阶段，编译优化，执行，取值，而编译阶段，而第一阶段大部分情况下都要花掉60的时间，所以绑定变量是很重要的，sqlserver和oracle都有缓存区，存放最近使用的sql语句，当有一条sql语句到达数据库服务器时，数据库会首先搜索缓存区，看它是否存在可以重用的sql语句，如果存在，则无需编译优化，因为缓存区的sql语句都是编译优化好了的，可以直接执行，节省相当多的时间。如果没有发现该语句，则必须要完全经历语句编译分析，优化计划，安全检查等过程，这不仅耗费了大量的cpu功率，而且还在相当长的一段时间内锁住了一部分数据库缓存，这样执行sql语句的人越多，等待的时间越长，系统的性能会大幅度的下降。很多人不知道SQL语句在SQL SERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。比如：select id,name,dept,emdate from personwhere name=王小雪 and id100000 用时：1220毫秒和执行:select * from table1 where id 100000 and name=王小雪 用时：1173毫秒一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果id是一个聚合索引，那么后一句仅仅从表的100000条以内的记录中查找就行了；而前一句则要先从全表中查找看有几个name=王小雪的，而后再根据限制条件条件id100000来提出查询结果。事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。 虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。 在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。 SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下：列名 操作符 或 操作符列名列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：Name=张三 ，价格5000 ，50005000如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQL SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。介绍完SARG后，我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验：1:Like语句是否属于SARG取决于所使用的通配符的类型如：name like 王% ，这就属于SARG而：name like %小雪,就不属于SARG。原因是通配符%在字符串的开通使得索引无法使用。 如以下查询没有对name进行索引select id,name,dept,emdate from person where name like %小雪用时 3654毫秒对name进行非聚集索引select id,name,dept,emdate from person where name like %小雪用时 3673毫秒对name进行聚集索引select id,name,dept,emdate from person where name like %小雪用时 3673毫秒由以上数据可以看到，将匹配符号放在被查询字段的前面，索引根本就不会发生作用，所以这也是要注意的地方，如果不会用到，最好少用2:or 是否会引起全表扫描有很多资料上说or会引起全表扫描。如name=王小雪 and emdate2007-01-10不会全表扫描,而name=王小雪 or emdate2007-01-10则会,但是据我观察,情况不是这样的.对于这样的一个sql语句select id,name,dept,emdate from person where name=王小雪 or emdate2007-06-08，我们可以看sqlserver对于它们的执行计划在有聚集索引的情况下(无论聚集索引建在哪些字段上)没有聚集索引但是主键索引的情况下没有任何索引的情况下由上可以得出结论,在用到or的时候，如果有聚集索引,就不会引起全表扫描,没有聚集索引,就会引起全表扫描，所以说，只要用or就会引起全表扫描是片面的，不正确的。3:非操作符、函数引起的不满足SARG形式的语句不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、!、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。下面就是几个不满足SARG形式的例子：ABS(价格)5000 ,SQL SERVER也会认为是SARG，SQL SERVER会将此式转化为： WHERE 价格2500/2 .但不推荐这样使用，因为有时SQL SERVER不能保证这种转化与原始表达式是完全等价的。4:IN 的作用是否相当与OR看下面的查询情况。有聚集索引select id,name,dept,emdate from person where name in(王小雪,聂海)所花时间:8936ms,select id,name,dept,emdate from person where name=王小雪 or name=聂海所花时间:5390ms,没有聚集索引select id,name,dept,emdate from person where name in(王小雪,聂海)所花时间:5310ms,select id,name,dept,emdate from person where name=王小雪 or name=聂海所花时间:5326ms,可见,or 比 in速度快,因为作了聚集索引,所以它们都没有执行table scan,不过因为聚集索引作用在日期字段emdate上,所以虽然查询使用了聚集索引,但并不意味着比全表扫描快,其实使用作用在emdate上的聚集索引查询,在某种意义上来说,也是一种全表扫描,只不过数据的扫描顺序不同而已,在这种情况下,甚至没有聚集索引反而更快5:exists 和 in 的执行效率是一样的 很多资料上都显示说，exists要比in的执行效率要高，同时应尽可能的用not exists来代替not in。但事实上，我试验了一下，发现二者无论是前面带不带not，二者之间的执行效率都是一样的。因为涉及子查询，我试验这次用SQL SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics I/O状态打开。 语法为:set statistics io on, 要查看语句的执行过程,打开查询分析器的消息栏就可以看到,但是在查询语句之前要加上set statistics io on（1）select title,price from titles where title_id in (select title_id from sales where qty30)该句的执行结果为：表 sales。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。表 titles。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。（2）select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty30)第二句的执行结果为：表 sales。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。表 titles。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。我们从此可以看到用exists和用in的执行效率是一样的。6:用函数charindex()和前面加通配符%的LIKE执行效率一样 前面，我们谈到，如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。但有的资料介绍说，用函数charindex()来代替LIKE速度会有大的提升，但据我测试，发现这种说明也是错误的：select id,name,dept,emdate from person where charindex(小雪,name)0用时：4010ms扫描计数 1，逻辑读 29905 次，物理读 0 次，预读 0 次。select id,name,dept,emdate from person where name like %小雪用时：4123ms扫描计数 1，逻辑读 29905 次，物理读 0 次，预读 0 次。7:union并不绝对比or的执行效率高 很多资料都推荐用union来代替or。事实证明，这种说法对于大部分都是适用的。(1):select id,name,dept,emdate from person where name=王小雪 or emdate2007-06-04用时：85626ms。扫描计数 1，逻辑读 129905 次，物理读 0 次，预读 0 次。次。(2):select id,name,dept,emdate from person where name=王小雪unionselect id,name,dept,emdate from person where emdate2007-06-04用时：17373ms。扫描计数 2，逻辑读 59810 次，物理读 0 次，预读 0 次。看来，用union在通常情况下比用or的效率要高的多。5:sqlserver的分区 对于一些超大型的表，分区是非常有用的。分区是一种逻辑概念，和oracle的分区概念是一样的.在通常情况下，一个表就是一个整体，当发生数据访问的时候，也是对整个表或整个表的索引进行访问，所谓分区，通俗点讲，就是把表按一定的规律划分成更小的逻辑单位,当发生访问的时候,不以表为单位进行访问,而先在表的基础上,判断数据在哪个分区,然后对特定的分区进行访问.正确的分区有利于提高查询性能.例如,有一个非常大的表,存储了一些销售记录,现在查询总是按销售季度来执行这个查询-每个销售季度包含几十万个记录,通常你只是要查询这个数据集的一个相当小的数据,但是给予销售季度的检索却的确是不太可行的.这个索引可能指向无数个记录,而以这种方式执行索引范围扫描是可怕的.为了处理许多查询任务,系统需要执行全表扫描,但是结果却必须扫描几百万个记录,其中绝大部分不使用我们的查询任务.使用智能分区方案,就可以按季度隔离数据.这样当我们为任意指定的季度去查询数据时,结果将只是扫描那个季度的数据.这是所有可能的解决方案种最好的方案.下面将介绍sqlserver的分区使用. 分区是比较复杂的，以分区的对象来分类的话，则分为两种，表分区和索引分区。J表分区主要指的是范围分区，(貌似比较单一，oracle里有散列分区等等，不过在sqlserver里我目前还没有看到).就这么说可能不清不楚，下面将以我们已经建立好的ipanel数据库为例，对person表进行按日期分区，假设ipanel每个月都要进出几十万人，然后HR每月还要作很多的统计吧。下面一步一步来，common创建文件组各种数据最终是存储在数据文件里，在实际应用中，表的分区都会分布在多个数据文件中，这样以便获得更好的 I/O 平衡，对于文件，是以文件组为单位进行管理，文件组相当于目录，数据文件就相当于目录里的文件。为数据库添加文件组，这个文件组分布存储person表的数据：ALTER DATABASE ipanel ADD FILEGROUP person_fg现在为ipanel数据库创建了一个名为person_fg的文件组。下面为该文件组添加数据文件。添加数据文件ALTER DATABASE ipanelADD FILE(NAME = Nperson001,FILENAME = NC:ipanelperson001.ndf,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)TO FILEGROUP person_fg如上，为文件组添加了一个数据文件创建分区函数既然分区，那么就应该有一个分区的标准，就是说数据将以什么标准来分区，分区函数就是做这件事情的，它定义数据划分的标准，对表进行逻辑上的划分。CREATE PARTITION FUNCTION personRangePFN(datetime)ASRANGE LEFT FOR VALUES (20030930,20050930,20070930,20090930)上面的分区函数创建了5个分区，并且定义了分区列的数据类型为datetime,因为分区的标准要建在表的某一列上，在此定义，分区列必须是日期时间型。RANGE LEFT表示范围分区，LEFT所在的选项有两个：LEFT,RIGHT.分区标识着数据的上界和下界。如当前选项是LEFT，则表示：分区1：20030930,20050930,20070930,20030930如果当前选项是RIGHT,则表示：分区1：=20030930,=20050930,=20070930,=20030930创建分区架构创建分区函数后，必须将其与分区架构相关联，以便将分区定向至特定的文件组。定义分区架构时，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。对于前面创建的范围分区 (personRangePFN)，存在五个分区；最后一个空分区将在 PRIMARY 文件组中创建。因为此分区永远不包含数据，所以不需要指定特殊的位置CREATE PARTITION SCHEME PersonEmdateSchemeASPARTITION personRangePFNTO (person001, person002, person003, person004, PRIMARY)创建分区表 定义分区函数（逻辑结构）和分区架构（物理结构）后，即可创建表来利用它们。表定义应使用的架构，而架构又定义函数。要将这三者结合起来，必须指定应该应用分区函数的列。范围分区始终只映射到表中的一列，此列应与分区函数中定义的边界条件的数据类型相匹配。另外，如果表应明确限制数据集（而不是从负无穷大到正无穷大），则还应添加 CHECK 约束。CREATE TABL