SQL优化

最新推荐文章于 2024-11-12 20:37:49 发布

转载最新推荐文章于 2024-11-12 20:37:49 发布 · 346 阅读

数据库系统专栏收录该内容

98 篇文章

订阅专栏

SQL语句编写注意问题

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。

1. is null 与is not null

不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优器是不允许使用索引的。

2.带通配符（%）的like语句

select * from employee where last_name like '%cliton%';

这里是由于通配符在搜索词首出现，在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到使用：

select * from employee where last_name like 'c%';

3.order by语句

任何在order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

SQL资料

（1）选择最有效的表名顺序（只在基于规则的优化器中有效）

ORACLE的解析器按照从右到左的顺序处理FROM子句的表名，FROM子句中写在最后的表将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

（2）where子句的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉最大数量的记录的条件必须写在WHERE子句的末尾。

（3）select子句中避免使用 ‘*’

ORACLE在解析的过程中，会将*依次转成所有的列名，这个工作是通过查询数据字典完成，这意味着将耗费更多的时间。

（4）整合简单，无关联的数据库访问

如果代有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系）

（5）删除重复记录

DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) 
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO);

上面是最高效的删除重复记录的方法。

（6）用truncate替代delete

当删除表中的记录中，在通常情况下，回滚段用来存放可以被恢复的信息，如果你没有commit事务，oracle会将数据恢复到删除之前的状态，而当运用truncate时，回滚段不再存放任何可被恢复的信息，当命令运行后，数据不能被恢复，因此很少的资源被调用，执行时间也会很短。

（7）用where子句替换having子句

on、where、having这三个都可以加条件的子句，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该是最快的，where也应该比having快点，因为它过滤数据事才进行sum，在两个表联接时才用on，所以在一个表的时候，主剩where和havng比较了。

（8）通过内部函数提高SQL效率

复杂的SQL往往牺牲了执行效率。

（9）使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个column上，这样一来，就可以减少解析的时间并减少那此由Column歧义引起的语法错误。

（10）使用exists替代in，not exists规划not in

在很多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接，在子查询中，not in子句将执行一个内部的排序和合并。

（高效）SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB') 
(低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

（11）用EXISTS替换DISTINCT

当提交一个包含一对多表信息的查询时，避免在select子句中使用distinct，一般可以考虑使用exists替换，exists使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立即返回结果。

（12）sql语句用大写的，因为ORACLE总是先解析sql语句，把小写字母转换成在大写的再执行。

（13）避免在索引列上使用计算

举例: 
低效： 
SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 
高效: 
SELECT … FROM DEPT WHERE SAL > 25000/12;

（14）用>=替换>

高效: 
SELECT * FROM  EMP  WHERE  DEPTNO >=4 
低效: 
SELECT * FROM EMP WHERE DEPTNO >3

两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描第一个DEPT大于3的记录。

（15）用uniot替换or（适用于索引列）

在下面的例子中，LOC_ID和REGION上都建立有索引。

高效: 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE LOC_ID = 10 
UNION 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE REGION = “MELBOURNE” 
低效: 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

最后欢迎大家访问我的个人网站：1024s