mysql sql优化<1>

来源：互联网发布：程序员的自我修养目录编辑：程序博客网时间：2024/06/05 00:20
<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 cashCouponAmountFROM  Client t1 , ClientAssetInfo t2 , ClientPersonalInfo t3WHERE 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: 0mysql> 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)
0 0