卷首语
欢迎来到本期的《驱动力》杂志！这个月我想和大家分享一些关于SQL调优和SQL书写技巧的知识。
作为一家互联网公司数据部门的员工，我相信大家对SQL并不陌生。SQL（Structured Query Language）是一种用于管理和操作关系型数据库的编程语言。在数据的处理过程中，优化SQL的性能是至关重要的。通过调优SQL语句，我们可以提高数据库的查询速度和效率，从而提升整个系统的性能。
在SQL调优方面，有一些常见的技巧和建议可以帮助我们优化查询语句。首先，我建议大家利用在线学习资源来入门学习SQL。一些在线学习平台如w3school、xuesql.cn自学SQL网、牛客网、sqlzoon等提供了丰富的学习材料和练习题，特别适合SQL的初学者。通过在线题库的学习，你可以系统地了解SQL的基础知识，并通过练习不断提升自己的技能。
另外，在实际使用中，我们也可以注意一些SQL的书写技巧。比如，在新建临时表时，如果一次性插入数据量很大，可以使用SELECT INTO代替CREATE TABLE，以提高速度。此外，在编写SQL语句时，注意使用合适的索引、避免不必要的数据类型转换和重复的查询操作，这些都能够有效提升SQL的执行效率。
除了调优，我还想强调SQL的书写技巧对于数据处理的重要性。准确、简洁地书写SQL语句能够增加代码的可读性和可维护性。在编写SQL语句时，我们可以通过使用别名、避免使用SELECT *、合理命名字段和表等方式来提高代码的可读性。此外，良好的注释和文档也是编写SQL的重要概念，能够提高代码的可理解性和团队之间的协作效率。
在本期的企业内部杂志中，我们收到了部门内小伙伴的热情响应，分享他们的经验和观点。希望通过这些宝贵的经验，能够帮助大家更好地理解和应用SQL调优和SQL书写技巧。
最后，我希望我们的读者朋友们能够从本期的电子杂志中收获到有价值的知识和经验。如果你对SQL调优和SQL书写技巧有什么问题或者想法，欢迎线下与我继续交流，如有必要可以考虑把SQL调优作为我们的固定板块。我们期待听到你们的声音！
目录
01‖ SQL调优技巧——知其所以然
02‖关于Left join，你可能不知道这些
03‖Spark SQL任务调优
04‖SQL常用函数及避坑点汇总『Hive系列』
05‖HiveSQL优化技巧
06‖SQL优化思路+经典案例分析
07‖MySQL常用30种SQL查询语句优化方法
驱动力
◀ SQL调优技巧——知其所以然
◀ SQL调优技巧&知其所以然
我们在使用SQL的时候不仅仅要关注数据结果也要注意执行效率
一、首先知道一条SQL的执行顺序，通常是按照如下的顺序执行的
◀ 计算机屏幕保护
二、了解MySQL的运行结构
「原创作品」 作者：秦令达
- FROM：确定要查询的表以及它们的连接方式。这个步骤决定了查询的主要数据集。
- JOIN：如果查询涉及多个表之间的连接，那么在这一步中会执行连接操作。连接操作基于连接条件将不同表的行匹配起来，生成一个合并的结果集。
- WHERE：应用查询条件，过滤掉不符合条件的行。只有符合WHERE条件的行才会被保留下来。
- GROUP BY：如果查询包含GROUP BY子句，那么在这一步中将按照指定的列对结果进行分组。相同值的行会被分为一组。
- HAVING：应用HAVING条件，过滤掉不符合条件的分组。只有符合HAVING条件的分组才会被保留下来。
- SELECT：选择要返回的列。在这一步中，执行函数、计算表达式以及对列进行重命名等操作。
- DISTINCT：如果查询包含DISTINCT关键字，那么在这一步中去除重复的行。
- ORDER BY：如果查询包含ORDER BY子句，那么在这一步中按照指定的列对结果进行排序。
- LIMIT：如果查询包含LIMIT子句，那么在这一步中限制返回的行数。
- connection也是采取线程方式执行sql
- 对sql进行解析、优化 （1: 查询磁盘把mysql中的全部数据都找一遍 2:看是不是走索引）
三、存储引擎
这里我们只说常用的InnoDB，提供读写接口，数据本身在磁盘上面
驱动力
◀ SQL调优技巧——知其所以然
◀ 计算机屏幕保护
1、查询的时候：先将数据从磁盘（ibd）加载到内存（Buffer Pool），下次再查就再再内存中直接查询了ibd文件如下
一条记录就占用：128Byte，比如一次查询就查询一个block（一个磁盘中的扇区）
一共有100条记录，就需要查询：100*128/512=25次
但是如果我们创建索引的话：
2、修改的时候：先在内存（Buffer Pool）中修改，产生redo log 日志，当内存中的缓存空间不足时(LRU)，被标记的修改数据就同步到磁盘中去了（当需要腾空间时就从flush-list里把需要同步的数据同步到磁盘中 ）
下面是存储引擎从磁盘查询数据的一个过程
InnoDB 以page为单位，将数据从磁盘中读取出来
- InnoDB中page大小默认为16KB
- 操作系统中的page大小为4KB
- 机械硬盘扇区512Byte
- SSD的page大小为4KB
通常我们为了查询的更快进行创建索引：目的就是为了减少磁盘查询，空间虽然占用了多，但是查询次数变少，数据查询速度变快
● 下面来解释一下创建索引的必要性：
比如一张用户表：
| 字段 | 占用字节大小 |
| user_id | 10 |
| user_name | 20 |
| password | 20 |
| remark | 20 |
| nick_name | 58 |
驱动力
◀ SQL调优技巧——知其所以然
◀ 计算机屏幕保护
假设一条索引的大小为16B（真实可能比这还小），那么一个block就可以查询 512/16=32个索引， 100条也就是查询： 100/32 = 4次
在索引的情况下查询最多次数： 4次索引磁盘的查询+1次数据磁盘的查询=5 次，与没有加索引的25次来比快很多，数据量越大索引的优势越明显
当然也有一个问题，当索引数量越来越多的时候，看下InnoDB是怎么做的：
用了B+Tree来实现了给索引建立索引（这里就不详细的展开说：二叉树->多路查找树->B-Tree->B+Tree的过程了，如果有兴趣可以线下一起交流），直接来说B+Tree：
它是以block块来建立索引的，第一次访问的根节点之后就不会变了，当block满了就开始进行升级：
这张表是336w条数据
查询一条数据：
select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10025103944' and CUSTOMERNUMBER ='10089513727';
因为所有数据存在叶子结点，读取的子节点可以存储更多的索引key（线索），数据做顺序读取时性能也更好（叶子节点的数据是有链表关系）
四、查询优化器
查询优化器： 找到执行SQL查询的最佳计划，根据表、列、索引的详细信息以及SQL语句的条件，让SQL进行高效的工作，简而言之就是影响where条件中的执行顺序，SQL语句最终真实执行的就是执行计划，通过执行计划来看SQL查询低效的原因
用我们现在正在做的CREM功能中涉及到的一张表来做例子：
驱动力
◀ SQL调优技巧——知其所以然
◀ 计算机屏幕保护
看这条SQL的执行的详细信息：
select trace from information_schema.OPTIMIZER_TRACE;
看下面优化器的详细信息：有3个重要的点
先进行了准备会将sql补全：join_preparation
再进行了优化会选择最优的索引方式：join_optimization
具体执行：join_execution
优化器详细信息如下：（点击图片放大预览）
2、is null 和 is not null 会不会走索引？
不绝对，看优化器的成本分析来决定
1、like会不会走索引
不会，下面2张图片对比
五、SQL优化
针对为什么直接说sql优化的例子没有说explain（以后补充上，肝不完了，我认为其实了解了原理具体怎么做就大同小异了）
表的ddl:（点击图片放大预览）
驱动力
◀ SQL调优技巧——知其所以然
◀ 计算机屏幕保护
3、索引函数计算会不会走索引？
需要看查询字段是否为后面where中的索引字段
explain select CUSTOMERNUMBER from operational_efficiency_access_ods where FROM_UNIXTIME(FEAT_FINAL_XS_TIME, '%Y-%m-%d') = '2022-01-04';
5、多列索引，顺序反了会不会走索引？
会， 但是需要遵循最左前缀
alter table operational_efficiency_access_ods add index idx_duo_index(CUSTOMERNUMBER,TASK_ID,FEAT_SASS_CUS_NO);
explain select * from operational_efficiency_access_ods where CUSTOMERNUMBER = '10088774372' and TASK_ID='TYSHRW20220901125821215126' and FEAT_SASS_CUS_NO ='10027250870';
4、类型不一致会走索引吗
会走索引，但是还是需要看查询字段 ，优化器的最终执行计划
explain select FEAT_FINAL_XS_TIME from operational_efficiency_access_ods where FEAT_FINAL_XS_TIME > '1591025358';
explain select * from operational_efficiency_access_ods where TASK_ID='TYSHRW20220901125821215126' and FEAT_SASS_CUS_NO ='10027250870' ;
驱动力
◀ SQL调优技巧——知其所以然
◀ 计算机屏幕保护
6、查询的数据太多会不会走索引？
explain select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10086249365';
会走索引,具体还是看查询优化器，因为查询优化器是不断的优化
explain select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10086249365'
union
select * from operational_efficiency_access_ods where TASK_ID = 'SHTYRW2493530732';
7、使用union还是OR好？
union好一些，成本更低，具体看下面的展示
explain select * from operational_efficiency_access_ods where FEAT_PLATFORM_CUS_NO ='10086249365' OR TASK_ID = 'SHTYRW2493530732';
成本为：
参考资料：
官网地址 https://dev.mysql.com/doc/
认为比较不错的文章
https://mp.weixin.qq.com/s/7UxDqwJi9ChR8OfIQwpWWw
驱动力
left join 通俗的解释：以左表为主表，返回左表的所有行，如果右表中没有匹配，则依然会有左表的记录，右表字段用null填充。看起来非常好理解，但实际操作的过程中可能会有一些很容易被忽略的点。
一、left join 之后的记录有几条
关于这一点，是要理解left join执行的条件。在A join B的时候，我们在on语句里指定两表关联的键。只要是符合键值相等的，都会出现在结果中。这里面有一对一，一对多，多对多等几种情况。我们用例子来说明。
1.一对一
这种情况最好理解。t_name表，有id，name(用户名称)，sex(性别)，dt(注册日期)等字段。t_age表。有id，age(年龄)，province(省份)，dt(更新日期)等字段。表中包含的信息如下：
现在我们进行t_name(左表，别名a)和t_age(右表，别名b)的left join 操作，关联键为id。a表有6条记录，b表有3条记录，且关键的键是唯一的，因此最终结果以a表为准有6条记录，b表有3条关联不上，相应的记录中，b表所有的字段都为空。
2.一对多
这回我们用t_age作为左表，关联条件为dt。重点看dt为20190905的记录。由于右表有3条20190905，这三条在关联的时候都满足关联条件，因此最终的结果会有3条记录是20190905。
◀ 关于Left join，你可能不知道这些
文章来源：知乎 作者：超哥要努力
原文链接：https://zhuanlan.zhihu.com/p/85856388
join 是 SQL查询中很常见的一种操作，具体来讲有join，left join, right join，full join等很多形式。具体的原理如下图所示。但其中最常见的还是使用left join 。
本文代码在mysql和hive中均测试通过，代码本身难度和长度都不大，我准备了测试数据的mysql和hive代码，如果觉得有必要，你可以在公众号后台回复“left”获取，方便自己修改和练习。
驱动力
2和3中我们看到了一对多和多对多的情况，其实前者是后者的特例。我们只是很简要的把两个表关联之后所有的字段都列出来了，但实际中可能需要做一些统计，聚合等。这里提醒大家在写关联条件之前，最好思考一下最终的结果是什么样的，最终可能有几行，会不会在计数的时候多统计，哪些行可能会存在空值，哪些字段可能会存在空值等。不要因为想当然而犯了错误。这里算是抛砖引玉，感兴趣的同学可以看看这篇博客，进一步学习，
https://www.cnblogs.com/qdhxhz/p/10897315.html
二、left join 的执行原理
接下来我们进一步看一下连接条件写在on里和写在where里的区别。在这之前，我们可以看看left join的具体执行逻辑。我参考了网上以为大神的博客：
https://developer.aliyun.com/article/718897，总结如下
mysql采用嵌套循环的方式处理left join。
SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
其中P1是on过滤条件，缺失则认为是TRUE，P2是where过滤条件，缺失也认为是TRUE，该语句的执行逻辑可以描述为：
FOR each row lt in LT {// 遍历左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行，找到满足join条件的行
IF P2(lt, rt) {//满足 where 过滤条件
t:=lt||rt;//合并行，输出该行
}
b=TRUE;// lt在RT中有对应的行
}
IF (!b) { // 遍历完RT，发现lt在RT中没有有对应的行，则尝试用null补一行
IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
t:=lt||NULL; // 输出lt和null补上的行
}
}
}
如果代码看不懂，直接看结论就好：
这回为准的表是t_age表，但显然结果并不是原本的3条记录，而是7条：20190905 3条，20190906 4条。如果你不太理解，可以继续往下看。
3.多对多
上面例子中，20190906的记录最终有4条，同样是因为满足了关联条件，是一种2对2的情况。这里我们还是回到t_name表做主表的情况，用dt来关联。可以预见，与2中相比，这次结果中会多一行20190907的，而b表相应的字段依然为空。
驱动力
2.有2个on条件
- 如果想对右表进行限制，则一定要在on条件中进行，若在where中进行则可能导致数据缺失，导致左表在右表中无匹配行的行在最终结果中不出现，违背了我们对left join的理解。因为对左表无右表匹配行的行而言，遍历右表后b=FALSE,所以会尝试用NULL补齐右表，但是此时我们的P2对右表行进行了限制，NULL若不满足P2(NULL一般都不会满足限制条件，除非IS NULL这种)，则不会加入最终的结果中，导致结果缺失。
- 如果没有where条件，无论on条件对左表进行怎样的限制，左表的每一行都至少会有一行的合成结果，对左表行而言，若右表若没有对应的行，则右表遍历结束后b=FALSE，会用一行NULL来生成数据，而这个数据是多余的。所以对左表进行过滤必须用where。
我们再来看看实例，返回来研究这段话可能更好理解一些。
1.只有1个on条件
这里可以直接看第一部分中的例子。最终会输出以左表为准，右表匹配不上补null的结果，但可能会有多对多的情况。
上图是在关联条件中增加了b.age=24之后的输出结果。由于对b表进行了限制，满足条件的只有一个，但是由于没有where条件，因此依然要以左表为准，又因为是一对一，所以输出还是左表的记录数。更极端的，我们可以“清空”b表。
以上两种情况，在b表中都没有符合条件的结果，因此在以左表为准的基础上，右边的所有字段都为空。
3.有where的情况
驱动力
当条件写在where 中：
将b.age=24写到where里，发现结果中只有这一行，打破了“left join”以左表为主的限制。同样再来看下后两种情况写到where里会发生什么：
没错，结果全部是为空的。因为where 在 on 后面执行，而on生成的结果里没有满足条件的记录！
这里给出两个结论：
- on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
- where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
4.有is null 或者有 is not null的情况
当条件写在on中：
直观的我们理解，WHERE … IS NULL 子句将从匹配阶段后的数据中过滤掉不满足匹配条件的数据行。对于条件写在on中的情况，又可以说，is null是否定匹配条件，is not null是肯定匹配条件。对于条件写在where中的，其实相比之下更容易理解，要看已有的where条件产生的结果是什么。读者可以从上面的例子中思考一下。
三、看两个实际案例
经过上面的讨论，我们来看两个案例，进一步理解和思考一下left join 的用法。
1.案例1
这个案例来自于一篇网络博客，前文有提到。链接：
https://developer.aliyun.com/article/718897
驱动力
对于这个需求，我们可以使用left join进行自关联，用之前活跃的天作为左表，最终期望计算的天作为右表，计算日期差，并进行左右表分别计数。初步的SQL如下：(数据是自己编的)
在往下看之前请确认你理解了需求目标，并先思考下，以上的写法有问题吗？能否得到上面期望的结果？
原始数据和这段SQL运行的结果如下：
大家可以先思考一下怎么写再到原文看答案。事实上，每个需求都很容易有两种写法，区别就在于条件是写在where中还是写在on中。判断的原则就是我们需要保证结果中数据不缺失也不多余。需求1的条件需要写在on中(保证结果不缺失)，需求2的条件需要写在where中(保证结果不多余)。
2.案例2
假设现在有一个用户活跃表t_active，记录了每天活跃的uid和相应的活跃日期。现在想要看距离某一天日期差为0天，1天，2天，3天…活跃的用户在当天还有多少活跃(也就是一个留存的概念)。期望得到的如下表所示：
对于表中数据，我们可以这样理解。距离2019-09-29 0天(也就是2019-09-29)的活跃人数为100,2019-09-29当天活跃的还剩100，距离2019-09-29 1天(也就是2019-09-28)的活跃人数为80,2019-09-29当天还剩60。以此类推。
驱动力
从上面的结果我们可以推演出最开始的SQL运行结果。例如，datediff=5的时候，共两条记录，左表右表的count(distinct uid)都为2。datediff为null的时候，左表结果为7，右表为0，其他的以此类推，和前面的结果是一样的。这样我们就知道了，没有达到预期的根源在于存在空的日期。那么怎么解决这个问题呢，显然就是把空日期填补上就可以了。可以使用case when 当右表日期关联不上的时候，用相应日期补足。代码如下：
可以看到最终得到了想要的结果，以最后一行为例，它表示，距离2019-09-29 5天的那天(也就是2019-09-24)活跃的人数有5个，那些人在2019-09-29仍然活跃的有2人，你可以看一下明细数据核对一下。其余的以此类推。我们使用case when 把日期写死了，这个是建立在我们知道是哪天的基础上的。实际中可能是一个变量，但一定也是一个固定的值，需要具体情况具体分析。
四、总结
本文我们学习了left join的原理和实践中可能会遇到的问题。包括关联时结果中的记录数，关联条件写在on和where中的区别，where语句中存在is null的时候如何理解，最后用实例帮助大家进行理解。在此过程中参考了网上的一些博客，大家可以在阅读本文的基础上进行查阅。希望对你有所帮助！公众号后台回复“left”，可以获取本文测试所用的数据集合代码。
运行结果中出现了dt和datediff为null的情况，你能想象的到这是为什么吗？而且当dt不为null的时候，最后两列的数据是相同的，显然和我们的预期不符。这是什么原因呢？我们来逐步看一下。
首先，我们使用left join 的方式应该是没有问题的，我们先看看不加任何计算的，select * 的结果是啥。
可以看到，这相当于是前文提到的不加where 条件的一对一关联，结果会以左表为准，关联不上的用null补齐。值得注意的是，关联不上的日期是null值，而null值在参与datediff的计算时，结果会是null。到这里你是不是明白一点了。由于null值参与计算，导致最终datediff 有null值，并且计数的时候，由于null值存在，最终用日期差作为维度的时候，导致左表和右表的数量是一样的。如下面代码所示：
驱动力
reference：
https://developer.aliyun.com/article/718897
https://blog.csdn.net/u013630349/article/details/71123409
https://www.cnblogs.com/qdhxhz/p/10897315.html
https://www.oschina.net/question/89964_65912
https://blog.csdn.net/muxiaosha
◀ Spark SQL任务调优
「原创文章」 作者：何剑
关于优化前面想说的
在实际生产优化中，很多开发同学一上来就看sql执行资源分配够不够，数据有没倾斜，与业务脱离的技术层通常摸不到业务的核心需要，这使得实际优化受限；比如一条任务跑了六七个小时，实际业务设计就是不合理的或者有更好更快的实现方式。
举个例子，在解决一实际生产问题时，曾苦恼调整没有好的实现方法，经沟通，该需求可以下线，无须处理；需求是指向人的，需求背后有更深层次的需求，作为一名资深的开发跟业务协调，更应该去琢磨业务需要，深层考量，而非仅仅是开发实现层面。
反过来，也会遇到这样一类业务，做需求只讲输出不言背景。这样一方面开发成长受限，协作表现可能不理想，一方面不能很好的调动开发资源，仅受限局限视角的具体实现。
1、对于基础表结构，一般是事先设定好的；但对于数仓表设计也是我们需要考量的一个点，比如数仓设计，全量增量、不同颗粒度，良好的设计能节约很多计算存储资源；
2、其次再就是语句合理性分析，这块可以结合执行计划读，比如实现该需求，使用怎样的逻辑处理会更快些，资源消耗会更少些；
3、最后再就是资源、参数优化。
如果表设计、语句、业务逻辑都没啥问题的话，则重点考虑数据倾斜，资源配置。
* 本内容仅针对yarn调度环境，spark版本：2.4
Spark任务调优策略
现象一：运行时长 > 60min 的任务，或是资源消耗最多的 Top50 任务，或 spark web ui 上某个阶段 Task 数特别多
驱动力
1、时间分区过滤条件：判断任务的 sql 中是否有 hive 分区表，若有的话，判断是否增加了正确的时间分区过滤条件。
2、缩小查询的时间范围：确认一下 sql 中查询的时间范围过滤条件是否过大，
若是的话，是否可以在符合业务需求的前提下，缩小查询的时间范围。
3、增量计算：确认一下 sql 是否是基于多天数据的全量计算，思考是否可改为基于时间的增量计算。
比如要拉取最近30天日活用户和最后活跃日期。
当前数据仓库有一张日活跃表，日分区；每次作业需要拉取近30天分区数据，数据量极大。
这时候可以建一张中间表，记录近30天活跃用户以及用户最后活跃日期，库表名：user_last_active_date
然后该中间表的修复任务，当天分区为，昨日数据剔除最后活跃日期在29天 + 当天活跃信息，按用户去重
4、调整参数：根据 task 的数据字节大小，调整和设置 executor 的资源配置、调整最大并发数。
现象二：所有Task启动时间差很多
如果你的任务本身并不是很大，SparkUI显示Task任务启动时间却相差较大。这个现象说明Spark Job分配了很多Task, 但却没有足够的并发能够同时启动这些Task执行。 由于分批启动，即等部分executor中的Task释放后，才会启动等待的新一批Task，所以导致Spark执行时间较长。
可以的优化方向，通过调整Spark的并发，即 spark.executor.cores * num-executors。
现象三：大量的Executor中 GC 占比较高
1、先判断 sql 的过滤条件是否有按业务需求正确添加。
2、判断任务是否出现了数据倾斜严重，数据倾斜可能会导致某些任务消耗大量内存，进而引发频繁的 GC。
3、如果资源配置的内存使用不合理，就有可能会导致频繁的垃圾回收（GC）现象，从而降低应用程序的性能。一般来说，我们可以通过调整 spark.executor.memory 参数来增加每个 Executor 的内存分配。
现象四：多张表 Join， Join 后的中间阶段的记录条数仍然很多、基本没有减少
1、调整join关联的顺序： 用其中一个条数最多的表，先跟其中一个条数最少的表做 Join；然后再跟其他表做 Join。
假设我们有这样一条sql：
- t1表10亿，
- t2表用户维度维表：1亿；
- t3表用于关联过滤：20万；
- t1表与t2表join的记录行10亿，再与t3表关联的记录行是1千万；
查看执行计划，计算顺序是从上往下走，这时候可以调整join中表顺序，先t1与t3表join，再与 t2 join。
驱动力
查看执行计划，计算顺序是从上往下走，这时候可以调整join中表顺序，先t1与t3表join，再与 t2 join。
2、数据预聚合：对于在join过程中，子表如果提前聚合能显著减少后续操作的记录条数或数据量，应事先按 key 聚合。
现象五：任务每天跑多次，但又不是增量处理类的任务
通过 梳理数据处理链路、业务逻辑，整改为 增量处理类任务 或 低调度频次的任务。
现象六：出现数据倾斜
判断数据倾斜的方法非常简单，通过Spark UI Stage的Summary的统计信息，是否小部分Task的shuffle writer和shuffle read数据量比较大。
在Spark2.4中， 尝试开启AQE 来解决。
Spark关键参数配置
1、Spark资源参数调优
（1）driver 资源配置
在开启动态申请executor资源后，Spark会根据当前任务负载自动增加或减少Executor实例数量，以优化资源利用率。maxExecutors参数定义了分配给应用程序的Executor实例的上限数量，在申请资源时不会超过这个设定的上限。
推荐的 资源参数：
（2）executor 资源配置
- 单个executor资源配置
配置推荐的 资源参数后，任务运行不正常的，需要看下错误信息、具体任务具体分析。
如果判断是因为 executor 内存不足，推荐用下一组 资源参数：
2、Spark性能参数调优
（1）join策略的参数
spark.sql.autoBroadcastJoinThreshold 参数用于控制 Spark SQL 中自动广播连接（join）的阈值，默认是10MB。这意味着当一个表的大小小于该阈值时，Spark SQL 将自动选择广播连接，使用广播连接可以大大的提升计算性能。如果你的小表大于10M，但又不是非常大的情况下，可以调整spark.sql.autoBroadcastJoinThreshold的数值，让其大于小表的大小，那么在join计算时就会走广播连接。
驱动力
但是有些情况下，即使你的小表较小但出现了大量的网络连接失败的日志或 broadcastTimeout，这时可能由于网络拥塞或其他原因导致任务失败，可以将spark.sql.autoBroadcastJoinThreshold=-1设置为-1禁用自动广播join操作，提升计算任务的稳定性。
（2）启动 Spark AQE （自适应查询执行） 功能
Spark AQE功能，它将更好的提升Spark运行稳定性和性能。
他们细节配置如下所示。
AQE中实际上是包含了三大特性：
1. 动态合并 Shuffle Partitions：在 Shuffle 过后，Reduce Task 数据分布参差不齐，AQE 将自动合并过小的数据分区。
2. 动态调整Join策略：如果某张表在过滤之后，Byte Size 小于广播变量阈值，这张表参与的数据 Join 就会从 Shuffle Sort Merge Join 降级 为执行效率更高的 Broadcast Hash Join。
3. 动态优化倾斜Join：结合配置项，AQE 自动拆分 Reduce 阶段过大的数据分区，降低单个 Reduce Task 的工作负载。
00-序言
SQL是数据分析同学日常用到的查询语言，Hive是基于Hadoop的数据仓库工具，Hive提供了SQL的查询功能，可将SQL转化为MapReduce任务来执行。本文汇总了小火龙在工作中常用的Hive SQL函数，以及其中可能涉及到的一些坑，供大家参考学习。函数类型如下图：
本文主要汇总「内置函数」的几种类型，对于「用户自定义函数」的创建，会在后面的文章中进行讲解。
01--数值函数
「数值函数」主要是对int和float数据类型进行的处理。常用函数汇总如下：
◀ SQL常用函数及避坑点汇总『Hive系列1』
文章来源：小火龙说数据
原文链接：
https://mp.weixin.qq.com/s/rixtQ9AcS-gQ9CtqCxBSCA
驱动力
避坑点
1、rand(x)：相同“种子”多次结果均一致；无“种子”任何一次结果均不一致。
02-字符串函数
「字符串函数」主要是对char数据类型进行的处理。常用函数汇总如下：
03-条件函数
「条件函数」主要是对字段进行判断。常用函数汇总如下：
04-日期函数
「日期函数」主要是对日期进行处理，以及加减操作，在计算留存时经常使用。常用函数汇总如下：
05-关系函数
「关系函数」主要是对字段进行匹配。常用函数汇总如下：
驱动力
避坑点
1、「NULL」和「空字符串」是不一样的，一般在数据库中表现为，「NULL」显示NULL，而「空字符串」显示为空，在匹配的时候需要注意。
举例：如果拿捏不好字段是哪种类型，并且需要去掉的情况，建议两者一起去掉。A is not null and A !=''
2、 Like、Rlike、Regexp的区别：
- Like：通配符，不是正则。通配符涵盖 % 和 _。
- Rlike：正则表达式，写法与java一样。
- Regexp：基本同Rlike。
「聚合函数」多行转一行。常用函数汇总如下：
07-分拆函数
「分拆函数」一行转多行。常用函数汇总如下：
08-窗口函数
「窗口函数」又称OLAP函数（online analytical processing），完成类似聚合函数的计算效果，但是又保持每行的数据，不被聚合到一起。常用函数汇总如下：
避坑点
1、 rank( )over( )、dense_rank( )over( )、row_number( )over( )的区别
驱动力
2、sum( )over( )函数中有order by 和 无order by的区别
HQL语句优化
1、使用分区剪裁、列剪裁
在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤。
select a.*
from test1 a
left join test2 b on a.uid = b.uid
where a.ds='2020-08-10'
and b.ds='2020-08-10'
上面这个SQL主要是犯了两个错误：
- 副表的过滤条件写在where后面，会导致先全表关联在过滤分区；
- on的条件没有过滤null值的情况，如果两个数据表存在大批量null值的情况，会造成数据倾斜。
老生常谈。hive的优化主要分为：配置优化、SQL语句优化、任务优化等方案。
其中在开发过程中主要涉及到的可能是SQL优化这块。
优化的核心思想是：
- 减少数据量（例如分区、列剪裁）；
- 避免数据倾斜（例如加参数、Key打散）；
- 避免全表扫描（例如on添加加上分区等）；
- 减少job数（例如相同的on条件的join放在一起作为一个任务）。
以上就是本期的内容 分享，希望可以帮助你理清Hive SQL常用函数。码字不 易，如果觉得对你有一点点帮助，欢迎「关注」「点赞」 「分享」哦，我会持续为大家输出优质的「原创内容」
◀ HiveSQL优化技巧
文章来源：数据仓库与python大数据
原文链接：
https://mp.weixin.qq.com/s/ZVXTratXxP7MWJntv0IGew
驱动力
select a.*
from test1 a
left join test2 b on (d.uid is not null and a.uid = b.uid and b.ds='2020-08-10')
where a.ds='2020-08-10'
如果null值也是需要的，那么需要在条件上转换，或者单独拿出来
select a.*
from test1 a
left join test2 b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10')
where a.ds='2020-08-10'
union all
select a.* from test1 a where a.uid is null
或者
select a.*
from test1 a
left join test2 b on
case when a.uid is null then concat("test",RAND()) else a.uid end = b.uid and b.ds='2020-08-10'
where a.ds='2020-08-10'
2、尽量不要用COUNT DISTINCT，因为COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换，虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。
select count(distinct uid)
from test
where ds='2020-08-10' and uid is not null
转换为
select count(a.uid)
from
(select uid from test where uid is not null and ds = '2020-08-10' group by uid) a
3、使用with as，因为拖慢hive查询效率出了join产生的shuffle以外，还有一个就是子查询，在SQL语句里面尽量减少子查询。with as是将语句中用到的子查询事先提取出来（类似临时表），使整个查询当中的所有模块都可以调用该查询结果。使用with as可以避免Hive对不同部分的相同子查询进行重复计算。
或者（子查询）
select a.*
from test1 a
left join
(select uid from test2 where ds = '2020-08-10' and uid is not null) b on a.uid = b.uid
where a.uid is not null
and a.ds='2020-08-10'
驱动力
select a.*
from test1 a
left join test2 b on a.uid = b.uid
where a.ds='2020-08-10'
and b.ds='2020-08-10'
可以转化为
with b
as
select uid
from test2
where ds = '2020-08-10' and uid is not null
select a.*
from test1 a
left join b on a.uid = b.uid
where a.ds='2020-08-10' and a.uid is not null
4、大小表的join，写有Join操作的查询语句时有一条原则：应该将条目少的表/子查询放在Join操作符的左边。原因是在Join操作的Reduce阶段，位于Join操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生OOM错误的几率。
但新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。
不过在做join的过程中通过小表在前可以适当的减少数据量，提高效率。
5、数据倾斜，数据倾斜的原理都知道，就是某一个或几个key占据了整个数据的90%，这样整个任务的效率都会被这个key的处理拖慢，同时也可能会因为相同的key会聚合到一起造成内存溢出。
数据倾斜只会发生在shuffle过程中。这里给大家罗列一些常用的并且可能会触发shuffle操作的算子：distinct、 groupByKey、reduceByKey、aggregateByKey、join、cogroup、repartition等。出现数据倾斜时， 可能就是你的代码中使用了这些算子中的某一个所导致的。
hive的数据倾斜一般的处理方案：
常见的做法，通过参数调优：
set hive.map.aggr=true;
set hive.groupby.skewindata = ture;
当选项设定为true时，生成的查询计划有两个MapReduce任务。
在第一个MapReduce中，map的输出结果集合会随机分布到reduce中，每个reduce做部分聚合操作，并输出结果。
这样处理的结果是，相同的Group By Key有可能分发到不同的reduce中，从而达到负载均衡的目的；
第二个MapReduce任务再根据预处理的数据结果按照Group By Key分布到reduce中（这个过程可以保证相同的Group By Key分布到同一个reduce中），最后完成最终的聚合操作。
但是这个处理方案对于我们来说是个黑盒，无法把控。
一般处理方案是将对应的key值打散即可。
例如：
select a.*
from test1 a
left join test2 b on a.uid = b.uid
where a.ds='2020-08-10'
and b.ds='2020-08-10'
如果有90%的key都是null，这样不可避免的出现数据倾斜。
驱动力
前言
大家好，捡田螺的小男孩。
SQL调优这块呢，大厂面试必问的。最近金九银十嘛，所以整理了SQL的调优思路，并且附几个经典案例分析。
◀ SQL优化思路+经典案例分析
文章来源：数据仓库与python大数据
原文链接：
https://mp.weixin.qq.com/s/ZVXTratXxP7MWJntv0IGew
1.慢SQL优化思路。
慢查询日志记录慢SQL
- explain分析SQL的执行计划
- profile 分析执行耗时
- Optimizer Trace分析详情
- 确定问题并采用相应的措施
如果有90%的key都是null，这样不可避免的 出现数据倾斜。
select a.uid
from test1 as a
join(
select case when uid is null then cast(rand(1000000) as int)
else uid
from test2 where ds='2020-08-10') b
on a.uid = b.uid
where a.ds='2020-08-10'
当然这种只是理论上的处理方案。
正常的方案是null进行过滤，但是日常情况下不是这中特殊的key。
那么在日常需求的情况下如何处理这种数据倾斜的情况呢：
1、sample采样，获取哪些集中的key；
2、将集中的key按照一定规则添加随机数；
3、进行join，由于打散了，所以数据倾斜避免了；
4、在处理结果中对之前的添加的随机数进行切分，变成原始的数据；
当然这些优化都是针对SQL本身的优化，还有一些是通过参数设置去调整的，这里面就不再详细描述了。
但是优化的核心思想都差不多：
- 减少数据量；
- 避免数据倾斜；
- 减少JOB数；
- 虚核心点：根据业务逻辑对业务实现的整体进行优化；
- 虚解决方案：采用presto、impala等专门的查询引擎，采用spark计算引擎替换MR/TEZ；
驱动力
1.1 慢查询日志记录慢SQL
如何定位慢SQL呢、我们可以通过慢查询日志来查看慢SQL。默认的情况下呢，MySQL数据库是不开启慢查询日志（slow query log）呢。所以我们需要手动把它打开。
查看下慢查询日志配置，我们可以使用show variables like 'slow_query_log%'命令，如下：
一般来说，我们需要重点关注type、rows、filtered、extra、key。
1.2.1 type
type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system：这种类型要求数据库表中只有一条数据，是 const类型的一个特例，一般情况下是不会出现的。
- const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
- eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
- ref : 常用于非主键和唯一索引扫描。
- ref_or_null：这种连接类型类似于 ref，区别在于 MySQL会额外搜索包含 NULL值的行
- index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
- unique_subquery：类似于 eq_ref，条件用了 in子查询
- index_subquery：区别于 unique_subquery，用于非唯一索引，可以返回重复值。
- range：常用于范围查询，比如：between ... and 或 In 等操作
- index：全索引扫描
- ALL：全表扫描
1.2.2 rows
该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。
1.2.3 filtered
该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
- slow query log表示慢查询开启的状态
- slow_query_log_file表示慢查询日志存放的位置
我们还可以使用show variables like 'long_query_time'命令，查看超过多少时间，才记录到慢查询日志，如下：
long_query_time表示查询超过多少秒才记录到慢查询日志。
我们可以通过慢查日志，定位那些执行效率较低的SQL语句，重点关注分析。
1.2 explain查看分析SQL的执行计划
当定位出查询效率低的SQL后，可以使用explain查看SQL的执行计划。
当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。即MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表等信息。
一条简单SQL，使用了explain的效果如下：
驱动力
1.2.4 extra
该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：
- Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
- Using index ：表示是否用了覆盖索引。
- Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。
- Using where : 表示使用了where条件过滤.
- Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
1.2.5 key
该列表示实际用到的索引。一般配合possible_keys列一起看。
1.3 profile 分析执行耗时
explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling。开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。
profiling默认是关闭，我们可以使用show variables like '%profil%'查看是否开启，如下：
可以使用set profiling=ON开启。开启后，可以运行几条SQL，然后使用show profiles查看一下。
show profiles会显示最近发给服务器的多条语句，条数由变量profiling_history_size定义，默认是15。如果我们需要看单独某条SQL的分析，可以show profile查看最近一条SQL的分析，也可以使用show profile for query id（其中id就是show profiles中的QUERY_ID）查看具体一条的SQL语句分析。
驱动力
除了查看profile ，还可以查看cpu和io，如上图。
1.4 Optimizer Trace分析详情
profile只能查看到SQL的执行耗时，但是无法看到SQL真正执行的过程信息，即不知道MySQL优化器是如何选择执行计划。这时候，我们可以使用Optimizer Trace，它可以跟踪执行语句的解析优化执行的全过程。
我们可以使用set optimizer_trace="enabled=on"打开开关，接着执行要跟踪的SQL，最后执行select * from information_schema.optimizer_trace跟踪，如下：
1.5 确定问题并采用相应的措施
最后确认问题，就采取对应的措施。
多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，我们可以 优化索引。
我们还可以优化SQL语句，比如一些in元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行时间分段查询
SQl没办法很好优化，可以改用ES的方式，或者数仓。
如果单表数据量过大导致慢查询，则可以考虑分库分表
如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数，跟DBA讨论优化方案
如果存量数据量太大，考虑是否可以让部分数据归档
我之前写了一篇文章，有关于导致慢查询的12个原因，大家看一看一下哈:盘点MySQL慢查询的12个原因
2. 慢查询经典案例分析
2.1 案例1：隐式转换
我们创建一个用户user表
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
userId varchar(32) NOT NULL,
age varchar(16) NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY idx_userid (userId) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
userId字段为字串类型，是B+树的普通索引，如果查询条件传了一个数字过去，会导致索引失效。如下：
大家可以查看分析其执行树，会包括三个阶段：
- join_preparation：准备阶段
- join_optimization：分析阶段
- join_execution：执行阶段
驱动力
如果给数字加上'',也就是说，传的是一个字符串呢，当然是走索引，如下图：
explain select * from user where name ='捡田螺的小男孩';
为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。隐式的类型转换，索引会失效。
2.2 案例2：最左匹配
MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了(a)、(a,b)、(a,b,c)三个索引。
假设有以下表结构：
CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
user_id varchar(32) NOT NULL,
age varchar(16) NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY idx_userid_name (user_id,name) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
假设有一个联合索引idx_userid_name，我们现在执行以下SQL，如果查询列是name，索引是无效的：
因为查询条件列name不是联合索引idx_userid_name中的第一个列，不满足最左匹配原则，所以索引不生效。在联合索引中，只有查询条件满足最左匹配原则时，索引才正常生效。如下，查询条件列是user_id
2.3 案例3：深分页问题
limit深分页问题，会导致慢查询，应该大家都司空见惯了吧。
limit深分页为什么会变慢呢？ 假设有表结构如下：
CREATE TABLE account (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
name varchar(255) DEFAULT NULL COMMENT '账户名',
balance int(11) DEFAULT NULL COMMENT '余额',
create_time datetime NOT NULL COMMENT '创建时间',
update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_name (name),
KEY idx_create_time (create_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
驱动力
以下这个SQL，你知道执行过程是怎样的呢？
select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
这个SQL的执行流程酱紫：
通过普通二级索引树 idx_create_time，过滤 create_time条件，找到满足条件的主键 id。
通过主键 id，回到 id主键索引树，找到满足记录的行，然后取出需要展示的列（回表过程）
扫描满足条件的 100010行，然后扔掉前 100000行，返回。
因此，limit深分页，导致SQL变慢原因有两个：
limit语句会先扫描 offset+n行，然后再丢弃掉前 offset行，返回后 n行数据。也就是说 limit 100000,10，就会扫描 100010行，而 limit 0,10，只扫描 10行。
limit 100000,10 扫描更多的行数，也意味着回表更多的次数。
如何优化深分页问题?
我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法
就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。
假设上一次记录到100000，则SQL可以修改为：
select id,name,balance FROM account where id > 100000 limit 10;
这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。
延迟关联法
延迟关联法，就是把条件转移到主键索引树，然后减少回表。如下
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;
优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。
2.4 案例4：in元素过多
如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多哈。in元素一般建议不要超过200个，如果超过了，建议分组，每次200一组进行哈。
反例:
select user_id,name from user where user_id in (1,2,3...1000000);
如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询，in后面的子查询，你都不知道数量有多少那种，更容易采坑.如下这种子查询：
select * from user where user_id in (select author_id from artilce where type = 1);
如果type = 1有1一千，甚至上万个呢？肯定是慢SQL。索引一般建议分批进行，一次200个，比如：
select user_id,name from user where user_id in (1,2,3...200);
in查询为什么慢呢？
这是因为in查询在MySQL底层是通过n*m的方式去搜索，类似union。
in查询在进行cost代价计算时（代价 = 元组数 * IO平均值），是通过将in包含的数值，一条条去查询获取元组数的，因此这个计算过程会比较的慢，所以MySQL设置了个临界值(eq_range_index_dive_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。
驱动力
2.5 order by 走文件排序导致的慢查询
如果order by 使用到文件排序，则会可能会产生慢查询。我们来看下下面这个SQL：
select name,age,city from staff where city = '深圳' order by age limit 10;
它表示的意思就是：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。
查看explain执行计划的时候，可以看到Extra这一列，有一个Using filesort，它表示用到文件排序。
order by文件排序效率为什么较低
大家可以看下这个下面这个图:
2、从索引树 idx_city， 找到第一个满足 city='深圳’条件的 主键id,假设 id为 X；
3、到主键 id索引树拿到 id=X的这一行数据， 取age和主键id的值，存到 sort_buffer；
4、从索引树 idx_city拿到下一个记录的 主键id，假设 id=Y；
5、重复步骤 3、4 直到 city的值不等于深圳为止；
6、前面5步已经查找到了所有 city为深圳的数据，在 sort_buffer中，将所有数据根据 age进行排序；遍历排序结果，取前10行，并按照id的值回到原表中，取出 city、name 和 age三个字段返回给客户端。
order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。
2.5.1 rowid排序
rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。以下这个SQL，使用rowid排序，执行过程是这样：
select name,age,city from staff where city = '深圳' order by age limit 10;
1、MySQL为对应的线程初始化 sort_buffer，放入需要排序的 age字段，以及 主键id；
2.5.2 全字段排序
同样的SQL，如果是走全字段排序是这样的：
select name,age,city from staff where city = '深圳' order by age limit 10;
- MySQL 为对应的线程初始化 sort_buffer，放入需要查询的 name、age、city字段；
- 从索引树 idx_city， 找到第一个满足 city='深圳’条件的主键 id，假设找到 id=X；
- 到主键id索引树拿到 id=X的这一行数据， 取 name、age、city三个字段的值，存到 sort_buffer；
- 从索引树 idx_city 拿到下一个记录的主键 id，假设 id=Y；
- 重复步骤 3、4 直到 city的值不等于深圳为止；
- 前面5步已经查找到了所有 city为深圳的数据，在 sort_buffer中，将所有数据根据age进行排序；
- 按照排序结果取前10行返回给客户端。
驱动力
sort_buffer的大小是由一个参数控制的：sort_buffer_size。
- 如果要排序的数据小于 sort_buffer_size，排序在 sort_buffer内存中完成
- 如果要排序的数据大于 sort_buffer_size，则借助磁盘文件来进行排序。
借助磁盘文件排序的话，效率就更慢一点。因为先把数据放入sort_buffer，当快要满时。会排一下序，然后把sort_buffer中的数据，放到临时磁盘文件，等到所有满足条件数据都查完排完，再用归并算法把磁盘的临时排好序的小文件，合并成一个有序的大文件。
2.5.3 如何优化order by的文件排序
order by使用文件排序，效率会低一点。我们怎么优化呢？
- 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化 order by语句。
- 我们还可以通过调整 max_length_for_sort_data、sort_buffer_size等参数优化；
2.6 索引字段上使用is null， is not null，索引可能失效
表结构:
单个name字段加上索引，并查询name为非空的语句，其实会走索引的，如下:
单个card字段加上索引，并查询name为非空的语句，其实会走索引的，如下:
但是它两用or连接起来，索引就失效了，如下：
很多时候，也是因为数据量问题，导致了MySQL优化器放弃走索引。同时，平时我们用explain分析SQL的时候，如果type=range,要注意一下哈，因为这个可能因为数据量问题，导致索引无效。
2.7 索引字段上使用（！= 或者 < >），索引可能失效
假设有表结构：
虽然age加了索引，但是使用了！= 或者< >，not in这些时，索引如同虚设。如下：
驱动力
如果把它们的name字段改为编码一致，相同的SQL，还是会走索引。
user表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8。
2.9 group by使用临时表
group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢SQL。
2.9.1 group by执行流程
假设有表结构：
执行左外连接查询,user_job表还是走全表扫描，如下：
其实这个也是跟mySQL优化器有关，如果优化器觉得即使走了索引，还是需要扫描很多很多行的哈，它觉得不划算，不如直接不走索引。平时我们用！= 或者< >，not in的时候，留点心眼哈。
2.8 左右连接，关联的字段编码格式不一样
新建两个表，一个user，一个user_job
- Extra 这个字段的 Using temporary表示在执行分组的时候使用了临时表
- Extra 这个字段的 Using filesort表示使用了文件排序
group by是怎么使用到临时表和排序了呢？我们来看下这个SQL的执行流程
驱动力
- 方向1：既然它默认会排序，我们不给它排是不是就行啦。
- 方向2：既然临时表是影响group by性能的X因素，我们是不是可以不用临时表？
我们一起来想下，执行group by语句为什么需要临时表呢？group by的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?
可以有这些优化方案：
- group by 后面的字段加索引
- order by null 不用排序
- 尽量只使用内存临时表
- 使用SQL_BIG_RESULT
2.10 delete + in子查询不走索引！
之前见到过一个生产慢SQL问题，当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select + in子查询，却可以走索引。
MySQL版本是5.7，假设当前有两张表account和old_account,表结构如下：
临时表的排序是怎样的呢？
就是把需要排序的字段，放到sort buffer，排完就返回。在这里注意一点哈，排序分全字段排序和rowid排序
如果是全字段排序，需要查询返回的字段，都放入sort buffer，根据排序字段排完，直接返回
如果是rowid排序，只是需要排序的字段放入sort buffer，然后多一次回表操作，再返回。
2.9.2 group by可能会慢在哪里？
group by使用不当，很容易就会产生慢SQL问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。
- 如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是 tmp_table_size），会把内存临时表转成磁盘临时表。
- 如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。
2.9.3 如何优化group by呢
从哪些方向去优化呢？
select city ,count(*) as num from staff group by city;
1、创建内存临时表，表里有两个字段 city和num；
2、全表扫描staff的记录，依次取出city = 'X'的记录。
- 判断临时表中是否有为 city='X'的行，没有就插入一个记录 (X,1);
- 如果临时表中有 city='X'的行，就将X这一行的num值加 1；
3、遍历完成后，再根据字段 city做排序，得到结果集返回给客户端。这个流程的执行图如下：
执行的SQL如下：
驱动力
可以发现，实际执行的时候，MySQL对select in子查询做了优化，把子查询改成join的方式，所以可以走索引。但是很遗憾，对于delete in子查询，MySQL却没有对它做这个优化。
日常开发中，大家注意一下这个场景哈
参考资料
慢SQL优化一点小思路: https://juejin.cn/post/7048974570228809741#heading-7 SQL优化万能公式：5 大步骤 + 10 个案例: https://developer.aliyun.com/article/980780
为什么select + in子查询会走索引，delete + in子查询却不会走索引呢？
我们执行以下SQL看看：
查看执行计划，发现不走索引：
但是如果把delete换成select，就会走索引。如下：
驱动力
引言
在开发和维护MySQL数据库时，优化SQL查询语句是提高数据库性能和响应速度的关键。通过合理优化SQL查询，可以减少数据库的负载，提高查询效率，为用户提供更好的用户体验。本文将介绍常用的30种MySQL SQL查询优化方法，并通过实际案例演示它们的应用。
3、使用EXPLAIN分析查询计划
使用EXPLAIN命令可以分析查询的执行计划，帮助优化查询语句，查找潜在的性能问题。
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
第二部分：优化查询条件
4、使用WHERE子句过滤数据
在查询数据时，尽量使用WHERE子句对数据进行过滤，减少返回的数据量。
-- 不推荐
SELECT * FROM table_name;
-- 推荐
SELECT * FROM table_name WHERE column_name = 'value';
5、使用索引覆盖查询
索引覆盖查询是指查询的字段都包含在索引中，不需要回表查询数据。这样可以减少IO操作，提高查询效率。
-- 创建索引
CREATE INDEX idx_column ON table_name(column_name);
-- 索引覆盖查询
SELECT column1, column2 FROM table_name WHERE column_name = 'value';
6、避免在WHERE子句中使用函数
在WHERE子句中使用函数会导致索引失效，需要全表扫描。尽量避免在WHERE子句中使用函数。
-- 不推荐
SELECT * FROM table_name WHERE DATE_FORMAT(date_column, '%Y-%m-%d') = '2023-01-01';
-- 推荐
SELECT * FROM table_name WHERE date_column = '2023-01-01';
◀ MySQL常用30种SQL查询语句优化方法
作者：good7ob
原文链接：
https://mp.weixin.qq.com/s/VPE-mCkpWa-w0Imw7YDINg
第一部分：基础优化方法
1、使用索引
索引是提高数据库查询性能的基础，通过为查询字段添加合适的索引，可以加快查询速度。在创建索引时，需要考虑查询的频率和数据的更新频率，避免过度索引或不必要的索引。
-- 创建索引
CREATE INDEX idx_column ON table_name(column_name);
2、避免使用SELECT *
在查询数据时，尽量避免使用SELECT *，而是明确指定需要查询的字段。这样可以减少返回的数据量，提高查询效率。
-- 不推荐
SELECT * FROM table_name;
-- 推荐
SELECT column1, column2 FROM table_name;
驱动力
7、使用合适的数据类型
选择合适的数据类型可以减少存储空间和查询时间，提高数据库性能。
-- 不推荐
CREATE TABLE table_name (id VARCHAR(100), name VARCHAR(100));
-- 推荐
CREATE TABLE table_name (id INT, name VARCHAR(100));
第三部分：连接查询优化
8、使用INNER JOIN代替WHERE子句连接
使用INNER JOIN可以更好地表达表之间的关系，提高查询的可读性和性能。
-- 不推荐
SELECT * FROM table1, table2 WHERE table1.id = table2.id;
-- 推荐
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
9、使用JOIN ON代替WHERE子句过滤连接
在连接查询时，尽量使用JOIN ON对连接进行过滤，而不是在WHERE子句中过滤连接。
-- 不推荐
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE table2.name = 'value';
-- 推荐
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id AND table2.name = 'value';
10、使用合适的连接类型
根据实际业务需求选择合适的连接类型，包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。
-- INNER JOIN（默认连接类型）
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
-- LEFT JOIN
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
-- RIGHT JOIN
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
-- FULL JOIN
SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;
第四部分：子查询优化
11、使用EXISTS代替IN
在使用子查询时，尽量使用EXISTS代替IN，EXISTS只关心是否存在记录，而IN会将子查询的结果集加载到内存中，可能导致性能问题。
-- 不推荐
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推荐
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);
12、使用JOIN代替子查询
在查询中使用JOIN可以更好地表达查询的逻辑，避免使用复杂的子查询。
驱动力
-- 不推荐
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推荐
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
第五部分：LIMIT优化
13、使用LIMIT限制返回的记录数
在查询大量数据时，使用LIMIT可以限制返回的记录数，避免查询过多的数据。
-- 返回前10条记录
SELECT * FROM table_name LIMIT 10;
14、使用分页查询
在查询分页数据时，可以使用LIMIT结合OFFSET实现分页查询。
-- 返回第1页的数据，每页10条记录
SELECT * FROM table_name LIMIT 0, 10;
-- 返回第2页的数据，每页10条记录
SELECT * FROM table_name LIMIT 10, 10;
第六部分：排序优化
15、使用合适的排序字段
在排序查询时，选择合适的排序字段可以减少排序的时间和开销。通常应该选择已经建立了索引的字段进行排序，避免对大量数据进行排序操作。
- 不推荐
SELECT * FROM table_name ORDER BY name;
-- 推荐
SELECT * FROM table_name ORDER BY indexed_column;
16、使用覆盖索引减少排序
如果查询中只需要排序字段，并且该字段已经建立了索引，可以使用覆盖索引来减少排序的时间。
-- 创建索引
CREATE INDEX idx_name ON table_name(name);
-- 使用覆盖索引
SELECT name FROM table_name ORDER BY name;
17、使用DESC进行降序排序
在进行降序排序时，使用DESC关键字可以明确排序方式，避免不必要的排序操作。
-- 降序排序
SELECT * FROM table_name ORDER BY column_name DESC;
第七部分：避免使用通配符
18、避免使用%通配符
在查询数据时，尽量避免使用%通配符在查询字段的开头，这会导致索引失效，需要进行全表扫描。
-- 不推荐
SELECT * FROM table_name WHERE column_name LIKE '%value';
-- 推荐
SELECT * FROM table_name WHERE column_name LIKE 'value%';
19、使用前缀索引
如果需要在查询中使用通配符%在字段的结尾，可以使用前缀索引来优化查询性能。
驱动力
-- 创建前缀索引
CREATE INDEX idx_column ON table_name(column_name(10));
-- 使用前缀索引
SELECT * FROM table_name WHERE column_name LIKE 'value%';
第八部分：联合查询优化
20、使用UNION ALL代替UNION
在使用联合查询时，如果不需要去重操作，应该使用UNION ALL，可以减少查询的开销。
-- 不推荐
SELECT * FROM table1 WHERE condition
UNION
SELECT * FROM table2 WHERE condition;
-- 推荐
SELECT * FROM table1 WHERE condition
UNION ALL
SELECT * FROM table2 WHERE condition;
21、使用EXISTS代替IN和UNION
在查询中使用EXISTS代替IN和UNION，可以更好地表达查询逻辑，提高查询性能。
-- 不推荐
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 WHERE condition)
UNION
SELECT * FROM table1 WHERE id IN (SELECT id FROM table3 WHERE condition);
-- 推荐
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id AND condition)
OR EXISTS (SELECT 1 FROM table3 WHERE table1.id = table3.id AND condition);
第九部分：使用子查询优化
22、使用内连接代替子查询
在使用子查询时，尽量使用内连接代替，可以减少查询的开销。
-- 不推荐
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推荐
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
23、使用EXISTS代替IN
在使用子查询时，尽量使用EXISTS代替IN，EXISTS只关心是否存在记录，而IN会将子查询的结果集加载到内存中，可能导致性能问题。
-- 不推荐
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
-- 推荐
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);
第十部分：数据表设计优化
24、使用合适的数据类型
在创建数据表时，选择合适的数据类型可以减少存储空间和查询时间，提高数据库性能。
驱动力
-- 不推荐
CREATE TABLE table_name (id VARCHAR(100), name VARCHAR(100));
-- 推荐
CREATE TABLE table_name (id INT, name VARCHAR(100));
25、垂直拆分表
在数据表中包含大量冗余数据时，可以考虑对表进行垂直拆分，将不同的数据拆分到不同的表中，提高查询性能。
第十一部分：其他优化方法
26、使用连接池
使用连接池可以减少连接数据库的开销，提高数据库的并发性能。
27、合理配置缓冲区
根据数据库的实际情况，合理配置缓冲区大小，加快数据的读写速度。
28、使用延迟关联
在进行关联查询时，可以考虑使用延迟关联，将关联查询放在最后执行，减少关联操作的次数。
29、避免使用临时表
在查询中尽量避免使用临时表，临时表会增加查询的开销。
30、定期优化数据表
定期对数据表进行优化，包括重新建立索引、压缩表等操作，可以提高数据库的性能。
结语
本文介绍了常用的30种MySQL SQL查询优化方法，并通过实际案例演示了它们的应用。优化SQL查询是提高数据库性能和响应速度的关键，通过合理优化SQL查询可以减少数据库的负载，提高查询效率。读者可以根据自己的实际情况和业务需求，选择合适的优化方法，提高数据库的性能和可用性。同时，我们也要不断学习和实践，在数据库领域不断提高自己的知识水平和技术能力，成为一名优秀的数据库工程师。
易宝支付数据部
联系电话：1580-136-5057
地址：北京市朝阳区朝外大街甲6号
投稿地址：kai.zhao@yeepay.com
增值电信业务经营许可证：陕B2-20210327 | 
陕公网安备 61102302611033号