利用临时表优化SQL

开发说他们有个SQL特别慢，让我帮忙看看

select id_ from t_channel where id_  not in (select distinct cdbh from sjkk_gcjl where jgsj>'2015-01-02 09:00:00' and jgsj<'2015-01-02 10:00:00' );
......
30分钟以上
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+------------------------------+
| id | select_type        | table     | type  | possible_keys | key      | key_len | ref  | rows   | Extra                        |
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+------------------------------+
|  1 | PRIMARY            | t_channel | index | NULL          | ID_      | 74      | NULL |  10476 | Using where; Using index     |
|  2 | DEPENDENT SUBQUERY | sjkk_gcjl | range | idx_jgsj      | idx_jgsj | 8       | NULL | 201263 | Using where; Using temporary |
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+------------------------------+

我看了下这个SQL，感觉如果查询In而不是not in,并改写下sql应该会快。和开发交流了下，可以换成in，他们处理逻辑上可以改下。

select id_ from t_channel where id_  in (select distinct cdbh from sjkk_gcjl where jgsj>'2015-01-02 09:00:00' and jgsj<'2015-01-02 10:00:00' );
......
1223 rows in set (29 min 29.29 sec)
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+------------------------------+
| id | select_type        | table     | type  | possible_keys | key      | key_len | ref  | rows   | Extra                        |
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+------------------------------+
|  1 | PRIMARY            | t_channel | index | NULL          | ID_      | 74      | NULL |  10476 | Using where; Using index     |
|  2 | DEPENDENT SUBQUERY | sjkk_gcjl | range | idx_jgsj      | idx_jgsj | 8       | NULL | 201263 | Using where; Using temporary |
+----+--------------------+-----------+-------+---------------+----------+---------+------+--------+------------------------------+

 改写上面SQL,速度快了很多。

select id_ from t_channel t1,(select distinct cdbh from sjkk_gcjl where jgsj>'2015-01-02 09:00:00' and jgsj<'2015-01-02 10:00:00' ) t2 where t1.id_= t2.cdbh;
.....
1223 rows in set (0.82 sec)
+----+-------------+------------+--------+---------------+----------+---------+---------+--------+------------------------------+
| id | select_type | table      | type   | possible_keys | key      | key_len | ref     | rows   | Extra                        |
+----+-------------+------------+--------+---------------+----------+---------+---------+--------+------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL     | NULL    | NULL    |   1700 |                              |
|  1 | PRIMARY     | t1         | eq_ref | PRIMARY,ID_   | PRIMARY  | 74      | t2.cdbh |      1 | Using where; Using index     |
|  2 | DERIVED     | sjkk_gcjl  | range  | idx_jgsj      | idx_jgsj | 8       | NULL    | 201263 | Using where; Using temporary |
+----+-------------+------------+--------+---------------+----------+---------+---------+--------+------------------------------+

后来他们说他们其实想更具查询出来的结果去刷新另一个表数据状态。然后更具他们的目的用临时表实现。

mysql> drop table if exists tmp_channel;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table tmp_channel  engine=memory (select distinct cdbh from sjkk_gcjl where jgsj>'2015-01-02 09:00:00' and jgsj<'2015-01-02 10:00:00' );
Query OK, 1700 rows affected (2.22 sec)
Records: 1700  Duplicates: 0  Warnings: 0

mysql> update t_channel set channelStatus = 1;
Query OK, 9075 rows affected (0.09 sec)
Rows matched: 10298  Changed: 9075  Warnings: 0

mysql> update t_channel set channelStatus = 0 where  id_   not in(select cdbh from t);
Query OK, 9075 rows affected (1.41 sec)
Rows matched: 9075  Changed: 9075  Warnings: 0

mysql> drop table if exists tmp_channel;
Query OK, 0 rows affected (0.00 sec)

这样整个过程也就3s+