原因:
某接口调用十分缓慢,通过 Explain 发现是SQL问题
FROM
orderInfo o
LEFT JOIN orderDetail d ON
o.orderCode = d.orderCode
LEFT JOIN user u ON
o.userId = u.userId
LEFT JOIN product p ON
d.productCode = p.productCode
LEFT JOIN adminUser au ON
u.adminId = au.id
LEFT JOIN domain_redemption dr on
dr.orderCode = o.orderCode
可以看到,在Join连接时,出现了BNL查询,BNL出现是因为,JOIN连接时 dr表也就是 domian_redemption 被驱动的表上没出现可用的索引。
个人解决方法:
在对应的连接字段上,既dr的orderCode字段,内表加上索引,再次执行Explain可以发现,BNL已经消失,走了索引,既使用了INLJ的方式
在Mysql的实现中,Nested-Loop Join有3种实现的算法:
Simple Nested-Loop Join:SNLJ,简单嵌套循环连接
Index Nested-Loop Join:INLJ,索引嵌套循环连接
Block Nested-Loop Join:BNLJ,缓存块嵌套循环连接
在选择Join算法时,会有优先级,理论上会优先判断能否使用INLJ、BNLJ:
Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join
Simple Nested-LoopJoin
1.简单嵌套循环连接实际上就是简单粗暴的嵌套循环,如果table1有1万条数据,table2有1万条数据,那么数据比较的次数=1万 * 1万 =1亿次,这种查询效率会非常慢。
2.所以Mysql继续优化,然后衍生出Index Nested-LoopJoin、Block Nested-Loop Join两种NLJ算法。在执行join查询时mysql会根据情况选择两种之一进行join查询。
Index Nested-LoopJoin(减少内层表数据的匹配次数)
1.索引嵌套循环连接是基于索引进行连接的算法,索引是基于内层表的,通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录进行比较, 从而利用索引的查询减少了对内层表的匹配次数,优势极大的提升了 join的性能:
原来的匹配次数 = 外层表行数 * 内层表行数
优化后的匹配次数= 外层表的行数 * 内层表索引的高度
使用场景:只有内层表join的列有索引时,才能用到Index Nested-LoopJoin进行连接。
由于用到索引,如果索引是辅助索引而且返回的数据还包括内层表的其他数据,则会回内层表查询数据,多了一些IO操作。
Block Nested-Loop Join(减少内层表数据的循环次数)
使用join Buffer 优化了 SNLJ
首先变量join_buffer_size用来控制Join Buffer的大小,调大后可以避免多次的内表扫描,从而提高性能。也就是说,当MySQL的Join有使用到Block Nested-Loop Join,那么调大变量join_buffer_size才是有意义的。而前面的Index Nested-Loop Join如果仅使用索引进行Join,那么调大这个变量则毫无意义。
- join_buffer_size的默认值是256K
当join_buffer_size大小不够时,会去清空joinbuffer,再从驱动表读取,与内表进行匹配,因此可以调大joinbuffersize,减少block的读取次数
设置join buffer size 的语句, 单位得是字节,既 如果想设置512K则语句为
SET GLOBAL join_buffer_size = 524288;
在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
INLJ的进一步优化 Batched Key Access Join
Index Nested-Loop Join虽好,但是通过辅助索引进行链接后需要回表,这里需要大量的随机I/O操作。若能优化随机I/O,那么就能极大的提升Join的性能。为此,MySQL 5.6推出了Batched Key Access Join,该算法通过常见的空间换时间,随机I/O转顺序I/O,以此来极大的提升Join的性能。
在使用BKA算法前,先得介绍mrr算法
mrr的优化在于,并不是每次通过辅助索引读取到数据就回表去取记录,而是将其rowid给缓存起来,然后对rowid进行排序后,再去访问记录,这样就能将随机I/O转化为顺序I/O,从而大幅地提升性能。
如果要使用 BKA 优化算法的话,你需要在执行 SQL 语句之前,先设置
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
开启后,再explain一下,可以发现 已经开启 batched key acess join;
文章来源:https://www.toymoban.com/news/detail-449222.html
参考 JOIN 优化文章:
https://www.cnblogs.com/starhu/p/6418842.html
https://www.cnblogs.com/starhu/p/6418833.html
https://www.cnblogs.com/starhu/p/6418824.html文章来源地址https://www.toymoban.com/news/detail-449222.html
到了这里,关于一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!