第一種：
select a4.* from a1
inner join a2 on a1.ordernum=a2.reqorderid
inner join a3 on a2.orderid=a3.orderno
inner join a4 on a3.id=a4.orderid
where a1.num='888464' and a1.operdate='20180719'
查詢結果耗時：16秒
第二種：
select a4.* from (
select a3.id from a1
inner join a2 on a1.ordernum=a2.reqorderid
inner join a3 on a2.orderid=a3.orderno
where a1.num='888464' and a1.operdate='20180719') a3
inner join a4 on a3.id=a4.orderid
查詢結果耗時：0.3秒
相關索引建立，block_nested_loop=on
explain 第一種寫法
1 SIMPLE a1 ref index_name,index_ordernum,index_mercnum index_name 152 const 4124 Using index condition; Using where
1 SIMPLE a2 ref index_ord_3,index_ord_1 index_ord_1 93 oss.a1.ordernum 1 Using index condition
1 SIMPLE a4 ALL index_so_orderid 164750 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE a3 eq_ref PRIMARY,index_sto_orderno PRIMARY 8 mall.a4.orderid 1 Using where
請問大牛們，這是什么原因導致的，應該如何解決？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/84078.html
標籤：MySQL