高性能MySQL（第三版）第六章：查询性能优化

如何设计最优的库表结构、 如何建立最好的索引， 这些对于高性能来说是必不可少的。 但这些还不够一一还需要合理的设计查询。 如果查询写得很糟糕，即使库表结构再合理、 索引再合适， 也无法实现高性能。

6.1 为什么查询速度会慢

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

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

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

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

了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。

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

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

• 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行或列。
• 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

有些查询会请求超过实际需要的数据， 然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担， 并增加网络开销。另外也会消耗应用服务器的CPU 和内存资源。 一些经典案例：

• 查询不需要的记录
  一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。
• 多表关联时返回全部列
  查询所有在电影Academy Dinosaur中出现的演员，千万不要按下面的写法编写查询：

SELECT * FROM sakila.actor INNER JOIN sakila.film_actor USING(actor_id) INNER JOIN 
sakila.film USING(film_id) 
where sakila.film.title = 'Academy Dinosaur';

正确的方式应该是像下面这样只取需要的列：

SELECT sakila.actor.* FROM sakila.actor ....;

• 总是取出全部列
  每次看到SELECT *的时候都需要要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必须的。取出全部的列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU消耗。 查询返回超过需要的数据也不是坏事。这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性，如果清楚这样做的性能影响，那么这种做法也是值得考虑的。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能有更有好处。
• 重复查询相同的数据
  初次查询时将数据缓存，在需要时在缓存中取出来。

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

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多 的数据。对于MySQL, 最简单的衡量查询开销的三个指标如下：

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

没有哪个指标能够完美地衡量查询的开销，但它们大致反映了MySQL在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。 这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间
响应时间是两个部分之和：服务时间和排队时间。 服务时间是指数据库处理这个查询真正花了多长时间。 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等I/O操作完成，也可能是等待行锁，等等。遗憾的是，我们无法把响应时间细分到上面这些部分，除非有什么办法能够逐个测量上面这些消耗，不过很难做到。 一般最常见和重要的等待是I/O和锁等待，但是实际情况更加复杂。

扫描的行数和返回的行数

分析查询时，查看该查询扫描的行数是非常有帮助的。 这在一定程度上能够说明该查询找到需要的数据的效率高不高。

对于找出那些 “糟糕” 的查询，这个指标可能还不够完美， 因为并不是所有的行的访问代价都是相同的。 较短的行的访问速度更快， 内存中的行也比磁盘中的行的访问速度要快得多。

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

扫描的行数和访问类型

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。 MySQL有好几种访问方式可以查找并返回一行结果。 有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。

在EXPLAIN语句中的type列反应了访问类型。 访问类型有很多种，从全表扫描到索引扫描、 范围扫描、 唯一索引查询、 常数引用等。 这里列的这些，速度是从慢到快，扫描的行数也是从小到大。 你不需要记住这些访问类型，但需要明白扫描表、 扫描索引、 范围访问和单值访问的概念。

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。

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

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

MySQL不会告诉我们生产结果实际上需要扫描多少行数据，而只会告诉我们生产结果时一共扫描了多少行数据。

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

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

6.3 重构查询的方式

• 确定一个复杂查询还是多个简单查询更加有效
• 切分查询
• 分解关联查询

删除旧的数据是一个很好的例子。如果只用一条语句一次性执行一个大的删除操作，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源、阻塞很多小的但重要的查询。将一个大的删除操作分解成多个较小的删除操作可以将服务器上原本一次性的压力分散到多次操作上，尽可能小地影响MySql性能，减少删除时锁的等待时间。同时也减少了MySql主从复制的延迟。
另一个例子是分解关联查询，即对每个要关联的表进行单表查询，然后将结果在应用程序中进行关联。
将一个关联查询拆解成多个单表查询有如下优点：
1.让缓存的效率更高。如果缓存的是关联查询的结果，那么其中的一个表发生变化，整个缓存就失效了。而拆分后，如果只是某个表很少的改动，并不会破坏所有的缓存。
2.可以减少锁的竞争。
3.更容易对数据库进行拆分，更容易做到高性能和可扩展。
4.查询本身的效率也有可能会有所提升。例如上面用IN()代替关联查询比随机的关联更加高效。