mysql sql优化

limit优化案例

explain
-> select
-> a.id,b.shop_id,b.erp_store_id,b.shop_name,a.order_id,
-> SUM(a.amount) amount,
-> SUM(b.ware_total_price) wareAmount,
-> SUM(b.promotion_price) promotionAmout,
-> SUM(IF(a.payment_method=12,amount,0)) as unionPay,
-> SUM(IF(a.payment_method=13,amount,0)) as alipay,
-> SUM(IF(a.payment_method=10,amount,0)) as weChat,
-> SUM(IF(a.payment_method=53,amount,0)) as balance,
-> SUM(IF(a.payment_method=40,amount,0)) as mtCard
-> FROM xx a ,xxx b
-> where
-> a.order_id=b.id
-> and b.vender_id=1
-> and b.erp_store_id in (273)
-> and a.created>=’2016-05-09’
-> and a.created<=’2016-05-10’
-> GROUP by a.order_id
-> limit 0,10;
+——+————-+——-+——–+—————————————-+————————-+———+————————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+——-+——–+—————————————-+————————-+———+————————-+——+————-+
| 1 | SIMPLE | a | index | off_ord_id_act_paid_idx,idx_created_bj | off_ord_id_act_paid_idx | 8 | NULL | 2328 | Using where |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 8 | xxx.a.order_id | 1 | Using where |
+——+————-+——-+——–+—————————————-+————————-+———+————————-+——+————-+
2 rows in set (0.00 sec)

explain
-> select * from (
-> select
-> a.id,b.shop_id,b.erp_store_id,b.shop_name,a.order_id,
-> SUM(a.amount) amount,
-> SUM(b.ware_total_price) wareAmount,
-> SUM(b.promotion_price) promotionAmout,
-> SUM(IF(a.payment_method=12,amount,0)) as unionPay,
-> SUM(IF(a.payment_method=13,amount,0)) as alipay,
-> SUM(IF(a.payment_method=10,amount,0)) as weChat,
-> SUM(IF(a.payment_method=53,amount,0)) as balance,
-> SUM(IF(a.payment_method=40,amount,0)) as mtCard
-> FROM xxx a ,xxx b
-> where
-> a.order_id=b.id
-> and b.vender_id=1
-> and b.erp_store_id in (273)
-> and a.created>=’2016-05-09’
-> and a.created<=’2016-05-10’
-> GROUP by a.order_id
-> ) t
-> limit 0,10;
+——+————-+————+——–+—————————————-+—————-+———+————————-+——-+——————————————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+————+——–+—————————————-+—————-+———+————————-+——-+——————————————————–+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 66778 | |
| 2 | DERIVED | a | range | off_ord_id_act_paid_idx,idx_created_bj | idx_created_bj | 8 | NULL | 66778 | Using index condition; Using temporary; Using filesort |
| 2 | DERIVED | b | eq_ref | PRIMARY | PRIMARY | 8 | xxxa.order_id | 1 | Using where |
+——+————-+————+——–+—————————————-+—————-+———+————————-+——-+——————————————————–+
3 rows in set (0.00 sec)
第一个sql没有用上索引过滤，第二个用上索引先生成个临时表。

sql优化案例二：

explain SELECT erp_store_id from dmall_order.wm_order_merge a
-> WHERE
-> EXISTS
-> (
-> select *
-> FROM
-> dmall_ledger.off_ord_act_paid b
-> WHERE
-> a.id=b.order_id
-> and a.vender_id=1
-> and b.payment_time>=’2016-05-10 09:00:00’
-> and b.payment_time<=’2016-05-10 19:00:00’
-> ) ;
+——+————-+——-+——+———————————————+————————-+———+——————+———-+—————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+——-+——+———————————————+————————-+———+——————+———-+—————————-+
| 1 | PRIMARY | a | ALL | PRIMARY | NULL | NULL | NULL | 39799056 | Using where |
| 1 | PRIMARY | b | ref | off_ord_id_act_paid_idx,idx_payment_time_id | off_ord_id_act_paid_idx | 8 | dmall_order.a.id | 1 | Using where; FirstMatch(a) |
+——+————-+——-+——+———————————————+————————-+———+——————+———-+—————————-+
a表是个大表，优化器不智能，应该是先把b过滤完后，在驱动a去筛选条件，这种情况下，直接转换成表连接的方式，效果好很多
explain
select a.erp_store_id from dmall_order.wm_order_merge a ,dmall_ledger.off_ord_act_paid b where a.id=b.order_id
and a.vender_id=1
and b.payment_time>=’2016-05-10 09:00:00’
and b.payment_time<=’2016-05-10 19:00:00’ ;
+——+————-+——-+——–+———————————————+———————+———+————————-+——-+———————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+——-+——–+———————————————+———————+———+————————-+——-+———————–+
| 1 | SIMPLE | b | range | off_ord_id_act_paid_idx,idx_payment_time_id | idx_payment_time_id | 8 | NULL | 48966 | Using index condition |
| 1 | SIMPLE | a | eq_ref | PRIMARY,idx_vender_id | PRIMARY | 8 | dmall_ledger.b.order_id | 1 | Using where |
+——+————-+——-+——–+———————————————+———————+———+————————-+——-+————-