据库知识学习，数据库设计优化攻略(五)

程序操作优化

概述：程序访问优化也可以认为是访问 SQL 语句的优化，一个好的 SQL 语句是可以减少非常多的程序性能的，下面列出

常用错误习惯，并且提出相应的解决方案

3.3.1 操作符优化 

➢ IN、NOT IN 操作符

IN 和 EXISTS 性能有外表和内表区分的，但是在大数据量的表中推荐用 EXISTS 代替 IN 。

Not IN 不走索引的是绝对不能用的，可以用 NOT EXISTS 代替

➢ IS NULL 或 IS NOT NULL 操作

索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于 0，字符

串类型设置一个默认值，判断是否等于默认值即可

➢ <> 操作符（不等于）

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。用其它相同功能的操作运算代替，如 a<>0 

改为 a>0 or a<0 a<>’’ 改为 a>’’

➢ 用全文搜索搜索文本数据，取代 like 搜索

全文搜索始终优于 like 搜索：

(1)全文搜索让你可以实现 like 不能完成的复杂搜索，如搜索一个单词或一个短语，搜索一个与另一个单词或短语相

近的单词或短语，或者是搜索同义词;

(2)实现全文搜索比实现 like 搜索更容易(特别是复杂的搜索);

3.3.2 SQL 语句优化 

➢ 在查询中不要使用 select * 

 为什么不能使用，地球人都知道，但是很多人都习惯这样用，要明白能省就省，而且这样查询数据库不能利用“覆盖索

引”了

➢ 尽量写 WHERE 子句

 尽量不要写没有 WHERE 的 SQL 语句

➢ 注意 SELECT INTO 后的 WHERE 子句

因为 SELECT INTO 把数据插入到临时表，这个过程会锁定一些系统表，如果这个 WHERE 子句返回的数据过多或者速度太

慢，会造成系统表长期锁定，诸塞其他进程。

➢ 对于聚合查询，可以用 HAVING 子句进一步限定返回的行

➢ 避免使用临时表

 (1)除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;

 (2)大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上

的操作需要跨数据库通信，速度自然慢。

➢ 减少访问数据库的次数：

 程序设计中最好将一些常用的全局变量表放在内存中或者用其他的方式减少数据库的访问次数

➢ 尽量少做重复的工作

尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：

 A、 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的

 B、减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

 C、杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

D、合并对同一表同一条件的多次 UPDATE，比如

 UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’

 UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’

 这两个语句应该合并成以下一个语句

 UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’ 

 WHERE EMP_ID=’ VPA30890F’

 E、UPDATE 操作不要拆成 DELETE 操作+INSERT 操作的形式，虽然功能相同，但是性能差别是很大的。

 F、不要写一些没有意义的查询，比如

 SELECT * FROM EMPLOYEE WHERE 1=2

3.3.3 WHERE 使用原则 

1)在下面两条 select 语句中:

 select * from table1 where field1<=10000 and field1>=0;

 select * from table1 where field1>=0 and field1<=10000;

 如果数据表中的数据 field1 都>=0,则第一条 select 语句要比第二条 select 语句效率高的多，因为第二条 select 语句

的第一个条件耗费了大量的系统资源。

 第一个原则：在 where 子句中应把最具限制性的条件放在最前面。

2)在下面的 select 语句中:

 select * from tab where a=… and b=… and c=…;

 若有索引 index(a,b,c)，则 where 子句中字段的顺序应和索引中字段顺序一致。

 第二个原则：where 子句中字段的顺序应和索引中字段顺序一致。

 以下假设在 field1 上有唯一索引 I1，在 field2 上有非唯一索引 I2。

3) select field3,field4 from tb where field1='sdf' 快

 select * from tb where field1='sdf' 慢，

 因为后者在索引扫描后要多一步 ROWID 表访问。

 select field3,field4 from tb where field1>='sdf' 快

 select field3,field4 from tb where field1>'sdf' 慢

 因为前者可以迅速定位索引。

 select field3,field4 from tb where field2 like 'R%' 快

 select field3,field4 from tb where field2 like '%R' 慢，

 因为后者不使用索引。

4) 使用函数如：

 select field3,field4 from tb where upper(field2)='RMN'不使用索引。

 如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有

限制。