mysql> explain SELECT c.`sn` clientSn,asm.`clientManagerSn`,pry.`productSn`,1 TYPE,pr.`capitalBalance`,pr.`yieldBalance`,pry.`realValueEndDate`,pr.`status`
-> FROM `ProductRepayment` pr
-> LEFT JOIN `ProductRepay` pry ON pry.`productSn` = pr.`productSn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = pr.`productSn`
-> LEFT JOIN `Client` c ON c.`sn` = pr.`clientSn`
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`=pr.`clientSn`
-> WHERE pa.`supportTransfer` =2
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,hd.productSn,2,thd.capitalBalance,thd.yieldBalance,thd.settlementDate,IF(thd.status =1 OR thd.status=2,1,2)
-> FROM `TransferProduct` tp
-> LEFT JOIN `TransferProductHolding` thd ON thd.transferProductSn = tp.sn
-> LEFT JOIN `ClientProductHolding` hd ON hd.sn = tp.holdingSn
-> LEFT JOIN `Client` c ON c.`sn` = thd.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = hd.productSn
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ch.productSn,3,ch.capitalBalance,ch.yieldBalance,pr.settlementDate,ch.status-1
-> FROM `ClientHolding` ch
-> LEFT JOIN `Client` c ON c.`sn` = ch.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = ch.productSn
-> LEFT JOIN `ProductRepay` pr ON pr.`productSn` = pa.`productSn`
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ci.productSn,1,ci.investAmount,NULL,NULL,0
-> FROM `ClientInvestOrder` ci
-> LEFT JOIN `Client` c ON c.`sn` = ci.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`;
+----+--------------+----------------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+
| 1 | PRIMARY | pr | ALL | NULL | NULL | NULL | NULL | 7081 | NULL |
| 1 | PRIMARY | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | Using where |
| 1 | PRIMARY | pry | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | NULL |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.clientSn | 1 | Using index |
| 1 | PRIMARY | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | tp | ALL | NULL | NULL | NULL | NULL | 53 | NULL |
| 2 | UNION | hd | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.tp.holdingSn | 1 | NULL |
| 2 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.hd.productSn | 1 | Using where |
| 2 | UNION | thd | ALL | NULL | NULL | NULL | NULL | 78 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.thd.clientSn | 1 | Using index |
| 2 | UNION | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| 3 | UNION | ch | ALL | NULL | NULL | NULL | NULL | 6426 | NULL |
| 3 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | Using where |
| 3 | UNION | pr | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | NULL |
| 3 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.clientSn | 1 | Using index |
| 3 | UNION | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| 4 | UNION | ci | ALL | NULL | NULL | NULL | NULL | 7258 | NULL |
| 4 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ci.clientSn | 1 | Using index |
| 4 | UNION | asm | ALL | NULL | NULL | NULL | NULL | 4618 | Using where; Using join buffer (Block Nested Loop) |
| NULL | UNION RESULT | <union1,2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+----------------+--------+---------------+---------+---------+-------------------+------+----------------------------------------------------+
20 rows in set (0.00 sec)
创建索引；
mysql> create index AssignClientManager_idx1 on AssignClientManager(clientSn);
mysql> explain SELECT c.`sn` clientSn,asm.`clientManagerSn`,pry.`productSn`,1 TYPE,pr.`capitalBalance`,pr.`yieldBalance`,pry.`realValueEndDate`,pr.`status`
-> FROM `ProductRepayment` pr
-> LEFT JOIN `ProductRepay` pry ON pry.`productSn` = pr.`productSn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = pr.`productSn`
-> LEFT JOIN `Client` c ON c.`sn` = pr.`clientSn`
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`=pr.`clientSn`
-> WHERE pa.`supportTransfer` =2
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,hd.productSn,2,thd.capitalBalance,thd.yieldBalance,thd.settlementDate,IF(thd.status =1 OR thd.status=2,1,2)
-> FROM `TransferProduct` tp
-> LEFT JOIN `TransferProductHolding` thd ON thd.transferProductSn = tp.sn
-> LEFT JOIN `ClientProductHolding` hd ON hd.sn = tp.holdingSn
-> LEFT JOIN `Client` c ON c.`sn` = thd.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = hd.productSn
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ch.productSn,3,ch.capitalBalance,ch.yieldBalance,pr.settlementDate,ch.status-1
-> FROM `ClientHolding` ch
-> LEFT JOIN `Client` c ON c.`sn` = ch.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`
-> LEFT JOIN `ProductAccess` pa ON pa.`productSn` = ch.productSn
-> LEFT JOIN `ProductRepay` pr ON pr.`productSn` = pa.`productSn`
-> WHERE pa.`supportTransfer` =1
-> UNION ALL
-> SELECT c.`sn` clientSn,asm.`clientManagerSn`,ci.productSn,1,ci.investAmount,NULL,NULL,0
-> FROM `ClientInvestOrder` ci
-> LEFT JOIN `Client` c ON c.`sn` = ci.clientSn
-> LEFT JOIN `AssignClientManager` asm ON asm.`clientSn`= c.`sn`;
+----+--------------+----------------+--------+--------------------------+--------------------------+---------+-------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+--------+--------------------------+--------------------------+---------+-------------------+------+----------------------------------------------------+
| 1 | PRIMARY | pr | ALL | NULL | NULL | NULL | NULL | 7081 | NULL |
| 1 | PRIMARY | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | Using where |
| 1 | PRIMARY | pry | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.productSn | 1 | NULL |
| 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.pr.clientSn | 1 | Using index |
| 1 | PRIMARY | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.pr.clientSn | 1 | NULL |
| 2 | UNION | tp | ALL | NULL | NULL | NULL | NULL | 53 | NULL |
| 2 | UNION | hd | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.tp.holdingSn | 1 | NULL |
| 2 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.hd.productSn | 1 | Using where |
| 2 | UNION | thd | ALL | NULL | NULL | NULL | NULL | 78 | Using where; Using join buffer (Block Nested Loop) |
| 2 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.thd.clientSn | 1 | Using index |
| 2 | UNION | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.c.sn | 1 | NULL |
| 3 | UNION | ch | ALL | NULL | NULL | NULL | NULL | 6426 | NULL |
| 3 | UNION | pa | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | Using where |
| 3 | UNION | pr | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.productSn | 1 | NULL |
| 3 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ch.clientSn | 1 | Using index |
| 3 | UNION | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.c.sn | 1 | NULL |
| 4 | UNION | ci | ALL | NULL | NULL | NULL | NULL | 7258 | NULL |
| 4 | UNION | c | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.ci.clientSn | 1 | Using index |
| 4 | UNION | asm | ref | AssignClientManager_idx1 | AssignClientManager_idx1 | 4 | zjzc.c.sn | 1 | NULL |
| NULL | UNION RESULT | <union1,2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+----------------+--------+--------------------------+--------------------------+---------+-------------------+------+----------------------------------------------------+
20 rows in set (0.00 sec)
