1、首先要搞明白什么叫执行计划?
执行计划是数据库根据 SQL 语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生的,
比如一条 SQL 语句如果用来从一个 10 万条记录的表中查 1 条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归
档,当前只剩下 5000 条记录了,那查询优化器就会改变方案,采用“全表扫描”方式。
可见,执行计划并不是固定的,它是“个性化的”。产生一个正确的“执行计划”有两点很重要:
(1) SQL 语句是否清晰地告诉查询优化器它想干什么?
(2) 查询优化器得到的数据库统计信息是否是最新的、正确的?
2、统一 SQL 语句的写法
对于以下两句 SQL 语句,程序员认为是相同的,数据库查询优化器认为是不同的。
select * from dual
Select * From dual
其实就是大小写不同,查询分析器就认为是两句不同的 SQL 语句,必须进行两次解析。生成 2 个执行计划。所以作为程
序员,应该保证相同的查询语句在任何地方都一致,多一个空格都不行!
3、不要把 SQL 语句写得太复杂
我经常看到,从数据库中捕捉到的一条 SQL 语句打印出来有 2 张 A4 纸这么长。一般来说这么复杂的语句通常都是有问题
的。我拿着这 2 页长的 SQL 语句去请教原作者,结果他说时间太长,他一时也看不懂了。可想而知,连原作者都有可能看糊
涂的 SQL 语句,数据库也一样会看糊涂。
一般,将一个 SELECT 语句的结果作为子集,然后从该子集中再进行查询,这种一层嵌套语句还是比较常见的,但是根据
经验,超过 3 层嵌套,查询优化器就很容易给出错误的执行计划。因为它被绕晕了。像这种类似人工智能的东西,终究比人的
分辨力要差些,如果人都看晕了,我可以保证数据库也会晕的。
另外,执行计划是可以被重用的,越简单的 SQL 语句被重用的可能性越高。而复杂的 SQL 语句只要有一个字符发生变化
就必须重新解析,然后再把这一大堆垃圾塞在内存里。可想而知,数据库的效率会何等低下。
4、使用“临时表”暂存中间结果
简化 SQL 语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,
后面的查询就在 TempDB 中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻
塞,提高了并发性能。
5、OLTP 系统 SQL 语句必须采用绑定变量
select * from orderheader where changetime > ‘2010-10-20 00:00:01’
select * from orderheader where changetime > ‘2010-09-22 00:00:01’
以上两句语句,查询优化器认为是不同的 SQL 语句,需要解析两次。如果采用绑定变量
select * from orderheader where changetime > @chgtime
@chgtime 变量可以传入任何值,这样大量的类似查询可以重用该执行计划了,这可以大大降低数据库解析 SQL 语句的
负担。一次解析,多次重用,是提高数据库效率的原则。
6、绑定变量窥测
事物都存在两面性,绑定变量对大多数 OLTP 处理是适用的,但是也有例外。比如在 WHERE 条件中的字段是“倾斜字段”
的时候。