MYSQL之not in优化方法:left join


本站和网页 http://www.piaoyi.org/database/mysql-not-in-left-join.html 的作者无关,不对其内容负责。快照谨为网络故障时之索引,不代表被搜索网站的即时页面。

MYSQL之not in优化方法:left join
飘易首页 |
留言本 |
关于我 |
订阅Feed
MYSQL之not in优化方法:left join
Author:飘易 Source:飘易
Categories:数据库 PostTime:2017-10-25 13:11:10
正 文:
有一个项目,mysql 语句采用了not in,结果某些页面打开需要40多秒,排查sql语句后,发现是采用了 not in 语法导致全表扫描,消耗了大量的时间,飘易记录下优化的过程:项目简介:会议应该签到表 signshould :15万条数据会议实际签到表 sign :10万条数据请假表 leaves :1000条数据其中字段:mid:会议id,uid:用户id【例一】:原先的 not in 语句:select uid from signshould where mid=897
and uid not in(select uid from sign where mid=897 and thetype=0)
and uid not in(select uid from leaves where mid=897)时间: 18.898s查看状态,可以看到 Handler_read_rnd_next 值很大,达到了 1073万次请求,该参数的含义:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。说明上诉sql语句引起了全表扫描。explain SQL语句:此时,我们在 mid 和 uid 上并未建立索引。优化思路1:在 mid 和 uid 上建立索引后再 explain:时间下降到: 0.039s。优化思路2:采用left join 和 右表.id is null 的方法优化:select a.* from signshould as a
LEFT JOIN (select * from sign where mid=897 and thetype=0) as b ON a.uid=b.uid
LEFT JOIN (select * from leaves where mid=897) as c ON a.uid=c.uid
where a.mid=897 and b.uid is null and c.uid is null没有建立索引时间: 0.031s建立索引时间: 0.016s飘易发现采用 left join 方法执行sql语句的时候:没有索引的前提下,执行时间仅为not in方法的 1.6/千分 【0.031/18.898】;建立了索引后消耗时间仅为not in(也建立索引)方法的 40% 【0.016/0.039】。索引not in 执行时间left join 执行时间优化后时间占比无索引18.8980.0311.6‰有索引0.0390.01640%可以看到优化后的 Handler_read_rnd_next 值下降到了22万。注:LEFT JOIN 关键字会从左表那里返回所有的行,即使在右表中没有匹配的行。EXPLAIN sql:【例二】:原先的not in的sql语句2:select uid from sign where mid=674 and thetype=0
and uid not in(select uid from sign where mid=674 and thetype=1)
and uid not in(select uid from leaves where mid=674)时间: 39.208s可以看到 Handler_read_rnd_next 值很大,达到了 2500万,也是全表扫描导致的。采用left join 和 右表.id is null 方法优化:select a.* from sign as a
LEFT JOIN (select * from sign where mid=674 and thetype=1) as b ON a.uid=b.uid
LEFT JOIN (select * from leaves where mid=674) as c ON a.uid=c.uid
where a.mid=674 and a.thetype=0 and b.uid is null and c.uid is null时间: 0.048s优化后 Handler_read_rnd_next 的值下降到了 18万。LEFT JOIN 要点:select t1.id,t2.id from t1
left join t2 on t1.id = t2.id and t1.id>1 and t2.id<>3在mysql的 left join 中条件放在on后面和在where后面是不同的;1. on后面只针对于t2表进行过滤,所以上面的 t1.id>1 将不起作用,切记,切记;2. where后面会对最终结果产生影响,所以如果t2.id<>3放到on后面和where后面也是会返回不同的结果; 例如下面脚本一会比脚本二多返回一些数据。select * from test2 left join test1 on test2.id = test1.id and test1.id<>3 where test2.id <>6;
--
select * from test2 left join test1 on test2.id = test1.id where test2.id <>6 and test1.id<>3;本文完。
作者:飘易
来源:飘易
版权所有。转载时必须以链接形式注明作者和原始出处及本声明。
Tag:MYSQL not in 优化 left join 浏览(次) 我要评论(2条)
上一篇:js版本的crc32计算,修正会出现负数的bug下一篇:Laravel如何使用github上自己fork来的依赖包资源
2条评论 “MYSQL之not in优化方法:left join”
1 熊22018-11-13 14:51:14现在遇到个单表扫描 按照你的这种方法写SQL 还是全表扫描,大佬能帮忙看下吗2 hex2019-9-17 9:43:20select * from a inner join b on b.id<>a.id
发表评论
名称(*必填)
邮件(选填)
网站(选填)
记住我,下次回复时不用重新输入个人信息
目 录
博客首页SEO技术(104)
Asp/Asp.Net(26)
网络技术(91)
电脑技术(71)
数据库(27)
病毒攻防(12)
网络营销(64)
网站设计(19)
个人杂想(79)
PHP编程(56)
Delphi编程(3)
C#编程(25)
SEO工具(12)
Linux技术(27)
移动开发(12)
JAVA开发(1)
Python(1)
物联网(13)
飘易搜索
最新文章
1、乐鑫ESP8266使用MQTT-AT固件连接乐为物联网
2、乐鑫ESP8266烧录固件、升级最新固件、刷MQTT固件
3、Arduino利用AT指令连接乐鑫ESP8266实现串口通讯、配网
4、Arduino控制RGB三色LED灯,实现1600万种酷炫颜色
5、Arduino通过DHT11数字式温湿度传感器获取温度、湿度
6、Arduino单片机开发基础、编程语言及各开发板区别
相关文章
1、SQL2005数据库还原到SQL2000的方法!
2、Access数据库转化Mssql 2000及数据库转移要点
3、windows下mysql每天定时备份数据库的方法
4、Microsoft SQL Server 2005简体中文开发版下载
5、CPanel下Cron Jobs还原超大MYSQL数据库的方法
6、解决MYSQL:Incorrect string value:\x错误!
随机文章
1、HTTPS架设:LNMP和LNMPA配置NameCheap的comodo positivessl SSL(9美元/年)
2、利用Tumblr Automatic Like软件日引英文流量2000IP+
3、Google改版搜索“咖啡因”对外公测
4、关于隐藏来路 Referrer 的方法探讨
5、百度快照惊现1970年网页
6、LNMP安装Pureftpd FTP服务器以及问题汇总
2007-2019 飘易博客 Www.Piaoyi.Org 原创文章版权由飘易所有

Copyright ©uecom 京ICP备18064371号-3 IPV6
2021-07-27 20:34:01
tech.zxsbr.com
172.17.253.20