這篇文字主要討論sql的一般編寫原則。下一篇討論根據(jù)執(zhí)行計劃進行調優(yōu)的話題。
網上這類文章很多，但往往只是給出結論，比如，這樣寫sql會比那樣寫sql效率更高。閱讀者如果打算打開數(shù)據(jù)庫自己做一遍實驗，看看效率差異到底有多少，需要自己造數(shù)據(jù)，還是比較麻煩。這篇文字會把DDL和DML以及sql的執(zhí)行時間都寫出來，一方面給一個更直觀的印象，另一方面方便閱讀者自己實驗。
以Oracle為實驗用數(shù)據(jù)庫，使用著名的emp表。安裝oracle數(shù)據(jù)庫之后，scott用戶的密碼是tiger. 在他的schema下有幾張sample表，很多sql教程都以這幾張表為基礎. 據(jù)說，熟悉oracle數(shù)據(jù)庫的人提到smith這個人名，就能聯(lián)想起sample表中他的工作是clerk. emp表就是sample表之一。這張表原始數(shù)據(jù)只有14行，為了體現(xiàn)不同sql性能上的差異，我們需要多填充一些數(shù)據(jù)進去。作為填充數(shù)據(jù)的預備知識，我們可以看一下如何生成一系列從小到大的id:
SELECT ROWNUM
FROM DUAL
CONNECT BY LEVEL < 10000;
填充隨機數(shù)據(jù)可以借助于dbms_random包，不想覆蓋已有的表，所以新創(chuàng)建一個表結構基本一樣的：
create table emp_new
as
select level empno,
SYS.dbms_random.String('u', SYS.dbms_random.value(3,10)) ename,
SYS.dbms_random.String('u', SYS.dbms_random.value(3,9)) job,
round(SYS.dbms_random.value(1000,9999)) mgr,
TO_DATE ( ROUND (DBMS_RANDOM.VALUE (1, 28))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1, 12))
|| '-'
|| ROUND (DBMS_RANDOM.VALUE (1980, 2012)),
'DD-MM-YYYY'
) hiredate,
round(SYS.dbms_random.value(300,9999)) sal,
round(SYS.dbms_random.value(1,6)) * 100 comm,
round(SYS.dbms_random.value(1,4)) * 10 deptno
FROM DUAL
CONNECT BY LEVEL < 1000000;
這里有一個局限，原本的emp表mgr列reference empno列。上面新創(chuàng)建的emp_new中失去了這個constraint. 這點可以從Oracle SQL Developer中看到。
emp表：
emp_new表：
下面就開始測試sql了： 1. 先比較一下加primary key前后的結果： select * from emp_new where empno=1; 加primary key constraint之前運行0.023秒。 加了primary key constraint之后0.001秒。加primary key constraint在100萬條數(shù)據(jù)上大約花費4秒鐘。 2. where子句 vs. having子句 select deptno, avg(sal) from emp_new group by deptno having deptno != 10 and deptno != 20; 0.24秒 select deptno, avg(sal) from emp_new where deptno != 10 and deptno != 20 group by deptno ; 0.16秒 所以having中的條件一般用于對一些集合函數(shù)的比較，如count()等，除此之外，一般條件應該寫在where子句中。
3. 減少對表的查詢 update emp_new set sal=(select max(sal) from emp_new), comm=(select max(comm) from emp_new) where empno=1237; 0.11秒左右 update emp_new set (sal, comm) =(select max(sal), max(comm) from emp_new) where empno=1224; 0.07秒到0.08秒之間
注意：以上三個測試都只fetch前50條數(shù)據(jù)。
4. 傳說中用exists替代in通常可提高查詢效率， not exists 也比not in 快。
先生成dept_new表：
create table dept_new
as
select level deptno,
SYS.dbms_random.String('u', SYS.dbms_random.value(3,10)) dname,
SYS.dbms_random.String('u', SYS.dbms_random.value(3,9)) loc
FROM DUAL
CONNECT BY LEVEL < 10000; 實際測試中，無論是執(zhí)行計劃還是實際測試的速度都是基本一致的。 第一組 select * from emp_new e where e.empno > 986000 and e.deptno in (select d.deptno from dept_new d where d.loc='AYDN')
select * from emp_new e where empno > 986000 and exists (select * from dept_new d where d.deptno = e.deptno and d.loc='AYDN')
第二組
select e.empno from emp_new e where e.empno > 996000 and not exists (select 1 from dept_new d where d.deptno = e.deptno and loc like 'A%')
select e.empno from emp_new e where e.empno > 996000 and e.deptno not in (select d.deptno from dept_new d where loc like 'A%')
|
這篇文字主要參考兩篇文章：