开发者学堂课程【MySQL 实战进阶：MySQL 查询优化实战】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/852/detail/14062
MySQL 查询优化实战
内容介绍：
一、优化目的与目标
二、优化流程及思路
三、原理剖析
四、MySQL 的行为
五、常规优化策略
六、小结
一、优化目的与目标
1、为什么要优化
优化的目的就是提高资源的利用率，让资源都充分的发挥它的价值，在常见的场景下，比如一台服务器上，四大资源 CPU、内存、网络、磁盘，一旦当中哪一个出现了瓶颈，整个服务器继续去提供服务的能力就会变得比较差，所以优化本质目的是让资源都充分地发挥它的价值，最终目的是满足更多用户的在线需求。
2、MySQL 优化目标
(1)减少磁盘 IO
对于磁盘 IO，就是在 MySQL 数据库当中主要来自全表扫描，或者一些其他的会扫描大量的数据块的场景，日志以及数据块的写入的情况主要来自这几个方面。
(2)减少网络带宽
网络带宽主要包括两个方面，首先是有时的 SQL 查询会返回太多数据，第二是可能插入的场景会跟 MySQL 的 server 端有过多的次数交互。
(3)降低 CPU 消耗
对于 CPU 消耗主要是两块，第一个是 MySQL 本身的逻辑读，第二个是一些额外的计算操作，比如一些排序分组，像 order by,group by,然后是一些聚合函数。
这个金字塔队从下往上列了14个查询的优化手段，从下往上的一次就是 SQL 及索引的优化。第二块是库表结构的优化，再往上是系统的配置，顶层是硬件。对于单个 MySQL 实例从下往上成本是逐步提升的，但是效果往往越来越差。从本质上讲，SQL 及索引的调优不需要花费成本，但是它一般可以取到特别好的效果。
二、优化流程及思路
1、关注的指标
(1)CPU 使用率
对于 CPU 使用率，一般认为它是 sql 查询的关键资源指标，首先主要是 CPU 的消耗，它主要是来自数据的扫描和一些显示的计算。
(2)IOPS
也是衡量磁盘设备的一个指标，IOPS 指的是每秒 io 请求的次数，我们一般认为它是物理读写的关键资源指标。
(3)QPS/TPS
对于 QPS/TPS 我们会认为是 MySQL 数据库的吞吐量，对于吞吐量我们有些时候也可以认为它可以反映出数据库整个业务系统的业务压力。
(4)会话数/活跃会话数
对于会话，一般出现问题，比如应用配置存在一个问题，没有合理的使用到连接池，或者 SQL 的执行效率比较差，我们会发现数据库的所有端上会加大量的会话，甚至会加大量的活跃会话。
(5)Innodb 逻辑读/物理读
关于 Innodb 的核心的指标就是逻辑读以及物理读，这两块主要是用于反映整体的查询效率。
(6)临时表
关于临时表，一般情况下它是导致 SQL 执行效率下降的特殊行为，它本质上是 SQL 执行过程中的行为，也不排除一些其他手段有可能使 MySQL 系统里产生临时表。
2、合理监控
如果想衡量，或者想知道数据库系统到底慢不慢，是什么样的吞吐，整个服务器资源各个方面的新的开销是什么样的，有了这些需求后，我们需要通过各种途径来获取需要衡量的这些数据资源。在比较传统的这个时代，通过 top，或者 iostat 等等，就是通过各种各样的命令去看，事实上，人的价值主要是用来分析数据，而不是通过各种人力的手段去获取数据。
上图是代理人的智能运维平台，可以看到整个业务系统里各个数据库实例上需要关心的指标一目了然，可以看到整个系统数据库的情况。
3、MySQL 优化流程
(1)构建完备的监控体系
前提是需要有一个完备的监控体系，监控体系的目的是获取诊断数据问题的数据，这也是做优化工作的前提，而不是每次出现问题的时候再去花大把的精力。获取到一些需要去诊断的数据对于完备的监控体系首先需要有细致合理的告警，第二要有多维度的图形化指标，只有做到这两点，才可以去暴露整个系统的性能缺陷，从而掌握大规模资源。
(2)分析定位问题
当出现问题的时候，或者发现某一段时间资源的指标跟属于想的不太一致的时候，需要做定性的分析，这就是性能诊断的过程。对于性能诊断的过程，一般关注五点：第一，发现异常时间的区间；第二，系统日志以及数据库的错误日志；第三，MySQL 日志；第四，通过一些合理的手段对 SQL 做一些执行统计；第五，关于 session、MySQL 的会话的分析。做了诊断分析之后，一般定位到某些会话，或者某些 SQL 语句，它可能会出现一些比较异常的行为。
(3)分析业务逻辑
对于业务逻辑，主要看三块。第一块是读写需求，看它的请求量是不是正常的；第二个看事物是不是有设计方面的一些缺陷；第三个是资源的调用关系，有时候可能 SQL 的执行本身不慢，但是资源的调用关系出现了一些所等待的问题。把这些问题分析清楚后，最后一步才是 SQL 优化。
(4)SQL 优化
比如刚才的读写需求，有的 SQL 语句是到这一步的时候可能定位到它是因为 SQL 执行的量比较高，而不是 SQL 本身的执行存在慢的问题，所以最后一步才是 SQL 优化。
关于 SQL 优化，主要有四块。首先是 explain，查看 SQL 的执行计划，关于 SQL 的优化手段，一般是 SQL 的改写或者索引的调整、参数的调整等等。
4、SQL 优化原则与方法
关于 SQL 优化的原则，主要是两点。
(1)减少访问量
数据存取是数据库系统最核心功能，所以 IO 是数据库系统中最容易出现性能瓶颈,减少 SQL 访问 IO 量是 SQL 优化的第一步;数据块的逻辑读也是产生 CPU 开销的因素之一。
减少访问量的方法:创建合适的索引、减少不必访问的列、使用索引覆盖、语句改写。
(2)减少计算操作
计算操作进行优化也是 SQL 优化的重要方向。SQL 中排序、分组、多表连接操作等计算操作都是 CPU 消耗的大户。
减少 SQL 计算操作的方法:排序列加入索引、适当的列冗余、SQL 拆分、计算功能拆分。
关于 SQL 优化的常规方法，主要分下面几点：
首先是创建索引去减少扫描量，然后调整索引减少计算量，通过索引覆盖的特性避免 SQL 语句回表的查询，第四，可以做 SQL 的等价改写，最后，甚至可以干预 SQL 的执行计划。
三、原理剖析
1、B+Tree index
B+Tree 索引分三个部分，它的核心特点是它的根和枝本质上都是不存储数据的，另外它的行高也是比较固定的，基本上每次通过 B+Tree 索引取数据必然是会经过根、枝、叶这三个节点的路径，所以通过 B+Tree 索引去取数据的代价基本是比较稳定的。另外一个特征是叶子节点上的数据其实是有序存储的。
2、Innodb Table
对于 MySQL，其实 Innodb 也是需要去研究的核心的存储引擎。首先需要强调 Innodb 的表本身是一个 IOT，这有一个比较核心的概念是 Innodb 的表的数据本身是 B+Tree 索引的叶子结点。从这里可以看到，每张表的段上，就是在 Innodb 上面，它本身就是给它构建了一个 B+Tree 的树状的结构。段的物理存储跟常规的关系数据库的存储方式一样，分区和块。
3、索引检索过程
上面的两块是二级索引，下面是主键索引，也叫聚集索引，聚集索引就是 Innodb 表的数据本身。右边依次看这三个流程，最上面是非主键查询，上面箭头是获取数据的入口，通过二级索引第一个过程它是返回聚集索引的一个 ID，第二个过程，再次去回表，相当于再去聚集索引再去做一次数据的检索，从聚集索引中获取真正需要的数据。第二个是主键查询，入口直接通过聚集索引的 ID，在聚集索引中获取想要的数据。第三个是覆盖索引，入口是二级索引，直接从二级索引中获取到想要的数据。这里展示的就是在 MySQL 里索引检索的主要的三个过程。
四、MySQL 的行为
1、MySQL SQL 执行过程
(1)客户提交一条语句。
(2)先在查询缓存查看是否存在对应的缓存数据，如有则直接返回(一般有的可能性极小，因此一般建议关闭查询缓存)。
(3)交给解析器处理，解析器会将提交的语句生成一个解析树。
(4)预处理器会处理解析树，形成新的解析树。这一阶段存在一些 SQL 改写的过程。
(5)改写后的解析树提交给查询优化器。查询优化器生成执行计划。
(6)执行计划交由执行引擎调用存储引擎接口，完成执行过程。这里要注意，MySQL 的 Server层和 Engine 层是分离的。
(7)最终的结果由执行引擎返回给客户端，如果开启查询缓存的话，则会缓存。
2、SQL 执行顺序
(8) SELECT
(1) FROM <left_ table>
(3) <join_ type> JOIN <right table>
(2) ON <join_ condition>
(4) WHERE <where_ condition>
(5) GROUP <group_ by_ _list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_ condition>
(10) ORDER BY <order_ by_ list>
(11) LIMIT < limit number>
(9) DISTINCT <select list>
SQL 执行顺序在 SQL 优化的时候可以给一些启发，比如 WHERE 条件和 ORDER BY，通常情况下，SQL 语句一般是先获取数据，然后在 select 的操作后，就是数据是先获取再返回，返回到 server 端的结果集的存储区后，再去做排序，如果获取数据的时候数据的排序已经做好了，这个过程就是在 MySQL 的存储引擎层就已经做了优化，所以 SQL 的执行顺序在某些时候是可以给一些指导性的建议的。
3、查询优化器
查询优化器是用来负责生成 SQL 语句的有效执行计划的数据库组件，它是数据库的“大脑”，是数据库的核心价值，优化 SQL,某种意义上就是理解优化器的行为。在 MySQL 里，它的优化依据是执行成本(CBO)，目前 MySQL 的优化器做的没有那么完善，执行成本主要是根据行数，优化器工作的前提是了解数据，工作的目的是解析 SQL,生成执行计划。
4、查询优化器工作过程
注意查询优化器的查询优化分两个阶段，第一块是逻辑优化，第二块是物理优化。逻辑优化，是把 SQL 交给查询优化器后，它是会做相应的改写动作，有些时候再去做 SQL 优化，也可以观测 MySQL 的查询优化器的行为，它的逻辑优化改写的行为，其实物理优化的过程就是优化器它真正生成的获取数据、扫描数据的路径。
5、查看和干预执行计划
(1)执行计划
explain [extended] SQL_ Statemen
通过 explain 这个关键词，也可以通过添加 extended 这个关键字，为什么要添加这样的关键字呢？因为 MySQL 优化器的逻辑优化改写的过程，有时候给 SQL 语句的执行计划里添加 extended 这样一个关键字，可以看到一些比较关键的信息。
(2)优化器开关
show variables like 'optimizer_ switch'
通过一些适当的手段，比如强制加索引，或者强制驱动表的顺序，这种方式可以干预 SQL 的执行计划，优化器的开关包含了 MySQL 查询优化器的一些比较重要的特性，比如 index_merge 等等，在 MySQL 的 Innodb 里一些比较关键的特性，通过控制优化器的开关也可以控制优化器的相关的行为。
6、processlist
观测 SQL 执行的特殊行为就是 processlist，通过会话观测当前在 MySQL 中所执行的所有 SQL 语句中有没有一些会话正在出现一些比较特殊的行为，会话操作可以通过两种途径，第一个是通过 show [full] processlist，也可以通过 information_schema. processlist 里的视图。
以下是常见的比较异常的行为：
copy to tmp table:出现在某些 alter table 语句的 copy table 操作
Copying to tmp table on disk:由于临时结果集大于 tmp_ _table_ size,正在将临时表从内存存储转为磁盘存储以此节省内存
converting HEAP to MylSAM:线程正在转换内部 MEMORY 临时表到磁盘 MylSAM 临时表
Creating sort index:正在使用内部临时表处理 select 查询
Sorting index:磁盘排序操作的一个过程
Sending data:正在处理 SELECT 查询的记录，同时正在把结果发送给客户端
Waiting for table metadata lock:等待元数据锁
五、常规优化策略
1、SELECT 优化-order by
(1)order by 查询的两种情况:
Using index。MySQL 直接通过索引返回有序记录,不需要额外的排序操作，操作效率较高
Using filesort。无法只通过索引获取有序结果集,需要额外的排序，某些特殊情况下，会出现 Using temporary
(2)优化目标:尽量通过索引避免额外的排序，减少 CPU 资源的消耗。一般有三个方法：首先，让 where 条件和 order by 使用相同的索引，然后，order by 的顺序和索引顺序相同，另外，order by 的字段同为升序或降序，因为 B+Tree 索引有多个字段，排序不可能出现不同的字段前面是升序，后面是这样的排序的情况。
当没有办法避免排序操作，可以进一步思路让排序的操作更快，就是排序算法，就是两次扫描算法，跟一次扫描算法本质的区别是两次扫描算法是分阶段完成的，目的是更加节约内存空间，从本质上看，一次扫描算法效率更高，但是它会消耗一些内存。
需要注意需要排序的字段总长度越小，越趋向于第二种扫描算法。
(3)对于提升排序的操作，一般可行的优化策略主要是下面三点：
第一，适当调大 max_ length_ for_ sort data 这个参数的值，让优化器更倾向于选择第二种扫描算法。刚才提到排序的两种算法，如果想提升排序的操作，核心思想就是通过空间换时间，一次扫描算法虽然会消耗更多的内存空间，但是它排序的速度更快。
第二，只使用必要的字段,不使用 select *的写法。因为我们的目的是让排序的字段的总长度不要过长。
第三，适当加大 sort_ buffer _size 这个参数的值，避免磁盘排序的出现(线程参数,不要设置过大)，要适当增加 buff 的值。
2、SELECT 优化-Subquery
子查询在大部分情况都会利用到临时表,一般情况下需尽量避免，对于临时表，一般的优化策略就是做等价的改写，这种策略在 MySQL 查询优化器里叫反嵌套。在 MySQL 里查询优化器也可以做一些比较简单的子查询的反嵌套的操作，但是在绝大部分情况下，还是需要去做一些人为的干预，比如像下面看到的 SQL 语句：
select * from customer where customer id not in (select customer id from payment)
改写形式:
select * from customer a left join payment b on a.customer id= b.customer id where b.customer id is null
用 not in 的方式，后面是一个子查询里去执行一个 select 语句,直接把 not in 改成 left join 就可以了，在 MySQL 里 join 的效率比子查询效率要高一些。
3、SELECT 优化-limit
分页查询，就是将过多的结果在有限的界面上分好多页来显示。其实质是每次查询只返回有限行，翻页一次执行一次。
对于分页查询，在绝大部分场景跟 order by是关联的，当我们在这种场景下，第一步是消除排序，第二是避免扫描到大量不需要的记录，比如这里有一个 SQL 场景(film_ id为主键) :
select film_id,description from film order by title limit 10000,20
此时 MySQL 排序出前10020条记录后仅仅需要返回第10001到10020条记录，前10000条记录造成额外的代价消耗。
为什么需要去优化分页查询？做分页的时候，像这种情况，前面的1万条记录会造成额外的数据扫描的代价。
对于分页查询的优化策略，第一个是覆盖索引，alter table film add index idx_ Imtest(title,description)，在某些比较特殊的场景，这个 where 条件以及 select 返回的字段都特别少的情况下，可以直接通过索引覆盖的方式从索引当中直接获取想要的数据，比如本次的 SQL，order by 是 title，前面 select film_id,description，对于接口只用给 title 和 description 建符合索引，film add 本身只是做的实例的一个主键，建了这个索引后 SQL 的 select 跟后面排序的字段其实全部都被索引覆盖了，所以通过覆盖索引最大的好处就是所需要的记录都可以直接从索引当中去获取，效率是最好的。
第二个优化策略是通过改写的方式去做优化，本质上也是通过覆盖索引的原则，select a.film_ id,a.description from film a inner join (select film id from film order by title limit 1000,20) b on a.film id=b.film id，通过 join 方式改写的前提是在 join 后面加了一个子查询，为什么要先加一个子查询？
这里就是一个 select 语句，相当于把分页查询分两步去做，第一步，通过里边的查询先取出需要的20条记录的主键值，第二步，通过跟主表做关联，然后再去取想要的这20条数据，本质上也是通过覆盖索引做优化，但是对于查询的字段其实一般情况下不会像场景一，它所返回的字段特别少，可能是为了条件或者 select 返回的字段可能都会有很多，不可能给所有的字段都建覆盖索引，对于这个情况，通常的优化改写策略就是通过 join 改写的方式，先通过覆盖索引取出主键 ID，然后再去通过这个关联。
4、SELECT 优化-or/and condition
or 和 and 的区别，or 是并集，and 是交集，在 MySQL 里需要注意，对于 and 交集的查询条件，可以看到 condition A 和 condition B，交集主要有一个条件如果利用到选择性比较好的索引，那么 SQL 语句的效率就没有太大的问题，但是它和 or 最大的区别就是 or 的条件在处理的时候会把它视为 UNION，所以 condition A 和 condition B 在查询语句中通过 or 把它联系起来，相当于条件 A 和条件 B 是两个 SQL 语句，对于 or，condition A 和 condition B 都需要通过对应的索引做优化，而不是像 and 一样有两个条件，只要有一个条件有过虑性比较好的索引就可以了。
5、join 优化
对于 MySQL 里的 join，首先先看一下比较典型的算法 Nested-Loop Join 算法。
for each row in t1 matching range {
for each row in t2 matching reference key{
for each row in t3{
if row satisfies join conditions, send to
client
}
}
}
这个算法就是一个嵌套循环。
T1、T2、T3这三张表做 join，首先 T1表扫描匹配到每一行，然后根据 T1跟 T2的管理条件，再去扫描 T2中匹配到的每一行，再在 T2跟 T3的时候做同样的事情。Join 本质是嵌套循环的过程，对于嵌套循环首先需要注意两点，首先要优化的核心，关联的字段要添加索引，目的是让每一层的内部循环获取需要关心的数据就，这里有一个引申算法叫 Bloack Nested-Loop。下面两个图，下面的就是 Bloack Nested-Loop。
在 MySQL 里有一个特性，两张表的关联如果不能通过索引做关联条件的匹配，那么做决定的时候它就会产生 join buffer，当 SQL 的 join 语句执行计划里出现 Bloack Nested-Loop 的时候，通常需要看管理条件是不是没有索引或者可能因为一些其他的原因而导致的关联匹配没有通过索引。当 SQL 出现了 Bloack Nested-Loop 的时候，SQL 的效率会大幅下降。
第二就是小表驱动原则，小表驱动也就是最外层的结果集尽量的让它小，目的是减少区域循环的次数。
·关联字段索引的必要性
案例上面是一条特别简单的 select count 语句，下面是语句执行计划的时候，已经出现了 using join buffer，前面所说的 Bloack Nested-Loop，通过 B 表访问到 A 表的时候，可以看到 rows 是127000多，整个访问过程的代价是特别大的，对于这种场景优化的策略是直接给 A 表添加一个索引，就是给关联的条件添加了一个索引。
通过 B 表再去访问到 A 表的时候，执行计划里的 key 使用到刚才添加的索引，它的 rows 从刚才的12万下降到了125。跟前面的执行效率的区别，从前面的接近两分钟，到这里需要0.31秒。
·小表驱动原则
首先忽略 B 表的索引，让 B 表自身作为驱动表。
执行计划里 select count 就是加了一个忽略索引，目的就是阐述下面的行为，在执行计划里通过 B 表做驱动表，rows 是4000乘以125，执行成本是0.31秒，刚才其实忽略了 B 表的索引，这里再去忽略 A 表的索引。
这时 A 表成了驱动表，B 表成了被驱动表，关联 rows 变成了127042乘以4，本质的区别就是后面的优化器扫描方式比前面的方式成本提升了不少。
虽然这两个 SQL 语句做关联的时候不管通过 A 表还是 B 表做驱动，最后在关联的时候都是通过索引，因为驱动表的大小问题导致最终的 rows 行数的代价不一样，后面的写法产生的时间是0.53秒，比前面差不多慢了一倍。
6、insert 优化
(1)优化策略一：
为了减少网络的消耗，主要策略是减少跟 MySQL 交互的次数，如批量插入语句，比如跑10万条，每一条都是 insert into 语句，对于单条的插句可以做一些适当的合并，下面在一条 insert into 语句后面接多个值，对刚才所说的场景做一些适当的合并，这种情况它的提升效果一般比较明显，因为减少了跟 server 段交互的次数，主要是从网络开销上做的优化。
insert into test values(1,2,3);
insert into test values(4,5,6);
insert into test values(7,8,9);
...
可改写为如下形式:
insert into test values(1,2,3),(4,5,6),(7,8,9) ...
(2)优化策略二：
通过文本装载的方式，就是 LOAD DATA INFILE 句式，通过文本装载数据，通常比 insert 语句快20倍。
六、小结
1、MySQL 查询优化
优化的目的是让资源发挥价值，SQL 和索引是调优的关键，可以起到“四两拨千斤”的效果，简单的加一个索引或者 SQL 语句，做很简单的改写，可以让整个数据库系统大幅的提升性能。
关于流程，首先需要充分了解数据库里的一些核心指标，并构建相对完善的监控体系,这是优化工作的前提，目的是获取诊断数据库性能的一些数据。
第二，SQL 优化的原则是减少数据访问及计算，常用的优化方法主要是调整索引、改写 SQL、干预执行计划。
关于 MySQL 内部核心的原理以及相应的行为，innodb 的表是典型的 IOT,数据本身根据 B+tree 索引的排列组织。扫描二级索引可以直接获取 select 的查询需要的数据，或者先返回主键 id，再去获取真正的数据。优化器是数据库的大脑，要了解优化器并观测以及干预 MySQL 的行为。
