MySQL查询性能优化

MySQL性能优化的点

• 库表结构优化
• 索引优化
• 查询优化

MySQL查询的生命周期

从客户端到服务器，然后再服务器上解析，生成执行计划，执行，并返回结果给客户端。其中执行，可以看作整个生命周期中最重要的阶段， 其中包括了大量
为了检索数据到存储引擎的调用和调用后的数据处理，包括排序、分组等。

查询需要在不同的地方花费时间，包括网络，cpu计算，生成统计信息和执行计划，锁等待等操作，尤其是向底层数据检索数据的调用操作， 这些调用需要在内存
操作、cpu操作和内存不足时导致的I/O操作上消耗时间，存储引擎不同，可能会产生大量的上下文切换和系统调用。

优化的目的：就是减少和消除这些操作所花费的时间。

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

• 确认应用程序是否在检索大量超过需要的数据。这意味着访问了太多的行，但有时候也不能访问太多的列。这会给MySQL服务器带来额外的负担，增加网络开销，消耗服务器的cpu和内存资源。
• 确认MySQL服务层是否在分析大量超过所需要的数据行。

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

• 衡量查询开销有三个指标：
  • 响应时间
  • 扫描行数
  • 返回的行数

响应时间

响应时间是两个部分之和： 服务时间和排队时间。服务时间指数据库处理这个查询花了多长时间。排队时间指服务器因为等待某些资源没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待锁等。

扫描的行数和返回的行数

这两个参数在一定程度上能够说明该查询找到的数据的效率高不高。

扫描的行数和访问类型

在explain中的type列反应了访问类型。从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。

一般MySQL有三种方式应用WHERE条件，从好到坏：

• 在索引中使用WHERE条件过滤不匹配的记录， 这是在存储引擎层完成的。
• 使用索引覆盖（在extra列种出现了using index）来返回记录， 直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务层完成的，但是无须回表查询记录。
• 从数据表中返回数据，然后过滤不满足的条件记录（在extra中出现using where）。这是在MySQL服务层完成。先从数据表读取记录然后过滤。

如果发现查询需要扫描大量的数据但是只返回少数的行，通常可以有以下技巧优化：

• 使用索引覆盖扫描， 把所需要的列都放到索引中，这样存储引擎无须回表获取对应的行。
• 改变库表结构。
• 重写这个复杂的查询， 让MySQL优化器能够以更优化的方式执行这个查询。

重构查询的方式

• 一个复杂的查询还是多个简单的查询
• 切分查询
• 分解关联查询

分解查询的方式有如下优势：

• 让缓存的效率更高。
• 将查询分解后，执行单个查询可以减少锁竞争
• 在应用层做关联， 可以更容易对数据库拆分， 更容易做到高性能和可扩展。
• 查询本身的效率也可能会提升
• 可减少冗余记录的查询

查询执行的基础

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

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

MySQL客户端和服务器之间的通信协议是“半双工”的， 即在任意时刻， 要么是服务器向客户端发送数据，要么是由客户端想服务器发送数据，这两个动作不能同时发生。
参数max_allowed_packet就很重要。

• 查询状态
  查看当前的状态，最简单是使用SHOW FULL PROCESSLIST命令

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

Query 线程正在执行查询或者正在将结果发送给客户端

Locked 在MySQL服务层，该线程正在等待表锁

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

Copying to tmp table[on disk] 线程正在查询，并将结果集复制到一个临时表中， 一般是在Group By操作，要么是做文件排序，或者union操作。

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

sending data 有多种情况： 线程可能在多个状态之间传送数据， 或者生成结果集、或向客户端返回数据。

• 查询优化处理
  • 语法解析器和预处理
  • 查询优化器

有多种原因导致MySQL优化器选择错误的执行计划：

• 统计星系不准确
• 执行计划的成本估算并不等于实际执行成本。
• MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能短，但是MySQL是基于成本模型选择最优的执行计划，有时不是最快的执行方式
• MySQL从不考虑其他并发执行的查询， 这可能会影响到当前查询速度
• MySQL并不是任何时候都是基于成本优化。如存在全文索引的Match()子句，则在全文索引时使用全文索引。有时在使用别的索引和where条件可以远比这种方式快， MySQL仍然会使用对应的全文索引。
• MySQL不会考虑不受其控制的操作成本。

• 查询执行引擎
• 返回结果给客户端

以下是MySQL能够处理的优化类型：

• 重新定义联表的顺序
• 将外连接转化为内链接
• 使用等价变化规则
• 优化count()、min()、max()
• 预估并转化为常数表达式
• 覆盖索引扫描
• 子查询优化
• 提前终止查询
• 等值传播
• 列表in()的比较