Java技術丨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
Advertisements
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能夠命中索引
Advertisements
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對應業務的合理性。