mysql之query

Author Avatar
Sean Yu 12月 03, 2020
  • 在其它设备中阅读本文章

对 MySql 进行优化，必须对 Scheme，索引，查询语句一同优化。

通过前面的章节我们掌握了 Scheme 和 索引的优化，最后我们来看一下查询优化。

为了优化查询，我们必须先了解查询是怎样执行的，然后探讨优化器在哪些方面做得还不足，以帮助 MySql 更有效的执行查询。

优化数据访问

在一条 Sql 语句执行的很慢的时候，可以从以下两个方面来分析：

  • 是否在检索的时候访问了太多的行或者列
  • MySql 服务器是否在分析大量超过需要的行

请求了不需要的数据

万恶之源 SELECT *

SELECT *必然要回表的，随意就算where子句命中了索引，还是要最后回表查询，效率低下，还会为服务器带来额外的I/O、内存和CPU的消耗。

一个很好用的观点就是在每次使用 SELECT * 取出全部行的时候都要审视一下自己是否需要全部数据。

取出所有列可能使得索引覆盖无效，一些 DBA 是严格禁止 SELECT * 的写法的。

重复查询数据

有些地方可能会不小心的重复查询了相同的数据。比如在论坛中，如果一个人回复多次，很有可能会一不小心每次都去请求这个人的资料，一个有效的方法就是使用缓存。

扫描额外的记录

确定查询只返回需要的数据以后，接下来该看一下为了返回需要的记录是否扫描了太多行了。有两个指标我们需要关注，一个是扫描的行数和返回行数的比值，另外一个是扫描的访问类型。

扫描行数和返回行数的比值如果过低，则需要扫描大量的数据才能返回结果，通常可以使用如下的方法来保存数据：

  • 使用索引覆盖，把所有的列放入索引中，就无需扫描表了
  • 改变数据库结构，比如采用单独的表汇总表
  • 重新写这个 SQL 语句

在 EXPLAIN 语句中的 type 列中反应了访问类型，从慢到快分别是：

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

如果查询没有使用合适的类型，可以合理的添加索引。

重构查询方式

将一个复杂查询拆分成多个查询

MySql 从设计上让连接和断开都很快。如果只是返回一个小小的结果，MySql 非常高效。

当然能一个查询就解决的要尽量写成一个查询，只是告诉大家不要太惧怕把查询拆分开来会带来性能损失。

切分查询

有时候一个大查询会占用表锁很久，影响业务。这时候可以将大查询分为小查询，每次执行这个查询的一小部分。

比如定期清除大量数据的时候，如果有一个大的语句一次性完成，则可能会占住很多资源，影响其他查询。

将删除改写成一次删除一小部分数据，分散开来在不同时间执行，可以将服务器压力分散到很长的一个时间段中。

分解关联查询

很多高性能应用会将一个大的关联查询分解成多个单表查询。

  • 让缓存效率更高，许多应用可以缓存单表查询的结果，那么下次查询的时候可以跳过这次单表查询
  • 单个查询减少锁的竞争
  • 更容易对数据库进行拆分
  • 减少冗余记录查询
  • 查询本身效率也可能会有所提升。例如，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。

MySql 查询过程

  • 客户端发送一条查询给服务器。
  • 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
  • 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
  • MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
  • 将结果返回给客户端。

image.png

MySQL 通信协议

MySQL 客户端和服务端的通信是半双工的，这意味着同一个时刻内，客户端和服务端只有一方在发送数据。一旦一方开始发送数据，另外一端必须接受完整个消息才能进行响应。

这就是为什么当查询语句特别长的时候，max_allowed_packet 特别重要了。

当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。客户端像是“从消防水管喝水”. 所以在必要的时候需要添加 LIMIT 限制。

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

查询状态
对于一个 MySQL 连接，任何时刻都有一个状态，该状态表示了 MySQL 当前正在做什么，用 SHOW FULL PROCESSLIST 命令即可。

  • Sleep 线程正在等待客户端发送新的请求。

  • Query 线程正在执行查询或者在将结果发送给客户端

  • Locked 该线程在等待表锁

  • Analyzing and statistics 线程正在收集存储引擎的统计信息，并且生成执行计划。

  • Copying to tmp table 线程正在把数据复制到一个临时表中，一般在 Group By 或者排序的时候会出现这个状态。

  • Sorting result 线程正在排序数据

  • Sending data 线程可能在多个状态之间传送数据，或者在向客户端返回数据。

查询缓存

MySQL查询缓存是MySQL中比较独特的一个缓存区域，用来缓存特定Query的整个结果集信息，且共享给所有客户端。为了提高完全相同的Query语句的响应速度，MySQL Server会对查询语句进行Hash计算后，把得到的hash值与Query查询的结果集对应存放在Query Cache中。当MySQL打开Query Cache之后，MySQL会对接收到的每一个SELECT 语句通过特定的Hash算法计算该Query的Hash值，然后通过该hash值到Query Cache中去匹配。

如果没有匹配，将这个hash值存放在一个hash链表中，并将Query的结果集存放到cache中，存放hash值链表的每个hash节点存放了相应Quey结果集在cache中的地址，以及该query所涉及到一些table相关信息；

如果通过hash值匹配到了一样的Query，则直接将cache中相应的Query结果集返回给客户端。

目前MySQL Query Cache只会cache select语句，其他类似show ，use的语句不会被cache。 MySQL 的每个Query Cache都是以SQL文本作为key来存储的，在应用Query Cache之前，SQL文本不会做任何处理。也就是说，两个SQL语句，只要相差哪怕一个字符(例如大小写不一样，多一个空格，多注释)，那么这两个SQL将使用不同的Cache地址。

打开查询缓存对读和写操作都会带来额外的消耗：

  • 读查询在开始之前必须先检查是否命中缓存。
  • 如果这个读查询可以被缓存，那么当完成执行后，MySQL若发现查询缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗。
  • 这对写操作也会有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存都设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大系统消耗
  • 如果查询缓存使用了很大量的内存，缓存失效操作就可能成为一个非常严重的问题瓶颈。如果缓存中存放了大量的查询结果，那么缓存失效操作时整个系统都可能会僵死一会儿。因为这个操作是靠一个全局锁操作保护的，所有需要做该操作的查询都要等待这个锁，而且无论是检测是否命中缓存、还是缓存失效检测都需要等待这个全局锁。
  • 对InnoDB表，当修改一个表时，设置了缓存失效，但是多版本特性会暂时将这修改对其他事务屏蔽，在这个事务提交之前，所有查询都无法使用缓存，直到这个事务被提交，所以长时间的事务，会大大降低查询缓存的命中
  • 相关系统变量设置不合理会造成大量的内存碎片，这样便会导致Query Cache频繁清理内存。
  • 查询语句不同，但查询结果相同的查询都会被缓存，缓存了却用不到，这样便会造成内存资源的过度消耗。

正因为这些缺点，大多数情况下，缓存的命中率都比较低，简而言之，mysql缓存，不用为妙。

查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。前两步不重要，这里重点说优化查询计划。

MySQL 对关联表顺序优化

三种不同的关联方式：https://zhuanlan.zhihu.com/p/148517223

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将如何完成这个查询：

outer_iter = iterator over tbl1 where col1 IN(5,6) 
outer_row  = outer_iter.next
while outer_row
  inner_iter = iterator over tbl2 where col3 = outer_row.col3
  inner_row=inner_iter.next
  while inner_row
    output [outer_row.col1, inner_row.col2]
    inner_row = inner_iter.next
  end
  outer_row=outer_iter.netxt
end

上面的执行计划对于单表查询和多表关联查询都适用，如果是一个单表查询，那么只需完成上面外层的基本操作。对于外连接上面的执行过程仍然适用。

image.png

从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中，然后将这个临时表当作一个普通表对待（正如其名“派生表”）。MySQL在执行UNION查询时也使用类似的临时表，在遇到右外连接的时候，MySQL将其改写成等价的左外连接。简而言之，当前版本的MySQL会将所有的查询类型都转换成类似的执行计划。

执行计划

MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。任何多表查询都可以使用一棵树表示：

image.png

在计算机科学中，这被称为一颗平衡树。但是，这并不是MySQL执行查询的方式。正如我们前面章节介绍的，MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以，MySQL的执行计划总是如图64所示，是一棵左测深度优先的树。

image.png

关联查询优化器

MySQL 优化器决定了多个表关联的顺序，关联优化器可以选择一个代价最小的关联顺序。（有可能先去查询得到结果集比较少的表，这样就可以减少回溯的次数）

有时候优化器选择的不是最优的顺序，这时候可以使用 STRAUGHT_JOIN 关键字进行查询，让优化器按照你认为最优的顺序查询，但是一般来说人判断的都没有优化器好。

优化器会尝试在所有的顺序中选择一个成本最小的关联顺序，但是当表非常多的时候，比如有 n 张表进行关联，就要进行 n！ 次比较。当表超过 optimizer_search_depth 的时候，就会选择贪婪搜索模式了。

MySQL 查询优化器的局限性

子查询

MySQL 的子查询优化的相当糟糕，最糟糕的一类是子查询中 WHERE 条件包含了 IN() 的子查询。比如用下面的语句查询

SELECT * FROM film WHERE film.id in (SELECT file_id from film_actor WHERE actor_id = 1)

我们可能会认为 MySQL 会执行后面的语句选择出 id 后才执行前面的查询，但是 MySQL 会将外层查询压入子查询中

SELECT * FORM film WHERE EXISTS(SELECT * FROM film_actor WHERE actor_id = 1 AND film_actor.film.id = film.id)

这个查询会对 film 进行全表扫描，性能非常糟糕。

所以我们最好用联合查询来代替这个查询。

这个问题直到 MySQL 5.5 还存在，MySQL 另外一个分支 MariaDB 在原有的基础上做了大量的改进，例如这里带 IN 的子查询。

当一个查询能被写成子查询和联合查询的时候，最好通过一些测试来判断哪个写法更快一些

UNION

有时候 MySQL 无法将闲置条件由外层推到内层，这使得本能限制扫描行数的 LIMIT 在内层查询中不起作用。

如果希望 UNION 的各个子句能根据 LIMIT 只取出部分结果集，或者希望能先排好序再分别使用这些子句，那么需要分别对这些查询使用 LIMIT 和 ORDER BY。

(SELECT * FROM XXX LIMIT 20) UNION ALL (SELECT * FROM XXX LIMIT 20)

并发执行

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

最大值和最小值

对于 MIN 和 MAX 查询，MySQL 的优化做的不是很好，

SELECT MIN(id) FROM actor

因为 id 是递增的，所以只需要扫描一行即可，但是 MySQL 仍然会做全表扫描。可以改下面的写法

SELECT id FROM actor LIMIT 1

特定优化查询

一般来说，使用 Percona Toolkit 中的 pt-query-advisor 能够解析查询日志，分析查询模式，然后给出详细的建议来帮助你优化 SQL 语句。

优化 COUNT 查询

当 COUNT 的值不可能为空的时候，MySQL 会转向统计行数。如果我们想要统计行数的时候，最好直接使用 COUNT(*)。

这种情况下通配符*并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

使用近似值

有时候某些业务不需要精确值，此时可以用近似值来代替，EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 不需要去真正的执行查询，效率高很多。

更复杂的优化

通常来说，COUNT()都需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。除了前面的方法，在MySQL层面还能做的就只有索引覆盖扫描了。

优化关联查询

  • 确保 ON 或者 USING 上的列有索引，在创建索引的时候需要考虑到关联列的顺序，比如说表 A，B 用列 c 进行关联的时候，如果优化器的关联顺序是 B，A，则只需要在 A 上建立索引即可。
  • 确保任何的 GROUP BY 和 ORDER BY 只涉及到一个表中的列

优化子查询

关于子查询给出的最主要的优化方法是：尽量使用关联查询代替子查询，因为 MySQL 的子查询优化的非常烂。不过这条意见只在旧版本有用，在 MySQL 5.6 以上和 MariaDB 中，可以忽略掉这条优化。

优化 GROUP BY 和 DISTINCT

MySQL 经常用同样的方法来优化这两个查询，它们都会用索引来优化，这也是最有效的优化办法。

当无法使用索引的时候，MySQL 会用临时表或者文件排序来执行 GROUP BY。

如果需要对关联查询做分组，那么通常采用标识列来进行分组效率会比较高。

优化 LIMIT 分页

当系统需要进行分页操作的时候通常会使用 LIMIT 加 偏移量的操作，同时加上合适的 ORDER BY 语句。如果有对应的索引，效率通常会不错。

但是当偏移量非常大的时候，LIMIT 10000,20，这种语句会导致扫描了10020 行，但是只返回 20 行。

优化这种查询的方法有：

  • 使用索引覆盖，只搜索引覆盖的行，然后通过一次查询把所有需要的数据查找出来
  • 通过延迟关联，（先查询有索引覆盖的记录，例如主键，然后通过这些主键来得到需要的列，以避免mysql直接在巨大的废弃数据上回表）

优化 SQL_CALC_FOUND_ROWS

分页的时候有时候会通过在 LIMIT 语句中加上 SQL_CALC_FOUND_ROWS。这样就可以获取去掉 LIMIT 条件后查询的行数，加上这个提示以后，不管是否需要，都会把全部的行都扫描一遍，而不是在满足了 LIMIT 的大小后停止扫描，这样会带来很大开销。

解决这个问题有两个方法

  • 采用 EXPLAIN ROW 中的近似值，有时候不需要那么精准的数据
  • 先获得比较多的缓存集，比如设置一个 100 页和一个 100 页以后的按钮，当用户需要 100 页后的按钮再去获取。

优化 UNION 查询

除非确实需要服务器消除重复的行，否则必须要使用 UNION ALL。

如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，然后做一次查重操作，这将带来极大的开销。

整体看下来，其实也就三点，不用子查询，子句用索引，select索引覆盖避免回表。

这里说一下一些高级特性，因为不知道把这些东西分到哪里好。

视图

MySQL5.0版本之后开始引入视图。视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是MySQL从其他表中生成的。视图和表是在同一个命名空间，MySQL在很多地方对于视图和表是同样对待的。不过视图和表也有不同，例如，不能对视图创建触发器，也不能使用DROPTABLE命令删除视图。

实现视图更好的方法是，重写含有视图的查询，将视图的定义SQL直接包含进查询的SQL中。MySQL可以使用这两种办法中的任何一种来处理视图。这两种算法分别称为合并算法（MERGE）和临时表算法（TEMPTABLE）。

如果视图中包含GROUYBY、DISTINCT、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，MySQL都将使用临时表算法来实现视图。

如果可能，会尽可能地使用合并算法。

image.png

在mysql中，试图对性能的影响十分有限。

在其他的关系数据库中你可能使用过物化视图，MySQL还不支持物化视图（物化视图是指将视图结果数据存放在一个可以查看的表中，并定期从原始表中刷新数据到这个表中）。MySQL也不支持在视图中创建索引。不过，可以使用构建缓存表或者汇总表的办法来模拟物化视图和索引。

看起来物化视图可以帮助我们在链表查询的时候加快数据库性能。pgsql就有。

外间约束

InnoDB是目前MySQL中唯一支持外键的内置存储引擎。使用外键是有成本的。比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作。虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。

不过，在某些场景下，外键会提升一些性能。如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高得多，此外，外键在相关数据的删除和更新上，也比在应用中维护要更高效，不过，外键维护操作是逐行进行的，所以这样的更新会比批量删除和更新要慢些。

外键约束使得查询需要额外访问一些别的表，这也意味着需要额外的锁。如果向子表中写入一条记录，外键约束会让InnoDB检查对应的父表的记录，也就需要对父表对应记录进行加锁操作，来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待，甚至会导致一些死锁。因为没有直接访问这些表，所以这类死锁问题往往难以排查。

总的来说，在代码里实现逻辑外建要比数据库里用物理外建更好。