MySQL in查询优化<一>

开发说他写了个SQL特别慢，让看看。

select * from t_channel where id_ in(select distinct cdbh from sjkk_gcjl where jgsj>'2015-01-02 08:00:00' and jgsj<'2015-01-02 12:00:00');
......
30min+

然后我查询内部SQL,只需要3s+

mysql> select distinct cdbh from sjkk_gcjl where jgsj>'2015-01-02 08:00:00' and jgsj<'2015-01-02 12:00:00';
.....
1755 rows in set (3.30 sec)

mysql> select count(*) from t_channel;
....
12062 rows in set (0.70 sec)

开发写的SQL为啥那么慢呢？看看执行计划

explain extended select * from t_channel where id_ in(select distinct cdbh from sjkk_gcjl where jgsj>'2015-01-02 08:00:00' and jgsj<'2015-01-02 12:00:00');
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+----------+------------------------------+
| id | select_type        | table     | type  | possible_keys | key      | key_len | ref  | rows   | filtered | Extra                        |
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+----------+------------------------------+
|  1 | PRIMARY            | t_channel | ALL   | NULL          | NULL     | NULL    | NULL |  12062 |   100.00 | Using where                  |
|  2 | DEPENDENT SUBQUERY | sjkk_gcjl | range | idx_jgsj      | idx_jgsj | 8       | NULL | 731868 |   100.00 | Using where; Using temporary |
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+----------+------------------------------

看看数据库转换后的语句

mysql> show warnings;
SELECT
    `shanghai_full`.`t_channel`.`ID_` AS `ID_`,
    `shanghai_full`.`t_channel`.`Code_` AS `Code_`,
     ...... --这里会列出所有字段
FROM
    `shanghai_full`.`t_channel`
WHERE
    < in_