假設訂單業務表結構為:
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可讀性差,可理解性差,可維護性差,強烈不推薦
大家可以點擊加入群:283943715【JAVA高級架構開發】裡面有Java高級大牛直播講解知識點 走的就是高端路線 (如果你想跳槽換工作 但是技術又不夠 或者工作上遇到了 瓶頸 我這裡有一個JAVA的免費直播課程 講的是高端的知識點 基礎不好的誤入喲 只要你有1-5年的開發經驗可以加群找我要課堂鏈接 注意:是免費的 沒有開發經驗誤入哦
六、作業
這樣的查詢能夠命中索引麼?
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對應業務的合理性。