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

xiaopingguoer

第一条：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 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 =1  AND o.payment_status = 2 AND short_time in
(20110928,20110927,20110926,20110925,20110924,20110923,20110922) AND o.store_id = '28' AND order_status  IN ('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_tip  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

kider

乱码七糟，都不想看。

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

je_ck

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 =1  AND
o.payment_status = 2 AND
short_time in (20110928,20110927,20110926,20110925,20110924,20110923,20110922) AND
o.store_id = '28' AND
order_status  IN ('1','2')  AND
shipping_status   IN ('0','1')  
ORDER BY o.add_time DESC
LIMIT 0, 90

je_ck

好像你所有操作都是围绕o（`pb89_online`.`mf_order`）来做的。
left join 感觉作用不大。
你可以考虑这样写
SELECT *
FROM (select * from `pb89_online`.`mf_order` WHERE 1 =1  AND
o.payment_status = 2
AND short_time in (20110928,20110927,20110926,20110925,20110924,20110923,20110922)
AND o.store_id = '28' AND order_status  IN ('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

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

像第二个就是比较简单的。如果你对性能不满意，我认为只有添加索引来解决了。