如何写出性能好的sql

wolfstar

开发人员是很少注意SQL对数据库性能影响的重要性的，大多程序员都会认为SQL是比较简单的，需要的时候查查手册就可以了，很少有深究的。
这样的观念对大型系统的开发是致命的，需要纠正这样的观念。
造成这样的原因，可能有如下几种：
1，对数据库性能的研究，成果不是显而易见，对程序员的成就感激励不足，因为开发环境中的数据很少，数据库性能好坏体现不出来，好的sql和差的sql体现不出差别，所以，更多的人选择的是写出来就可以了，没有想到过性能。没有吃过这方面的亏，没有深刻的教训，人总是要有了教训才会重视，而以教训作为学习方法其实是很高成本的。
2，程序员更重视能写多少功能，会多少语言，对于一些不是显而易见和不好衡量的能力是不够重视的。
今天客户的一套系统因为一段sql没有考虑性能问题，导致数据在几万条数据的情况下，竟然也出现了巨大的性能开销，导致系统瘫痪无法使用的状况，所以，觉得有必要将一些心得写出来与大家共享。
索引是非常重要的，但是很多人是不重视的。
我总是不厌其烦的向很多开发人员介绍索引的重要性，但是，很多时候都可以从他们脸上看出来那种不以为然，但我还是在不厌其烦的向他们讲解，因为索引能否正确使用对系统的性能太重要了。
我觉得几个重要而简单的概念应该是必须掌握的：
a.聚集索引和非聚集索引，各应该用在什么场合
b.什么样的语句会使用索引，什么样的语句将不使用索引
c.应该在什么样的字段上面建立索引
d.复合索引的使用特点
一些具体的规则，如果不能理解，死记住也会获益
1，经常用来做联接的字段上面加索引
2，经常用来做条件的字段上面加索引
3，坚决避免在条件中使用否定意义的计算符，如：
      select * from table1 where column1 not in (select column2 from table2)
      select * from table1 where column1 not exists (select column2 from table2)
     select * from table1 where id  100
4，聚集索引能对范围查询的性能产生巨大的提升，一定要善加利用，但是如果使用不当，也会带来巨大的性能损失
     如你可以将一个订单表的订单日期加上聚集索引，订单日期是递增的，这样你按照日期范围查询时，你将获得最高的性能
     select * from 客户订单 where 订单日期 between '2005-1-1' and '2005-1-31'  --是范围的查询
    但是你把客户编号作为聚集索引，将不会带来重大的性能提升，反而会有负效果，因为订单的顺序客户编号是非顺序的，这样由于聚集索引需要重新排列物理磁盘，这样将会给数据写入带来巨大的开销
5，不要在生成顺序不规则的字段上面加聚集索引，应该选择能够按照递增或递减顺序生成数据的字段作为聚集索引的字段
6，坚决避免对条件字段何作为连接的字段进行运算，甚至使用函数，如：
      select * from UserInfo where firstname + lastname = 'Bill Gates'
      select * from UserInfo where dbo.fGetBasicSalary(UserInfo.UserId) = 2000
      select * from table1 inner join table2  on table1.A + table1.B = table2.C
7，建立索引是有必要的，但不是越多越好      
8，可能的话，主键多用整型值，用一个整型字段做连接，比一个字符串在大数据量的情况下性能会提高很多
9，不要把大数量的内容放到 in ()里面，如避免如下写法：
      select * from table1 where column1 in (select column2 from table2) --如果此时table2的数据量比较大的情况下，性能将会非常差
      其实类似的语句经常可以改写为连接的方式来实现
10，经常用来Order By的字段加上索引，也会有性能的提升
11，子查询不要出现太多，大多数能够使用外连接来替代
select *,
(select z1 from T1 where z2=T2.Id) z1,
(select z2 from T1 where z2=T2.Id) z2,
(select z3 from T1 where z2=T2.Id) z3,
from T2
这段语句肯定不是优化的，会有性能问题的，可以有如下的方式来改写
select *, T1.Z1, T1.Z2, T1.Z3
from T2 left outer join T1 on T1.Z2 = T2.ID
12，对于like的使用
       推荐：select * from 用户 where 姓名 like '李%'
       不推荐：select * from 用户 where 姓名 like '%白%'

建议：
      写点东西算作抛砖引玉吧，希望能给一些数据库初学者一些指引和建议吧！
      还有就是，如果我们的系统出现了性能的问题，多从软件的角度来考虑优化，而不是动不动就要求客户升级服务器，硬件的改善对性能的提升能力是很有限的，提高一倍，两倍都是很厉害的了，而软件的优化有时候能起到数十倍，上百倍，甚至更高的性能提升。
      呼吁大家提高对数据性能的重视。


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/7496/showart_90373.html