六、查询性能优化

查询优化、索引优化、库表结构优化需要齐头并进，一个不落

6.1 为什么查询速度会慢

真正重要的是响应时间。如果把查询看作是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

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

查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：

1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列
2. 确认MySQL服务器层是否在分析大量超过需要的数据量

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

查询不需要的记录

多表关联时返回全部列

总是取出全部列

重复查询相同的数据

2）MySQL是否在扫描额外的记录

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

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

响应时间

响应时间 = 服务时间 + 排队时间

扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的，但实际情况这种 "美事" 并不多。在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一会，一般在1:1和10:1之间，不过有时候这个值也可能非常大。

扫描的行数和访问类型

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

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

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

如果发现查询需要扫描大量数据但只返回少数行，可以使用下面的技巧去优化它：

• 使用索引覆盖扫描，把所有需要的列都放到索引中
• 改变库表结构
• 重写这个复杂的查询

6.3 重构查询的方式

可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。

1）一个复杂查询还是多个简单查询

传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。

MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快得多，无论是带宽还是延迟。

MySQL内部每秒能够扫描内存中上百万的数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。别害怕这样做，好好衡量一下这样做是不是减少工作量。

2）分解关联查询

可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联

事实上，用分解关联查询的方式重构查询有如下的优势：

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

6.4 查询执行的基础

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

MySQL客户端和服务器之间的通信协议是半双工的

换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。客户端像是 "从消防水管喝水"

2）查询状态

show full processlist命令

• sleep 线程正在等待客户端发送新的请求
• query 线程正在查询或者正在将结果发送给客户端
• locked 在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中
• analyzing and statistics 线程正在收集存储引擎的统计信息，并生成查询的执行计划
• copying to tmp table [on disk] 线程正在执行查询，并且将其结果都复制到一个临时表中，这种状态一般要么是在做 GROUP BY 操作，要么是文件排序操作，或者是 UNION 操作。如果这个状态后面还有 "on disk" 标记，那表示 MySQL 正在将一个内存临时表放到磁盘上
• Sorting result：线程正在对结果集进行排序
• Sending data：这表示多种情况，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

6.7 优化特定类型的查询

count()的作用

可以统计某个列的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL），如果在count()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数（不是NULL）

优化关联查询

• 确保ON或者USING子句中列上有索引
• 确保任何的GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列
• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方

优化LIMIT分页

尽可能地使用索引覆盖扫描

优化UNION查询

除非确实需要服务器消除重复行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候这样做是没有必要的。