點擊上方 "程序員小樂"關注, 星標或置頂一起成長
每天凌晨00點00分, 第一時間與你相約
每日英文
If nothing can be done, then just let nature take its course.
如果無能為力，那就它順其自然吧。
每日掏心話
一切順其自然，專注於過程而少想結果，安於當下，不想過去亦不想未來，把每一個發生都當作一種修煉。
來自：素小暖OSC | 責編：樂樂
鏈接：my.oschina.net/u/4006148/blog/3225442
程序員小樂(ID:study_tech)第 834 次推文 圖片來自百度
往日回顧：前後端分離模式下的權限設計方案
正文
最近在做有關查詢數據庫的工作，由於一個表的數據量過大，導致程序進程卡死情況出現，SQL優化迫在眉睫，索引閃亮登場！
Oracle中查詢走索引的情況：
1、對返回的行無任何限定條件，即沒有where子句。
2、未對數據表與任何索引主列相對應的行限定條件。
例如：在id-name-time列創建了三列複合索引，那麼僅對name列限定條件不能使用這個索引，因為name不是索引的主列。
3、對索引的主列有限定條件，但是在條件表達式裡使用以下表達式則會使索引失效，造成權標掃描：
（1）where子句中對字段進行函數、表達式操作，這將導致引擎放棄使用索引而進行全表掃描。
（2）查詢字段is null時索引失效，引起全表查詢。
解決辦法：SQL語法中使用null會有很多麻煩，最好索引列時not null的；對於is null，可以建立組合索引， nvl(字段,0),對錶和索引analyse後，is null查詢時可以重新啟用索引查找,但是效率還不是值得肯定；is not null時永遠不會使用索引。一般數據量大的表不要用is null查詢。
（3） 查詢條件中使用了不等於操作符（<>、!=）會限制索引、引起全表掃描 。
解決方法：通過把不等於操作符改成or，可以使用索引，避免全表掃描。
例如，把column<>10，改成column<10 or column>10，就可以使用索引了。
（4）對索引的主列有限定條件，但是條件使用like操作以及值以‘%’開始或者值是一個賦值變量。
例如：where city like '%大連%'。
select * from citys where name like '%大連' (不使用索引)
select * from citys where name like '大連%' (使用索引) 。
解決辦法：首先儘量避免模糊查詢，如果必須使用，不採用全模糊查詢，也應儘量採用右模糊查詢， 即like ‘…%’，是會使用索引的；左模糊like ‘%...’無法直接使用索引，但可以利用reverse + function index 的形式，變化成 like ‘…%’；
全模糊查詢是無法優化的，一定要使用的話建議使用搜索引擎。
4、 or語句使用不當會引起全表掃描
原因：where子句中比較的兩個條件，一個有索引，一個沒索引，使用or則會引起全表掃描。
例如：where A=：1 or B=：2，A上有索引，B上沒索引，則比較B=：2時會重新開始全表掃描
5、組合索引
排序時應按照組合索引中各列的順序進行排序，即使索引中只有一個列是要排序的，否則排序性能會比較差。
例如：
create index skip1 on emp5(job,empno，date);
select job，empno from emp5 where job=’manager’ and empno=’10’ order by job,empno,date desc;
實際上只是查詢出符合 job=’manager’ and empno=’10’ 條件的記錄並按date降序排序，但寫成order by date desc性能較差。
6、 Update語句，如果只更改1、2個字段，不要Update全部字段，否則頻繁調用會引起明顯的性能消耗，同時帶來大量日誌。
7、 對於多張大數據量的表JOIN，要先分頁再JOIN，否則邏輯讀會很高，性能很差。
8、 select count(*) from table；
這樣不帶任何條件的count會引起全表掃描，並且沒有任何業務意義，是一定要杜絕的。
9、 sql的where條件要綁定變量，比如where column= 1，不要寫成where column=‘aaa’，這樣會導致每次執行時都會重新分析，浪費CPU和內存資源。
10、 不要使用in操作符，這樣數據庫會進行全表掃描。
11、 not in 使用not in也不會走索引
推薦方案：用not exists或者（外聯結+判斷為空）來代替
12、 > 及 < 操作符（大於或小於操作符）
大於或小於操作符一般情況下是不用調整的，因為它有索引就會採用索引查找。
但有的情況下可以對它進行優化，如一個表有100萬記錄，一個數值型字段 A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。
那麼執行A>2與A>=3的效果就有很大的區別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。
13、 UNION操作符
UNION在進行錶鏈接後會篩選掉重複的記錄，所以在錶鏈接後會對所產生的結果集進行排序運算，刪除重複的記錄再返回結果。
實際大部分應用中是不會產生重複的記錄，最常見的是過程表與歷史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
這個SQL在運行時先取出兩個表的結果，再用排序空間進行排序刪除重複的記錄，最後返回結果集，如果表數據量大的話可能會導致用磁盤進行排序。
推薦方案：採用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結果合併後就返回。
14、 WHERE後面的條件順序影響
Select * from zl_yhjbqk where dy_dj = '1K以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1K以下'
以上兩個SQL中dy_dj及xh_bz兩個字段都沒進行索引，所以執行的時候都是全表掃描，
第一條SQL的dy_dj = '1KV以下'條件在記錄集內比率為99%，而xh_bz=1的比率只為0.5%，在進行第一條SQL的時候99%條記錄都進行dy_dj及xh_bz的比較。
而在進行第二條SQL的時候 0.5%條記錄都進行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU佔用率明顯比第一條低。
15、查詢表順序的影響
在FROM後面的表中的列表順序會對SQL執行性能影響，在沒有索引及ORACLE沒有對錶進行統計分析的情況下ORACLE會按表出現的順序進行鏈接，
由此因為表的順序不對會產生十分耗服務器資源的數據交叉。（注：如果對錶進行了統計分析，ORACLE會自動先進小表的鏈接，再進行大表的鏈接）
表連接時，數據量大的為主表效率更高。
16、Oracle的解析器按照從右到左的順序處理from子句中的表名，因此from子句中寫在最後的表（基礎班 driving table）將先被最先處理。
在from子句中包含多個表的情況下，必須選擇記錄條數最少的表作為基礎表，放在最後。
以下幾種情況快速判斷基礎表（驅動表）：
（1）where子句中儘量使用索引。
（2）連接操作應返回較少行上驅動。
（3）如果where中包含選擇性條件，如where id=1，應將最具有選擇性部分放在表達式最後。
（4）如果只有一個表有索引，另一表無索引，無索引的表通常作為基礎表。
歡迎在留言區留下你的觀點，一起討論提高。如果今天的文章讓你有新的啟發，學習能力的提升上有新的認識，歡迎轉發分享給更多人。
猜你還想看
阿里、騰訊、百度、華為、京東最新面試題彙集
從上帝視角看Java如何運行
一次項目代碼重構：使用Spring容器幹掉條件判斷
手把手教你用1行代碼實現人臉識別
關注訂閱號「程序員小樂」，收看更多精彩內容
嘿，你在看嗎？
