Mysql SQL 性能优化

explain EXTENDED  SELECT t.id           AS trade_id,
       t.trade_no,
       t.gmt_create   AS order_time,
       t.gmt_modified AS cancel_time,
       IF(t.status = 7, TIMESTAMPDIFF(SECOND, t.gmt_create, NOW()), '') AS countDownSecond,
       IFNULL(ti.deal_price, 0) - IFNULL((SELECT IFNULL(SUM(IFNULL(tp.remit_price, 0)),0) FROM trade_payment tp WHERE tp.trade_id = t.id), 0) AS unpaidAmount,
       ti.deal_price,
       ti.data_validity,
       t.status,
       t.payment_type,
       ti.product_id,
       t.source,
       t.type,
       p.id           AS product_id,
       p.name         AS product_name,
       p.image        AS product_image,
       p.type         AS product_type,
       p.product_price
    FROM trade t
           INNER JOIN trade_info ti ON t.id = ti.trade_id
           LEFT JOIN product p ON p.id = ti.product_id
    WHERE t.is_delete = 0
      AND ti.is_delete = 0
      AND t.customer_id='193e45f7efdf43648e523832fad75c54'
    ORDER BY t.gmt_create DESC;

SHOW WARNINGS ;

显示出可优化的sql，进行优化即可，假设 trade, trade_info 各50w数据

/* select#1 */ select `t`.`id`                                                                          AS `trade_id`,
                      `t`.`trade_no`                                                                    AS `trade_no`,
                      `t`.`gmt_create`                                                                  AS `order_time`,
                      `t`.`gmt_modified`                                                                AS `cancel_time`,
                      if((`t`.`status` = 7), timestampdiff(SECOND, `t`.`gmt_create`, now()),
                         '')                                                                                   AS `countDownSecond`,
                      (ifnull(`ti`.`deal_price`, 0) -
                       ifnull((/* select#2 */ select ifnull(sum(ifnull(`tp`.`remit_price`, 0)), 0)
                                              from `trade_payment` `tp`
                                              where (`tp`.`trade_id` = `t`.`id`)),
                              0))                                                                              AS `unpaidAmount`,
                      `ti`.`deal_price`                                                                 AS `deal_price`,
                      `ti`.`data_validity`                                                              AS `data_validity`,
                      `t`.`status`                                                                      AS `status`,
                      `t`.`payment_type`                                                                AS `payment_type`,
                      `ti`.`product_id`                                                                 AS `product_id`,
                      `t`.`source`                                                                      AS `source`,
                      `t`.`type`                                                                        AS `type`,
                      `p`.`id`                                                                          AS `product_id`,
                      `p`.`name`                                                                        AS `product_name`,
                      `p`.`image`                                                                       AS `product_image`,
                      `p`.`type`                                                                        AS `product_type`,
                      `p`.`product_price`                                                               AS `product_price`
               from `trade` `t`
                      join `trade_info` `ti`
                      left join `product` `p` on ((`p`.`id` = `ti`.`product_id`))
               where ((`ti`.`trade_id` = `t`.`id`) and (`ti`.`is_delete` = 0) and
                      (`t`.`is_delete` = 0) and (`t`.`customer_id` = '193e45f7efdf43648e523832fad75c54'))
               order by `t`.`gmt_create` desc