关于mysql连接派生表效率的问题

Question

create TEMPORARY TABLE temp_table
select * from tbl_TMSReport_0AF9F5E31BD94C468A62F4780198ED26;
Update tbl_TMSReport_0AF9F5E31BD94C468A62F4780198ED26 t2
Inner Join
(
Select r.OutletID,r.tmsOutletID,t.BizDate, r.MPOutletID, t.TableCapacity, Sum(floor(r.ReserveCover/ trc.TableCount)) As ReservedCover
From temp_table t
Inner Join tms_Reservation r
On r.OutletID = t.OutletID  And r.ReserveDate = t.BizDate And r.MPOutletID = t.MPOutletID And t.tmsOutletID=r.tmsOutletID
And (r.RowStatus In ('A', 'C') Or (r.RowStatus = 'X' And r.CancelMsgSubCat = 'NS')) And r.ReserveType = 'N' And r.ReserveMode <> 'W'
inner Join tms_TableReserve tr
On  tr.ReserveID = r.ReserveID And t.TableCapacity = tr.Capacity And tr.tmsPOSID=r.tmsPOSID And tr.tmsAccID=r.tmsAccID And tr.RowStatus In ('A', 'X') 
inner Join (Select ReserveID, Count(TableNo) As TableCount,tmsPosID,tmsAccID From tms_TableReserve Where RowStatus In ('A', 'X') Group By tmsPosID,tmsAccID,ReserveID) trc
On trc.ReserveID = r.ReserveID And trc.tmsPOSID=r.tmsPOSID And trc.tmsAccID=r.tmsAccID
Group By r.OutletID,r.tmsOutletID,t.BizDate, r.MPOutletID,t.TableCapacity
) A
On t2.OutletID = A.OutletID And t2.tmsOutletID=A.tmsOutletID And t2.BizDate = A.BizDate And t2.MPOutletID = A.MPOutletID And t2.TableCapacity = A.TableCapacity
Set t2.ReservedCover = IFNULL(A.ReservedCover, 0);蓝色字体tms_reservation表总数目为106502，tms_tablereserve表总数目为18万
这个sql是从mssql迁移过来的，但是效率就非常慢，也加了相关的索引
求教各位大神有什么指导方法？

Answer 1

EXPLAIN 
Select r.OutletID,r.tmsOutletID,t.BizDate, r.MPOutletID, t.TableCapacity, Sum(floor(r.ReserveCover/ trc.TableCount)) As ReservedCover
 From temp_table t
 Inner Join tms_Reservation r
 On r.OutletID = t.OutletID And r.ReserveDate = t.BizDate And r.MPOutletID = t.MPOutletID And t.tmsOutletID=r.tmsOutletID
 And (r.RowStatus In ('A', 'C') Or (r.RowStatus = 'X' And r.CancelMsgSubCat = 'NS')) And r.ReserveType = 'N' And r.ReserveMode <> 'W'
 inner Join tms_TableReserve tr
 On tr.ReserveID = r.ReserveID And t.TableCapacity = tr.Capacity And tr.tmsPOSID=r.tmsPOSID And tr.tmsAccID=r.tmsAccID And tr.RowStatus In ('A', 'X')  
 inner Join (Select ReserveID, Count(TableNo) As TableCount,tmsPosID,tmsAccID From tms_TableReserve Where RowStatus In ('A', 'X') Group By tmsPosID,tmsAccID,ReserveID) trc
 On trc.ReserveID = r.ReserveID And trc.tmsPOSID=r.tmsPOSID And trc.tmsAccID=r.tmsAccID
 Group By r.OutletID,r.tmsOutletID,t.BizDate, r.MPOutletID,t.TableCapacity
贴结果

Answer 2

先检查你的
Select r.OutletID,r.tmsOutletID,t.BizDate, r.MPOutletID, t.TableCapacity, Sum(floor(r.ReserveCover/ trc.TableCount)) As ReservedCover
From temp_table t
Inner Join tms_Reservation r
On r.OutletID = t.OutletID  And r.ReserveDate = t.BizDate And r.MPOutletID = t.MPOutletID And t.tmsOutletID=r.tmsOutletID
And (r.RowStatus In ('A', 'C') Or (r.RowStatus = 'X' And r.CancelMsgSubCat = 'NS')) And r.ReserveType = 'N' And r.ReserveMode <> 'W'
inner Join tms_TableReserve tr
On  tr.ReserveID = r.ReserveID And t.TableCapacity = tr.Capacity And tr.tmsPOSID=r.tmsPOSID And tr.tmsAccID=r.tmsAccID And tr.RowStatus In ('A', 'X')  
inner Join (Select ReserveID, Count(TableNo) As TableCount,tmsPosID,tmsAccID From tms_TableReserve Where RowStatus In ('A', 'X') Group By tmsPosID,tmsAccID,ReserveID) trc
On trc.ReserveID = r.ReserveID And trc.tmsPOSID=r.tmsPOSID And trc.tmsAccID=r.tmsAccID
Group By r.OutletID,r.tmsOutletID,t.BizDate, r.MPOutletID,t.TableCapacity这段效率是否正常。 MYSQL与MS SQL在优化上有些地方不一样。

Answer 3

1 PRIMARY <derived7> ALL (NULL) (NULL) (NULL) (NULL) 76861 Using temporary; Using filesort 
1 PRIMARY r ref tmsPOSID,idx_D,idx_O,idx_OMC,PK_tbl_Reservation PK_tbl_Reservation 5 trc.ReserveID 1 Using where 
1 PRIMARY tr ref idx_R,tmsPOSID tmsPOSID 15 gm_default.r.tmsPOSID,gm_default.r.tmsAccID,trc.ReserveID 1 Using where 
1 PRIMARY t ref index_1 index_1 19 gm_default.r.OutletID,gm_default.r.tmsOutletID,gm_default.r.MPOutletID,gm_default.tr.Capacity 3 Using where 
7 DERIVED tms_TableReserve index (NULL) tmsPOSID 42 (NULL) 185262 Using where 

Answer 4

我已贴出explain语句麻烦帮我看看

Answer 5

Select ReserveID, Count(TableNo) As TableCount,tmsPosID,tmsAccID From tms_TableReserve Where RowStatus In ('A', 'X') Group By tmsPosID,tmsAccID,ReserveID你的这个表tms_TableReserve 上有索引吗？  
create index xxx on tms_TableReserve  (RowStatus,tmsPosID,tmsAccID,ReserveID);

Answer 6

在 temp_table 连接字段上建立索引 

Answer 7

不是说IN语句会 让索引失效吗，所以我的组合索引没有加上rowstatus

Answer 8

我在temp_table建了索引 但是效率还是跟mssql有距离
create index index_1 on temp_table(OutletID,tmsOutletID,MPOutletID,TableCapacity);

Answer 9

建索引的时候字段顺序要按照group by字段顺序的吗？