查询性能的优化 - 查询慢的基础知识:优化数据访问

 

一个查询执行的不是很理想，大部分原因都是由于数据量过大。很多查询都筛选了大量的数据并且并没有什么作用。其实大部分不好的语句都可以访问更少的数据。我们可以通过两个步骤来分析性能差的查询语句。

 

 

1. 找出你的应用程序所获取的数据是否超出了你的需求。意思就是它访问了过多的数据，但是它也可能访问了过多的列。
2. 找出MySQL服务器是否分析了过多的行。

从数据库获得的数据是否超出了你的需要？

一些查询语句获取了很多不需要的数据，之后再把它们扔掉。这就需要MySQL服务器做额外的工作，加重了网络的负担，并且消耗了应用服务器的内存和CPU资源。

下面都是一些常见的错误。

获取了不需要的行

一个最常见的错误就是MySQL要提供所需的数据，而不是计算并且返回所有的数据集。我们看到在熟悉其他数据库的人设计的应用程序中，这种错误常常发生。这些开发人员常常用SELECT获取很多行，之后再取前N行，并且关闭了数据集。他们可能考虑到MySQL会提供10行并且中止了查询的执行，但是MySQL真正所做的时候生成了完整的结果集。客户端库获取了所有的行并且抛弃了很多行。最好的方案是在查询后加上LIMIT条件。

在表的多连接查询中获取所有的列

如果你想获取所有出现在Academy Dinosaur影片中的演员，不要写如下的语句

mysql> 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';

这个查询语句会返回这三个表的所有列。正确的语句如下：

mysql> SELECT sakila.actor.* FROM sakila.actor...;

获取所有的列

当你看到select *的时候一定要有所怀疑。你真的需要所有的列么？也许不是。获取所有的列可能会使一些优化失效，比如覆盖索引，同样的会增加I/O，内存，CPU的消耗。

一些DBA因为这个原因已经禁用了SELECT *，并且当修改了表的列也会降低了发生错误的几率。

当然，获取一些超出你需要的数据也并不总是不好的。在许多我们研究的案例中，人们告诉我们这种浪费资源的方法可以简化一些开发，它能让开发者在不同的地方使用相同的代码。这一点是可以考虑的，只要你能明白性能的消耗在哪就行了。如果你在程序中应用了一些缓存方案或者有一些其他好的想法，获取不需要的数据还是很有用的。获取和缓存所有的对象可能要比许多单独获取一部分数据要更好些。

MySQL是否检查了过多的数据？

一旦你确定了你的查询获取了所需的数据，你就可以查看查询是否检查了太多的数据。在MySQL中，最简单的消耗指标是：

• 执行时间
• 所检查的行数
• 返回的行数

没有一个指标可以完美的衡量查询的消耗。但是它们能反映出MySQL执行一个查询所访问的数据并且能大约的推算出查询运行的速度。这三个指标都记录在慢查询日志中，因此要想知道是否检查了过多的数据，查看慢查询日志是最好的方法。

 

执行时间

在第二章我们已经讨论过了，在MySQL5.0以及之前的版本中，慢查询日志有很多的限制。包括了缺乏更细颗粒度的日志。

幸运的是，有许多补丁可以使你记录和测量微秒级别的查询。它们都包含在了MySQL5.1服务器之中了，但是如果你用的是老版本的话，只能打补丁了。要小心的是，不要过度的看重执行时间。看这个指标的原因是它是一个目标的指标，但是在变化的条件下它并不是一直不变的。其他的因素-比如存储引擎的锁，高并发，并且还有硬件-都可能影响到执行的时间。这个指标非常有助于f发现那些对应用响应时间或者服务器取读取的影响的查询语句，但是它不能给出实际的执行时间。

 

 

检查和返回的行

当分析查询语句时，考虑检查的行数是非常有用的。因为你能查看查询是怎样找到你所需要的数据的。然而，就像执行时间，这个指标来发现不好的语句并不是很完美。并不是所有的行的访问是相同的。短的行访问时间很快，并且从内存中获取行要比从硬盘中更快。

 

理想的情况下，所检查的行数和返回的行数相同，但是在实际中并不太可能。举个例子，当join构建的行时候，很多行一定被访问用来在结果集中生成行。检查和返回行的比例一般来说都很小，在1：1和10：1之间，但是有的时候也可能会很大。

 

行的检查和访问类型

当你思考一个查询的消耗，要考虑在一个表中查找一个单独行的消耗。MySQL会使用许多访问方法来找到和返回一行。一些查询检查很多的行，但是其他的可能没有检查任何行就会生成结果。

 

这个访问类型在EXPLAIN输出的TYPE列中显示。这个访问类型从全表扫描（full table scan）到，索引扫描（index scans）,范围扫描(range scans),唯一索引查找（unique index lookups），以及常量（constants）。它们的速度是依次递增的，因为它需要访问很少的数据。你不比记下这些类型，但是你应该明白扫描表，扫描一个索引，范围访问，以及单值访问的基本概念。

 

如果语句的访问类型不好，最佳的方法就是添加一个合适的索引。我们已经在前一章讨论了索引。现在你应该明白为什么索引对于查询优化如此重要了吧。索引可以让MySQL更高效的找到所需的行并且检查更少的数据。

 

让我们看看Sakila数据库的例子：

mysql> SELECT * FROM sakila.film_actor WHERE film_id = 1;

 

这个查询会返回10行，并且EXPLAIN显示了MySQL使用了ref访问类型。

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

*************************** 1. row ***************************

           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可能会使用一个性能差点的访问类型。让我们先删除索引，再执行这个查询。

mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;

mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;

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

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: film_actor

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 5073

        Extra: Using where

 

和预想的一样，访问类型已经变为全表扫描了（All），并且MySQL检查了5073行来满足这个查询。“Using Where”的意思是，在存储引擎读取行之后，MySQL服务器使用了WHERE条件了过滤行。

 

一般情况下，MySQL以三种方式来应用一个WHERE，顺序为最佳到最差。

• 在索引查找操作上来应用条件，便于去掉不匹配的行。这个操作在存储引擎一层。
• 使用一个覆盖索引（在Extra列显示为“using index”）来避免行的访问，并且在从索引获取结果之后，再过滤掉不匹配的行。这发生在服务器层，但是它不需要从表中读取行。
• 从表中获取行，之后过滤掉不符合的行。（Extra为“Using Where”）。这发生在服务器层，并且需要服务器在过滤结果之前从表中读取行。

这个例子证明了有个好的索引设计是多么的重要。好的索引帮助你的查询有好的访问类型并且仅仅检查你需要的行。然而，添加一个索引，并不意味着MySQL访问和返回的行数相同。举个例子，一个查询使用了COUNT()聚合函数。

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

这个查询仅仅返回了200行。但是它需要读取上千行来构建结果集。像这种查询语句，索引就不能降低要检查的行数了。

不幸的是，MySQL不会告诉你它访问了多少行用来构建结果集。它仅仅告诉你它所访问的行数。其中的一些行可能会被WHERE条件过滤并且不会对结果集有什么贡献。在上个例子中，在移除索引之后，这个查询访问了表中的每一行并且WHERE条件除了那10行外已经全部过滤掉。仅仅剩了10行来创建结果集。要明白服务器访问了多少行，还有有多少行对这个查询有用。

如果你发现有很多的行被检查而结果的行数却很少，你需要做如下的修补

• 使用覆盖索引，它们存储这你要的数据，因此存储引擎就没必获取全部的行。
• 改变数据库模型。一个例子就是使用汇总表。
• 重写这个复杂的查询，因此MySQL优化器能更好的进行优化。（以后会详细讨论）