MySQL 如何分析查询性能

原作者: [db:作者] 来自: [db:来源] 收藏邀请

查询优化、索引优化和表设计优化是环环相扣的。如果你有丰富的编写MySQL查询语句的经验，你就会知道如何设计表和索引来支持有效的查询。同样的，知晓表设计同样有助于了解表结构如何对查询语句产生影响。因此，即便表设计和索引都设计得很好，但如果查询语句写得很糟糕，那查询的性能也会很糟糕。

在尝试编写快速的查询语句前，务必记住快速都是基于响应时间进行评估的。查询语句是一组由多个子任务组成的大任务，每一个子任务都会消耗时间。为了优化查询，我们需要尽可能地减少子任务的数量，或者让子任务执行得更快。注：有些时候我们也需要考虑查询对系统其他查询的影响，在这种情况下，还需要尽可能地减少资源消耗。 _ 通常，我们可以认为查询的生命周期贯穿于客户端到服务端的整个交互时序图中，包括了查询语句解析、查询计划、执行过程和数据返回到客户端。执行是查询过程中最为重要的一环，包括了从存储引擎获取数据行而发起的大量调用，以及获取数据后的处理，例如分组和排序。

当完成所有这些任务后，查询还会在网络传错、CPU处理、数据统计和策略规划、等待锁、从存储引擎获取数据行的操作中消耗时间。这些调用会在内存操作、CPU操作和I/O操作中消耗时间。在每一种情况中，如果这些操作被滥用、执行次数过多、或过慢，就会导致额外的时间开销。查询优化的目标是避免这些情况——通过消除或减少操作，或者让操作运行更快。

需要注意的是，我们没法绘制一个精确的查询生命周期图，我们的目的是展示理解查询生命周期的重要性，并思考这些环节的耗时。有了这个基础，就能够着手去优化查询语句。

慢查询基础：优化数据获取

查询性能差的最基础的原因是处理了太多的数据。有些查询必须从大量数据中进行筛选，这种情况就没法优化。但这是不太正常的情况。大部分糟糕的查询可以通过访问更少的数据进行优化。下面的两个步骤对分析性能差的查询十分有用：

找出应用是不是获取了你需要之外的数据。通常这意味着应用获取了太多的数据行或数据列。
找出MySQL服务器是不是分析了超过需要的行。

检查是否向数据库请求了不必要的数据

有些查询会向数据库服务器请求所需要的数据，然后将这些数据丢弃。这会增加MySQL服务器的工作、加重网络负荷、消耗更多内存和应用服务器的CPU资源。下面是一些典型的错误：

获取不需要的数据行：一个常见的误区是假设MySQL只提供需要的结果，而不是计算和返回全部的结果集。通常这种错误发生在熟悉其他数据库系统的人身上。这些开发者习惯于使用返回很多行的SELECT语句，然后从中取出前N行，之后不再使用返回的结果集（例如从一个资讯网站获取最近的100篇文章，然后在前端仅仅展示其中的10条）。他们会认为MySQL在拿到10行数据后就会停止查询，而实际MySQL会获取完整的数据集合。然后，客户端或获取全部的数据再将其中的大部分丢弃。最佳的解决方案是在查询中加上LIMIT条件。
在一个多表联合查询终获取全部列：如果你需要获取恐龙时代这部电影的全部演员，不要像下面那样写你的SQL语句：

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

这会返回参与联合查询的三张表的全部列。更好的做法是，像下面那样写：

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

获取全部数据列：在你看到SELECT *这样的查询时，一定要保持怀疑：真的需要全部的列吗？很可能不是的。获取全部的数据列会让覆盖索引失效、增加I/O负担、内存消耗和CPU负荷。有些DBA直接因为这个禁用SELECT *，并且可以减少人员修改表的列后引发的问题。当然，请求不必要的数据并不总是糟糕。在调查中发现，这种方式可以简化开发工作，因为这样可以提高代码的复用性。只要你知道这会影响性能，那会是一个正当的理由。同样的，如果在应用中使用了某些缓存机制，也会提高缓存的命中率。获取和缓存全部对象可以通过运行多个获取部分对象的独立的查询来处理会更好。
重复获取相同数据：如果粗心的话，很容易在应用中编写获取相同数据的代码。例如，如果你要在评论列表中展示用户个人信息中的头像，你可能再每一条评论都获取一次。更有效的方式是第一次获取后缓存起来直接在评论列表使用。

检查MySQL是不是处理了过多的数据

一旦确定了查询语句没有获取不必要的数据，就可以查找那些在返回结果前处理过多数据的查询。在MySQL中，最简单的查询消耗标准是：

响应时间
处理的数据行数量
返回的数据行数量

这些标准没有一个是完美的查询性能评估手段，但它们大致反映了MySQL执行查询语句时在内部处理过程中获取的数据量和查询运行的速度。这三个标准都在慢查询日志中记录，因此从慢查询日志中去发现数据处理过多的查询是查询优化的最佳实践方式。

响应时间首先，注意查询响应时间是我们看到的一个表象。实际上，响应时间比我们想象的要更为复杂。响应时间由两部分组成：服务时间和队列时间。服务时间是服务端实际处理查询的时间。队列时间是服务端并没有真正执行查询的那部分时间——它在等待某些资源，例如I/O操作的完成、行锁释放等等。问题在于，你没法准确将响应时间拆分成这两部分——除非你能够单独测量这两部分的时间，而这是很难做到的。最常见和最重要的情形是I/O阻塞和等待锁，但不是百分之百都是这样。

结果就是，响应时间在不同负荷情况下并不是一成不变的。其他的因素，例如存储引擎锁、高并发和硬件都会影响响应时间。因此，当检查响应时间的时候，首先要决定这个响应时间是不是仅仅是这个查询引起的。可以通过计算查询的快速上限估计（QUBE）方法来评估其响应时间：通过检查查询计划和使用的索引，来决定需要的顺序和随机I/O访问操作，然后乘以机器的硬件执行每次操作的时间来评估。通过将全部的时间求和可以评估查询响应慢是因为查询本身引起的还是其他原因。

处理和返回的数据行数量在分析查询语句时，思考处理行的数量十分有用，因为这样可以直观地知道查询是如何获取我们所需的数据。然而，这对查找糟糕的查询并不是完美的测量工具。并不是所有的行访问都是一致的。更少的行访问速度更快，而从内存中获取数据行比在磁盘获取要快很多。

理想情况下，处理的数据行和返回的数据行是相等的，但是实际上很少会这样。例如，使用联合索引构建返回行时，服务端必须从多个行中获取数据以产生返回的行数据。处理的数据行和返回的数据行的比例通常很小，在1:1到10:1之间，但有时候可能是更大的数量级。

数据行处理和获取类型

当思考查询的代价时，可以考虑从数据表获取单独一行的代价。MySQL使用多种获取方法去查找和返回一行数据。有些需要处理多行，而有些则可能不需要检查直接得到返回结果。

获取数据的方法在EXPLAIN输出结果的type列。包括了全表扫描、索引扫描、范围扫描、唯一索引查找和常量。由于数据读取量依次减少，因此上述的每一种方法都比它之前的要快。我们不需要记住获取类型，但需要理解其中的基本概念。

如果没有好的获取类型，最佳解决问题的方式是增加一个合适的索引。索引使得MySQL检查更少的数据，从而更有效地查询数据行。例如，以下面的简单查询为例：

EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

这个查询会返回10行数据，然后EXPLAIN指令显示了MySQL在idx_fk_film_id索引上使用了ref类型执行查询语句。

***********************1. row************************
id: 1
select_type: SIMPLE
table:film_actor
type: ref 
possile)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 DROP KEY idx_fk_film_id;
EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

***********************1. row************************
id: 1
select_type: SIMPLE
table:film_actor
type: ALL 
possile)keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5073
Extra: Using where

如同预期的那样，获取类型变成了全表扫描（ALL），MySQL估计需要处理5073行数据才能完成查询。在Extra列中的Using where显示MySQL服务器使用了WHERE条件来丢弃存储引擎读取的其他不符合条件的数据。通常，MySQL会在下面三种方式中使用WHERE条件，效果依次是从好到差：

通过索引查找操作去除不匹配的数据行，这发生在存储引擎层；
使用覆盖索引（在Extra列显示是Using index）去避免数据行访问，并在获取到结果后将不符合条件的数据过滤掉。这发生在服务器层，但不需要从数据表读取数据行。
从数据表获取数据行，然后在过滤掉不匹配的数据（在Extra列显示为Using where）。这发生在服务器层，并且需要在过滤数据前从数据表读取数据行。

下面的例子演示了有好的索引的重要性。好的索引有助于使用好的数据获取类型并且只需要处理所需要的数据行。然而，添加索引并不总是意味着MySQL获取和返回的数据行是一致的。例如，下面的COUNT()聚合方法。

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

这个查询只返回200行，但是在构建返回结果集前需要读取数千行数据。这种查询语句，即便有索引也无法减少需要的处理的数据行数。

不幸的是，MySQL并不会告知获取了多少行来构建返回结果集，它仅仅告知获取的总行数。很多行通过WHERE条件过滤掉了，而对返回结果集没有任何作用。在前面的例子中，移除sakila.film_actor索引后，查询获取了数据表的全部行，但是只从中取了10条数据作为结果集返回。理解服务器获取的数据行数量和返回的数据行数量有助于理解查询本身。如果发现了需要获取大量数据行而只是在结果使用很少的行，可以通过下面的方式修复这个问题：