MySQL查询性能优化（上）

本是古典何须时尚 2023-01-19 13:51 231阅读 0赞

> 参考文献《高性能MySQL（第三版）》

上一期我们深入理解了MySQL的索引，有了MySQL索引可以一定程度上提高MySQL的查询速度。这一期我们来学习下MySQL查询性能优化的一般方法。

## 1 为什么查询速度会慢？ ##

其实很简单，在一个应用程序中，我们要查询一些数据，通常是从客户端出发，请求经过网络传输到达服务端后，在服务端进行解析，然后把查询命令发送给MySQL，MySQL经过一系列解析、优化等，最终将结果查询出来，返回给客户端，最终给到我们用户。

在这一系列操作里，由于网络时延、CPU、内存、锁竞争、系统调用、上下文切换、存储引擎检索数据触发的一系列操作等，查询的速度或多或少会受到一定的影响，条件不利时，查询速度就会变慢。

大致地知道查询速度受到这些因素影响后，我们就可以找到优化查询速度的一些方向了。

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

查询性能低的**最基本原因**是访问的数据太多，我们可以通过以下两步来分析低效查询：

1.  确认应用程序是否在检索大量超过需要的数据行
2.  确认MySQL服务器层是否在分析大量超过需要的数据行

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

应用程序从MySQL查询请求了较多不需要数据时，这些多余数据其实会在应用程序的逻辑层中被丢弃掉，这种多余的操作会给MySQL服务器带来额外的负担，并增加网络开销，还会消耗应用服务器的CPU和内存资源。以下几种情况均是这种类型：

1.  查询了不需要的记录。像应用程序select \* 时其实并不需要全部的数据，却没有去加limit进行限制，从而把全表的数据都捞出来，显然多此一举。
2.  多表关联时返回全部列。多个表进行关联，像一些没有什么意义的列也全部查出来，也是会影响性能。
3.  总是取出全部的列。像select \* 这类查询，取出全部列，不一定会完成索引覆盖这类优化，会触发各种回表查询，为服务器带来额外的IO、内存和CPU消耗。
4.  重复查询相同的数据。对于每次查询都返回同样结果的这类查询，其实查一遍就够了，把结果存到Redis这类缓存中，减轻MySQL的压力。

### 2.2 MySQL是否扫描了额外的记录 ###

对于MySQL，衡量性能开销的三个指标是：**响应时间、扫描行数、返回行数**。

#### 响应时间 ####

![响应时间的分类][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]

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

理想情况下，扫描的行与返回的行之间的比率通常要小，MySQL额外扫描的记录就少。

#### 扫描的行数与访问类型 ####

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

**使用explain语句中的type列反应了访问类型。** 访问类型有索引扫描、范围扫描、唯一索引查询、常数引用等。

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

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

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

*  使用索引覆盖扫描，把需要查询到的列都放到索引中，这样存储引擎就无须回表查询就可以返回结果
 *  改变库表结构，使用一些汇总表来存储结果，来避免各种联合查询
 *  重写复杂的查询，让MySQL优化器可以以更优化的方式执行这个查询

## 3 重构查询的方式 ##

### 3.1 一个复杂查询or多个简单查询 ###

MySQL其实在设计上是让连接和断开都很轻量级，在返回一个小的查询结果方面很高效。如果想用一个复杂的查询，而这个查询涉及了多个表的关联，那其实性能还远不如将这个查询分解成的多个简单查询。

因此，一般情况下，能用多个简单查询，就不要用一个复杂查询。

### 3.2 切分查询 ###

对于一个大查询可以采用**分而治之**，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

### 3.3 分解关联查询 ###

将MySQL多表关联查询拆分成多个单表查询，然后将查询结果在应用程序逻辑层进行处理，可以提升性能。优势如下：

*  查询分解后，执行单个查询可以减少锁竞争。这是因为复杂的关联查询时间一般较长，事务之间的锁竞争一般会更激烈，锁等待时间一般也会更长
 *  在应用程序逻辑层进行数据结果关联，可以更容易做分库分表、提高性能和扩展性
 *  查询本身效率也会有所提升，单表查询走索引的SQL语句更容易编写、使用in()代替关联查询可以让MySQL按照id顺序进行查询，这会比随机关联更高效（后面会介绍到）
 *  可以减少冗余记录的查询，多表关联查询时可能会重复地访问一部分数据，而应用程序逻辑层关联，只需要MySQL将某部分数据只查询一次返回给应用程序即可
 *  让缓存的效率更高，应用程序逻辑层可以方便地缓存单表查询对应的结果；对于在MySQL的查询缓存而言，如果关联查询中某个表发生了变化，那么查询缓存就失效了

## 4 查询执行的基础 ##

前面讲了这么多关于查询优化的内容，现在我们了解下MySQL执行一个查询的过程：  
![查询SQL执行路径][SQL]  
有一个大致的流程后，我们来具体看下每一步的细节。

### 4.1 MySQL客户端与服务端之间的通信协议 ###

由于客户端与服务端之间传输的数据都必须是要完整可靠的，显然是**使用TCP协议来建立连接**。

MySQL客户端与服务端需要进行通信，在任意一个时刻，要么是服务端发送数据给客户端，要么是客户端发送数据给服务端，即**半双工通信**。

这种通信协议让MySQL客户端与服务端之间通信简单，但也限制了MySQL，例如一端必须完整地接受了另外一端发送来的数据，才能够给另外一端响应数据，就当我们使用像DataGrip、Navicat等客户端连接好MySQL服务端时，我们要select \* from一张数据量很大的表，那么我们只能等服务端返回结果了。这一个查询请求占用了大量的资源，如果有很多个这样的查询请求，那MySQL服务端的压力肯定是很大的咯。所以，从数据库捞全表的数据而不使用limit加以限制，客户端和服务端都很难顶的。

> 书中讲到：当客户端从服务端获取数据时，看起来是一个拉数据的过程，实际上是服务端在向客户端推送数据的过程。客户端不断地接受从服务端推送来的数据，且没办法让服务端停下来，**像从消防水管喝水一样。**

所以通常，使用查询缓存可以减少服务器压力，让查询早点结束并释放相关资源。

**查询状态**  
对于每一个MySQL连接，也可以说一个线程，任意时刻都有一个状态，该状态表示了MySQL当前正在做的事情。

*  **sleep** 线程正在等待客户端发送新的请求
 *  **query** 线程正在执行查询或将查询结果返回给客户端
 *  **locked** MySQ服务器层，表示线程正在等待表锁
 *  **analyzing and statistics** 线程正在收集存储引擎的统计信息，并生成查询的执行计划
 *  **copying to tmp table \[on disk\]** 线程正在执行查询，并且将其结果集都复制到一个临时表中（group by、文件排序、union操作等），若有\[on disk\]标记，则表示MySQL正将一个内存临时表放到磁盘上
 *  **sorting result** 线程对结果集进行排序
 *  **sending data** 线程在多个状态件传送数据；或在生成结果集；或在向客户端返回数据

### 4.2 查询缓存 ###

在解析一个查询语句前，若MySQL的查询缓存功能开启，那么MySQL会优先检查该查询是否命中查询缓存中的数据。如果命中了查询缓存，则返回结果；若未命中，则继续后续流程。

### 4.3 查询优化处理 ###

查询优化处理分为多个子阶段：解析SQL、预处理、优化SQL执行计划。

#### 4.3.1 语法解析器和预处理 ####

MySQL通过关键字将SQL语句进行解析，生成一棵对应的解析树，MySQL解析器将使用MySQL语法规则验证和解析查询。

预处理器根据MySQL规则进一步检查解析树是否合法，如将检查数据表和列属否存在、解析名字和别名、看看是否有歧义。接下来会进一步验证权限。

#### 4.3.2 查询优化器 ####

查询优化器的主要作用是找到执行一条SQL语句的最好执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

优化器的评估成本时对要进行的随机IO次数的统计信息计算主要是受到每个表或索引页个数、索引基数、索引分布和数据行的长度、索引分布情况等影响。**优化器在评估成本时并不会考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘IO。**

MySQL优化器可能选择错误的执行计划的情况：

*  统计随机IO次数的信息不准确。受存储引擎的影响，例如InnoDB因为MVCC机制，不能很好地维护一个数据表行数的精确统计信息
 *  执行计划中的成本估算并不等同于实际执行的成本。因为MySQL分析执行计划时并不知道哪些页在内存、哪些页在磁盘，所以在查询的执行过程中具体需要多少次磁盘IO是很难估计准确的
 *  MySQL是基于成本模型而选择最优的执行计划，而我们用户是希望查询时间尽可能短
 *  MySQL不考虑并发执行的查询，这可能会在实际执行过程中影响查询的速度
 *  有些无法预知的问题也会影响MySQL实际的执行  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 1]

MySQL能够处理的优化类型：

*  重新定义关联表的顺序。可能SQL语句是`select * from a inner join b on a.id = b.id`，但MySQL查询优化器关联表的顺序可能是先b表后a表。
 *  将外连接转化为内连接。可能outer join会因为where条件、库表的结构被MySQL优化器变成一个内连接。
 *  使用等价变换规则，简化表达式。例如：`5=5 and a>5`可以被简化为`a>5`
 *  可以优化count()、min()、max()。MySQL的索引在此起到了极大的作用，例如查找某一列的最小值，只需要查询B+树最左端的叶子节点并直接获取叶子节点的第一条记录即可。同理，查找某一列的最大值，只需要查询B+树最右端的叶子节点并直接获取叶子节点的最后一条记录即可。对于统计count()，不同类型的存储引擎有不同的优化方式，例如MyISAM维护了一个变量来存表的记录数，这使得count计数的时间复杂度直接优化成O(1)。
 *  预估并转换为常数表达式。例如`where 1=1`，此外，使用了主键或者唯一索引也可以转为常数表达式。例如：`select a.name, b.money from a inner join b using(a.id) where a.id = 2021`，a表的id是主键索引，且a.id有一个确定的值为2021，那就将a表中返回的a.id为2021这一列的记录当作是一个常数来处理，然后再去b表进行查询。同时，using子句也让MySQL知道a.id是一个常量。
 *  覆盖索引。当索引中的列包含要查询的列时，MySQL会直接使用索引来返回需要的数据。
 *  子查询优化。MySQL会将子查询转换成高效的形式，从而避免多个查询多次对数据进行访问。
 *  提前终止查询。当MySQL发现已经满足查询需求时或者查询条件不成立等情况，MySQL就会终止查询。如：使用limit；where条件不成立，MySQL会立即返回空结构；存储引擎检索不同取值或者判断值的存在性等。
 *  等值传播。若两个列的值通过等值关联，MySQL能够把其中一个列的where条件传递到另外一列上。例如：`select * from a inner join b on a.id = b.id where a.id = 2021`，a表与b表通过相同的id关联，a.id = 2021这个条件同样适用于b表。
 *  列表IN()的比较。MySQL将IN中的数据先排序，然后通过二分查找的方式来确定IN中的条件是否满足条件，这就是一个O(log n)的操作了。所以当表有大量数据时，查询条件有多个，可以考虑使用IN语句来优化查询。

#### 4.3.3 关联优化 ####

MySQL在执行连接查询时，往往会先执行子查询，并将子查询的结果存放到一个临时表中，然后将临时表中的结果当作条件来执行父查询。MySQL的优化器会对`select a.id, b.name from a inner join b on a.id = b.id`之类的关联查询进行优化。优化器会对多个表关联时的顺序进行优化，它通过评估不同顺序时的成本来选择一个代价最小的关联顺序来执行查询。

#### 4.3.4 排序优化 ####

排序是一个成本很高的操作，故从性能上来讲，应该尽可能地避免排序或对大量数据进行排序。当MySQL不能使用索引进行排序时，它需要进行文件排序（数据量小在内存中进行，数据量大需要使用磁盘）。

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行快速排序。如果内存不够排序，MySQL会将数据分块，对每个独立的块使用快速排序，并将各个块的排序结果存放在磁盘上，然后将各个排序的块进行合并，最后返回排序结果。

MySQL 5.6以上，排序的算法是单次传输排序：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。虽然这个排序算法只需要一次顺序IO读取所有的数据，但如果需要返回的列非常多、非常大，会额外占用大量的空间，而这些列对排序操作本身是没有什么用的，所以有利有弊吧。

值得一提的是，在关联查询的时候如果需要排序，MySQL会分情况来处理这样的文件排序

*  如果order by子句中的所有列都来自于关联的第一个表，那么MySQL在关联处理第一个表时就会进行文件排序
 *  除此之外，MySQL会将关联的结果先存放到一个临时表中，然后再进行文件排序

## 4.4 查询执行引擎 ##

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。查询执行引擎会根据执行计划给出的指令逐步执行。在执行过程中，有大量操作需要通过调用存储引擎实现的接口来完成，接口称为“handler API”。MySQL在优化阶段就为每个表创建了一个handler实例，优化器会根据这些实例的接口获取表的相关信息（列名、索引统计信息等）。

> 并非所有操作均有handler完成。例如，MySQL需要进行表锁时，handler可能会实现特定级别、更细粒度的锁，如InnoDB就实现了自己的行基本锁，但这并不能代替服务器层的表锁。

## 4.5 返回结果给客户端 ##

查询执行的最后一个阶段是将结果返回给客户端，即使查询无需返回结果集，MySQL仍然会返回查询的一些信息，例如查询影响到的行数等。

若查询可以被缓存，MySQL返回结果给客户端前会将结果存储到查询缓存中。

MySQL将结果集返回给客户端是一个增量、逐步返回的过程。这样处理的好处是：服务端无需存储太多结果，也不会因为要返回太多结果而消耗太多内存；客户端也能够快速地获取到返回的结果。

结果集中的每一行都会以一个满足MySQL客户端/服务端通信协议的封包发送，然后通过TCP协议传输，在TCP传输过程中，可能对MySQL的封包进行缓存然后批量传输。

## 小结 ##

本期主要对MySQL的查询过程进行了简要的梳理，理解了一条SQL执行的过程需要经过MySQL的各种组件，下一期，我们将重点探索下MySQL查询性能优化的方法。我是Zhongger，一个在互联网公司摸鱼写代码的打工人，你们的支持是我创作的最大动力，我们下期见～  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70]: /images/20221021/eefd95eeaa4f4c17b6a68232c57623de.png
[SQL]: /images/20221021/fbe46e9991a34c4dba07fe12576e9344.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 1]: /images/20221021/7003c02ea03643a1b326845b4b207581.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzM5NTkxMQ_size_16_color_FFFFFF_t_70 2]: /images/20221021/f09edf61f13f417d9c228e2e1d915624.png