Mysql SQL 性能优化
2019-04-23 本文已影响0人
赛亚人之神
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
