V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
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
sujin190
V2EX  ›  MySQL

mysql 查询 Copying to tmp table 疑问

  •  
  •   sujin190 ·
    snower · 2015-04-17 10:12:03 +08:00 · 3546 次点击
    这是一个创建于 3492 天前的主题,其中的信息可能已经有所发展或是发生改变。
    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
    hahasong
        1
    hahasong  
       2015-04-17 10:50:06 +08:00
    搞联合查询带这么多条件还玩子句,不慢才怪。明显不合理。在代码里拆分一下吧,宁可拆成二次查询
    sujin190
        2
    sujin190  
    OP
       2015-04-17 11:03:31 +08:00
    @hahasong 可是就算如此,join那部分就很快啊,万条数据而已,太不正常了吧
    ElmerZhang
        3
    ElmerZhang  
       2015-04-17 11:11:07 +08:00   ❤️ 1
    你这个SQL的扫描行数按explain的结果来看,大概会是 2959 * 1659 * 1 = 4908981
    sujin190
        4
    sujin190  
    OP
       2015-04-17 11:26:12 +08:00
    @ElmerZhang mysql这时候要扫描这么多数据么?这种情况和直接把手机号写在in里有什么区别呢?
    whiteblack
        5
    whiteblack  
       2015-04-17 13:40:52 +08:00   ❤️ 1
    DEPENDENT SUBQUERY 的问题,这里涉及到in的执行过程,具体看这篇博文

    http://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html
    sujin190
        6
    sujin190  
    OP
       2015-04-17 14:46:48 +08:00
    @xiaobaigsy 好吧,了解了,感谢,好坑啊,为什么要设计成这样啊?
    zhanglp888
        7
    zhanglp888  
       2015-04-17 15:31:21 +08:00
    有了group by后,必然会慢
    whiteblack
        8
    whiteblack  
       2015-04-17 17:14:34 +08:00
    @sujin190 用久了mysql 就知道了,这玩意全是坑。。。。已经不知道发现多少诡异的mysql问题,最后了解到是mysql的bug了。。。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5346 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 07:45 · PVG 15:45 · LAX 23:45 · JFK 02:45
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.