分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇

发布于 2022-4-15 21:24
浏览
0收藏

前 言

通过前几期文章的积累,现在我们的理论知识已经极为扎实了,这个时候就可以动手开始sql优化了,sql优化是非常重要,因为即使再好的MySQL设计架构,也扛不住一个频繁查询的垃圾sql语句。关于sql的优化,我们也是有一定的原则和先后顺序的,大体的步骤的我们用一张流程图来看一下:分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

总体呢,大概可以分为以下几个步骤:(1)首先,我们得要看下sql语句中是否有join语句,比如内连接查询inner join,外连接查询 left join right join等;因为join语句一般都涉及到跨表查询了,所以首先我们得要为join语句中,负责连接两张表的字段创建索引,这样的话可以利用索引加快两张表关联的速度。(2)接下来,我们会再看一下sql语句中的where语句,我们可以根据当前表中的数据量,以及where语句的过滤条件,预估下查询结果的数据量是否会很大,如果数据量很大的话,查询的速度肯定就会很慢,所以,为了提高sql语句的执行效率,我们得要为where语句中过滤字段单独创建索引。(3)当我们把join语句以及where语句中的字段优化完之后,就可以来看一下其他的一些细节部分,比如sql语句中如果使用了聚合函数,或者对查询的结果进行了排序,那么,一般我们都建议为聚合函数中的字段,以及排序的字段都创建索引,让这些操作利用索引速度更快点。sql优化中不管是对where语句、聚合函数、还是排序操作的优化,优化起来相对而言会简单点,为对应的字段创建合适的索引就可以了,但是,join语句这块的优化涉及到一些比较重要的原理,我们还是有必要来看下的。简单来说,在mysql中使用join语句关联2张表的话,比如执行这条sql:

select * from order_info t1 left join order_item_detail t2 on t1.order_no = t2.order_no

这个时候,join关联查询的过程是什么样子的呢?其实,这个就取决于当前join语句用到的算法了,join语句一共有3种算法,最基础的是Simple nested loop算法,接下来,我们一起来看下。

Simple nested loop算法

Simple nested loop算法,说白了就是一个双重for循环遍历的算法,Simple nested loop算法匹配的过程是这样的:分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

从左边的驱动表order_info中,每取出一条记录都要遍历一遍被驱动表order_item_detail,说白了就是一个双重for循环。

如果驱动表和被驱动表中都有100条数据的话,那么此时就需要匹配 100 * 100 = 10000次,可见效率是非常低的,所以,MySQL并没有选择使用 Simple nested loop 算法,而是使用了优化后的Block nested loop 算法。

Block nested loop 算法

Block nested loop 算法对 Simple nested loop 算法进行了优化,它引入了 join buffer,join buffer 主要用于优化不带索引条件的 join 查询,它会缓存连接过程中用到的字段,这样可以有效减少匹配次数,就像这样:分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

可以看到,Block nested loop的优化思路,是减少被驱动表的匹配次数,它主要是通过一次性缓存驱动表的多条数据,以此来减少被驱动表的匹配次数,从而可以达到提升性能的目的。

需要注意的是,MySQL提供了一个参数join buffer_size,它是用来控制 join buffer 大小的,而MySQL默认的join_buffer_size 是 256K,所以如果驱动表的数据太多的话,默认的join buffer可能一次性放不下全部的数据。

这个时候,join buffer就会采用分段缓存的机制来缓存驱动表的数据,但是这种分段缓存方式的性能,是比一次性缓存全部数据要差一些的。

所以,我们可以通过join_buffer_size参数,适当调大join buffer的大小,使join buffer可以一次性放下驱动表的所有数据,这样可以提升join的性能。

Index nested loop算法

最后还有一种Index nested loop算法: 分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

它的优化思路主要是减少被驱动表数据的匹配次数, 就是驱动表直接与被驱动表的索引进行匹配,这样就不用和被驱动表的每条记录比较了。

原来的匹配次数为:驱动表行数 * 被驱动表行数,而现在变成了:驱动表行数 * 被驱动表索引的高度,这样就极大的减少了被驱动表的匹配次数,极大的提升了join的性能。

如果join关联查询能使用到索引的话,MySQL就会使用Indexnestedloop算法,如果无法使用Indexnestedloop算法,MYSQL默认会使用Blocknestedloop算法。

到底能不能使用join?

好了,我们刚才了解了Simple nested loop 、 Block nested loop、Index nested loop 这三种算法,那么现在可以回答开头的问题了:到底能不能使用join?

其实,如果能用上被驱动表上的索引,说白了就是可以用上 Index nested loop 算法的话,是可以使用 join 的。

而如果使用的是 Block nested loop 算法的话,由于扫描行数和比较次数会比较多,所以会占用大量的系统资源,所以这种情况能不用join就不用join。

我们平常使用explain优化sql的时候,如果 explain 结果中的 Extra 字段,如果包含 ' Using join buffer (Block Nested Loop) ' 的话,这个时候就代表使用了 Block nested loop 算法了。

如果能使用上被驱动表上的索引的话,join还是可以使用的,这个时候基本不会影响性能,那么我们这里为什么要优化掉join呢?

主要由于2个原因,首先后边我们有分库分表的计划,所以为了有更好的扩展性,我们会优化掉join,其次MySQL是专门用来做数据存储的,所以,还是尽量不要把业务相关的逻辑放到MySQL层面来做。

所以基于这2个原因,我们会将单体应用版本的join给优化掉。

join关联查询优化实战

被驱动表order_no列未加索引

(1)join关联查询sql语句分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

可以看到,sql语句中,left join语句中,订单明细表是通过order_no字段和订单表关联的,此时驱动表order_info的order_no是加了索引的,而被驱动表order_item_detail的order_no字段没有添加索引

(2)看一下查询时间

此时order_info中的数据量为2500万条,而订单明细表 order_item_detail 的数据量是1亿条。分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

可以看到被驱动表order_item_detail没使用到索引时,查询效率是非常低下的。

优化:被驱动表order_no列添加索引

(1)为被驱动表添加索引

现在我们为被驱动表order_item_detail的order_no添加索引,添加索引sql如下:

create index inx_item_order_no
    on order_item_detail (order_no);

(2)再次查看join关联查询的时间分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

此时我们发现被驱动表order_item_detail的关联字段order_no用上索引后,查询效率提升的非常明显。

进一步优化:去掉join

此时我们为了更好的扩展性,需要将join关联查询给优化掉

(1)看下join优化后的代码:

拆分join,改成单表查询,内存中再组装数据分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

(2)看一下优化后的时间分库分表实战之六:小试牛刀 — 千万级数据之sql优化上篇-开源基础软件社区

可以看到,将join关联查询优化掉之后,我们除了可以获取到更大的扩展性外,可以发现对查询性能的提升也是非常大的。

被动向主动的转变,监控系统诞生

在sql优化这个例子中,这个问题是由DBA同学发现的,然后DBA同学将问题反馈给了我们,实际在工作中呢,也可能是产品同学发现订单信息查询页面有点慢,然后将问题反馈给我们。

不管是谁发现的,对于我们订单系统的开发人员来说都是非常被动的,因为我们不能及时主动的发现问题,比如某一个接口变慢了,我们不能及时知道,只能等别人反馈给我们,这样被动的发现问题,会在一定程度上扩大问题的影响。

为了解决这个问题,我们建立了一套完善的监控系统,这个监控系统呢,可以添加很多监控面板,比如我们可以添加订单的监控面板,订单监控面板中的核心指标包含:订单核心接口的请求次数、失败次数、TP50、TP99等等。

然后,为了及时发现问题,这个监控系统还集成了报警的功能,说白了就是针对某一个监控指标,我们会设置一个报警规则,比如每天的某一个时间段,在多少分钟内,失败请求超过多少,那么就会报警给对应的开发人员,报警方式呢,会分为2种,分别是报警电话和消息推送(推送给公司内部的办公聊天软件)

报警的时候为了避免开发人员的单点故障,报警接收人一般会添加多个,如果第一个人不接报警电话的话,那么就顺延给第二个人打电话,这样就可以最大程度的及时发现问题了,就可以真正的由被动转为主动了。

本文转载自微信公众号「石杉的架构笔记」

收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐