分别在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,不解其意,特来问问。
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,不解其意,特来问问。
不过像我这种主查询和子查询都很复杂的应该如何改造呢?
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);
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