查询性能优化(一)

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

查询优化、索引优化、库表结构优化需要齐头并进，一个不落。在获得编写 MySQL 查询的经验的同时，也将学习到如何为高效的查询设计表和索引。
同样的，也可以学习到在优化库结构时会影响到哪些类型的查询。

为什么查询速度会慢

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

MySQL 在执行查询的时候有哪些子任务，哪些子任务运行的速度很慢？
通常来说，查询的生命周期大致可以按照顺序来看: 从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。
其中 "执行" 可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。

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

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。
优化查询的目的就是减少和消除这些操作所花费的时间。

再次申明一点，对于一个查询的全部生命周期，上面列的并不完整。这里知识想说明:
了解查询的生命周期、清楚查询的时间消耗情况对于优化查询有很大的意义。

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

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

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

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

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

这里有一些典型案例:

查询不需要的记录

一个常见的错误是常常会误认为 MySQL 会只返回需要的数据，实际上 MySQL 却是先返回全部结果集再进行计算。我们经常会看到一些了解其他数据库系统的人会设计出这类应用程序。
这些开发者习惯使用这样的技术，先使用 SELECT 语句查询大量的结果，然后获取前面的 N 行后关闭结果集(例如在新闻网站中取出 100 条记录，但是只是在页面上显示前面 10 条)。
他们认为 MySQL 会执行查询，并只返回他们需要的 10 条数据，然后停止查询。
实际情况是 MySQL 会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。
最简单有效的解决方法就是在这样的查询后面加上 LIMIT

多表关联时返回全部列

如果你想查询所有在电影 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';

这将返回这三个表的全部数据列。正确的方式应该是像下面这样只取需要的列:

1	`SELECT sakila.actor.* FROM sakila.actor...;`

总是取出全部列

每次看到 SELECT * 的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必需的。
取出全部列，会让优化器无法完成索引覆盖扫描这类优化，行会让服务器带来额外的 I/O、内存和 CPU 的消耗。
因此，一些 DBA 是严格禁止 SELECT * 的写法的，这样做有时候还能避免某些列被修改带来的问题。

当然，查询返回超过需要的数据也不总是坏事。在我们研究过的许多案例中，人们会告诉我们说这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性，如果清楚这样做的性能影响，那么这种做法也是值得考虑的。
如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。
获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处

重复查询相同的数据

如果你太不小心，很容易出现这样的错误 -- 不断地重复执行相同的查询，然后每次都返回完全相同的数据。
例如，在用户评论的地方需要查询用户头像的 URL，那么用户多次评论的时候，可能就会反复查询这个数据。
比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

MySQL 是否在扫描额外的记录

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

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

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

响应时间

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

所以在不同类型的应用压力下，响应时间并没有什么一致的规律或者公式。
诸如存储引擎的锁(表锁、行锁)、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。
所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同。

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。
实际上可以使用 "快速上限估计" 法来估算查询的响应时间，概括地说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多个顺序和随机 I/O，再用其乘以再具体硬件条件下一次 I/O 的消耗时间。
最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值

扫描的行数和返回的行数

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

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

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

扫描的行数和访问类型

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

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

如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。现在应该明白为什么索引对于查询优化如此重要了。
索引让 MySQL 以最高效、扫描行数最少的方式找到需要的记录

例如，我们看看示例数据库 Sakila 中的一个查询案例:

1	`SELECT * FROM sakila.film_actor WHERE film_id = 1;`

这个查询将返回 10 行数据，从 EXPLAIN 的结果可以看到，MySQL 在索引 idx_fk_film_id 上使用了 ref 访问类型来执行查询

EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G

id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: const
rows: 10
Extra:

EXPLAIN 的结果也显示 MySQL 预估需要访问 10 行数据。换句话说，查询优化器认为这种访问类型可以高效地完成查询。如果没有合适的索引会怎样？
MySQL 就不得不使用一种更糟糕的访问类型，下面我们来看看如果我们删除对应的索引再来运行这个查询:

ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film

ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;

EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G

id: 1
select_type: SIMPLE
table: film_actor
type: ALL
possible_keys: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where

正如我们预测的，访问类型变成了一个全表扫描(ALL)，现在 MySQL 预估需要扫描 5073 条记录来完成这个查询。这里的 "Using Where" 表示 MySQL 将通过 WHERE 条件来筛选存储引擎返回的记录

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

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

上面的例子说明了好的索引多么重要。好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。
但也不是说增加索引就能让扫描的行数等于返回的行数。例如下面使用聚合函数 COUNT() 的查询:

1	`SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;`

这个查询需要读取几千行数据，但是仅返回 200 行结果。没有什么索引能够让这样的查询减少需要扫描的行数。

不幸的是，MySQL 不会告诉我们生成结果实际上需要扫描多少行数据(例如关联查询结果返回的一条记录通常是由多条记录组成)，而只会告诉我们生成结果时一共扫描了多少行数据。
扫描的行数中的大部分都很可能是被 WHERE 条件过滤掉掉的，对最终的结果集并没有贡献。
在上面的例子中，我们删除索引后，看到 MySQL 需要扫描所有记录然后根据 WHERE 条件过滤，最终只返回 10 行结果。理解一个查询需要扫描所有记录然后根据 WHERE 条件过滤，最终只返回 10 行结果。
理解一个查询需要扫描多少行和实际需要使用的行数需要先去理解这个查询背后的逻辑和思想。

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

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

重构查询的方式

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果 -- 而不一定总是需要从 MySQL 获取一模一样的结果集。
有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。

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

设计插叙你的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。
在传统实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情

但是这样的想法对于 MySQL 并不适用，MySQL 从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，无论是带宽还是延迟。
在某些版本的 MySQL 上，即使在一个通用服务器上，也能够运行每秒超过 10 万的查询，即使是一个千兆网卡也能轻松满足每秒超过 2000 次的查询。所以运行多个小查询现在已经不是大问题了。

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

不过，在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。
例如，我们看到有些应用对一个数据表做 10 次独立的查询来返回 10 行数据，每个查询返回一条结果，查询 10 次！

切分查询

有时候对于一个大查询我们需要 "分而治之"，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

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

1	`DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);`

那么可以用类似下面的办法来完成同样的工作:

rows_affected = 0
do {
    rows_affected = do_query(
        "DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH LIMIT 10000"
    )
} while rows_affected > 0

一次删除一万行数据一般来说是一个比较高效而且对服务器(Percona Toolkit 中的 pt-archiver 工具就可以安全而简单地完成这类工作)影响也最小的做法(如果是事务型引擎，很多时候小事务能够更高效)。
同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间

分解关联查询

很多高性能的应用都会对关联插叙能进行分解。
简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。例如，下面这个查询:

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

可以分解成下面这些查询代替:

SELECT * FROM tag WHERE tag='mysql';

SELECT * FROM tag_post WHERE tag_id=1234;

SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

到底为什么要这样做？乍一看，这样做并没有什么好处，原本一条查询，这里却变成多条查询，返回的结果又是一模一样的。事实上，用分解关联查询的方式重构查询有如下的优势:

让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面的查询中的 tag 已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了 ID 为 123、567、9098 的内容，那么第三个查询的 IN() 中就可以少几个 ID。另外，对 MySQL 的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了
将查询分解后，执行单个查询可以减少锁的竞争
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
查询本身效率也可能会有所提升。这个例子中，使用 IN() 代替关联查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的关联要更高效
可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用 MySQL 的嵌套循环关联。某些场景哈希关联的效率要高很多。

在很多场景下，通过重构查询将关联放在应用程序中将会更加高效，这样的场景有很多，比如: 当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同的 MySQL 服务器上的时候、当能够使用 IN() 的方式代替关联查询的时候、当查询中使用同一个数据表的时候