MySQL查询性能优化

待我称王封你为后i 2024-04-17 17:08 87阅读 0赞

> 本文为《高性能MySQL》读书笔记

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

查询性能低下最基本的原因是访问的数据太多。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效的：

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

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

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

**一些典型案例：**

![format_png][]  
![format_png 1][]  
![format_png 2][]

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

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

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

这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

**响应时间：**

响应时间是由两个部分组成：服务器处理时间和排队等待时间。服务器处理时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间 —— 可能是等I/O操作完成，也可能是等待行锁，等等。遗憾的是，目前我们无法把响应时间细分到上面这些部分，但是查询响应时间 还是我们分析慢查询的重要参考值。

**扫描的行数和返回的行数：**

扫描过多的行数 则说明该查询找到需要的数据的效率不高，其实也就是索引建的不够优化。

扫描的行数与返回的行数大小相等，是最优的查询。

**扫描的行数和访问类型：**

是不是选择最优的访问类型来减少扫描的行数。

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

在EXPLAIN语句中的Type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从大到小。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。

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

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

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

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

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

## 重构查询的方式 ##

简化SQL，拆分大SQL，分解关联查询。

## MySQL查询执行过程 ##

![format_png 3][]

### MySQL 客户端 / 服务器端 通信协议 ###

MySQL客户端与服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么有服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。

这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没有办法做流量控制。一旦一端开始发送消息，另一端要接收整个消息才能响应它。这就像来回抛球的游戏：在任何时刻，只有一个人能控制球，而且只有控制球的人才能将球抛回去（发送消息）

客户端用一个单独的数据包将查询传给服务器，这也是为什么当查询的语句很长的时候，参数`max_allowed_packet`就特别重要了。一旦客户端发送了请求，它能做的事情就只是等待结果了。

相反的，一般服务器响应给用户的数据很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收到整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地断开连接，都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。

换一种方式解释这种行为：当客户端从服务器取数据时，实际上是MySQL在向客户端推送数据的过程。客户端不断地接收服务器推送的数据，客户端也无法让服务器停下来。

多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没有什么问题，但是如果需要返回一个很大的结果集的时候。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。（SQL\_BUFFER\_RESULT）

### 查询状态 ###

查看当前查询线程状态：`SHOW FULL PROCESSLIST`命令。

状态枚举值：

*  Sleep：线程正在等待客户端发送新的请求。
 *  Query：线程正在执行查询或者正在将结果发送给客户端。
 *  Locked：在MySQL服务器层，该线程正在等待表所。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会提现在线程状态中。
 *  Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划。
 *  Copying to tmp table \[on disk\]：线程正在执行查询，并将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表存到磁盘上。
 *  Sorting result：线程正在对结果集进行排序。
 *  Sending data：线程可能在多个状态之间传送数据，或者在生成结果集，或者在想客户端返回数据。

### 查询缓存 ###

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接存缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

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

查询的生命周期的下一步是将一个SQL转换为一个执行计划，MySQL再按照这个执行计划和存储引擎进行交互。这个阶段可以分为几个子阶段：解析SQL、预处理、优化SQL执行计划。

**语法解析器和预处理：**

首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则校验和解析查询。例如，它将校验是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。

预处理器则根据一些MySQL规则进一步检查解析树是否合法，例如，这里讲检查数据表和规则列是否存在，还会解析名字和别名，看看它们是否有歧义。

**查询优化器：**

一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

mysql的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单的分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将where条件转换成另一种等价形势。静态优化不依赖于特别的数值，如where条件中带入的一些常熟等等。静态优化在第一次完成后就一直有效，几遍使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。

相反动态优化则和查询上下文有关，也可能和很多其他因素有关，例如，where条件中的取值、索引中条目对应的数据行等等。着需要在每次查询的时候重新评估，可以认为这是“运行时的优化”。

下面是一些mysql能够处理的优化类型：

*  重新定义关联表的顺序；
 *  将外链接转化成内连接；
 *  使用等价变换规则（如 5=5 AND a>5将被改写成a>5）；
 *  优化count()、min()和max()；
 *  预估并转化为常数定义式；
 *  覆盖索引扫描；
 *  子查询优化；
 *  提前终止查询；
 *  等值传播；
 *  对象IN()的比较；

### MySQL如何执行关联查询 ###

mysql中的“关联”一次所包涵的意义比一般意义上理解的要更广泛。总的来说，mysql认为任何一个查询都是一个“关联” —— 并不仅仅是一个查询需要到两个表匹配才叫关联，所以在mysql中，每一个查询，每一个片段（包括子查询，甚至基于单表的select）都有可能是关联。

我们根据union查询的例子来理解关联查询。对于union查询，mysql先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表来完成union查询。在mysql的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

当前mysql关联执行得策略很简单：mysql对任何关联都执行嵌套循环关联操作，即mysql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表寻找匹配的行，一次下去，知道找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询结果汇总需要的各个列。mysql会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更更多行以后，mysql返回到上一层次的关联表，看是否能够找到更多的匹配记录，一次类推迭代执行。

按照这样的方式查找第一个表的记录，再嵌套查询下一个关联表，然后回溯到上一个表，在mysql中时通过嵌套循环的方式实现的。 请看下面的例子中的简单查询：

SELECT tbl1.col1 , tbl2.col2 FROM tbl1 INNER JOIN tbl2 USING(col3) where tbl1.col1 in (5,6)

假设MySQL按照查询中的表顺序进行关联操作，我们则可以使用下面的伪代码表示MySQL将如何完成这个查询：

![format_png 4][]

![format_png 5][]  
![format_png 6][]

## 特定类型的查询优化 ##

### 优化COUNT()查询 ###

`COUNT()`可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在`COUNT()`的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数（而非NULL）。

`COUNT()`的另一个作用是统计结果集的行数，当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用`COUNT(*)`的时候，这种情况下通配符 \* 并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

一种常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用`count(*)`，这样写意义清晰，性能也会很好。

`MYISAM`只有当没有任何`WHERE`条件的`COUNT(*)`才非常快，因为此时无须实际地计算表的行数。`MySQL`可以利用存储引擎的内部计数器直接获得这个值。如果MySQL知道某列col不可能为NULL值，那么MySQL内部会将`COUNT(col)`表达式优化为`COUNT(*）`。当统计待WHERE子句的结果集行数时，MyISAM与其他数据库引擎没有太大区别。

### 优化关联查询 ###

*  确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
 *  确保任何的GROUP BY和ORDER BY 中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

### 优化子查询 ###

尽可能使用关联查询代替子查询。

### 优化GROUP BY优化 ###

针对以GROUP BY的优化 主要分为有索引和无索引两种情况。

当无法使用索引的时候，GROUP BY使用两种策略来完成分组工作：使用临时表或者文件排序来做分组，其实就是进行一次全表扫描筛选数据形成一个临时表，然后按照GROUP BY 指定的列进行排序。在这个临时表里面，对于每一个group的数据行来说是连续在一起的。完成排序之后，就可以发现所有的GROUPS，并可以执行聚合函数。所以，我们常常在explain后看到“Using temporary; Using filesort”。

如果没有通过 ORDER BY子句显示地指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，使分组的结果集按需要的方向进行排序。

### 优化LIMIT分页查询 ###

假设有如下的分页SQL语句：

SELECT * FROM table LIMIT offset , rows ;

这是一条典型的LIMIT语句，常见的使用场景是，某些查询返回的内容特别多，而客户端处理能力有限，希望每次只取一部分结果进行处理。

上述SQL语句的实现机制是：

1.  从“table”表中读取offset+rows行记录。
2.  抛弃前面的offset行记录，返回后面的rows行记录作为最终的结果。

这种实现机制存在一个弊端：虽然只需要返回rows行记录，但却必须先访问offset行不会用到的记录。对一张数据量很大的表进行查询时，offset值可能非常大，此时limit语句的效率就非常低了。

**使用覆盖索引来优化：**

尽可能使用索引覆盖扫描，确定需要返回行的主键等，然后再根据需要做一次关联操作再返回所需的列。通常此种优化都是使用子查询来实现。

比如我们有如下的SQL语句：

select * from student where score > 90 limit 1000,10

我们就可以先利用覆盖索引查询速度快的优点先查询出对应分页段内的学号，然后再根据学号去做关联查询，第二步是直接使用主键做关联，也是非常快的。优化后的SQL如下：

select * from student as stu INNER JOIN (select id from student limit 1000,10) as tmp on stu.id = tmp.id;

**确定分页起始值，减少扫描行数**

我们可以记住上次取数据的位置，然后下次就可以直接从该位置开始扫描数据，然后取指定的长度。假设上次获取到的最后一个学生学号是：20180131200，则我们可以改写成如下SQL：

select * from student stu where stu.id > 20180131200 order by id limit 100 ;

上面的SQL首先使用主键进行排序，因为聚簇索引的特性，所以主键ID在索引树中本身已经有序存储了，所以此处的order by 非常快。然后再使用主键进行筛选 也是非常快的。

优秀网文：[mysql大数据量之limit优化][mysql_limit]

### 优化UNION查询 ###

Mysql总是通过创建和填充临时表的方式来执行UNION查询，因此很多优化策略在UNION中没法很好使用，需要下推到UNION的子查询中，例如直接将这些子句冗余的写一份到各个子查询中。

所以，除非需要消除重复的行，不然一定要使用UNION ALL。使用UNION的时候，Mysql会给临时表添加一个DISTINCT选项，对整个临时表做唯一检查，性能代价很高。

加油

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzU4NDQ3LzIwMTgwMS83NTg0NDctMjAxODAxMzAxMzEyMzE5MDYtMjI2ODA5NzM2LnBuZw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzU4NDQ3LzIwMTgwMS83NTg0NDctMjAxODAxMzAxMzE0MDYyNjUtNDc1OTI0MjM5LnBuZw?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzU4NDQ3LzIwMTgwMS83NTg0NDctMjAxODAxMzAxMzE0NDU0NjgtNzAxNzg4MDM2LnBuZw?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzU4NDQ3LzIwMTgwMS83NTg0NDctMjAxODAxMzAxOTU4MzY3NjUtMTgwODk5MDU5MS5wbmc?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzU4NDQ3LzIwMTgwMS83NTg0NDctMjAxODAxMzAyMTEyMjE3MTgtMTc5MDk1MjI2Ny5wbmc?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzU4NDQ3LzIwMTgwMS83NTg0NDctMjAxODAxMzAyMTEzMTExNTYtNTgyMTc2OTY2LnBuZw?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE3LmNuYmxvZ3MuY29tL2Jsb2cvNzU4NDQ3LzIwMTgwMS83NTg0NDctMjAxODAxMzAyMTE0MDc3ODEtMTc3MTY1ODUxLnBuZw?x-oss-process=image/format,png
[mysql_limit]: http://www.cnblogs.com/lisqiong/p/5635009.html