这是一个创建于 3305 天前的主题,其中的信息可能已经有所发展或是发生改变。
select mobile, count(*) as cnt from trading_order where order_at>='2015-04-04 00:00:00' and order_at<'2015-04-17 00:00:00' and mobile>'' and status>3000 and mobile in (select o.mobile from trading_order_goods g left join trading_order o on g.order_id=o.order_id where g.trading_id='551e656c3f5bdd24568b4567' and o.order_at>='2015-04-04 00:00:00' and o.order_at<'2015-04-17 00:00:00' and o.mobile>'' and o.status>3000 group by o.mobile );
InnoDB引擎,2w数据,查询时间超过60s,explain
+----+--------------------+---------------+--------+--------------------------------------------+------------------+---------+-----------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------+--------+--------------------------------------------+------------------+---------+-----------------+------+----------------------------------------------+
| 1 | PRIMARY | trading_order | range | index_mobile,index_order_at | index_order_at | 4 | NULL | 2959 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | g | ref | index_order_id,index_trading_id | index_trading_id | 74 | const | 1659 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | o | eq_ref | index_order_id,index_mobile,index_order_at | index_order_id | 74 | gege.g.order_id | 1 | Using where |
+----+--------------------+---------------+--------+--------------------------------------------+------------------+---------+-----------------+------+----------------------------------------------+
3 rows in set (0.00 sec)
show processlist查看发现卡在了
Copying to tmp table | select mobile, count(*) as cnt from trading_order where order_at>='2015-04-04 00:00:00' and order_at
这是为什么呢?
8 条回复 • 2015-04-17 17:14:34 +08:00
|
|
1
hahasong 2015-04-17 10:50:06 +08:00
搞联合查询带这么多条件还玩子句,不慢才怪。明显不合理。在代码里拆分一下吧,宁可拆成二次查询
|
|
|
2
sujin190 2015-04-17 11:03:31 +08:00
@ hahasong 可是就算如此,join那部分就很快啊,万条数据而已,太不正常了吧
|
|
|
3
ElmerZhang 2015-04-17 11:11:07 +08:00 1
你这个SQL的扫描行数按explain的结果来看,大概会是 2959 * 1659 * 1 = 4908981
|
|
|
4
sujin190 2015-04-17 11:26:12 +08:00
@ ElmerZhang mysql这时候要扫描这么多数据么?这种情况和直接把手机号写在in里有什么区别呢?
|
|
|
7
zhanglp888 2015-04-17 15:31:21 +08:00
有了group by后,必然会慢
|
|
|
8
whiteblack 2015-04-17 17:14:34 +08:00
@ sujin190 用久了mysql 就知道了,这玩意全是坑。。。。已经不知道发现多少诡异的mysql问题,最后了解到是mysql的bug了。。。
|