开发者学堂课程【MySQL 实战进阶：MySQL 查询优化实战】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/83/detail/1308
MySQL 查询优化实战
内容介绍：
一、课程大纲
二、优化的目的与目标
三、优化流程及思路
四、原理剖析
五、MySQL 的行为
六、常规的优化策略
七、总结
一、课程大纲
本课程主要讲解 MySQL 查询优化，课程大纲主要分为以下几个模块，第一模块是优化目的与目标，第二模块是优化流程及思路，第三、四模块会讲一些语言类的内容，包括原理剖析和 MySQL 的行为，最后一章节会讲解一些常规场景的优化策略。
二、优化的目的与目标
1、首先来看一下优化的目的与目标，优化的目的是为了提高资源利用率，就是为了让资源能够充分发挥它的价值。在常见的场景下，比如说一台服务器的四大资源，一个是 CPU、CPU 内存、网络和磁盘，就是这四个资源，一旦当中的某一个出现瓶颈的话，整个服务器再继续提供服务的能力就会变得较差。所以做优化本质的目的是为了让资源充分发挥它的价值。
优化的目的还包括避免短版效应、提高系统吞吐量，最终的一个目的是为了能够同时满足更多用户的在线需求。
2、MySQL 的优化目标主要有三点。第一点是减少磁盘 IO，对于磁盘 IO 在 MySQL 数据库中主要来自于全表扫描，或者说一些其它的会扫描大量数据块的场景，然后就是日志以及数据块写入的情况，还有磁盘临时表，主要是来自于这几个方面。
第二点是减少网络带宽，对于网络带宽第一个是在有时 sql 查询的时候会返回太多的数据，第二个是像插入的场景，会跟 MySQL 的客户端有过多的次数交互。
第三点是降低 CPU 消耗，对于 CPU 的消耗主要是两块，第一个是 MySQL 本身的逻辑读，第二个是一些额外的计算操作，比如说一些排序分组，像 order by，group by，然后就是一些聚合函数，像 max，min，sum 等等。
3、看下图所示金字塔，
从下往上列了四个查询优化手段，从下往上依次是 sql 所有的优化，第二块是库表结构的优化，再往上是系统的配置，然后是硬件。对于单个 MySQL 实例来说，从下往上成本是逐步提升的，但效果往往越来越差。从本质上来说 sql 之所以要调优，往往不需要花费什么成本，一般都可以取到很好的效果。
三、优化流程及思路
1、下面看 sql 优化的常规流程及思路。需要关注的指标第一个是 CPU 使用率，对于 CPU 使用率一般认为它是 sql 查询的关键资源指标。CPU 的消耗主要来自于数据扫描和一些显示计算。第二个是 IOPS，也是衡量磁盘设备的一个指标。IOPS 指的是每秒 io 请求次数，对于 IOPS 一般认为它是物理读写的关键资源指标。
2、第三块是 OPS 以及 TPS，对于它会认为这个是 MySQL 的吞吐量，对于吞吐量在有些时候也可以认为它可以反映出整个业务系统的业务压力。第四块是会话数以及活跃会话数，会话以及活跃会话是跟 station 相关的，对于会话一般出现问题可能，比如说应用配置存在一个问题，没有合理使用到连接词的情况，或者说 sql 的执行效率比较差。像这种情况会发现数据库的 sql 端可能会加大量的会话，甚至会加大量的活跃会话。
3、第五块是关于 Innodb 的核心指标逻辑读以及物理读，对于这两块主要用于反应整体查询效率的引擎指标。最后一块关于临时表，临时表一般情况下是导致 sql 执行效率下降的特殊行为，它本质上是 sql 执行过程中的一个行为。当然也不排除有一些其他的手段，也有可能会引起 MySQL 系统里面会产生临时表。
4、下面看一下监控，既然要关注的指标有这么多，平时想要衡量或想要知道数据系统慢不慢，它到底是一个怎样的吞吐，这个服务器的资源现在开销是什么样的？
当我们有了这些需求之后，可能需要通过各种途径来获取需要衡量的这些数据资源，在比较传统的时代，可能会提供 top、iostar 等等各种各样的命令去看。人的价值主要是用来分析数据的，而不是通过各种人力的手段去获取数据。
下图是 EasyDO 的智能运维平台，
在这上面可以看到整个业务系统里面各个数据库实例它所需要关心的，比如 CPU、IO 等等需要关心的指标，在这上面可以一目了然的看到整个系统的数据库情况。
5、MySQL 的常规优化流程
（1）第一个是需要有一个完备的监控体系，监控体系的目的是为了获取、诊断数据库问题的数据，这也是做优化工作的前提，而不是出现问题的时候才花大把精力去获取一些需要诊断的数据。对于完备的监控体系，第一个需要有细致合理的告警，第二个要有多维度的图形化指标，只有做到这两点才能暴露性能缺陷，掌控大规模资源。
（2）第二块是当出现问题的时候，或者是当发现某一段时间它资源的指标跟预想的不太一致的时候，就需要做定性的分析，这个过程就是想要诊断的过程。对于性能诊断这个过程一般关注五点，第一点是发现异常时间的区间；第二点是系统日志，就是数据库的错误日志；第三点是 MySQL 的日志，就是可以通过合理的手段对 sql 做一些执行统计；最后一点是关于 session，对 MySQL 会话的分析。在做了一些诊断分析之后，这个时候可能会定位到某些会话或某些 sql 语句，可以看到它会出现一些比较异常的行为。
（3）第三块需要分析它对应的业务逻辑，对于业务逻辑主要看三点，第一点读写需求，看他的请求量是否正常；第二点看事物是否有设计上的缺陷；第三点是资源的调用关系，有的时候 sql 的执行本身不慢，但资源的调用关系出现了一些所等待的问题。
（4）把这些问题充分分析清楚之后，最后一步才是 sql 优化，比如说像前面第三步读写需求，有的 sql 语句定位到是因为 sql 执行的量比较高导致的，而不是 sql 本身执行存在一个慢的问题。最后一步才是 sql 优化的这一步，关于 sql 优化主要有四点，第一点是 explain，去查看 sql 的执行计划，关于 sql 的优化手段有 sql 的改写、索引调整、参数调整等等。
6、SQL 优化原则与方法
（1）关于 sql 优化原则与方法这里做了一个简单的总结。首先关于 sql 优化原则主要是两点，第一点要减少数据的访问量，数据存取是数据库系统最核心的功能，所以 IO 是数据库系统中最容易出现性能瓶颈的地方，减少 sql 访问 IO 量是 sql 优化的第一步，数据库的逻辑读也是产生 CPU 开销的因素之一。减少访问量的方法主要有创建合适的索引、减少不必访问的列、使用索引覆盖、语句改写。
（2）第二点是减少计算的操作，计算操作进行优化也是 sql 优化的重要方向。sql 中排序、分组、多表连接操作等计算操作都是 CPU 消耗的大户。减少 sql 计算操作的方法有排序列加入索引、适当的列冗余、sql 拆分、计算功能拆分。
（3）sql 优化常规的方法分下面几点，第一点创建索引减少扫描量，第二点调整索引减少计算量，第三点可以通过索引覆盖的特性，避免 sql 语句回表的查询，第四点做 sql 的等价改写，最后一点在有些时候可以干预 sql 的执行计划。
四、原理剖析
1、看几个比较核心的原理性概念，第一个是 B+Tree 索引，B+Tree
索引分三个部分，就是根、枝和叶，B+Tree 就是这样一个数据结构。
它本身核心的特点就是根和枝本质上都是不存储数据的，它的行高也是比较固定的。每次通过 B+Tree 索引取数据的话必然会经过根、枝、叶这三个节点的路径，所以通过 B+Tree 取数据的代价是比较稳定的。另外一个特征是叶子节点上面的数据，它是有序存储的。
2、下面看 Innodb 的表，在 MySQL 当中 Innodb 也是一个需要研究的存储引擎。
首先需要强调的一点是 Innodb 的表本身是一个 IOT，前面也有对 B+Tree 索引的介绍，对于 Innodb 的表有一个核心的概念，Innodb 的表数据本身就是 B+Tree 索引的叶子节点，从上图中可以看出每一个表的段在 Innodb 上面，在 MySQL 里面本身构建了一个 B+Tree 索引的结构。这个段的物理存储跟常规的官衔数据库的存储方式是一样的，是分区和块的。
3、下面看MySQL 里面索引的检索过程，图中画了三个流程
上面的两块是二级索引，下面属于主键索引，也叫聚集索引。对于聚集索引 Innodb 的表的数据本身。右边可以依次的看三个流程，最上面是非主键查询，非主键查询就是上面的箭头获取数据的入口是从二级索引，通过二级索引第一个过程是返回聚集索引的 ID，第二个过程再回表，相当于聚集索引再去做一次数据的检索，然后从聚集索引当中获取真正想需要的数据。第二个是主键查询，主键查询的入口是直接通过聚集索引的 ID，就可以在聚集索引当中获取想要的数据。第三个是覆盖索引，覆盖索引的入口是二级索引，直接从二级索引当中获取到了想要的数据。上图主要展示了 MySQL 索引检索的主要的三个过程。
