作者：dcguo使用sql做数仓开发有一段时间了，现做一下梳理复盘，主要内容包括sql语法、特性、函数、优化、特殊业务表实现等。mysql数据结构常用innodb存储为B+树特点多路平衡树，m个子树中间节点就包含m个元素，一个中间节点是一个page(磁盘页)默认16kb；子节点保存了全部得元素，父节点得元素是子节点的最大或者最小元素，而且依然是有序得；节点元素有序，叶子节点双向有序，便于排序和范围查询。优势平衡查找树，logn级别crud；单一节点比二叉树元素更多，查询io次数更少；所有查询都要查询到叶子节点性能稳定；所有节点形成逻辑有序链表，便于排序查询范围查询。索引优化索引是提升性能主要手段，主要用到的索引基本为以下三种：聚簇索引一般表会用自增主键做聚集索引，没有的话mysql会默认创建，但是一旦确定之后这个主建得更改代价就会很高，所以建表时候要考虑自增主建不能频繁update非聚簇索引根据实际情况自行添加得索引都是辅助索引，就是一个为了寻找主键索引得二级索引，就是先找到主键索引再通过主键索引找数据。辅助索引可能没有我们需要的select列，这就引出innodb非聚集索引独有得耗时操作回表，sql重要得优化操作索引覆盖。覆盖索引: 指从辅助索引就可以得到查询结果，不需要“回”到聚集索引中查询；辅助索引可能是等值查询，范围查询或者全索引扫描。回表:对二级查询中查询到的每个主键，都需要回到聚集索引中在查询数据行。比如开发人员最喜爱得select*...就经常会回表回表理解：select*导致业务覆盖不到索引，那么优化器决策后很可能就不走辅助索引了，因为辅助索引上拿到的key太多了，随机回表开销太大，还不如走聚集索引，经常出现再范围查询,join操作上，但是现在磁盘都是ssd,不怕随机读，所以我们又可以用forceindex()操作强制优化器走辅助索引。demo: -- 辅助索引select * from t where a > '1999-08-01';如果从辅助索引取，根据一些过滤条件得到50w行，此时我还需要回表50w次，50w*3层=150w次i/o，而且因为回表时主键是无序的，所以是随机io。如果我不走辅助索引，直接顺序扫描这150w行的数据，需要(50w*3层)/(16KB/100B)约1w次i/o，而且是顺序io。优化方案mrr将二级索引上查询出来的主键排序之后在回表，explain得extra有一列usingmrr。复合索引其实还是一个b+树，每个节点是几个字段值concat起来的元组，比如复合索引(a,b)的b+树上，对(a)列是有序的，对(a,b)组合列也是有序的，但是对(b)列却不一定是有序的，对其叶子节点上带的pk列也是无序的。聚簇索引/非聚簇索引sql语法casewhen--demo1 case 搜索函数 case when xxx then xxx else endselect id, case when score 60)-- 使用临时表select distinct id from tmp;groupby/withrollupgroupby主要是用来做数据聚合需要选择字段作为聚合维度后，然后通过聚合函数得到汇总值的过程。count，sum，avg，...max/min，std，variance，...rank，first/last_value，row_number，...demo:select score, count(distinct id)from testgroup by score优化: 分组是一个相对耗时的操作，我们可以先通过where缩小数据的范围之后，再分组；也可以将分组拆分，如果是大表多维度分组，可以使用withas语法先计算一部分得到临时表然后再利用临时表进行计算，sql也可以简化。withrolluprollup是groupby子句的扩展，rollup选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。-- demo-- 下图结果第三行就是超级聚合行select name, sum(score)from studentsgroup by rollup(name)namesum(score)dc100xc100NULL200tag:如何区分超级聚合行得NULL和普通NULL？使用grouping函数可以识别超级聚合形成的NULL,避免和普通的NULL混淆。union/unionall/intersect/except用法基本类似，只举例部分union并集intersect交集except差集-- union 去重, union all 不去重select column_name(s) from table_name1unionselect column_name(s) from table_name2limitlimit分页查询使用使用select idfrom testwhere id > 1000000 limit 20;in/notin/exists/notexists/betweenin/notin: 作用不用多说exists/notexists：强调的是是否返回结果集exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false；exist指定一个子查询，检测行的存在。in/exists对比 in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询；如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；而notextsts的子查询依然能用到表上的索引；所以无论那个表大，用notexists都比notin要快。between:如果表达式大于或等于>=low值且小于或等于b.c1order by a.a1leftjoin/rightjoin外连接左外连接略右外连接略fulljoin全连接fulljoin略leftsemijoin左半连接只显示左表中的记录。可通过在leftsemijoin，where...in和whereexists中嵌套子查询来实现。左半连接与左外连接的区别是，左半连接将返回左表中符合join条件的记录，而左外连接将返回左表所有的记录，匹配不上join条件的记录将返回null值。select student_info.name, student_info.courseIdfrom student_infoleft semi join course_infoon student_info.courseId = course_info.courseId隐式连接与内连接功能相同，返回两表中满足where条件的结果集，但不用join显示指定连接条件select student_info.name, course_info.courseNamefrom student_info,course_infowhere student_info.courseId = course_info.courseId;having使用聚合函数进行计算使用having子句筛选分组where/onjoin时候where/on不可以混用innerjoin中where可以代替on但是on不能代替whereon是usingon的简便写法explain(mysql)字段名含义id查询或者关联查询得顺序如果没有子查询且只有一个查询，则为一个常数1，表示第一步如果有子查询则子查询为1，父查询为2id相同查询顺序从上到下，否则id越大，优先级越高select_type显示查询种类是简单还是复杂selectSIMPLE查询中不包含子查询或者unionPRIMARY查询中若包含任何复杂的子查询，最外层查询则被标记为PRIMARYUNIONunion查询中第二个或者后面的selectSUBQUERY子查询中第一个selectUNIONRESULTunion的结果DEPENDENTUNION查询中第二个或者后面的select，取决于外面的查询DEPENDENTSUBQUERY子查询中的第一个select，取决于外面的查询DERIVED派生表的select,from子句的子查询UNCACHEABLESUBQUERY一个子查询的结果不能被缓存，必须重新评估外连接的第一行table显示这一行的数据是关于哪张表的type访问类型，all,index,rane,ref,eq_red,const,system,null性能从差到好all全表遍历index索引树遍历range检索给定范围的行，使用索引选择行ref表示表的连接匹配条件，即哪些列或者常量被用于查找索引列上的值eq_ref类似于ref，只是使用的索引是主键或者唯一索引const、system查询优化为了常量，比如主键再where列表里面，system是const特例，表只有一行则是systemnull优化分解语句后，执行时甚至不需要访问表或者所以extra解决查询的详细信息Usingfilesort表示mysql会对结果使用外部排序，不是按照索引从表内读行，无法利用索引Usingindex表示覆盖索引得到结果，避免回表Usingwhere列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回，表示对表的全部请求都是索引的部分Usingtemporary表示需要临时表来存储结果集，常见于排序和分组查询Usingjoinbuffer获取链接条件时候没使用索引，并且需要连接缓冲区存储中间结果Impossiblewhere强调了where语句会导致没有符合条件的行Selecttablesoptimizedaway意味着仅通过使用索引，优化器可能从聚合函数结果中返回一行keykey_lenrefrowspossible_keyskey列显示mysql实际决定使用的键key_len表示索引中使用的字节数，可以计算查询使用的索引的长度，越短越好ref表示连接匹配条件，那些列或者常量被用于查找索引列上的值rows表示mysql根据表统计信息以及索引选用情况，估算查询需要读取的行数possible_keys表示可以使用哪个索引查到记录，查询涉及的字段若存在索引则会被列出，但不一定使用hive/spark/mysql8.0之前也没有接触过大数据相关知识，简单了解这几种sql其实大差不差，主要区别就是hive/spark操作的数据可以很大很大，单机存不下，所以数据文件位于分布式文件系统HDFS。hive:sql解析引擎，将sql转译成map/reducejob然后再hadoop执行，相当于hadoop的客户端工具。hive的表其实就是hdfs的目录，按照表名分开文件夹，就是分区表，分区值就是子文件夹，可以直接再map/reducejob里面使用。hive数据存储格式textfile,sequencefile,avro,rcfile,orcfile,parquetparquet不支持update操作(数据写成后不可修改),不支持acid等 业务中建设数仓时，数仓数据都是由业务数据库拉取而来，数仓本身不进行什么更新操作，仅仅只有新增这种操作，所以使用parquet。内部表/外部表hive默认创建的是内部表外部表没有办法直接truncatetable创建外部表的sqlCREATE EXTERNAL TABLE IF NOT EXISTS test ( `quota` STRING COMMENT '', `package` INT COMMENT '', `all_sys` INT COMMENT '') COMMENT 'test'PARTITIONED BY (timeline STRING COMMENT '时间分区')STORED AS ARQUET关于删数据？在删除内部表的时候，hive将会把属于表的元数据和数据全部删掉；而删除外部表的时候，hive仅仅删除外部表的元数据，数据是不会删除的，也就是说，外部表的数据其实不是hive自己管理的。如何选择创建内部或者外部表？但是作为一个经验，如果所有处理都需要由hive完成，应该创建表，否则使用外部表，基于此，我们使用数仓都是基于hive完成，所以应该创建内部表。内置函数函数主要有取值函数和变换函数等round，abs，ceilsin，cos，sqrt时间unix时间戳转字符串from_unixtime(unix_timestamp(),'yyyy-MM-dd')from_unixtime(unix_timestamp(),'%Y-%m-%d')截取部分日期，其他部分默认为01selecttrunc('2009-02-12','year')字符串转uninx时间戳to_unix_timestamp('2022-04-27','yyyy-MM-dd')随机数获取i≤r≤j这个范围的随机整数rselectfloor(i+(rand()*(j-i+1))字符串-- 函数用于将多行数据聚合为单行，从而提供与特定值关联的数据列表，它将以逗号来分割列表-- 结果-- 1 python,c-- 2 r,java-- 3 swiftselect id,wm_concat(',',name) as language from tmp_test group by id;-- 字符串截取-- CDEselect substring("ABCDE", 3)-- json 解析select get_json_object({"score":3}, '$.score')统计函数--coalesce(expression_1,expression_2,...,expression_n) 依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值select coalesce(null, null, '200')判断语句-- 条件判断，满足第一个表达式返回 1, 否则返回 0select if(1>0, 1, 0)列转行concat_ws-- 结果: a#b#cselect concat_ws('#', 'a', 'b', 'c', null)collect_list/collect_set-- 特性: 它们都是将分组中的某列转为一个数组返回，不同的是 collect_list 不去重而 collect_set 去重-- collect_set 去重, collect_list 不去重-- 还可以利用 collect 来突破 group by 的限制, hive 中在 group by 查询的时候要求出现在 select 后面的列都必须是出现在 group by 后面的，即 select 列必须是作为分组依据的列select username, collect_list(video_name)[0] from t_visit_video group by username;group_concat--结果--+----------+--------+--|lastname|name|--+----------+--------+--|a|aa,ab|--|b|ba,bb|--+----------+--------+selectlast_name,group_concat(name)asnamefromtestwherelastnamein('a','b')lateralviewexplode-- 数据准备-- id score-- 1 100,96,98,100,96,85-- 2 98,97,100,85,99,100-- 计算-- 找到具备 100 的 idselect *from socre lateral view explode(spilt(scores, ',')) score AS each_scorewhere each_score in ("100")udf函数其实就是一个简单的函数，执行过程就是在hive转换成mapreduce程序后，执行java方法，类似于像mapreduce执行过程中加入一个插件，方便扩展。udf只能实现一进一出的操作，如果需要实现多进一出，则需要实现udaf。hive可以允许用户编写自己定义的函数udf，来在查询中使用。使用udf函数进行业务内复杂结构字段提取-- parse_components 就是业务种自定义的 udf 函数，用来解析一个复杂得动态字段，此字段根据不同的模板可能出现得字段枚举超过百种select test..., parse_components(doc.components, '100', '101').test as template_fieldfrom testorder by test窗口函数ps:以前一直不知道为啥叫窗口函数，总联想到滑动窗口，但是其实不是这样partitionby的结果是分组的结果，每个组都是不同的范围，窗口的意思就是范围它可以做什么？有些需求可以使用窗口函数完成，如下：排名问题，根据科目进行分组topn问题，找出每个科目前三的同学进行奖励增量表问题某字段某段时间移动平均某种行为前百分之多少的数据此时可以引入窗口函数该函数也叫olap函数（onlineanallyticalprocessing，联机分析处理），可以对数据库数据进行实时分析处理它的执行顺序where之后demo就下面图2这个查询不用窗口该咋写？各种子查询想想都难受，so使用窗口。我们做到了，在一个单个sql中计算列的综合，以及每一列占总数得比例，以及当前列，这就是窗口函数得一个突破。基本语法-- partition by 用于给结果集分组，另外如果不指定那么会默认把整个结果集作为分组-- partition by 需要分组的列名-- order by 需要排序的列名-- rows between 参与计算的行起始位置 and 参与计算的行终止位置-- over括号中的那些如果不需要可以省略 over (partition by order by rows between and )-- rows between 指定计算起始行和结束行的方法-- rows between 3 preceding and current row 包括本行和前三行-- rows between current row and 3 following 包括本行和后三行-- rows between unbounded preceding and current row 包括本行和该分组内之前所有的行-- rows between current row and unbounded following 包括本行和该分组内之后所有行-- rows between 5 preceding and 1 following 包括前五行和后一行-- over (order by x) 相当于 over(order by x rows between unbounded preceding and current now) 从前方无界到当前行-- over () 相当于从前方无界到后方无界，整组内容-- 另, partition 子句可省略省略就是不指定分组-- 例:select *, rank() over (order by scores desc) as ranking from students哪些函数可以使用窗口函数专用函数rank/dens_rank/row_numberrank/dens_rank/row_number这三个函数得区别是分组排序后得到的虚拟rank列不同实际上此函数可以为查出来的每一行增加rank序号rankdens_rankrow_number注意rank()函数中得到的rank值可能是会出现重复值，如果要取1条，需要sql查到的数据不重复，rank=1不能保证仅取1条,除非你使用得函数是row_number()：demo大概场景就是，我们需要查询一张表，要按照某个字段a去排序另一个字段b，并且每个c字段只取前n条数据select a.id, a.a, a.b, a.c, a.d from (select t.id, t.a, t.b, t.c, t.d, rank() over(partition by t.a order by t.b desc) rk from test t) awhere rk 、!、notin、notlike等，会导致全表扫描。这条规定想满足其实很难，有些业务必不可免需要用到，那么可以考虑如果数据量大的情况使用以下用法：select oid from order where uid = 1 and status != 1;这条sql只要uid有索引，就可以先走索引缩小数据范围，此时再接上一个负向查询也没什么性能影响了。拆分大的insert/delete子查询往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。分页优化-- demo1select id, content from news order by title limit 50,5--> 优化后select news.id, news.contentfrom news inner join ( select id from news order by title limit 50,5) as tmp using(id)-- demo2select id from t limit 10000, 10--> 优化后select id from t where id > 10000 limit 10groupby/orderby优化确保任何groupby和orderby的列只涉及到一个表中的列，这样mysql才可以用索引去优化。join优化当表a和表b都用列c列来关联时候，如果优化器关联的顺序是ab，那么只需要再b表c列添加索引即可；具体原因可以参考优化器优化sql后得执行逻辑，反推就可以得到以上结果。谓词下推谓词下推将查询语句中的过滤表达式计算尽可能下推到距离数据源最近的地方，以尽早完成数据的过滤，进而显著地减少数据传输或计算的开销。谓词下推案例-- 谓词下推到存储层-- demo1select * from t where a = '2000-01-01' group by A.give_day)select a.give_day, -- a.num, sum(case when b.give_day = a.give_day then b.num else 0 end), sum(b.num) as all_numfrom tmp a, tmp bwhere b.give_day
|手机版|心飞设计-版权所有：微度网络信息技术服务中心
( 鲁ICP备17032091号-12 )|网站地图
发表于 2024-9-21 02:31:57
ARQUET关于删数据？在删除内部表的时候，hive将会把属于表的元数据和数据全部删掉；而删除外部表的时候，hive仅仅删除外部表的元数据，数据是不会删除的，也就是说，外部表的数据其实不是hive自己管理的。如何选择创建内部或者外部表？但是作为一个经验，如果所有处理都需要由hive完成，应该创建表，否则使用外部表，基于此，我们使用数仓都是基于hive完成，所以应该创建内部表。内置函数函数主要有取值函数和变换函数等round，abs，ceilsin，cos，sqrt时间unix时间戳转字符串from_unixtime(unix_timestamp(),'yyyy-MM-dd')from_unixtime(unix_timestamp(),'%Y-%m-%d')截取部分日期，其他部分默认为01selecttrunc('2009-02-12','year')字符串转uninx时间戳to_unix_timestamp('2022-04-27','yyyy-MM-dd')随机数获取i≤r≤j这个范围的随机整数rselectfloor(i+(rand()*(j-i+1))字符串-- 函数用于将多行数据聚合为单行，从而提供与特定值关联的数据列表，它将以逗号来分割列表-- 结果-- 1 python,c-- 2 r,java-- 3 swiftselect id,wm_concat(',',name) as language from tmp_test group by id;-- 字符串截取-- CDEselect substring("ABCDE", 3)-- json 解析select get_json_object({"score":3}, '$.score')统计函数--coalesce(expression_1,expression_2,...,expression_n) 依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值select coalesce(null, null, '200')判断语句-- 条件判断，满足第一个表达式返回 1, 否则返回 0select if(1>0, 1, 0)列转行concat_ws-- 结果: a#b#cselect concat_ws('#', 'a', 'b', 'c', null)collect_list/collect_set-- 特性: 它们都是将分组中的某列转为一个数组返回，不同的是 collect_list 不去重而 collect_set 去重-- collect_set 去重, collect_list 不去重-- 还可以利用 collect 来突破 group by 的限制, hive 中在 group by 查询的时候要求出现在 select 后面的列都必须是出现在 group by 后面的，即 select 列必须是作为分组依据的列select username, collect_list(video_name)[0] from t_visit_video group by username;group_concat--结果--+----------+--------+--|lastname|name|--+----------+--------+--|a|aa,ab|--|b|ba,bb|--+----------+--------+selectlast_name,group_concat(name)asnamefromtestwherelastnamein('a','b')lateralviewexplode-- 数据准备-- id score-- 1 100,96,98,100,96,85-- 2 98,97,100,85,99,100-- 计算-- 找到具备 100 的 idselect *from socre lateral view explode(spilt(scores, ',')) score AS each_scorewhere each_score in ("100")udf函数其实就是一个简单的函数，执行过程就是在hive转换成mapreduce程序后，执行java方法，类似于像mapreduce执行过程中加入一个插件，方便扩展。udf只能实现一进一出的操作，如果需要实现多进一出，则需要实现udaf。hive可以允许用户编写自己定义的函数udf，来在查询中使用。使用udf函数进行业务内复杂结构字段提取-- parse_components 就是业务种自定义的 udf 函数，用来解析一个复杂得动态字段，此字段根据不同的模板可能出现得字段枚举超过百种select test..., parse_components(doc.components, '100', '101').test as template_fieldfrom testorder by test窗口函数ps:以前一直不知道为啥叫窗口函数，总联想到滑动窗口，但是其实不是这样partitionby的结果是分组的结果，每个组都是不同的范围，窗口的意思就是范围它可以做什么？有些需求可以使用窗口函数完成，如下：排名问题，根据科目进行分组topn问题，找出每个科目前三的同学进行奖励增量表问题某字段某段时间移动平均某种行为前百分之多少的数据此时可以引入窗口函数该函数也叫olap函数（onlineanallyticalprocessing，联机分析处理），可以对数据库数据进行实时分析处理它的执行顺序where之后demo就下面图2这个查询不用窗口该咋写？各种子查询想想都难受，so使用窗口。我们做到了，在一个单个sql中计算列的综合，以及每一列占总数得比例，以及当前列，这就是窗口函数得一个突破。基本语法-- partition by 用于给结果集分组，另外如果不指定那么会默认把整个结果集作为分组-- partition by 需要分组的列名-- order by 需要排序的列名-- rows between 参与计算的行起始位置 and 参与计算的行终止位置-- over括号中的那些如果不需要可以省略 over (partition by order by rows between and )-- rows between 指定计算起始行和结束行的方法-- rows between 3 preceding and current row 包括本行和前三行-- rows between current row and 3 following 包括本行和后三行-- rows between unbounded preceding and current row 包括本行和该分组内之前所有的行-- rows between current row and unbounded following 包括本行和该分组内之后所有行-- rows between 5 preceding and 1 following 包括前五行和后一行-- over (order by x) 相当于 over(order by x rows between unbounded preceding and current now) 从前方无界到当前行-- over () 相当于从前方无界到后方无界，整组内容-- 另, partition 子句可省略省略就是不指定分组-- 例:select *, rank() over (order by scores desc) as ranking from students哪些函数可以使用窗口函数专用函数rank/dens_rank/row_numberrank/dens_rank/row_number这三个函数得区别是分组排序后得到的虚拟rank列不同实际上此函数可以为查出来的每一行增加rank序号rankdens_rankrow_number注意rank()函数中得到的rank值可能是会出现重复值，如果要取1条，需要sql查到的数据不重复，rank=1不能保证仅取1条,除非你使用得函数是row_number()：demo大概场景就是，我们需要查询一张表，要按照某个字段a去排序另一个字段b，并且每个c字段只取前n条数据select a.id, a.a, a.b, a.c, a.d from (select t.id, t.a, t.b, t.c, t.d, rank() over(partition by t.a order by t.b desc) rk from test t) awhere rk 、!、notin、notlike等，会导致全表扫描。这条规定想满足其实很难，有些业务必不可免需要用到，那么可以考虑如果数据量大的情况使用以下用法：select oid from order where uid = 1 and status != 1;这条sql只要uid有索引，就可以先走索引缩小数据范围，此时再接上一个负向查询也没什么性能影响了。拆分大的insert/delete子查询往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。分页优化-- demo1select id, content from news order by title limit 50,5--> 优化后select news.id, news.contentfrom news inner join ( select id from news order by title limit 50,5) as tmp using(id)-- demo2select id from t limit 10000, 10--> 优化后select id from t where id > 10000 limit 10groupby/orderby优化确保任何groupby和orderby的列只涉及到一个表中的列，这样mysql才可以用索引去优化。join优化当表a和表b都用列c列来关联时候，如果优化器关联的顺序是ab，那么只需要再b表c列添加索引即可；具体原因可以参考优化器优化sql后得执行逻辑，反推就可以得到以上结果。谓词下推谓词下推将查询语句中的过滤表达式计算尽可能下推到距离数据源最近的地方，以尽早完成数据的过滤，进而显著地减少数据传输或计算的开销。谓词下推案例-- 谓词下推到存储层-- demo1select * from t where a = '2000-01-01' group by A.give_day)select a.give_day, -- a.num, sum(case when b.give_day = a.give_day then b.num else 0 end), sum(b.num) as all_numfrom tmp a, tmp bwhere b.give_day