UniqueKey:让你的查询跑的更快-阿里云开发者社区

开发者社区> KB小秘书> 正文

UniqueKey:让你的查询跑的更快

简介: UniqueKey:让你的查询跑的更快
+关注继续查看

执行计划的好坏对于查询的性能起着至关重要的影响,那么一个执行计划的生成都和哪些因素相关? 内容简要 1. 什么是Uniquekey 2. 进一步识别UniqueKey 3. UniqueKey的应用和实践 UniqueKey是在一个结果集中能够唯一确定一条记录的一组表达式,对于一个复杂的查询,这个结果集通常是执行到一定阶段的中间结果集。 比如说有一个5张表连接,第一个阶段是每一个结果的单个结果集,在这个阶段每一张表都有自己的UniqueKey,当第一张表和第二张表建立之后,建立的结果又有一个怎样的UniqueKey?前三张表建立之后它的UniqueKey是怎么样的,整个过程中都可以判断它的UniqueKey是什么样,基于这些信息,优化器可以做出更加明智的决断! ![image.png](https://ucc.alicdn.com/pic/developer-ecology/e5c4ac27a28f446cb20fea457269dfdc.png)

UniqueKey!=Unique Index

 

UniqueKey和Unique Index有很强的关联性,通常UniqueKey的起点就是Unique Index,但它们是不同的,首先从简单的例子看起:

image.png

这种场景是UniqueKey的最简单的场景,那么a是唯一的UniqueKey吗?

答案是否定的,因为唯一索引允许有多个空值存在。A并不是唯一的,因为Unique Index允许多个NULL。排除NULL的因素即可。

 

如何去保证一个Unique Index出来的结果是不包含空的:

第一种场景是在定义表结构的时候,列上面就有非空约束,就可以很清楚的判断它里边一定不会有空值;第二种判断会根据用户输入的查询,就是在表里它可能会存在多个空值,但是对于这个结果集里边a它一定不会有空值存在;第三种Select a from t where a in一个子查询。如果a是一个空a in子查询,一定也不会返回True。所以a它也一定不是是空的;第四种就会不那么直观,它是Select a from t where func(a)大于10,并且函数它的Isstrict的属性是True,Isstrict的属性True它表达的意思是如果它的输入参数a是一个空,那么它的返回值一定也是空,那么它的返回值就一定不会大于10。

也可以简单的说,唯一索引再加上一个非空约束,最终就可以转变为一个唯一键。例如下图所示:

image.png

再看一个组合索引的情况,如下图所示:

image.png

至此为止讨论的都是唯一键和单表查询,实际上更复杂的场景来自于表连接。来看一个两张表连接的例子,如下图:

image.png

对于这样的一个结果集,它里边是没有唯一键的,那么对于一个表查询来说,它的唯一键在哪些场景下面是可以继承下来的呢?其他场景会产生UniqueKey的例子,如下图:

image.png

在现实场景中的UniqueKey会隐藏在一个复杂查询中的一部分。需要在一个执行计划的产生过程中,在任何一个阶段准确判断出UniqueKey的属性,以便优化器产生更优的执行计划。

image.png

UniqueKey的用途

 

唯一键的用途?

第一减少半连接;

第二减少无用连接;

第三可以去除一些不需要的Distinct操作。

 

 

1.减少半连接  如下图所示:

image.png

image.png

这里的t2有100万条数据,要把这100万条数据全部扫描一遍,根据执行情况,可以看到它执行的时间是372毫秒。

image.png

整个过程中只对于t整个循环了10次,而不是原来的100万次,它的执行时间也从原来的370多毫秒下降到了0.235毫秒。

但为什么前面的就不能这样做,后面的这个就可以这样做呢?

原因在于 a它是一个唯一键,而 b不是唯一键,在 a是一个唯一键的场景下面,可以做一些查询的改写。

 

半连接的转化过程如下:

本来是Select from t2 wehre b in(Select a from t )它可以将这个半连接转换成一个正常的Inner Join;Select t2. from t2,t where t2.b=t.a,这里的t2.b=t.a,a又是一个UniqueKey通过这样的教育,并不会让原来的t2的一条结果变成多条结果,所以这个语义还是相等的。 当把它们转变成一个正常的连接以后,优化器就会同时去看,拿着t2去教育t生成一些执行计划,也会拿着t去教育t2去生成另外的一批执行计划。

最后优化器会认为拿着t去教育t2可以得到一个更好的执行计划,也就是前面看到的,扫描外表只有10行,整个过程中只需要循环10次即可达到目的。

这里查询的改写依赖于 a它是一个唯一键。

image.png

2.减少无用连接:

最终出现的这样的一个查询的逻辑分析的一个结果,就是在这种场景下面,t2里边的任何的一个结果集都不会被重复,并且每一条数据都应该被返回,并且都不会被重复,这个结构可以直接去扫描一下,t2跟t1没有任何的关系。

最终可以看到对于 Left Join这样的操作,产生的一个执行计划,仅仅是对t2进行了一个全表扫描和 t没有任何的关系。

 

那么用户为什么会写一个这样的查询?会写出这种没有用的连接语句呢?

 

实际上这是一个使用场景,假设有一个表,它有很多的属性,这些属性全部放到一起,表就会非常的大,而这个时候可以把一个宽表拆分成很多的窄表,然后拆分成窄表以后,如果是只去访问其中的个别列的话,其实是访访问了一个更小的结果集,它的性能就会更好。

但有一样是不变的,就是说每个人如果想去获得更多的列,他就要去写很多不同的Join,这个时候其实有一种做法,创建一个视图,把所有的表全部通过Left键把它给连接起来,这个时候用户看到的是一个像宽表一样的,它只有去查询这个视图,去检索它需要的量。如果说它只检索很少的量,优化器就会直接通过这项技术把那些无关的表的Left那些Join全部给去掉,性能也会变得很好,用户也会非常的方便。在PG内部的优化视图里边,内置的一些视图里边很多都采用了这样的技术。

image.png

Join会使得一方的结果集过滤掉或者一行变多行。

.Left Join保证了左表的数据一定不会被过滤掉。

.Join右表的条件又是UniqueKey,所以左表的数据一定不会被重复。

.最终只需要扫描一遍t2即可,完全忽略掉t1.

实用场景:将一个宽表拆分面多个窄表,然后通过left join来创建视图,应用按需检索列。

 

消除无用的Distinst操作

正常的 Distinst的操作,Select Distinst a c d首先要得到一个结果集,然后对结果集进行一次去除,如果它的数据量是很大的,这个去除它本身也可能要花费很长的时间,但是如果说可以判断出这个结果集已经是唯一的了,去重操作是可以不做的。看右边的查询,它是Select Disstind b c d那么a本来就是唯一的,加上c、d,它自然还是唯一的Distinst的操作。可以完全忽略这样的操作,省掉了一个代价很高的节点。

这样一个功能的意义在现实生活中,在平时开发应用程序的时候,有可能也会进行很多的表连接,当创建完表连接以后,其实也很难判断它到底是不是唯一的,这个时候就会去加一个Distinct的操作,同时有一些框架也比较适合去加这种Distinct的操作。

 

 

如果数据库的内核识别不了这些唯一键,它就必须要去生成一个去除的节点,可能要消耗很大的资源,但是如果说能够识别这样的一个信息,这个节点就可以直接去除,从而达到很好的性能。如下图所示:

 image.png

阿里云在今年1月份的时候提交了一个Patch, Patch可以更加系统化的去识别,在一个复杂阶段的UniqueKey,它的唯一键是什么?

相对提供了一个非常完整的监测机制,同时又增强了一个Distinct的操作。将UniqueKey用于Distinct的这种场景下边。这个功能有望会在PGConf和入到PGConf14这个版本里边。

image.png

PolarDB(兼容PG/Oracle语法)

特点:

-计算存储分离

-计算节点点瞬间扩容

-存储节点按量收费

-高度兼容Oracle语法

image.png

它是一个一写多读的架构,但和传统的主备不同,它采用的是计算存储分离的架构,并且底层是一个共享的文件系统Polar Store。

这种架构的好处是计算存储分离,计算节点可以做到瞬间的扩容,并且存储节点也可以按量付费。

在语法层面,我们高度兼容Oracle语法,用户可以轻易的从Oracle数据库迁移到Polar Store,在读节点和写节点之上,还有一个MaxSQL节点,用户只需要连接MaxSQL节点,就会根据当时的准备延迟情况,将请求转发给读写节点或者只读节点,从而充分的利用硬件资源。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle索引或这类索引的分区处于不可用状态 查询
ORA-01502: 索引或这类索引的分区处于不可用状态 原因: 出现这个问题,可能有人move过表,或者disable 过索引。 1. alter table xxxxxx move tablespace xxxxxxx 命令后,索引就会失效。
1463 0
Oracle 查询对应表所有字段名称,可排除不想要的字段
由于Oracle varchar类型长度限制为4000 ,以下方法仅支持查询字段拼接后长度小于等于4000 第一种方法 select wm_concat(column_name) from user_tab_cols where table_name = upper(...
1066 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10493 0
gdb可以查询执行文件的宏, 但是查询不了o文件的宏
在gcc使用-g3编译的时候, gdb可以查看对应c语言的宏. ``` gdb a.out -ex 'list main' -ex 'info macro XXXX' -ex 'q' Defined at /xvdc/w.c:6 #define XXXX ppppppppppppp ``` 但是o文件却看不了对应的宏, ``` gdb w.o -ex 'list ma
1276 0
Oracle11g select查询时候输出未选定行
解决方法是: 查询的表名是否是大写的; 是否没有提交执行结果:可以commit一下;
1136 0
DNS查询竟然可以黑掉Systemd
本文讲的是DNS查询竟然可以黑掉Systemd,Systemd是Linux社区最钟爱的基石,但可能会被恶意DNS服务器劫持或弄崩溃。补丁已出,受影响用户应尽快安装。
1343 0
带你读《GraphQL学习指南》之三:GraphQL查询语言
本书为希望开始使用GraphQL的前端Web开发人员、后端工程师以及项目或产品经理提供了一条清晰的学习路径。书中将先后探索图论、图数据结构和GraphQL类型,之后在实际项目中学习如何为照片共享应用构建schema。本书还介绍了Apollo Client,可用来将GraphQL连接到用户界面。
1349 0
Cassandra 2.1 数据查询语法。
1,官方文档,基本类型 数据查询语言文档: http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/update_r.html cql 支持的数据类型: 相对于 MySQL,有几个类型比较有意思,uuid类型,map,list,set类型,这个优化关联查询,直接将List存入一条记录。 CQL Type Cons
1678 0
+关注
1090
文章
1253
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载