SQL高效与低效---聚沙成塔

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') ---来自腾讯云社区的---聚沙成塔