sql查询优化之个人体会

来源:互联网 发布:华师网络自助缴费系统 编辑:程序博客网 时间:2024/03/28 22:26

在数据量不大(我说的是少于十万条数据)的情况下,怎么写SQL可以说是随心所欲,只是没语法错误就成了。不过,在经历了庞大数据量的考验之后(OR考验中),俺终于用惨痛的经历和教训,开始体会查询优化的极度重要!是的,你可以随便JOIN,随便LIKE,或者加无数个OR和IN,不过,一切都只是在数据少的前提下。面对百万级的数据,要谨慎、谨慎再谨慎。只是,有时明明知道语句运行得很慢,却又觉得除此之外别无良方。呵呵,相信方法总比“死锁”多!查询优化的方法也会一直总结体会出来的。现在先写下个人惨痛体会和各位大虾们的高招。不过有些网上招数不一定实用,往往自己实验过之后得到相反答案,所以,实践见真知!有啥东西俺以后继续补贴!

1、强烈反对select *!,要多少字段查多少。十个八个字段的表不显得什么,长表可是折磨得够晕!

2、可以left join的时候不要full join,可以inner join的时候不要left join。没啥,节省下资源。

3、在经常用于查询,常用于group by,但是取值范围不大的字段做聚集索引,选些其它做非聚集索引。比如一个很多中学教师的信息表,俺会用“学科”来做聚集索引,而不是用他的ID号、或者出生年份做。我的原因,你懂的。

4、group by a1,a2,a3的时候,尽量将索引字段排在首位。如果你需要嵌套很多次group by,如,select a1 from (select b1 from b where XXX group by b1)t inner join a on b.b1=a.a2 group by a1之类的(手写简单版,不保证正确性),那最好还是一次group完吧。你肯定有办法的。

5、如果A表很大,B表只是A的几份之一,select XXX from A left join B on A.A1=B.B1居然比反过来要快。即是从大表连到小表。很奇怪,求高人解释。

6、IN的效率其实不怎么快。因为IN(XX,XXX,XXXX)是会解释成OR的。OR是要引用全表扫描的。

7、很多人说LIKE和CHARINDEX的效率是一样的,因为两者都用不了索引。不过我又感觉CHARINDEX是快些的。特别是被匹配字符很多的时候。

8、或者你认为where 条件一 and 条件二 and 条件三,将条件的次序,对查询速度会有影响,但是基本上是没有的,可能SQL本身就会分析最优的查询策略吧。又或者将条件放入子查询里,再去联接其它表,跟将条件放在外面,会有不同。例如select a1,b1 from A left join B on A.aa=B.bb where a.CC=xxx...和select a1,b1 from(select a1,aa from A where a.CC=xxx)t left join B on t.aa=B.bb 效率会不同。不过,暂以我的经历,我也觉得区别不太大。

9、不要重复地去联接一个表。可能听上去很“憨居”,不过有时真的会发生。这样感觉和陷入死锁差不多了。比如:select aid,aname,atype from (select aid from B inner join A on B.XX=A.XXX where 条件条件条件... group by A.aid)t inner join A on t.aid=A.aid。那还不如

select  aid,aname,atype from from A inner join B on A.XXX=B.XX where 条件条件条件 group by A.aid,aname,atype

10、distinct是把双刃剑。小表又不想group by,是挺快的。就是感觉数据很大时,搞得人够呛。

11、Exists和not Exists不快,可以有“替身”。曾经试过用判断select count(*) from A是否大于零去代替not exists,效果不错。也许你会说count(*)!,第一条不是反对*?!如果这个表有主键,count(*)据说和count(主键)一样。俺体会过一下,不慢。

12、不到万不得已,少用游标。如果要针对每行数据进行很复杂的操作,那没话说,只是简单的更新插入,就避之则吉。

13、表变量和临时表。其实数据不多的时候,表变量挺快的,其余时候,临时表快一些。不过,想来俺们也不会插个一万几千条数据去表变量吧?!没啥情况,俺两个都不想用。

14、不要动不动就用自定义函数放在查询条件里,不骗大家,真的很慢。亲身惨痛体会。

15、TOP子句是很高效的。哎,其实俺是想说,不要一万几千的数据装到一个数据集返回来,分个页就好了。不关查询优化的事,是物理I/O问题。

好了,暂时只有这么多可以写了。未来未知的原则,可能又要经过惨痛经验才能得出。稍等吧。。。

同时欢迎看贴的大虾在楼下补充。