分库分表实战之六：小试牛刀 — 千万级数据之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种，分别是报警电话和消息推送(推送给公司内部的办公聊天软件)

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

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