MYSQL子查询优化.docx

上传人:lao****ou 文档编号:136913 上传时间:2023-04-10 格式:DOCX 页数:5 大小:21.08KB
下载 相关 举报
MYSQL子查询优化.docx_第1页
第1页 / 共5页
MYSQL子查询优化.docx_第2页
第2页 / 共5页
MYSQL子查询优化.docx_第3页
第3页 / 共5页
MYSQL子查询优化.docx_第4页
第4页 / 共5页
MYSQL子查询优化.docx_第5页
第5页 / 共5页
亲,该文档总共5页,全部预览完了,如果喜欢就下载吧!
资源描述

《MYSQL子查询优化.docx》由会员分享,可在线阅读,更多相关《MYSQL子查询优化.docx(5页珍藏版)》请在第一文库网上搜索。

1、子查询优化子查询转换为连接子查询优化是MySQL DBA必备的一个技能,原因如下: MySQL对子查询的处理并不是很好,不能像Oracle一样可以改写子查询。基于代价的查询优化器并不完善(在MySQL5.7版本之后逐渐加强)。本章我们讲解几个子查询优化案例,让大家加深对子查询的印象。案例中所使用的表及数据可以从hHps:/问题现象每日夜间对客户系统做备份时总是不成功,并导致整个系统不能正常运行,排查后发现是由于该备份操作与一条查询SQL语句发生了冲突。关于备份为何会与查询SQL语句冲突这里不做过多解释,我们主要分析整条SQL语句为何执行过慢。SQL语句结构如下:select count(*)

2、from enloyees as a where exists (select en_no from dept_emp b wherea.emp no = b. emp no and b. dept_no = 1 d007 ,);这是一个很简单的子查询,但是在早期的MySQL版本(客户使用的是5.5版本)中对子查询的处理并不是先执行子查询,然后再与外表进行关联的,而是遍历employees (a)表中的每一条记录,代入到子查询中。这条查询语句的执行计划如图381所示。| 1d | select.type| type | posslble.keys | koy | key.ln | ref| r

3、ows | Ero|1 | PRIMARY| a| Index | NULL| PRIMARY | 4| HULL|299512 | Using where; Using index |2 I DEPENDENTSU8QUERY | b| eq.ref | PRIMARYno | PRIMARY | 8| employee*.a.eop.no.const|1 | Using where; UK” index |图38-1从执行计划(关于执行计划详解,请参考本书下载资源中的“附录D”)上看,先执行的是b表,但是b表的selecjtype是“DEPENDENT SUBQUERY”,表示这个子查询依

4、赖于外表查询,而不是先执行子香询。在MySQL早期版本中,对于in的操作子查询是不能展开的,但是在新版本中做了一些优化,可以将子查询展开。例如,下面的SQL语句的执行计划如图38-2所示。mysql explain extended select count(*) from enloyees as a where enp_n。in (select emp_nofrom dept enp b where b. enp no = a. enp no and b. dept no = fd0071);,I.图38-2在执行计划中,我们通过explain extended查看到语句已经被转换为连接,此

5、时的执行计划是先执行b表,然后再与a表做关联查询。优化方案在MySQL的有些版本中,如果不能将子查询展开,则可以将SQL语句改写为关联查询的形式,如下所示。select count(*) from employees as a, (select distinct en)_no from dept_en5) where dept_no=*d007,)b where a.&mp_no = b.emp_no;改写为关联查询后,能达到的优化效果和子查询展开效果一样(这里的distinct是为了去重,如果emp_no存在重复的数据,则执行结果集不对。其实这里不用使用distinct,因为dept_no与

6、emp_no是联合主键),执行计划如图38-3所示。explain select count() fron employees as 1 (select distinct e*p.no fro dept.ep fer。 dpt.no : ,dOTt)b i*ere p.no b.ep.AO1 1ype |Itype*1possible.1 F1 ahey.Un |1 rows1Extra1 111PR1WARY|IAll1MULLI WLL1NULL|NULL|1NULL1111PR1RARY|*I1W!MARV| PRIHARY141b一JO |11Using 1n0e select cou

7、nt() from employees as a where exists (select emp_no from dept_emp bwhere a.emp_no = b.en5_no and b.dept_no = fd0071);I count (*) I52245 |1 row in set (0.44 sec)mysql select count() from employees as a,(select distinct emp_no from dept_en wheredept_no = *d0071 )b where a. en5_no = b. emp_no;I count

8、(*) I52245 I1 row in set (0.07 sec)增大数据量级,两种查询执行的效果会更加明显。通过上面的例子,我们总结出子查询优化的思路如下:驱动表是小表,因为驱动表需要做全表扫描,这一点在21.2节中已讲过。尽量让子查询先执行过滤结果集,这样才能让更小的结果集去驱动大表。接。通过查看执行计划来判断子查询是否展开,若没有展开,则将子查询转换为连子查询优化使用delete删除数据前面两章中的案例都是关于查询问题的,但是有时候我们在做删除、更新时同样会遇到性能问题,解决思路是相同的,本章讲解的就是一个删除数据时性能存在问题的案例。问题现象通常我们删除数据时都是单表删除,带有一个

9、where条件。但有时根据业务需求,也会在使用delete删除数据时依赖其他表的数据,写起来就是一个子查询,例如:delete from e_cons_snapwhere cons_id in (select 1from c conswhere mr sect no = 111and status code=9);e_cons_snap和c_cons这两个表的数据量都在百万级,执行时间需要10s左右,执行计划如图39-1所示。r.fqb do T “ 二n三*j :飞飞 Je i* :seec: 1Tor 5/引 1 SKt、,)-ireW :;iiiiiId二月箕 grllk、*;、27 1

10、虱 * 13 fe:rc 屋下 /:, UMI IJIIIIMH axnle.1-*.-;厂$_i.引、3 t力铲;rm.s_c,-? 3rd x.r-.sict.ro - 1一 二id -.urs_ =b.ji3_idid 三_1_1了卜三 IrbK lypTi.u:ib】e_k可,I/k:?_lvr i ei j 3 Sir 二h rf 1dx_5e:t_ro二 15二。八stl js Ing .ewrv5二y 二 LE二rj;_idl:,.J.ur$_ 二二 。.Oir5_i J1 HULL:ro.s 1r 5- :*0. W 5oc:图39-2这个执行计划先执行了c_cons表(b表),过滤后只有一条记录,再与a表(e_cons_sn叩表)根据cons_id做关联查询,此时a表扫描的记录就会很少。经过线上环境的实际验证,改写后的SQL语句的执行时间不到1s。

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置:首页 > 应用文档 > 汇报材料

copyright@ 2008-2022 001doc.com网站版权所有   

经营许可证编号:宁ICP备2022001085号

本站为文档C2C交易模式,即用户上传的文档直接被用户下载,本站只是中间服务平台,本站所有文档下载所得的收益归上传人(含作者)所有,必要时第一文库网拥有上传用户文档的转载和下载权。第一文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。若文档所含内容侵犯了您的版权或隐私,请立即通知第一文库网,我们立即给予删除!



客服