PostgreSQL vs. MS SQL Server 已翻译 100%

oschina 投递于 2014/11/17 14:44 (共 71 段, 翻译完成于 12-01)
阅读 42957
收藏 176
14
加载中

从一个数据分析师的视角来对比两个关系型数据库。

0.本文是关于什么的?

我在一个全球专业服务公司做数据分析师(你肯定听说过的)。我干了大概有10年。10中我处理数据、数据库软件、数据库硬件、数据库用户、数据库程序员以及数据分析方法,所以我对这些东西了解的比较多。我经常遇到对相关内容了解很少的人,虽然他们中的一部分并没有意识到这件事

这些年里,我已经太多太多次的讨论了 PostgreSQL 和 MS SQL 的问题。IT 行业中一个知名的原则说:如果你准备不只一次的做同一件事,那就让它自动化。本文是我的自动化方法的谈话。

开源中国七里香
开源中国七里香
翻译于 2014/11/17 15:31
2

除非另有说明,我指的是PostgreSQL 9.3和MS SQL Server 2014,即使我的经验是在MS SQL Server 2008 R2和2012版。为了公平起见,我将比较最新版的MS SQL Server和PostgreSQL。由于微软的糟糕的文档,我不得不大量的依赖于Google、Stack Overflow以及网络上的用户。因为我对两个数据库的经验不相等,所以我知道像这样的比较不够科学严谨。不过这不是一个学者的练习题,这是现实中的比较。我尽可能让我对于MS SQL Server的了解正确,因为我们都知道要糊弄整个互联网是不可能的。如果我发现我弄错了什么事情,我会修正的。

我将以一个数据分析师的角度来比较两个数据库。MS SQL Server可能会因为QLTP后台而踢PostgreSQL的屁股(虽然我比较怀疑),不过那些不是我这里要关注的,因为我不是一个OLTP开发者/DBA/系统管理员。

最后,右上角有一个email地址。如果你愿意的话你会用到的,我会尽可能回复的。

免责声明:本文所有观点仅代表我个人。

开源中国七里香
开源中国七里香
翻译于 2014/11/17 22:05
1

1. 为什么说 PostgreSQL 比 MS SQL Server 强的多

额,剧透了。本节从数据分析的角度对比这两种数据库。

1.1. 支持 CSV

CSV 其实是转移结构化数据(如: 表)的一种标准方式。不论是哪一种数据库,都能用自己专有的格式,把数据导出来。以这种格式存储的数据,其他软件无法读取. 用来做备份或者复制数据还行。如果想从 X 系统, 把数据移植到 Y 系统,那问题就大了。

一个数据分析平台, 既要能读取不同系统的数据, 也要能生成其他系统能读取的分析结果.  也就是说, 要能快速, 稳定, 可重复的, 而且毫无痛苦的读写 CSV.  我再说一次:一个不能很好的处理 CSV 的数据分析平台,就是没用的累赘。

BreakingBad
BreakingBad
翻译于 2014/11/17 19:05
3

PostgresSQL对CSV的支持在业内是顶尖的。  COPY TO和 COPY FROM命令支持RFC4180(最接近官方标准的文档)中列出的所有规格,也支持很多常见的和不常见的变种和方言。 这些命令运行速度很快而且很强大。 发生一个错误时,它们会给出有帮助性的错误信息。 更重要的是,它们不会默默地损坏、误解、修改数据。

而MS SQL Server既不支持导入也不支持导出CSV文件。 很多人不相信当我告诉他们这一点时。 然后,某一次,他们自己验证了这一点。通常他们的观察是这样的:

  • MS SQL Server默默地清除(truncate)了一个文本字段的数据

  • MS SQL Server对文本进行编码时发生错误

  • MS SQL Server抛出一个错误信息因为它不理解引用或转义(出乎人们的意料,对CSV来说引用和转义不是特殊的扩展。从字面上看,引用和转义是每一个人类可读的数据序列化规范的基本概念。不要相信那些不懂这些东西的人。)

  • MS SQL Server导出损坏的、不可用的CSV文件

  • 微软有一篇惊人的文档。他们怎么能把CSV这么简单的东西如此复杂化的呢?

如果你不相信,下载这个格式正确的、符合标准的UTF-8编码的CSV文件,用MS SQL Server计算文件中最后一列(共有50列)字符串的平均长度(或者是字符的数量,等等)。继续,试一下。

(你得到的答案将是 183.895。)

daxiang
daxiang
翻译于 2014/11/18 08:43
2

当然,事实上,对 PostgreSQL 来说,确定这么做非常简单。最耗费时间的地方是创建保存这些数据的且具有50个字段的数据表。微软本身似乎就很难理解CSV文件;而且打开这样的文件还会引起Access和Excel中断。

痛苦但却是事实的情况是:我了解到近期一些数据库编程人员花费大量的时间和精力编写Python代码,以实现对CSV文件的“清理”,从而让MS SQL服务器可以把这些文件的内容导入到数据库里。但是,这种处理方法不可避免的要更改实际的数据。这就像花费大量金钱购买了Photoshop,然后不得不编写一些定制的代码来让Photoshop打开JPEG,到头来仅仅发现只是稍稍修改了图片那样让人抓狂。

几点人
几点人
翻译于 2014/11/18 10:30
2

1.2.人机工程

值得一提的是每个数据分析平台都是图灵完备的,这大概意味着任何一个数据分析平台可以做其他数据分析平台做的任何事情。也就不存在“你可以在A软件中做X这件事而不可以在B软件中做X这件事”。即你可以在任何软件里做任何事情-所不同是难易程度。好的工具让你要做的事情做起来非常简单;差的工具就会让你要做的事情做起来很难。说到底就是这么回事。

(理论上来讲这一切都是正确的,然而现实中却不是这样的-例如,我了解到没有关系型数据库管理系统(RDBMS)使用3D图形。不过,任意一个关系型数据库管理系统都可以模拟GPU执行任何图形计算。)

几点人
几点人
翻译于 2014/11/18 11:17
1

很显然,PostgreSQL 是由实际关心如何对资料进行处理的人编写的。而MS SQL服务器则是由那些根本就不需要使用MS SQL服务器来实现某件事情的人编写的。下面的几个例子就可以说明:

  • PostgreSQL支持DROP TABLE IF EXISTS,这是一个非常聪明且显而易见的处理方式,它表明:“如果这个表不存在,就什么也不做,如果存在,就删除它”。例如:

    DROP TABLE IF EXISTS my_table;

    在MS SQL服务器中,你却需要这么做:


    IF OBJECT_ID (N'dbo.my_table', N'U') IS NOT NULL
    DROP TABLE dbo.my_table;

    是的,只是多了一行代码,不过要留意OBJECT_ID函数中令人奇怪的第二个参数。删除视图时你需要把它替换为N'V'。删除存储过程时替换为N'P'。我不知道所有不同类型的数据库对象对应的各个字符(为什么我必须要知道呢?)还要注意到另一点:不必要地重复了表名。你稍不留意,就容易做出以下事情:



    IF OBJECT_ID (N'dbo.some_table', N'U') IS NOT NULL
    DROP TABLE dbo.some_other_table;

    看看此时会发生什么?这确实是令人懊恼、浪费时间的错误。


几点人
几点人
翻译于 2014/11/18 11:56
2
  • PostgreSQL支持DROP SCHEMA CASCADE,它会删除模式以及该模式下的所有数据库对象。对一个强壮的分析方法来说,做到这一点非常、非常重要,因为此时分割和重建是进行可重复的、可审计的协作分析工作的基本操作方法。而MS SQL服务器却不是这样的。你不得不手工删除该模式下的所有对象,而且要按照正确的顺序删除,因为在你试图删除一个其他对象依赖的对象时,MS SQL服务器只会抛出一个错误。使得整个处理过程非常笨拙。

  • PostgreSQL 支持 CREATE TABLE AS。一个简单的例子如下:

    CREATE TABLE good_films AS
    SELECT
      *
    FROM
      all_films
    WHERE
      imdb_rating >= 8;

    这就意味着你可以使用除第一行以外的其他行,并执行,在开发SQL代码时,这是一个常见的且非常有用的处理方式。在MS SQL服务器里,你要采用如下代码才能以上面的方式创建表:


    SELECT
      *
    INTO
      good_films
    FROM
      all_films
    WHERE
      imdb_rating >= 8;

    此时,要执行普通的SELECT语句的话,你需要注释或者删除INTO部分。是的,注释两行非常简单;不过这不是我们关注的地方。我们关注的是在PostgreSQL里,你不需要修改代码就可以执行这个简单的任务,而在MS SQL服务器上,你无法做到这一点,而且你要做到这一点还会带来另一个潜藏的漏洞和令人讨厌的东西。


几点人
几点人
翻译于 2014/11/18 12:52
2
  • 在PostgreSQL里,你可以在一次批处理里执行你愿意执行数量级的SQL语句;只要每个语句都以分号结束,你就可以执行你所想到的任何语句组合。对于哪些要执行自动批处理、构建重复数据或者进行输出的程序来说,这是一个非常重要的功能。在MS SQL服务器里,在一个批处理的SQL语句中间不能出现CREATE PROCEDURE语句。这么做没有任何好的理由,仅仅是随意加的一个限制。此时就意味着需要额外的手工操作来执行大量的SQL批处理。手工操作会增加风险,降低效能。

  • PostgreSQL 支持 RETURNING 子句,允许 UPDATE,INSERTDELETE 语句返回已更改行上的数据值。这么做非常简洁有益。MS SQL 服务器有个 OUTPUT 子句可满足这方面需求,不过它需要单独定义表变量来实现此功能。这么做很笨拙而且不方便,还迫使程序开发人员创建并维护不怎么需要的代码。

几点人
几点人
翻译于 2014/11/18 13:33
2
  • PostgreSQL 支持用 $$ 将字符串括起来, 像这样:

    SELECT $$Hello, World$$ AS greeting;

    这样写, 对动态生成 SQL 语句很有用, 因为 (a) 当嵌入字符串时, 能避免既繁琐, 又容易出错的手工引用和对特殊字符的转义.  (b) 由于文本编辑器和 IDE 一般不把 $$ 当作字符串分隔符, 动态生成的 SQL 语句依然根据语法高亮显示。
  • PostgreSQL 允许你向数据库引擎提交面向过程的编程语言代码; 你可以使用, 像 Python , Perl , R 或 JavaScript, 或其他已被支持的语言(具体看下面), 在同一个脚本文件的 SQL 语句旁边, 加上面向过程的代码. 这样做简洁方便, 易于维护. 同时也方便查看代码, 重复使用, 等等各种好处.  

    而 MS SQL Server, 你可以使用笨拙, 缓慢, 还有点尴尬的 T-SQL, 或者用 .NET  生成组件(Assembly) 然后加载到数据库中. 。也就是说,你的代码存在两个不同的地方。你得在各种图形界面之间切来换去的修改这些代码。想要将这些东西统一打包放在一起,困难重重。而且也容易出错。

BreakingBad
BreakingBad
翻译于 2014/11/18 16:03
1
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(71)

blu10ph
blu10ph

引用来自“subool”的评论

mssql性能并不差, 关键要配置和设置好. 其次, mssql最重要的, 管理介面非常好, 非常方便. postgresql用什么管理呢? 命令行?

引用来自“菲戈”的评论

postgresql有个pg admin,也很好用
居然有人说好用,我用的是假的pg admin吗?~
WawaLili
WawaLili
Are you kidding me?
等我看完了你才告诉我 postgresql 比 sqlserver 慢!
雷兽
mysql分支版本 哪个 哪天 完全本质的达到甚至超越pg的水平 也不尽然是不可能的
雷兽
说到底 mysql的根基有点差 但是 不妨碍他被越来越多的分支 百花齐放 未必不能最终被哪个第三方分支改的面目全非 但是 本质的达到甚至超越pg的水平 也不尽然
我只是程序员 架构人员 开发人员 不是dba 认识有限 但是我只知道 变化中的一切是 mysql层级的不行 已经越来越少了 被诟病的很多东西都在减少 被认为不存在的功能 都在支持中 我看就算再过10年20年 pg也不可能使用量超过mysql和其分支 这是个必然 而且 就算是企业领域 金融领域 pg也不见得100%能挡住mysql方面的侵蚀, 当一个开源软件有太多用户 支持者 修改者的时候 其前途是无法预期的 哈哈哈
雷兽

引用来自“subool”的评论

mssql性能并不差, 关键要配置和设置好. 其次, mssql最重要的, 管理介面非常好, 非常方便. postgresql用什么管理呢? 命令行?

引用来自“菲戈”的评论

postgresql有个pg admin,也很好用
那货不是一直被人说难用吗 哈哈哈
雷兽

引用来自“encro”的评论

看完了,基本上可以改成mysql vs. sqlserver只需要将postgresql替换成mysql就行。如果改成marialdb估计能写更多内容。

引用来自“huan”的评论

没搞错吧,mysql?! 这个玩意儿能做数据分析用?
marialdb 基本能 已经支持hash join了 不过数据真大了 哪个速度都快不了 有啥两样?哈哈哈
忧郁-欢哥
very good.
hefju
hefju
sqlserver将查询结果导出到csv, 然后再导入到当前数据库, 错误: 提示指定了多个代码页. 指望用csv传递数据到其他电脑的数据库无望了. sqlserver导入excel还会截断电话号码, 身份证等资料, 数字和字符在同一列, 字符往往会丢失. 这是处理导致与原数据不符也不报错, 大坑啊.
第二界
mysql只能给PostgreSQL 提鞋
第二界

引用来自“encro”的评论

看完了,基本上可以改成mysql vs. sqlserver只需要将postgresql替换成mysql就行。如果改成marialdb估计能写更多内容。
mysql那不规范的语法,真恶心
返回顶部
顶部