group by优化 hive group by 优化总结 _经验知识

导读当咱们交友平台在线上运行一段期间后。为了给平台客户在搜索好友时。在搜索结果中介绍并置顶他感兴趣的好友。这时候。咱们会对客户的行为做数据分析。根据分析结果给他介绍其感兴趣的好友。
这里。我选用最简单的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个执行阶段：
Using where：通过搜索可能的idx_user_viewed_user索引树定位到满足部分条件的viewed_user_id 。然后。回表继续查找满足其他条件的记录Using temporary：使用临时表暂存待groupBy分组及统计字段信息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设计了一套新的内存分配和释放机制。可以减少甚至避免临时表内存碎片。提升内存临时表的利用率。