关于mysql5.6对于子查询的优化

[netxuning]

分别在5.5.30和5.6.2中建立数据表及插入内容：

CREATE TABLE t1(id int, PRIMARY KEY (id)) engine=innodb;
INSERT INTO t1(id) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

CREATE TABLE t2(rid int, id int, PRIMARY KEY (rid, id)) engine=innodb;
INSERT INTO t2(rid, id) VALUES(547, 1),(547, 2),(547, 5),(547, 8),(203, 2),(203, 4),(203, 8);

用explain查看执行结果：

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.30-log |
+------------+
1 row in set (0.01 sec)

mysql> explain SELECT * FROM t1 INNER JOIN t2 WHERE rid = 203 AND t2.id > 2 AND t2.id IN (SELECT t1.id FROM t1 INNER JOIN t2 USING(id) WHERE rid = 547);
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                          |
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
|  1 | PRIMARY            | t2    | range  | PRIMARY       | PRIMARY | 8       | NULL       |    2 | Using where; Using index       |
|  1 | PRIMARY            | t1    | index  | NULL          | PRIMARY | 4       | NULL       |   10 | Using index; Using join buffer |
|  2 | DEPENDENT SUBQUERY | t2    | eq_ref | PRIMARY       | PRIMARY | 8       | const,func |    1 | Using index                    |
|  2 | DEPENDENT SUBQUERY | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | func       |    1 | Using where; Using index       |
+----+--------------------+-------+--------+---------------+---------+---------+------------+------+--------------------------------+
4 rows in set (0.01 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.10    | 
+-----------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM t1 INNER JOIN t2 WHERE rid = 203 AND t2.id > 2 AND t2.id IN (SELECT t1.id FROM t1 INNER JOIN t2 USING(id) WHERE rid = 547);
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                                              |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | ref    | PRIMARY       | PRIMARY | 4       | const            |    1 | Using where; Using index                           | 
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 8       | const,test.t2.id |    1 | Using index                                        | 
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.id       |    1 | Using index                                        | 
|  1 | SIMPLE      | t1    | index  | NULL          | PRIMARY | 4       | NULL             |   10 | Using index; Using join buffer (Block Nested Loop) | 
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+----------------------------------------------------+
4 rows in set (0.00 sec)

从执行策略上讲，5.6强于5.5，但不知道为什么，在实际生产环境中5.6在执行类似的sql语句时会很慢

mysql> EXPLAIN SELECT t1.id FROM `t1` INNER JOIN `t2` USING(id)  WHERE `t2`.`rid` =11825 AND `t1`.`id` IN  (SELECT `t1`.`id` FROM `t1` INNER JOIN `t2` USING(id) WHERE `t2`.`rid` =413157);                                            
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+------------------------------+
| id | select_type | table           | type   | possible_keys | key     | key_len | ref                                    | rows | Extra                        |
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+------------------------------+
|  1 | SIMPLE      | t2 | ref    | rid        | rid | 3       | const                                  | 6501 | Using index                  | 
|  1 | SIMPLE      | t2 | ref    | rid        | rid  | 7       | const,test.t2.id |    1 | Using index; Start temporary | 
|  1 | SIMPLE      | t1          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.id       |    1 | Using index                  | 
|  1 | SIMPLE      | t1          | eq_ref | PRIMARY       | PRIMARY | 4       | test.t2.id       |    1 | Using index; End temporary   | 
+----+-------------+-----------------+--------+---------------+---------+---------+----------------------------------------+------+------------------------------+

这个语句在老版本的mysql上执行很快，但5.6上则非常慢
而且，从执行策略上看，还多了Start temporary和End temporary，不解其意，特来问问。

[netxuning]

引用 1 楼 rucypli 的回复:

mysql最忌讳用in一个子查询 更改成表连接吧还是

哎 。。。

[netxuning]

引用 1 楼 rucypli 的回复:

mysql最忌讳用in一个子查询 更改成表连接吧还是

不过像我这种主查询和子查询都很复杂的应该如何改造呢？

SELECT * FROM t1 INNER JOIN t2 WHERE rid = 203 AND t2.id > 2 AND t2.id IN (SELECT t1.id FROM t1 INNER JOIN t2 USING(id) WHERE rid = 547);

[rucypli]

mysql最忌讳用in一个子查询 更改成表连接吧还是

[莱昂纳多达芬奇]

你这个查询也不复杂吧，in基本上都是可以改为join的 SELECT T1.ID, T2.RID, T2.ID FROM t1 INNER JOIN t2 JOIN ( SELECT T1.ID FROM T1 JOIN T2 ON T1.ID = T2.ID WHERE T2.RID = 547 )T ON T2.ID = T.ID WHERE rid = 203 AND t2.id > 2