高性能 MySQL（八）：通过优化数据访问，来解决慢查询

❤️ 个人主页：水滴技术
🌸 订阅专栏：高性能 MySQL
🚀 支持水滴：点赞👍 + 收藏⭐ + 留言💬

系列文章目录

🔥 高性能 MySQL（一）：逻辑架构

🔥 高性能 MySQL（二）：并发控制（锁）

🔥 高性能 MySQL（三）：事务与锁详解

🔥 高性能 MySQL（四）：多版本并发控制（MVCC）

🔥 高性能 MySQL（五）：设计表结构时，如何选择数据类型会更高效？

🔥 高性能 MySQL（六）：索引类型

🔥 高性能 MySQL（七）：11个高性能的索引策略

---

---

前言

大家好，我是水滴~~

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

---

一、为什么查询速度会慢

如果把查询看作是一个任务，那么它由一系列子任务组件，每个子任务都会消耗一定的时间。如果想要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

通常来说，一个查询的生命周期，按顺序大致分为：从客户端，到服务端，然后在在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括大量为了检索数据，而到存储引擎的调用，以及调用后的数据处理，包括排序、分组等。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作。

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

二、优化数据访问

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，可以通过下面两个步骤来分析。

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

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

下面列出一些典型的案例：

1.1 查询不需要的记录

一个常见的案例是，当需要获取最新一条数据时，一些开发者会按时间倒序查询出所有数据，并返回给应用程序，然后应用程序再获取结果集中的第一条。

select * from student order by create_time desc;

最简单有效的解决方法是，在查询后面加上 LIMIT。

select * from student order by create_time desc limit 1;

1.2 多表关联时返回全部列

比如我们要查出所有“软件工程2101班”的所有学生，下面写法是不推荐的：

SELECT
  * 
FROM
  student s
  INNER JOIN class c ON s.class_id = c.id 
WHERE
  c.NAME = '软件工程2101班';

该查询会返回这两个表的全部数据列。正确的方式应该像下面这样，只取需要的列：

SELECT
  s.* 
FROM
  student s
  INNER JOIN class c ON s.class_id = c.id 
WHERE
  c.NAME = '软件工程2101班';

1.3 总是取出全部列

每次看到select *的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必需的。

取出全部的列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的 I/O、内存和 CPU 的消耗。

当然，使用select *也并不总是坏事。在很多案例当中，这种方式能够提高相同代码片段的复用性，简化了开发。如果能够清楚这样做的性能影响，也是值得考虑的。

1.4 重复查询相同的数据

在一些情况下，需要不断地重复执行相同的查询，然后每次返回完全相同的数据。例如，在用户评论的地方需要查询用户头像的 URL，那么用户多次评论的时候，可能就会反复查询这个数据。像这种应用场景，一般通过缓存技术来避免重复查询相同的数据。

2. MySQL 是否在扫描额外的记录

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

2.1 响应时间

响应时间是两个部分之和：服务时间和排队时间。

• 服务时间是指数据库处理这个查询真正花了多长时间。
• 排队时间是指服务器因为等待某些资源而没有真正执行查询的时间（例如等待 I/O 操作完成、等待行锁等等）

当我们看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是否是一个合理的值。

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

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

较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。

理想情况下，扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多。

2.3 扫描的行数和访问类型

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

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

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