深入理解 TiDB Placement Rules 功能的应用与优化
1056
2023-04-23
千万数据量下的真实业务场景SQL性能优化
前 言
通过前几期文章的积累,现在我们的理论知识已经极为扎实了,这个时候就可以动手开始sql优化了,sql优化是非常重要,因为即使再好的MySQL设计架构,也扛不住一个频繁查询的垃圾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算法匹配的过程是这样的:
从左边的驱动表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 查询,它会缓存连接过程中用到的字段,这样可以有效减少匹配次数,就像这样:
可以看到,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算法:
它的优化思路主要是减少被驱动表数据的匹配次数, 就是驱动表直接与被驱动表的索引进行匹配,这样就不用和被驱动表的每条记录比较了。
原来的匹配次数为:驱动表行数 * 被驱动表行数,而现在变成了:驱动表行数 * 被驱动表索引的高度,这样就极大的减少了被驱动表的匹配次数,极大的提升了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语句中,left join语句中,订单明细表是通过order_no字段和订单表关联的,此时驱动表order_info的order_no是加了索引的,而被驱动表order_item_detail的order_no字段没有添加索引
(2)看一下查询时间
此时order_info中的数据量为2500万条,而订单明细表 order_item_detail 的数据量是1亿条。
可以看到被驱动表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关联查询的时间
此时我们发现被驱动表order_item_detail的关联字段order_no用上索引后,查询效率提升的非常明显。
进一步优化:去掉join
此时我们为了更好的扩展性,需要将join关联查询给优化掉
(1)看下join优化后的代码:
拆分join,改成单表查询,内存中再组装数据。
(2)看一下优化后的时间
可以看到,将join关联查询优化掉之后,我们除了可以获取到更大的扩展性外,可以发现对查询性能的提升也是非常大的。
被动向主动的转变,监控系统诞生
在sql优化这个例子中,这个问题是由DBA同学发现的,然后DBA同学将问题反馈给了我们,实际在工作中呢,也可能是产品同学发现订单信息查询页面有点慢,然后将问题反馈给我们。
不管是谁发现的,对于我们订单系统的开发人员来说都是非常被动的,因为我们不能及时主动的发现问题,比如某一个接口变慢了,我们不能及时知道,只能等别人反馈给我们,这样被动的发现问题,会在一定程度上扩大问题的影响。
为了解决这个问题,我们建立了一套完善的监控系统,这个监控系统呢,可以添加很多监控面板,比如我们可以添加订单的监控面板,订单监控面板中的核心指标包含:订单核心接口的请求次数、失败次数、TP50、TP99等等。
然后,为了及时发现问题,这个监控系统还集成了报警的功能,说白了就是针对某一个监控指标,我们会设置一个报警规则,比如每天的某一个时间段,在多少分钟内,失败请求超过多少,那么就会报警给对应的开发人员,报警方式呢,会分为2种,分别是报警电话和消息推送(推送给公司内部的办公聊天软件)
报警的时候为了避免开发人员的单点故障,报警接收人一般会添加多个,如果第一个人不接报警电话的话,那么就顺延给第二个人打电话,这样就可以最大程度的及时发现问题了,就可以真正的由被动转为主动了。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。