MySQL in查询优化<一>

最新推荐文章于 2024-09-09 10:43:28 发布

kf_panda

最新推荐文章于 2024-09-09 10:43:28 发布

阅读量9.5k

点赞数 1

分类专栏： MYSQL索引&SQL优化&服务器优化

本文链接：https://blog.csdn.net/gua___gua/article/details/47401621

版权

针对一个运行缓慢的SQL查询，分析发现MySQL优化器将IN查询转换为EXISTS，导致全表扫描，严重影响性能。在MySQL 5.5版本中，这种现象尤为明显。通过两种优化策略，包括将IN查询转为JOIN操作以及利用MEMORY引擎的临时表，可以显著提升查询效率。

摘要由CSDN通过智能技术生成

开发说他写了个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_