最近一次在數(shù)據(jù)庫(kù)查詢一條數(shù)據(jù)時(shí)允跑，發(fā)現(xiàn)查詢速度變得巨慢王凑，看了一下查詢的where條件也的確是索引字段，因?yàn)槭腔A(chǔ)服務(wù)部開(kāi)發(fā)的數(shù)據(jù)庫(kù)查詢平臺(tái)聋丝，本以為是自己平臺(tái)出的問(wèn)題索烹，后來(lái)發(fā)現(xiàn)其他使用用戶并沒(méi)有出現(xiàn)這樣的問(wèn)題，肯定是SQL本身性能的問(wèn)題了弱睦，于是拿著這個(gè)SQL去測(cè)試庫(kù)做了一次執(zhí)行計(jì)劃的分析百姓。
測(cè)試使用工具：DataGrip
執(zhí)行這句SQL，我們查看一下執(zhí)行的時(shí)間况木，執(zhí)行的時(shí)間占用131毫秒垒拢，拉取數(shù)據(jù)占用161毫秒
SELECT * FROM PAYADM.RPMTORD WHERE CRE_DT =20171229;
在Oracle中執(zhí)行下面的命令，我們可以看一下這句sql的執(zhí)行過(guò)程：
EXPLAIN PLAN FOR SELECT * from PAYADM.RPMTORD WHERE CRE_DT =20171229;
SELECT * FROM TABLE (dbms_xplan.display);
?下圖就是我們這句命令的執(zhí)行計(jì)劃火惊，我們可以看見(jiàn)在表中第二行Operation中出現(xiàn)“TABLE ACCESS FULL”求类，意思就是我們這句sql使用的是“按全表掃描”，而不是索引檢索屹耐，接著往下看：1 - filter(TO_NUMBER("CRE_DT")=20171229)尸疆，到這里真相就大白了，CREDT字段在數(shù)據(jù)庫(kù)中實(shí)際上一個(gè)字符串類型，然而我們where條件中的參數(shù)傳的是number類型寿弱，因此會(huì)出現(xiàn)放棄索引犯眠，并且每次檢索都會(huì)做一次TO_NUMBER轉(zhuǎn)換的情況；
然后我們更正sql后再次執(zhí)行一下,我們發(fā)現(xiàn)這個(gè)時(shí)候執(zhí)行時(shí)間是42毫秒症革，拉取數(shù)據(jù)占用161毫秒筐咧，執(zhí)行時(shí)間上已經(jīng)比之前提速了89毫秒，拉取數(shù)據(jù)花費(fèi)的時(shí)間暫不考慮地沮，因?yàn)樗褪欠裼兴饕裏o(wú)關(guān)嗜浮，同時(shí)我們可以看一下此時(shí)的執(zhí)行計(jì)劃是什么，第二行“Table Access By Index RowID”摩疑，此時(shí)很明顯我們可以看到此次檢索使用的是“按索引查找”危融。
當(dāng)前我們測(cè)試環(huán)境下表的數(shù)據(jù)量將近13萬(wàn)條，而我們實(shí)際線上生產(chǎn)環(huán)境的數(shù)據(jù)量已經(jīng)超過(guò)了2000萬(wàn)條雷袋，所以全表掃描導(dǎo)致的“慢查詢”現(xiàn)象將會(huì)變得非常的十分的明顯吉殃，一個(gè)全表掃描可以耗時(shí)超過(guò)十分鐘之上，造成阻塞甚至數(shù)據(jù)庫(kù)掛掉楷怒。所以我們無(wú)論是在開(kāi)發(fā)還是在查數(shù)據(jù)的時(shí)候蛋勺，所有的帶有“WHERE”條件的語(yǔ)句都要在測(cè)試環(huán)境做一次執(zhí)行計(jì)劃，以免出現(xiàn)慢查詢的問(wèn)題鸠删。
下面收集了其它博主對(duì)SQL優(yōu)化的總結(jié)和一些避免全盤掃描的注意事項(xiàng)：
1.應(yīng)盡量避免在 where 子句中使用 != 或 <> 操作符抱完，否則將引擎放棄使用索引而進(jìn)行全表掃描。
2.應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件刃泡，如果一個(gè)字段有索引巧娱，一個(gè)字段沒(méi)有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描
可以考慮用union
select id from t where num=10 or Name = 'admin'?
可以替換為
select id from t where num = 10
union all
select id from t where Name = 'admin'
3. in? 和 not in 也要慎用烘贴，否則會(huì)導(dǎo)致全表掃描禁添，可以用 exists 代替 in
select id from t where num in(1,2,3)
可以替換為
select id from t where num between 1 and 3
4.如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描桨踪。
select id from t where num = @num (bad !!!)
5.避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作老翘，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描
select id from t where num/2 = 100
應(yīng)該為
select id from t where num = 100*2
6.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作
select id from t where substring(name,1,3) = ’abc’
應(yīng)改為
select id from t where name like 'abc%'
7.Update 語(yǔ)句，如果只更改1锻离、2個(gè)字段铺峭，不要Update全部字段，否則頻繁調(diào)用會(huì)引起明顯的性能消耗
8.盡量使用數(shù)字型字段纳账，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型逛薇，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷疏虫。這是因?yàn)橐嬖?處 理查詢和連 接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符永罚，而對(duì)于數(shù)字型而言只需要比較一次就夠了啤呼。
9.盡可能的使用 varchar/nvarchar 代替 char/nchar,因?yàn)槭紫葀archar是一個(gè)變長(zhǎng)的字段,?變長(zhǎng)字段存儲(chǔ)空間小，
可以節(jié)省存儲(chǔ)空間呢袱，其次對(duì)于查詢來(lái)說(shuō)官扣，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。
10.刪除JOIN和WHERE子句中的計(jì)算字段
SELECT * FROM sales a?
JOIN budget b ON ((YEAR(a.sale_date)* 100) + MONTH(a.sale_date)) = b.budget_year_month
應(yīng)改為
SELECT * FROM PRODUCTSFROM sales a?
JOIN budget b ON a.sale_year_month = b.budget_year_month
