• / 18
  • 下载费用:10 金币  

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

关 键 词:
数据库的性能优化解决方案.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 , --记录的 id [name] [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]ON[PRIMARY]表示该表建在系统的默认文件组上,在 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 --部门,入职日期 AS declare @id int set @id=@start while @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()) andemdate'2007-06-01' 查询速度:1664 毫秒 select id,name,dept,emdate from person where emdate'2007-06-01' and name=’王小雪’ 查询速度:1640 毫秒 select gid,fariqi,neibuyonghu,title from person where 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 或 操作符列名 列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。如: 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 emdate’2007-01-10’不会全表扫描,而 name=’王小雪’ or emdate’2007-01-10’则会,但是据我观察,情况不是这样的.对于这样的一个 sql 语句 select id,name,dept,emdate from person where name='王小雪' or emdate'2007-06-08',我们可以看 sqlserver 对于它们的执行计划 在有聚集索引的情况下(无论聚集索引建在哪些字段上) 没有聚集索引但是主键索引的情况下 没有任何索引的情况下 由上可以得出结论,在用到 or 的时候,如果有聚集索引,就不会引起全表扫描,没有聚集索引,就会引起全表扫描,所以说,只要用 or 就会引起全表扫描是片面的,不正确的。3:非操作符、函数引起的不满足 SARG 形式的语句 不满足 SARG 形式的语句最典型的情况就是包括非操作符的语句,如:NOT、!=、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 emdate'2007-06-04' 用时:85626ms。扫描计数 1,逻辑读 129905 次,物理读 0 次,预读 0 次。次。 (2):select id,name,dept,emdate from person where name='王小雪' union select id,name,dept,emdate from person where emdate'2007-06-04' 用时:17373ms。扫描计数 2,逻辑读 59810 次,物理读 0 次,预读 0 次。 看来,用 union 在通常情况下比用 or 的效率要高的多。 5:sqlserver 的分区 对于一些超大型的表,分区是非常有用的。分区是一种逻辑概念,和 oracle 的分区概念是一样的.在通常情况下,一个表就是一个整体,当发生数据访问的时候,也是对整个表或整个表的索引进行访问,所谓分区,通俗点讲,就是把表按一定的规律划分成更小的逻辑单位,当发生访问的时候,不以表为单位进行访问,而先在表的基础上,判断数据在哪个分区,然后对特定的分区进行访问.正确的分区有利于提高查询性能.例如,有一个非常大的表,存储了一些销售记录,现在查询总是按销售季度来执行这个查询----每个销售季度包含几十万个记录,通常你只是要查询这个数据集的一个相当小的数据,但是给予销售季度的检索却的确是不太可行的.这个索引可能指向无数个记录,而以这种方式执行索引范围扫描是可怕的.为了处理许多查询任务,系统需要执行全表扫描,但是结果却必须扫描几百万个记录,其中绝大部分不使用我们的查询任务.使用智能分区方案,就可以按季度隔离数据.这样当我们为任意指定的季度去查询数据时,结果将只是扫描那个季度的数据.这是所有可能的解决方案种最好的方案.下面将介绍 sqlserver 的分区使用.分区是比较复杂的,以分区的对象来分类的话,则分为两种,表分区和索引分区。 表分区主要指的是范围分区,(貌似比较单一,oracle 里有散列分区等等,不过在sqlserver 里我目前还没有看到).就这么说可能不清不楚,下面将以我们已经建立好的 ipanel数据库为例,对 person 表进行按日期分区,假设 ipanel 每个月都要进出几十万人,然后 HR每月还要作很多的统计吧。下面一步一步来,common创建文件组 各种数据最终是存储在数据文件里,在实际应用中,表的分区都会分布在多个数据文件中,这样以便获得更好的 I/O 平衡,对于文件,是以文件组为单位进行管理,文件组相当于目录,数据文件就相当于目录里的文件。为数据库添加文件组,这个文件组分布存储 person 表的数据: ALTER DATABASE ipanel ADD FILEGROUP [person_fg] 现在为 ipanel 数据库创建了一个名为 person_fg 的文件组。下面为该文件组添加数据文件。 添加数据文件 ALTER DATABASE ipanel ADD FILE (NAME = N'person001', FILENAME = N'C:\ipanel\person001.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [person_fg] 如上,为文件组添加了一个数据文件 创建分区函数 既然分区,那么就应该有一个分区的标准,就是说数据将以什么标准来分区,分区函数就是做这件事情的,它定义数据划分的标准,对表进行逻辑上的划分。 CREATE PARTITION FUNCTION personRangePFN(datetime) AS RANGE 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 PersonEmdateScheme AS PARTITION personRangePFN TO ([person001], [person002], [person003], [person004], [PRIMARY])创建分区表 定义分区函数(逻辑结构)和分区架构(物理结构)后,即可创建表来利用它们。表定义应使用的架构,而架构又定义函数。要将这三者结合起来,必须指定应该应用分区函数的列。范围分区始终只映射到表中的一列,此列应与分区函数中定义的边界条件的数据类型相匹配。另外,如果表应明确限制数据集(而不是从负无穷大到正无穷大),则还应添加 CHECK 约束。CREATE TABLE [dbo].[person] ( [id] [bigint] NOT NULL , --记录的 id [name] [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 PersonEmdateScheme (emdate) 如果要限制的 emdate 的值的范围,则必须给它加上约束,如只允许 emdate 的值从 2002年 9 月 1 日到 2010 年 9 月 1 日, 则将[emdate] [datetime] NULL 改为 [emdate] [datetime] NULL CONSTRAINT personRangeYear CHECK ([emdate] = '20020901' AND [emdate] = '20100901 11:59:59.997') 分区总结 到此,对于分区表 person 已经设置完毕,person 的数据会根据 emdate 的值分布到几个不同的数据文件里,在查询的时候,系统会首先判断 emdate 的值,看它在哪个分区,然后只进入该分区查找数据,这对于超大规模的系统来说,是很有用的,如果一个表有几千万上亿的数据,即使是索引扫描也是一个很费时的过程,不要忘记,索引也就像相当于简化了的表。对于索引,sqlserver 里有索引分区,如果索引分区和表分区对齐的话,就是说和表一样使用了相同的分区函数和相同的分区架构,那么对于索引的查找,就不是对整个索引的查找了,而是先判断在哪个索引分区,然后再取查找该索引值,然后找到数据,这样就会节省很多时间。分区还有一个好处就是,对于一些数据可以更好的进行管理,比如说,定义了 2006 年度的销售数据存储在对应的分区 area6,而 area6 对应的数据文件是 sale006.ndf,到 2007 年的时候,一般情况下,可能不用 06 年的数据,按照分区的理论,它也不会访问 06 年的数据所在的区域。 分区的应用是比较复杂的,上面只是介绍了其中一部分,其他还有索引分区,分区合并,分区移出等比较多的的东西。在 oracle 里,分区的概念是比较多的,包括对索引的分区都会有很多介绍,如散列分区,混合分区,局部索引,全局索引,原理上是差不多的。在此谈分区只是一个抛砖引玉的过程,如果对 sqlserver 分区想更深入了解的话,可以看看 msdn,有中文的,不过翻译得很烂。 6:后记 我以前看到过很多项目,数据库系统只是被纯粹的当作了一个存储数据的地方,建完表能增删改查就万事大吉了,有的连索引都没有,对于数据库的建立也很不严谨,更谈不上管理,虽然很多人认为数据库的管理是 DBA 的事情,但是我想作为一个技术人员,加深对数据库的了解是绝对没有坏处的,开发大型的系统,数据库肯定是非常重要的。如果想深入学习一门数据库的话,我建议大家从 oracle 开始学,因为 sqlserver 作了很多封装,而 oracle 更为复杂,是的,虽然它概念很多,比较复杂,但是却有助你了解更多的数据库细节,在很多方面,大部分的数据库系统都是相同的,oracle 学好再来学其他的数据库,上手就非常容易,如果你会写PL/SQL 程序,那有什么理由不会写 TRANACT-SQL 的数据库程序呢,语法只是一些细微的差别,而很多的概念却是相同的。
展开阅读全文
  微传网所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
0条评论

还可以输入200字符

暂无评论,赶快抢占沙发吧。

关于本文
本文标题:数据库的性能优化解决方案.doc
链接地址:https://www.weizhuannet.com/p-10087945.html
微传网是一个办公文档、学习资料下载的在线文档分享平台!

网站资源均来自网络,如有侵权,请联系客服删除!

 网站客服QQ:80879498  会员QQ群:727456886

copyright@ 2018-2028 微传网络工作室版权所有

     经营许可证编号:冀ICP备18006529号-1 ,公安局备案号:13028102000124

收起
展开