《高性能MySQL》（第三版）之五：查询性能优化

5.1、优化基础：
        高性能需要建立在库表结构优化、索引优化、查询优化的基础上，关于查询优化，就是要消除、减少某些操作花费的时间。查询性能低下的基本原因是访问的数据太多。访问的数据很多，但是很有可能并不是最终真正需要的，所以请确定：
1、    应用程序是否在检索大量超过需要的数据。
        例如，select * 取出全部列，让优化器无法完成索引覆盖扫描，带来额外的I/O、内存以及CPU的消耗。又比如，查询后加上limit可以避免mysql先返回全部数据集再进行计算。
2、    mysql服务器是否在分析大量超过需要的数据行。
        查询开销的三大指标：（都会记录到mysql的慢日志）
        ①    、响应时间（服务时间 + 排队时间）
        ②    、扫描行数
        ③    、返回行数
        explain语句中的type列，反映了访问类型。例如全表扫描、索引扫描、唯一索引查询、常数引用等。它们速度从慢到快，扫描行数从多到少。如果没有合适的访问类型，一般意味着需要添加一个合适的索引了。

 

        一般的mysql的where条件可以使用如下三种方式进行应用，从好到坏依次是：
        ①    、索引中使用where过滤不匹配的记录，在存储引擎层完成。
        ②    、使用索引覆盖扫描（extra列出现 using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果，在mysql服务器层完成。
        ③    、从数据表中返回数据，然后再过滤不满足条件的记录（extra列出现using where），在mysql服务器层完成。
        由此可见，好的索引可让查询使用合适的访问类型，尽可能只扫描需要的数据行。mysql不会生成实际需要扫描多少行，只会生成实际扫描了多少行，其中扫描的大部分数据可能被过滤掉属于无用数据。这种情形下使用如下技巧优化尝试：
        ①    、使用索引覆盖扫描，把所有需要的列都放到索引中，这样存储引擎无需回表获取对应行即可返回结果。
        ②    、改变库表结构，例如增加汇总表
        ③    、重写复杂查询

5.2、重构查询：
        5.2.1、一个复杂查询拆分为多个简单查询
                    对于mysql无需担心这样做可能导致更高的网络通信、查询解析优化造成的代价，其实都很轻量级。

        5.2.2、切分查询：
                    一句话：一次sql完成的任务可以分成多次完成，降低每次的压力。
        5.2.3、分解关联查询：
        优势：
                1、缓存效率更高，分解后的查询结果缓存后可以被以后重复利用
                2、分解后单个查询减少锁竞争
                3、在应用层进行关联，可以更容易拆分数据库，实现高性能和可扩展
                4、可能提升查询本身效率
                5、减少冗余记录的查询

5.3、查询的原理基础：
        先来看一下查询的执行流程：
               

        一共五步
        ①    、客户端发送一条查询到服务器
        ②    、服务器先检查查询缓存、若命中缓存，立刻返回结果，否则下一步
        ③    、服务器端进行 sql解析、预处理，再由 优化器 生成对应 执行计划
        ④    、根据执行计划调用存储引擎API执行查询
        ⑤    、返回结果到客户端

        这五步分别对应如下小节：
        5.3.1、mysql客户端与服务器端通信协议
        半双工的通信协议，在同一时刻，服务器端向客户端发送数据 与 客户端向服务器端发送数据 这两个动作不可以同时发生。简单快速，但无法流量控制，一旦开始，只能等待结果。
        mysql需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存能减少服务器压力。查询结束越早，越能尽早释放资源。所以现在能理解必要的时候为啥一定要给查询加上limit的限制了吗？

        参考线程的生命周期，mysql连接也有多个不同的状态，show full processlist 命令可查看：

        sleep、线程等待客户端发送新请求
        query、线程正执行查询或者正将结果发还给客户端
        locked、线程正在等待表锁（mysql服务器层）
        analyzing and statistics、线程正在收集存储引擎的统计信息并生成查询的执行计划
        copying to tmp table [on disk]、线程正在执行查询并将结果拷贝到（磁盘）临时表
        sorting result、线程正在对结果集进行排序
        sending data、表示多种情况，线程在多个状态间传递数据、或者在生成结果集、或者在向客户端返回数据。

        在之前的文章中我们曾剖析单条查询的时候，使用命令分析过这些状态的时间统计，如下图：以供参考。
               

        5.3.2、查询缓存
                这里简要概述，后续后进行详细介绍：
                查询缓存打开的情况下，先检查是否命中查询缓存中的数据，若命中检查用户权限，然后返回缓存中命中的数据。
        5.3.3、查询优化处理
                5.3.3.1、语法解析器与预处理
                就是把sql变成查询执行计划的过程，中间包含解析sql、预处理、优化sql执行计划等阶段。通过关键字把sql语句解析成一颗解析树，然后解析器进行语法验证、解析查询。预处理器再进行权限验证。
                5.3.3.2、查询优化器
                目的是找到成本最小的执行计划（一条查询可以有多种执行方式，成本的最小单位是随机读取一个4K数据页的成本。优化器评估成本时不考虑任何缓存，假设读取任何数据都要一次磁盘I/O）

                查询优化器的优化策略：
                ①    、静态优化，直接对解析树解析来完成优化，不依赖特别的数值，在第一次完成后一直有效，即使用不同的参数重复执行查询也不会变，是一种编译时优化。
                ②    、动态优化，和查询上下文有关，需要在每次查询时重新评估。
                列举一些mysql能处理的优化类型：（挑选列举）
                        1、    重定义关联表顺序
                        2、    外连接转内连接
                        3、    等价变换
                        4、    优化count()、min()、max()
                        5、    覆盖所引扫描，这个前面有提到过
                        6、    子查询优化
                        7、    提前终止查询，这个前面也有提到，比如加上limit

                5.3.3.3、数据和索引的统计信息
                查询优化器在服务器层，数据和索引的统计信息却由存储引擎实现，所以查询优化器在生成查询执行计划时需要向存储引擎获取相应统计信息数据。
                5.3.3.4、关于关联查询
                先纠正常识错误：不只是用到两个表匹配才叫关联，mysql认为任何一个查询都是一次关联。
                mysql的关联执行策略：嵌套循环关联（简单解释一下，先从A表取出单条数据，嵌套循环到B表寻找匹配行，依次下去直到找到 所有表 中匹配的行为止，然后根据这些行查找需要的各个列），通过嵌套循环关联的解释，不难理解马上要说的关联查询优化器。它通过决定多个表关联时的顺序来选择代价最小的关联。因为标的关联顺序不同，控制的嵌套循环和回溯操作数量也不同。

                5.3.3.5、关于排序
               排序的成本很高，尽可能避免排序或者大数据量的排序操作。
               当不能使用索引生成排序结果时，mysql会自己排序（filesort文件排序），数据量小在内存中（快速排序）就执行了，数据量大则依赖磁盘（先把数据分块，对每块进行快速排序，再把每块排序结果放在磁盘，再合并，然后返回）。

                mysql 排序算法：
                1、    两次传输排序
                        先读取行指针和需要排序的字段，然后排序，根据排序结果读取需要的数据行（这一步是读取排序列排序后的所有记录，产生大量随机I/O）
                2、    单次传输排序
                        先读取查询所需要的所有列，再根据给定列进行排序，最后直接返回排序结果。只需一次顺序I/O，无任何随机I/O，但是如果返回列过多过大，会额外占用大量空间。
        5.3.4、查询执行引擎
        查询执行引擎执行查询执行计划
        5.3.5、返回结果到客户端
        这是一个增量逐步返回的过程，这样有利于服务器端无需存储太多结果导致消耗内存。

5.4、查询优化器局限性：
        这一部分主要是针对某些特殊情况进行说明，毕竟mysql的万能嵌套循环并不是对每种查询都是最优的。
例如：
1、    关联子查询
        where 条件包含in的子查询。通常建议使用exists（）等效的改写查询提升效率，毕竟mysql会把相关外层表压到子查询中，而不是我们通常理解的认为先执行子查询返回所有符合条件的数据。
eg: select * from A where id in (
        select a_id from B where nice_word = ‘你真漂亮’
)

        扩展：关于exists的阐述： exists子句返回一个布尔值，其内部有一个子查询语句，将外查询表 的每一行，代入内查询进行检验，如果内查询返回结果取非空值，则exists子句返回true，这一行就可作为外查询的结果行，否则不能作为结果。

2、union的限制
        直接举例子好理解：A表取10个然后 union B表取20个，最终想要30个数据。假设A中共100条数据，B也是100，那么将会生成一个A和B共计200的临时表，然后再取出符合条件的30个。这样一来意思显而易见。
        常用的处理办法是在 从select A 和 select B 的时候就加上limit，使得临时表的数据量大大降低。

        扩展： union  和  union  all 的区别。
                union对两个结果集取并集，不包括重复行，默认排序。
                union all 对两个结果集取并集，显示所有结果，包括重复行，不排序。
                在不考虑去重的前提下，union all 比  union 的效率高。

3、    mysql 无法利用多核特性进行并行执行查询
4、    松散索引扫描
        mysql不支持松散索引扫描，无法按照不连续的方式扫描一个索引。假设有索引（a,b），如果查询中只指定了b字段，因为索引前导字段是a，那就无法使用这个索引。
explain中的extra字段会显示 using index for group-by，表示使用松散索引扫描（注意：mysql5.0之后的某些版本）

5.5、特定类型的查询优化：！！！
        这个小节可以说是和日常工作开发联系很紧密的了：
        5.5.1、优化count()查询
       count()中如果指定了表达式，则统计表达式有值的结果数。count(*)是不会扩展成所有的列，而是忽略所有列直接统计行数。通常需要扫描大量的行，访问大量数据，，优化也是一般从前面提到过的覆盖索引、增加汇总表等角度考虑进行。
        5.5.2、优化关联查询
        1、前面提到过的，表的关联顺序是有很大的影响的，直接影响扫描数据行数。所以创建索引的时候要考虑表的关联顺序。举例：A表和B表共用c列，进行关联。如果优化器的关联顺序是B关联A，就无须在B表对应列上创建索引，一般只需在A表（关联顺序中的第二个表）的相应列上创建索引。
        2、确保 任何group by 和 order by 的表达式只涉及到一个表中的列，否则无法使用索引来优化

        5.5.3、优化子查询
        一句话：尽可能使用关联查询进行替代，mysql5.6之后可以无视了。
        5.5.4、优化group by 和 distinct
        都可以使用索引来优化。
        若无法使用索引，group by 采用如下两种策略，使用临时表或者文件排序来做分组。
        若需要对关联查询做分组，并且按照查找表中的某个列做分组，这个列最好采用表的标识列（比如主键列）效率会比其他列更高。

        分组查询中select 中直接使用非分组列，一定是直接依赖分组列的，并且其在每个组内的值是唯一的，或者说业务上根本不在乎这个值具体是什么。

        如果没有指定order by 语句显式排序，若查询使用group by子句，结果集会默认自动按照分组的字段排序（会导致文件排序），如果业务上不关心结果集的顺序，可以加上 order by null 语句进行优化。
        5.5.5、优化limit分页
        偏移量很大的时候，比如limit 1000,20 ，需要查10020条然后只返回最后20条，前面10000条被抛弃，扫描大量不需要的行再抛弃，代价太大。

        策略一：延迟关联，就是说尽可能使用索引覆盖扫描，而不是查所有的列，然后根据需要做一次关联查询再返回需要的列。能让mysql扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询所需的所有列。简单理解概括就是先控制结果的范围，然后根据范围内的关联列去原表查出这些范围内结果中需要的所有列数据。
                eg: select film_id,description from film order by title limit 50,5;
                改为：
                select film.film_id,film.description from film
                inner join (
                    select film_id from film order by title limit 50,5
                ) as lim
                using (film_id);

       策略二：在偏移量上想办法，比如添加条件间接重置偏移量？
       假设select * from A order by id desc limit 20;查出的数据id范围是16049到16030，那么下一页的查询可以从id = 16030这个点开始，而不是最开始的16049，相当于动态变更偏移量的初始位置。
       改为：
        select * from A where id < 16030 order by id desc limit 20;

        5.5.6、优化union查询
        mysql总是通过创建并填充临时表的方式来执行union的查询。前面有提到过union与union all 的区别，你还记得吗？除非必须要消除重复行，否则一定要使用union all,否则临时表会被加上distinct，对整个临时表做数据唯一性检查，代价非常高。

        别的还有一些零碎的、冷门的优化，这里就不再赘述了，常见的、需要掌握了解的上面基本上已经包括。结合前面的三、四两章，加上本章节，这三章内容多看几遍，加强印象，哪里不理解的写个小demo sql试一试，理解会更深刻。

        就像这次工作上对于发电量数据的查询优化，通过自己不断摸索尝试的过程，既解决了问题，也使得自己加深了学习理解，才会有这个系列的总结分享。写作的过程，何尝不是一次 梳理、思考的过程呢？

        从此书的目录上来看，后续内容应该就是一些扩展上的内容了，比较琐碎、广泛，个人认为这有利于拓宽一个开发人员的视野，了解更多更深的内容；如果只是想针对工作中常见的查询性能优化问题进行处理，我觉得之前的内容基本够用了。所以后续的文章内容我不会再做细致的总结分享，而是针对一些自认为有掌握价值的内容做一些随笔记录，写一写想法就可以了。如果有什么建议或者想说的，欢迎诸位留言，同时在此也感谢近期新关注公号的朋友们，感谢您的支持！我们下期见！