DBA,如果你心动,就赶紧行动,当然,作为DBA重要职责之一的就是数据的优化,通过和咱项目经理的浅谈,对于新入门的菜鸟是不可能一下就接触到优化内容了,至少必须有两年左右的经验才能真正领悟优化的内涵。
提前了解这个行业岗位的技术背景,这是一个ITPUB论坛的话题探讨,答案很齐全,可自行翻阅。
为了保证数据库的一致性和完整性,在逻辑设计的时候往往会设计过多的表间关联,尽可能的降低数据的冗余。
如果数据冗余低,数据的完整性容易得到保证,提高了数据吞吐速度,保证了数据的完整性,清楚地表达数据元素之间的关系。 而对于多表之间的关联查询(尤其是大数据表)时,其性能将会降低,同时也提高了客户端程序的编程难度。 因此,物理设计需折衷考虑,根据业务规则,确定对关联表的数据量大小、数据项的访问频度,对此类数据表频繁的关联查询应适当提高数据冗余设计但增加了表间连接查询的操作,也使得程序的变得复杂 为了提高系统的响应时间,合理的数据冗余也是必要的。设计人员在设计阶段应根据系统操作的类型、频度加以均衡考虑。 ===================================================================== 【1】表设计的优化 1、数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询效率。 2、能够用数字类型的字段尽量选择数字类型而不用字符串类型的。 3、对于不可变字符类型char和可变字符类型varchar 都是8000字节,char查询快,但是耗存储空间,varchar查询相对慢一些但是节省存储空间。 4、字段的长度在最大限度的满足可能的需要的前提下,应该尽可能的设得短一些,这样可以提高查询的效率,而且在建立索引的时候也可以减少资源的消耗。 5、字段顺序对存储效率也有不小的影响。在做表结构设计的时候,我们往往不会去考虑字段的摆放顺序。但是,实际上字段的摆放顺序对数据库操作的性能是有影响的。 越靠后的字段效率越低 :比如4AAAA6BBBBBB2CC,当要扫描到2CC时,数据库并不知道C的位置,如果头占了10个长度,那么C的位移就是 10+(4+1)+(6+1)+1=23.每个字段没有直接的位移地址的,既然没有23的位移,ORACLE只有通过前面的头,以及A B的位置来推算C的位移。那么越往后面的字段,推算位移次数越多,CPU计算的次数也越多。 越靠后的字段操作开销越大 :刚刚说了越往后面的字段,推算位移次数越多,CPU计算的次数也越多,这就意味着CPU的开销就越大。 6、针对各种类型的表来存储各种类型的数据。例如什么时候使用索引组织表,什么时候必须使用堆表能对系统能带来有利影响是需要考虑之一 ===================================================================== 【2】 语句的查询优化 二、查询的优化 1、保证在实现功能的基础上,尽量减少对数据库的访问次数; 2、通过搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负担;能够分开的操作尽量分开处理,提高每次的响应速度; 3、在数据窗口使用SQL时,尽量把使用的索引放在选择的首列;算法的结构尽量简单; 4、在查询时,不要过多地使用通配符如SELECT * FROM T1语句,要用到几列就选择几列如:SELECT COL1,COL2 FROM T1; 5、在可能的情况下尽量限制尽量结果集行数如:SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。 6、在没有建索引的情况下,数据库查找某一条数据,就必须进行全表扫描了,对所有数据进行一次遍历,查找出符合条件的记录。 7、在数据量比较小的情况下,也许看不出明显的差别,但是当数据量大的情况下,这种情况就是极为糟糕的了。 8、合理的使用临时表。例如表A 的 ID 字段有索引,并且这个表的数据有很多。这时候要查询这个ID 的最大值与最小值,如果能合理使用临时表,速度将大幅度提高! 9、多层的子查询需要进行简单化。 ===================================================================== 【三】、建立高效的索引 创建索引一般有以下两个目的:维护被索引列的唯一性和提供快速访问表中数据的策略。 大型数据库有两种索引即簇索引和非簇索引,一个没有簇索引的表是按堆结构存储数据,所有的数据均添加在表的尾部,而建立了簇索引的表,其数据在物理上会按照簇索引键的顺序存储,一个表只允许有一个簇索引 因此,根据B树结构,可以理解添加任何一种索引均能提高按索引列查询的速度,但会降低插入、更新、删除操作的性能,尤其是当填充因子(Fill Factor)较大时。 所以对索引较多的表进行频繁的插入、更新、删除操作,建表和索引时因设置较小的填充因子,以便在各数据页中留下较多的自由空间,减少页分割及重新组织的工作。 据说大部分的数据库性能问题都可以采用索引技术得到解决。 作为一条规则,我通常对逻辑主键使用唯一的成组索引,对系统键(作为存储过程)采用唯一的非成组索引,对任何外键列[字段]采用非成组索引。 虽说,索引可以大幅度的提高查询效率。但这个索引也不是随便使用的!因为:索引的维护也需要开销!特别是高并发量的数据库表,索引块分裂导致的等待事件特别严重。 另外,有了索引也未必就能一定提高查询效率。这个与索引所在字段的倾斜度,选择度有密切的关系。 ===================================================================== 【四】 强制查询转换 有时候oracle 的优化器未必能走正确的查询路线,这个时候就需要添加一些hint 之类的来规定他的执行路线。 当然了,。这个未必是最好的处理方案。因为虽然现在走这个路线是对的,以为因为数据的变化到这这个HINT 变得不可取! -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 第二个问题很好说,在日常我们开发的系统中有很多的SQL 语句被写成N层嵌套,看着头都大..在了解了业务之后,将复杂的SQL 改成多个简单的语句逐步处理。 再或者用with as 语法,这个解决了不少的性能的问题。 再者,很多同事们不知道或者不会使用分析函数,或者认为它会带来一定的性能问题。其实这是错的。使用分析函数的查询,不但语法简洁,查询效率来看比自己拼得多层查询的效率要好很多。
对与并发量极高的SQL语句进行优化?
通常优化的方向:快速返回完成操作, 减少资源争用, 可以采用:1、使用绑定变量, 仅进行一次"硬解析", 减少CPU的争用2、在查询谓词上创建适当索引,尽量实现全索引扫描,避免全表扫描和回表3、对于索引可以采用反向索引,避免索引热块的争用4、对于采用DBLINK 访问远程数据库表或对象,采用物化视图预先同步到本地,减少网络传输5、保持查询对象(表、索引)统计的时效性,避免查询计划改变或失效查询的优化
保证在实现功能的基础上,尽量减少对数据库的访问次数;通过搜索参数,尽量减少对表的访问行数,最小化结果集,从而减轻网络负担;能够分开的操作尽量分开处理,提高每次的响应速度;在数据窗口使用SQL时,尽量把使用的索引放在选择的首列;算法的结构尽量简单;在查询时,不要过多地使用通配符如 Select * FROM T1语句,要用到几列就选择几列如:Select COL1,COL2 FROM T1;
在可能的情况下尽量限制尽量结果集行数如:Select TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。
在没有建索引的情况下,数据库查找某一条数据,就必须进行全表扫描了,对所有数据进行一次遍历,查找出符合条件的记录。在数据量比较小的情况下,也许看不出明显的差别,但是当数据量大的情况下,这种情况就是极为糟糕的了。SQL语句在SQL SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解。比如:select * from table1 wherename='zhangsan' and tID > 10000
select * from table1 where tID > 10000 and name='zhangsan'
select id from t where num is null
select id from t where num=0
select id from t where num=10 or num=20
select id from t where num in(1,2,3)
select id from t where num between 1 and 3
即使NAME字段建有索引,前两个查询依然无法利用索引完成加快操作,引擎不得不对全表所有数据逐条操作来完成任务。
而第三个查询能够使用索引来加快操作。
6.必要时强制查询优化器使用某个索引,如在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:可以改为强制查询使用索引:select id from t with(index(索引名)) where num=@num
Select * FROM T1 Where F1/2=100
elect num from a where num in(select num from b)
IF (Select COUNT(*) FROM table_name Where column_name = 'xxx')
IF EXISTS (Select * FROM table_name Where column_name = 'xxx')
SelectnameFROM employee Where salary > 60000
Select orderID FROM Details Where UnitPrice > 10 GROUPBY orderID
SelectDISTINCT orderID FROM Details Where UnitPrice > 10
UNION ALL不执行Select DISTINCT函数,这样就会减少很多不必要的资源
25.尽量不要用Select INTO语句。
Select INOT 语句会导致表锁定,阻止其他用户访问该表。 上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较不同的语句以得到最佳方案。最好的方法当然是测试,看实现相同功能的SQL语句哪个执行时间最少,但是数据库中如果数据量很少,是比较不出来的,这时可以用查看执行计划,即:把实现相同功能的多条SQL语句考到查询分析器,按CTRL+L看查所利用的索引,表扫描次数(这两个对性能影响最大),总体上看询成本百分比即可。