MySQL [TCSceneryMemberOrderAll]> explain select count(1) as refundCount from TCSceneryMemberOrderAll.RefundSupplier rs inner Join TCSceneryMemberOrderAll.OrderMain om ON om.SerialID=rs.SerialID inner Join TCSceneryMemberOrderAll.RefundOrder ro force index(IX_RefundOrder_RefundSerialId ) ON ro.RefundSerialId=rs.RefundSerialId where 1=1 and rs.CreateTime>=‘2024-03-01’ and rs.CreateTime<‘2024-04-24’ and rs.SupplierID=7387 and rs.RefundStatus=0 and rs.SerialID=‘sz178254326770384896087747’ and ro.PlatId <> 415 and ro.ProjectID=0 and rs.IsValid=1 and om.AgentID=1993;
±-----------------------------------------±------------±----------±------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info
|
±-----------------------------------------±------------±----------±------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_16 | 1.00 | root | | funcs:count(Column#127)->Column#126
|
| └─HashJoin_19 | 0.00 | root | | CARTESIAN inner join
|
| ├─HashJoin_36(Build) | 0.00 | root | | inner join, equal:[eq(tcscenerymemberorderall.refundsupplier.refundserialid, tcscenerymemberorderall.refundorder.refundserialid)]
|
| │ ├─IndexLookUp_74(Build) | 0.00 | root | |
|
| │ │ ├─IndexRangeScan_71(Build) | 1872.78 | cop[tikv] | table:rs, index:IX_RefundSupplier_RefundStatus_IsValid(RefundStatus, IsValid) | range:[0 1,0 1], keep order:false
|
| │ │ └─Selection_73(Probe) | 0.00 | cop[tikv] | | eq(tcscenerymemberorderall.refundsupplier.serialid, “sz178254326770384896087747”), eq(tcscenerymemberorderall.refundsupplier.supplierid, 7387), ge(tcscenerymemberorderall.refundsupplier.createtime, 2024-03-01 00:00:00.000000), lt(tcscenerymemberorderall.refundsupplier.createtime, 2024-04-24 00:00:00.000000) |
| │ │ └─TableRowIDScan_72 | 1872.78 | cop[tikv] | table:rs | keep order:false
|
| │ └─HashAgg_57(Probe) | 18276237.11 | root | | group by:tcscenerymemberorderall.refundorder.refundserialid, funcs:count(Column#136)->Column#127, funcs:firstrow(tcscenerymemberorderall.refundorder.refundserialid)->tcscenerymemberorderall.refundorder.refundserialid |
| │ └─IndexLookUp_58 | 18276237.11 | root | |
|
| │ ├─IndexFullScan_54(Build) | 18696096.00 | cop[tikv] | table:ro, index:IX_RefundOrder_RefundSerialId(RefundSerialId) | keep order:false |
| │ └─HashAgg_50(Probe) | 18276237.11 | cop[tikv] | | group by:tcscenerymemberorderall.refundorder.refundserialid, funcs:count(1)->Column#136 |
| │ └─Selection_56 | 18520869.18 | cop[tikv] | | eq(tcscenerymemberorderall.refundorder.projectid, 0), ne(tcscenerymemberorderall.refundorder.platid, 415) |
| │ └─TableRowIDScan_55 | 18696096.00 | cop[tikv] | table:ro | keep order:false |
| └─IndexLookUp_81(Probe) | 0.00 | root | | |
| ├─IndexRangeScan_78(Build) | 1.00 | cop[tikv] | table:om, index:IX_OrderMain_SerialID(SerialID) | range:[“sz178254326770384896087747”,“sz178254326770384896087747”], keep order:false |
| └─Selection_80(Probe) | 0.00 | cop[tikv] | | eq(tcscenerymemberorderall.ordermain.agentid, 1993) |
| └─TableRowIDScan_79 | 1.00 | cop[tikv] | table:om | keep order:false |
±-----------------------------------------±------------±----------±------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17 rows in set, 3 warnings (0.00 sec)
全索引扫描了