加载中…MySQL where条件包含IN()的子查询优化
(2015-01-20 18:47:30) | 分类： MySQL |
MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中
包含IN()的子查询。例如：
select * from testdb.test_0
where id in(select id from testdb.test_1 where
name='bj');
+----+------+------+
| id | name | sex
|
+----+------+------+
| 6 | xxx
| male |
| 19 | GZ |
male |
+----+------+------+
2 rows in set (0.00
sec)
MySQL对IN()列表中的选项有专门的优化策略，一般会认为MySQL会先执行子查询返回所有name='bj'的id。
一般情况下，IN()列表查询速度很快，所以我们会认为上述的查询会这样运行：
select * from testdb.test_0
where id in(6,19);
+----+------+------+
| id | name | sex
|
+----+------+------+
| 6 | xxx
| male |
| 19 | GZ |
male |
+----+------+------+
2 rows in set (0.00
sec)
但是很不幸，MySQL不是这样做的。MySQL会将相关的外层表压到子查询中，它认为这样可以更高效率地查找数据行。也就是说MySQL会将查询改写成下面的样子：
select * from testdb.test_0
where EXISTS(select * from testdb.test_1 where name='bj' and
test_1.id = test_0.id);
EXISTS后面的子查询实际不会返回具体的行数据，而是返回TRUE或者FALSE。也就是判断是否存在“select
* from testdb.test_1 where name='bj' and test_0.id =
test_1.id”这样的记录，如果存在，返回TRUE，最终返回查询结果。
来分析一下子查询语句：
explain select * from
testdb.test_0 where id in(select id from testdb.test_1 where
name='bj');
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type
| table | type | possible_keys
| key | key_len | ref | rows |
Extra
|
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY
| test_0 |
ALL | NULL
| NULL |
NULL | NULL |
15 | Using where |
| 2 |
DEPENDENT SUBQUERY | test_1 | ALL | NULL
| NULL | NULL
| NULL | 14
| Using where |
+----+--------------------+--------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00
sec)
根据EXPLAIN的输出我们可以看到，MySQL先选择对test_0表进行全表压缩，然后根据返回的id逐个执行子查询。如果是一个很小的表，这个查询的糟糕性能可能不会引起注意，但是如果外层表（这里是test_0）是一个非常大的表，进行了全表扫描，那么性能会变得非常糟糕。
当然我们可以很容易用下面的方法来重写该查询语句：
select test_0.* from test_0
inner join test_1 using(id) where test_1.name='bj';
+----+------+
| id | sex
|
+----+------+
| 6 | male
|
| 19 | male |
+----+------+
喜欢
0
赠金笔