作者：虚心的小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开发手册（嵩山版）》最新公布，速速下载！

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