复杂SQL语句及其优化
2022/7/1 2:22:54
本文主要是介绍复杂SQL语句及其优化,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1,复杂SQL语句类型
1,1 笛卡尔连接
题目1:找出工资超过各自经理的员工姓名
表:employee(id , name , depid , salary, manager_id )
SELECT e1.name AS employee_name, e1.salary, e2.name AS manager_name, e2.salary FROM employee e1, employee e2 WHERE e1.manager_id = e2.id AND e1.salary > e2.salary;
1.2 相关子查询
相关子查询和普通子查询(也叫非相关子查询)的差别就在于这子查询中是否有对外部查询中涉及到的表的引用。
此时,先执行外部查询,拿到一个结果后,去执行内部查询,判断是否满足条件。
举例: 查询 “工资大于该员工所在部门平均工资的员工”
SELECT employee_number, name FROM employees emp WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = emp.department # 子查询中的表就是外部查询的引用表 );
1.3 行转列 或者 列转行
行转列: 行的某列数据,转化到多个列上。
方法:主要是使用CASE WHEN 条件函数,增加表格的列。
比如:
学生表student: id name subject score sam yuwen 50 sam shuxue 70 bob yuwen 78 ##### SELECT id, name AS 姓名 , sum(CASE Subject WHEN 'yuwen' THEN score ELSE 0 END) AS 语文 , sum(CASE Subject WHEN 'shuxue' THEN score ELSE 0 END) AS 数学 , sum(CASE Subject WHEN 'yingyu' THEN score ELSE 0 END) AS 英语 FROM student
列转行: 某列的数据,转化到多个行上。
方法:假设在HIVE 库内,使用LATERAL VIEW语法
电影信息表 movie_info: name types 《疑犯》 悬疑,动作,科幻,爱情 --> name type 《疑犯》 悬疑 《疑犯》 动作 《疑犯》 科幻 《疑犯》 爱情 SELECT name, type FROM movie_info LATERAL VIEW explode(types) alias_table AS type;
1.4 窗口函数 (针对HIVE)
语法:
题目: 要求拉出一个表,包含当前表信息,并且包含该次消费的上一次消费日期。
# 订单表order:(name ,date, cost)
name: 顾客姓名 date: 日期 cost: 花费
select name, date, cost, lag(cost, 1, 0) over(patitioned by name order by date) as preDate from order
二,典型逻辑SQL逻辑举例:
1, 每个用户连续签到天数
t_user_attendence表(fdate, fuser_id, fis_sign_in ) 表的说明:日期【fdate】,用户id【fuser_id】,用户当天是否签到【0否1是】
举例;
2020-10-01 002 1
2020-10-02 003 1
2020-10-02 002 0
SELECT fuser_id, datediff('2022-06-26', fdate_max) AS fconsecutive_days # 当前日期 - 最近未签到日期 = 连续签到日期 FROM ( SELECT fuser_id, max(fdate) AS fdate_max #找出用户最近未签到的日期 FROM t_user_attendence WHERE fis_sign_in = 0 GROUP BY fuser_id ) t1;
2, 每个用户最大的连续签到天数
含义是,在整个签到表的日期范围内,最大的连续签到日期。
表格:同上
表格:同上 SELECT fuser_id, max(length(cut_fsign_record)) AS fmax_days # 对“11111”求长度,就是连续登录天数 FROM ( SELECT fuser_id, fsign_record, cut_fsign_record FROM ( SELECT fuser_id, wm_concat(fis_sign_in) AS fsign_record # 多列合并到一行,列值以","号分隔起来 FROM t_user_attendence GROUP BY fuser_id ) t1 LATERAL VIEW explode(split(fsign_record, '0')) t AS cut_fsign_record # 用0分割列,把列值转多行。(111 111) ) t2 WHERE cut_fsign_record <> '' GROUP BY fuser_id; # 语句适用于HIVE SQL。 ??
2,Explain 语句
对于hive:
explain会把查询语句转化成stage组成的序列,主要由三方面组成:
1:查询的抽象语法树
2:plane中各个stage的依赖情况
3:每个阶段的具体描述:描述具体来说就是显示出对应的操作算子和与之操作的对应的数据,例如查询算子,filter算子,fetch算子等等。
这里,你可以查看是否有严重计算密集的stage(或者是其中的算子,比如map , reduce , fillter , fetch , group by等等) , 可以查看每个算子操作的数据大小情况。使你可以看到
HSQL执行的mapreduce 底层运行情况。 由此决定如何调优(参见我的另一篇文章: HIVE 调优思路和实践)。优化思路的前提是你必须对Mapreduce的原理比较熟悉。
对于MySQL:
explain 语句的输出信息包括: 1, 一个语句被分解成多个查询计划(比如 嵌套查询 ,Union查询)。 2,每个查询计划涉及到数据情况 ,索引使用情况 ,查询效率
Column | JSON Name | Meaning |
---|---|---|
id | select_id | select标识号 |
select_type | None | select类型 |
table | table_name | 这一行数据是关于哪张表的 |
partitions | partitions | 匹配的分区,对于未分区表,该值为空 |
type | access_type | 使用的连接类别,有无使用索引 |
possible_keys | possible_keys | MySQL能使用哪个索引在该表中找到行 |
key | key | MySQL实际决定使用的键(索引) |
key_len | key_length | MySQL决定使用的键长度。如果键是NULL,长度为NULL |
ref | ref | 与索引关联的列 |
rows | rows | mysql认为执行sql时必须被校验的行数 |
filtered | filtered | 表示此查询条件所过滤的数据的百分比 |
Extra | None | 附加信息 |
关注的字段:
type : 常用的类型有:NULL, system, const, eq_ref, ref, range, index, ALL(从左到右,性能越来越差)
- NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成
- system:这个表(也可能是查询出来的临时表)只有一行数据 (= system table). 是const中的一个特例
- const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const 表常量次数, const用于查询条件为PRIMARY KEY或UNIQUE索引并与常数值进行比较时的所有部分。
- eq_ref:对于前几个表中的每一行组合,从该表中读取一行。除了system和const,这是最好的连接类型。当连接使用索引的所有部分,并且索引是主键或唯一非空索引时,将使用它。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。 这种情况可认为,完全利用索引去查询的类型,效率比较高。
- ref: 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字查询结果为单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
- range: 只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
- index: 该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。
- ALL: 对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出
rows: rows 列显示MySQL认为它执行查询时必须检查的行数。
fillter: 表示此查询条件所过滤的数据的百分比 , 数值越高越好。
根据expain语句提供的信息,查看SQL 执行计划,确定延迟最大的查询阶段,并对此进行优化。
措施包括:
1,修改查询SQL逻辑,最大程度利用索引结构,加速查询效率。
2,修改表的索引设置 。
3,MySQL资源优化配置层面进行优化。
这篇关于复杂SQL语句及其优化的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现
- 2024-06-03为什么以及如何要进行架构设计权衡?
- 2024-05-31全网首发第二弹!软考2024年5月《软件设计师》真题+解析+答案!(11-20题)
- 2024-05-31全网首发!软考2024年5月《软件设计师》真题+解析+答案!(21-30题)