记录一次简单Presto查询优化

问题描述

    公司项目老板想看公司特定的汇总数据分析，这些信息数据比较零散，分析困难，同时数据量比较庞大，此外数据查询需要实时性，因此项目团队考虑到大数据hive处理数据采集，然后汇总到presto数据库查询，最后数据汇总也有上千万数据。后端多维度实施分析数据返回给前端浏览器时，效率比较慢。因此，本文针对presto查询慢问题分析以及一些慢查询分析思路，如有写不好，望多多指教。

presto平台数据库脚本

    如下是模拟平台数据库脚本，主要有两张表，表结构是一对多,其中 USER 表只有2000条数据，cource两个月数据量有一千多万数据

DROP TABLE IF EXISTS USER;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户id',
  `mobile` int(11) NOT NULL COMMENT '手机号',
  `state` tinyint(2) NOT NULL DEFAULT '1' COMMENT '是否有效,1可用，0不可用',
  `login_type` tinyint(4) NOT NULL DEFAULT '10' COMMENT '登录方式，10 用户密码登录；20短信验证码登录；30微信登陆；40指纹登陆',
  `created_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  KEY `idx` (`mobile`) COMMENT '用户手机号'
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

CREATE TABLE `cource` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `cource_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '课程id',
  `user_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'USER表的用户user_id',
  `state` tinyint(2) NOT NULL DEFAULT '1' COMMENT '是否有效,1可用，0不可用',
  `cource_name` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '课程名称',
  `created_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_cource_id` (`cource_id`,`user_id`) COMMENT '课程id',
  KEY `idx_user_id` (`user_id`) COMMENT '用户id索引',
  KEY `idx_cource_name` (`cource_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程表';

问题分析

当初慢数据脚本大概样式如下

select 
	u.updated_time,
	sum(case when c.cource_name like '课程名称%' THEN 1 else 0 END) as '课程名称'
from cource c
LEFT JOIN user u on c.user_id= u.user_id
where 
	u.user_id in(1,2,3) and u.state=1 and (c.cource_name like '课程名称%' or c.cource_name like  '%3158') 
group by u.updated_time

当初分析，随着user_id的查询数据量比较多时，查询速度就会越来越慢，一开始以为是left join的问题，后来了解到大数据居于缓存查询，和mysql查询的索引概念不一致,修改后变为with数据量获取查询
“Presto中join的默认算法是broadcast join，即将join左边的表分割到多个worker，然后将join右边的表数据整个复制一份发送到每个worker进行计算。如果右边的表数据量太大，则可能会报内存溢出错误。” 得以解决性能问题。

总结

1. presto查询和mysql不一样，没有索引的概念，有分区的性质，还有排序的理念，查询语句尽量加上分区，此外如果频繁查询的字段，最好插入数据库前加索引。
2. presto 关联条件过多时，使用缓存表，缓存表可以用with创建或者create table

WITH sample AS (SELECT * FROM (VALUES ('strA', 'strB'), ('strC', 'strD'), ('strE', 'strF')) AS account (name, cat)),
	slab (SNo,Amount) AS (VALUES (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(6,6000),(7,7000),(8,8000),(9,9000),(10,10000),(11, 11000),(12,12000),(13,13000),(14,14000),(15,15000),(16,16000),(17,17000),(18,18000),(19,19000),(20,20000),(21,21000),(22,22000),(23,23000),(24,24000),(25,25000),(26,26000),(27,27000),(28,28000),(29,29000),(30,30000),(31,31000),(32,32000),(33,33000),(34,34000),(35,35000),(36,36000),(37,37000),(38,38000),(39,39000),(40,40000),(41,41000),(42,42000),(43,43000),(44,44000),(45,45000),(46,46000),(47,47000),(48,48000),(49,49000),(50,50000),(51,51000)) 
SELECT name, cat,Amount from sample,slab ;

-- 或者
with w as (
    select * from (values 1) t(x)
)
select * from w;

4. 尽量少查询原则，避免多次遍历和深度查询，这个和mysql类似
5. 遇到不懂知识点方面的知识时，尽量多去思考，多积累基础知识。

参考资料

【1】 Presto查询优化
【2】presto-create-table-with-with-queries