MySQL 查询显示 Copying to tmp DEPENDENT SUBQUERY的解决方案

当一个MySQL查询中包含子查询的时候, 一定要格外留意, 最好先用EXPLAIN检查一下, 因为这个查询可能会非常缓慢, 甚至永远无法返回, 如果查询的表只有表级锁, 这个查询会阻塞后续的所有写入查询, 并且发起这些查询的连接也不会释放, 最终导致最大可用连接耗尽, 除非启用管理员账号, 可能连登录数据库都无法实现. 下面是一个案例.

 
SELECT COUNT(*) FROM `stat` WHERE `ip` IN (SELECT ip  FROM `stat`   GROUP BY `ip` HAVING COUNT(ip) > 1000 ORDER BY COUNT(ip) DESC)
 

这个查询首先收集那些达到1000次访问记录的IP地址, 然后查询所有来自这些IP地址的访问记录总数.

我以为这个查询只需要几秒钟就会完成, 但发出查询之后MySQL命令行客户端停在那里一动不动, 运行show processlist显示查询一直卡在"Copying to tmp table"状态.

 
+----+------+----------------+----------+---------+-------+----------------------+
| Id | User | Host           | db       | Command | Time  | State                |
+----+------+----------------+----------+---------+-------+----------------------+
|  3 | root | localhost:4158 | database | Query   | 69403 | Copying to tmp table |
|  6 | root | localhost:7595 | database | Query   |     0 | NULL                 |
+----+------+----------------+----------+---------+-------+----------------------+
 

我想知道这个查询到底需要多久, 于是不关机并让这个查询继续运行, 第二天再看show processlist的结果, 发现这个查询已经运行了19个小时, 还没有跑完. 考虑到整个表的数据才不到30万行, 可以肯定这个查询不是快慢的问题, 而是根本没有进展, 或许是卡在某个环节不动了, 因为看着个样子, 这个查询可能无限期运行.

按照一开始的想法, 这个查询应该可以拆分成两个独立的查询. 运行结果显示, 子查询比较费时, 花了5.18秒返回27行结果. 然后手工将这个27个结果拼接成IN查询字句的参数得到外层查询, 运行时间是0.27s.

 
 
mysql> select ip  FROM `stat`   group by `ip` having count(ip) > 1000 order by count(ip) desc;
+-----------------+
| ip              |
+-----------------+
| 112.124.0.114   |
| 118.31.120.31   |
| 39.101.185.229  |
| 120.26.50.66    |
| 47.99.196.234   |
| 121.40.190.236  |
| 198.143.187.202 |
| 117.41.183.82   |
| 125.64.94.206   |
| 39.103.142.195  |
| 39.101.184.55   |
| 121.42.142.188  |
| 42.62.37.103    |
| 162.241.24.182  |
| 222.42.21.228   |
| 31.128.252.4    |
| 121.196.99.99   |
| 45.227.255.149  |
| 125.64.94.221   |
| 47.113.87.53    |
| 183.3.161.252   |
| 39.101.205.97   |
| 104.148.87.124  |
| 37.187.56.66    |
| 46.105.98.166   |
| 142.4.215.116   |
| 218.77.94.78    |
+-----------------+
27 rows in set (5.18 sec)
 
SELECT count(*) FROM `stat` where `ip` in ('112.124.0.114','118.31.120.31','39.101.185.229', '120.26.50.66', '47.99.196.234', '121.40.190.236', '198.143.187.202', '117.41.183.82', '125.64.94.206', '39.103.142.195', '39.101.184.55', '121.42.142.188', '42.62.37.103', '162.241.24.182', '222.42.21.228', '31.128.252.4', '121.196.99.99', '45.227.255.149', '125.64.94.221', '47.113.87.53', '183.3.161.252', '39.101.205.97', '104.148.87.124', '37.187.56.66', '46.105.98.166', '142.4.215.116', '218.77.94.78')
 
+----------+
| count(*) |
+----------+
|    43387 |
+----------+
1 row in set (0.27 sec)
 

哪里出了问题?

执行EXPLAIN告诉我们这些信息

 
+----+--------------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+--------------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | PRIMARY            | stat  | ALL  | NULL          | NULL | NULL    | NULL | 287789 | Using where                     |
|  2 | DEPENDENT SUBQUERY | stat  | ALL  | NULL          | NULL | NULL    | NULL | 287789 | Using temporary; Using filesort |
+----+--------------------+-------+------+---------------+------+---------+------+--------+---------------------------------+
 

最重要的报警信号就是DEPENDENT SUBQUERY. 意思是这个查询会从外向内执行, 而不是我们认为的从内向外. 也就是说对外部查询的每一行都要把内部查询执行一次. 如果仅仅是检查这个查询本身, 这不是大多数人所预期的查询计划. 一般的想法是先执行内部查询得到一个IP地址列表, 然后外层查询对每一行判断IP是否在这个列表中, 即便外层查询执行了全表扫描, 也不会花费太长时间. MySQL实际做的与此相反, 由DEPENDENT SUBQUERY可知, 这个查询被认为是correlated subquery, 即相关子查询, 意思是子查询之中引用了外部查询的表, 在这个例子中子查询和外部查询用的是同一个表, 构成的相关子查询, 因此子查询不能独立于外部查询. 可以这样理解, 子查询好比一个回调函数, 该函数需要一个参数, 只有外部查询才能提供这个参数, 每当外部查询取得一行, 就要调用子查询并传参. 这样解释了为什么查询总是处在"Copying to tmp table"状态, 因为整个查询在一遍又一遍地重复执行子查询. 虽然事实上在这个例子中逻辑上子查询是可以独立执行的, 因为查询的是同一个表, 既可以认为引用了外层查询涉及的表, 也可以认为只是引用了自身, 但是MySQL的查询优化器并没有这么智能, 而是采用了最保守的判断, 因为这样判断肯定不会出错, 查询快慢的优先级是靠后的.

如上所述, 每次子查询花费5秒多一点的时间, 那么总时间大概是300,000 * 5 = 416小时.

怎么优化DEPENDENT SUBQUERY子查询?

可以看到这里的关键问题是耗时的子查询被不必要的反复执行了多次, 因此最直接的优化方法就是利用某种缓存机制, 因为每次执行子查询返回的结果是一样的. 一个办法是用derived table来缓存子查询的结果, 外层查询的每次查询只是针对这个缓存, 那么子查询只会运行一次, 其余的时间就是外层查询循环查询derived table. 注意这样并没有改变DEPENDENT SUBQUERY的性质. 只不过现在是dependent derived table, 而不是dependent subquery.

 
mysql> SELECT COUNT(*) FROM `stat` WHERE `ip` IN (SELECT `ip` FROM (SELECT ip  FROM `stat` GROUP BY `ip` HAVING COUNT(ip) > 1000 ) AS temp);
+----------+
| COUNT(*) |
+----------+
|    43387 |
+----------+
1 ROW IN SET (4.59 sec)