【操作步骤&问题现象】1、利用SQL语句查询时仅能传回50条数据，为什么返回不全呢？大家知道怎么解决吗？【截图信息】这个地图上仅有50个国家显示了信息，其他国家的信息未返回过来，显示不全。直接在数据湖探索里面编辑SQL查询语句是查询正常的，有一百多个国家。

在 IT 的很多术语中，正向解释非常难，反向描述反而更容易懂。幂等性处理就是这类。举两个数据处理时，非幂等性常见的场景：1.在创建订单时，偶有因网络抖动，痴呆，掉线等因素，造成客户端与服务器之间通讯不畅。比如，客户端发起请求后，在约定时间内(通常 30秒)，没有得到服务器的反馈，导致重复发起创建订单的请求，实际上前面看似失败的订单已创建成功，最终造成创建两个甚至多个同样的订单2.重复扣款，扣库存。这个是最不能容忍的。如前所述，客户端重新不断发起扣款、扣库存的请求，会导致账目混乱。由此可见，做好程序的幂等性处理，非常重要!很多教科书，会笼统的说，幂等性处理是一种最终返回结果一致的程序处理。这么讲，不完美。幂等性处理，不仅对结果有约束，对处理造成的负面影响也有约束。来看关系型数据库的 DML 的幂等性处理。在库存管理软件中，对同一批货物操作增删改，就可能带来负面影响。比如在苹果门店的仓库管理软件中，某天门店客流量非常大，操作库存也比平时频繁了很多。这样一来，给库存管理就带来了风险。比如某台结算终端，就因为访问人数过多，经常掉线，超时。小王好不容易卖出去两台，结果死活就是结账不成功，连续操作4,5次后无果后，小王叫店长来重启了电脑。等重启后，结算是成功了，但库存为 0 了。店长跑去仓库一看，10 台 iPhone 13 都好好躺在那里，为什么库存为 0 了呢?这就是非幂等性处理造成的。客户端发起交易后，网络堵塞，结账请求一直没发成功。等计算机重启后，连续将之前的订单，重复发送了 10次，结果库存全扣没了。看下库存表的设计：create table ProductInventory( ProductLotId INT, ProductName VARCHAR(200), ProductInventoryVolume INT )iPhone 13 库存是这样的：ProductLotId ProductName ProductInventoryVolume A0001 iPhone13 10更新程序也挺简单：UPDATE ProductInventory SET ProductInventoryVolume = ProductInventoryVolume - 1 WHERE ProductLotId = 'A0001'由此可见，是连续的交易请求，让库存清 0 了。于是，第一种幂等性处理方法就来了 - UUID 通用唯一标识符：CREATE TABLE ProductSalesTransactionAudit( AuditId BIGINT, RequestUUID UniqueIdentifier, RequestCompleted BIT )在每次请求中，加入一个 RequestUUID(Universally Unique Identifier，通用唯一标识符, Java/C#/Python 等编程语言均有实现 UUID 的库)在数据库端维护一张表 ProductSalesTransactionAudit，若有请求被数据库接收到，先去该表查询是否存在.若存在且 RequestCompleted 为1，就表示该请求被数据库正确处理过，可以跳过这次处理，并将 RequestCompleted 返回给客户端;没有，则在这表里插入一行，且把数据库的处理结果，更新到 RequestCompleted.这样，一个可行的幂等性处理，就完成了。但不是十分完美，因为该表数据量，会显著性增长，造成性能缓慢。于是，要寻找下一种幂等性处理方案。接下来再看这个例子，依旧是以苹果这家门店为例。某天仓库中剩余 10只 iPhone 13. 小王和小黄同时销售出去 2只，理论上剩下 6只。按照正常操作，小王和小黄在操作库存时，同时看到有 10只，每人减去 2只，剩余 8只，由于看不到对方的操作，因此显示 8只剩余时，两个人都没觉得库存错了。create table ProductInventory( ProductLotId INT, ProductName VARCHAR(200), ProductInventoryVolume INT )小王和小黄，同时查询 iPhone 的库存时，是这样:ProductLotId ProductName ProductInventoryVolume A0001 iPhone 13 10他俩抓取后，经过他俩各自的本地计算(网页端或手持设备)，变成了这样:ProductLotId ProductName ProductInventoryVolume A0001 iPhone 13 8当他们把本地数据上传时，无论谁先，数据库最终的 iPhone 13 的存量，都成了 8. 但事实上，错的离谱，店长要骂娘!那么平时我们设计系统时，该怎么处理这种意料中的错误呢，这里涉及到事务管理的技巧。有一种乐观派做法是，在库存表上，加一列，标识行的版本。当本行数据更新时，首先对比这个版本列，若相同，则更新，若不同，则报 ”您修改的数据，已被其他人抢先更新，请确定后再次保存“ 的提示，最后标识列会被自动更新。接下来，实现上面这种版本控制的做法：create table ProductInventory( ProductLotId INT, ProductName VARCHAR(200), ProductInventoryVolume INT， ProductLotTS timestamp)原库存是这样：ProductLotId ProductName ProductInventoryVolume ProductLotTS A0001 iPhone 13 10 2022050114364700001他俩抓取后，经过各自的本地计算，变成了这样:ProductLotId ProductName ProductInventoryVolume ProductLotTS A0001 iPhone 13 8 2022050114364700001当小王上传数据时，程序会同时以 A0001 + 2022050114364700001 作为更新条件，先将 ProductInventoryVolume 更新成8，同时因 timestamp 是系统自动更新的对象，已经变成了 2022050114364700002 .等到小黄再更新，程序也同样同时以 A0001 + 2022050114364700001 作为更新条件，发现 ProductLotTS 已经改变了，意味着在读取数据后，有别人先一步做了更新，此时小黄更新库存就会失败。他必须重新读取数据后，再操作。只要一次更新成功，ProductLotTS 就会改变，即使相同的请求再发送一遍，也会因为 ProductLotTS 不匹配，导致失败!这就是第二种幂等性处理程序，不仅仅做了防重复处理，还能省去一张表的维护代价。

实际上arm架构服务器是不可以安装SQL SERVER的，SQL SERVER 2022 preview 版本说明也没有提及兼容arm架构。目前唯一的做法是安装 Azure SQL Edgesudo docker pull mcr.microsoft.com/azure-sql-edgesudo docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=Your@Strong!Password' -e 'MSSQL_PID=Developer' -p 1433:1433 --name azuresqledge -d mcr.microsoft.com/azure-sql-edge

介绍一个SQL Server 2016后新增的功能：查询存储。查询存储的工作原理类似于飞行数据记录器或者黑匣子，不断地收集与查询和计划相关的编译和运行时信息，包括已执行查询的历史记录，查询运行时执行统计信息，针对执行计划的执行计划等。与查询相关的数据将永久保存在内部表中，并通过一组视图向用户显示。通过这些信息，可以快速查找性能差异，识别由查询计划更改和故障排除引起的性能等等问题。通过以下命令或者SSMS界面进行开启ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON; 查询存储开启后官方对内部对应的一些表，详细描述如下查看说明当然，这种类似的节点信息收集的东西，其实并不适合查询频率过大的查询，经过非严谨测试，性能损耗大概在5%左右。做过DB性能优化的人应该都知道，以前我们要么通过持续性的日志记录分析，要么通过实时的监控去找到对应的性能瓶颈，包括CPU、内存、IO等，查询存储其实就是在此基础上更进一步，把我们关心的点都存储起来，并且有更详尽信息和标准分析报告，相当省事。具体可以查看官方文档学习学习。

SQL Server在两年前进行了最后一次重大里程碑更新，即SQL Server 2019更新。SQL Server 2022更新的一大重点是与Microsoft Azure云的更紧密集成。同时，微软正在为Cosmos DB数据库提供一系列增量更新，包括索引指标-帮助优化查询性能，以及新Patch API-支持数据库中优化部分文档更新。Gartner公司分析师Adam Ronthal称：“Cosmos DB仍然是强大的多模型非关系DBMS(数据库管理系统)产品。在提供具有多个非关系API的平台时，微软将Cosmos DB定位为适用于云原生应用程序的灵活、现代的DBMS。”在Ronthal看来，微软对Cosmos DB采取了不同于其一些核心竞争对手的方法，主要体现在他们提供多模型非关系平台，而非多个最适合的工程系统。Ronthal称：“这提供了一种统一的方法，对希望整合其数据管理领域的企业很有吸引力。”SQL Server 2022将推动微软云数据服务Cosmos DB是一种较新的多模型云原生数据库，微软仍致力于推进其更成熟的SQL Server 数据库。Cosmos DB于2017年首次发布，而SQL Server的历史可以追溯到1989年，早于现代云时代。通过SQL Server 2022，微软的目标是将关系数据库平台引入其Azure云生态系统。微软执行副总裁Scott Guthrie在Ignite技术会议上说：“SQL Server 2022是迄今为止支持云功能最多的SQL Server版本。”Guthrie指出，SQL Server 2022增加了新的业务连续性功能，在Azure中具有内置灾难恢复集成。该更新还添加了与数据治理平台Azure Purview的集成。Guthrie补充说，它还包括本地操作SQL Server数据的分析，其中Azure Synapse分析运行在云端。总的来说，Guthrie表示微软正在寻求在其内部部署和云数据服务之间提供双向灵活性。根据微软在SQL Server 2022中所采取的方向，Ronthal表示这有助于将Azure定位为分析和业务连续性的重心，同时为本地和云组件提供整体方法。 Ronthal 称：“微软继续投资于本地组件和云组件之间的更深层次集成，利用它们在两个领域的优势。”

引言相信大家都知道索引可以加快数据的查询速度，但是有时候如果索引设计不当，也可能造成索引失效而进行全表数据扫描，从而最终导致系统性能下降。因此我们在索引设计阶段就需要充分考虑各种可能情况，尽量避免由于索引设计缺陷导致的后期出现数据查询性能问题。本文总结了7个实用Mysql索引设计原则，相信在大家进行索引设计的时候可以进行参考。索引设计原则我们在数据库表设计好之后，先不要着急马上就进行表的索引设计，因为这个时候其实你也并不清楚未来在这个表上可能存在的查询条件到底是什么。所以我们需要先根据实际的产品需求来进行业务代码开发，在这个过程中我们必然会涉及到数据库持久化操作，也就是我们常说的CRUD。等我们把对应的Mapper接口以及SQL写好后，也就基本确定了哪些字段是条件字段、哪些字段是排序字段以及哪些字段是分组字段。这些字段确认好之后，我们就可以着手进行数据库表的索引设计了。关于如何设计索引，这里给大家梳理了7条非常实用的索引设计原则，相信大家在实际的项目中都可以用得上。原则一：根据SQL语句中的where条件、order by条件以及group by条件对应的字段进行索引设计。当我们的SQL语句中出现where条件、order by条件以及group by条件的时候，也就是表示我们需要通过SQL语句来进行数据过滤(where条件)、根据哪些字段进行排序(order by条件)以及根据哪些字段进行分组聚合(group by条件)。因此我们的设计的索引需要尽可能的覆盖这些字段，为的就是在数据查询的时候通过这些字段用上索引。假设我们有这样一张表clothes可以用来查询衣服，那么在设计索引的时候就需要根据实际的查询需求在对应的字段建立索引。那么对于衣服这张表来说一般会在c_brand(品牌)、c_type(类型)以及c_size(尺码)等这些字段建立索引，因为他们是最常用的筛选条件，另外可以考虑在价格字段上进行排序，这也是非常常见的过滤条件。原则二：在基数比较大的字段上建立索引，同时需要将基数更高的字段放在最左边。什么叫基数比较大的字段呢?实际就是值比较多的字段，或者说就是字段值的区分度比较高，我们可以用一个简单的公式来评判某个字段的区分度，区分度等于count(distinct 具体的列) / count(*)，表示字段不重复的比例。也就是说字段中包含的变化数据比较多的话是比较适合建索引的，因为这样才能发挥索引B+树的潜力。为什么这么说呢?假设有这样一张员工表中包含了性别字段i_gender，它的值只有0：男性，1：女性这两个值。我们都知道Mysql的索引结构是通过B+树实现的，而B+树背后的核心本质思想实际就是二分查找。而二分查找就需要待排序的数据基数大，也就是区分度高。而字段中只有0、1这样的就属于基数比较小，无法发挥索引树检索的效率，Mysql认为这种索引树还不如全表查询来的痛快。另外还需要特别注意点是，对于区分度高的字段我们应该把它放在联合索引的左侧，因为这样可以更快得过滤掉更多的无效数据，从而提升索引的使用效率。还是拿员工信息来举例子，员工表中的毕业院校的字段的区分度就比民族字段区分度要高的多，索引我们在设计联合索引的时候就需要将毕业院校的字段仿造民族的左侧，这样可以更快的过滤掉无效数据。原则三：如果SQL中出现JOIN操作，那么JOIN的字段必须建立索引，同时字段的类型、字符集都需要保持一致。数据库JOIN是常见的数据记录遍历的SQL操作，假设平台有一张用户表以及订单表，这个时候如果想要获取用户的订单信息，那么就可以使用JOIN操作来完成操作。不过在使用JOIN的过程中如果参与JOIN的表过多的话，对应的结果可能是一个笛卡尔积，对于Mysql的优化器来说实在是很难选择出来哪个才是最好的执行计划，就好比找对象一样，如果只有一个可以选择也没什么好纠结的，如果有10个可以选择，那就很头大了，不知道选择哪个好，因此我们要避免出现过多数量表的JOIN。另外很重要的一点就是在进行JOIN的字段上一定要建立索引，否则全表扫描。同时JOIN字段的类型、字符集等都要保持一致，避免在JOIN过程中可能导致的隐式的类型转换造成不走索引的后果。原则四：如果SQL中出现JOIN操作，那么JOIN的字段必须建立索引，同时字段的类型、字符集都需要保持一致。数据库JOIN是常见的数据记录遍历的SQL操作，假设平台有一张用户表以及订单表，这个时候如果想要获取用户的订单信息，那么就可以使用JOIN操作来完成操作。不过在使用JOIN的过程中如果参与JOIN的表过多的话，对应的结果可能是一个笛卡尔积，对于Mysql的优化器来说实在是很难选择出来哪个才是最好的执行计划，就好比找对象一样，如果只有一个可以选择也没什么好纠结的，如果有10个可以选择，那就很头大了，不知道选择哪个好，因此我们要避免出现过多数量表的JOIN。另外很重要的一点就是在进行JOIN的字段上一定要建立索引，否则全表扫描。还有很重要的一点，用于JOIN的字段的类型、字符集等都需要保持一致，否则可能存在隐式的类型转换导致走不了索引。原则五：尽量在字段类型值比较小的字段上建立索引。索引本身也是占用磁盘空间的，因此如果可以在字段类型比较小的字段上面建立索引，相应的索引占用空间就会更少，对应其数据检索的效率就会更高。但是这并非绝对的，如果存在区分度更高的字段但是字段类型比较大，那么我们还是会在区分度高的字段上面建立索引，但是我们可以采取一些折中的办法，比如我们可以取字段的前10个字符作为索引，这样我们们既可以在区分度高的字段建立索引，但是又至于太占用磁盘空间。原则六：索引不是建地越多越好有的同学在设计索引的时候恨不得把所有的字段都加上索引，总是觉得索引越多肯定性能越好，实际上真实场景下并非如此。我们都知道索引就像是一本书的目录，就像树的目录会占用书中的纸张一样，索引也是需要占用磁盘空间进行存储的，因此过多的索引会浪费资源。另外索引过多反而会降低性能，因为在进行数据插入的过程中，如果索引建立的过多就会导致更新多棵索引树，在这个过程中，如果数据的插入并不是按照顺序插入那么还会导致数据页分裂的问题。因此我们尽量通过两道三个联合索引来覆盖全部的查询场景。原则七：使用字符串前缀创建索引有些字段类型的长度比较长，因此字段的区分区相对来说也是比较大的，因此这些字段比较适合建索引。但是也是因为字段长度的原因，所建立的索引占用磁盘空间就会相对较大。实际上只要字段区分度足够高，没有必要对全字段建立索引，我们可以截取字段指定数量的字符作为检索条件的索引，具体需要截取多少字符那需要根据截取的字符串是否可以保持比较大区分度来进行决定。总结本文主要总结了在进行索引设计的时候需要考虑的几点设计原则，其实索引设计的根本无非就是两点，一个是希望通过两三个联合索引来覆盖数据检索的各个场景，避免因为检索的时候没有索引导致的数据检索效率低的问题，再者就是希望在实际的SQL运行过程中尽量避免索引失效情况的发生，避免建了索引但是实际上并不起作用。把握了这两个准则之后，相信大家在设计索引的时候可以游刃有余。

上一期酷哥分析了openGauss数据库的启动过程，包括主线程，辅助线程及业务处理线程的启动过程，这一期主要分析简单查询语句在业务处理线程Postgres上的执行流程，并介绍如何利用gdb梳理代码逻辑。简单查询的执行SQL引擎是数据库系统的入口，执行用户简单查询的入口函数是exec_simple_query。运行在业务处理线程Postgres。通常可以把SQL引擎分成SQL解析和查询优化两个主要的模块，SQL引擎对输入的SQL语言进行词法分析、语法分析、语义分析，从而生成逻辑执行计划，逻辑执行计划经过代数优化和代价优化之后，产生物理执行计划。在SQL引擎将用户的查询解析优化成可执行的计划之后，数据库进入查询执行阶段。执行器基于执行计划对相关数据进行提取、运算、更新、删除等操作，以达到用户查询想要实现的目的。exec_simple_query 1.start_xact_command()：开始一个事务2.pg_parse_query()：对查询语句进行词法和语法分析，生成一个或者多个初始的语法分析树3. 进入foreach (parsetree_item, parsetree_list)循环，对每个语法分析树执行查询4. pg_**yze_and_rewrite()：根据语法分析树生成基于Query数据结构的逻辑查询树，并进行重写等操作5. pg_plan_queries()：对逻辑查询树进行优化，生成查询计划6. CreatePortal()：创建Portal, Portal是执行SQL语句的载体，每一条SQL对应唯一的Portal7. PortalStart()：负责进行Portal结构体初始化工作，包括执行算子初始化、内存上下文分配等8. PortalRun()：负责真正的执行和运算，它是执行器的核心9. PortalDrop()：负责最后的清理工作，主要是数据结构、缓存的清理10. finish_xact_command()：完成事务提交11. EndCommand()：通知客户端查询执行完成gdb调试调试需要用到符号信息，configure使用如下命令./configure --gcc-version=7.3.0 CC=g++ CFLAGS='-O0' --prefix=$GAUSSHOME --3rd=$BINARYLIBS --enable-debug --enable-cassert --enable-thread-safety --with-readline --without-zlibgdb attach 进程号，这里进程号为17012gdb attach 17012info threads查看所有线程，t 线程号切换线程，bt可以查看线程调用栈也可以使用linux工具gstack 打印函数调用栈以调试select语句为例，gdb attach 进程号，在exec_simple_query打上断点，执行select语句即可开始调试

先聊聊Postgre的词汇结构1.词汇结构1.1标识符和关键字SQL 输入由一系列命令组成。命令由一系列标记组成，以分号 （“;”） 结尾。输入流的末尾也会终止命令。哪些关键字有效取决于特定命令的语法。标记可以是关键字、标识符、带引号的标识符、文本（或常量）或特殊字符符号。标记通常由空格（空格，制表符，换行符）分隔，但如果没有歧义，则不需要这样（通常只有在特殊字符与其他标记类型相邻时才会出现这种情况）。举个栗子  select * from s_student ;   update s_student set age =1 where name ='xiaoming'; insert  into student  values （"liming' ,25）；以上是有效的 SQL 输入这是一个由三个命令组成的序列，每行一个命令（尽管这不是必需的;一行上可以有多个命令，并且可以有效地跨行拆分命令）。此外，注释可以出现在 SQL 输入中。它们不是标记，它们实际上等同于空格。标记（如 、或上面的示例中）是关键字的示例，即在 SQL 语言中具有固定含义的单词。令牌 和 是标识符的示例。它们标识表、列或其他数据库对象的名称，具体取决于使用它们的命令。因此，它们有时被简单地称为“名称”。关键字和标识符具有相同的词汇结构，这意味着如果不了解语言，就无法知道令牌是标识符还是关键字。SQL 标识符和关键字必须以字母（-，但也包括带有变音符号和非拉丁字母的字母）或下划线 （） 开头。标识符或关键字中的后续字符可以是字母、下划线、数字 （-） 或美元符号 （）。请注意，根据 SQL 标准的字母，标识符中不允许使用美元符号，因此使用它们可能会使应用程序的可移植性降低。SQL标准不会定义包含数字或以下划线开头或结尾的关键字，因此这种形式的标识符是安全的，不会与标准的未来扩展发生冲突。az_09$系统使用不超过 -1 个字节的标识符;较长的名称可以写在命令中，但它们将被截断。默认情况下，为 64，因此最大标识符长度为 63 个字节。如果此限制有问题，可以通过更改 中的常量来提高它。关键字和未加引号的标识符不区分大小写。因此：select * from s_student ;  写成seLEct * from s_student ; 也不是不可以，不过这个就是个人习惯，轻度强迫症 必须全部大写或者小写 。经常使用的惯例是用大写字母写关键词，用小写字母写名字，例如：UPDATE student SET age =‘5’；还有第二种标识符：分隔标识符或带引号的标识符。它是通过将任意字符序列括在双引号 （） 中而形成的。分隔标识符始终是标识符，而不是关键字。因此，可用于引用名为“select”的列或表，而未加引号将被视为关键字，因此在需要表或列名称时使用时会引起解析错误。该示例可以使用带引号的标识符编写，如下所示：UPDATE “student” SET "age"=‘5’；带引号的标识符可以包含任何字符，但代码为零的字符除外。（要包含双引号，请写两个双引号。这允许构造原本不可能实现的表名或列名，例如包含空格或 & 符号的表名或列名。长度限制仍然适用。引用标识符也会使其区分大小写，而未引用的名称始终折叠为小写。例如，PostgreSQL 认为标识符 、 和 是相同的，但与这三者不同。（在PostgreSQL中将未引用的名称折叠为小写与SQL标准不兼容，SQL标准规定未引用的名称应折叠为大写。因此，应等同于不按标准。如果你想编写可移植的应用程序，建议你总是引用一个特定的名字，或者永远不要引用它。STU"stu""Stu""STU"stu带引号的标识符的变体允许包括由其码位标识的转义 Unicode 字符。此变体以（大写或小写 U 后跟 & 符号）开头，紧挨着开始的双引号，中间没有任何空格，例如 。（请注意，这会与运算符 产生歧义。在运算符周围使用空格以避免此问题。在引号内，可以通过编写反斜杠后跟四位十六进制码位号或反斜杠后跟加号后跟六位十六进制码位号来以转义形式指定 Unicode 字符。例如，标识符可以写为U&U&"foo"&"data"转义字符可以是除十六进制数字、加号、单引号、双引号或空格字符以外的任何单个字符。请注意，转义字符写在 单引号中，而不是双引号，位于 之后。UESCAPE若要在标识符中按字面意思包含转义字符，请将其写入两次。4 位或 6 位转义形式可用于指定 UTF-16 代理项对，以组合代码点大于 U+FFFF 的字符，尽管 6 位格式的可用性在技术上使这变得不必要。（代理项对不直接存储，而是合并到单个代码点中。如果服务器编码不是 UTF-8，则由这些转义序列之一标识的 Unicode 码位将转换为实际的服务器编码;如果无法做到这一点，则会报告错误。1.2常量常量里面又分为字符串常量，带有C风格转移的字符串常量，带UNICODE风格转义字符的常量，带有美元字符的字符串常量，位字符串常量，数字常量，其他类型的常量。下来一一做一个简单的讨论。SQL 中的字符串常量是由单引号 （） 限定的任意字符序列，例如 。要在字符串常量中包含单引号字符，请编写两个相邻的单引号，例如 。请注意，这与双引号字符 （） 不同。''This is a string''Dianne''s horse'"仅由至少一个换行符的空格分隔的两个字符串常量将被串联并有效地处理，就好像字符串已编写为一个常量一样。PostgreSQL还接受“转义”字符串常量，这是SQL标准的扩展。转义字符串常量是通过在开始单引号之前写入字母（大写或小写）来指定的，例如.（跨行继续转义字符串常量时，请仅在第一个开头引号之前写入。在转义字符串中，反斜杠字符 （） 开始一个类似 C 的反斜杠转义序列，其中反斜杠和后续字符的组合表示一个特殊的字节值，如下表\b退格\f进纸\n换行符\r回车\t标签\o， ， （o = 0–7）\oo\ooo八进制字节值\xh， （h = 0–9， A–F）\xhh十六进制字节值\uxxxx， （x = 0–9， A–F）\Uxxxxxxxx16 位或 32 位十六进制 Unicode 字符值PostgreSQL还支持另一种类型的字符串转义语法，允许按码位指定任意Unicode字符。Unicode 转义字符串常量以（大写或小写字母 U 后跟与号）开头，紧跟在左引号之前，中间没有任何空格，例如 。（请注意，这会与运算符 产生歧义。在运算符周围使用空格以避免此问题。在引号内，可以通过编写反斜杠后跟四位十六进制码位号或反斜杠后跟加号后跟六位十六进制码位号来以转义形式指定 Unicode 字符。虽然用于指定字符串常量的标准语法通常很方便，但当所需的字符串包含许多单引号或反斜杠时，可能很难理解，因为每个单引号或反斜杠都必须加倍。为了允许在这种情况下进行更具可读性的查询，PostgreSQL提供了另一种称为“美元报价”的方法来编写字符串常量。以美元报价的字符串常量由美元符号 （）、零个或多个字符的可选“标记”、另一个美元符号、组成字符串内容的任意字符序列、美元符号、以此美元报价开头的相同标记以及美元符号组成。位字符串常量看起来像常规字符串常量，在开头引号之前有一个（大写或小写）（没有中间空格）其中数字是一个或多个十进制数字（0 到 9）。如果使用小数点，则必须至少有一个数字位于小数点之前或之后。指数标记 （） 后面必须至少有一个数字（如果存在）。常量中不能嵌入任何空格或其他字符。

SQL 数据类型在介绍完一些基本概念之后，我们来认识一下，Flink SQL 中的数据类型。Flink SQL 内置了很多常见的数据类型，并且也为用户提供了自定义数据类型的能力。总共包含 3 部分：原子数据类型。复合数据类型。用户自定义数据类型。一、原子数据类型1、字符串类型：CHAR、CHAR(n)：定长字符串，就和 Java 中的 Char 一样，n 代表字符的定长，取值范围 [1, 2,147,483,647]。如果不指定 n，则默认为 1。VARCHAR、VARCHAR(n)、STRING：可变长字符串，就和 Java 中的 String 一样，n 代表字符的最大长度，取值范围 [1, 2,147,483,647]。如果不指定 n，则默认为 1。STRING 等同于 VARCHAR(2147483647)。2、二进制字符串类型：BINARY、BINARY(n)：定长二进制字符串，n 代表定长，取值范围 [1, 2,147,483,647]。如果不指定 n，则默认为 1。VARBINARY、VARBINARY(n)、BYTES：可变长二进制字符串，n 代表字符的最大长度，取值范围 [1, 2,147,483,647]。如果不指定 n，则默认为 1。BYTES 等同于 VARBINARY(2147483647)。3、 精确数值类型：DECIMAL、DECIMAL(p)、DECIMAL(p, s)、DEC、DEC(p)、DEC(p, s)、NUMERIC、NUMERIC(p)、NUMERIC(p, s)：固定长度和精度的数值类型，就和 Java 中的 BigDecima一样，p 代表数值位数(长度)，取值范围 [1, 38];s 代表小数点后的位数(精度)，取值范围 [0, p]。如果不指定，p 默认为 10，s 默认为 0。TINYINT：-128 到 127 的 1 字节大小的有符号整数，就和 Java 中的 byte 一样。SMALLINT：-32,768 to 32,767 的 2 字节大小的有符号整数，就和 Java 中的 short 一样。INT、INTEGER：-2,147,483,648 to 2,147,483,647 的 4 字节大小的有符号整数，就和 Java 中的 int 一样。BIGINT：-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 的 8 字节大小的有符号整数，就和 Java 中的 long 一样。4、有损精度数值类型：FLOAT：4 字节大小的单精度浮点数值，就和 Java 中的 float 一样。DOUBLE、DOUBLE PRECISION：8 字节大小的双精度浮点数值，就和 Java 中的 double 一样。关于 FLOAT 和 DOUBLE 的区别可见 https://www.runoob.com/w3cnote/float-and-double-different.html。5、布尔类型：BOOLEAN。6、NULL 类型：NULL。7、Raw 类型：RAW('class', 'snapshot') 。只会在数据发生网络传输时进行序列化，反序列化操作，可以保留其原始数据。以 Java 举例，class 参数代表具体对应的 Java 类型，snapshot 代表类型在发生网络传输时的序列化器。8、日期、时间类型：DATE：由 年-月-日 组成的 不带时区含义 的日期类型，取值范围 [0000-01-01, 9999-12-31]TIME、TIME(p)：由 小时：分钟：秒[.小数秒] 组成的 不带时区含义 的的时间的数据类型，精度高达纳秒，取值范围 [00:00:00.000000000到23:59:59.9999999]。其中 p 代表小数秒的位数，取值范围 [0, 9]，如果不指定 p，默认为 0。TIMESTAMP、TIMESTAMP(p)、TIMESTAMP WITHOUT TIME ZONE、TIMESTAMP(p) WITHOUT TIME ZONE：由 年-月-日 小时：分钟：秒[.小数秒] 组成的 不带时区含义 的时间类型，取值范围 [0000-01-01 00:00:00.000000000, 9999-12-31 23:59:59.999999999]。其中 p 代表小数秒的位数，取值范围 [0, 9]，如果不指定 p，默认为 6。TIMESTAMP WITH TIME ZONE、TIMESTAMP(p) WITH TIME ZONE：由 年-月-日 小时：分钟：秒[.小数秒] 时区 组成的 带时区含义 的时间类型，取值范围 [0000-01-01 00:00:00.000000000 +14:59, 9999-12-31 23:59:59.999999999 -14:59]。其中 p 代表小数秒的位数，取值范围 [0, 9]，如果不指定 p，默认为 6。TIMESTAMP_LTZ、TIMESTAMP_LTZ(p)：由 年-月-日 小时：分钟：秒[.小数秒] 时区 组成的 带时区含义 的时间类型，取值范围 [0000-01-01 00:00:00.000000000 +14:59, 9999-12-31 23:59:59.999999999 -14:59]。其中 p 代表小数秒的位数，取值范围 [0, 9]，如果不指定 p，默认为 6。TIMESTAMP_LTZ 与 TIMESTAMP WITH TIME ZONE 的区别在于：TIMESTAMP WITH TIME ZONE 的时区信息是携带在数据中的，举例：其输入数据应该是 2022-01-01 00:00:00.000000000 +08:00;TIMESTAMP_LTZ 的时区信息不是携带在数据中的，而是由 Flink SQL 任务的全局配置决定的，我们可以由 table.local-time-zone 参数来设置时区。INTERVAL YEAR TO MONTH、 INTERVAL DAY TO SECOND：interval 的涉及到的种类比较多。INTERVAL 主要是用于给 TIMESTAMP、TIMESTAMP_LTZ 添加偏移量的。举例，比如给 TIMESTAMP 加、减几天、几个月、几年。二、复合数据类型数组类型：ARRAY、t ARRAY。数组最大长度为 2,147,483,647。t 代表数组内的数据类型。举例 ARRAY、ARRAY，其等同于 INT ARRAY、STRING ARRAY。Map 类型：MAP。Map 类型就和 Java 中的 Map 类型一样，key 是没有重复的。举例 Map、Map。集合类型：MULTISET、t MULTISET。就和 Java 中的 List 类型，一样，运行重复的数据。举例 MULTISET，其等同于 INT MULTISET。对象类型：ROW、ROW、ROW(n0 t0, n1 t1, ...>、ROW(n0 t0 'd0', n1 t1 'd1', ...)。就和 Java 中的自定义对象一样。举例：ROW(myField INT, myOtherField BOOLEAN)，其等同于 ROW。三、用户自定义数据类型用户自定义类型就是运行用户使用 Java 等语言自定义一个数据类型出来。但是目前数据类型不支持使用 CREATE TABLE 的 DDL 进行定义，只支持作为函数的输入输出参数。

SQL 执行流程其实一个 SQL 从输入到返回数据，其过程大致为：建立连接、分析 SQL、优化 SQL、执行 SQL。建立连接当我们发送 SQL 给 MySQL 之前，我们都会输入账号和密码，从而与 MySQL 建立连接。这部分的工作，其实就是 MySQL 的连接器处理的。连接器负责跟客户端建立连接、获取权限、维持和管理连接。当我们用管理员账号对账号权限做修改后，不影响已经存在的连接的权限，只有新建的连接才会使用新的权限设置。我们可以通过 show processlist 命令查看目前的连接情况分析 SQL在 MySQL 8.0 版本之前，MySQL 拿到一个查询请求后，会先到查询缓存中看看是否有查过。如果有，那么直接返回缓存的结果。但在 8.0 版本之后，查询缓存功能直接被删除了。主要是因为查询缓存弊大于利。因为只要对一个表进行更新，这个表上的查询缓存就会被清空。可能你刚刚把结果缓存起来了，一个更新操作一来，这些缓存就全部失效了。所以查询缓存适合那些更新不频繁的表，用来提高查询效率。当拿到 SQL 之后，MySQL 会对 SQL 进行词法分析和语法分析。词法分析会解析每个词的含义，而语法分析则是解析语法是否准确，分析器先会做词法分析，再做语法分析。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。例如：select 表示查询，t 表示 t 这个表，字符串 ID 识别成列 ID。做完词法分析之后，就会做语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足 MySQL 语法。如果不满足语法，会有「You have an error in your SQL syntax」的错误提醒。优化 SQL经过分析器，MySQL 就知道你要做什么了。但在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序。有时候两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。执行 SQLMySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。例如对于 select * from T where ID=10; 这条语句，ID 字段没有索引，那么执行器的执行流程是这样的：调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中。调用引擎接口取「下一行」，重复相同的判断逻辑，直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。第一次调用的是「取满足条件的第一行」这个接口，之后循环取「满足条件的下一行」这个接口，这些接口都是引擎中已经定义好的。你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句在执行器执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。MySQL 技术架构其实上面的过程，就是按着 MySQL 的技术架构来的，其技术架构如下图所示。大体来说，MySQL 技术架构可以分为 Server 层和存储引擎层两部分。Server 层负责建立连接、分析 SQL 等功能。 所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。存储引擎层负责数据的存储和提取。 其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的是 InnoDB 存储引擎，从 MySQL 5.5.5 开始成为了默认的存储引擎。InnoDB 存储引擎目前使用最广泛的是 InnoDB 存储引擎，其体系架构分为三大块，分别是：后台线程、内存池、文件，其体系架构如下图所示。在上图中，后台线程负责刷新内存池的数据，内存池负责缓存磁盘的数据，文件则是具体的数据存储。后台线程的主要工作是负责刷新内存池的数据，保证缓冲池中的内存缓存的是最近的数据。InnoDB 存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。目前有 4 种不同类型的处理线程，分别是：Master Tread、IO Thread、Purge Thread、Page Cleaner Thread。内存池是 InnoDB 所管理内存的统称，主要用于缓存磁盘数据，从而加快数据的读取。根据其用途不同，内存池还可以分为：缓冲池、重做日志缓冲、额外内存池三大块。文件则是最终存取数据库数据的地方，其存储了包括索引文件、数据文件等相关的数据文件。总结最后我们总结一下一条 SQL 语句从查询到返回数据的 5 个阶段，分别是：建立连接。客户端会首先与 MySQL 建立 TCP 连接，在连接器中会进行连接管理、权限验证等操作。分析 SQL。分析器进行词法、语法分析，词法分析知道要查询什么内容，语法分析判断语法是否有问题。优化 SQL。优化器根据 SQL 情况，判断使用哪种执行方式更好，例如使用哪个索引，哪种表连接方式。执行 SQL。根据优化器的优化结果，生成执行计划，执行器调用存储引擎的 API 来执行查询，最终将数据返回给客户端。

本文分享自华为云社区《[GaussDB(DWS) SQL进阶之SQL操作之聚集函数](https://bbs.huaweicloud.com/blogs/293963?utm_source=csdn&utm_medium=bbs-ex&utm_campaign=other&utm_content=content)》，作者：两杯咖啡。 聚集操作是SQL语言中除扫描、投影、连接外的另一个常用基本操作，主要用于对海量数据进行分组，然后在组内进行统计计算的场景。在AP场景下，经常面临海量数据处理的场景，而最终用户希望通过海量数据获取汇总信息，聚集操作的使用将更加广泛。本文从基本聚集操作入手，介绍常用的SQL语法，以及一些扩展的聚集功能，同时会讲到在GaussDB(DWS)里聚集相关的一些优化思路。 # 一．典型语法 SQL的聚集操作的典型语法是： ``` SELECT , , Agg_func() FROM t GROUP BY 1, 2 HAVING ; ``` 其中基本元素及概念如下： - 聚集操作子句 在SQL中，聚集操作子句通过GROUP BY实现，后面紧接聚集分组列，可以是列名，或者本层输出列的顺序号，从1开始。 - 聚集分组列 聚集分组列表明本聚集操作是以哪些列的值进行分组的，聚集分组列值均相等的元组会被划分到同一组。聚集分组列可以是一个，也可以是多个。 - 聚集函数 聚集函数即进行分组后，每组进行统计计算的函数，分为简单的和复杂的聚集函数。其中常用简单聚集函数包括以下五种： - COUNT()：用于进行分组内的计数。对于COUNT (column)，计数不包含column为NULL值的元组；对于COUNT (*)，计数包含所有元组。 - SUM()：用于计算分组内列或表达式的和，计算不包含列为NULL值的元组。 - AVG()：用于计算分组内列或表达式的平均值，AVG(col)等价于SUM(col)/ COUNT(col)（分组内存在元组）。 - MIN()：用于计算分组内列或表达式的最小值。 - MAX()：用于计算分组内列或表达式的最大值。 注： 1. 如果缺少GROUP BY且包含聚集函数，则所有元组视为一个分组。 2. 聚集函数不能嵌套。 - 聚集分组过滤条件 该条件为进行完聚集操作后，以分组为单位进行过滤的条件。聚集分组过滤条件是HAVING条件，在聚集后进行过滤，而我们通常使用的WHERE条件，需要在分组前进行过滤。 语法要求： 由于聚集操作是对聚集列进行去重分组，并进行聚集函数的分组计算，因为聚集操作的输出列和过滤条件中只能包含聚集列、聚集函数和常量，以及由它们组成的表达式。当出现非聚集列时，查询会报错。 特殊地，GaussDB(DWS)支持在主键列或唯一约束列上进行聚集的操作（尽管该操作为冗余操作），此时可以在输出列和过滤条件中包含任何列。 以TPC-H测试集的lineitem表举例说明，该表记录订单里的每种类型的零件，所属的订单号，零件所属的供应商，在订单中的序号以及价格、发货等信息。 表定义如下： ``` CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL , L_PARTKEY BIGINT NOT NULL , L_SUPPKEY BIGINT NOT NULL , L_LINENUMBER BIGINT NOT NULL , L_QUANTITY DECIMAL(15,2) NOT NULL , L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL , L_DISCOUNT DECIMAL(15,2) NOT NULL , L_TAX DECIMAL(15,2) NOT NULL , L_RETURNFLAG CHAR(1) NOT NULL , L_LINESTATUS CHAR(1) NOT NULL , L_SHIPDATE DATE NOT NULL , L_COMMITDATE DATE NOT NULL , L_RECEIPTDATE DATE NOT NULL , L_SHIPINSTRUCT CHAR(25) NOT NULL , L_SHIPMODE CHAR(10) NOT NULL , L_COMMENT VARCHAR(44) NOT NULL ) with (orientation = column) distribute by hash(L_ORDERKEY); ``` ``` SELECT MAX(l_receiptdate) FROM lineitem; -- 正确，获得所有零件的最后收货时间 SELECT SUM(l_quantity) FROM lineitem where l_orderkey=100000; -- 正确，获得订单号为100000的零件总数 SELECT l_orderkey, MAX(l_shipdate), MIN(l_shipdate) FROM lineitem GROUP BY l_orderkey; -- 正确，求每个订单的最早发货日期和最晚发货日期 SELECT l_orderkey, MAX(l_shipdate), MIN(l_shipdate) FROM lineitem GROUP BY 1; -- 正确，等价于上一条语句 SELECT l_orderkey, MAX(l_shipdate), MIN(l_shipdate) FROM lineitem GROUP BY 1 HAVING MIN(l_shipdate) ‘1999-01-01’; -- 正确，求零件最早发货日期在1999-01-01之前的，每个订单的最早和最晚的发货日期（每个零件可能单独发货） SELECT l_orderkey || ‘_’ || SUM(l_quantity), SUM(L_EXTENDEDPRICE) FROM lineitem GROUP BY l_orderkey; -- 正确，求每个订单的组合标识（订单号+零件个数），以及总价格 SELECT l_orderkey, l_partkey, AVG(l_discount) FROM lineitem GROUP BY 1; -- 错误，l_partkey不是聚集列，但出现在输出列中 ``` # 二．GaussDB(DWS)聚集执行及调优 在GaussDB(DWS)中，由于是分布式系统，数据计算应该尽量在各个DN上并行计算以得到最优的性能。因此，支持以下聚集操作计算方式： - 如果分布键是GROUP BY列的子集，此时在各个DN上分别计算，结果汇总即可。 例如：lineitem表以l_orderkey作为分布键，则聚集列包含l_orderkey的均可以在各DN执行后汇总。 - 对于不满足（1）的场景，各DN分别执行后，DN间仍然可能存在聚集列相等的数据，需要二次聚集，此时GaussDB(DWS)支持三种计算方式。 示例语句（TPC-H Q1，输出列部分省略）： ``` select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty from lineitem where l_shipdate = date '1998-12-01' - interval '90' day (3) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus; ``` 1> 各DN上进行一次聚集，将结果汇总到CN上进行二次聚集。  lineitem总共行数为59亿行。该方法中，经过DN一次聚集后，各DN输出4行数据（全局96行），这些数据汇总到CN上，由CN进行96行数据的二次聚集，最终输出6行数据。（数据信息均为估算值） 2> 选择聚集列的子集列进行重分布，回退到（1）的情况后，各DN分别聚集后进行结果汇总。  该方法中，首先按聚集的两列进行重分布，重分布数据量为59亿，然后各DN完成聚集，并将结果返回CN。 3> 各DN上进行一次聚集，然后选择聚集列的子集列进行重分布，各DN上进行二次聚集后结果汇总。  该方法中，各DN进行一次聚集，行数由59亿减少到4行，然后按聚集的两列进行重分布，各DN进行二次聚集。 可以看出，该查询适合用1>和3>的方式进行执行，因为聚集后的行数比较少，在CN上执行或重分布的数据量都不大，所以开销较小。而2>的方式要对59亿行数据进行网络重分布，网络占用较大。可以总结出三种方法的适用场景： 1> 该方法适合于一次聚集后行数较少且DN数较少的场景，这样汇聚到CN的行数较少，不会导致CN成为计算的瓶颈。 2> 相较于3>方法，该方法适合于DN一次聚集后行数缩减不明显的场景，这时可以以所有数据重分布的代价，省略DN的一次聚集操作。 3> 与2>相反，该方法适合于DN一次聚集后行数缩减明显的场景，例如上面的示例。 在GaussDB(DWS)中，以上三种方法的选择是根据代价来自动选择的，也可以通过参数best_agg_plan来强制控制选择某种方法进行执行。best_agg_plan=1, 2, 3分别对应于上述三种方法，0为默认值，表示由产品自动选择最优计划。 在单DN上执行时，GaussDB(DWS)支持以下三种算法： 1> Plain Agg：最终仅输出一行数据，适合于无聚集列的场景。 2> HashAgg：使用Hash表来进行元组的去重，首先计算聚集列的hash值，hash值相同的再进行列值的比较，避免与所有数据比较后进行去重。去重时进行聚集函数的计算。适合于聚集后行数缩减较多的场景。 3> Sort + GroupAgg：首先对数据按照聚集列进行排序，这样聚集列相等的元组均相邻，通过遍历一遍排序后的数据，即可完成元组的去重和聚集函数的计算。相较于2>，适合于聚集后行数缩减较少的场景。 以上2>和3>的方法可以通过参数enable_sort和enable_hashagg来控制（默认均为on）。当enable_hashagg=on且enable_sort=off时，优先选择2>；当enable_sort=on且enable_hashagg=off时，优先选择3>。大数据量场景，通常HashAgg可以获得较好的性能，所以GaussDB(DWS)对HashAgg进行了较深入的优化。对于个别场景选择3>的方法导致性能问题，可以通过关闭enable_sort来进行调优。 # 三．DISTINCT表达式 聚集函数中，均可以通过关键字DISTINCT对聚集列进行去重后进行计算，例如：COUNT(DISTINCT col)表示分组内col值不同的值的个数。 ``` SELECT COUNT(DISTINCT(l_partkey)) FROM lineitem GROUP BY l_returnflag, l_linestatus; -- 计算每种发货状态下的不同零件数量 ``` 在分布式环境下，为了避免l_partkey相同的值在不同的DN上导致无法去重，GaussDB(DWS)对DISTINCT类操作进行了转换，上面语句等价于： ``` SELECT COUNT(l_partkey) FROM (select l_returnflag, l_linestatus, l_partkey FROM lineitem GROUP BY l_returnflag, l_linestatus, l_partkey) GROUP BY l_returnflag, l_linestatus; ``` 这样，在GaussDB(DWS)中实际上使用两次Agg来计算DISTINCT表达式的值，计划如下：  通过计划可以看出，第8-9层为lineitem基表扫描，上面有两次Agg处理COUNT(DISTINCT)算子。第6-7行为第一次Agg，聚集列为：l_returnflag, l_linestatus, l_partkey，选择Hashagg的方法二；第3-5行为第二次Agg，聚集列为：l_returnflag, l_linestatus，选择Hashagg的方法三。 注：目前SQL标准仅支持聚集函数中出现一列，对于要求多列的COUNT(DISTINCT)，例如：COUNT(DISTINCT l_partkey, l_suppkey)，实际可以通过手动使用上述改写方式进行求解： ``` SELECT COUNT(1) FROM (select l_returnflag, l_linestatus, l_partkey, l_suppkey FROM lineitem GROUP BY l_returnflag, l_linestatus, l_partkey, l_suppkey) GROUP BY l_returnflag, l_linestatus; ``` # 四．聚集扩展功能 在SQL 1999标准中，对聚集函数进行了扩展，新增了OLAP函数ROLLUP(), CUBE(), GROUPING SETS()，用于更灵活的多维数据分组统计功能。其实，这三个函数都可以使用简单的GROUP BY的集合合并操作（UNION ALL）来实现，本文中使用UNION ALL(GROUP BY x)来替代，例如： GROUP BY a UNION ALL GROUP BY b的表达式中，x包括：(a), (b)。本文下面的讨论着重针对x进行。 - ROLLUP()是聚集列前缀的聚集结果的合并实现的，例如： ROLLUP(a, b, c)中，x包括：(a,b,c), (a,b), (a), ()。（其中GROUP BY()表示所有行聚集到一组的无GROUP BY语义），对于n个聚集列，x中包含n+1个聚集组合。 ROLLUP()中的元素可以是列的集合，例如： ROLLUP((a, b), (b, c))，x包括：(a,b,b,c)（等价于(a,b,c)）, (a,b), ()。 - CUBE()是聚集列组合的枚举的聚集结果合并实现的，例如： CUBE(a, b, c)中，x包括：(a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ()，对于n个聚集列，x中包含2^n个聚集组合。 - GROUPING SETS()是聚集列的枚举的聚集结果合并实现的，例如： GROUPING SETS(a, b, c, d)中，x包括：(a), (b), (c), (d)，对于n个聚集列，x中包含n个聚集组合。 由于OLAP函数中，并不是聚集列均出现在每一个聚集结果中，所以增加GROUPING函数来标识参数列是否参与每一行聚集结果的运算，例如：对于CUBE(a, b, c)，其中x包括：(a,b,c), (a,b), (a,c), (b,c), (a), (b), (c), ()时，对于x为(a,b,c), (a,b), (a,c), (a)的聚集结果行，GROUPING(a)的值为0，其它为1。 对于包含OLAP函数的如下语句： ``` select l_returnflag, l_linestatus, l_shipmode, sum(l_extendedprice), grouping(l_returnflag) from lineitem group by cube(1,2,3) order by 1,2,3; ``` GaussDB(DWS)的计划如下：  目前GaussDB(DWS)中使用Sort+GroupAgg来实现OLAP函数，后续版本会支持HashAgg进行执行，提高性能。 # 五．总结 聚集操作是SQL语言中的基本操作，只有深入了解聚集操作的语法、语义和支持的功能范围，才能更灵活地驾驭灵活的SQL语言进行开发，为学习更高阶的SQL语言打下良好的基础。

>摘要：几乎所有涉及应用数据交互的场景都可以通过DCM来改善应用结构，提升开发与计算效率。 本文分享自华为云社区《[DCM：中间件家族迎来新成员](https://bbs.huaweicloud.com/blogs/354299?utm_source=csdn&utm_medium=bbs-ex&utm_campaign=other&utm_content=content)》，作者： 石臻臻的杂货铺。 # DCM是什么 现代应用无时无刻不在与数据打交道，数据计算无处不在，报表统计、数据分析、业务处理不一而足。当前数据处理的主要手段仍然是以关系数据库为代表的相关技术，虽然使用高级语言（如Java）硬编码也能实现各类计算，但远不如数据库（SQL）方便，数据库在当代数据处理中仍然发挥举足轻重的作用。 不过，随着信息技术的发展，存储与计算分离、微服务、前置计算、边缘计算等架构与概念的兴起，过于沉重、封闭的数据库在应对这些场景时越来越显得捉襟见肘。数据库要求数据入库才能计算，但面对丰富的多样数据源时，数据入库不仅效率低资源消耗大，实时性也无法保障，而有的数据只是临时使用却要入库持久化就更得不偿失了。另外对于微服务、边缘计算等需要将计算能力前置到应用端的场景，数据库也很难嵌入使用。 在这样的背景下，如果有一种不依赖数据库、具备开放计算能力、能够与应用嵌入集成使用的数据计算处理技术，那么这些问题就都能够很好地解决，这就是数据计算中间件（Data Computing Middleware，简称DCM）。DCM的应用场景非常广泛，可以说无处不在，在优化应用开发、微服务实现、存储过程替代、数据库解耦、ETL辅助、多样性数据源计算、BI数据准备等等多方面都能发挥重要的作用，几乎所有涉及应用数据交互的场景都可以通过DCM来改善应用结构，提升开发与计算效率。 # DCM应用场景 ## 优化应用开发 应用中数据处理逻辑只能通过编码实现，使用原生的Java实现由于缺少必要的结构化计算类库往往比较困难，即使用新增加的Stream/Kotlin也并没有明显改善。借助ORM技术可以一定程度缓解开发困境，但仍然缺乏专业的结构化数据类型，集合运算不够方便，同时读写数据库时代码繁琐，复杂计算也难以实现。ORM的这些缺点经常导致业务逻辑的开发效率不仅没有明显提升，甚至还大幅降低。此外，这些实现方式还会导致应用结构问题。Java实现的计算逻辑必须与主应用一起部署导致紧耦合，同时由于不支持热部署开发运维也很麻烦。 如果借助DCM的敏捷计算、易集成、热切换等特性，在应用中替代Java实现数据处理逻辑，就可以很好解决上述问题，不仅开发效率提升，还可以优化应用结构，实现计算模块的解耦，同时支持热部署。  ## 多样性数据源计算 现代应用还经常面临多样性数据源问题，通过数据库处理不仅需要数据入库，效率低下，还无法保障数据的实时性。不同数据源有各自的优势，RDB计算能力较强，但IO吞吐能力弱；NoSQL的IO效率高，但计算能力很弱；而文本等文件数据完全没有计算能力，但使用非常灵活。强迫这些数据入库就会丧失这些原数据源的优势。 通过DCM的多源混算能力，不仅可以直接对RDB、文本、Excel、JSON、XML、NoSQL以及其他网络接口数据进行混合计算，保证数据与计算的实时性，而且还能同时保留各类数据源的优点，充分发挥其效力。  ## 微服务实现 当前微服务实现时仍然大量依赖Java和数据库实施数据处理，Java的缺点在于实现复杂、无法热切换；而数据库由于有“库”的限制，多源数据要入库才能计算，灵活性很低，不仅数据时效性无法保证，也无法充分发挥各类数据源的优势。 将可集成的DCM分别嵌入中台或微服务的各个环节完成数据采集整理、数据处理以及前置的数据计算任务，利用开放的计算体系可以充分发挥多数据源自身的优势，灵活性增强。多源数据处理、实时计算、热部署这些问题均能迎刃而解。  ## 存储过程替代 以往为了实现复杂计算或整理数据常常会使用存储过程，存储过程在库内计算有一定优势，但缺点也很明显。存储过程缺乏可移植性，编辑调试困难，创建和使用存储过程需要较高权限存在安全问题，为前端应用服务的存储过程还会造成数据库与应用紧耦合。 通过DCM将存储过程外置到应用中，可以实现“库外存储过程”，数据库则主要用于存储，将存储过程从数据库中解耦出来就可以很好解决存储过程带来的各类问题。  ## 报表BI数据准备 为报表提供数据准备是DCM的重要场景，以往使用数据库为报表准备数据存在实现难度高、耦合性强等问题，而报表本身计算能力不足又无法完成很多复杂计算。通过DCM的库外强计算能力就可以为报表提供一个专门的数据计算层，不仅可以解耦数据库为数据库减负，还可以弥补报表工具自身的计算能力不足。逻辑上分层后，报表开发维护都很清爽。  ## 中间表消除 有时为了加快查询效率事先将要查询的数据加工成结果表存储在数据库中，这就是中间表。另外，有些复杂计算需要保存中间结果也会存成中间表；多样数据源也要先存成中间表才能在数据库中混合计算。与存储过程类似，中间表一旦建立就可能被多个应用（模块）使用，造成应用与数据库的紧耦合，同时由于中间表无法轻易删除，数量会越积越多。中间表数量过多会引发数据库容量和性能问题，存储中间表需要空间，加工中间表则需要数据库计算资源。 通过DCM可以将中间表外置到文件系统，利用DCM实施计算，解耦数据库减轻数据库存储和计算负担。这里的关键是DCM使得文件也拥有了计算能力，所以才能将库内的中间表置于库外，原来中间表放在库内主要为了获得数据库的计算能力，现在有DCM的计算能力中间表存成什么形式就不重要了，外置到文件系统反而更优。  ## T+0查询 数据量积累到一定程度时基于生产库查询会影响交易，这时就会将大量的历史数据剥离到其他历史数据库中，进行冷热数据分离。这时如果要查询全量数据就要完成跨库查询、冷热数据路由等工作。数据库对于跨库查询尤其是跨异构库存在很多问题，不仅效率低下，还存在数据传输不稳定、可扩展性低等很多不足，无法很好实现T+0全量数据查询。 而这些问题都可以通过DCM来解决，由于具备独立且完善的计算能力，可以分别从不同的数据库取数计算，因此可以很好适应异构数据库的情况，还可以根据数据库的资源状况决定计算是在数据库还是DCM中实施，非常灵活。在计算实现上，DCM的敏捷计算能力还可以简化T+0查询中的复杂计算，提升开发效率。  ## ETL ETL需要对数据清洗转换再加载到目标端，但由于源端数据可能来源多处（文本、数据库、web）加上数据质量参差不齐，因此E和T这两个步骤会涉及大量数据计算。目前除了数据库以外，其他数据源并不太具备这样的计算能力，想要完成这些计算就要先加载到数据库再进行，这就形成了LET。大量无用的数据存储在数据库中会占用大量存储空间，极易引发容量问题。而将清洗和转换的计算工作都压给数据库又会增加数据处理时间，再叠加大量未经清洗转换的原始数据入库时间，有限的ETL时间窗口很可能不够，如果无法在规定时间完成ETL工作就会影响第二天的业务。 在ETL任务中引入DCM就可以按顺序完成清洗E、转换T、加载L，解决LET面临的各种问题。借助DCM的开放计算能力，在库外对多源数据实施清洗转换，DCM拥有强计算能力可以应对各类复杂计算，最后将整理后数据装在到目标端，实现真正的ETL。  # DCM特性 可以看到，DCM的应用场景非常广泛。那么要很好应对这些场景，一个优秀的DCM应该具备哪些特点呢？ ## 兼容性（Compatible） 首先DCM需要具备很好的兼容性，可以跨平台使用，各类操作系统、云平台、应用服务器下均可以很好运行，这决定了DCM的使用范围。 此外，兼容性还意味着可以兼容多样性数据源，无论何种数据源都可以直接使用并进行混合计算，这要求DCM拥有足够强的开放性。 ## 热部署（Hot-deploy） 数据处理是一种高频且稳定性较差的场景，在业务开展过程中经常要新增修改计算任务，这就要求DCM应该具备热部署特性，修改数据处理逻辑无需重启应用（服务）就能生效。 ## 高性能（Efficient） 计算性能是数据计算场景重点关注的方面，有时会成为最主要的关注点，所谓天下武功无快不破。DCM应该能够高效处理数据，提供诸如高性能计算库、高性能存方案、并行计算等高性能保障机制。 ## 敏捷性（Agile） 敏捷性要求DCM能够快速实现数据处理逻辑，具备完备的计算能力，尤其面对复杂计算场景通过足够简单的编码就能完成数据处理，同时可以高效运行。这需要DCM提供敏捷编程机制和易于使用的开发环境等支持。 ## 扩展性（Scalable） 当计算容量无法满足需要时，DCM应该具备灵活的横向扩展能力。扩展性对当代应用十分重要，扩展能力的好坏决定了DCM的上限。 ## 集成性（Embeddable） DCM应该能够很好与应用集成嵌入使用，在应用内充当计算引擎，作为应用的一部分随应用一起打包部署。这样应用本身就获得了强计算能力，不再强依赖数据库后，可以很好应对存储与计算分离、微服务和边缘计算等场景。并且，良好的集成性还是敏捷性的另一方面体现，DCM很轻，随时随地都能嵌入与应用结合使用。 如果将DCM这几个特性的首字母组合起来，与CHEESE（奶酪）很接近（CHEASE），而DCM的作用就像夹在汉堡里的奶酪一样，如果缺少，味道和营养都会差很多。  这样能否作为理想的DCM就可以使用CHEASE的标准去考察。这里不妨看一下一些主流技术对DCM的满足情况。 # 现有技术的情况 ## SQL 数据库是使用SQL的主要阵地，数据库通常具备较强的计算能力，一些头部数据库的计算性能也很强，基本可以满足高性能（E）的需要。而且数据库过于封闭，数据要入库才能计算，无法很好满足多样性数据源场景的需要，兼容性（C）较差。 对于集成性（E），由于绝大部分数据库都是独立使用的，极少数（如SQLite）支持嵌入的数据库往往功能和性能都达不到要求，因此数据库几乎不满足集成性的要求。 而SQL作为专用的集合计算语言，实现简单计算很方便，但复杂计算用SQL表达很繁琐，经常要嵌套多层，实际业务中经常能看到几千行的“长”SQL，不仅难写，维护也不方便，所以SQL不太符合敏捷性（A）的要求。 与数据库类似的Hadoop相关技术也存在同样的问题，封闭性导致兼容性差、敏捷性不足、基本不具备集成性等缺点，虽然在扩展性方面表现要优于数据库，但总体并不符合DCM的要求。Spark的表现要略好，但Scala不支持热部署，实现复杂计算也不够方便，而Spark SQL仍然存在SQL的那些问题。这些技术都过于沉重，很难满足DCM在敏捷性、集成性、热部署等方面的需要。 ## Java Java作为原生的编程语言可以很好跨平台运行，也可以通过编码完成多数据源计算任务，因此兼容性（C）很好。而且对于大部分都采用Java开发的应用来说，集成性（E）也不在话下。 但Java的缺点也很明显，作为编译型语言无法实现热部署（H）。由于缺少必要的结构化计算类库完成简单的分组汇总也要几十行代码，就别提复杂计算了。虽然现在微服务架构中也经常使用Java硬编码完成数据处理，但其实计算实现要比SQL复杂得多，没办法，计算前置就不能再用数据库，难写也得挺着，因此敏捷性（A）极其不足。虽然在Java8以后引入了Stream，但计算能力并没有实质改善（Kotlin也存在类似的问题）。 使用Java虽然理论上也能实现各类高性能算法，但是如果只是为某个应用/项目服务，要实现这些高性能算法封装投入就太大了，因此从实际应用角度来看，Java并不具备高性能（E）特性。扩展性（S）也存在同样的问题。因此综合来看，Java很难作为优秀的DCM技术使用。 ## Python Python作为大火的一类计算技术不得不提一下。Python的兼容性（C）较强，无论是跨平台还是对接多数据源都能支持。尤其是丰富的数据处理包让Python的适用范围极广。 Python在结构化数据处理相比于Java等技术有相当的优势，但却难说很完善，尤其在处理有序分组等复杂计算时会很绕，Python在敏捷性（A）上略有所欠缺。 不仅如此，Pandas的性能（E）也往往达不到要求，尤其针对大数据量计算方面，这跟算法的实现效率有很大关系，敏捷语法可以很方便地实现高性能算法，反之就很困难。同样，在扩展性（S）方面，Python也不尽如人意，本质上来讲作为编程语言的Python要拥有良好的扩展性需要投入大量资源开发完成，这点与Java是一样的。 Python最大的问题是集成性（E），很难与现有应用集成在一起使用。虽然可以通过诸如sidecar模式进行服务间调用，但本质上与DCM要求与应用结合嵌入在一起（同一个进程）相去甚远。Python的主要应用场景并非像Java一样做企业级应用开发，各有用途，勉强不来。归根到底，专业的事儿还需要专业的工具来做。 # 专业数据计算中间件SPL 开源集算器SPL是专业的数据计算中间件，具备不依赖数据库的完备计算能力，同时开放的计算能力可以混合计算多样性数据，同时解释执行的SPL天然支持热部署，良好的集成性可以很方便嵌入应用中，让应用拥有强计算能力，充分发挥DCM的效力。 ## 兼容性 SPL采用Java开发，跨平台能力与Java一致，可以很好运行在各类操作系统、云平台下。而在多数据源支持方面，SPL具备开放的计算能力，可以对接多种数据源，RDB、NoSQL、CSV、Excel、JSON/XML、Hadoop、RESTful、Webservice都可以直接对接并进行混合计算，不需要入库，数据实时性和计算实时性都可以很好保障。  多源计算支持很好解决了原来数据库无法跨源计算、无法计算外部数据的问题，再加上SPL完备的计算能力和相对SQL更简洁的语法，对于应用来说就获得了与数据库相当（超过）的计算能力。 除了原生计算语法，SPL还提供了SQL支持（相当SQL92标准），可以使用SQL查询文本、Excel、NoSQL等非RDB数据源，这样就极大方便了熟悉SQL的应用开发人员。  DCM只有在开放计算体系的支持下才能拥有足够强的兼容性，才能适应更多的应用场景。 ## 热部署 SPL采用解释执行机制，天然支持热部署。这样对于一些稳定性差经常需要新增、修改计算逻辑的业务（如报表、微服务）非常友好。  ## 高性能 在性能方面，SPL提供了诸多高性能算法与高性能存储机制。在前面提到的DCM消除中间表和ETL场景中，数据往往要落地成文件存储在数据库外，这时采用SPL的文件格式存储可以获得比文本等开放格式高很多的性能。 SPL提供了两种存储类型：集文件和组表。集文件采用了压缩技术（占用空间更小读取更快），存储了数据类型（无需解析数据类型读取更快），支持可追加数据的倍增分段机制，利用分段策略很容易实现并行计算，保证计算性能。组表支持列式存储，在参与计算的列数（字段）较少时会有巨大优势。组表上还实现了minmax索引，同时支持倍增分段，这样不仅能享受到列存的优势，也更容易并行提升计算性能。 SPL还支持各种高性能算法。比如常见的TopN运算，在SPL中TopN被理解为聚合运算，这样可以将高复杂度的排序转换成低复杂度的聚合运算，而且很还能扩展应用范围。  这里的语句中没有排序字样，也不会产生大排序的动作，在全集还是分组中计算TopN的语法基本一致，而且都会有较高的性能，类似的算法在SPL中还有很多。 SPL也很容易实施并行计算，发挥多CPU的优势。SPL有很多计算函数都提供并行机制，如文件读取、过滤、排序只要增加一个@m选项就可以自动实施并行计算，简单方便。同时也可以显示编写并行程序，通过多线程并行提升计算性能。  ## 敏捷性 SPL提供了原生的计算语法和简洁易用的IDE环境，在IDE中不仅可以很方便编码调试，过程计算的每步计算结果都可以实时查看，网格式编码代码天然整齐，通过格子名称引用中间计算结果无需定义变量，简单方便。  同时，基于SPL丰富的计算类库实施结构化数据计算更方便，分组汇总、循环、过滤、集合运算、有序计算等应有尽有。  SPL尤其擅长复杂计算，原来SQL要嵌套很多层的计算使用SPL却可以很方便实现。比如根据股票记录计算某只股票最长连续上涨多少天？SPL就比SQL简单很多。  上面SQL嵌套了3层，读起来都很绕就别提写了；下面的SPL完全按照自然思维、简单3行就能实现，高下立判。 良好的敏捷性不仅能提升开发效率，很多高性能算法通过SPL可以很方便实现。算法不仅要能想出来，还要能实现，最好实现还简单，SPL提供了这种可能。 ## 扩展性 对于计算性能要求较高的场景，SPL还可以部署单独的计算服务，同时支持多机分布式集群，支持负载均衡和容错机制，当计算资源达到上限时可以通过横向扩容增加算力，具备良好的扩展性。 在分布式计算中，用户可根据数据和计算任务的特点灵活定制数据分布及冗余方案，有效减少节点间数据传输量，以获得更高性能，实现可控数据分布。 SPL采用无中心集群设计，集群没有永久的中心主控节点，允许程序员用代码控制参与计算的节点，从而有效避免单点失效。同时SPL会根据每个节点空闲程度（线程数量）决定是否分配任务，实现负担和资源的有效平衡。 在容错方面，SPL提供内外存两种数据容错机制，外存冗余式容错和内存备胎式容错。支持计算容错，节点故障时自动将该节点计算任务迁移掉其他节点继续完成。 ## 集成性 作为DCM与应用结合方面，SPL提供了标准JDBC/ODBC/RESTful接口，应用可以像调用存储过程一样请求SPL计算结果。  逻辑上SPL作为DCM介于应用和数据源之间实施数据处理，对上提供计算服务，对下屏蔽多样性数据源差异，充分彰显了DCM的重要作用。 JDBC调用SPL 代码示例： ``` Class.forName("com.esproc.jdbc.InternalDriver"); Connection conn =DriverManager.getConnection("jdbc:esproc:local://"); CallableStatement st = conn.prepareCall("{call splscript(?, ?)}"); st.setObject(1, 3000); st.setObject(2, 5000); ResultSet result=st.execute(); ``` 综合起来，从DCM的6个特性（CHEASE）来看，SPL在各方面能力综合起来十分均衡，整体远优于其他技术，是DCM的理想选择。  # SPL资料 - SPL官网 - SPL下载 - [SPL源代码](https://github.com/SPLWare/esProc)

作为一名后端程序员，可以说天天都要跟数据库打交道，不管使用的是 MySQL， Oracle 还是 SQL Server，毫无疑问都逃不开 SQL，所以日常工作中对于 SQL 的性能优化可谓说十分重要。今天阿粉就带大家看一下，每个后端程序员都应该知道的十个提升查询性能的技巧。1、使用 Exists 代替子查询子查询在日常的工作中不可避免一定会使用到，很多时候我们的用法都是这样的：SELECT Id, Name FROM Employee WHERE DeptId In (SELECT Id FROM Department WHERE Name like '%Management%');相信大家平常肯定都是这样来使用的，其实还有一种更好的方法，如下所示：SELECT Id, Name FROM Employee WHERE DeptId Exist (SELECT Id FROM Department WHERE Name like '%Management%');这里我们使用 exist 关键字而不是 In 关键字，当然如果在数据量不大的时候，两种方式都可以，但是当数据量很大的时候，exist 的方式会比 in 的方式效率高很多。因为 Exist 函数根据查询结果返回一个布尔值，速度会快很多2、适当的使用 JOIN 来代替子查询除了上面的exist 之外在有些场景我们可以使用 JOIN 来替换子查询，毕竟子查询的效果是很差的，如下所示：SELECT Id, Name FROM Employee WHERE DeptId in (SELECT Id FROM Department WHERE Name like '%Management%');使用 JOIN 的方式如下：SELECT Emp.Id, Emp.Name,Dept.DeptName FROM Employee Emp RIGHT JOIN Department Dept on Emp.DeptId = Dept.Id WHERE Dept.DeptName like '%Management%';3、使用 Where 替代不必要的Having对于 where 的使用相信大家都很擅长，但是对于 Having 的使用可能平时用的不多，阿粉这里只能说：用得不多，挺好的!对于 Having 我们是能不用就不用不到万不得已的时候不要用，说真的阿粉工作这么多年，真没有使用 Having 的场景。我们先看下面的示例：Having 的用法SELECT Emp.Id, Emp.Name,Dept.DeptName,Emp.Salary FROM Employee Emp RIGHT JOIN Department Dept on Emp.DeptId = Dept.Id GROUP BY dept.DeptName HAVING Emp.Salary >= 20000;Where 的用法SELECT Emp.Id, Emp.Name,Dept.DeptName,Emp.Salary FROM Employee Emp RIGHT JOIN Department Dept on Emp.DeptId = Dept.Id WHERE Emp.Salary >= 20000;为什么说 Having 的性能没有 Where 高呢?那是因为 Where 是一种精确的匹配，但是 Having 是需要配合 Group By 来配合使用，只要涉及到 Group By 自然就效率高不起来了。4、使用精确的字段类型有些小伙伴为了系统的可扩展性或者压根就不知道该把数据库字段的类型设置什么，所以就全部使用 char 或者 varchar，总觉得这样更灵活，但是往往这个时候是对系统的最大隐患。在使用时间类型的字段的时候，就需要设置成 DateTime，不能用 varchar;在使用标识是否删除的时候就应该使用 tinyint，能用 varchar 的就不要用 char;对于大字段 text 需要独立出来，这样在查询的时候就不会影响性能;对于能设置成唯一键的就需要设置成唯一键，因为你永远无法避免程序会出现脏数据，要在数据层保证一致性。5、使用批处理代替循环在插入数据的时候的，我们可以使用 values 来批量进行插入，而不是通过循环来进行单条数据的查询，如下所示：//不可取 For(Int i = 0;i <= 5; i++) { INSER INTO Table1(Id,Value) Values( i , 'Value' + i ); } //推荐 INSERT INTO Table1(Id, Value) Values(1,Value1),(2,Value2),(2,Value3),(4,Value4),(5,Value5);不过要注意 values 后面的数量也是有限制的，所以两者可以结合使用，具体的可以根据表字段的多少来决定分多少批来执行。另外这里有一个注意的点，很多系统都会底层做操作日志，而且很多时候可能是 SQL 级别的，那这个时候就需要注意，记录操作日志的表的字段是有长度限制的，这里整个 SQL 的长度是不能超过日志字段的长度的。6、使用 UNION ALL 替代 UNION在使用联合查询的时候，很多时候我们会使用到 UNION ALL 或者 UNION 来联合多个表，进行汇总。那么 UNION ALL 和 UNION 的区别是什么呢?这两个的区别是 UNION ALL 会返回联合后的所有行记录，而 UNION 是会进行去重后返回。7、用精确的字段代替 *另一个比较影响性能的点是使用 *，很多小伙伴为了省事，在编写查询语句的时候，会使用 * 来代替所有的字段，其实并不是说这种写法有什么问题，只是这种写法有点不可控，使用 * 表示要查询所有字段，当我们的表是一个很简单的表，而且里面的字段都是一些小字段的时候，使用 * 完全是可以的。但是如果是对于一些大表特别是有 text 这种大字段的表，或者是一些敏感数据的表，我们还使用 * 号去查询数据的话，就会有很大的问题了，一方面是有安全隐患，一方面还是增加磁盘，内存和网络的传输，完全得不偿失。8、给必要的字段增加索引索引作为数据库里面一个很重要的内容，相比大家都不陌生，给必要的字段加上索引也是很有必要的，除了主键索引，我们还可以添加聚簇索引和唯一索引。总结后端程序员除了跟服务器打交道之外最多的就是跟数据库打交道了，如何在数据库层面提效也是一个长久的话题，这也是为什么数据库能得到发展的原因，从关系型数据库到 NoSQL 数据库，从 MySQL 到 ClickHouse，数据库行业也在长久的发展。

前言SQL程序语言有四种类型，对数据库的基本操作都属于这四类，它们分别为;数据定义语言(DDL)、数据查询语言(DQL)、数据操纵语言(DML)、数据控制语言(DCL)数据定义语言(DDL)DDL全称是Data Definition Language，即数据定义语言，定义语言就是定义关系模式、删除关系、修改关系模式以及创建数据库中的各种对象，比如表、聚簇、索引、视图、函数、存储过程和触发器等等。数据定义语言是由SQL语言集中负责数据结构定义与数据库对象定义的语言，并且由CREATE、ALTER、DROP和TRUNCATE四个语法组成。比如：--创建一个student表 create table student( id int identity(1,1) not null, name varchar(20) null, course varchar(20) null, grade numeric null )--student表增加一个年龄字段 alter table student add age int NULL--student表删除年龄字段，删除的字段前面需要加column，不然会报错，而添加字段不需要加column alter table student drop Column age --删除student表 drop table student --删除表的数据和表的结构 truncate table student -- 只是清空表的数据，，但并不删除表的结构，student表还在只是数据为空数据操纵语言(DML)数据操纵语言全程是Data Manipulation Language，主要是进行插入元组、删除元组、修改元组的操作。主要有insert、update、delete语法组成。--向student表中插入数据 --数据库插入数据 一次性插入多行多列 格式为INSERT INTO table (字段1, 字段2,字段3) VALUES (值1,值2,值3),(值1,值2,值3),...; INSERT INTO student (name, course,grade) VALUES ('张飞','语文',90),('刘备','数学',70),('关羽','历史',25),('张云','英语',13);--更新关羽的成绩 update student set grade='18' where name='关羽'--关羽因为历史成绩太低，要退学，所以删除关羽这个学生 delete from student where name='关羽'数据查询语言(DQL)数据查询语言全称是Data Query Language，所以是用来进行数据库中数据的查询的，即最常用的select语句--从student表中查询所有的数据 select * from student--从student表中查询姓名为张飞的学生 select * from student where name='张飞'数据控制语言(DCL)数据控制语言：Data Control Language。用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视。比如常见的授权、取消授权、回滚、提交等等操作。1、创建用户语法结构：CREATE USER 用户名@地址 IDENTIFIED BY '密码'; --创建一个testuser用户，密码111111 create user testuser@localhost identified by '111111';2、给用户授权语法结构：GRANT 权限1, … , 权限n ON 数据库.对象 TO 用户名; --将test数据库中所有对象(表、视图、存储过程，触发器等。*表示所有对象)的create,alter,drop,insert,update,delete,select赋给testuser用户 grant create,alter,drop,insert,update,delete,select on test.* to testuser@localhost;3、撤销授权语法结构：REVOKE权限1, … , 权限n ON 数据库.对象 FORM 用户名; --将test数据库中所有对象的create,alter,drop权限撤销 revoke create,alter,drop on test.* to testuser@localhost;4、查看用户权限语法结构： SHOW GRANTS FOR 用户名; --查看testuser的用户权限 show grants for testuser@localhost;5、删除用户语法结构：DROP USER 用户名; --删除testuser用户 drop user testuser@localhost;6、修改用户密码语法结构：USE mysql; UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’; FLUSH PRIVILEGES; --将testuser的密码改为123456 update user set password=password('123456') where user='testuser' and host=’localhost’; FLUSH PRIVILEGES;结尾本文对SQL程序语言有四种操作语言做了一个简单的介绍和概括，对数据库的基本操作都属于这四类，它们分别为;数据定义语言(DDL)、数据查询语言(DQL)、数据操纵语言(DML)、数据控制语言(DCL) 。

本文分享自华为云社区《[GaussDB(DWS) SQL进阶之PLSQL（二）-游标](https://bbs.huaweicloud.com/blogs/330535?utm_source=csdn&utm_medium=bbs-ex&utm_campaign=ei&utm_content=content)》，作者： xxxsql123 。 # 前言 游标是一种数据处理方法，提供了在查询结果集中进行逐行遍历浏览数据的方法，也可以将游标当做上下文区域的句柄或者指针，借助游标对指定位置的数据进行查询与处理，本章我们主要聚焦于GaussDB(DWS)存储过程中的游标使用。 # 显式游标 显示游标主要用于处理存储过程中的查询结果集是游标常用的用法，具体分为如下几个步骤： # Step 1 定义游标： ## 静态游标定义： 即定义一个游标名以及与其相对应的SELECT语句 语法图：  示例如下： ``` --在存储过程的DECLARE中声明游标定义 CURSOR C1 IS SELECT section_name, place_id FROM hr.sections WHERE section_id = 50; CURSOR C2(sect_id INTEGER) IS SELECT section_name, place_id FROM hr.sections WHERE section_id = sect_id; ``` ## 动态游标定义： 即ref游标，可以通过静态的SQL语句在合适的时候动态的打开游标。先定义ref游标类型，后面通过open for动态绑定SELECT语句 语法图：  示例如下： ``` --在存储过程的DECLARE中声明游标定义 TYPE CURSOR_TYPE IS REF CURSOR; ``` 同时GaussDB(DWS)做了Oracle兼容，支持sys_refcursor动态游标类型，函数或存储过程可以通过sys_refcursor参数传入或传出游标结果集合，函数也可以通过返回sys_refcursor来返回游标结果集合。 语法图：  示例如下： ``` --在存储过程的DECLARE中声明游标定义 C1 SYS_REFCURSOR; ``` # Step 2 打开游标： ## 静态游标打开： 即执行游标对应的SELECT语句，将结果集放入工作区，将游标的指针指向工作区的起始位置。 语法图：  示例如下： ``` --在存储过程的BODY中打开游标 OPEN C1; OPEN C2(10); ``` ## 动态游标打开： 通过OPEN FOR语句打开动态游标，通过USING对SELECT语句进行动态绑定。 语法图：  示例如下： ``` --在存储过程的BODY中打开游标 SQL_STR := 'SELECT section_name, place_id FROM hr.sections WHERE section_id = :DEPT_NO;'; OPEN C3 FOR SQL_STR USING 50; ``` # Step 3 提取游标数据： 即提取游标指针指向的数据 语法图：  示例： ``` --在存储过程的BODY中执行 FETCH C3 INTO DEPT_NAME, DEPT_LOC; ``` # Step 4 循环处理游标数据： 提取数据后可以基于存储过程的语句灵活发挥 例如，给工资低于3000的员工增加500块钱工资 ``` --在存储过程的BODY中执行 LOOP FETCH C INTO V_EMPNO, V_SAL; EXIT WHEN C%NOTFOUND; IF V_SAL=3000 THEN UPDATE hr.staffs_t1 SET salary =salary + 500 WHERE staff_id = V_EMPNO; END IF; END LOOP; ``` # Step 5 关闭游标： 在处理完游标的数据后，应及时释放游标，以便释放游标所占用系统资源，游标关闭后工作区将变成无效，不能再使用FETCH语句获取其中数据。关闭后的游标可以使用OPEN语句重新打开。 语法图：  ``` --在存储过程的BODY中执行 CLOSE C1;--关闭游标 ``` # 游标属性 我们可以通过游标的属性来了解当前游标的状态。下面将介绍4中游标属性： ※ %FOUND布尔型属性：当最近一次读记录时成功返回，则值为TRUE。 ※ %NOTFOUND布尔型属性：与%FOUND相反。 ※ %ISOPEN布尔型属性：当游标已打开时返回TRUE。 ※ %ROWCOUNT数值型属性：返回已从游标中读取的记录数。 示例： ``` OPEN C1;--打开游标 LOOP --通过游标取值 FETCH C1 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC); END LOOP; CLOSE C1;--关闭游标 ``` 接下来我们将结合前面所学习的知识，在存储过程运用显示游标。 数据准备： ``` CREATE SCHEMA hr; SET CURRENT_SCHEMA = 'hr'; DROP TABLE IF EXISTS sections; CREATE TABLE sections(section_id INT, section_name VARCHAR(100), place_id NUMBER(4)) DISTRIBUTE BY HASH(section_id); INSERT INTO sections VALUES (1, 'section_name1', 1),(2, 'section_name2', 2),(3, 'section_name3', 3); ``` 显示游标使用示例： ``` --游标参数的传递方法。 CREATE OR REPLACE PROCEDURE cursor_proc1() AS DECLARE DEPT_NAME VARCHAR(100); DEPT_LOC NUMBER(4); --定义游标 CURSOR C1 IS SELECT section_name, place_id FROM hr.sections WHERE section_id = 50; CURSOR C2(sect_id INTEGER) IS SELECT section_name, place_id FROM hr.sections WHERE section_id = sect_id; TYPE CURSOR_TYPE IS REF CURSOR; C3 CURSOR_TYPE; SQL_STR VARCHAR(100); BEGIN OPEN C1;--打开游标 LOOP --通过游标取值 FETCH C1 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC); END LOOP; CLOSE C1;--关闭游标 OPEN C2(10); LOOP FETCH C2 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C2%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC); END LOOP; CLOSE C2; SQL_STR := 'SELECT section_name, place_id FROM hr.sections WHERE section_id = :DEPT_NO;'; OPEN C3 FOR SQL_STR USING 50; LOOP FETCH C3 INTO DEPT_NAME, DEPT_LOC; EXIT WHEN C3%NOTFOUND; DBMS_OUTPUT.PUT_LINE(DEPT_NAME||'---'||DEPT_LOC); END LOOP; CLOSE C3; END; / CALL cursor_proc1(); DROP PROCEDURE cursor_proc1; ``` 执行结果： ``` postgres=# CALL cursor_proc1(); section_name3---3 section_name1---1 section_name2---2 section_name1---1 section_name2---2 section_name3---3 section_name1---1 section_name2---2 section_name3---3 cursor_proc1 -------------- (1 row) ``` SYS_REFCURSOR游标示例： ``` --SYS_REFCURSOR类型做为函数参数 CREATE OR REPLACE PROCEDURE proc_sys_ref(O OUT SYS_REFCURSOR) IS C1 SYS_REFCURSOR; BEGIN OPEN C1 FOR SELECT section_ID FROM HR.sections ORDER BY section_ID; O := C1; END; / DECLARE C1 SYS_REFCURSOR; TEMP NUMBER(4); BEGIN proc_sys_ref(C1); LOOP FETCH C1 INTO TEMP; DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT); EXIT WHEN C1%NOTFOUND; END LOOP; END; / --删除存储过程 DROP PROCEDURE proc_sys_ref; ``` 执行结果： ``` postgres=# DECLARE postgres-# C1 SYS_REFCURSOR; postgres-# TEMP NUMBER(4); postgres-# BEGIN postgres$# proc_sys_ref(C1); postgres$# LOOP postgres$# FETCH C1 INTO TEMP; postgres$# DBMS_OUTPUT.PUT_LINE(C1%ROWCOUNT); postgres$# EXIT WHEN C1%NOTFOUND; postgres$# END LOOP; postgres$# END; postgres$# / 1 2 3 3 ANONYMOUS BLOCK EXECUTE ``` # 隐式游标 对于非SELECT语句，例如UPDATE，DELETE操作，系统会自动的未这些操作设置游标，这些有系统隐含创建的游标即隐式游标。隐式游标的定义，打开，取值，关闭操作均有系统自动的完成，无需用户进行处理，用户只能通过隐式游标的相关属性完成相应的操作。 隐式游标属性： ※ SQL%FOUND布尔型属性：当最近一次读记录时成功返回，则值为TRUE。 ※ SQL%NOTFOUND布尔型属性：与%FOUND相反。 ※ SQL%ROWCOUNT数值型属性：返回已从游标中读取得记录数。 ※ SQL%ISOPEN布尔型属性：取值总是FALSE。SQL语句执行完毕立即关闭隐式游标。 隐式游标示例如下： ``` --删除EMP表中某部门的所有员工，如果该部门中已没有员工，则在DEPT表中删除该部门。 CREATE TABLE hr.staffs_t1 AS TABLE hr.staffs; CREATE TABLE hr.sections_t1 AS TABLE hr.sections; CREATE OR REPLACE PROCEDURE proc_cursor3() AS DECLARE V_DEPTNO NUMBER(4) := 100; BEGIN DELETE FROM hr.staffs WHERE section_ID = V_DEPTNO; --根据游标状态做进一步处理 IF SQL%NOTFOUND THEN DELETE FROM hr.sections_t1 WHERE section_ID = V_DEPTNO; END IF; END; / CALL proc_cursor3(); --删除存储过程和临时表 DROP PROCEDURE proc_cursor3; DROP TABLE hr.staffs_t1; DROP TABLE hr.sections_t1; ``` 以上就是在GuassDB(DWS)的存储过程中游标的基本使用。 # 总结 GuassDB(DWS)的游标使用在postgresql的基础上做了对Oracle的语法兼容，存储过程中的游标功能对于原来依赖Oracle的系统可以平滑的迁移。同时由于GuassDB(DWS)是分布式架构，和postgresql本身以及GuassDB(DWS)的单机模式上游标的行为细节上会略有不同，例如事务中的DECLARE CURSOR由于分布式和单机的实现差异导致在pg_cursors视图查询结果差异等。