第6章 查询性能优化（一）

对于高性能来说，如何设计最优的库表结构、如何建立最好的索引、合理的设计查询，都是必不可少的

6.1 为什么查询速度会慢

查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

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

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

最基本的原因是访问的数据太多。

1. 确认应用程序是否在检索大量超过需要的数据
2. 确认MySQL服务器是否在分析大量超过需要的数据行

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

查询不需要的记录
一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。
多表关联时返回全部列
总是取出全部列
一些DBA是严格禁止SELECT * 的写法的
重复查询相同的数据

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

对于MySQL，最简单的衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

响应时间
响应时间是两个部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁，等等。
扫描的行数和返回的行数
扫描的行数和访问类型
在EXPLAIN语句中type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。
一般MySQL能够使用如下三种方式

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

可以通常尝试下面的技巧去优化它：

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

6.3 重构查询的方式

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

MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。MySQL内部每秒都能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。

6.3.2 切分查询

删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大地语句一次性完成地话，则可能需要一次锁定很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能地影响MySQL性能，同时还可以减少MySQL复制地延迟。

6.3.3 分解关联查询

分解关联查询的优势:

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应地结果对象。
• 将查询分解后，执行单个查询可以减少锁地竞争
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身效率也可能会有所提升。
• 可以减少冗余记录地查询。
• 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL地嵌套循环关联。某些场景地哈希关联地效率要高很多。

6.4 查询执行的基础

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

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

MySQL客户端和服务器之间的通信协议是“半双工”的。这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。
一般服务器 响应给用户的数据通常很多，由多个数据包组成。

=未完待续=