MySQL中or、in、union與索引優化詳析

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

假設訂單業務表結構為：

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

其中：

• oid，訂單ID，主鍵
• date，下單日期，有普通索引，管理后臺經常按照date查詢
• uid，用戶ID，有普通索引，用戶查詢自己訂單
• status，訂單狀態，有普通索引，管理后臺經常按照status查詢
• money/time，訂單金額/時間，被查詢字段，無索引
• …

假設訂單有三種狀態：0已下單，1已支付，2已完成

業務需求，查詢未完成的訂單，哪個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

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

但是...

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

select * from order where status=0

union all

select * from order where status=1

說明：

直接告訴MySQL怎么做，MySQL耗費的CPU最少

程序員并不經常這么寫SQL(union all)

二：簡單的in能夠命中索引

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

說明：

讓MySQL思考，查詢優化耗費的cpu比union all多，但可以忽略不計

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

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

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

說明：

讓MySQL思考，查詢優化耗費的cpu比in多，別把負擔交給MySQL

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

對于老版本的MySQL，建議查詢分析下

四、對于!=，負向查詢肯定不能命中索引

select * from order where status!=2

說明：

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

禁止使用負向查詢

五、其他方案

select * from order where status < 2

這個具體的例子中，確實快，但是：

這個例子只舉了3個狀態，實際業務不止這3個狀態，并且狀態的“值”正好滿足偏序關系，萬一是查其他狀態呢，SQL不宜依賴于枚舉的值，方案不通用

這個SQL可讀性差，可理解性差，可維護性差，強烈不推薦

六、作業

這樣的查詢能夠命中索引么？

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對應業務的合理性。

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對億速云的支持。