› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
mysql 查询 Copying to tmp table 疑问
sujin190 · snower · 2015-04-17 10:12:03 +08:00 · 3872 次点击这是一个创建于 3850 天前的主题，其中的信息可能已经有所发展或是发生改变。
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
这是为什么呢？
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
这是为什么呢？
 |
1
hahasong 2015-04-17 10:50:06 +08:00
搞联合查询带这么多条件还玩子句，不慢才怪。明显不合理。在代码里拆分一下吧，宁可拆成二次查询
|
 |
3
ElmerZhang 2015-04-17 11:11:07 +08:00  1
你这个SQL的扫描行数按explain的结果来看，大概会是 2959 * 1659 * 1 ＝ 4908981
|
 |
4
sujin190 OP @ElmerZhang mysql这时候要扫描这么多数据么？这种情况和直接把手机号写在in里有什么区别呢？
|
 |
5
whiteblack 2015-04-17 13:40:52 +08:00 1
|
|
6
sujin190 OP @xiaobaigsy 好吧，了解了，感谢，好坑啊，为什么要设计成这样啊？
|
 |
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了。。。
|
Select Language