SQL高效与低效

一头小山猪

发布于 2020-04-10 11:31:00

5380

发布于 2020-04-10 11:31:00

文章被收录于专栏：微光点亮星辰

SQL高效与低效

SQL 架构

1. SELECT ....
2. FROM Table1大,Table2中,Table3小
3. whereTable1=条件一
4.  andTable2=条件二
5.  and table3=table2        
6.  and table2=table1

PS: 如Where 有使用到 Subquery 尽量改写为虚拟 Table

低效:

1. SELECT ....
2. FROM Table1,Table2,Table3
3. whereTable1=条件一
4.  andTable2=条件二
5.  and table2 =(select.....
6.  fromTable4
7.  where条件四)
8.  and table3=table2        
9.  and table2=table1

高效:

1. SELECT ....
2. FROM Table1,Table2,(select.....
3.  fromTable3,Table4
4.  where条件四
5.  andTable3=Table4)Table3
6. whereTable1=条件一
7.  andTable2=条件二
8.  and table3=table2        
9.  and table2=table1

(1) 低效:

1. SELECT …
2. FROM DEPT
3. WHERE SAL *12>25000;

高效:

1. SELECT …
2. FROM DEPT
3. WHERE SAL >25000/12;

(2) 高效:

1. SELECT *
2. FROM EMP
3. WHERE DEPTNO >=4

低效:

1. SELECT *
2. FROM EMP
3. WHERE DEPTNO >3

(3) 高效:

1. SELECT LOC_ID , LOC_DESC , REGION
2. FROM LOCATION
3. WHERE LOC_ID =10
4. UNION
5. SELECT LOC_ID , LOC_DESC , REGION
6. FROM LOCATION
7. WHERE REGION ='MELBOURNE'

低效:

1. SELECT LOC_ID , LOC_DESC , REGION
2. FROM LOCATION
3. WHERE LOC_ID =10 OR REGION ='MELBOURNE'

(4) 低效: (索引失效)

1. SELECT …
2. FROM DEPARTMENT
3. WHERE DEPT_CODE IS NOT NULL;

高效: (索引有效)

1. SELECT …
2. FROM DEPARTMENT
3. WHERE DEPT_CODE >=0;

不使用索引:

1. SELECT ACCOUNT_NAME
2. FROM TRANSACTION
3. WHERE AMOUNT !=0;

使用索引:

1. SELECT ACCOUNT_NAME
2. FROM TRANSACTION
3. WHERE AMOUNT >0;

(5) 低效:执行约156.3秒

1. SELECT …
2. FROM EMP E
3. WHERE SAL >50000
4. AND JOB ='MANAGER'
5. AND 25<(SELECT COUNT(*) FROM EMP
6. WHERE MGR=E.EMPNO);

高效:执行约10.6秒

1. SELECT …
2. FROM EMP E
3. WHERE 25<(SELECT COUNT(*) FROM EMP
4. WHERE MGR=E.EMPNO)
5. AND SAL >50000
6. AND JOB ='MANAGER';

(6) 使用DECODE函数来减少处理时间使用DECODE函数可以避免重复描述相同记录或重复连结相同的表例如:

1. SELECT COUNT(*)，SUM(SAL)
2. FROM EMP
3. WHERE DEPT_NO =0020
4.   AND ENAME LIKE 'SMITH%';
5.  
6. SELECT COUNT(*)，SUM(SAL)
7. FROM EMP
8. WHERE DEPT_NO =0030
9.   AND ENAME LIKE 'SMITH%';

你可以用DECODE函数更高效

1. SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT,
2.        COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,
3.        SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
4.        SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
5. FROM EMP
6. WHERE ENAME LIKE 'SMITH%';

· 使用日期

当使用日期时,需要注意如果有超过5位小数加到日期上,这个日期会到下一天例如:

1. SELECT TO_DATE('01-JAN-93'+.99999)
2. FROM DUAL;
3. Returns:
4. '01-JAN-93 23:59:59'
1. SELECT TO_DATE('01-JAN-93'+.999999)
2. FROM DUAL;
3. Returns:
4. '02-JAN-93 00:00:00'

· 以笔数多的放在前面

表 TAB1 16,384 笔，表 TAB2 1 笔选择TAB2作为基础表 (最好的方法)

1. select count(*)from tab1,tab2

执行约0.96秒

选择TAB2作为基础表 (不佳的方法)

1. select count(*)from tab2,tab1

执行约26.09秒

· 不同表的效能

原表

1. SELECT NAME
2. FROM EMP
3. WHERE EMP_NO =1234;
4. SELECT NAME
5. FROM DPT
6. WHERE DPT_NO =10;
7. SELECT NAME
8. FROM CAT
9. WHERE CAT_TYPE ='RD';

高效

1. SELECT E.NAME,D.NAME,C.NAME
2. FROM CAT C,DPT D,EMP E,DUAL X
3. WHERE NVL('X',X.DUMMY)= NVL('X',E.ROWID(+))
4.   AND NVL('X',X.DUMMY)= NVL('X',D.ROWID(+))
5.   AND NVL('X',X.DUMMY)= NVL('X',C.ROWID(+))
6.   AND E.EMP_NO(+)=1234
7.   AND D.DEPT_NO(+)=10
8.   AND C.CAT_TYPE(+)='RD';

缺点程序可携性低

· 删除重覆记录

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

· 减少对表的查询

低效

1. SELECT TAB_NAME
2. FROM TABLES
3. WHERE TAB_NAME =
4.  (SELECT TAB_NAME
5.      FROM TAB_COLUMNS
6.      WHERE VERSION =604)
7.   AND DB_VER=
8.  (SELECT DB_VER
9.      FROM TAB_COLUMNS
10.      WHERE VERSION =604)

高效

1. SELECT TAB_NAME
2. FROM TABLES
3. WHERE (TAB_NAME,
4.        DB_VER)=
5.  (SELECT TAB_NAME,
6.             DB_VER)
7.   FROM TAB_COLUMNS WHEREVERSION =604)

· Update 多Column 例子:

低效:

1. UPDATE EMP
2. SET EMP_CAT =
3.  (SELECT MAX(CATEGORY)
4.    FROM EMP_CATEGORIES),
5.     SAL_RANGE =
6.  (SELECT MAX(SAL_RANGE)
7.    FROM EMP_CATEGORIES)
8. WHERE EMP_DEPT =0020;

高效:

1. UPDATE EMP
2. SET (EMP_CAT,
3.      SAL_RANGE)=
4.  (SELECT MAX(CATEGORY),
5.           MAX(SAL_RANGE)
6.    FROM EMP_CATEGORIES)
7. WHERE EMP_DEPT =0020;
· 使用EXISTS(或NOT EXISTS)通常能提高查询的效率.

低效:

1. SELECT *
2. FROM EMP
3. WHERE EMPNO >0
4.   AND DEPTNO IN
5.  (SELECT DEPTNO
6.      FROM DEPT
7.      WHERE LOC ='MELB')

高效:

1. SELECT *
2. FROM EMP
3. WHERE EMPNO >0
4.   AND EXISTS
5.  (SELECT 'X'
6.      FROM DEPT
7.      WHERE DEPT.DEPTNO = EMP.DEPTNO
8.        AND LOC ='MELB')

· 用NOT EXISTS替代NOT IN,为了避免使用NOT IN ,我们可以把它改成外部连接(Outer Joins)或NOT EXISTS.

例如:

1. SELECT …
2. FROM EMP
3. WHERE DEPT_NO NOT IN
4.  (SELECT DEPT_NO
5.      FROM DEPT
6.      WHERE DEPT_CAT='A');

(方法一: 高效)

1. SELECT ….
2. FROM EMP A,
3.      DEPT B
4. WHERE A.DEPT_NO = B.DEPT(+)
5.   AND B.DEPT_NO IS NULL
6.   AND B.DEPT_CAT(+)='A'

(方法二: 最高效)

1. SELECT ….
2. FROM EMP E
3. WHERE NOT EXISTS
4.  (SELECT 'X'
5.      FROM DEPT D
6.      WHERE D.DEPT_NO = E.DEPT_NO
7.        AND DEPT_CAT ='A');

· 用EXISTS替代DISTINCT

低效:

1. SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPTD,EMP E
2. WHERE D.DEPT_NO = E.DEPT_NO

高效:

1. SELECT DEPT_NO,DEPT_NAME FROM DEPTD
2. WHERE EXISTS ( SELECT ‘X’ FROM EMP E
3. WHERE E.DEPT_NO = D.DEPT_NO);

· 计算目前执行SQL的效能状况,以下为找出最低效的SQL

1. SELECT EXECUTIONS,
2.        DISK_READS,
3.        BUFFER_GETS,
4.        ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
5.        ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
6.        SQL_TEXT
7. FROM V$SQLAREA
8. WHERE EXECUTIONS>0
9.   AND BUFFER_GETS >0
10.   AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS <0.8
11. ORDER BY 4 DESC;

· Outer Join 外部连接

1. select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum;

2. select d.deptname,e.ename from dept d, emp e where d.empno = e.enum(+);

· 检查 table 或 view 是否存在于 Oracle

1. SELECT COUNT (TABLE_NAME)
2. FROM all_tables
3. WHERE UPPER (TABLE_NAME)= UPPER ('PathologyTest')
4.   SELECT COUNT (view_name)
5.   FROM all_views WHEREUPPER (view_name)= UPPER ('v_PathologyTest')

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2017-03-10，如有侵权请联系 cloudcommunity@tencent.com 删除

sql