优化SQL语句的若干方法
1、操作符号: NOT IN操作符
此操作是强列推荐不使用的,因为它不能应用表的索引。
推荐方案:用NOT EXISTS 或(外连接+判断为空)方案代替 "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT IN", "NOT LIKE", "LIKE '%500'",
因为他们不走索引全是表扫描。
NOT IN会多次扫描表,
使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代,特别是左连接,
而Exists比IN更快,最慢的是NOT操作。
使用in时,在IN后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,这样可以减少判断的次数
2、注意union和union all的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时,尽量改成连接查询,少用子查询。
4、尽量少用视图,它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质:它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时,不要使用指向多个表的视图,
直接从表检索或者仅仅包含这个表的视图上读,否则增加了不必要的开销,查询受到干扰.
5、创建合理的索引,对于插入或者修改比较频繁的表,尽量慎用索引。因为如果表中存在索引,插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时,将参数的大小设置为合适即可,勿设置太大。这样开销很大。
7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'是一样的。
由于in会在比较多次,所以有时会慢些。
8、ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名
例如:表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2
执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1
执行时间26.09秒
9、ORACLE采用自下而上的顺序解析WHERE子句,
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
低效,执行时间156.3秒
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;
高效,执行时间10.6秒
10、没有必要时不要用DISTINCT和ORDER BY,它们增加了额外的开销。这些动作可以改在客户端执行。
14、一般在GROUP BY和HAVING字句之前就能剔除多余的行,所以尽量不要用它们来做剔除行的工作。
他们的执行顺序应该如下最优:select 的Where字句选择所有合适的行,Group By用来分组个统计行,Having字句用来剔除多余的分组。
这样Group By和Having的开销小,查询快.对于大的数据行进行分组和Having十分消耗资源。
15、 使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.
16、计算记录条数
和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
17、减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER)
= ( SELECT TAB_NAME, DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update 多个Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
HERE EMP_DEPT = 0020;
18、避免在索引列上使用NOT
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的
影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0
1、操作符号: NOT IN操作符
此操作是强列推荐不使用的,因为它不能应用表的索引。
推荐方案:用NOT EXISTS 或(外连接+判断为空)方案代替 "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT IN", "NOT LIKE", "LIKE '%500'",
因为他们不走索引全是表扫描。
NOT IN会多次扫描表,
使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代,特别是左连接,
而Exists比IN更快,最慢的是NOT操作。
使用in时,在IN后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,这样可以减少判断的次数
2、注意union和union all的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时,尽量改成连接查询,少用子查询。
4、尽量少用视图,它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质:它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时,不要使用指向多个表的视图,
直接从表检索或者仅仅包含这个表的视图上读,否则增加了不必要的开销,查询受到干扰.
5、创建合理的索引,对于插入或者修改比较频繁的表,尽量慎用索引。因为如果表中存在索引,插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时,将参数的大小设置为合适即可,勿设置太大。这样开销很大。
7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'是一样的。
由于in会在比较多次,所以有时会慢些。
8、ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名
例如:表 TAB1 16,384 条记录
表 TAB2 1 条记录
选择TAB2作为基础表 (最好的方法)
select count(*) from tab1,tab2
执行时间0.96秒
选择TAB2作为基础表 (不佳的方法)
select count(*) from tab2,tab1
执行时间26.09秒
9、ORACLE采用自下而上的顺序解析WHERE子句,
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
低效,执行时间156.3秒
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;
高效,执行时间10.6秒
10、没有必要时不要用DISTINCT和ORDER BY,它们增加了额外的开销。这些动作可以改在客户端执行。
14、一般在GROUP BY和HAVING字句之前就能剔除多余的行,所以尽量不要用它们来做剔除行的工作。
他们的执行顺序应该如下最优:select 的Where字句选择所有合适的行,Group By用来分组个统计行,Having字句用来剔除多余的分组。
这样Group By和Having的开销小,查询快.对于大的数据行进行分组和Having十分消耗资源。
15、 使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.
16、计算记录条数
和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
17、减少对表的查询
在含有子查询的SQL语句中,要特别注意减少对表的查询.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER)
= ( SELECT TAB_NAME, DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
Update 多个Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
HERE EMP_DEPT = 0020;
18、避免在索引列上使用NOT
我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的
影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0