这几天同事写报表,sql语句如下

select * from `sail_marketing`.`mk_coupon_log` a left join `cp0`.`coupon` c on c.code_id = a.coupon_code;

查询出来的结果花了60多秒

数据背景

mk_coupon_log表数据 9368

coupon表数据37735

mk_coupon_log表的coupon_code字段有索引

分析过程

分析原始sql语句

select * from `sail_marketing`.`mk_coupon_log` a left join `cp0`.`coupon` c on c.code_id = a.coupon_code;

type都是ALL,即全表查询,没有用到索引,其中Extra列出现 Using join buffer (Block Nested Loop),从字面意思理解用到了缓存跟循环

其中a表也没用到coupon_code列的索引

一般情况下,第一行称为驱动表,第二行称为被驱动表,从上图可知,a表示驱动表,c表示被驱动表

果断给coupon表的code_id字段加上索引

增加索引之后查询时间0.136秒,分析其sql语句

select * from `sail_marketing`.`mk_coupon_log` a left join `cp0`.`coupon` c on c.code_id = a.coupon_code;

从上图可知,a表驱动c表,c表用到刚加的索引,以及ref到a表的索引,查询效率上急速提高

试试反向left join,查询结果为1.362秒,分析sql

explain select * from `cp0`.`coupon` c  left join `sail_marketing`.`mk_coupon_log` a on c.code_id = a.coupon_code

c表驱动a表,用到了a表的索引,也ref到c表的索引,由上可知,都有索引的情况下说明小表驱动大表效率更高

删掉coupon表刚加的索引,还原到原始状况

将left去掉,使用 join连接两个表

select * from `sail_marketing`.`mk_coupon_log` a join `cp0`.`coupon` c on a.coupon_code =c.code_id

查询结果为0.138秒,分析sql语句

select * from `sail_marketing`.`mk_coupon_log` a join `cp0`.`coupon` c on a.coupon_code =c.code_id

去掉left后,发现跟初始的语句explain解析结果不一样了,c表变成驱动表,a表变成被驱动表,查询效率上也急速提升

select * from `sail_marketing`.`mk_coupon_log` a, `cp0`.`coupon` c where c.code_id = a.coupon_code

语句跟上述语句效果一样

从上述结果来看,

1:如果使用left join语句,则left join 前的表设置为驱动表,left join 后的表设置为被驱动表,被驱动表如果没加索引,效率会非常低,Extra列出现using join buffer(block Nested-loop)

2:使用join on,where,inner join on等连接表语句,则mysql会自动优化, 将加索引的表设置为被驱动表,未加索引的表设为驱动表

3:两个表都存在索引的情况下,小表驱动大表查询效果更好

初始sql语句执行效率超过60秒,初步判断原因在于using join buffer(Block nested loop), 查询多方资料,从insidemysql公众号上查到关于join的分析

join原理参考下图,R是驱动表,S是被驱动表

上图表示R表joinS表, 其中Fetch阶段是指当内表(被驱动表)关联的列是辅助索引时,但是需要访问表中的数据,那么这时就需要再访问主键索引才能得到数据的过程,不论表的存储引擎是InnoDB存储引擎还是MyISAM,这都是无法避免的,只是MyISAM的回表速度要快点,因为其辅助索引存放的就是指向记录的指针,而InnoDB存储引擎是索引组织表,需要再次通过索引查找才能定位数据。

接着计算两张表Join的成本,这里有下列几种概念:

外表(驱动表)的扫描次数,记为O。通常外表的扫描次数都是1,即Join时扫描一次驱动表的数据即可

内表(被驱动表)的扫描次数,记为I。根据不同Join算法,内表的扫描次数不同

读取表的记录数,记为R。根据不同Join算法,读取记录的数量可能不同

Join的比较次数,记为M。根据不同Join算法,比较次数不同

回表的读取记录的数,记为F。若Join的是辅助索引,可能需要回表取得最终的数据

通常来说join的实现由以下三种

1:simple nested-loop join(SNLJ)

算法相当简单,即驱动表(外表r)中的每一条记录与被驱动表(内表s)的记录进行判断

从驱动表中取出R1匹配S表所有列,然后R2,R3,直到将R表中的所有数据匹配完,然后合并数据

可以看到这种方式效率是非常低的,以上述a表数据8000条,c表数据30000条计算,则S*R =2亿多次,开销统计如下

2: index Nested-loop join(INLJ)

我们通常建议在被驱动表建索引,原因就是使用到了index Nested-loop join原理

驱动表中的每条记录通过被驱动表的索引进行访问,因为索引查询的成本是比较固定的,故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)

这种算法在链接查询的时候,驱动表会根据关联字段的索引进行查找,当在索引上找到了符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择,MySQL优化器一般情况下是会选择记录数少的作为驱动表,但是当SQL特别复杂的时候不排除会出现错误选择。

上表Smatch表示通过索引找到匹配的记录数量。同时可以发现,通过索引可以大幅降低内表(被驱动表)的Join的比较次数,每次比较1条外表的记录,其实就是一次indexlookup(索引查找),而每次index lookup的成本就是树的高度,即IndexHeight。

根据开始的示例,可知如果被驱动表加索引,效率是非常高的,但是开始的示例中加的索引不是主键索引,所以还得进行一次回表查询,辅助索引先查主键索引,然后根据主键索引再查询数据结果, 故如果被驱动表的索引是主键索引,则效率会更高,上述我未尝试,有兴趣的可以自己尝试下

3:block Nested-loop join

通常情况下,mysql会先判断被驱动表是否有索引,如果没有索引的话也肯定不是simple Nested-loop join方式,毕竟代价太大,效率太低,故出现第三种方式

Simple Nested-Loop Join算法的缺点在于其对于内表的扫描次数太多,从而导致扫描的记录太过庞大。Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数,仅需扫描内表一次。

Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程,也就是join buffer,使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中,然后批量与非驱动表进行比较,这也来实现的话,可以将多次比较合并到一次,降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话,就不会出现多次访问非驱动表的情况了,也只有这种情况下才会访问join buffer。

在MySQL当中,我们可以通过参数join_buffer_size来设置join buffer的值,然后再进行操作。默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

整体效率比较 INLJ > BNLJ > SNLJ

上述分析可知,原始的sql用到了Block Nested-loop 原理,虽然比Simple Nested-loop效率高,但整体而言还是很慢的,