MySQL查询优化之【小表驱动大表，小数据集驱动大数据集】.pdfVIP

MySQL查询优化之 【⼩表驱动⼤表，⼩数据集驱动⼤数据 集】 Mysql 在进⾏关联表查询时，通常先 ⼀张表A进⾏查询，然后将循环使⽤查询出来的每⼀条记录的关联信息，再来查询另⼀张表B中的记 录，这样查询次数为 1 + COUNT(从表A查询的记录)。所以，为了提⾼查询效率，需要尽量将⼩表作为⾸先查询的表，我们可以关联查询时 ⾸次使⽤的表A为主表，将其它表如表B成为嵌套表。(EXPLAIN时，第⼀个出现的⼀定是⼩表即驱动表，后边出现的是⼤表即被驱动表) ⼀⼀、、IN和和 EXISTS查查询询 根据中的相应步骤在 tb_dept_bigdata 表中插⼊100条数据，在 tb_emp_bigdata 表中插⼊5000条数据。 注 ：100个部门，5000个员⼯。tb_dept_bigdata （⼩表），tb_emp_bigdata （⼤表）。 ①当 B 表的数据集⼩于 A 表数据集时，即A⼤B⼩时，⽤ in 优于 exists。 select * from tb_emp_ bigdata A where A.deptno in (select B.deptno from tb_dept_ bigdata B) B表为tb_dept_bigdata：100条数据，A表tb_emp_bigdata：5000条数据。 ⽤in的查询时间为 ： 将上⾯sql转换成exists ： select *from tb_emp_ bigdata A where e ists(select 1 from tb_dept_ bigdata B where B.deptno=A.deptno) ; ⽤exists的查询时间： 经 ⽐可看到，在B表数据集⼩于A表的时候，即A⼤B⼩时，⽤ in 要优于 exists，当前的数据集并不⼤，所以查询时间相差并不多。 ②当A表的数据集⼩于B表的数据集时，即A⼩B⼤时，⽤ exists 优于 in。 select *from tb_dept_ bigdata A where A.deptno in(select B.deptno from tb_emp_ bigdata B) ; ⽤in的查询时间为 ： 将上⾯sql转换成exists ： select *from tb_dept_ bigdata A where e ists(select 1 from tb_emp_ bigdata B where B.deptno=A.deptno) ; ⽤exists的查询时间： 由于数据量并不是很⼤，因此 ⽐并不是难么的强烈。 附上视频的结论截图： 结论 ： in 后⾯跟的是⼩表，exists 后⾯跟的是⼤表。 简记 ：in+⼩表，exists+⼤表。 ⼆⼆、、JOIN查查询询 A JOIN B，表A是左表，表B⽰右表 ；左表永远是主表，右表永远是嵌套表。 Mysql在版本5.5之前，使⽤了Nest Loop Join算法，在版本5.5之后，采⽤了优化的Block Nested-Loop Join算法。优化的Block Nested-Loop Join⽅法，⾸先将外层循环数据 （即驱动表查询出来的数据）的⼀部分缓存在 j oin buffer （ 决定这个缓冲区的⼤⼩）中， 然后读取被驱动表数据，逐条和buffer中的数据进⾏ ⽐，从⽽减少了查表次数 （⼀次Block嵌套查询，仅仅 被驱动表读取⼀次），这样 便可以提⾼效率 （被驱动表越⼤，驱动表越⼩，效果越明显）。⽽驱动表的选取，是系统根据既定规则进⾏选择的，理论上是选择⼩表作为 驱动表。⽽ straight_j oin 是留给⽤户进⾏驱动表选择的利器，即使⽤ straight_j oin 时永远选择主表为驱动表，当 where 条件和 order by 不在⼩表时，可以尝试使⽤ straight_j oin 选择⼤表驱动⼩表。 Block Nested-Loop Join算法 ： 建⽴ t 1, t2 两个完全相同的表，t 1 表中写⼊ 100 条数据，t2 表中 写⼊ 1000 条数据。 Index Nested-Loop Join （NLJ） (被驱动表有索引的情况选择) 　　- 语句 　　　　- 为了避免Mysql 选择驱动表 于分析的影响，改⽤ straight_j oin 让 My