开发者学堂课程【MySQL 实战进阶：MySQL 查询优化实战】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/83/detail/1308
MySQL 查询优化实战
六、常规的优化策略
1、第一块是对排序的优化，对于排序的优化先看对 order by 的查询分两种情况，首先是第一种 using index，这个是针对查询优化器的两种行为去分的。Using index，MySQL 直接通过索引返回有序记录，不需要额外的排序操作，操作效率较高。第二种叫 using filesort，无法只通过索引获取有序结果集，需要额外的排序，某些特殊情况下，会出现临时表排序的情况。对于 order by 排序优化的目标很简单，就是尽量通过索引来避免额外的排序，减少 CPU 资源的消耗。
2、对于这一方面一般有三个方法去做，第一个是 where 条件和 order by 使用相同的索引；第二个是 order by 的顺序和索引顺序尽量相同；最后一个也比较重要，就是 order by 的字段最好同为升序或降序。因为 B+Tree 索引有多个字段的话，不同的字段之间，如果是不同升或者不同降，本质上就是 B+Tree 索引的排序是不可能出现不同的字段，前面是升序，后面是降序这样的一个排序情况。
注:当 where 条件中的过滤字段为覆盖索引的前缀列，而order by字段是第二个索引列时，只有 where 条件是 const 匹配时，才可以通过索引消除护序，而between…and 或>?、这种 range 匹配都无法避免 filesort 操作
2、当没有办法避免排序操作时，优化的思路就是让排序的操作更快。排序的操作更快就提到了排序算法，包括两次扫描算法和一次扫描算法。它们本质的区别是两次扫描算法分阶段去完成，它的目的是为了更加节约内存空间。从本质上看，一次扫描算法效率更高，但是它会消耗一些内存。这里需要注意到的一点就是需要排序的字段总长度越小，越趋向于第二种扫描算法，
MySQL通过 max_length_for_sort_data 参数的值来进行参考选择。这样也可以给我们一些有帮助的建议，对于 select 语句提升排序的操作，一般可行的优化策略主要是三点。
第一点适当调大 max_length_for_sort_data 这个参数的值，这个参数的目的是为了让优化器更倾向于选择第二种扫描算法。刚才也提到了关于排序的两种算法，想要提排序训操作的话，一个核心的思想就是通过空间换时间，一次扫描算法虽然会消耗更多的内存空间，但是它排序的速度会更快。第二点，只使用必要的字段，不要使用 select*的写法，因为目的是为了让字段排序的总长度不要过长。第三点可以适当加大sort_buffer_size 这个参数的值，避免磁盘排序的出现。
4、第二个是对于 subquery 子查询，对于子查询会在大部分情况下都会利用到临时表，一般情况下需要尽量避免；可以使用效率更高的 join 查询来代替。对于临时表一般的优化策略可以做等价改写，这种策略在 MySQL 查询优化器里面有一个专业的词叫做反嵌套。在 MySQL 里面查询优化器也可以做一些简单的子查询的反嵌套的操作，但是在绝大部分情况下，还是需要人为的干预。看下面的 SQL 语句用 not in 的方式，后面是在一个子查询里面执行 select 语句。
对于这个 SQL 语句思路很清晰，直接把 not in 该写成 left join。
在 MySQL 里面，join 的效率比子查询的效率还是高一些的。
5、第三种是 limit，分页查询的优化。分页查询本质上就是将过多的结果在有限的界面上分好多页来显示，它的实质是每次查询只返回有限行，翻页一次执行一次。对于分页查询的目标，第一个是消除排序，分页查询当中一般 limit 的操作，在绝大部分情况下都会跟 order by 排序，是跟他关联的，所以在这种场景下第一步需要消除排序的。
第二点是避免扫描到大量不需要的记录，比如说有一个 SQL 场景，
film_id 是主键，这里列的 SQL语句 limit 10000，20，对于这个 SQL 语句，通常情况下它自己执行的话，需要扫描到 MySQL 里面前10020条数据，真正返回客户端的时候把前10001之前的数据全部去掉。为什么要优化分页查询？就是在做分页的时候，这种情况前面的一万条记录会造成额外的数据扫描的代价。
6、对于分页查询的策略，第一个提到了覆盖索引，
在某些比较特殊的场景，就是 where 条件和 select 返回的字段都特别少的情况下，就可以直接通过索引覆盖的方式，可以直接从索引当中获取想要的数据比如说像本次的 SQL，order by title，前面是select film_id，description。
对于这个 SQL 只要给 title 和description 建一个复合索引，film_id 本身是做的示例的主键。建了这个索引之后可以看到这个 sql 的 select，跟后面排序的字段都被索引所覆盖。通过覆盖索引最大的一个好处是需要的记录可以直接从索引中获取，效率最高；仅适合查询字段较少的情况。
7、下面是第二种优化策略，通过改写的这种方式做优化。
这个本身上也是通过覆盖索引的原则，通过这种 join 的方式去改写的前提，可以看到在 join 后面加了一个子查询。先加这个子查询是因为这个里面是一个 select 语句，其实是把分页的查询分为两步去做，第一步就是通过里面的查询先取出所需要的20条记录的主键 id；第二步就是通过跟主表做关联，然后再取到想要的20条数据。本质上也是通过覆盖索引的方式做优化，对于查询的字段，一般情况下不会像场景一，它查询的、所返回的字段特别少。通常情况下 where 条件 select 返回字段可能都会有很多，不可能给所有的字段都建复合索引，这样的情况下，通常优化改写的策略就是通过 join 改写的方式，先通过覆盖索引取出主键 ID，然后再通过关联。
8、下面的场景是 or 和 and 条件的优化，首先看一下 or 和and 的区别。or 是并集，and 是交集，在 MySQL 里面需要注意一点，交集的查询条件。这里可以看到 condition a 和 condition b，对于交集的话就是其中有一个条件，可以利用到选择性比较好的索引，sql语句本身效率就没有太大的问题。
它和 or 最大的一个区别是or 条件在处理的时候会把它视为 UNION，所以 or 可以看到condition a 和 condition b。在一个查询语句当中通过 or 把他去联系起来，相对于条件 a 和条件 b 是两个 SQL 语句看待，所以对于 or 的话 condition a 和 condition b他们都需要通过对应的索引做优化。而不是像 and 一样，有两个条件，只要有一个条件有过滤性比较好的索引就可以了。
①SELECT 优化-or/and condition
·and 结果集为关键字前后过滤结果的交集·or结果集为关键字前后分别查询的并集
·and 条件可以在前一个条件过滤基础上过滤
·or 条件被处理为 UNION，相当于两个单独条件的查询·复合索引对于or条件相当于一个单列索引
②处理策略:
√and 子句多个条件中拥有一个过滤性较高的索引即可
√or 条件前后字段均要创建索引
√为最常用的and组合条件创建复合索引
9、下面看关于 join 的优化，在 MySQL 里面，先看一个比较典型的算法，Nested-Loop Join 的算法。对于这个算法简单点来说，就是一个循环，t1、t2、t3 这三张表去做 join 的话，t1 表首先去扫描匹配到的行，通过 t1 当中的每一行，再根据 t1 和 t2 的关联条件，再去扫描t2 当中匹配到的每一行，在 t2 跟 t3 的时候也是这样。
Join 的本质就是嵌套循环的过程，对于嵌套循环需要注意两点，第一点，优化的核心跟关联的字段要添加索引，添加索引的目的是为了让每一层的内部循环仅仅获取需要关心的数据就可以了。有一个隐身算法叫 Block Nested-Loop，看下图
下面是 Block Nested-Loop，在MySQL里面有一个特性叫 join buffer。
简单点说，当两张表的关联不能通过索引去做关联条件的匹配的话，在做join的时候就会产生 join buffer。当 SQL 的 join 语句他的执行计划里面出现 Block Nested-Loop 的时候，在这样情况下就需要花心思去看，看关联条件是不是没有索引，或者因为一些其他的原因而导致关联的匹配没有通过索引。因为当 sql 语句出现 的时候，SQL 的效率会大幅下降。第二点是小表驱动原则，就是让最外层的结果结尽量小，这个目的是为了减少循环的次数。
10、关联字段索引的必要性，看如下案例
对于特别简单的select count * 的语句，语句非常简单，就是两张表的一个 join 关联。下面看 SQL 语句的执行计划的时候，已经出现了 using join buffer，就是前面所说的 Block Nested Loop，可以看到通过 b 表去访问 a表的时候，可以看到rows 是127042，也就是整个访问的过程代价非常大。对于这种产品优化的策略也特别简单，
直接给 a 表添加一个索引，给关联的条件添加一个索引。后面可以看到通过 b 表再去访问 a表的时候，执行计划里面的 key 就使用到了刚才添加的索引，rows 也从127042下降到了125。执行效率前面接近两分钟，优化后是0.31秒。
11、第二个是小表驱动原则，
首先做了一个事情是忽略 b 表的索引，使 b 表作为驱动表。先看执行计划，这里是 select count * 的语句，加了一个忽略索引的 hence。这样做的目的是为了产生下面的行为，在执行计划里面是通过 b 表做驱动表，b 表做驱动表的时候可以看 rows 是4000×125，执行的成本是0.31秒。可以通过添加 hence 的方式控制优化器的行为，
刚才是忽略了b 表的索引，这里在忽略 a 表的索引，
这个时候就会发现 a 表成了驱动表，b 表成了被驱动。这个时候再看rows，它变成了127042×4。这有一个很本质的区别就是，后面这一种优化器扫描的方式，比前面的方式成本是提升了不少的。虽然在这两个 SQL 语句做关联的时候，不管是通过 a 表还是 b 表去做驱动，最后在通过关联的时候，其实都是通过了索引，但是因为他们驱动表的大小问题导致最终 rows 行数的代价是不一样的，后面这一种写法产生的时间是0.53秒，这比前面差不多慢了一倍。
12、最后一种是关于 insert 插入的优化，关于插入的优化策略主要是两个，第一个是为了减少网络的消耗，主要的策略是减少跟 MySQL SQl 交互的次数。有些时候可能会有些开发人员会提交大批量的插入语句，如果拷十万条，每一条都是 insert into 这样的语句，
对于这种单条的插入语句可以做一些适当的合并。在下面一条 insert into 语句后面可以接多个值，
对刚才所说的场景做一些适当的插入的合并，这种提升的效果一般是比较明显的。因为它减少了 SQL端交互的次数，主要是从网络的开销上面做的优化。第二种策略可以通过文本装载的方式，通过 LOAD DATA INFILE 句式，通过文本装载数据的方式，通常情况下，通过 load data in fact 方式比直接插入 insert 语句快20倍。
七、总结
1、本课程对于各个优化的环节基本上讲完了，最后做一个小结。关于 MySQL 查询优化第一块，优化的目的是为了让资源发挥价值，SQL 和索引是调优的关键，往往可以起到四两拨千斤的效果。可能简单的加一个索引或一个 SQL 语句，做一个简单的改写，可以让整个系统大幅提升它的性能。
2、第二块关于优化流程和思路上，第一个需要充分了解数据库里的核心指标，并且需要构建相对完备的监控体系，这是优化工作的前提。因为这样做的目的是为了获取整个数据库性能的一些数据。第二个是 SQL 优化的原则是减少数据的访问及计算，常用的优化方法主要是调整索引、改写 SQL 以及干预执行计划。
3、第三块是关于 MySQL 内部核心的原理，以及相应的行为相关的内容，第一个innodb 的表示典型的 IOT，数据本身是 B+Tree 索引的叶结点，第二个是扫描二级索引可以直接获取 select 查询数据，或者是返回主键ID，再获取真正的数据。最后，关于优化器需要注意的一点是优化器是数据库的大脑，需要做优化工作的话，要先了解优化器，并且在实际的调优过程中要观测以及在适当的时候干预 MySQL 的行为。关于 MySQL 查询优化的课程到此结束。
