xiaopingguoer 发表于 2011-10-11 14:05:11

向大家请教几个mysql优化，学习了

第一条：SELECT o.seller_memo,o.order_id, o.order_sn, o.add_time, o.order_status, o.shipping_status, o.order_amount, o.total_amount,o.deal_status,o.confirm_status,o.payment_status,o.payment_id, o.consignee, o.email, o.order_code, o.order_memo, o.order_flag,o.is_error,o.store_id,o.refunds_type,s.store_name, IFNULL(u.user_name, '匿名用户') AS user_name FROM `pb89_online`.`mf_order` AS oLEFT JOIN `pb89_online`.`mf_users` AS u ON u.user_id = o.user_id LEFT JOIN `pb89_online`.`mf_store` AS s ON s.store_id = o.store_id WHERE 1 =1AND o.payment_status = 2 AND short_time in
(20110928,20110927,20110926,20110925,20110924,20110923,20110922) AND o.store_id = '28' AND order_statusIN ('1','2')AND shipping_status   IN ('0','1')ORDER BY o.add_time DESC LIMIT 0, 90

第二条：SELECT g.goods_id, g.goods_name, g.goods_sn, g.promote_price, g.shop_price, g.cost_price, g.market_price, g.is_on_sale, g.is_best, g.is_new, g.is_hot, g.is_index, g.style_sort, g.goods_number,g.goods_number_A, g.integral, SUM(og.goods_number) AS sell_count, g.click_count, g.quota, (g.promote_price > 0 AND g.promote_start_date <= '1317225599' AND g.promote_end_date >= '1317225599') AS is_promote, g.sort_order, FROM_UNIXTIME(g.add_time, '%Y-%m-%d') AS add_date, g.goods_thumb, g.goods_class,g.up_down_tipFROM `pb89_online`.`mf_goods` AS g LEFT JOIN `pb89_online`.`mf_order_goods` AS og ON og.goods_sn = g.goods_snWHERE is_delete='0'AND g.is_real='1' GROUP BY g.goods_id ORDER BY is_on_sale DESC

kider 发表于 2011-10-13 10:01:20

乱码七糟，都不想看。

有好逻辑使语句简单。
多用用explian
检查索引
尽量少用in
...

je_ck 发表于 2011-10-14 05:59:59

第一个SQL语句的整理

本帖最后由 je_ck 于 2011-10-14 06:11 编辑

SELECT *
FROM `pb89_online`.`mf_order` AS o
        LEFT JOIN
`pb89_online`.`mf_users` AS u
ON u.user_id = o.user_id
        LEFT JOIN `pb89_online`.`mf_store` AS s
ON s.store_id = o.store_id
WHERE 1 =1AND
o.payment_status = 2 AND
short_time in (20110928,20110927,20110926,20110925,20110924,20110923,20110922) AND
o.store_id = '28' AND
order_statusIN ('1','2')AND
shipping_status   IN ('0','1')
ORDER BY o.add_time DESC
LIMIT 0, 90

je_ck 发表于 2011-10-14 06:11:04

第一个SQL语句的点评

本帖最后由 je_ck 于 2011-10-14 06:11 编辑

好像你所有操作都是围绕o（`pb89_online`.`mf_order`）来做的。
left join 感觉作用不大。
你可以考虑这样写
SELECT *
FROM (select * from `pb89_online`.`mf_order` WHERE 1 =1AND
o.payment_status = 2
AND short_time in (20110928,20110927,20110926,20110925,20110924,20110923,20110922)
AND o.store_id = '28' AND order_statusIN ('1','2')AND shipping_status   IN ('0','1')
ORDER BY o.add_time DESC LIMIT 0, 90
)AS o
        LEFT JOIN
`pb89_online`.`mf_users` AS u
ON u.user_id = o.user_id
        LEFT JOIN `pb89_online`.`mf_store` AS s
ON s.store_id = o.store_id

对于想通过联合过滤的，可以采用定时删除的方式。

je_ck 发表于 2011-10-14 06:13:12

第二个SQL语句的整理

本帖最后由 je_ck 于 2011-10-14 06:13 编辑

SELECT *
FROM `pb89_online`.`mf_goods` AS g
        LEFT JOIN
`pb89_online`.`mf_order_goods` AS og
ON og.goods_sn = g.goods_sn
WHERE is_delete='0'AND g.is_real='1' GROUP BY g.goods_id
ORDER BY is_on_sale DESC

je_ck 发表于 2011-10-14 06:14:37

像第二个就是比较简单的。如果你对性能不满意，我认为只有添加索引来解决了。
页: [1]
查看完整版本: 向大家请教几个mysql优化，学习了