最近發(fā)現(xiàn)數(shù)據(jù)庫sql優(yōu)化對客戶的體驗有很大的提高。在項目中將之前的DBUtils+反射來查詢的方式改變?yōu)镾pring Template。效率升高了很多。因此，覺得有必要對數(shù)據(jù)庫查詢優(yōu)化做一個總結(jié)。
1.根據(jù)實際情況創(chuàng)建數(shù)據(jù)庫索引
在經(jīng)常需要的字段創(chuàng)建索引，比如經(jīng)常用到的name, id, date等字段。具體的情況根據(jù)業(yè)務(wù)確定。也可以在多個字段上創(chuàng)建索引
2.使用預(yù)編譯查詢
一般的后臺程序是根據(jù)用戶的輸入執(zhí)行動態(tài)SQL,這時候可能有SQL注入漏洞攻擊。所以，一般要求對SQL進(jìn)行預(yù)編譯,并且第一次執(zhí)行的時候DBMS會對其進(jìn)行語句查詢優(yōu)化并且預(yù)編譯。這樣執(zhí)行SQL的時候就直接使用預(yù)編譯結(jié)果，提高執(zhí)行的速度。
3.調(diào)整Where語句中的連接順序
DBMS一般采用自下而上的的順序解析Where字句，根據(jù)這個原理表連接最好寫在其他where條件之前。并且那些過濾掉最大數(shù)量記錄的條件必須寫在Where子句的末尾
4.盡量將多條SQL語句壓縮到一條SQL
每次執(zhí)行SQL的時候都要建立網(wǎng)絡(luò)連接，進(jìn)行權(quán)限校驗，進(jìn)行SQL語句查詢優(yōu)化、發(fā)送執(zhí)行結(jié)果，這個過程非常耗時，所以一般業(yè)務(wù)可行的條件下壓縮多條語句到一條SQL語句中
5.用Where替換having
不要使用having，having只會在檢索出所有記錄之后才會對結(jié)果集進(jìn)行過濾，而where實在聚合之前。having一般用于聚合函數(shù)
6.使用表的別名
當(dāng)在SQL語句中連接多個表時，請用表的別名，并且把別名前綴到每個列名上，可以減少解析時間，避免發(fā)生列名歧義引起的錯誤
7.in 和 exists 一般情況下使用exists，因為in不走索引
8.避免在索引上使用計算
在where子句中，如果索引是函數(shù)的一部分，DBMS的優(yōu)化器將不會使用索引而使用全表查詢。
// 效率低：
select * from person where salary*12>25000(salary是索引列)
// 效率高：
select * from person where salary>25000/12(salary是索引列)
9.用 union all 替換 union
當(dāng)SQL語句需要union兩個查詢結(jié)果時，一般使用union all。
union和union all的差別就在于union會對數(shù)據(jù)做一個distanct的動作，而這個distanct動作的速度則取決于現(xiàn)有數(shù)據(jù)的數(shù)量，數(shù)量越大則時間也越慢。而對于幾個數(shù)據(jù)集，要確保數(shù)據(jù)集之間的數(shù)據(jù)互相不重復(fù)，基本是O(n)的算法復(fù)雜度。
UNION還有一個用處，我們在海量數(shù)據(jù)的查詢中，如果使用
select * from c_cons where cons_id in ('691339365','3387785','3387954');
這樣的查詢語句，會引起全表掃描，可以使用UNION ALL來代替，如：
select * from c_cons where cons_id='691339365'
UNION ALL
select * from c_cons where cons_id='3387785'
UNION ALL
select * from c_cons where cons_id='3387954'
這樣查詢比使用in查詢要快很多，它不會去進(jìn)行全表掃描。
10.避免SQL中出現(xiàn)隱式類型轉(zhuǎn)換
當(dāng)一張表中索引字段在作為where條件的時候，如果進(jìn)行了隱式類型轉(zhuǎn)化，則此索引字段將不會被識別，因為隱式類型也屬于計算，索引將會采用全表掃描。
11.防止檢索范圍過寬
使用is not null 或者不等于判斷，可能造成優(yōu)化器假設(shè)匹配的記錄數(shù)太多
使用立刻運(yùn)算符的時候， "a%" 將會使用索引， 而"%a"和"%ac"則會使用全表掃面，因此"%a"和"%ac"盡量不要使用
