今年所做的优化,大的涉及到体系架构改造,通过cache来减少sql执行次数,通过更改应用的实现方式等,小的如加hint,建索引等等,大大小小快接近上百个了,如何来做优化,这边我稍微阐述一下。
1.扎实的基本功
数据库最基本的理论要清楚,要理解的透彻一点。比如索引为什么有些块delete后不能重用,而表delete后这些块很快会被重用掉的?想象索引B树的树状结果,想象关系型数据库heap表的最大特点是什么,就很容易理解上述问题了。我一直认为会什么不重要,重要的是有扎实的基本功,勤奋,态度,态度能决定一切,很多东西要通过时间来沉淀的,叶开提到过,statspack中每个sql该排在报表中的哪个位置要清清楚楚,这样一有风吹草动很容易快速定位出来。
2.如何判定sql的性能?
通过逻辑读大小是很难断定sql是否优化,看是很难看出来的,要结合业务,返回的数据量来判断。
举个例子,web分页语句每页显示20行,单次逻辑读消耗在60左右,每小时执行次数在300万左右,这种sql要学会评估,假设20行各分散在不同的块上,取20行的数据最多需要20个块+索引扫描取rowid 差不多在3-10个块左右,那sql消耗的逻辑读差不多在30个块左右,逻辑读在60左右肯定是高了,如果能优化到30左右,每小时节省的逻辑读在300万执行次数*30,差不多1个亿的逻辑读。我们这种系统中,很多sql每小时执行次数在百万级别之上,甚至在达到千万级别,sql优化要特别重视,也很有效。
3.理解分页list的两种写法
基本上是必修课了,有不用这个功能的应用吗?分页对于oracle来说有两种写法:普通写法和rowid写法,要深刻理解这两个写法,特别是rowid写法,优势是什么,用在哪个地方,具体的可以参考http://www.taobaodba.com和piner的书,里面有多篇文章。
4.更改sql的实现方式
基本上我是禁止大表之间的关联查询,如果核心表出现这种 查询,我是坚决要求改掉,很多时候宁愿开发拆成两个sql,分两次来执行。举个例子,比如汇总表(sborder_biz)和明细表(sborder_detail)是一对多的关系,一个汇总存在多个明细,我一般都是建议在明细表做的足够详细,做到不需要关联汇总表来实现。大家可以想象一下,关联两个大表,一个表走索引(t2.it_id索引)另一个表只能走关联的键值(t1.orderid) 了,进而回表,对于oltp型的数据库来说,记录是非常分散的,回表的代价很大,t2返回多少条记录,就需要去回表t1的多少记录。
select t1.order_id,t2.createdate,t2.deleted from sborder_biz t1, sborder_detail t2
where t1.order_id = t2.order_id and t1.deleted = 0 and t2.it_id = :1 and t2.code = :2
5.建合适的索引
建合适的索引,特别是联合索引,需要很多技巧的,要根据查询条件,根据索引列的数据分布来建立,联合索引的好坏对sql性能影响非常大,这个在《sql查询中尽量减少<>的使用》中也有描述.
6.不用is null条件
建表时,列要尽量设置成not null,尽量设置成不为空或是默认值,我是很反对这种写法的.select * from table where (status=0 or status is null)
7.关注细节,关注应用实现方式
是的,一定要多关注细节,从小事做起,我认为这是最重要的。
简单总结了这么几条,其他的如怎么利用cache,核心表怎么设计等这些以后会补充进来,实际上面我所提到的几条,任何一条是否能做好,对数据库的性能影响都是极大的。优化我最强调的是细节,特别是自己跟的应用,好比小时候去河里拣田螺,一次一小颗,什么时候才能拣到一篮呢,优化也是,我们这么大的系统,刚开始优化很难出效果的,做好每一个sql,关注每个sql的实现方式,积少成多,当你埋头在拣田螺时,篮子也渐渐满了。