一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)

这篇具有很好参考价值的文章主要介绍了一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

原因:
某接口调用十分缓慢,通过 Explain 发现是SQL问题
一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)

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 调优 ,join 的调优,索引优化(Block Nested Loop)

在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查询。
一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)

Index Nested-LoopJoin(减少内层表数据的匹配次数)

1.索引嵌套循环连接是基于索引进行连接的算法,索引是基于内层表的,通过外层表匹配条件直接与内层表索引进行匹配,避免和内层表的每条记录进行比较, 从而利用索引的查询减少了对内层表的匹配次数,优势极大的提升了 join的性能:

原来的匹配次数 = 外层表行数 * 内层表行数
优化后的匹配次数= 外层表的行数 * 内层表索引的高度

一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)

使用场景:只有内层表join的列有索引时,才能用到Index Nested-LoopJoin进行连接。
由于用到索引,如果索引是辅助索引而且返回的数据还包括内层表的其他数据,则会回内层表查询数据,多了一些IO操作。

Block Nested-Loop Join(减少内层表数据的循环次数)

使用join Buffer 优化了 SNLJ
一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)
首先变量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;
一次线上mysql 调优 ,join 的调优,索引优化(Block Nested Loop)

参考 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模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 记一次线上kafka造成的事故

    背景:所有的原始数据均存储在mysql,mysql会通过binlog将数据同步至kafka消息队列,但是有人将mysql中的数据进行删除(大概有2、3年的数据),被删除的数据也通过binlog被同步至消息队列里导致大量消息积压,且该消息队列只有3个分区,最多3个线程消费,消费方即使过滤也远

    2024年02月13日
    浏览(44)
  • 通过一次线上问题,讲下Ribbon重试机制

    前段时间,产品经理在线上验证产品功能的时候,发现某个功能不符合需求预期,后来测试验证发现是服务端的一个接口大概率偶现超时,前端做了兜底处理,所以对线上用户么有太大影响。 由于服务端的接口偶现超时,并且网关设置了30s超时熔断,所以前端请求就直接报错

    2024年02月15日
    浏览(58)
  • 记一次线上问题 → 偶尔的热情真的难顶呀!

    昨晚和媳妇坐在沙发上刷视频 我用手肘轻轻推了推媳妇:你看这渣男,玩完女的都不娶人家 媳妇:哎哟我天,哎呀妈,我这也没好哪去呀 我疑惑的看向媳妇:啥意思啊 媳妇看向自己的手机:啥意思啊,特么有些人,娶完了也不玩呀 我负责的系统需要同步上游系统的数据 同

    2024年02月03日
    浏览(42)
  • 得物-Golang-记一次线上服务的内存泄露排查

    在风和日丽的一天,本人正看着需求、敲着代码,展望美好的未来。突然收到一条内存使用率过高的告警。 告警的这个项目,老代码是python的,最近一直在go化。随着go化率不断上升,发现内存的RSS使用率越飙越高。最终达到容器内存限制后,进程会自动重启。RSS如下图所示

    2024年02月04日
    浏览(58)
  • 记一次线上kafka重复消费的问题解决及思考

    线上ELK日志发现kafka消费者消费到重复消息 由于生产方本身就发送了重复的消息,导致消费到重复消息 消费方采用的是循环poll的模式,具体是在多线程分租户去批量处理的消息

    2024年02月10日
    浏览(50)
  • 【记一次线上事故的排查思路】- CPU飙升问题排查

    由于项目排期较紧,临时从其他组调来三个开发资源帮我一起做项目,难免上线的时候大家的需求一块上线。 问题来了,上线三天后,线上CPU总是莫名奇妙的突然飙升,飙升后CPU并未降下来,而是一直处在高点。 由于是线上导致的问题,CPU超限后,会自动重启项目,未能保

    2024年01月23日
    浏览(49)
  • MySQL 服务器的调优策略

    点击上方 ↑ “追梦 Java”关注,一起追梦! 在工作中,我们发现慢查询一般有2个途径,一个是被动的,一个是主动的。被动的是当业务人员反馈某个查询界面响应的时间特别长,你才去处理。主动的是通过通过分析慢查询日志来主动发现执行效率缓慢的 sql 语句,或者通过

    2024年02月15日
    浏览(39)
  • 记一次线上bug排查-----SpringCloud Gateway组件 请求头accept-encoding导致响应结果乱码

           基于公司的业务需求,在SpringCloud Gateway组件的基础上,写了一个转发服务,测试开发阶段运行正常,并实现初步使用。但三个月后,PostMan请求接口,返回异常,经排查,从日志中获取到转发响应的结果为乱码:        跟踪日志: 转发到目标接口,响应结果已乱码

    2024年02月04日
    浏览(49)
  • mysql的两张表left join 进行关联后,索引进行优化案例

    1.表1没加索引  2.表2没加索引 3.查看索引 1.表1添加索引   2.表2添加索引   3.查看  

    2024年02月12日
    浏览(45)
  • MSQL系列(十三) Mysql实战-left/right/inner join 使用详解及索引优化

    Mysql实战-left/right/inner join 使用详解及索引优化 前面我们讲解了B+Tree的索引结构,也详细讲解下Join的底层驱动表 选择原理,今天我们来了解一下为什么会出现内连接外连接,两种连接方式,另外实战一下内连接和几种最常用的join语法 Left join 左表 left join 右表查询 right join 左

    2024年02月05日
    浏览(48)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包