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 
这是一个创建于 3937 天前的主题，其中的信息可能已经有所发展或是发生改变。
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 年 4 月 17 日 搞联合查询带这么多条件还玩子句，不慢才怪。明显不合理。在代码里拆分一下吧，宁可拆成二次查询 |
 | 3 ElmerZhang 2015 年 4 月 17 日  1 你这个SQL的扫描行数按explain的结果来看，大概会是 2959 * 1659 * 1 ＝ 4908981 |
 | 4 sujin190 OP @ElmerZhang mysql这时候要扫描这么多数据么？这种情况和直接把手机号写在in里有什么区别呢？ |
 | 5 whiteblack 2015 年 4 月 17 日 1 |
| 6 sujin190 OP @xiaobaigsy 好吧，了解了，感谢，好坑啊，为什么要设计成这样啊？ |
 | 7 zhanglp888 2015 年 4 月 17 日 有了group by后，必然会慢 |
| 8 whiteblack 2015 年 4 月 17 日 @sujin190 用久了mysql 就知道了，这玩意全是坑。。。。已经不知道发现多少诡异的mysql问题，最后了解到是mysql的bug了。。。 |
Select Language