查询性能优化

为什么查询速度会慢

查询大概有以下过程，从客户端，到服务器，在服务器上解析，生成执行计划，执行，返回给客户端，其中执行包括了检索数据，存储引擎的调用，调用后数据处理（排序，分组）。
查询在不同的地方花费的时间，包括网络，CPU计算，生成统计信息，执行计划，锁等待，向底层存储引擎检索数据的调用操作
一些不必要的额外操作，被重复多次的操作，执行慢的操作，优化的目的就是减少这些操作

如何优化

对于低效的查询，下面两个步骤总是很有效
1.确认应用程序是否在检索大量超过需要的数据，这通常是访问了太多的行列
2.确认 MySQL 服务器层是否在分析大量超过需要的数据行

常见情况

• 查询不需要的记录
  可能只要显示 10 条记录，却查询出来 100 条记录
• 多表关联时返回全部列
  只需返回表中需要的字段
• 重复查询相同数据
  对于要多次查询的相同数据，可在初次查询的时候缓存起来
  查询的结果是否扫描了过多的数据行
  可从下面三个指标衡量，响应时间，扫描行数，返回的行数，这个三个指标都会记录到 MySQL 的慢日志中

响应时间是两部分之和：服务时间和排队时间，查询所花时间和等待资源时间，响应时间长，可能是查询问题或服务器问题，可以使用“快速上限估计” 来估算时间

扫描行数，在 EXPLAIN 语句中的 type 列反映了访问类型。访问类型有很多，从全表扫描，索引扫描，范围扫描，唯一索引扫描，常数引用等，这里速度是从慢到快，扫描的行数也是从小到大

当 base_user 表的 phone 有索引时
如：EXPLAIN SELECT * FROM base_user where phone ＝ '15068722364’ 的 type 是 ref
EXPLAIN SELECT * FROM base_user where phone LIKE '15068722364％’ 的 type 是 range
EXPLAIN SELECT * FROM base_user where phone LIKE ‘％15068722364％’ 的 type 是 all

当 base_user 表的 phone 没有索引时
EXPLAIN SELECT * FROM base_user where phone ＝ '15068722364’ 的 type 是 all ，Extra：Using where 这是直接通过 where 条件来筛选存储引擎返回的数据

一般 MySQL 能够使用如下三种方式应用 where 条件， 从好到坏

• 在索引中使用 where 条件来过滤不匹配的记录，这是在存储引擎层完成的
• 使用索引覆盖扫描（Extra 中出现 Using index）来返回记录，直接从索引过滤不需要的记录并返回命中结果，这是在 MySQL 服务器层完成的
• 从数据表中返回数据，然后过滤不满足条件的记录（Extra 中出现 Using where），这是 MySQL 服务器层完成的

当发现查询需要扫描大量数据但只返回少数的数据时，就要考虑优化了

• 可使用索引覆盖扫描，把所有需要用的列都放到索引中
• 该表库表的机构，例如使用单独的汇总表
• 重写这个复杂的查询，让 MySQL 优化器能够以更优化的方法执行这个查询

下面来具体说一如何重构查询的方式
将一个复杂的查询分成多个简单的查询

1. 切分查询
   分而治之，讲大查询切分成小查询，每个查询功能一样，只是完成一小部分，或只返回一小部分查询结果
   如：删除大量旧数据，如果用一个大语句，可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞其他查询，切分成小的语句，可尽可能小的影响 MySQL 性能，如，加 limit
2. 分解关联查询
   可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做有如下优势：

• 让缓存效率更高，很多应用可以缓存但表查询对应的结果对象，拆分后，如果某个表改变很少，那么基于该表的查询就可以重复利用查询缓存结果
• 执行耽搁查询可以减少锁竞争
• 在应用层关联，容易对数据库进行拆分，容易做到高性能和可扩展
• 查询效率提升，如 返回数据少的时候，使用 in（） 代替关联查询
• 可以减少冗余记录查询，在数据库做关联查询，可能需要重复访问一部分数据

MySQL 查询是如何工作的
MySQL 查询执行路径

1. 客户端发送一条查询给服务器
2. 服务器先检查缓存，如果命中，立刻返回存储在缓存中的结果
3. 否则服务器进行 SQL 解析、预处理，再由优化器生成对应的执行计划
4. MySQL 根据优化器生成执行计划，调用存储引擎的 API 执行查询
5. 讲结果返回给客户端

先来看一下 客户端／服务器的通信协议
MySQL 的客户端／服务器的通信协议是半双工，这说明，任何一个时刻，只有服务端向客户端发数据 ，或客户端向服务端发数据，不能同时发生。

当客户端从服务端拉取数据时，实际上是 MySQL 在向客户端推送数据的过程，客户端不断接收从服务端推送的数据，客户端无法让服务端停下来。

多数连接 MySQL 的库函数都可以获得全部结果集并缓存到内存里，以减少服务器压力。

可通过 show full processlist 命令查询当前的状态

• sleep 线程正在等待客户端发送新的请求
• query 正在执行查询或将结果发送给客户端
• locked 正在等待锁，在存储引擎级别实现的锁，如行锁，并不会体现在线程状态中
• analyzing and statistics 正在收集存储引擎的统计数据，并生成查询执行计划
• copying to tmp table 正在执行查询，并将结果集复制到一个临时表中，一般是 group by 操作，union 操作等
• sorting result 正在对结果集就行排序
• sending data 在多个状态之间传送数据，或在生成结果集，或向客户端返回数据

查询缓存
在解析查询语句前，如果查询缓存是打开的，就会先检查这个查询缓存中是否命中查询缓存中的数据

查询优化处理
在完成查询缓存后，是将一个 SQL 转换成一个执行计划，MySQL 依照这个执行计划和存储引擎进行交互。包括解析 SQL、预处理、优化 SQL 执行。

1. MySQL 通过通过关键字将 SQL 进行解析，并生成一颗 “解析树” ，MySQL解析器将使用 MySQL 语法规则验证和解析查询。预处理器则根据一些 MySQL 规则进一步检查解析树是否合法
2. 查询优化器
   在语法树认为是合法的了，并且由优化器将其转化成执行计划，优化器的作用是找到最好的执行计划，可通过查询当前的 Last_query_cost 的值来得知 MySQL 计算的查询成本。
   select sql_no_cache count(*) from skila.film_actor
   show status like ‘Last_query_cost’;

优化策略分为两种，静态优化和动态优化，静态优化可直接对解析树进行分析，并完成优化 。例如，优化器可以通过一些简单的代数变换将 where 条件转换成另一种等价形式，静态优化在第一次完成后一直有效，编译时优化。
动态优化则和查询的上下文有关，例如，where 条件中的取值、索引中条目对应的数据行数，需要每次查询重现评估，运行时优化

下面时 MySQL 能勾处理的优化类型

• 重新定义关联表的顺序
• 将外连接转化成内连接
• 使用等价交换规则，可以合并和减少一些比较
  例如，（5=5 and a>5）将被改写为 a >5,
• 优化 count() min() max()
  例如，要找到某一列的最小值，只需查对应 B-Tree 索引最左端的记录，MySQL 可以直接获取索引的第一行记录
• 预估并转化为常数表达式
  例如，自定义的变量在查询中没有发生变化时，就可以转换为一个常数
• 覆盖索引扫描
  索引中包含查询所需列，MySQL 就可以使用索引返回，无需查询对应的数据行
• 子查询优化
  可以将子查询转换一种效率更高的形式
• 提前终止查询
  发现已经满足查询需求的时候，MySQL 会立刻终止查询，例如，limit
• 等值传播
• 列表 in（）的比较
  MySQL 将 in（）列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这个一个O（log n）复杂度的操作，等价转换成 or 查询的复杂度为O（n），MySQL 处理速度更快