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

悠悠小仙仙

查询性能低下最基本的原因是访问的数据太多。某些查询不可避免的需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化

对于低效的查询，可以通过下面两个步骤进行分析：

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

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

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

让我们来看一些典型案例：

查询不需要的记录

一个常见的错误就是人们会误以为MySQL只会返回需要的数据，实际上MySQL确实先返回全部结果集然后再进行计算。

比如在新闻网站中取出100条记录，但是只在页面上显示前10条。实际情况是MySQL会查询出全部的结果集，客户端的应用程序会在接收全部的结果集数据，然后抛弃其中大部分的数据。

解决这个问题最简单有效的解决方案就是在这样的查询后面加上 LIMIT。

总是取出所有列

每次看到SELECT * 都应该用怀疑的眼光审视，是不是真的需要返回全部的列？

答案很可能不是必须的。取出所有的列，会让优化器无法完成索引覆盖扫描这类的优化，还会为服务器带来额外的I/O、内存和CPU的消耗。因此，一些DBA是严格禁止这样的写法的，这样做有时候还可以避免某些列被修改带来的问题。

当然，查询返回超过需要的数据也不总是坏事。

使用这样有点浪费数据库资源的方式可以简化开发，提高代码片段的复用性。如果清楚这样做的性能影响，那么这种做法也是值得考虑的。

重复查询相同的数据

在编写程序时有时候总会一不小心出现这样的错误——不断的重复执行相同的查询，然后每次都返回完全相同的数据。

比较好的方案是，当初次查询的时候就应该把这个数据缓存起来，只有在需要的时候才从缓存中取出来，这样子性能会更好。

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。

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

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

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

响应时间

响应时间是服务时间和排队时间的和。服务时间指的是数据库处理这个查询真正花了多长时间。排队时间指的是服务器因为等待某些资源而没有真正执行查询的时间——可能等待I/O操作完成，也可能是等待行锁等等。但是我们无法将响应时间细分到上面这些部分，所以响应时间既可能是一个问题的结果也可能是一个问题的原因。

当我们看到一个查询的响应时间时，首先应该判断这个响应时间是否是一个合理的值。概括来讲，我们可以通过了解这个查询需要那些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗加在一起，就可以获得一个大概的参考值来判断当前响应时间是不是一个合理的值。

扫描的行数与返回的行数

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

但是这个指标可能还不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

理想情况下扫描的行数以及返回的行数应该是相同的。

但实际上这样完美的情况并不多见，比如在做关联查询时，服务器需要扫描多行才能生成结果集中的一行。

扫描的行数和访问类型

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

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

如果查询无法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引让MySQL以最高效，扫描行数最少的方式找到需要的记录。

我们看这样一个例子：

SELECT * FROM sakila.file_actor WHERE file_id = 1;

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

EXPLAIN的结果也显示MySQL预估需要访问10行数据。也就是说，查询优化器认为这种访问类型可以高效的完成查询。

我们可以尝试删除索引，这样MySQL就不得不使用更糟糕的访问类型：

删除索引之后，访问类型变成了全表扫描。这里的“Using Where”表示MySQL会通过WHERE条件来筛选存储引擎返回的记录。

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

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

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

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