高性能MySQL-笔记总结（3）-查询性能优化

第六章 查询性能优化

6.2 慢查询基础：优化数据访问

1. 确认应用程序是否在检索大量超过需要得数据。可能访问了过多得列或者行
2. 确认MySQL服务器层是否在分析大量超过需要得数据行

6.2.1 是否向数据库请求了不需要的数据

注意：

总是取出全部的列

​ 审视每一个select * ,是否真的需要每一列，当然有时候浪费一些系统资源可以简化开发。

重复查询相同的数据

​ 类似用户头像的url，这样的数据可以存放在缓存种，避免在MySQL种重复查询。

6.2.2 MySQL是否在扫描额外的记录

衡量查询的开销：

响应时间

关系到 存储引擎的锁，高并发资源竞争，硬件响应等

扫描的行数和返回的行数

并不是所有的行的访问代价都是相同的。较短的行的访问速度更快。扫描的行与返回的行的比率一般在1：1 到10：1之间。

扫描的行数和访问类型

访问类型有：全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用

这些访问类型有着不同的扫描行数。如果查询没有办法找到合适的访问类型，那么解决做好办法通常就是增加一个合适的索引。

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

• 在索引中使用where条件来过滤不匹配的记录。存储引擎完成。
• 使用索引覆盖扫描来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。服务层完成。
• 从数据表中返回数据，过滤不满足条件的记录。

如果发现查询需要扫描大量的数据但只返回少量的行，以下有几种尝试方法：

• 使用索引覆盖扫描，存储引擎无需回表获取对应行。
• 改变表结构。使用单独的汇总表。
• 重写这个复杂查询，让MySQL优化器能够优化。

6.3 重构查询的方式

6.3.1 一个复杂查询 & 多个简单查询

在以前的时候总认为网络通信、查询解析和优化是代价很高的。但是在MySQL上，连接与断开都是很轻量的。而且现在网络的带宽提升了很多，所以不要害怕将一个复杂查询分解成一个一个小查询。

6.3.2 切分查询

“分而治之” ：

将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

如果是事务引擎，很多时候小事务更加的高效。

6.3.3 分解关联查询

很多高性能的应用都会对关联查询进行分解。

6.4 查询执行的基础

MySQL执行一个查询的过程：

• 客户端发送一条查询给服务器。
• 服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段。
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
• MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端

6.4.1 MySQL客户端/服务器通信协议

​ MySQL客户端与服务器之间的通信是”半双工“ 的，我们无法也无需将一个消息切成小块独立来发送。

​ 这种通信简单快速，但也限制了MySQL无法进行流量控制。一旦一段开始发生消息，另一端要接受完整个消息才能响应他。

​

​ 所以当查询语句很长或者单独数据包较大的时候，使用max_allowed_packet来限制包大小，客户端发送请求时，不能让服务端只能等待。同时在sql语句中增加 limit 来限制数据过长。

​

​ 多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要得数据。MySQL通常要等所有得数据都已经发送给客户端才能释放这条查询所占用得资源。所以接受全部结果并缓存通常可以减少服务器得压力，让查询早点结束、早点释放相应的资源。

​

查询状态

• sleep

  线程等待客户端发送新的请求

• Locked

  在MySQL服务层，该线程正在等待表锁。InnoDB的行锁不会表现在线程状态中。

• Analyzing and statistics

  线程正在收集存储引擎的统计信息，并生成查询的执行计划。

• Copying to tmp table[ on disk ]

  线程正在执行查询，并将结果集都复制到一个临时表中。

• Sort result

  线程正在对结果集进行排序。

• Sending data

  线程可能在多个状态之间传送数据 || 正在生成结果集 || 正在向客户端返回数据

6.4.2 查询缓存

​ 解析一个查询语句之前，如果缓存是打开的，MySQL会优先检查这个查询是否命中缓存中的数据，检查通过一个对大小写敏感的哈希查找实现的。

​ 如果命中了查询缓存，那么会先检查一次用户权限，如果权限无问题，那么会跳过其他所有姐u但，直接从缓存中拿到结果并返回给客户端。

6.4.3 查询优化处理

语法解析器和预处理

通过关键字将SQL语句进行解析，并生成一颗解析树。MySQL解析器先使用MySQL语法规则验证和解析查询。

依次检查： 错误关键字，关键字顺序，验证引号匹配等等，然后进一步检查解析树是否合法，例如表明和数据列是否存在，类型是否正确。

下一步预处理会验证权限，这一步通常会很快，除非服务器上由非常多的权限配置。

查询优化器

​ 当语法树被认为是合法的，由优化器将其转化成执行计划。

​ 优化器的作用是在多种执行方式中找到最好的执行计划

​ MySQL使用基于成本的优化器。尝试预测一个查询使用某种执行计划时的成本，并选择其中最小的一个。成本的最小单位： 读取一个4K数据页的成本。例如一个where条件比较的成本大概时1040个单位：

​

MySQL基于成本最优模型可能并不是你所想的最优。MySQL的优化策略可以分为两种：静态优化 ， 动态优化。

静态优化

直接对解析树进行分析，完成优化。

动态优化

动态优化与查询上下文有关。比如条件取值、索引条目对应的数据行等等，每次查询都要重新评估。

下面是一些MySQL能够处理的优化类型：

• 重新定义关联表的顺序。
• 将外连接转化为内连接
• 使用等价变换规则 （比如将 5=5 AND x > 5 变换成 x > 5）
• 优化COUNT() 、 MIN() 、MAX()
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询（已经满足需求）
• 等值传播（如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上）
• 列表IN()的比较

MySQL的优化器能做的不仅仅如此, 他已经非常聪明了, 让优化器按照他的方式工作就可以

数据和索引的统计信息

在服务层有查询优化器,但没有保存数据和索引的统计信息. 统计信息由存储引擎实现. 所以优化器在生成查询的执行计划时,需要向存储引擎获取相应的统计信息.

MySQL如何执行关联查询

MySQL关联执行的策略很简单: MySQL对任何关联都执行嵌套循环关联操作. 即先在一个表中循环取出单条数据, 然后再嵌套循环到下一个表中寻找匹配的行, 直到找到所有表中匹配的行为止.

执行计划

​ MySQL不会生成查询字节码来执行查询. MySQL生成一颗指令树, 由存储引擎执行完成这棵树并返回结果.

关联查询优化器

​ 关联查询优化器通过评估不同顺序时的成本来选择一个代价最小的关联顺序

排序优化

​ 无论何时排序的成本都很高. 从性能角度考虑,应该尽可能避免排序或者对大量数据进行排序. 当排序不能使用索引生成排序结果的时候,MySQL需要自己进行排序.

if 需要排序数据量 < “排序缓冲区”

​ 使用内存进行"快速排序"

else

​ 先将数据分块,对每个单独的块使用"快速开虚",并将结果存在磁盘上, 然后将各个排序好的块进行合并, 然后返回排序结果.

6.4.4 查询执行引擎

​ 查询执行引擎根据解析和优化阶段生成的查询对应的执行计划来完成整个查询，这里的执行计划是一个数据结构。

​ 在查询执行阶段，MySQL只是简单的根据执行计划给出的指令逐步执行。执行过程中，大量的操作需要调用**存储引擎实现的接口（handler API）**来完成。

​ 查询中每一个表由一个handler的实例表示，MySQL在优化阶段就为每个表创建了一个handler实例，优化器根据handler实例的接口可以获取表的相关信息。

​ 存储引擎 底层接口只有几十个，像搭积木一样完成大部分的操作。

​ 并不是所有操作都是由handler完成，大概MySQL需要进行表锁的时候，handler可能会实现自己界别的更细粒度的锁。

6.4.5 返回结果给客户端

​ 执行查询的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL任然会返回这个查询的一些信息，例如查询影响到的行数。

​ 如果允许被缓存，结果也会被放入缓存中。

​ MySQL将结果集返回给客户端是一个增量逐步返回的过程。例如服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。两个好处：1. 服务器无须存储太多的结果 2. 客户端可以第一时间获得返回的结果。

6.5 MySQL查询优化器的局限性

6.5.1 关联子查询

​ WHERE 条件中包含 IN() 的子查询语句在MySQL中实现的很糟糕。

上面的SQL会被优化成：

解决方法：

• 关联改写

• 使用函数GROUP_CONCAT() 在IN()中构造一个逗号分隔的列表。
• 通常建议用EXISTS() 等效改写查询来获取更好的效率。

6.5.2 索引合并优化

​ 当WHERE子句中包含多个复杂条件的时候,MySQL能够访问单个表的多个索引以合并和交叉过滤的方法来定位需要查找的行。

6.5.3 UNION限制

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话就需要再UNION的各个子句中分别使用这些子句。从零时表中取出的数据的顺序并是一定的，所以如果向他要获得正确的顺序，还需要加上一个全局的ORDER BY 和 LIMIT 操作。

6.5.4 等值传递

​ 如果有一个非常打的IN() 列表，而MySQL优化器发现存在WHERE、ON或者USING的子句，将这个列表的值和另一个列表的值相关联，那么优化器会将IN()列表都复制应用到关联的各个表中，这会产生额外消耗。目前只有修改MySQL源代码来解决这个问题。

6.5.5 并行执行

​ MySQL无法利用多核特性来并行执行查询。

6.5.6 哈希联表

​ MySQL不支持哈希关联。所有的关联都是嵌套循环关联。

6.5.7 松散索引扫描

​ MySQL不支持松散索引扫描

6.5.8 最大值和最小值优化

select MIN(actor_id) from sakila.actor WHERE first_name = ‘PENELOPE’;

​ 当在一个没有索引的属性上做MIN和MAX时，MySQL会进行依次全表扫描，如果MySQL能够进行主键扫描，那么在扫描到第一个符合where内条件值得时候就找到了，因为主键是严格按照字段大小排列的。

​ 有一个曲线优化办法是移除MIN（）,用LIMIT代替

select actor_id from sakila.actor use INDEX(PRIMARY) where first_name = ‘PENELOPE’ limit 1;

6.5.9 在同一个表上查询和更新

MySQL不允许对同一张表同时进行查询和更新。但是可以通过生成表的形式来绕过上面的限制。MySQL会把这个表当作一个临时表来处理。在UPDATE语句打开表之前完成SELECT语句就可以正常执行。

6.7 优化特定类型的查询

6.7.1 优化COUNT() 查询

​ COUNT() 的作用： 他可以统计某个列值的数量，也可以统计行数，统计列值时要求列值是非空的。如果括号内指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。另一个作用就是统计结果集的行数。

​ 最常见的错误：括号内指定了一个列，却希望统计结果集的行数，如果是这样最好还是使用count(*) 。语义清晰、性能也会很好。

1. 简单的优化： 采取反向扫描，利用MySQL的count(*)速度很快的特点做减法，减少扫描行数。
2. 使用近似值，有时候不需要太过精确的数字，而一些限制条件很可能导致全盘扫描，所以删除限制条件，查询要来的快得多。
3. 索引覆盖扫描、增加汇总表、外部缓存系统等。

6.7.2 优化关联查询

• 确保ON或者USING子句中在列上有索引。创建索引的同时要考虑到关联表的关联顺序。
• Group By 和 Order By 中的表达式，只涉及到一个表中列，这样MySQL才有可能使用索引来优化这个过程。

6.7.3 优化子查询

子查询尽可能使用关联查询来代替，但也不是绝对的。

6.7.4 优化GROUP BY 和 DISTINCT

6.7.5 优化LIMIT 分页

分页操作通常使用LIMIT加上偏移量实现，同时加上合适的Order By子句，如果有对应的索引，通常效率会不错。

所以优化分页查询的时候最简单的方法就是尽可能使用索引覆盖扫描。然后根据需要做一次关联操作再返回所需的列。

如果无法增加索引，那么可以使用下面的方法：

转换成下面的：

​