数据库优化

最新推荐文章于 2025-08-12 22:09:30 发布

转载最新推荐文章于 2025-08-12 22:09:30 发布 · 833 阅读

文章标签：

#sql #数据库

数据库专栏收录该内容

35 篇文章

订阅专栏

数据库优化包含以下三部分，数据库自身的优化，数据库表优化，程序操作优化.此文为第三部分

概述：程序访问优化也可以认为是访问SQL语句的优化，一个好的SQL语句是可以减少非常多的程序性能的，下面列出常用错误习惯，并且提出相应的解决方案

一、操作符优化

1. IN、NOT IN 操作符

IN和EXISTS 性能有外表和内表区分的，但是在大数据量的表中推荐用EXISTS 代替IN 。
Not IN 不走索引的是绝对不能用的，可以用NOT EXISTS 代替Not IN

2. IS NULL 或IS NOT NULL操作

索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可

3. <> 操作符（不等于）

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

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

全文搜索始终优于like搜索：
(1)全文搜索让你可以实现like不能完成的复杂搜索，如搜索一个单词或一个短语，搜索一个与另一个单词或短语相近的单词或短语，或者是搜索同义词;
(2)实现全文搜索比实现like搜索更容易(特别是复杂的搜索);

二、SQL语句优化

1、在查询中不要使用select *

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

2.　尽量写WHERE子句

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

3. 注意SELECT INTO后的WHERE子句

因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

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

5. 避免使用临时表

(1)除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;
(2)大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

6.减少访问数据库的次数：

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

7.尽量少做重复的工作

尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：
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

三、where使用原则

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

select * from table1 where field1<=10000 and field1>=0;
select * from table1 wherefield1>=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 whereupper(field2)='RMN' 不使用索引。
如果一个表有两万条记录，建议不使用函数；

如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。

####################################

1.尽量不要在where中包含子查询;

关于时间的查询，尽量不要写成：where to_char(dif_date,’yyyy-mm-dd’)=to_char(‘2007-07-01′,’yyyy-mm-dd’);

2.在过滤条件中，可以过滤掉最大数量记录的条件必须放在where子句的末尾;

FROM子句中写在最后的表(基础表，driving table)将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有三个以上的连接查询，那就需要选择交叉表 (intersection table)作为基础表，交叉表是指那个被其他表所引用的表;

3.采用绑定变量

4.在WHERE中尽量不要使用OR

5.用EXISTS替代IN、用NOT EXISTS替代NOT IN;

6.避免在索引列上使用计算：WHERE SAL*12>25000;

7.用IN来替代OR： WHERE LOC_ID=10 OR LOC_ID=15 OR LOC_ID=20

8.避免在索引列上使用IS NULL和IS NOT NULL;

9.总是使用索引的第一个列;

10.用UNION-ALL替代UNION;

11.避免改变索引列的类型：SELECT…FROM EMP WHERE EMPNO=’123’，由于隐式数据类型转换，to_char(EMPNO)=’123’，因此，将不采用索引，一般在采用字符串拼凑动态SQL语句出现;

12.’!=’ 将不使用索引;

13.优化GROUP BY;

14.避免带有LIKE参数的通配符，LIKE ‘4YE%’使用索引，但LIKE ‘%YE’不使用索引

15.避免使用困难的正规表达式，例如select * from customer where zipcode like “98___”，即便在zipcode上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改成select * from customer where zipcode>”98000″，在执行查询时就会利用索引来查询，显然会大大提高速度;

16.尽量明确的完成SQL语句，尽量少让数据库工作。比如写SELECT语句时，需要把查询的字段明确指出表名。尽量不要使用SELECT *语句。组织SQL语句的时候，尽量按照数据库的习惯进行组织。

########################################

一、对SQL SELECT查询语句进行优化的理由
　　下列几方面的原因是我们进行SQL语句优化的理由：
　　 SQL语句是对数据库(数据)进行*作的惟一途径；
　　 SQL语句消耗了70%~90%的数据库资源；
　　 SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低；
　　 SQL语句可以有不同的写法；
　　 SQL语句易学，难精通。
　　从大多数数据库应用系统的实例来看，查询*作在各种数据库*作中所占据的比重最大，而查询*作所基于的SQL SELECT语句又是代价最大的语句。
　　二、对SQL SELECT查询语句的优化建议
　　（1）、合理使用索引：where子句中变量顺序应与索引字键顺序相同。
　　如：create index test_idx on test(hm, rq, xx)
　　　　索引字键顺序：首先是号码hm，其次是日期rq，最后是标志xx，所以where子句变量顺序应是where hm<=“P1234”and rq=“06/06/1999”and xx=“DDD”,不应是where xx=“DDD” and rq=“06/06/1999” and hm <=“P1234”这样的不按索引字键顺序写法。
　　（2）、将最具有限制性的条件放在前面，大值在前，小值在后。
　　　　如：where colA<=10000 AND colA>=1　效率高
　　　　 where colA>=1 AND colA<=10000 效率低
　　（3）、避免采用MATCHES和LIKE通配符匹配查询
　　通配符匹配查询特别耗费时间。即使在条件字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。
　　例如语句：SELECT ＊ FROM customer WHERE zipcode MATCHES “524*”
　　可以考虑将它改为SELECT ＊ FROM customer WHERE ZipCode<=“524999” AND ZipCode >=“524000”，则在执行查询时就会利用索引来查询，显然会大大提高速度。
　　（4）、避免非开始的子串
　　例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“24”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。
　　（5）、避免相关子查询
　　一个字段的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的字段值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
　　例如：将下面的语句
　　select hm,rq from TabA
　　where item IN (select item form TabB where TabB.num=50)
　　改为：select hm,bf from TabA, TabB
　　 where TabA.item=TabB.item AND TabB.num=50
　　（6）、避免或简化排序
　　应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：
　　索引中不包括一个或几个待排序的字段；
　　 group by或order by子句中字段的次序与索引的次序不一样；
　　排序的字段来自不同的表。
　　为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的字段的范围等。
　　（7）、消除对大型表行数据的顺序存取
　　在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的字段进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。
　　还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序*作：
　　 SELECT ＊ FROM orders WHERE (cust_num=126 AND order_num>1001) OR order_num=1008
　　虽然在cust_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：
　　 SELECT ＊ FROM orders WHERE cust_num=126 AND order_num>1001
　　 UNION
　　 SELECT ＊ FROM orders WHERE order_num=1008
　　这样就能利用索引路径处理查询。
　　（8）、对于大数据量的求和应避免使用单一的sum命令处理，可采用group by方式与其结合，有时其效率可提高几倍甚至百倍。
　　（9）、避免会引起磁盘读写的rowid*作。在where子句中或select语句中，用rowid要产生磁盘读写，是一个物理过程，会影响性能。
　　最后一个就是使用临时表加速查询
　　把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序*作，而且在其他方面还能简化优化器的工作。
　担醒大家一定要注意的是：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。