《高性能mysql》第六章读书笔记

一、慢查询优化语句

     低效查询的分析步骤：

    1.确认程序是否存在检索大量超过需要的数据。

    2.确认服务器层是否在分析大量的超出需要的数据行。

    下面详细描述一下这两方面

     1.1 是否查询了大量不必要的数据

1.查询不需要的数据

2.多表关联时候返回全部列

3.总是取出全部列

        在大部分场景不建议如此，会耗费大量的I/O、内存和CPU资源

        4.重复查询相同的数据

    1.2 mysql是否在扫描额外的数据航

衡量查询的三个指标：

响应时间，扫描行数，返回行数

     响应时间

         是服务时间(真正查询的时间)和排队时间(服务器因为某些原因而等待没有执行查询的时间)之和，

     扫描行数和返回的行数

        扫描行数和返回行数比例一般在 1:1到10:1之间

    扫描行数和访问类型

       访问类型种类: 全表扫描、索引扫描、范围查询、唯一索引查询和常数引用查询。

                              从前到后速度从慢到块，扫描行数从小到大

mysql的三种应用where条件：

1.在索引中使用where条件过滤不匹配数据，在存储引擎中完成

2.使用索引覆盖扫描过滤不需要的数据，在服务层完成，不需要回表

3.从数据表中返回数据，然后过滤不需要的数据，在服务层完成，mysql需要先从数据表中读出记录然后过滤。

二、重构查询的方式

1.在不同的场景下，一个复杂sql还是选择拆分多个简单sql要根据情况选择

2.切分查询

例如删除海量的数据可以拆分成多次少量数据的删除，可以省去mysql的资源而不影响业务，用时间换空间，大大降低对服务器的影响。

3.分解关联查询

将多表关联的sql语句差分成多个单表查询，有如下优点：

可以高效的利用缓存

执行单个查询减少锁的竞争

在业务层面做表的关联，易于数据库的拓展

查询本身效率提高

减少冗余记录的查询

最后，这样实际上是在应用中实现了哈希关联，而不是mysqlde嵌套循环关联，某些场景下，效率更高。

三、执行查询的基础

一条查询语句的执行流程：

[1]客户端发送一条查询语句给客户端

[2]查询缓存，如果命中，返回结果，否则进入下一阶段。

[3]服务器端进行sql解析，预处理，再由优化器生成对应的执行计划

[4]mysql根据优化器生成的执行计划，调用存储引擎的API执行查询

[5]将结果返回给客户端

3.1mysql的客户端和服务端的通信协议

mysql的客户端和服务端的通讯协议是"半双工的"，意味着在某一时刻，服务器向客户端发送数据和客户端向服务器发送数据只能发生一个，不会同时发生。

一般来说查询的结果会放到缓存中后再返回给客户端，这就需要注意返回的结果数据集不能过于庞大，可能导致所有的资源都被这一个查询所占用。

3.2查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么mysql会优先查询是否会命中缓存中的数据，如果命中，就返回结果，而不会解析sql语句生成执行计划。

3.3解析器和预处理

mysql通过关键字将sql语句进行解析，生成对应的"解析树"。mysql解析器使用mysql语法规则验证和解析查询。

预处理器根据mysql规则验证生成的解析树是否合法，进而验证权限。

解析书验证合法，下一步优化器将其转为执行计划。mysql使用基于成本的优化器，会尝试预测使用一种执行计划的成本选择最小的成本的计划。

导致mysql选择错误的执行计划的因素：

统计信息不准确。

执行计划中的成本不等于实际执行的成本。

mysql对于最优的定义和开发者的理解会有偏差。

mysql从不考虑其他并发执行的查询。

mysql不是任何时候都是基于成本的优化。

不会考虑不受其控制的成本。

无法估算所有可能的执行计划。

3.4优化器

mysql的优化器有两种：静态优化和动态优化。

静态优化：可以直接对解析树进行分析完成优化，不依赖特别的数值，在第一次优化完成后一直有效，即使使用不同的参数重复查询也不会变化，是一种"编译时优化"。

动态优化：与查询上下文有关，对查询的动态优化每次执行时都会重新评估。

mysql能够处理的优化类型有：

重新定义关联表的顺序

将外连接转为内连接

使用等价变换规则

优化COUNT()、MIN()、MAX()

预估并转为常数表达式

覆盖索引扫描

子查询优化

提前终止查询

列表IN()的比较

当前mysql的关联执行策略：对任何关联都执行嵌套循环关联操作。

3.5执行计划

mysql生成查询指令树，通过存储引擎执行这个指令树返回结果，最后的执行计划包含重构查询的全部计划。

关联查询优化器:决定多个表的关联顺序，关联优化查询器通过评估不同顺序时的成本选择代价最小的关联顺序。

排序优化：排序是很高的操作，从性能上来说尽量避免使用排序操作。需要排序数据量小于"排序缓冲区"则在内存排序，内存不够则会先将数据分块，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块合并，返回结果，会使用磁盘，mysql将这个过程统一称为文件排序，即使是仅使用内存排序。

mysql两种排序算法：

两次传输排序(旧版本使用)：读取指针和需要排序的字段，进行排序，根据排序结果读取需要的数据行

单次排序使用(新版本使用)：mysql4.1及以后的版本引入此算法。读取查询需要的所有列，根据给定列排序，返回排序结果。

单次排序的优点是只需一次顺序I/O读取所有数据，无需任何的随机I/O，缺点是需要返回的列多非常大会很占用空间。

mysql进行文件排序使用的临时存储空间比想象的要大，因为mysql在排序时，对每个排序揭露都会分配一个足够长的定长空间来存放。定长空间必须足够长以容纳其中最长的字符串。

查询执行引擎：根据自执行计划完成整个查询，在执行计划逐步执行的过程中会大量的调用存储引擎实现的接口来完成，这些接口又称为"handler API"。

3.6 返回结果给客户端

是一个增量逐步返回的过程

四、Mysql查询优化器的局限性

4.1关联子查询

4.2UNION的限制

4.3等值传递

4.4并行执行

4.5哈希关联

4.6松散索引扫描

4.7最大值和最小值优化

4.8在同一个表上查询和优化

五、查询优化器的提示

     如果对执行计划不满意，可以使用优化器提供的几个提示来控制最终的执行计划。

六、优化特定类型的查询

6.1优化count()查询

统计某个列值的数量也可以统计行数。

对于MYISAM的误解：count()函数极其的快，是有前提条件的，即不能有where条件才会快。

简单的优化

使用近似值，结合实际业务场景考虑

更复杂的优化，考虑使用缓存

6.2优化关联查询

确保ON或USING子句中的列上有索引。

确保order by 和 group by 的表达式只设计一个表中的列。

当升级mysql的时候要注意：关联语法、运算符优先级等可能发生改变的地方

6.3优化子查询

截止目前的mysql版本，建议尽量使用关联查询代替

6.4优化group by 和 distinct

mysql无法使用索引时候，GROUP BY 使用两种策略：使用临时表或文件排序来做分组。

使用GROUP BY WITH ROLLUP，即对返回的结果再次的分组。

6.5优化LIMIT分页

尽量减少扫描的行数，可以使用覆盖索引优化

6.6优化SQL_CALC_FOUNDP_ROWS

使用分页模糊设计和缓存优化

6.7优化UNION查询

除非必要去重，否则尽量使用UNION ALL，mysql会给临时表加个DISTINCT选项，导致对整个临时表的数据做唯一性检查，代价很高。

6.8静态查询分析

6.8使用用户自定义变量

是一个用来存储内容的临时容器。

以下场景补鞥呢使用：

使用自定义变量查询，无法使用查询缓存。

不能在使用常量或标识符的地方使用，例如表名，列名和LIMIT子句中。

如果使用连接池或持久化连接，自定义可能让看起来毫无关系的代码发生交互。

兼容性问题。

不能显式的声明自定义变量的类型。

在某些场景自定义变量可能被优化掉。

赋值的顺序和时间点不固定，依赖优化器的决定。

赋值符号 := 的优先级很低

使用未定义变量不会产生任何语法错误。

总结：自定义变量使用门槛较高，需要较深的研究才可以使用，如果不太擅长，尽量避免使用自定义变量。