PG+MySQL第4课

分享人:Digoal 阿里云资深数据库专家

正文:

本篇内容将通过三个部分来介绍PG+MySQL第4课。

一、数据类型

二、常用语法

三、约束和索引

PG+MySQL第4课


一、数据类型


我们先来讲一讲数据类型,我们分了几类最常用的数据类型。首先是Mysql里的数值类型。整型是固定长度,在Mysql里有一个字节到八个字节的整型。并且里面无符号的定义。然后zerofilll是,如果在创建类型的时候,未达到长度,它会在左侧填零。在pg里,对应的类型是两字节,四字节,八字节。它没有一个字节,三个字节,同时它也没有无符号的整型。你可以直接用大于等于它的取值范围类型,然后加一个check。numeric叫做精确数据类型,你可以指定它的整数位和小数位,在Mysql里最大支持65个数字。在pg里,有13万个整数,16383个小数。浮点也是一个固定长度,它是近似值的数据类型。序列,在Mysql里叫auto increment的指定字段。我们在Mysql里面写数据时,如果不指定它的值,它就会从一开始自增,每次加一。在pg里,序列指定类型叫serial2,serial4,serial8。它会默认自动创建序列,并指定字段的默认值。

PG+MySQL第4课

接下来,是和时间相关的数据类型。首先是data类型,在Mysql里是一个日期。它是没有时间戳。在pg里,只有时间,没有日期。在pg里,有一个类型叫timetz,你可以在时间里面存一个时区。datetime的话呢跟timestamp是两个一样的类型。在pg里都叫time stamp。time stamp又有日期又有时间类型。在Mysql里,有一个类型叫做year。它的取值范围是1901~2155年。

PG+MySQL第4课

关于字符串类型,分为固定长度,可变长度以及超大长度。固定长度是char,可变长度是varchar。假如,你设了一个char100。即使它不到100,也会在末尾什帮你填空格。varchar单纯存入,输入的字符长度。在pg里,它支持的长度更大。chra最多能支持10485760个字符,varchar最大不能超过1G的字节。在Mysql里,char存255个字符,varchar存64k字节。

binary和varbinary在mysql里,输入和存储都用字节流。在pg里,对应bytea,最大是1GB。在mysql里,它的长度跟varchar一样受限。blob和text是叫做文本数据类型。在Mysql里分为2^8,2^16,2^24,<2^32字节。在pg里,blob对应的是bytea,最大1GB。enum在pg里最大有1GB,在Mysql里是64k。set是一个去重的集合。我们可以直接用数组来代替它。

PG+MySQL第4课

接下来,我们看看空间类型。因为Mysql支持空间类型较晚,它的功能相对来比较有限。在pg里,有geography,点云模型,raster三格模型和top模型。

PG+MySQL第4课

JSON在pg跟Mysql里的用法基本一样,在一些函数上会些许的差别。在pg里,多了一个叫json b。json跟json b的差别在哪里呢?在pg里,json输入的格式和存储格式一样。json b可以将输入如格式,转化成json格式存储。它能支持更多的索引,操作符,json pass。所以我们建议在pg里用jsonb。

PG+MySQL第4课

除了以上的常用类型,PG还有很多额外类型。内置类型包括:varbit,货币,interval,平面几何,网络,全文检索等等。扩展类型包括:树类型,多维类型,高维向量类型,图像特征值,化学分子等等。这些额外类型在物流,医药,科研等各行各业都有广泛的应用。

PG+MySQL第4课

函数操作,针对不同的类型,都有对应的函数去操作。Mysql和pg对应的函数,大家可以在在使用过程中,直接查阅手册。

PG+MySQL第4课

当数据量特别大的时候,我们会建议做分区。因为每一个子分区较小,在子分区上索引,垃圾回收的消时较短。hash分区通常用在用户表,基于用户id做查询。range分区通常用在历史表或者日志表。List分区在历史表或者日志表中,可枚举值的字段。当有些记录不在分区范围内,我们可以创建默认分区,然后分级,根据多个维度查询。

PG+MySQL第4课


二、常用语法


常用的语法,我们举了一些例子,包括limit,类型转换,事务隔离级别,2PC,upsert等等。类型转换,在pg和Mysql中都是一样的。隐式commit在Mysql

里,DDL都会默认未完成的事务。在pg里,DDL是支持事务。所以大家在使用的时候一定要注意,pg里面commit不做隐式。desc用来去查看表的结构。在pg里,它可以查看任意对象的内容,包括视图,物化视图,序列等等。\ddescribed的缩写。

PG+MySQL第4课

存储过程,函数,触发器在pg里面的功能会更加的丰富。存储过程函数debug,event trigger,function我们都会在日常用到。

PG+MySQL第4课


三、约束和索引


接下来,我们讲一讲约束。pg相比Mysql,支持更多的约束。check在Mysql

里不强制约束,你可以设置construct。在pg里,check强制某一个字段的值必须要大于等于零。在pg里还有exclude约束,通常用在范围类型用和空间类型。当你的数据存进去时,它会根据空间索引判断,测绘时是否出现重叠交叉的问题。可以去防止有污染的数据进来。只要写进来的数据和已有数据冲突,就有范围交叉重叠,它就会报错。

PG+MySQL第4课

关于两个数据库的索引,Mysql里有btree,invert,fulltext,multi-value等索引,除此之外还有表达式索引,它只能用在普通索引,不能用在空间索引。pg支持的索引包括btree,hash,gin,gist等八种索引。除此之外,它支持表达式索引,排他索引。其中任何索引都能支持表达式索引。

PG+MySQL第4课

接下来,针对索引简单讲几个pg特有的。full text search,即全文检索功能。它可以通过包含,相交来操作。根据文本结构,进行存储分类。如果内置分词不能满足你需求,你可以通过自定义分词,上传词条数据。索引支持倒排,分词+其他。

PG+MySQL第4课

特征化过程,又名分词过程。把一个文本转换成特征向量。对吧?首先是parse解析,解析之后生成很多token。token有两个属性,即归类和位置。通过配置不同的类别,会映射到不同的字典里。数据库会扫描字典,token通过字典匹配之后,它会转换成别名,存到这个tsvector的类型里。

PG+MySQL第4课

我们在搜索的时候,通过tsquery查询某一些特征值,包含与否。然后指定对应的位置和类别,最后用rank计算排列。

PG+MySQL第4课

当倒排索引使用fastupdate,就可以快速写入数据。利用list+tree合并查询的方式,就可以实现数据的实时查询。当倒排索引里有大量,没有合并到倒牌数里的内容,查询就会变慢。这个时候,只需要合并数据就好了。倒排索引的用途很多,比如正则查询,全文检索,json查询,数组,任意字段组合的查询等等。通常用在分析全文检索。

PG+MySQL第4课

接下来,我们聊聊空间索引。Mysql只支持包含geohash,prefix search的索引,比如geohash编码,btree索引。Pg支持搜索种类多,支持专业GIS处理。包括平面、立体、栅格、点云、轨迹、路径规划等等。Pg实现了空间+非空间+多值列的组合过滤。

PG+MySQL第4课

上图是Mysql和PG的参考资料,大家如果在学习过程中遇到问题,也可以在相关网站查询。

 

 

 

上一篇:Directx11教程(44) alpha blend(1)


下一篇:.NET Core实战项目之CMS 第六章 入门篇-Vue的快速入门及其使用