MySQL中or、in、union與索引優(yōu)化詳析

本文緣起自《一分鐘了解索引技巧》的作業(yè)題。

假設(shè)訂單業(yè)務(wù)表結(jié)構(gòu)為：

order(oid, date, uid, status, money, time, …)

其中：

• oid，訂單ID，主鍵
• date，下單日期，有普通索引，管理后臺(tái)經(jīng)常按照date查詢(xún)
• uid，用戶(hù)ID，有普通索引，用戶(hù)查詢(xún)自己訂單
• status，訂單狀態(tài)，有普通索引，管理后臺(tái)經(jīng)常按照status查詢(xún)
• money/time，訂單金額/時(shí)間，被查詢(xún)字段，無(wú)索引
• …

假設(shè)訂單有三種狀態(tài)：0已下單，1已支付，2已完成

業(yè)務(wù)需求，查詢(xún)未完成的訂單，哪個(gè)SQL更快呢？

• select * from order where status!=2
• select * from order where status=0 or status=1
• select * from order where status IN (0,1)
• select * from order where status=0
  union all
  select * from order where status=1

結(jié)論：方案1最慢，方案2，3，4都能命中索引

但是...

一：union all 肯定是能夠命中索引的

select * from order where status=0

union all

select * from order where status=1

說(shuō)明：

直接告訴MySQL怎么做，MySQL耗費(fèi)的CPU最少

程序員并不經(jīng)常這么寫(xiě)SQL(union all)

二：簡(jiǎn)單的in能夠命中索引

select * from order where status in (0,1)

說(shuō)明：

讓MySQL思考，查詢(xún)優(yōu)化耗費(fèi)的cpu比union all多，但可以忽略不計(jì)

程序員最常這么寫(xiě)SQL(in)，這個(gè)例子，最建議這么寫(xiě)

三：對(duì)于or，新版的MySQL能夠命中索引

select * from order where status=0 or status=1

說(shuō)明：

讓MySQL思考，查詢(xún)優(yōu)化耗費(fèi)的cpu比in多，別把負(fù)擔(dān)交給MySQL

不建議程序員頻繁用or，不是所有的or都命中索引

對(duì)于老版本的MySQL，建議查詢(xún)分析下

四、對(duì)于!=，負(fù)向查詢(xún)肯定不能命中索引

select * from order where status!=2

說(shuō)明：

全表掃描，效率最低，所有方案中最慢

禁止使用負(fù)向查詢(xún)

五、其他方案

select * from order where status  2

這個(gè)具體的例子中，確實(shí)快，但是：

這個(gè)例子只舉了3個(gè)狀態(tài)，實(shí)際業(yè)務(wù)不止這3個(gè)狀態(tài)，并且狀態(tài)的“值”正好滿(mǎn)足偏序關(guān)系，萬(wàn)一是查其他狀態(tài)呢，SQL不宜依賴(lài)于枚舉的值，方案不通用

這個(gè)SQL可讀性差，可理解性差，可維護(hù)性差，強(qiáng)烈不推薦

六、作業(yè)

這樣的查詢(xún)能夠命中索引么？

select * from order where uid in (

   select uid from order where status=0

)

select * from order where status in (0, 1) order by date desc

select * from order where status=0 or date = CURDATE()

注：此為示例，別較真SQL對(duì)應(yīng)業(yè)務(wù)的合理性。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。