1. 描述

報(bào)表的核心是數(shù)據(jù)，數(shù)據(jù)集是否合理決定報(bào)表的質(zhì)量。

1.每張報(bào)表都應(yīng)該有一個(gè)主數(shù)據(jù)集，為了降低維護(hù)時(shí)的工作量，盡量將所有字段置于主數(shù)據(jù)集，除非在某些情況下，不使用多源數(shù)據(jù)集會(huì)導(dǎo)致主數(shù)據(jù)集異常復(fù)雜。

2.在制作報(bào)表之前，盡量考慮到所有需要展示的數(shù)據(jù)字段，在數(shù)據(jù)庫軟件中，合理編寫sql語句，大數(shù)據(jù)情況盡量對sql做優(yōu)化，以及添加索引。

擁有高性能SQL查詢語句，能使查詢速度加快，報(bào)表展示速度得到較明顯的提升！

2. 方案介紹

2.1 SELECT子句中避免使用 “*”

當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí)，使用動(dòng)態(tài)SQL列引用‘’是一個(gè)方便的方法。不幸的是，這是一個(gè)非常低效的方法。 實(shí)際上，ORACLE在解析的過程中, 會(huì)將“” 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時(shí)間。

2.2 刪除重復(fù)記錄

最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID)

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

2.3 用TRUNCATE替代DELETE

當(dāng)刪除表中的記錄時(shí)，在通常情況下，回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息，如果你沒有COMMIT事務(wù)，ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)，而當(dāng)運(yùn)用TRUNCATE時(shí)，回滾段不再存放任何可被恢復(fù)的信息。當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù)。因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短。

2.4 計(jì)算記錄條數(shù)

和一般的觀點(diǎn)相反， count(*) 比count(1)稍快 ，當(dāng)然如果可以通過索引檢索，對索引列的計(jì)數(shù)仍舊是最快的。 例如 COUNT(EMPNO)

2.5 用EXISTS替代IN

在許多基于基礎(chǔ)表的查詢中，為了滿足一個(gè)條件，往往需要對另一個(gè)表進(jìn)行聯(lián)接。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。

--低效

SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC =’MELB’)

--高效

SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)

2.6 用EXISTS替換DISTINCT

當(dāng)提交一個(gè)包含一對多表信息(比如部門表和雇員表)的查詢時(shí),避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換 例如: Sql代碼

--低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO

--高效:

SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)

EXISTS 使查詢更為迅速

2.7 用>=替代>

如果DEPTNO上有一個(gè)索引

--高效：

SELECT * FROM EMP WHERE DEPTNO >=4

--低效：

SELECT * FROM EMP WHERE DEPTNO >3

兩者的區(qū)別在于, 前者DBMS將直接跳到第一個(gè)DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個(gè)DEPT大于3的記錄.

2.8 應(yīng)盡量避免在 where 子句中對字段判斷!

如：

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：

select id from t where num=0

2.9 應(yīng)避免在 where 子句中使用!=或<>操作符!

將引擎放棄使用索引而進(jìn)行全表掃描。優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行。

2.10 應(yīng)避免在 where 子句中使用 or 連接!

否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10 union all select id from t where num=20

2.11 in 和 not in 也要慎用

因?yàn)镮N會(huì)使系統(tǒng)無法使用索引,而只能直接搜索表中的數(shù)據(jù)。如：

select id from t where num in(1,2,3)

對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

2.12 應(yīng)避免在 where 子句中進(jìn)行表達(dá)式操作

這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

SELECT * FROM T1 WHERE F1/2=100

應(yīng)改為:

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’

應(yīng)改為:

SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

應(yīng)改為:

SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

即：任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊。

2.13 應(yīng)避免在where子句中進(jìn)行函數(shù)操作

這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)='abc'

--name以abc開頭的id

select id from t where datediff(day,createdate,'2005-11-30')=0

--‘2005-11-30’生成的id

應(yīng)改為:

select id from t where name like 'abc%'

select id from t where createdate>=’2005-11-30′ and createdate<'2005-12-1'

2.14 不要在 where 子句中的“=”左邊運(yùn)算

進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，系統(tǒng)將可能無法正確使用索引。

2.15 盡量避免向客戶端返回大數(shù)據(jù)量

若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。

2.16 避免使用不兼容的數(shù)據(jù)類型

例如float和int、char和varchar、binary和varbinary是不兼容的。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作。例如:

SELECT name FROM employee WHERE salary > 60000

在這條語句中,如salary字段是money型的,則優(yōu)化器很難對其進(jìn)行優(yōu)化,因?yàn)?0000是個(gè)整型數(shù)。我們應(yīng)當(dāng)在編程時(shí)將整型轉(zhuǎn)化成為錢幣型,而不要等到運(yùn)行時(shí)轉(zhuǎn)化。

2.17 充分利用連接條件

在某種情況下，兩個(gè)表之間可能不只一個(gè)的連接條件，這時(shí)在 WHERE 子句中將連接條件完整的寫上，有可能大大提高查詢速度。

例：

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

第二句將比第一句執(zhí)行快得多。

2.18 能用DISTINCT的就不用GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

可改為：

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

2.19 能用UNION ALL就不要用UNION！

UNION ALL不執(zhí)行SELECT DISTINCT函數(shù)，這樣就會(huì)減少很多不必要的資源

2.20 盡量不要用SELECT INTO語句！

SELECT INTO 語句會(huì)導(dǎo)致表鎖定，阻止其他用戶訪問該表。