count(*)、order by、join详解

昀息
116 阅读12分钟

本节我要向大家分享我对count()、order by、join的理解。如有理解不当或者表述错误，欢迎指正。 本节主要内容如下图所示。

SQL常用函数.png 在平常使用数据库中，order by、count、join都会经常地用到。他们的作用分别是排序、统计、连表。

Count(*)统计表行数

随着系统中记录数越来越多，这条语句执行得也会越来越慢。

count(*)实现方式

在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
  • 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

count执行流程

在MySQL中，COUNT()函数的执行流程如下：

  1. 对于COUNT(*)，MySQL会遍历表中的每一行，然后返回行数。这种方法对于大型表来说是非常低效的。
  2. 对于COUNT(column)，MySQL会遍历表中的每一行，如果指定列的值不是NULL，就会加到计数中。这种方法对于大型表来说也是低效的。
  3. 对于COUNT(DISTINCT column)，MySQL会遍历表中的每一行，为指定列的每个不同的值保留一个计数。这种方法对于大型表来说也是低效的。

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？

因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

统计表行数方法

  • MyISAM 表虽然 count(*) 很快，但是不支持事务；
  • show table status 命令虽然返回很快，但是不准确；
  • InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

一个页面经常要显示交易系统的操作记录总数，到底应该怎么办呢？

首先，老师提出用redis缓存行数，但是由于逻辑不一致问题无法解决。是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。

然后，老师提出在数据库保存行数，解决了崩溃丢失的问题，通过事务解决逻辑不一致问题。

count(*)、count(id)、count(1)都表示返回满足条件的结果集的总行数.而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。三者性能差别如何分析？

  1. server 层要什么就给什么；
  2. InnoDB 只给必要的值；
  3. 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

对于 count(字段) 来说：

  1. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
  2. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
  3. count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。

order by”是怎么工作的？

全字段排序

全字段排序（Full Field Sorting）是指在数据库查询或信息检索系统中，根据多个字段的组合来对结果进行排序的一种方式。它不是指对所有字段进行排序，而是指可以根据一个或多个特定字段来决定记录的顺序。

为避免全表扫描，需要在字段加上索引。在 字段上创建索引之后，用 explain 命令来看看这个语句的执行情况。Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

select city,name,age from t where city='杭州' order by name limit 1000 ;

通常情况下，这个语句执行流程如下所示 ：

  1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
  2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
  7. 按照排序结果取前 1000 行返回给客户端。

按 XXX排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。

sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

rowid 排序

如果 MySQL 认为排序的单行长度太大会怎么做呢？

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。

整个执行流程就变成如下所示的样子：

  1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
  2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据按照字段 name 进行排序；
  7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

总之

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

join

关于 join 语句使用的问题，一般会集中在以下两类：

1、公司不让使用 join，使用 join 有什么问题呢？

2、如果有两个大小不同的表做 join，应该用哪个表做驱动表呢？

join语句执行流程

select * from t1 straight_join t2 on (t1.a=t2.a);

通过 explain 这条语句结果为

这个语句的执行流程是这样的：

  1. 从表 t1 中读入一行数据 R；
  2. 从数据行 R 中，取出 a 字段到表 t2 里去查找；
  3. 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
  4. 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ。

到底可不可以使用join？

  1. 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
  2. 如果使用 join 语句的话，需要让小表做驱动表。

驱动表如何选择呢？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

join语句如何优化呢？

join语句有两种算法， 分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。BNL 算法在大表 join 的时候性能就差多了，比较次数等于两个表参与 join 的行数的乘积，很消耗 CPU 资源。所以我们可以对它进行优化。

Multi-Range Read 优化

Multi-Range Read 优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘。其优化思路是因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

select * from t1 where ...;

语句的执行流程变成了这样：

  1. 根据索引 ，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
  2. 将 read_rnd_buffer 中的 id 进行递增排序；
  3. 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中，read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录，并继续循环。

如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access

KA 算法，其实就是对 NLJ 算法的优化。

NLJ执行的基本逻辑：

  1. 选择一个表作为外层表（通常是较小或者有更好选择性的表），另一个表作为内层表。
  2. 对于外层表中的每一行： a. 获取当前行。 b. 对于内层表中的每一行： i. 获取当前行。 ii. 检查这两个行是否满足连接条件（例如，等值连接条件）。 iii. 如果满足，则组合这两行的数据，并将结果添加到结果集中。
  3. 当两个表的所有行都已经被处理完毕，返回最终的结果集。

BKA的工作原理如下：

  1. 构建探查键（Build Probe Keys） ：
    • 对于外层表中的每一行，根据连接条件生成一个或多个探查键（probe keys）。这些键将用于访问内层表上的索引。
  1. 排序探查键（Sort Probe Keys） ：
    • 将所有生成的探查键进行排序。这一步是为了能够有效地批量读取内层表的数据，以减少随机I/O并充分利用顺序I/O的优势。
  1. 批量访问（Batch Access） ：
    • 使用排序后的探查键来批量访问内层表上的索引。因为探查键已经排序，所以可以一次性地高效扫描索引，从而找到所有匹配的记录。这个过程可能涉及到多次批量读取，取决于探查键的数量和大小。
  1. 结果组装（Result Assembly） ：
    • 一旦找到了所有匹配的记录，就将它们与外层表的相关行组合起来，形成最终的结果集。

大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响，你可以考虑增大 join_buffer_size 的值，减少对被驱动表的扫描次数。

也就是说，BNL 算法对系统的影响主要包括三个方面：

  1. 可能会多次扫描被驱动表，占用磁盘 IO 资源；
  2. 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
  3. 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。

我们执行语句之前，需要通过理论分析和查看 explain 结果的方式，确认是否要使用 BNL 算法。如果确认优化器会使用 BNL 算法，就需要做优化。优化的常见做法是，给被驱动表的 join 字段加上索引，把 BNL 算法转成 BKA 算法。

优化方法

  1. BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
  2. BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
  3. 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
  4. MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。
avatar
文章
阅读
粉丝