在KunlunBase中使用高级SQL功能
在KunlunBase中使用高级SQL功能
KunlunBase支持丰富的高级SQL功能，包括视图、物化视图、触发器、存储过程、domain、CHECK约束、Row Level Security(RLS)、多层级多粒度的访问控制 等。KunlunBase的这些功能继承自PostgreSQL，同时经过我们扩展和增强，让它们在KunlunBase分布式数据库系统中可以继续正常工作。
这些高级SQL功能在经典数据库时代就已经存在，在现在的分布式数据库时代，这些功能还有用吗？对数据库系统性能有什么影响？有没有更好的替代方案？这应该是有一定数据库使用经验的技术人员会考虑的问题。下面我们就介绍一下每一种功能的使用场景，以及在KunlunBase中这些功能的价值和利弊。
访问控制
在这个信息化智能化时代，数据被广泛比喻为黄金，所以数据库就是金库。只有在数据的源头也就是数据库系统中定义了精确的访问控制规则(Access Control Rules, ACR)，确保只有对特定的数据有相关操作权限的用户才可以把数据从DBMS中取出来，或者对数据做增删改操作。这样才能确保数据安全。如果在数据库系统之外实现ACR，就仿佛幻想打开金库的大门，让黄金被任何人随意取出金库后还能不丢失，那是非常幼稚和不负责任的。因此，管理数据的访问控制规则是一个专业DBA的关键任务。
KunlunBase继承了PostgreSQL全套访问控制系统，包括角色(Role)和用户(user)的管理，连接授权(pg_hba.conf)，ACR的管理和执行等，并且其功能和用法与PostgreSQL完全相同。所以本文只着重介绍容易被用户忽略的几个功能点。
KunlunBase支持多层级多粒度的访问控制，支持在database（库），schema（模式），table（表），row（行）， column（列） ，domain（域，可以理解为列类型），sequence（序列）这些粒度设置ACR。ACR中可以管控所有针对特定类型的数据库对象有意义的操作类型。
对于每一个在计算节点创建的用户，KunlunBase在存储节点中也会创建相同名称的用户，并且使用与当前登录用户同名的用户名连接存储节点。因此每个用户创建的表分片在存储节点上的所有者是操作时的登录用户名。尽管存储节点也具备访问控制功能，但是KunlunBase的访问控制检查是在计算节点执行的，因为执行一个SQL语句时，只有先通过了访问控制检查才会进入到查询优化和执行阶段。KunlunBase的存储节点仍然需要验证用户名和密码才能连接上去，除非特殊情况DBA不需要直接连接到存储节点上，应用软件的用户名更是绝不应该直接连接到存储节点（并且应用软件使用其在计算节点创建的用户名及其密码无法连接到存储节点），否则不仅绕过了计算节点的访问控制机制，而且用户对数据表做过ALTER的话，计算节点是不知道的，这就造成了严重的元数据不匹配问题。
KunlunBase还支持在视图、物化视图和存储过程定义ACR。通过视图可以针对多个表的特定行列的组合数据定义非常精细和灵活的ACR，这部分在视图一节介绍。通过对存储过程定义ACR，ACR管控的操作类型从增删改查这种基础操作类型，扩展到一组基础操作以用户定义的方式组成的整体，同时ACR管控的数据也由过程逻辑定义，可以做到最大程度的灵活性和可定制性。
Row Level Security
KunlunBase的行(row)级的ACR是基于Row Level Security, RLS这个功能来实现的。RLS结合GRANT语句的列级ACR，让用户可以在最细粒度也就是字段级别来定义访问控制规则。用户需要执行CREATE POLICY来定义特定用户可以针对特定表的哪些行执行特定操作（SELECT, INSERT,UPDATE,DELETE）。这个‘哪些行’是一个表达式，引用这个表的若干个列，使用每一行的各个引用的列的对应字段计算出一个bool值。当该值为true时，该用户才能对这一行做该规则指定的操作。可以为一个表定义多个POLICY来分别管控多个用户针对该表的每一种操作的权限。
RLS会对增删改查性能造成轻微的影响，得到的是非常细粒度的数据访问控制能力和数据安全保障。在实践中可以对个别含有关键数据的数据表创建POLICY，比如，人员信息表中的工资列，这一列应该是DBA账号也无法看到的，只有人力资源主管等高管可以看到全体人员的工资，部门经理可以看到本部门人员的工资，员工个人可以看到自己的工资。供应商的协议价格，产品针对特定客户的协议价格和其他定制细节，对很多公司来说是一个高度保密信息，这些信息是DBA无权看到的，只有专管的高管的账号可以看到。这才能充分保障公司机密信息。
同时，应用软件需要对那些有特定高级权限的用户，用他们各自专属的数据库账号来连接数据库，而不是用统一的数据库账号来连接数据库。统一的数据库账号只具备最基本的权限，不可以看到任何需要控制访问权限的数据。
Domain
KunlunBase和PostgreSQL中的Domain（域）就是关系代数中的domain的概念，它相当于是一种列类型。Domain同时带有列的数据类型、缺省值和列的约束（包括是否可NULL和CHECK约束）。所以用CREATE DOMAIN定义了一个domain之后，就可以用这个domain作为任意表的列类型来使用，只要是符合业务逻辑的。可以用ALTER DOMAIN修改一个domain的定义，不过不能修改其数据类型。有意义的修改主要是缺省值和约束。修改之后，所有使用这个domain的表的相应的列都发生了修改。
约束
一个表的常规的数据有效性约束包括数据类型、主键(primary key)、唯一性(uniqueness)、是否可空（nullability）这些通用规则，它们能够在一定程度规范数据有效性，但是它们无法具体规定一个表每一行一个或者多个字段的合法字段值范围和约束关系。只有使用CHECK约束才可以做到这一点，所以CHECK约束对于应用系统正确地实现业务逻辑是极其重要的。
必须在数据源头也就是数据库系统中定义数据有效性验证规则，而不是依赖应用软件层做这样的校验和检查，否则在实践中就一定会有非法数据进入数据库系统从而影响应用系统正常工作。之所以如此，是因为随着应用软件持续的迭代、扩展，那些数据有效性规则可能在最初的系统设计中被严格遵守，而到了后来则可能随着开发人员的更迭和技术文档缺失而被遗漏。
非法数据就像毒药和污染物一样，会污染有效数据，影响应用系统稳定性，甚至对数据分析决策产生误导。非法数据进入数据库后，原有的业务逻辑可能发生无法预料的异常行为，影响系统的稳定性甚至导致其不能正确工作。这是因为使用这些数据的模块根据业务需求来处理数据，并不会预期到会有这些非法数据。所以数据库的数据有效性校验机制就像过滤器一样，把有毒的、非法的、错误的数据过滤出去，确保数据库中存储的都是有意义的合法数据。
CHECK约束
在CREATE TABLE语句中定义CHECK约束，可以在INSERT和UPDATE每一行时检查该行的一个或者多个字段符合用户定义的有效性约束规则。不符合这个表的有效性规则的INSERT/UPDATE语句会执行出错被回滚掉，这就杜绝了非法数据进入数据库。
外键
在经典数据库时代，外键是一种常用的约束，不过外键的引用完整性规则对于系统的INSERT，UPDATE,DELETE的性能有较大的影响。而在分布式数据库系统中，这种性能开销就更加巨大了 --- 表t1的一行所引用的表t2的行可能在另一个节点上，这就让引用完整性检查变的非常昂贵，因此KunlunBase不支持外键。这是KunlunBase的唯一一个不支持的常用经典SQL高级功能。
外键最初之所以有必要，很重要的原因是Edgar Codd最初设计关系模型时设想的是把SQL作为用户直接操作数据库中的数据的语言而存在的，也就是SQL由人来输入，就像那个时代使用计算机就是在敲命令进去执行一样。这就容易引发人为误操作导致的错误。而在当今实际的应用系统中，数据库都是作为应用软件的后台服务器而运行的，SQL是由应用软件开发人员撰写或者ORM中间件自动生成的，然后由应用软件发送给数据库的，这就把人为错误排除在了实际运行期间之外。开发调试期间这类错误就被解决了。
同时应用操作逻辑也完全可以正确地定制化实现符合需求的引用完整性规则。如果要删除t2的一行，那么引用它的t1中的行可以由应用软件显式删除，或者保留，或者设置为NULL或者其他值。这样更安全更灵活，也可以防止级联删除导致的数据意外丢失。
给MySQL用户的特殊忠告 --- 使用适当的数据类型
MySQL直到8.0.16版本才正式支持了CHECK约束，在此之前用户定义的CHECK约束直接被解析器忽略了。CHECK约束的缺失曾经给MySQL应用开发带来了极大的隐患，依赖应用软件做数据有效性检查非常容易导致有效性规则被无意甚至有意绕过。所以强烈建议MySQL用户尽快养成在CREATE TABLE语句中设置有效性检查规则的良好习惯。
MySQL支持非常灵活的数据类型转换，这种灵活性也是一种诅咒，让一些初级MySQL用户失去了对数据类型的理解，导致数据类型的约束性、有效性验证、比较方法等功能都失效或者被滥用了。
数据类型本身就是一种最基本的数据有效性约束，它规定了合法数据的范围和可以执行的操作，特别是做比较的方法和数据排列顺序。数据类型之间也并不是都可以转换的，比如把日期、时间、时间戳与数值之间互转通常没什么意义，但是MySQL竟然是支持的，并且其转换方法也很有想象力 --- 比如’2023-07-16’ 这个日期值与20230716这个整数是互转的。
这种任意的数据类型转换不仅很容易导致非法值出乎意料地进入数据表，而且容易导致索引工作不正常或者不符合预期，比如找不到本应该在表中的值，常见于范围查找；或者本应该使用到索引但是实际上没有用到等问题。
比如有的用户习惯把表的所有数值类型定义为字符串，这样的问题是你要做范围查找的时候，返回的结果其实是错误的，因为数据按照字符串来比较。除非你在字符串表示的数值左边补0，但是那样你就要么面临数值溢出风险因为预定的宽度较小，要么就是占用空间比数值类型大很多倍。同时，字符串比较的CPU开销也远大于NUMERIC/DECIMAL以外的数值类型比较。
有的用户习惯把时间戳列也定义为字符串或者datetime类型，这就导致时间戳类型的时区信息无法生效，无法对不同时区的用户显示当地时区的值。同时把任何date, time, datetime, timestamp列定义为字符串类型都会导致范围查找出错，数据显示格式混杂，本地化(l10n)和国际化(i18n)失效，非法值进入数据表等等问题，并且占用的存储空间远大于使用正确类型。
最后再说一下字符串类型的字符集和collation属性。MySQL支持在表级和列级指定charset和collation，这本来是一个非常灵活的功能，但同时对很多用户也是个巨大的坑。在实际的应用中，很多用户经常因为这种灵活性而出现由于两个列的charset, collation不匹配而导致的问题。比如表t1.a和t2.b都是varchar(64)类型并且有UNIQUE约束，但是二者的collation不同，然后查询语句中出现WHERE t1.a = t2.b AND t2.b=’xxx’ 这样的条件，然后你EXPLAIN发现竟然对t1做了全表扫描，而不是使用t1.a的唯一索引，导致性能极差。另一类问题是collation转换操作带来的性能问题，如果同一个语句中对大量行的字段做collation转换操作的话，其性能开销也很可观。
所以，最好在一个database范围内统一使用UTF8MB4。UTF8MB4这个字符集包含了人类所有语言的文字，以及emoji表情包，真的非常全面了，不需要使用其他字符集了。有的国内用户习惯使用GB2312/GB18030/GBK等字符集，但是这些字符集不包含很多外文文字，如果有一天你的业务可以出海了，你会发现海外用户的输入数据无法在你的系统中正确使用。所以还是建议统一使用UTF8MB4。
视图和物化视图
视图(view)是一种非常有用的高级SQL功能，它把业务关心的数据在若干个表的存储细节，与数据的逻辑意义和应用场景分开，类似于一种数据接口。如果数据表的存储结构发生了调整，那么只需要相应修改视图定义即可，应用层的SQL通常不需要修改。对应用系统开发者来说，从视图中查询数据更加直观，通常不再需要写多表连接语句，也不需要构造投影表达式，这些已经在视图定义中定义好了。通常查询视图只需要加一个过滤条件即可。
KunlunBase支持对视图定义访问控制规则，也就是数据访问控制规则根据数据的应用场景和逻辑意义来定义，更加直观和精准。所以一个设计良好的应用系统，应该有一个视图层，应用开发者几乎总是查询某个视图来获取数据，极少需要直接查询基础表。
物化视图是在视图基础上，把视图查询结果数据存储在一个数据表文件中，这样如果反复查询一个视图，那么可以避免反复执行视图定义（通常比较复杂）中的查询语句，从而获得更好的查询性能。不过物化视图缓存的结果会随着相关基表被持续更新而逐渐老化过时，所以在创建好物化视图之后，需要定期执行REFRESH MATERIALIZED VIEW语句来更新缓存的数据。
在KunlunBase中，物化视图的数据存储在存储节点中，计算节点重放(replay)一个CREATE MATERIALIZED VIEW语句不会再次刷新其中的数据，而REFRESH MATERIALIZED VIEW语句不会进入DDL日志，也就是说计算节点做DDL重放不会重复刷新物化视图导致性能问题。
触发器
触发器（trigger）是让用户定义针对一个表的每一行或者符合过滤条件的行（行级trigger）做插入、更新、删除操作之前或者之后，以及针对一个表执行插入、更新、删除语句（语句级trigger）之前和之后，定制需要执行的操作。
那么，为什么不在应用代码中做这些操作呢？对于行级trigger，还真的无法在数据库外部精准地抓住这种时机来做这样的定制化操作，除非整个数据更新逻辑实现成一个存储过程，在其中用cursor扫过符合条件的行，在增删改之前和之后做相应的行级定制操作；所以，行级trigger是有一定的使用价值的。KunlunBase支持行级trigger。
而语句级trigger，本来是完全可以在应用代码中做的，只要在其之前或之后在同一个事务中执行即可。不过考虑应用软件的维护性和生命周期，在实践中很多应用软件经过一些年之后已经很难维护了，那么如果需要增加这些定制操作，又无法修改应用代码，那么增加语句级trigger来扩充语句的行为就是一个还不错的补救和给应用软件延寿的手段了，DBA可以不依赖与应用软件供应商的版本周期随时按需来做，比如增补一些审计操作。
性能开销
无论是行级还是语句级trigger，都增加了查询处理的工作量，并且有可能因此在无意中严重影响查询语句的性能。经典数据库时代，数据库系统无法水平扩容，这种影响就更加严重而且难解，只能换一台计算能力更强也昂贵得多的服务器。对于KunlunBase分布式数据库来说，可以按需增加计算节点来增加计算能力，因此触发器和存储过程带来的性能开销和压力可以通过增加计算节点来完美解决。
行级触发器的替代方案
对于一些需求来说，使用行级trigger的方案或许可以用事后处理数据更新事件流来解决。对于MySQL来说就是处理binlog流；KunlunBase CDC功能可以输出数据变更流供外部插件消费。这种做法是目前比较普遍的，也可以使用到相关的工具链。
存储过程
存储过程可以让用户定义一组操作，然后调用它即可，其好处与在软件开发语言代码中定义函数和调用函数是类似的。存储过程避免了在数据库服务器和应用服务器之间传递数据的网络延时和网络带宽消耗，数据只在数据库集群内部被操作，有时可以达到比较好的性能。
存储过程可以实现更好和更灵活的数据访问控制，敏感数据经过存储过程的计算和处理之后，调用方只得到最终处理后的结果，可以做到涉密的敏感的数据完全不出库。可以定义用户和角色对存储过程的执行权限，来确保授权操作。
不过如果存储过程中有较重的计算负载，那么其实际执行性能是比较堪忧的，特别是对比于目前大多数常用编程语言。此时计算时耗可能会大于数据传输时耗，导致性能反而较低。而且存储过程的调试也比较麻烦，因为没有调试器帮忙，因此开发维护成本比较大。
存储过程对于维护旧系统也有比较好的效果，对于调用存储过程实现的功能逻辑，理论上在应用开发商无法快速更新应用系统的情况下，最终用户还有机会通过更新存储过程和其他相关数据库对象比如表、事务、触发器等来部分更新应用软件。
存储过程与触发器有类似的性能开销问题和扩展性问题，不过对于KunlunBase来说解决方案也是相同的---只要增加更多的计算节点即可。
KunlunBase继承了PostgreSQL强大的存储过程功能，不仅支持PL/SQL 写存储过程，也支持使用python, perl 等语言写存储过程。而且PostgreSQL社区还有lua, java, javascript 等语言的存储过程插件，装上之后就可以用这些语言写存储过程，并且这些语言的执行效率比PL/SQL高，从而避免了PL/SQL存储过程的性能弱点，而且有非常丰富的函数库和类库，比如python的机器学习和数据分析库，这样就可以用KunlunBase的数据节点作为数据处理节点，其优势至少包括两方面：数据不需要离开KunlunBase就可以实现数据分析和隐私计算，还可以按需增加KunlunBase的计算节点来增大数据分析和处理能力，实现计算能力的水平扩容。
总结
对于本文所述的SQL高级功能，我们的观点是除了外键之外，其他功能都是有一定实用价值的，特别是多层级多粒度的访问控制，以及除了外键之外的各种约束，还有视图，物化视图等功能，对于设计和实现优秀的应用软件系统非常有价值；
特别是，要准确地使用数据类型，并且认真考虑好NULL-ability和uniqueness，这两点常常容易被遗漏。如果用ALTER TABLE修改一个列的nullability，或者修改数据类型即使相同基类型改宽了（int改为bigint），都是需要全表copy数据的。如果可以在建表时定义好CHECK约束那么就非常好了，对于理解应用需求和业务逻辑的架构师来说，这是可以做到的。后期修改就可能会面临处理表中已有的非法数据的问题，这些后期补救工作通常会因为维护困难而最终放弃，这就会影响应用系统的稳定性、易用性和可维护性。
推荐把存储过程用于数据分析场景，这样可以利用python等语言的丰富的数据分析和机器学习资源库，快速开发相关功能，并且在避免数据出库的情况下就完成分析和计算任务，这样可以形成快速的迭代。 而且用户可以利用KunlunBase的计算节点的水平扩容能力，按需增加计算节点，所以分析计算任务不会遇到性能瓶颈。DBA可以为数据分析工作者单独分配一组计算节点，在避免影响事务处理负载的情况下完成数据分析。
触发器和存储过程要酌情而定，并且很多时候或许这两个功能已经不是实现相关功能逻辑的最优技术路线，更多可以作为兼容老旧的应用系统的迂回方案来使用。