Mysql高级调优篇——第三章：Sql实战调优场景剖析（上）

        上几个章节我们讲述了很多硬核的知识，那本章开始我们正式进入Sql实战过程！！

1、热身Case

        回顾上节讲的Explain知识点，我们简单做个回顾，来个热身的Case：

        看下面的Sql执行是如何的，请列出步骤！

        很明显先看id，id依次递增，谁最大谁先执行：

        所以先执行id=4，执行t2表：select name, id from t2

        再执行id=3，t1表的查询：select id, name from t1 where other column = ''

        再执行id=2，t3表的查询：select id from t3

        再执行id=1，这个表是由id=3这个表衍生的表，查询的结果来自id=3的结果，且table被标记为derived3，select_type为primary表示外层查询。

        最后执行id=NULL，代表从union的临时表读取行的阶段，table为<union1，4>表示用id=1和id=4的select结果进行union操作！

2、索引单表优化案例剖析

        表的结构在我之前的一篇文章里，附上链接可以自行去Copy：
Mysql高级调优篇表补充——建表SQLhttps://blog.csdn.net/qq_31821733/article/details/120886389?spm=1001.2014.3001.5501

        新建article表：

2.1、查询category_id为1且comments大于1的情况下，views最多的article_id

        Sql：select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

        执行计划后，会发现多一个列filtered，这个是5.7版本后添加的，意思是：

        指返回结果的行占需要读到的行(rows列的值)的百分比，比如我返回了10行，但是我需要读1000行，这个比例就是10%，filtered的数值其实越高，表示通过索引直接返回的行很多，数值较低时，一般出现在type=ALL或者index的情况。

         分析下这个执行计划，type=ALL全表扫，而且产生了filesort，功能完成了，但是作为开发一定要考虑到你的sql性能，这个是你的谈资！

        看下索引：目前只有一个主键id默认主键索引。

        因为我们sql的where条件后有三个字段，首先想到加的复合索引：

        我也不知道对不对，但是先这么尝试，然后看看执行计划：

        发现走了索引了，但是发现走到了filesort，这样还不行；

        说明这个索引不起作用吗？那假设我们把sql调整为comments = 1再看看执行计划

        很明显，filesort没有了，type一下从range变成了ref，这个情况是最好的，但是合不合理？业务都变了。

         分析下：我们建立的索引是ccv，上面的sql是范围查comments，而下面的是指定常量const，肯定是常量更精确，所以在Mysql中，索引中出现了范围查找，后面就失效，comments出现了范围，索引在找的时候，发现comments无法直接定位到，影响了order by views的索引排序，进而出现了filesort。不好意思，你让我去2楼找进3口的入口，我一个一个找，这个时候我无法走到索引，自然找不到我就不能按照你的索引顺序走到3楼了。Mysql做不到时，内部自己产生了个排序，出现了filesort。总结一句话：

        范围索引全失效

        结论是：type变成了range，这是可以忍受的，但是Extra里出现了filesort是无法接受的，但是我们建立了索引为什么没有用，这是因为按照Mysql的BTREE工作原理，先排序category_id，如果遇到相同的，再排序comments，如果遇到相同的，再排序views，当comments位置处于联合（复合）索引的中间位置时，Mysql无法对范围（range）后面的字段进行索引排序，从而后面的字段索引失效！

        刚刚建立的索引对于当前的需求，并不是最合适，那么怎么优化呢？

        我们先删除刚刚的索引：

        既然范围之后索引失效，那么我们能不能绕过去？直接新建category_id, views的复合索引。执行计划告诉我们，这个索引加的很合适！

 3、索引两表优化案例剖析

        两表的情况在开发中也很常见，这里我新建book表和class表：

        这两个表无需关心含义，我们看具体Sql：

        Sql1：select  * from class left join book on class.card = book.card;

        看到这个sql应该立马想到我之前画的图，很明显取的是class的全部，加上book不满足的部分；

         执行计划跑下看：明显这个type为ALL，索引也没有加

        问题来了，索引加哪边？是加class.card还是book.card？

        我们都试试，先添加右边book表的索引：

        执行计划走下：book的很明显的改变，type变成了ref

         此时我把book表的索引删掉，而建立class左表的索引看看执行计划：明显，加了class表的索引后，发现type是index，并且rows20行记录，全索引扫描，性能不会有刚刚的好！

        同样的sql，同样的索引列，左连接的时候，加的索引所在的表不同，效果不同；

        总结一句话：左连接相反加！

        结论：上面出现效果不同，这个是由左连接的特性决定的，left join 条件用于确定如何从右边搜索行，而左边一定是都有的； 左边全有，确定核心的点在于确定如何从右表中搜索数据行，右边是关键点，要加索引！所以左连接索引加在右表上，同理，右连接也是相反加！

        有没有人好奇，如果两个索引都建呢会是什么样？我们尝试下加上看看：

        现在book和class表上的card字段都加了索引，效果比上面两个都好！

        在具体工作中我们还是要具体分析！

4、索引三表优化案例剖析

        讲完两个表，我们讲下三表怎么优化

        在基于上面两个表book和class的前提下，我们新增加一个表，叫做phone表，字段也差不多：

         把book表和class表的索引都清除掉；

        假设我有这么一个sql：

        select * from class left join book on class.card = book.card left join phone on book.card = phone.card; 看下结果，两表的连接基础打牢固，其实三表的是一样的。

         此时三个表都没有索引：我们走下执行计划后发现，Extra字段多了Using join buffer；首先join buffer意思是使用了连接缓存，在5.7之后，Mysql对表和表之间的连接，做了优化缓存，实际上在A left join B的过程，Mysql会更在意B的表往A中相同的部分，所以类似一个for循环，最外层for A，内层是for B，找到B中的每一行满足A行的记录，因为是要A的全部，所以最外层一定是A，然后合并行，最后输出；而在3表中，等于3个for循环。

        其中其实发现有个Block Nested-Loop Join——BNL算法，这个算法将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。所以最外层的表是class，先for整个class，然后放在join buffer里，接下来循环内表的时候，直接取buffer的行去比对，减少对磁盘的IO。

        但是整个type=ALL，rows都是20，全表扫，这是我们无法接受的。

         那么三张表怎么加索引呢？可以想想，左连接建右表上，那么这个是不是说class左表，建立索引在book和phone上？试试！

        走下执行计划看看：很明显，改善很多!

        那么很明显这个原则也成立，总结下：

        尽可能减少join语句中的NestedLoop循环总次数，永远用小结果集驱动大的结果集，这里的例子，就是左表尽量数据小于右表，外层for的次数就减少了，IO次数也会降低。

        优先优化NestedLoop的内层循环；

        保证join语句中被驱动表上的字段已经被索引；

        当无法保证被驱动表的join字段被索引且内存资源充足的前提下，不要吝啬JoinBuffer的设置值。JoinBuffer在my.cnf中，由DBA运维着。

        其实你可以试试，如果class表加了索引，效果会比右连接稍微好点，哈哈

         但是这个不一定可行，具体数据场景具体分析！

        本章讲了优化索引实例，下一章节继续索引其他场景实战！！