<pre name="code" class="html">explain SELECT t.* FROM
(
SELECT
t1.sn AS clientSn,
t1.userNick,
t1.mobilePhone,
t3.personName,
t2.availableBalance,
(SELECT IFNULL(SUM(amount) , 0) FROM ClientRechargeOrder t WHERE t.clientSn= t1.sn AND t.status ='2') AS rechargeAmount,
(SELECT IFNULL(SUM(amount) , 0) FROM ClientWithDrawOrder t WHERE t.clientSn= t1.sn AND t.status IN ('1','2','3','4') ) AS withdrawAmount,
( (SELECT IFNULL(SUM(capitalBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '1')
+
(SELECT IFNULL(SUM(capitalBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '1')
) AS investAmount,
( (SELECT IFNULL(SUM(yieldBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '2')
+
(SELECT IFNULL(SUM(yieldBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '2')
) AS yieldAmount,
(SELECT IFNULL(SUM(t0.amount) , 0) FROM ClientCoupon t,Coupon t0 WHERE t.clientSn= t1.sn AND t.status = '2' AND t.couponSn = t0.sn AND t0.type IN (1,2)) AS cashCouponAmount
FROM Client t1 , ClientAssetInfo t2 , ClientPersonalInfo t3
WHERE t1.sn = t2.clientSn AND t1.sn = t3.clientSn
) t WHERE (t.rechargeAmount + t.yieldAmount + t.cashCouponAmount - t.withdrawAmount - t.investAmount - t.availableBalance) != 0;
+----+--------------------+------------+--------+---------------+---------+---------+-----------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+---------------+---------+---------+-----------------+-------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2327 | Using where |
| 2 | DERIVED | t1 | ALL | PRIMARY | NULL | NULL | NULL | 2327 | NULL |
| 2 | DERIVED | t3 | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.t1.sn | 1 | NULL |
| 2 | DERIVED | t2 | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.t1.sn | 1 | NULL |
| 9 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 9 | DEPENDENT SUBQUERY | t0 | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.t.couponSn | 1 | Using where |
| 8 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 7 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 12890 | Using where |
| 6 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 12890 | Using where |
| 4 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 2126 | Using where |
| 3 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 5786 | Using where |
+----+--------------------+------------+--------+---------------+---------+---------+-----------------+-------+-------------+
12 rows in set (0.00 sec)
添加3个索引:
mysql> create index ProductRepayment_idx1 on ProductRepayment(clientSn);
mysql> create index ClientRechargeOrder_idx1 on ClientRechargeOrder(clientSn);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index ClientWithDrawOrder_idx1 on ClientWithDrawOrder(clientSn);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
create index ClientCoupon_idx1 on ClientCoupon(clientSn);
修改后的执行计划:
mysql> explain SELECT t.* FROM ( SELECT t1.sn AS clientSn, t1.userNick, t1.mobilePhone, t3.personName, t2.availableBalance, (SELECT IFNULL(SUM(amount) , 0) FROM ClientRechargeOrder t WHERE t.clientSn= t1.sn AND t.status ='2') AS rechargeAmount, (SELECT IFNULL(SUM(amount) , 0) FROM ClientWithDrawOrder t WHERE t.clientSn= t1.sn AND t.status IN ('1','2','3','4') ) AS withdrawAmount, ( (SELECT IFNULL(SUM(capitalBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '1') + (SELECT IFNULL(SUM(capitalBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '1') ) AS investAmount, ( (SELECT IFNULL(SUM(yieldBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '2') + (SELECT IFNULL(SUM(yieldBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '2') ) AS yieldAmount, (SELECT IFNULL(SUM(t0.amount) , 0) FROM ClientCoupon t,Coupon t0 WHERE t.clientSn= t1.sn AND t.status = '2' AND t.couponSn = t0.sn AND t0.type IN (1,2)) AS cashCouponAmount FROM Client t1 , ClientAssetInfo t2 , ClientPersonalInfo t3 WHERE t1.sn = t2.clientSn AND t1.sn = t3.clientSn ) t WHERE (t.rechargeAmount + t.yieldAmount + t.cashCouponAmount - t.withdrawAmount - t.investAmount - t.availableBalance) != 0;
+----+--------------------+------------+--------+--------------------------+--------------------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+--------------------------+--------------------------+---------+-----------------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2327 | Using where |
| 2 | DERIVED | t1 | ALL | PRIMARY | NULL | NULL | NULL | 2327 | NULL |
| 2 | DERIVED | t3 | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.t1.sn | 1 | NULL |
| 2 | DERIVED | t2 | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.t1.sn | 1 | NULL |
| 9 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 9 | DEPENDENT SUBQUERY | t0 | eq_ref | PRIMARY | PRIMARY | 4 | zjzc.t.couponSn | 1 | Using where |
| 8 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 7 | DEPENDENT SUBQUERY | t | ref | ProductRepayment_idx1 | ProductRepayment_idx1 | 4 | zjzc.t1.sn | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | t | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | t | ref | ProductRepayment_idx1 | ProductRepayment_idx1 | 4 | zjzc.t1.sn | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | t | ref | ClientWithDrawOrder_idx1 | ClientWithDrawOrder_idx1 | 4 | zjzc.t1.sn | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | t | ref | ClientRechargeOrder_idx1 | ClientRechargeOrder_idx1 | 4 | zjzc.t1.sn | 1 | Using where |
+----+--------------------+------------+--------+--------------------------+--------------------------+---------+-----------------+------+-------------+
12 rows in set (0.00 sec)