查询性能优化（高性能MySQL读书笔记）

优化数据访问

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，或访问了太多的列。

2. 确认MySQL服务器层是否分析大量找过需要的数据行。

向数据库请求不需要的数据

• 查询不需要的记录

• 多表关联时返回全部列

• 总是取出全部列

• 重复查询相同的数据

MySQL扫描额外的记录

衡量查询开销的指标：

• 响应时间

  服务时间和排队时间之和。

  服务时间：数据库处理这个查询真正花了多长时间。

  排队时间：服务器因为等待某些资源而没有真正执行查询的时间（例如：I/O操作、行锁等）。

• 扫描的行数和返回的行数

type对应了访问的类型：

从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。

Using where，从好到坏：

• 在索引中使用WHERE条件来过滤不匹配的记录。在存储引擎层完成。

• 使用索引覆盖扫描（Extra列中出现了 Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果。在MySQL服务器层完成，无须回表。

• 从数据表中返回数据，然后过滤不满足条件的记录（Extra列中出现Using where）。在MySQL服务器层完成，MySQL需要先从数据表中读出记录然后过滤。

重构查询的方式

• 一个复杂查询还是多个简单查询

• 切分查询。大查询切分成小查询，分而治之。

• 分解关联查询。

  1. 让缓存的效率更高。

  2. 将查询分解后，执行单个查询可以减少锁的竞争。

  3. 查询本身效率也可能会有所提升。例如：使用in() 替代关联查询。

  4. 减少冗余记录的查询，在应用层做关联查询。

  5. 在应用中实现了哈希关联，而不是MySQL嵌套循环关联。

查询执行的基础

查询执行路径

1. 客户端发送一条查询给服务器。

2. 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。

4. MySQL 根据优化器生成的执行计划，调用存储引擎的API来执行查询。

5. 将结果返回给客户端。

客户端/服务器通信协议

MySQL客户端和服务器之间的通信协议是半双工的。任意一个时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据。

客户端使用一个单独的数据包将查询传给服务器。max_allowed_packet限制了服务端接收的数据大小，如果超过会抛出异常。客户端一旦发送了请求就只能等待结果。

服务器想给给客户端的数据通常很多，由多个数据包组成。当服务器开始响应客户端的时候，客户端必须完整地接收整个返回结果。当服务器生成第一个结果的时候就已经向客户端发送。等所有数据都已经发送给客户端才能释放这条查询所占用的资源