MySQL 系列教程（笔记）

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

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

  1. 确认应用程序是否在检索大量超过需要的数据。

    这通常意味着访问了太多的行，但有时候也可能是访问了太多的列

  2. 确认 MySQL 服务器层是否在分析大量超过需要的数据行。

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

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

这里有一些典型的案例：

  • 查询不需要的记录

    先使用 SELECT 语句 查询大量的结果，然后 获取前面的 N 行后关闭结果集，例如：在新闻网站中取出 100 条记录，但是值在页面上显示前面 10 条。

    他们会认为 MySQL 会执行查询，并只返回他们需要的 10 条数据，然后停止查询。实际情况是 MySQL 会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。

    最简单有效的方法就是在这样的查询后面加上 limit

  • 多表关联时返回全部列

    只返回需要的数据列

  • 总是取出全部列

    每次看到使用 select * 时，都需要用怀疑的眼光审视，是不是真的需要返回全部的列？取出全部的列，会让 优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的 I/O 、内存和 CPU 的消耗。

    也有获取超过需要的数据的好处，比如能重用代码，简化开发。如果清楚这样做的性能影响，那么这种做法也是值考虑的。

    如果应用程序使用了某种缓存机制，或则有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能更有好处。

  • 重复查询相同的数据

    如果你不太小心，很容易出现这样的错误：不断重复执行相同的查询，然后每次都返回 完全相同的数据。

    比如：在用户评论的地方需要查询用户头像的 URL，那么用户多次评论的时候，可能就会反复查询这个数据。比较好的方案是，首次查询时将整个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。

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

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

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

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

# 响应时间

响应时间只是一个 表面 上的值。但是它任然是最重要的指标，这有点复杂，后面再细说。

响应时间是两个部分之和：

  1. 服务时间

    指数据库处理这个查询真正花了多长时间。

  2. 排队时间

    指服务器因为等待某些资源而没有真正执行查询的时间，可能是 I/O 操作完成，也可能是等待行锁，等等。

我们无法把响应时间细分到上面这些部分，除非有什么办法能测量上面这些消耗，不过很难做到。一般最常见和重要的等待是 I/O 和锁等待，但实际情况更加复杂。

所以在不同类型的应用压力下，响应时间并没有什么一致的规律或则公式。诸如：存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。所以，响应时间既可能是一个问题的结果也可能是一个问题的原因，不同案例情况不同，除非能够使用第 3 章的「单个查询问题还是服务器问题」介绍的技术来确定到底是因还是果。

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

# 扫描的行数和返回的行数

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

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

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

# 扫描的行数和访问类型

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

在 explain 语句中的 type 列反应了访问的类型。访问类型有很多中：

  • 全表扫描
  • 索引扫描
  • 范围扫描
  • 唯一索引查询
  • 常数引用

等等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。

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

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

select *
from sakila.film_actor
where film_id = 1;
1
2
3

该查询返回 10 行数据，MySQL 在索引 idx_fk_film_id 上使用了 ref 访问类型来执行查询

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film_actor NULL ref idx_fk_film_id idx_fk_film_id 2 const 10 100 NULL

explain 的结果也显示预估需要访问 10 行数据，换句话说，查询优化器认为这种访问类型可以高效的完成查询。下面我们来看看没有索引的查询，会是什么计划呢

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;
1
2
3
4
5
6
7
8
9
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film_actor NULL ALL NULL NULL NULL NULL 5462 10 Using where

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

测试完成之后，记得还原回去

alter table film_actor
	add constraint fk_film_actor_film
		foreign key (film_id) references film (film_id)
			on update cascade;
create index idx_fk_film_id
    on film_actor (film_id);
1
2
3
4
5
6

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

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

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

select actor_id, count(*)
from sakila.film_actor
group by actor_id
1
2
3
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE film_actor NULL index PRIMARY,idx_fk_film_id PRIMARY 4 NULL 5462 100 Using index

这个查询只返回 200 行数据，但是要读取 5000 多行数据，没有什么索引能减少这样查询需要扫描的行数。

不幸的是，MySQL 不会告诉我们生成结果实际上需要扫描多少行数据（例如关联查询结果返回的一条记录通常是由多条记录组成），只会告诉我们生成结果时一共扫描了多少行数据。扫描的行数中的大部分都很可能是被 where 条件过滤掉的，对最终的结果集并没有贡献。

在上面的例子中，我们删除索引后，看到需要扫描所有记录然后根据 where 条件过滤，最终只返回了 10 行结果。理解一个查询需要扫描多少行和实际需要使用的行数需要先去理解这个查询背后的逻辑和思想。

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

  • 使用索引覆盖扫描

    把所有需要用到的列都放到索引中，这存储引擎无须回表获取对应行就可以返回结果了

  • 改变库表结构

    例如使用单独的汇总表

  • 重写这个复杂的查询

    让 MySQL 优化器能够以更优化的方式执行这个查询

除了第三个是后续要讨论的问题，前面两个都是前面已经讨论过的了。

← 重构查询的方式 →