作者：谦虚小K来源：www.juejin.cn/post/6957696820621344775简介当我们的交友平台上线运行一段时间后，为了平台用户在搜索时在搜索结果中给予推荐friends感兴趣的朋友，这时候我们会对用户的行为进行数据分析，根据分析结果向他推荐感兴趣的朋友。这里，我使用最简单的SQL分析方法：统计用户以往浏览过的好友的性别和年龄，按年龄分组得到统计结果。根据结果??，向用户推荐某个性别和年龄段的人数最多的朋友。那么，假设我们现在有一个明细表t_user_view，供用户浏览好友记录。表结构如下：CREATETABLE`t_user_view`(`id`bigint(20)unsignedNOTNULLAUTO_INCREMENTCOMMENT'auto-incrementid',`user_id`bigint(20)DEFAULTNULLCOMMENT'userid',`viewed_user_id`bigint(20)DEFAULTNULLCOMMENT'vieweduserid',`viewed_user_sex`tinyint(1)DEFAULTNULLCOMMENT'viewedusergender',`viewed_user_age`int(5)DEFAULTNULLCOMMENT'Vieweduser'sage',`create_time`datetime(3)DEFAULTCURRENT_TIMESTAMP(3),`update_time`datetime(3)DEFAULTCURRENT_TIMESTAMP(3)ONUPDATECURRENT_TIMESTAMP(3),PRIMARYKEY(`id`),UNIQUEKEY`idx_user_viewed_user`(`user_id`,`viewed_user_id`))引擎=InnoDB默认字符集=utf8；为了方便使用SQL统计，见上表结构，我把查看用户的性别和年龄字段冗余了。我们再看一下这张表中的记录：现在结合上面的表结构和表记录，我以user_id=1的用户为例，统计18~22岁的女性用户数量此用户分组查看：SELECTviewed_user_ageasage,count(*)asnumFROMt_user_viewWHEREuser_id=1ANDviewed_user_ageBETWEEN18AND22ANDviewed_user_sex=1GROUPBYviewed_user_age统计结果如下：可以看出，numberoffemaleuserswhoseageis18usersviewedbythisuseris2年龄为19的女性用户数量为1，年龄为20的女性用户数量为3。因此user_id=1的用户更感兴趣20的女性用户，更多20的女性用户可以推荐给他。如果此时t_user_view表的记录数达到千万条，这条SQL的查询效率就会直线下降。为什么？有什么办法可以优化吗？要想知道原因，就得先看看这条SQL执行的过程。Explain我们用explain来看这条SQL：EXPLAINSELECTviewed_user_ageasage,count(*)asnumFROMt_user_viewWHEREuser_id=1ANDviewed_user_ageBETWEEN18AND22ANDviewed_user_sex=1GROUPBYviewed_user_age执行上面的explain语句后，我们得到如下结果：Extra列中出现了三个Using，这三个Using代表《导读》中的groupBy语句经历了三个执行阶段：条件，然后回表继续查找满足其他条件的记录Usingtemporary：使用临时表临时存储groupBy分组和统计字段信息Usingfilesort：使用sort_buffer对分组字段进行排序。这三个阶段出现了一个名词：临时表。我在《MySQL分表时机：100w？300w？500w？都对也都不对！》的文章中提到了这个词。这是MySQL连接线程可以独立访问和处理的内存区域。那么，这张临时表长什么样呢？下面先说说这张MySQL临时表，然后结合上面说的三个阶段，详细讲解《导读》中SQL的执行过程。对于临时表，我们先看《导读》中包含groupBy语句的SQL，其中包含一个分组字段viewed_user_age和一个统计字段count(*)。这两个字段在这个SQL中统计是必须的。如果我们要做这样的统计和分组，并固化结果，肯定需要一块内存或者磁盘区域来drop第一次统计的结果，然后用这个结果做下一次的统计，所以，像这样的中间存储的是结果，以及根据结果做进一步处理的区域，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所在的行是一个自由链表，链表中每个箭头连接的部分是块，块中有left和size，每个块之间的箭头是使用的下一个指针：单向链表，每个单元在链表称为块，使用的内存区域存储在块中。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是如何分配内存的？分配并初始化MEM_ROOT，如上图：min_malloc=32block_num=4first_block_usage=0pre_alloc=0block_size=1000err_handler=0free=0used=0申请内存，如上图：由于初始化MEM_ROOT时free=0，说明表示freelist不存在，于是向Linuxkernel申请了4个大小为1000/4=250的block，构造了一个freelinkedlist。如上图所示，链表包含4个块。结合前面对空闲链表结构的描述，每块大小为250，左边也分配了250内存，见上图：(1)遍历空闲链表，从中取出第一个块自由链表的头部，如上图向下的箭头（2）从取出的块中划分220的内存区域，如上图右箭头上方为-220，block中左边由250变为30(3)将划分好的220大小的内存区域分配给SQL中的groupby字段viewed_user_age和统计字段count(*)进行后续的统计分组在这块内存区收集数据(4)由于在步骤(2)中，分配块中的left变为30，30<32，小于步骤(1)中初始化的min_malloc，所以，结合上面的min_malloc解释一下意思，这个block会被插入到used链表的末尾，如上图底部所示，由于在步骤（1）初始化时used链表为0，所以这个block会被插入到使用过的链表的末尾，也就是会被插入到头部来释放下面的还是以中的分组统计为例，我们来看看MEM_ROOT是如何释放内存的？image-20210323233158459.png如上图所示，MEM_ROOT释放内存的过程如下：遍历使用过的链表，找到需要释放的块。如上图所示，block(30,250)是之前分配给groupstatistics的block。left+220，即30+220=250，释放block使用的220内存区域，得到释放的block(250,250)，在freelist的尾部插入block(250,250)，如图在上面的曲线箭头部分，通过MEM_ROOT的内存分配和释放解释，我们发现MEM_ROOT的内存管理方式是在每个Block上连续分配的，内部碎片基本都在每个Block的末尾，由min_malloc成员控制变量，但是min_malloc的值是硬编码在代码中的，有点不够灵活所以，对于一个block来说，当left小于min_malloc时，向其申请的内存越大，left中的值越小block，那么block的内存利用率越高，碎片越少，反之，碎片越多。这段死代码是MySQL内存分配的缺陷。磁盘临时表当分组和统计字段对应的所有值的大小超过tmp_table_size确定的值时，MySQL会使用磁盘来存储这些值。这个存储值的磁盘区域，MySQL称之为磁盘临时表。我们都知道磁盘访问的性能肯定比内存访问差很多，因为会产生磁盘IO，所以一旦分组和统计字段都要写到磁盘，性能就比较差，所以我们尝试调整大参数tmp_table_size可以使组和统计字段在临时内存表中处理。无论执行过程使用内存临时表还是磁盘临时表，临时表对组和统计字段的处理方式都是一样的。在《导读》中，我提到过如果要优化《导读》中的SQL，需要了解SQL的执行原理。因此，我将结合上面讲解的临时表的概念，详细描述这条SQL的执行过程，见下图：创建临时表temporary，表中有viewed_user_age和count(*)两个字段，主键为viewed_user_age，如上图，倒数第二个框temporary代表临时表，和box包含viewed_user_age和count(*)两个字段，box是这两个字段对应的值，其中viewed_user_age是这张临时表的主键，扫描辅助索引树idx_user_viewed_user，取出叶子上的id值节点，即从索引树的叶子节点中获取表的主键id。如上图idx_user_viewed_user框为索引树，框右侧的箭头表示根据主键id获取表的主键id，在叶子节点中查找记录聚簇索引cluster_index，即扫描cluster_index叶子节点：(1)得到一条记录，然后得到记录中viewed_user_age字段的值。如上图，在cluster_index框内，框内最右边一列为viewed_user_age字段的值(2)如果临时表中没有主键为viewed_user_age的行，则插入一条记录(viewed_user_age,1)。如上图临时框所示，向左箭头表示将cluster_index框中viewed_user_age字段的值写入temporary临时表（3）如果临时表中有主键为viewed_user_age的行，加上viewed_user_age第1行的count(*)值。如上图临时框遍历完成后，sort_buffer根据字段viewed_user_age排序，结果集返回给客户端。如上图最右边的箭头所示，表示将临时框中的viewed_user_age和count(*)的值写入sort_buffer，然后通过sort_buffer中的viewed_user_age字段进行排序。通过《导读》中SQL执行过程的讲解，我们发现该过程经历了四个部分：idx_user_viewed_user、cluster_index、temporary和sort_buffer。对比上面的explain结果，前两个对应结果中的Usingwhere，temporary对应Usingtemporary，sort_buffer对应Usingfilesort。优化方案此时，我们可以做些什么来优化这条SQL呢？既然这个SQL执行需要经过4个部分，那我们能不能去掉最后两部分，即去掉temporary和sort_buffer呢？答案是肯定的，我们只需要在SQL中为表t_user_view增加如下索引即可：你可以自己试试！解释康康有什么变化！小结本章围绕《导读》中的组统计SQL，通过explain分析SQL的执行阶段，结合临时表的结构，进一步分析SQL的详细执行过程。最后引出一个优化方案：通过临时表Field统计加索引避免分组，sort_buffer对分组统计字段进行排序。当然，如果实在无法避免使用临时表，那就尽量增大tmp_table_size，避免使用磁盘临时表统计分组字段。思考题为什么要加索引idx_user_age_sex来避免统计临时表的分组字段，而sort_buffer是用来排序分组和统计字段的？提示：结合索引查找的原理。近期热点文章推荐：1.1,000+Java面试题及答案（2021最新版）2.别在满屏的if/else中，试试策略模式，真的很好吃！！3.操！Java中xx≠null的新语法是什么？4、SpringBoot2.5发布，深色模式太炸了！5.《Java开发手册（嵩山版）》最新发布，赶快下载吧！感觉不错，别忘了点赞+转发！
