mysql sql force_index 优化案例

下面的sql运行了27秒
explain SELECT
-> COUNT(1)
-> FROM
-> (SELECT
-> t3.store_id
-> FROM
-> dmall_tmp.wm_order t1 , dmall_oop.store t2, dmall_dw.dm_store t3
-> WHERE
-> t1.shop_id = t2.sap_id
-> AND t2.id = t3.ori_store_id
-> AND t1.order_create_time >= 20160504
-> AND t1.order_create_time <= 20160505
-> AND t3.store_id IN (718 , 719, 720, 4262, 4263, 4264, 4265, 4266, 4267, 4268, 4269, 4270, 4271, 4272, 4439, 4440, 4637, 4638, 4639, 5709, 5710, 5712, 5713)
-> GROUP BY DATE_FORMAT(t1.order_create_time, ‘%Y%m%d’) , t3.store_id) a;
+——+————-+————+——–+—————————+———+———+————————–+——+———————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+————+——–+—————————+———+———+————————–+——+———————————————-+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 5451 | |
| 2 | DERIVED | t3 | range | PRIMARY,iindex | PRIMARY | 4 | NULL | 23 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | t2 | eq_ref | PRIMARY,sap_id | PRIMARY | 4 | dmall_dw.t3.ori_store_id | 1 | Using where |
| 2 | DERIVED | t1 | ref | shop_id,order_create_time | shop_id | 8 | dmall_oop.t2.sap_id | 237 | Using index condition; Using where |
+——+————-+————+——–+—————————+———+———+————————–+——+———————————————-+
4 rows in set (0.00 sec)
我们看到在最下面的一行中，使用了shop_id去连接了，没有使用到order_create_time，而时间列的过滤性是十分强的，最好的情况就是按时间过滤后，再去关联，这样结果集就小很多，按照这个思路，我们让t1使用时间列上的索引

explain SELECT
-> count(*)
-> FROM
-> dmall_tmp.wm_order t1 FORCE INDEX(order_create_time), dmall_oop.store t2, dmall_dw.dm_store t3
-> WHERE
-> t1.shop_id = t2.sap_id
-> AND t2.id = t3.ori_store_id
-> AND t1.order_create_time >= 20160504
-> AND t1.order_create_time <= 20160505
-> AND t3.store_id IN (718 , 719, 720, 4262, 4263, 4264, 4265, 4266, 4267, 4268, 4269, 4270, 4271, 4272, 4439, 4440, 4637, 4638, 4639, 5709, 5710, 5712, 5713);
+——+————-+——-+——–+——————-+——————-+———+————————–+——-+————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+————-+——-+——–+——————-+——————-+———+————————–+——-+————————————+
| 1 | SIMPLE | t3 | range | PRIMARY,iindex | PRIMARY | 4 | NULL | 23 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY,sap_id | PRIMARY | 4 | dmall_dw.t3.ori_store_id | 1 | Using where |
| 1 | SIMPLE | t1 | range | order_create_time | order_create_time | 9 | NULL | 87108 | Using index condition; Using where |
+——+————-+——-+——–+——————-+——————-+———+————————–+——-+————————————+
虽然t1的结果集变大了，但是没有了，实际执行时间下将到2s