前言

查詢語句的優(yōu)化是SQL效率優(yōu)化的一個(gè)方式，可以通過優(yōu)化sql語句來盡量使用已有的索引，避免全表掃描，從而提高查詢效率。最近在對(duì)項(xiàng)目中的一些sql進(jìn)行優(yōu)化，總結(jié)整理了一些方法。

1、盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷

應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。

如：

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

2、盡量避免在 where 子句中使用 or 來連接條件

盡量避免在 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?

3、in 和 not in 要慎用

in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，

如：

select id from t where num in(1,2,3) 對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了： select id from t where num between 1 and 3

4、如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描

如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然 而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。

如下面語句將進(jìn)行全表掃描：

select id from t where num=@num 可以改為強(qiáng)制查詢使用索引： select id from t with(index(索引名)) where num=@num

5、盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作

應(yīng)盡量避免在where子句中對(duì)字段進(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 ‘a(chǎn)bc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′?

6、 exists 代替 in

很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：

select num from a where num in(select num from b) 用下面的語句替換： select num from a where exists(select 1 from b where num=a.num)?

7、應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列

因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

8、盡量使用數(shù)字型字段

若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

9、與臨時(shí)表一樣，游標(biāo)并不是不可使用

對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時(shí) 間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

10、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作

應(yīng)盡量避免在 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

文章到這里就結(jié)束了，了解更多，請(qǐng)點(diǎn)擊：https://www.bilibili.com/video/BV137411V7Y1

作者：麒麟才子

鏈接：https://juejin.cn/post/6911123584874348557

來源：掘金

著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。