MYSQL子查询优化.docx

《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 en5_no 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 co

7、unt() 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

10、1虱 * 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。