sql如何（sql如何输出指定数据）
三个案例，带你体验SQL的神奇特性
本文分享自华为云社区《
我们可以看到，5.7.21的默认模式包含
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_pISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
而第一个：ONLY_FULL_GROUP_BY就会约束：当我们进行聚合查询的时候，SELECT的列不能直接包含非GROUPBY子句中的列。那如果我们去掉该模式（从“严格模式”到“宽松模式”）呢？
我们发现，上述报错的SQL
--宽松模式下可以执行SELECTcno,cname,count(sno),MAX(sno)FROMtbl_student_classGROUPBYcno;
能正常执行了，但是一般情况下不推荐这样配置，线上环境往往是“严格模式”，而不是“宽松模式”；虽然案例中，无论是“严格模式”，还是“宽松模式”，结果都是对的，那是因为cno与cname唯一对应的，如果cno与cname不是唯一对应，那么在“宽松模式下”cname的值是随机的，这就会造成难以排查的问题，有兴趣的可以去试下;
二SQL的第二个神奇特性2.1问题描述下今天我想比较两个数据集
表A一共50,000,000行，其中有一列叫「ID」，表B也有一列叫「ID」。我想查的是有A表里有多少ID在B表里面，数据库用的是snowflake，它是一种一种多租户、事务性、安全、高度可扩展的弹性数据库，或者叫它实施数仓也行，具备完整的SQL支持和schema-less数据模式，支持ACID的事务，也提供用于遍历、展平和嵌套半结构化数据的内置函数和SQL扩展，并支持JSON和Avro等流行格式；
用query:
withAas(selectDISTINCT(id)asidfromTable_A),Bas(selectdistinct(id)asidfromTable_B),resultas(select*fromAwhereidin(selectidfromB))selectcount(*)fromresult
返回结果是26,000,000
也就是说，A应有24,000,000行不在B里面，对吧
可是我把第11行的in改成notin后，情况有点出乎我的意料
withAas(selectdistinct(id)asidfromTable_A),Bas(selectdistinct(id)asidfromTable_B),resultas(select*fromAwhereidnotin(selectidfromB))selectcount(*)fromresult
返回结果竟然是0，而不是24,000,000
于是我在snowflake论坛搜了下，发现5年前在这帖子下面有人回复到：
IfyouuseNOTIN(subquery),itcompareseveryreturnedvalueandincaseofNULLonanysideofcomparisonitstopsimmediatelywithnondefinedresultifyouuseNOTIN(subquery),itcompareseveryreturnedvalueandincaseofNULLonanysideofcomparisonitstopsimmediatelywithnondefinedresult
就是说，当你用notin，subquery（例如上面第11行的selectidfromB）里如果有Null,那么它就会立刻停止，返回未定义的结果，所以最后结果是0；
该如何解决
很简单
2.2去掉null值在第7行加了限定whereidisnotnull后，结果正常了
withAas(selectdistinct(id)asidfromTable_A),Bas(selectdistinct(id)asidfromTable_Bwhereidisnotnull),resultas(select*fromAwhereidnotin(selectidfromB))selectcount(*)fromresult
最终返回结果为24,000,000，这样就对了啊
2.3用notEXISTS代替notin注意第11行，用了notexists代替notin
withAas(selectdistinct(id)asidfromTable_A),Bas(selectdistinct(id)asidfromTable_Bwhereidisnotnull),resultas(select*fromAwherenotexists(select*fromBwhereA.id=B.id))selectcount(*)fromresult
返回结果也为24,000,000
当然，这肯定不是bug哈，而是特性，不然不会这么多年都留着，是我懂得太少了，得恶补下SQL哭泣。
不过不知道这个特性设计当初目的是什么，如果subquery返回了undefined，你好歹给我报个错啊。这个「特性」不仅仅在snowflake上面出现，看StackOverflow上讨论，貌似Oracle也是有这个「特性」的哈
三SQL的第三个神奇特性像Web服务这样需要快速响应的应用场景中，SQL的性能直接决定了系统是否可以使用；特别在一些中小型应用中，SQL的性能更是决定服务能否快速响应的唯一标准
严格地优化查询性能时，必须要了解所使用数据库的功能特点，此外，查询速度慢并不只是因为SQL语句本身，还有可能内存分配不佳、文件结构不合理、刷脏页等其他原因啊；
因此下面介绍些SQL神奇特性，但不能解决所有的性能问题，但是却能处理很多因SQL写法不合理而产生的性能问题
所以下面尽量介绍一些不依赖具体数据库实现，使SQL执行速度更快、消耗内存更少的优化技巧，只需调整SQL语句就能实现的通用的优化Tip
3.1环境准备下文所讲的内容是从SQL层面展开的哈，而不是针对某种特性的数据库，也就是说，下文的内容基本上适用于任何关系型数据库都可以；
但是，关系型数据库那么多，逐一来演示示例了，显然不太现实；我们以常用的MySQL来进行就行啦
MySQL版本：5.7.30-log，存储引擎：InnoDB
准备两张表：tbl_customer和tbl_recharge_record
3.2使用高效的查询针对某一个查询，有时候会有多种SQL实现，例如IN、EXISTS、连接之间的互相转换
从理论上来讲，得到相同结果的不同SQL语句应该有相同的性能的哈，但遗憾的是，查询优化器生成的执行计划很大程度上要受到外部数据结构的影响
所以，要想优化查询性能，必须知道如何写SQL语句才能使优化器生成更高效的执行计划
3.3使用EXISTS代替IN关于IN，相信大家都比较熟悉，使用方便，也容易理解；虽说IN使用方便，但它却存在性能瓶颈
如果IN的参数是1,2,3这样的数值列表，一般还不需要特别注意，但如果参数是子查询，那么就需要注意了
在大多时候，[NOT]IN和[NOT]EXISTS返回的结果是相同的，但是两者用于子查询时，EXISTS的速度会更快一些
假设我们要查询有充值记录的顾客信息，SQL该怎么写？
相信大家第一时间想到的是IN
IN使用起来确实简单，也非常好理解；我们来看下它的执行计划
我们再来看看EXISTS的执行计划：
可以看到的是，IN的执行计划中新产生了一张临时表：，这会导致效率变慢
所以通常来讲，EXISTS比IN更快的原因有两个
1、如果连接列（customer_id）上建立了索引，那么查询tbl_recharge_record时可以通过索引查询，而不是全表查询2、使用EXISTS，一旦查到一行数据满足条件就会终止查询，不用像使用IN时一样进行扫描全表（NOTEXISTS也一样）如果当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时表里（内联视图），然后扫描整个视图，很多情况下这种做法非常耗费资源
使用EXISTS的话，数据库不会生成临时表
但是从代码的可读性上来看，IN要比EXISTS好，使用IN时的代码看起来更加一目了然，易于理解
因此，如果确信使用IN也能快速获取结果，就没有必要非得改成EXISTS了
其实有很多数据库也尝试着改善了IN的性能
Oracle数据库中，如果我们在有索引的列上使用IN，也会先扫描索引
PostgreSQL从版本7.4起也改善了使用子查询作为IN谓词参数时的查询速度
说不定在未来的某一天，无论在哪个关系型数据库上，IN都能具备与EXISTS一样的性能
3.4使用连接代替IN其实在平时工作当中，更多的是用连接代替IN来改善查询性能，而非EXISTS，不是说连接更好，而是EXISTS很难掌握
回到问题：查询有充值记录的顾客信息，如果用连接来实现，SQL改如何写？
这种写法能充分利用索引；而且，因为没有了子查询，所以数据库也不会生成中间表；所以，查询效率是不错的
至于JOIN与EXISTS相比哪个性能更好，这不太好说；如果没有索引，可能EXISTS会略胜一筹，有索引的话，两者都差不多
3.5避免排序说到SQL的排序，我们第一时间想到的肯定是：ORDERBY，通过它，我们可以按指定的某些列来顺序输出结果
但是，除了ORDERBY显示的排序，数据库内部还有很多运算在暗中进行排序；会进行排序的代表性的运算有下面这些
如果只在内存中进行排序，那么还好；但是如果因内存不足而需要在硬盘上排序，那么性能就会急剧下降
所以，要尽量避免（或减少）无谓的排序，能够大大提高查询效率
灵活使用集合运算符的ALL可选项
SQL中有UNION、INTERSECT、EXCEPT三个集合运算符，分表代表这集合运算的并集、交集、差集
默认情况下，这些运算符会为了排除掉重复数据而进行排序
Usingtemporary表示进行了排序或者分组，显然这个SQL并没有进行分组，而是进行了排序运算
所以如果我们不在乎结果中是否有重复数据，或者事先知道不会有重复数据，可以使用UNIONALL代替UNION试下，可以看到，执行计划中没有排序运算了
对于INTERSECT和EXCEPT也是一样的，加上ALL可选项后就不会进行排序了
加上ALL可选项是一个非常有效的优化手段，但各个数据库对它的实现情况却是参差不齐，如下图所示
注意：Oracle使用MINUS代替EXCEPT；MySQL压根就没有实现INTERSECT和EXCEPT运算
3.6使用EXISTS来代替DISTINCT为了排除重复数据，DISTINCT也会进行排序
还记得用连接代替IN的案例吗，如果不用DISTINCT
SQL：SELECTtc.*FROMtbl_recharge_recordtrrLEFTJOINtbl_customertcontrr.customer_id=tc.id
那么查出来的结果会有很多重复记录，所以我们必须改进SQL
SELECTDISTINCTtc.*FROMtbl_recharge_recordtrrLEFTJOINtbl_customertcontrr.customer_id=tc.id
会发现执行计划中有个Usingtemporary，它表示用到了排序运算
我们使用EXISTS来进行优化
可以看到，已经规避了排序运算
3.7在极值函数中使用索引SQL语言里有两个极值函数：MAX和MIN，使用这两个函数时都会进行排序
例如：SELECTMAX(recharge_amount)FROMtbl_recharge_record
会进行全表扫描，并会进行隐式的排序，找出单笔充值最大的金额
但是如果参数字段上建有索引，则只需扫描索引，但不需要扫描整张表
例如：SELECTMAX(customer_id)FROMtbl_recharge_record;
会通过索引：idx_c_id进行扫描，找出充值记录中最大的顾客ID
但是这种方法并不是去掉了排序这一过程，而是优化了排序前的查找速度，从而减弱排序对整体性能的影响
能写在WHERE子句里的条件千万不要写在HAVING子句里
我们来看两个SQL以及其执行结果
你就明白了
从结果上来看，两条SQL一样；但是从性能上来看，第二条语句写法效率更高，原因有两个:
减少排序的数据量GROUPBY子句聚合时会进行排序，如果事先通过WHERE子句筛选出一部分行，就能够减轻排序的负担了
有效利用索引3.8WHERE子句的条件里可以使用索引HAVING子句是针对聚合后生成的视图进行筛选的，但是很多时候聚合后的视图都没有继承原表的索引结构
关于HAVING，更多详情可查看：神奇的SQL之HAVING→容易被轻视的主角
在GROUPBY子句和ORDERBY子句中使用索引
一般来说，GROUPBY子句和ORDERBY子句都会进行排序
如果GROUPBY和ORDERBY的列有索引，那么可以提高查询效率
特别是在一些数据库中，如果列上建立的是唯一索引，那么排序过程本身都会被省略掉
使用索引使用索引是最常用的SQL优化手段，这个大家都知道，怕就怕大家不知道：明明有索引，为什么查询还是这么慢（为什么索引没用上）
关于索引未用到的情况，可查看：神奇的SQL之擦肩而过→真的用到索引了吗，本文就不做过多阐述了
总之就是：查询尽量往索引上靠，规避索引未用上的情况
减少临时表在SQL中，子查询的结果会被看成一张新表（临时表），这张新表与原始表一样，可以通过SQL进行操作
但是，频繁使用临时表会带来两个问题
1、临时表相当于原表数据的一份备份，会耗费内存资源2、很多时候（特别是聚合时），临时表没有继承原表的索引结构因此，尽量减少临时表的使用也是提升性能的一个重要方法
灵活使用HAVING子句对聚合结果指定筛选条件时，使用HAVING子句是基本原则
但是如果对HAVING不熟，我们往往找出替代它的方式来实现，就像这样
然而，对聚合结果指定筛选条件时不需要专门生成中间表，像下面这样使用HAVING子句就可以
HAVING子句和聚合操作都是同时执行的，所以比起生成临时表后再执行WHERE子句，效率会更高一些，而且代码看起来也更简洁
需要对多个字段使用IN谓词时，让它们汇总到一处
SQL-92中加入了行与行比较的功能，这样一来，比较谓词=、和IN谓词的参数就不再只是标量值了，而应是值列表了
我们来看一个示例，多个字段使用IN谓词
这段代码中用到了两个子查询，我们可以进行列汇总优化，把逻辑写在一起
这样一来，子查询不用考虑关联性，而且只执行一次就可以
还可以进一步简化，在IN中写多个字段的组合
简化后，不用担心连接字段时出现的类型转换问题，也不会对字段进行加工，因此可以使用索引
先进行连接再进行聚合
连接和聚合同时使用时，先进行连接操作可以避免产生中间表
合理地使用视图
视图是非常方便的工具，我们在日常工作中经常用到
但是，如果没有经过深入思考就定义复杂的视图，可能会带来巨大的性能问题
特别是视图的定义语句中包含以下运算的时候，SQL会非常低效，执行速度也会变得非常慢
小结下文中虽然列举了几个要点，但其实优化的核心思想只有一个，那就是找出性能瓶颈所在，然后解决它；
其实不只是数据库和SQL，计算机世界里容易成为性能瓶颈的也是对硬盘，也就是文件系统的访问（因此可以通过增加内存，或者使用访问速度更快的硬盘等方法来提升性能）
不管是减少排序还是使用索引，亦或是避免临时表的使用，其本质都是为了减少对硬盘的访问！
四高斯数据库特性为啥优异首先能释放CPU多核心的计算资源众所周知，软件计算能力的提升一方面得益于CPU硬件能力的增强，另一方面也得益于软件设计层面能够充分利用CPU的计算资源。当前处理器普遍采用多核设计，GaussDB(forMySQL)单个节点最多可以支持64核的CPU。单线程查询的方式至多能用满一个核的CPU资源，性能提升程度有限，远远无法满足企业大数据量查询场景下对降低时延的要求。因此，复杂的查询分析型计算过程必须考虑充分利用CPU的多核计算资源，让多个核参与到并行计算任务中才能大幅度提升查询计算的处理效率；下图是使用CPU多核资源并行计算一个表的count()过程的例子：表数据进行切块后分发给多个核进行并行计算，**每个核计算部分数据得到一个中间count()结果**，并在最后阶段将所有中间结果进行聚合得到最终结果
然后是并行查询GaussDB(forMySQL)支持并行执行的查询方式，用于降低分析型查询场景的处理时间，满足企业级应用对查询低时延的要求。如前面所述，并行查询的基本实现原理是将查询任务进行切分并分发到多个CPU核上进行计算，充分利用CPU的多核计算资源来缩短查询时间。并行查询的性能提升倍数，理论上与CPU的核数正相关，就是说并行度越高能够使用的CPU核数就越多，性能提升的倍数也就越高；下图展示的是：在GaussDB(forMySQL)的64U实例上查询100G数据量的COUNT(*)查询耗时，不同的查询并发度分别对应不同耗时，并发度越高对应的查询耗时越短
它支持多种类型的并行查询算子，以满足客户各种不同复杂查询场景。当前最新版本（2021-9）已经支持的并行查询场景包括：
主键查询、二级索引查询主键扫描、索引扫描、范围扫描、索引等值查询，索引逆向查询并行条件过滤（where/having）、投影计算并行多表JOIN（包括HashJoin、NestLoopJoin、SemiJoin等）查询并行聚合函数运算，包括SUM/AVG/COUNT/BIT_AND/BIT_OR/BIT_XOR等并行表达式运算，包括算术运算、逻辑运算、一般函数运算及混合运算等并行分组groupby、排序orderby、limit/offset、distinct运算并行UNION、子查询、视图查询并行分区表查询并行查询支持的数据类型包括：整型、字符型、时间类型、浮点型等等其他查询下图是GaussDB(forMySQL)并行查询针对TPC-H的22条查询场景所做的性能测试结果，测试数据量为100G，并发线程数据是32。下图展示了并行查询相比传统MySQL单线程查询的性能提升情况：32并行执行下，单表复杂查询最高提升26倍性能，普遍提升20倍性能。多表JOIN复杂查询最高提升近27倍性能，普遍提升10倍性能，子查询性能也有较大提升；总而言之GaussDB(forMySQL)并行查询充分调用了CPU的多核计算资源，极大降低了分析型查询场景的处理时间，大幅度提升了数据库性能，可以很好的满足客户多种复杂查询场景的低时延要求。