现象

目前公司的订单表有100多万条，使用订单号查询数据时，所需时间大多要10-30秒不等，查看了慢查询日志，发现有的订单查询竟然耗时65秒

查询65秒.png

原因

我查看了原有的查询语句，发现where后面跟了or查询，虽然3个or都索引，使用explain分析查询结果，发现要扫描近70万行，几乎是全盘扫描一遍，只为获取最多3条数据，效率实在是低下
这3个字段均设置了索引，但or在这个语句中，使索引失效了（主要看最后几行）

explain select mashangOrder.order_id as orderId, mashangOrder.order_price as orderPrice, mashangOrder.subsidy_fee as subsidyFee, mashangOrder.kickback_fee as kickbackFee, mashangOrder.tianmao_total_fee as tianmaoTotalFee, merchant.wangwang as userId, mashangOrder.wangwang as wangwang, mashangOrder.mobile as mobile, mashangOrder.state as orderStatus, mashangOrder.tianmao_state as tianMaoState, mashangOrder.created_at as createdAt, mashangOrder.verify_expire_time as verifyExpireTime,  mashangOrder.merchant_id as customerId, mashangOrder.service_time as serviceTime, cast(mashangOrder.request_cancel_status as unsigned integer) as requestCancelStatus,  mashangOrder.request_cancel_time as requestCancelTime,  mashangOrder.planned_refund_time as plannedRefundTime,  mashangOrder.refund_reason as refundReason,  mashangOrder.buyer_email as buyerEmail,  mashangOrder.note as note,  mashangOrder.type as type,  mashangOrder.pay_status as payStatus,  mashangOrder.pay_time as payTime,  mashangOrder.liyang_id as liyangId,  mashangOrder.license_number as licenseNumber,  mashangOrder.order_detail as orderDetail,  mashangOrder.user_verify_code as userVerifyCode,  mashangOrder.verify_time as verifyTime,  mashangOrder.force_freezed_state as forceFreezedState,  mashangOrder.seller_memo as sellerMemo,  mashangOrder.freezed_amount as freezedAmount,  cast(city.level as unsigned integer) as levelId 
from ms_order mashangOrder 
left join cm_merchant merchant on mashangOrder.merchant_id = merchant.merchant_id 
left join cm_merchant_place place on mashangOrder.place_id = place.id
left join cm_region district on place.region_id = district.region_id
left join cm_region city on district.parent_id = city.region_id
where mashangOrder.state != 0
and (mashangOrder.order_id = '123456789'
or mashangOrder.parentBizOrderId = '123456789'
or mashangOrder.serviceOrderId = '123456789')
order by mashangOrder.created_at desc

索引失效.png

解决思路

使用union all代替or查询，也就是说把3个字段的查询分别做查询，将结果使用union all连接在一起，这样单次查询可以用到索引，效率大大提高
先看一下分析结果

优化分析.png

简要的sql语句，查询结果不超80ms

SELECT * FROM 
(
select columnA,columnB,...... from ms_order mashangOrder 
where mashangOrder.state != 0 and mashangOrder.order_id='106004683871' 
UNION ALL
select columnA,columnB,...... from ms_order mashangOrder
where mashangOrder.state != 0 and mashangOrder.serviceOrderId ='106004683871'
UNION ALL
select columnA,columnB,...... from ms_order mashangOrder
where mashangOrder.state != 0 and mashangOrder.serviceOrderId is null  AND mashangOrder.parentBizOrderId='106004683871'
) t
order by t.createdAt desc;

优化后的查询结果.png

其它知识

1. union和union all的区别
   这2者最主要的区别是前者会合并重复项，并且默认排序，后者均不会，所以后者的效率会更高一些
2. 联合索引
   在两个或多个列上建立的索引叫联合索引，也叫复合索引、组合索引，要遵循最左匹配原则，例如abc三个字段组成的联系索引，a，ab，abc均能使用到索引，除此之外类似ac，bc，b，c等，均索引失效