「 关注“石杉的架构笔记”，大厂架构经验倾囊相授 」
帮您解忧，儒猿特别打造 “跳槽训练专栏系列”
通过前几期文章的积累，现在我们的理论知识已经极为扎实了，这个时候就可以动手开始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算法，说白了就是一个双重for循环遍历的算法，Simple nested loop算法匹配的过程是这样的：
从左边的驱动表order_info中，每取出一条记录都要遍历一遍被驱动表order_item_detail，说白了就是一个双重for循环。
如果驱动表和被驱动表中都有100条数据的话，那么此时就需要匹配 100 * 100 = 10000次，可见效率是非常低的，所以，
MySQL并没有选择使用 Simple 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算法：
它的优化思路
主要是减少被驱动表数据的匹配次数， 就是驱动表直接与被驱动表的索引进行匹配，这样就不用和被驱动表的每条记录比较了。
原来的匹配次数为：驱动表行数 * 被驱动表行数，而现在变成了：驱动表行数 * 被驱动表索引的高度，这样就极大的减少了被驱动表的匹配次数，极大的提升了join的性能。
如果join关联查询能使用到索引的话，MySQL就会使用Index
nested
loop
算法，如果无法使用Index
nested
loop
算法，MYSQL默认会使用Block
nested
loop
算法。
好了，我们刚才了解了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给优化掉。
被驱动表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_item_detail的order_no添加索引，添加索引sql如下：
create index inx_item_order_no on order_item_detail (order_no);
此时我们发现被驱动表order_item_detail的关联字段order_no用上索引后，查询效率提升的非常明显。
此时我们为了更好的扩展性，需要将join关联查询给优化掉
（1）看下join优化后的代码：
拆分join,改成单表查询，内存中再组装数据
（2）看一下优化后的时间
可以看到，将join关联查询优化掉之后，我们除了可以获取到更大的扩展性外，可以发现对查询性能的提升也是非常大的。
在sql优化这个例子中，这个问题是由DBA同学发现的，然后DBA同学将问题反馈给了我们，实际在工作中呢，也可能是产品同学发现订单信息查询页面有点慢，然后将问题反馈给我们。
不管是谁发现的，对于我们订单系统的开发人员来说都是非常被动的，因为我们不能及时主动的发现问题，比如某一个接口变慢了，我们不能及时知道，只能等别人反馈给我们，这样被动的发现问题，会在一定程度上扩大问题的影响。
为了解决这个问题，我们建立了一套完善的监控系统，这个监控系统呢，可以添加很多监控面板，比如我们可以添加订单的监控面板，订单监控面板中的核心指标包含：订单核心接口的请求次数、失败次数、TP50、TP99等等。
B站视频链接：https://sourl.cn/JWapeE
下期连载预告：“分库分表实战（第7期）
：抽丝剥茧 — 千万级数据之sql优化下篇
”
------------- END -------------
为了程序员能够顶住面试现场的连环炮，将生产上线的分库分表经验用到自己项目中去。顺利跳槽加薪，实现人生跨越！儒猿特制了“海量数据场景下的分库分表生产实践”专栏。有跳槽计划或分库分表技术感兴趣的同学一定要点击下方海报了解哈！
