各位老铁们，大家好，今天由我来为大家分享三个案例带你体验SQL的神奇特性，以及的相关问题知识，希望对大家有所帮助。如果可以帮助到大家，还望关注收藏下本站，您的支持是我们最大的动力，谢谢大家了哈，下面我们开始吧！

听我解释

有一个学生班级表(tbl_student_class)，数据如下

DROP TABLE IF EXISTS tbl_student_class;CREATE TABLE tbl_student_class ( id int(8) unsigned NOT NULL AUTO_INCRMENT COMMENT ‘自增主键’, sno varchar(12) NOT NULL COMMENT ‘学号’, cno varchar(5) NOT NULL COMMENT ‘班级编号’, cname varchar(20) NOT NULL COMMENT ‘班级名称’, PRIMARY KEY (id)) COMMENT=’学生班级表’;– —————– — ———- tbl_student_class 的记录– —————————-INSERT INTO tbl_student_class VALUES ( ‘ 1’, ‘20190607001’, ‘0607’, ‘影视七班’);INSERT INTO tbl_student_class VALUES (‘2’, ‘20190607002’, ‘0607’, ‘影视七班’);INSERT INTO tbl_student_class VALUES (‘3’, ‘20190608003’, ‘0608’, ‘影视8级’);INSERT INTO tbl_student_class VALUES (‘4’, ‘20190608004’, ‘0608’, ‘影视8级’);INSERT INTO tbl_student_class VALUES(‘5’, ‘20190609005’, ‘0609’, ‘影视九班’);INSERT INTO tbl_student_class VALUES(‘6’, ‘20190609006’, ‘0609’, ‘影视九班’) ;我想统计每个班级（班级号、班级名）一个学生有多少人，最大的学生人数？这个查询SQL应该怎么写呢？我想每个人都可以用脚来写

SELECT cno,cname,count(sno),MAX(sno) FROM tbl_student_classGROUP BY cno,cname;但有些人可能认为cno和cname本来就是一对一的。一旦cno确定，cname也就确定了。那我们用SQL可以这样写吗？

SELECT cno,cname,count(sno),MAX(sno) FROM tbl_student_classGROUP BY cno;执行报错

[Err] 1055 – SELECT 列表的表达式#2 不在GROUP BY 子句中，并且包含非聚合列“test.tbl_student_class.cname”，该列在功能上不依赖于GROUP BY 子句中的列；这与sql_mode=only_full_group_by不兼容提示信息：SELECT列表中的第二个表达式（cname）不在GROUP BY子句中，并且它不是**聚合函数**；这与SQL 模式不兼容：ONLY_FULL_GROUP_BY。为什么GROUP BY之后不能直接引用呢？原始表中的列（不在GROUP BY 子句中）？别着急，慢慢看就会明白

1.0 SQL 模式

MySQL 服务器可以在不同的SQL 模式下运行，并且这些模式可以针对不同的客户端应用不同的方式，具体取决于sql_mode 系统变量的值。 DBA 可以设置全局SQL 模式来匹配站点服务器操作要求，每个应用程序可以根据自己的要求设置其会话SQL 模式。

模式会影响MySQL支持的SQL语法及其执行的数据验证检查，这使得在不同环境中使用MySQL以及将MySQL与其他数据库服务器一起使用变得更加容易。更多详情请查看官网，自行查找：Server SQL Modes

不同MySQL版本的内容（包括默认值）会略有不同。检查的时候一定要和自己的MySQL版本保持一致。

SQL模式主要分为两类：语法支持和数据检查。常用的有以下几种：

语法支持类

ONLY_FULL_GROUP_BY 对于GROUP BY 聚合操作，如果SELECT、HAVING 或ORDER BY 子句中的列没有出现在GROUP BY 中，则SQL 不合法ANSI_QUOTES 启用ANSI_QUOTES 后，无法使用双引号。引用该字符串，因为它被解释为标识符。设置完后update t set f1=”.会报Unknown column ” in field list之类的语法错误。 PIPES_AS_CONCAT 对待||作为字符串连接运算符而不是OR 运算符。这和Oracle数据库是一样的。同样，与字符串拼接函数CONCAT()类似。使用SHOW CREATE TABLE时，NO_TABLE_OPTIONS不会输出MySQL特定的语法部分，例如ENGINE。使用mysqldump跨数据库类型迁移时需要考虑这一点。 NO_AUTO_CREATE_USER并不意味着NO_AUTO_CREATE_USER的字面意思。自动创建用户。在授权MySQL用户时，我们习惯使用GRANT.ON.TO dbuser和创建用户一起使用。设置该选项后，与Oracle操作类似。授权前必须先建立用户

1.1 数据检查类

。 NO_ZERO_DATE认为日期’0000-00-00’是非法的，这与后续是否设置严格模式有关。 1、如果设置了严格模式，那么自然满足NO_ZERO_DATE。但如果是INSERT IGNORE或UPDATE IGNORE，’0000-00-00’仍然允许，只显示警告；

2. 如果在非严格模式下设置NO_ZERO_DATE，效果与上面相同，允许’0000-00-00’但显示警告；如果没有设置NO_ZERO_DATE，则没有警告被视为完全合法的值；

3、NO_ZERO_IN_DATE情况与上面类似。区别在于控件的日期和日期是否可以为0，即2010-01-00是否合法；

NO_ENGINE_SUBSTITUTION 当使用ALTER TABLE 或CREATE TABLE 指定ENGINE 时，所需的存储引擎将被禁用或不编译。我应该怎么办？当启用NO_ENGINE_SUBSTITUTION时，直接抛出错误；当未设置此值时，CREATE 使用默认存储引擎，ATLER 不会进行更改并引发警告

STRICT_TRANS_TABLES 设置它以启用严格模式。请注意，STRICT_TRANS_TABLES 不是多种策略的组合。分别指的是如何在INSERT和UPDATE中处理小值或无效值： 1.如上所述将”传递给int在严格模式下是非法的。如果启用非严格模式，则变为0，产生警告；

2. Out Of Range，成为插入的最大边界值；

3. 当要插入的新行不包含其定义没有显式DEFAULT 子句的非NULL 列的值时，该列缺少值。

1.2 默认模式

当我们不修改配置文件时，MySQL有自己的默认模式；不同版本有不同的默认模式。

— 检查MySQL 版本SELECT VERSION();– 检查sql_modeSELECT @@sql_mode;

我们可以看到5.7.21的默认模式包含

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION和第一个：ONLY_FULL_GROUP_BY会约束：当我们执行聚合查询时，SELECT列不能直接包含非GROUP BY列条款。那么如果我们删除该模式（从“严格模式”到“宽松模式”）呢？

我们发现上面报的SQL错误

— 在宽松模式下，可以执行SELECT cno,cname,count(sno),MAX(sno) FROM tbl_student_classGROUP BY cno;并且可以正常执行，但是一般情况下不建议这样配置。在线环境常常处于“严格模式”。而不是“放松模式”；虽然在本例中，无论是“严格模式”还是“宽松模式”，结果都是正确的，那是因为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 等流行格式；

使用查询：

with A as( select unique(id) as id from Table_A),B as ( select unique(id) as id from Table_B ),result as ( select * from A where id in (select id from B))select count(* ) 结果返回26,000,000

换句话说，A 应该有24,000,000 行不在B 中，对吗？

但是当我把第11行的in改为not in后，情况有点出乎我的意料。

with A as（从Table_A中选择distinct（id）作为id），B as（从Table_B中选择distinct（id）作为id），结果为（从A中选择*，其中id不在（从B中选择id））选择count（ *) 结果返回0 而不是24,000,000

于是我在雪花论坛上搜索了一下，发现5年前就有人回复过这个帖子：

如果您使用NOT IN（子查询），它会比较每个返回值，如果比较的任何一方为NULL，它会立即停止并显示未定义的结果如果您使用NOT IN（子查询），它会比较每个返回值，如果比较为NULL在比较的任何一侧，它都会立即停止并出现未定义的结果

也就是说，当你使用not in时，如果子查询中存在Null（比如上面第11行的select id from B），那么它会立即停止并返回一个未定义的结果，所以最终结果为0；

如何解决

很简单

2.2 去掉null值

在第7行添加id不为空的限制后，结果正常。

with A as（从Table_A中选择distinct（id）作为id），B as（从Table_B中选择distinct（id）作为id，其中id不为空），结果为（从A中选择*，其中id不在（从B中选择id） ))select count(*) from result 最终返回结果是24,000,000，所以没错

2.3 用not exists代替not in

请注意，在第11 行中，使用not contains 代替not in。

with A as（从Table_A中选择distinct（id）作为id），B as（从Table_B中选择distinct（id）作为id，其中id不为空），结果为（从A中选择*，其中不存在（从B中选择*，其中A.id=B.id))select count(*) from result 返回结果也是24,000,000

当然，这绝对不是一个bug，而是一个特性。不然我也不会保留这么多年。就是因为我知道的太少，所以才要弥补，哭。

但我不知道这个功能的最初目的是什么。如果子查询返回未定义，可以向我报错。这种“特征”不仅仅出现在雪花上。看看StackOverflow上的讨论，看来Oracle也有这个“功能”哈哈

三 SQL的第三个神奇特性

在Web服务等需要快速响应的应用场景中，SQL的性能直接决定系统能否使用；尤其是在一些中小型应用中，SQL的性能是决定服务能否快速响应的唯一标准。

在严格优化查询性能时，必须了解所使用的数据库的功能特征。另外，查询速度慢不仅是SQL语句本身的原因，还可能是内存分配不好、文件结构不合理、脏页等造成的；

因此，下面介绍SQL的一些神奇特性，但它们并不能解决所有的性能问题，但是可以解决很多由于SQL编写不合理而导致的性能问题。

因此，我们尝试介绍一些不依赖于特定数据库实现的优化技术，使SQL执行速度更快，消耗更少的内存。通过简单调整SQL 语句即可实现的一般优化技巧。

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:subquery2的执行计划中生成了新的临时表，这会导致效率变慢。

所以一般来说，EXISTS比IN快的原因有两个

1、如果连接列（customer_id）建立了索引，则可以通过索引查询来查询tbl_recharge_record，而不需要全表查询。 2、使用EXISTS，一旦找到一行满足条件的数据，查询就会终止，这与使用IN时不同。扫描整个表（NOT EXISTS 也是如此）。如果IN的参数是子查询，数据库会先执行子查询，然后将结果存储在临时表（内联视图）中，然后扫描整个视图。在许多情况下，这种方法非常耗费资源

使用EXISTS，数据库不会生成临时表

但从代码可读性的角度来看，IN 比EXISTS 更好。使用IN 时的代码看起来更加清晰易懂。

因此，如果你确定使用IN可以快速得到结果，就没有必要改成EXISTS。

事实上，很多数据库也尝试过提升IN的性能。

在Oracle数据库中，如果我们在索引列上使用IN，索引也会首先被扫描

从版本7.4 开始，PostgreSQL 在使用子查询作为IN 谓词参数时还提高了查询速度

也许未来的某一天，IN无论在哪个关系型数据库上都能有和EXISTS一样的性能。

3.4 使用连接代替 IN

事实上，在日常工作中，更多人使用连接而不是IN来提高查询性能，而不是EXISTS。并不是连接比较好，而是EXISTS很难掌握。

回到问题：如果我们使用连接来查询有充值记录的客户信息，那么SQL应该怎么写呢？

这种写法可以充分利用索引；而且，因为没有子查询，数据库不会生成中间表；所以查询效率还是不错的

至于哪个JOIN比EXISTS性能更好，很难说；如果没有索引，EXISTS可能会稍微好一点，但是如果有索引，两者就差不多了。

3.5 避免排序

说到SQL排序，我们首先想到的肯定是：ORDERBY，通过它我们可以按指定列顺序输出结果。

然而，除了ORDERBY显示的排序之外，数据库内部还有很多秘密执行排序的操作；执行排序的代表性操作包括以下

如果你只在内存中进行排序，那就没问题；但如果由于内存不足而需要在硬盘上排序，那么性能就会大幅下降。

因此，我们应该尽量避免（或减少）不必要的排序，这样可以大大提高查询效率。

灵活使用集合运算符的ALL选项

SQL中有三个集合运算符UNION、INTERSECT和EXCEPT。子表表示这些集合运算的并集、交集和差集。

默认情况下，这些运算符进行排序以消除重复项

使用临时意味着执行排序或分组。显然这个SQL不进行分组，而是进行排序操作。

所以如果我们不关心结果是否有重复数据，或者事先知道不会有重复数据，我们可以用UNIONALL代替UNION来尝试。可以看到执行计划中没有排序操作。

对于INTERSECT 和EXCEPT 也是如此。添加ALL选项后，将不再进行排序。

添加ALL选项是一种非常有效的优化方法，但是它在各个数据库中的实现参差不齐，如下图

注意：Oracle使用MINUS而不是EXCEPT； MySQL 根本不实现INTERSECT 和EXCEPT 操作。

3.6 使用 EXISTS 来代替 DISTINCT

为了排除重复数据，DISTINCT也会进行排序

还记得使用CONNECT 而不是IN 的情况吗？如果不使用DISTINCT

SQL: SELECT tc.*FROM tbl_recharge_record trr LEFTJOIN tbl_customer tc on trr.customer_id=tc.id 结果会有很多重复记录，所以必须改进SQL

SELECTDISTINCT tc.*FROM tbl_recharge_record trr LEFTJOIN tbl_customer tc on trr.customer_id=tc.id 会发现执行计划中有Usingtemporary，说明使用了排序操作

我们使用EXISTS 进行优化

可以看到，排序操作已经被避免了

3.7 在极值函数中使用索引

SQL 语言中有两个极值函数：MAX 和MIN。使用这两个函数时会进行排序。

例如：SELECTMAX(recharge_amount) FROM tbl_recharge_record

会进行全表扫描，并进行隐式排序，找到单次充值的最大金额。

但如果参数字段上建有索引，则只需要扫描索引即可，不需要扫描全表。

例如：SELECTMAX(customer_id) FROM tbl_recharge_record;

会扫描索引：idx_c_id找到充值记录中最大的客户ID

但该方法并没有消除排序过程，而是优化了排序前的搜索速度，从而减弱了排序对整体性能的影响。

可以写在WHERE 子句中的条件一定不能写在HAVING 子句中。

我们来看两条SQL及其执行结果

你就会明白

从结果来看，两条SQL是一样的；但从性能角度来看，第二条语句效率更高，原因有两个：

减少排序的数据量

GROUP BY 子句将在聚合时排序。如果提前通过WHERE子句过滤掉一些行，就可以减轻排序的负担。

有效利用索引

3.8 WHERE 子句的条件里可以使用索引

HAVING子句用于过滤聚合后生成的视图，但很多时候聚合视图并不会继承原表的索引结构。

关于HAVING，更多详情请参见：神奇的SQL HAVING 容易被低估的主角

在GROUP BY 子句和ORDER BY 子句中使用索引

一般来说，GROUP BY 子句和ORDER BY 子句都会排序

如果GROUP BY和ORDER BY的列有索引，可以提高查询效率

特别是在某些数据库中，如果在列上建立了唯一索引，那么排序过程本身就会被省略。

使用索引是最常用的SQL 优化方法。这个大家都知道，但恐怕大家都不知道：为什么有索引查询却这么慢（为什么不使用索引）

总之，查询尽可能靠近索引，避免出现不使用索引的情况。

减少临时表在SQL中，子查询的结果将被视为一个新表（临时表）。这个新表可以像原来的表一样通过SQL操作。

但是频繁使用临时表会带来两个问题

1、临时表相当于原表数据的备份，会消耗内存资源。 2、很多情况下（尤其是聚合时），临时表并不会继承原表的索引结构。因此，尽量减少临时表的使用也可以提高性能。一个重要的方法

灵活使用HAVING子句在指定聚合结果的过滤条件时，使用HAVING子句是基本原则

但是如果你不熟悉HAVING，我们经常会想办法替换它，就像这样

但在指定聚合结果的过滤条件时，不需要专门生成中间表。您可以按如下方式使用HAVING 子句

HAVING子句和聚合操作是同时执行的，因此比生成临时表后执行WHERE子句效率更高，而且代码看起来更简洁。

当您需要在多个字段上使用IN 谓词时，请将它们汇总到一处

SQL-92中增加了行与行比较的功能，这样

来，比较谓词 = 、< 、> 和 IN 谓词的参数就不再只是标量值了，而应是值列表了

我们来看一个示例，多个字段使用 IN 谓词

这段代码中用到了两个子查询，我们可以进行列汇总优化，把逻辑写在一起

这样一来，子查询不用考虑关联性，而且只执行一次就可以

还可以进一步简化，在 IN 中写多个字段的组合

简化后，不用担心连接字段时出现的类型转换问题，也不会对字段进行加工，因此可以使用索引

先进行连接再进行聚合

连接和聚合同时使用时，先进行连接操作可以避免产生中间表

合理地使用视图

视图是非常方便的工具，我们在日常工作中经常用到

但是，如果没有经过深入思考就定义复杂的视图，可能会带来巨大的性能问题

特别是视图的定义语句中包含以下运算的时候，SQL 会非常低效，执行速度也会变得非常慢

小结下

文中虽然列举了几个要点，但其实优化的核心思想只有一个，那就是找出性能瓶颈所在，然后解决它；

其实不只是数据库和 SQL，计算机世界里容易成为性能瓶颈的也是对硬盘，也就是文件系统的访问（因此可以通过增加内存，或者使用访问速度更快的硬盘等方法来提升性能）

不管是减少排序还是使用索引，亦或是避免临时表的使用，其本质都是为了减少对硬盘的访问！

四高斯数据库特性为啥优异

首先能释放CPU多核心的计算资源众所周知，软件计算能力的提升一方面得益于CPU硬件能力的增强，另一方面也得益于软件设计层面能够充分利用CPU的计算资源。当前处理器普遍采用多核设计，GaussDB(for MySQL)单个节点最多可以支持64核的CPU。单线程查询的方式至多能用满一个核的CPU资源，性能提升程度有限，远远无法满足企业大数据量查询场景下对降低时延的要求。因此，复杂的查询分析型计算过程必须考虑充分利用CPU的多核计算资源，让多个核参与到并行计算任务中才能大幅度提升查询计算的处理效率；下图是使用CPU多核资源并行计算一个表的count()过程的例子：表数据进行切块后分发给多个核进行并行计算，**每个核计算部分数据得到一个中间count()结果**，并在最后阶段将所有中间结果进行聚合得到最终结果

然后是并行查询GaussDB(for MySQL)支持并行执行的查询方式，用于降低分析型查询场景的处理时间，满足企业级应用对查询低时延的要求。如前面所述，并行查询的基本实现原理是将查询任务进行切分并分发到多个CPU核上进行计算，充分利用CPU的多核计算资源来缩短查询时间。并行查询的性能提升倍数，理论上与CPU的核数正相关，就是说并行度越高能够使用的CPU核数就越多，性能提升的倍数也就越高；下图展示的是：在GaussDB(for MySQL)的64U实例上查询100G数据量的COUNT(*)查询耗时，不同的查询并发度分别对应不同耗时，并发度越高对应的查询耗时越短