乐趣区

关于java:Group-By-深度优化真是绝了

jiezi

作者：虚心的小 K

起源：www.juejin.cn/post/6957696820621344775

导读

当咱们交友平台在线上运行一段时间后，为了给平台用户在搜寻好友时，在搜寻后果中举荐并置顶他感兴趣的好友，这时候，咱们会对用户的行为做数据分析，依据剖析后果给他举荐其感兴趣的好友。

这里，我采纳最简略的 SQL 分析法：对用户过来查看好友的性别和年龄进行统计，依照年龄进行分组失去统计后果。根据该后果，给用户举荐计数最高的某个性别及年龄的好友。

那么，假如咱们当初有一张用户浏览好友记录的明细表t_user_view，该表的表构造如下：

CREATE TABLE `t_user_view` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增 id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户 id',
  `viewed_user_id` bigint(20) DEFAULT NULL COMMENT '被查看用户 id',
  `viewed_user_sex` tinyint(1) DEFAULT NULL COMMENT '被查看用户性别',
  `viewed_user_age` int(5) DEFAULT NULL COMMENT '被查看用户年龄',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_viewed_user` (`user_id`,`viewed_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

为了方便使用 SQL 统计，见下面的表构造，我冗余了被查看用户的性别和年龄字段。

咱们再来看看这张表里的记录：

当初联合下面的表构造和表记录，我以 user_id=1 的用户为例，分组统计该用户查看的年龄在 18 ~ 22 之间的女性用户的数量：

SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

失去统计后果如下：

可见：

  • 该用户查看年龄为 18 的女性用户数为 2
  • 该用户查看年龄为 19 的女性用户数为 1
  • 该用户查看年龄为 20 的女性用户数为 3

所以，user_id=1的用户对年龄为 20 的女性用户更感兴趣，能够更多举荐 20 岁的女性用户给他。

如果此时，t_user_view 这张表的记录数达到千万规模，想必这条 SQL 的查问效率会直线降落，为什么呢？有什么方法优化呢？

想要晓得起因，不得不先看一下这条 SQL 执行的过程是怎么的？

Explain

咱们先用 explain 看一下这条 SQL：

EXPLAIN SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

执行完下面的 explain 语句，咱们失去如下后果：

在 Extra 这一列中呈现了三个 Using，这 3 个Using 代表了《导读》中的 groupBy 语句别离经验了 3 个执行阶段：

  1. Using where：通过搜寻可能的 idx_user_viewed_user 索引树定位到满足局部条件的viewed_user_id，而后，回表持续查找满足其余条件的记录
  2. Using temporary：应用长期表暂存待 groupBy 分组及统计字段信息
  3. Using filesort：应用 sort_buffer 对分组字段进行排序

这 3 个阶段中呈现了一个名词：长期表。这个名词我在《MySQL 分表机会：100w？300w？500w？都对也都不对！》一文中有讲到，这是 MySQL 连接线程能够独立拜访和解决的内存区域，那么，这个长期表长什么样呢？

上面我就先讲讲这张 MySQL 的长期表，而后，联合下面提到的 3 个阶段，具体解说《导读》中 SQL 的执行过程。

长期表

咱们还是先看看《导读》中的这条蕴含 groupBy 语句的 SQL，其中蕴含一个分组字段 viewed_user_age 和一个统计字段 count(*)，这两个字段是这条 SQL 中统计所需的局部，如果咱们要做这样一个统计和分组，并把后果固化下来，必定是须要一个内存或磁盘区域落下第一次统计的后果，而后，以这个后果做下一次的统计，因而，像这种存储两头后果，并以此后果做进一步解决的区域，MySQL 叫它 长期表。

刚刚提到既能够将两头后果落在内存，也能够将这个后果落在磁盘，因而，在 MySQL 中就呈现了两种长期表：内存长期表 和磁盘长期表。

内存长期表

什么是内存长期表？在晚期数据量不是很大的时候，以存储分组及统计字段为例，那么，基本上内存就能够齐全寄存下分组及统计字段对应的所有值，这个寄存大小由 tmp_table_size 参数决定。这时候，这个寄存值的内存区域，MySQL 就叫它内存长期表。

此时，或者你曾经感觉 MySQL 将两头后果寄存在内存长期表，性能曾经有了保障，然而，在《MySQL 分表机会：100w？300w？500w？都对也都不对！》中，我提到过内存频繁的存取会产生碎片，为此，MySQL 设计了一套新的内存调配和开释机制，能够缩小甚至防止长期表内存碎片，晋升内存长期表的利用率。

此时，你可能会想，在《为什么我调大了 sort_buffer_size，并发量一大，查问排序慢成狗？》一文中，我讲了用户态的内存分配器：ptmalloc 和 tcmalloc，无论是哪个分配器，它的作用就是防止用户过程频繁向 Linux 内核申请内存空间，造成 CPU 在用户态和内核态之间频繁切换，从而影响内存存取的效率。用它们就能够解决内存利用率的问题，为什么 MySQL 还要本人搞一套？

或者 MySQL 的作者感觉无论哪个内存分配器，它的实现都过于简单，这些复杂性会影响 MySQL 对于内存解决的性能，因而，MySQL 本身又实现了一套内存分配机制：MEM_ROOT。它的内存解决机制绝对比较简单，内存长期表的调配就是采纳这样一种形式。

上面，我就以《导读》中的 SQL 为例，具体解说一下分组统计是如何应用 MEM_ROOT 内存调配和开释机制的？

MEM_ROOT

咱们先看看 MEM_ROOT 的构造，MEM_ROOT设计比较简单，次要蕴含这几局部，如下图：

free：一个单向链表，链表中每一个单元叫 block，block 中寄存的是闲暇的内存区，每个 block 蕴含 3 个元素：

  • left：block中残余的内存大小
  • size：block对应内存的大小
  • next：指向下一个 block 的指针

如上图，free所在的行就是一个 free 链表，链表中每个箭头相连的局部就是 block，block 中有 left 和 size，每个 block 之间的箭头就是 next 指针

used：一个单向链表，链表中每一个单元叫 block，block 中寄存已应用的内存区，同样，每个 block 蕴含下面 3 个元素

min_malloc：管制一个 block 残余空间还有多少的时候从 free 链表移除，退出到 used 链表中

block_size：block对应内存的大小

block_num：MEM_ROOT 治理的 block 数量

first_block_usage：free链表中第一个 block 不满足申请空间大小的次数

pre_alloc：当开释整个 MEM_ROOT 的时候能够通过参数管制，抉择保留 pre_alloc 指向的block

上面我就以《导读》中的分组统计 SQL 为例，看一下 MEM_ROOT 是如何分配内存的？

调配

  1. 初始化MEM_ROOT，见上图：

    min_malloc = 32

    block_num = 4

    first_block_usage = 0

    pre_alloc = 0

    block_size = 1000

    err_handler = 0

    free = 0

    used = 0

  2. 申请内存，见上图：

    因为初始化 MEM_ROOT 时，free = 0，阐明 free 链表不存在，故向 Linux 内核申请 4 个大小为 1000/4=250 的block，结构一个 free 链表，如上图，链表中蕴含 4 个 block，联合后面free 链表构造的阐明，每个 block 中size为 250，left也为 250

  3. 分配内存，见上图：

    (1) 遍历 free 链表，从 free 链表头部取出第一个block，如上图向下的箭头

    (2) 从取出的 block 中划分 220 大小的内存区，如上图向右的箭头下面 -220，block 中的 left 从250变成30

    (3) 将划分的 220 大小的内存区调配给 SQL 中的 groupby 字段 viewed_user_age 和统计字段count(*)，用于前面的统计分组数据收集到该内存区

    (4) 因为第 (2) 步中，调配后的 block 中的 left 变成 30，30 < 32，即小于第(1) 步中初始化的 min_malloc，所以，联合下面min_malloc 的含意的解说，该 block 将插入 used 链表尾部，如上图底部，因为 used 链表在第 (1) 步初始化时为 0，所以，该 block 插入 used 链表的尾部，即插入头部

开释

上面还是以《导读》中的分组统计为例，咱们再来看一下 MEM_ROOT 是如何开释内存的？

image-20210323233158459.png

如上图，MEM_ROOT开释内存的过程如下：

  1. 遍历 used 链表中，找到须要开释的 block，如上图，block(30,250) 为之前已调配给分组统计用的block
  2. 将 block(30,250) 中的 left + 220，即30 + 220 = 250，开释该block 已应用的 220 大小的内存区，失去开释后的block(250,250)
  3. 将 block(250,250) 插入 free 链表尾部，如上图曲线箭头局部

通过 MEM_ROOT 内存调配和开释的解说，咱们发现 MEM_ROOT 的内存治理形式是在每个 Block 上间断调配，外部碎片根本在每个 Block 的尾部，由 min_malloc 成员变量管制，然而 min_malloc 的值是在代码中写死的，有点不够灵便。所以，对一个 block 来说，当 left 小于 min_malloc，从其申请的内存越大，那么block 中的 left 值越小，那么，该 block 的内存利用率越高，碎片越少，反之，碎片越多。这个写死是 MySQL 的内存调配的一个缺点。

磁盘长期表

当分组及统计字段对应的所有值大小超过 tmp_table_size 决定的值，那么，MySQL 将应用磁盘来存储这些值。这个寄存值的磁盘区域，MySQL 叫它磁盘长期表。

咱们都晓得磁盘存取的性能肯定比内存存取的性能差很多，因为会产生磁盘 IO，所以，一旦分组及统计字段不得不写入磁盘，那性能绝对是很差的，所以，咱们尽量调大参数tmp_table_size，使得组及统计字段能够在内存长期表中解决。

执行过程

无论是应用内存长期表，还是磁盘长期表，长期表对组及统计字段的解决的形式都是一样的。《导读》中我提到想要优化《导读》中的那条 SQL，就须要晓得 SQL 执行的原理，所以，上面我就联合下面解说的长期表的概念，具体讲讲这条 SQL 的执行过程，见下图：

  1. 创立长期表 temporary，表里有两个字段viewed_user_age 和count(*)，主键是 viewed_user_age，如上图，倒数第二个框temporary 示意长期表，框中蕴含两个字段 viewed_user_age 和count(*)，框内就是这两个字段对应的值，其中 viewed_user_age 就是这张长期表的主键
  2. 扫描表辅助索引树 idx_user_viewed_user，顺次取出叶子节点上的id 值，即从索引树叶子节点中取到表的主键 id。如上图中的 idx_user_viewed_user 框就是索引树，框右侧的箭头示意取到表的主键 id

  3. 依据主键 id 到聚簇索引 cluster_index 的叶子节点中查找记录，即扫描 cluster_index 叶子节点：

    (1) 失去一条记录，而后取到记录中的 viewed_user_age 字段值。如上图，cluster_index框，框中最左边的一列就是 viewed_user_age 字段的值

    (2) 如果长期表中没有主键为 viewed_user_age 的行，就插入一条记录 (viewed_user_age, 1)。如上图的 temporary 框，其左侧箭头示意将 cluster_index 框中的 viewed_user_age 字段值写入 temporary 长期表

    (3) 如果长期表中有主键为 viewed_user_age 的行，就将 viewed_user_age 这一行的 count(*) 值加 1。如上图的 temporary 框

  4. 遍历实现后，再依据字段 viewed_user_age 在sort_buffer中做排序，失去后果集返回给客户端。如上图中的最左边的箭头，示意将 temporary 框中的 viewed_user_age 和count(*)的值写入 sort_buffer，而后，在sort_buffer 中按 viewed_user_age 字段进行排序

通过《导读》中的 SQL 的执行过程的解说，咱们发现该过程经验了 4 个局部：idx_user_viewed_user、cluster_index、temporary 和 sort_buffer，比照下面 explain 的后果，其中前 2 个就对应后果中的 Using where，temporary 对应的是 Using temporary，sort_buffer 对应的是 Using filesort。

优化计划

此时，咱们有什么方法优化这条 SQL 呢？

既然这条 SQL 执行须要经验 4 个局部，那么，咱们可不可以去掉最初两局部呢，即去掉 temporary 和 sort_buffer？

答案是能够的，咱们只有给 SQL 中的表 t_user_view 增加如下索引：

ALTER TABLE `t_user_view` ADD INDEX `idx_user_age_sex` (`user_id`, `viewed_user_age`, `viewed_user_sex`);

你能够本人尝试一下哦！用 explain 康康有什么扭转！

小结

本章围绕《导读》中的分组统计 SQL，通过 explain 剖析 SQL 的执行阶段，联合长期表的构造，进一步分析了 SQL 的具体执行过程，最初，引出优化计划：新增索引，防止长期表对分组字段的统计，及 sort_buffer 对分组和统计字段排序。

当然，如果切实无奈防止应用长期表，那么，尽量调大tmp_table_size，防止应用磁盘长期表统计分组字段。

思考题

为什么新增了索引 idx_user_age_sex 能够防止长期表对分组字段的统计，及 sort_buffer 对分组和统计字段排序？

提醒：联合索引查找的原理。

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2021 最新版)

2. 别在再满屏的 if/ else 了，试试策略模式，真香！！

3. 卧槽！Java 中的 xx ≠ null 是什么新语法？

4.Spring Boot 2.5 重磅公布，光明模式太炸了！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

退出移动版