likes
comments
collection
share

万字长文与你一起探索MySQL查询性能优化

作者站长头像
站长
· 阅读数 33

我报名参加金石计划1期挑战——瓜分10万奖池，这是我的第1篇文章

参考文献《高性能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，衡量性能开销的三个指标是：响应时间、扫描行数、返回行数。

响应时间

  • 服务时间：数据库处理查询时真正花费的时间
  • 排队时间：数据库因为等待某些资源，如I/O完成、锁等待而未执行查询的时间

扫描的行数与返回的行数

理想情况下，扫描的行与返回的行之间的比率通常要小，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 查询执行的基础

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实际的执行

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的封包进行缓存然后批量传输。

5 MySQL查询优化器的局限

MySQL查询优化器对于以下几种类型的查询是不适用的。

5.1 关联子查询

where条件中包含in子句的子查询语句。例如：

  • select * from a where a.id in (select b.id from b where b.name = 'zhongger')

对于这类查询，MySQL会将表a进行全表扫描，然后根据表a的id逐个去执行in语句中的子查询。如果a表很大，那么这个查询性能会非常差。

5.2 Union的限制

当需要对结果集合并时，需要使用union子句。例如：

  • (select a.name from a order by a.name) union all (select b.name from b order by b.name) limit 10

这是将两个查询结果合并，然后取前10条记录。MySQL对于这条SQL的处理是把a表中的记录和b表中的记录存放在一个临时表中，然后再从临时表取出10条记录。如果a，b表的记录很大，那么这样子性能也是很慢的。可以将上述SQL改写成如下形势：

  • (select a.name from a order by a.name limit 10) union all (select b.name from b order by b.name limit 10) limit 10

5.3 索引合并优化

当where条件中包含多个复杂条件的时，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。

5.4 等值查询

等值传递也会带来意想不到的额外消耗。例如：有一个非常大的IN列表，而MySQL优化器发现存在where、on或者using的子句，将这个列表的值和另一个的某个列相关联。

在执行查询时，查询优化器会将In列表都复制到关联的各个表中来进行匹配关联，如果IN列表非常大，则会导致执行和优化都会变慢。

5.5 并行执行

MySQL无法利用CPU多核的特性来并行执行查询。

5.6 哈希关联

MySQL不支持哈希关联（MySQL的所有关联都是嵌套循环关联的）

5.7 松散索引扫描

MySQL不支持松散索引扫描，也就无法按照不连续的方式扫描一个索引。例如：有覆盖索引（a，b），SQL语句：

  • select * from t where b between 2 and 3

因为索引的最左前缀列是a，但查询中只覆盖了列b，故MySQL不走索引，只能全表扫描。

5.8 最大值和最小值局限

对于Min()和Max()查询，MySQL的优化做得并不好。例如：

  • select min(id) from t where name = 'zhongger'

因为在name字段上没有索引，所以MySQL会有一次全表扫描。如果MySQL能够进行主键扫描，那么理论上MySQL读到第一个满足条件的记录时，就需要我们找到的最小值了，因为主键索引中的叶子节点是按照id的大小顺序排序。但是MySQL这时还是会做全表扫描。一个优化方法是：

  • select id from t use index(primary) where name = 'zhongger' limit 1

这可以让MySQL扫描尽可能少的记录。

6 优化特定类型的查询

前面做了这么多的铺垫，都是为了这一小节能够对查询优化的理解更加深刻。下面一起来看下吧。

6.1 优化count()查询

count()是一个聚合函数，它的主要作用是：

  • 统计某个列值的数量
  • 统计表的记录的行数

在统计列值的时候，要求列值是非空的（即不统计NULL值），如果在count()的括号中传入了列或者列的表达式作为参数，则统计的就是这个列或列表达式有值的结果数。如果在在count()的括号中传入了通配符*作为参数，则会统计结果集中的所有行数。

如果希望知道结果集的行数，最好使用count(*)，而不是count(结果集中的某一列)，这样意义清晰而且性能更好。

简单的优化

在不加任何where条件时，MyISAM存储引擎因为有对表的行数进行存储，所以有些情况下可以考虑使用MyISAM存储引擎来优化count(*)。

使用近似值

有些时候某些业务场景并不要求完全精确的count值，因此可以使用近似值来代替。像一些弱一致性的场景，没必要每次都去数据库中查count，可以考虑利用Redis缓存来提升效率。

更复杂的优化

通常来说，count需要扫描大量的行才可以获取精确的结果，因此还是比较难优化的。此外，可以考虑新建立一个汇总表，每写入一条记录，汇总表对应的记录就加1，查询count时只需要查一遍汇总表的数字即可，这样可以避免全表扫描，当然这样也增加了维护的难度。

快速，精确和实现简单，三者只能取其二。

6.2 优化关联查询

对于这条关联SQL：

  • select * from a inner join b on a.id = b.id

优化需要注意如下的点：

  • 确保on或者using子句中的列有索引。此外，在创建索引的时候也要考虑到表关联的顺序
  • 确保任何的group by和order by中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程
  • 当升级MySQL时需要注意：关联语法、运算符优先级等可能会发生变化的地方。以前是普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果等

6.3 优化子查询

MySQL5.6以下的版本，子查询最好使用关联查询来代替；MySQL5.6及以上的版本，子查询已经被优化了。

6.4 优化group by和distinct

  • 大多数场景下，MySQL会采用索引来优化group by查询。
  • 当无法使用索引时，group by优化策略是使用临时表或者文件排序来做分组，可以通过SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器进行优化。
  • 如果对关联查询做group by，且按照查找表中的某列进行分组，那么常采用查找表的标识列来group by会比其他列效率高。例如：select * from a inner join b on a.id = b.id group by a.id 的效率比 select * from a inner join b on a.id = b.id group by a.name 高。

6.5 优化limit分页

在系统中需要进行分页操作的时候，我们通常会使用limit加上offset的方法实现，同时加上合适的order by子句。如果有对应的索引，效率通常会不错；否则，MySQL需要做大量的文件查询。

在offset非常大时，例如limit 10000，20这样的查询，这时MySQL要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大offerset的性能。

优化此类分页查询的一个最简单的方法是尽可能地使用索引覆盖扫描，而非查询所有的列，然后根据需要做一次关联操作再返回所需要的列。对于offset很大的时候，这么做的效率会有很大的提升。对于如下SQL：

  • select id, name from a order by title limit 50, 5;

如果a表非常大，那么这个查询最好改写成下面形式：

  • select a.id , a.name from a inner join (select a.id fom a order by title limit 50,5) as lim using(a.id);

这让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列返回原表查询需要的所有列，因为利用了聚簇索引去扫描。

有时也可以将limit转为已知未知的查询，让MySQL通过范围扫描获得结果，例如改写成：

  • select id, name from a order by title limit 50, 5 where position between 50 and 54 order by position

对于大的offset，会使得MySQL扫描大量不需要的行然后抛弃掉。可以采用书签的方式，记录上次取数据的位置，下次就可以从书签记录的位置开始扫描，这样就可以避免使用offerset。

6.6 优化Union查询

MySQL总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中没办法很好使用。所以需要将where、limit、order by等写到需要union的各个子查询中，以便优化器可以充分利用这些条件进行优化。

最后

MySQL查询性能优化是一个很大的课题，往往需要结合实际情况来制定优化策略。一般的步骤不外乎就是先explain分析，然后尽可能地利用索引，避免全表扫描，避免索引失效等。