sql查询优化or篇
原sql,使用or的情况 SELECT * FROM coin_trade_history WHERE (buyer_uid = ${userId} OR seller_uid = ${userId}) order by id desc
在有索引的情况可以使用UNION ALL函数,优化后的sql
SELECT * FROM coin_trade_history WHERE buyer_uid = ${userId} UNION ALL
SELECT * FROM coin_trade_history WHERE seller_uid = ${userId}) order by id desc
如果查询上有索引,union all比or快,因为前者会利用索引查找,or会使索引失效;
如果查询上没有索引,or比union快,因为前者查询引擎会一次性完成指令分析。