利用临时表优化SQL
来源:互联网 发布:淘宝网禁止出售药房 编辑:程序博客网 时间:2024/05/19 05:06
开发说他们有个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: 0mysql> update t_channel set channelStatus = 1;Query OK, 9075 rows affected (0.09 sec)Rows matched: 10298 Changed: 9075 Warnings: 0mysql> 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: 0mysql> drop table if exists tmp_channel;Query OK, 0 rows affected (0.00 sec)这样整个过程也就3s+
0 0
- 利用临时表优化SQL
- 利用SQL全局临时表防止登录
- 利用SQL的全局临时表防止用户重复登录
- 利用SQL的全局临时表防止用户重复登录
- 利用临时表、REF游标、动态SQL实现分页查询
- 利用SQL的全局临时表防止用户重复登录
- 利用SQL的全局临时表防止用户重复登录
- 利用SQL临时表判断用户是否登录
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用 SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- SQL优化实战:临时表+分批提交+按日结存
- 优化临时表使用,SQL语句性能提升100倍
- 优化临时表使用,SQL语句性能提升100倍
- MySQL5.6 运用临时表做SQL优化
- 调用通讯录、蓝牙、内购、GameCenter、iCloud、Passbook系统等服务开发汇总
- 多重循环的一些例子!字母塔,三角形塔,菱形塔,空心菱形塔,9*9乘法表等!
- 运行sh提示 unexpected operator
- java-事件监听
- C#基本异步操作
- 利用临时表优化SQL
- 关于链表操作编程实现的一些总结
- Qt开发小工具之gif转换器
- 经典算法题每日演练——第二题 五家共井
- Apache 映射端口-http_proxy方式
- 获取url中的参数的方法
- web安全中的token
- 数据结构与算法-抽象数据类型
- Hadoop集群(第6期)_WordCount运行详解