客观公正地评价MySQL和PostgreSQL的优劣

一 前言 前一段时间 我曾经翻译过一篇将你的网站从MySQL改为PostgreSQL 其实当初我更感兴趣的是一个应用程序的后台数据库从MySQL转为PostgreSQL的具体 *** 作 并没有关心MySQL和PostgreSQL的优劣 没想到反应出乎意料的大 因此我也就觉得有写这篇文章的必要了 在这篇文章中 我们选用MySQL alpha与PostgreSQL 进行比较 因为MySQL alpha开始支持事务的概念 因此这样的比较对于MySQL应该较为有利 我们这样的比较不想仅仅成为一份性能测试报告 因为至少从我个人来看 对于一个数据库 稳定性和速度并不能代表一切 对于一个成熟的数据库 稳定性肯定会日益提供 而随着硬件性能的飞速提高 速度也不再是什么太大的问题 二 两者的共同优势 这两个产品都属于开放源码的一员 性能和功能都在高速地提高和增强 MySQL AB的人们和PostgreSQL的开发者们都在尽可能地把各自的数据库改得越来越好 所以对于任何商业数据库使用其中的任何一个都不能算是错误的选择 三 两者不同的背景 MySQL的背后是一个成熟的商业公司 而PostgreSQL的背后是一个庞大的志愿开发组 这使得MySQL的开发过程更为慎重 而PostgreSQL的反应更为迅速 这样的两种背景直接导致了各自固有的优点和缺点 四 MySQL的主要优点 首先是速度 MySQL通常要比PostgreSQL快得多 MySQL自已也宣称速度是他们追求的主要目标之一 基于这个原因 MySQL在以前的文档中也曾经说过并不准备支持事务和触发器 但是在最新的文档中 我们看到MySQL alpha已经开始支持事务 而且在MySQL的TODO中 对触发器 约束这样的注定会降低速度的功能也列入了日程 但是 我们仍然有理由相信 MySQL将有可能一直保持速度的优势 MySQL比PostgreSQL更流行 流行对于一个商业软件来说 也是一个很重要的指标 流行意味着更多的用户 意味着经受了更多的考验 意味着更好的商业支持 意味着更多 更完善的文档资料 与PostgreSQL相比 MySQL更适宜在Windows环境下运行 MySQL作为一个本地的Windows应用程序运行(在NT/Win /WinXP下 是一个服务) 而PostgreSQL是运行在Cygwin模拟环境下 PostgreSQL在Windows下运行没有MySQL稳定 应该是可以想象的 MySQL使用了线程 而PostgreSQL使用的是进程 在不同线程之间的环境转换和访问公用的存储区域显然要比在不同的进程之间要快得多 MySQL可以适应 / 运行 在绝大多数情况下 你不需要为MySQL运行任何清除程序 PostgreSQL目前仍不完全适应 / 运行 这是因为你必须每隔一段时间运行一次VACUUM MySQL在权限系统上比PostgreSQL某些方面更为完善 PostgreSQL只支持对于每一个用户在一个数据库上或一个数据表上的INSERT SELECT和UPDATE/DELETE的授权 而MySQL允许你定义一整套的不同的数据级 表级和列级的权限 对于列级的权限 PostgreSQL可以通过建立视图 并确定视图的权限来弥补 MySQL还允许你指定基于主机的权限 这对于目前的PostgreSQL是无法实现的 但是在很多时候 这是有用的 由于MySQL alpha开始支持事务的概念 因此事务对于MySQL不再仅仅成为劣势 相反 因为MySQL保留无事务的表类型 这就为用户提供了更多的选择 MySQL的MERGE表提供了一个独特管理多个表的方法 MySQL的myisampack可以对只读表进行压缩 此后仍然可以直接访问该表中的行 五 PostgreSQL的主要优点 对事务的支持与MySQL相比 经历了更为彻底的测试 对于一个严肃的商业应用来说 事务的支持是不可或缺的 MySQL对于无事务的MyISAM表 采用表锁定 一个长时间运行的查询很可能会长时间地阻碍对表的更新 而PostgreSQL不存在这样的问题 PostgreSQL支持存储过程 而目前MySQL不支持 对于一个严肃的商业应用来说 作为数据库本身 有众多的商业逻辑的存在 此时使用存储过程可以在较少地增加数据库服务器的负担的前提下 对这样的商业逻辑进行封装 并可以利用数据库服务器本身的内在机制对存储过程的执行进行优化 此外存储过程的存在也避免了在网络上大量的原始的SQL语句的传输 这样的优势是显而易见的 对视图的支持 视图的存在同样可以最大限度地利用数据库服务器内在的优化机制 而且对于视图权限的合理使用 事实上可以提供行级别的权限 这是MySQL的权限系统所无法实现的 对触发器的支持 触发器的存在不可避免的会影响数据库运行的效率 但是与此同时 触发器的存在也有利于对商业逻辑的封装 可以减少应用程序中对同一商业逻辑的重复控制 合理地使用触发器也有利于保证数据的完整性 对约束的支持 约束的作用更多地表现在对数据完整性的保证上 合理地使用约束 也可以减少编程的工作量 对子查询的支持 虽然在很多情况下在SQL语句中使用子查询效率低下 而且绝大多数情况下可以使用带条件的多表连接来替代子查询 但是子查询的存在在很多时候仍然不可避免 而且使用子查询的SQL语句与使用带条件的多表连接相比具有更高的程序可读性 支持R trees这样可扩展的索引类型 可以更方便地处理一些特殊数据 PostgreSQL可以更方便地使用UDF(用户定义函数)进行扩展 六 那么我究竟应该使用MySQL还是PostgreSQL 这个问题很难说得清 而且事实上除了MySQL和PostgreSQL外 使用Oracle Sybase Informix等也是明智的选择 如何你确定只在MySQL和PostgreSQL中进行选择 以下规则总是有效的 如果你的 *** 作系统是Windows 你应该使用MySQL 如果你对数据库并不了十分了解 甚至不知道事务 存储过程等究竟是什么 你应该使用MySQL 如果你的应用对数据的完整性和严肃性要求不高 但是追求处理的高速度 例如是一个论坛和社区 你应该使用MySQL 你的应用是一个严肃的商业应用 对数据完整性要求很高 而且你希望对一些商业数据逻辑进行很好的封装 例如是一个网上银行 你应该使用PostgreSQL 你的应用处理的是地理数据 由于R TREES的存在 你应该使用PostgreSQL 你是一个数据库内核的狂热爱好者 你甚至希望拥有你自己版本的数据库 毫无疑问 你必须使用PostgreSQL 没准下一个PostgreSQL版本中某一个模块的作者就是你 七 结语 以上只是希望从我的理解客观公正地评价MySQL和PostgreSQL的优劣 其中的带有倾向性的意见只代表个人观点 由于本人水平的限制 有不当之处还请批评指正 lishixinzhi/Article/program/MySQL/201311/29459

（1）用户实用程序：

createdb 创建一个新的PostgreSQL的数据库（和SQL语句：CREATE DATABASE 相同）

createuser 创建一个新的PostgreSQL的用户（和SQL语句：CREATE USER 相同）

dropdb 删除数据库

dropuser 删除用户

pg_dump 将PostgreSQL数据库导出到一个脚本文件

pg_dumpall 将所有的PostgreSQL数据库导出到一个脚本文件

pg_restore 从一个由pg_dump或pg_dumpall程序导出的脚本文件中恢复PostgreSQL数据库

psql 一个基于命令行的PostgreSQL交互式客户端程序

vacuumdb 清理和分析一个PostgreSQL数据库，它是客户端程序psql环境下SQL语句VACUUM的shell脚本封装，二者功能完全相同

（2）系统实用程序

initdb 创建一个用于存储数据库的PostgreSQL数据目录，并创建预定义的模板数据库template0和template1,生成共享目录表 catalog；此程序通常只在安装PostgreSQL时运行一次

initlocation 创建一个辅助的PostgreSQL数据库存储区域

ipcclean 从停止的PostgreSQL服务器中清除共享内在和孤立信号标志

pg_ctl 启动、停止、重启PostgreSQL服务（比如：pg_ctl start 启动PostgreSQL服务，它和service postgresql start相同）

pg_controldata 显示PostgreSQL服务的内部控制信息

postgres PostgreSQL单用户模式的数据库服务

postmaster PostgreSQL多用户模式的数据库服务

4这里面最重要的是psql这个客户端程序最为重要。启用客户端程序psql的方法是：

切换到PostgreSQL预定义的数据库超级用户postgres，启用客户端程序psql，并连接到自己想要的数据库，比如说：

psql template1

出现以下界面，说明已经进入到想要的数据库，可以进行想要的 *** 作了。

template1=#

5在数据库中的一些命令：

template1=# \l 查看系统中现存的数据库

template1=# \q 退出客户端程序psql

template1=# \c 从一个数据库中转到另一个数据库中，如template1=# \c sales 从template1转到sales

template1=# \dt 查看表

template1=# \d 查看表结构

template1=# \di 查看索引

[基本数据库 *** 作]========================

创建数据库：

create database [数据库名];

查看数据库列表：

\d

删除数据库：

drop database [数据库名];

创建表：

create table ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],<,primary key (字段名m,字段名n,)>;);

查看表名列表：

\d

查看某个表的状况：

\d [表名]

重命名一个表：

alter table [表名A] rename to [表名B];

删除一个表：

drop table [表名];

========================================

[表内基本 *** 作]==========================

在已有的表里添加字段：

alter table [表名] add column [字段名] [类型];

删除表中的字段：

alter table [表名] drop column [字段名];

重命名一个字段：

alter table [表名] rename column [字段名A] to [字段名B];

给一个字段设置缺省值：

alter table [表名] alter column [字段名] set default [新的默认值];

去除缺省值：

alter table [表名] alter column [字段名] drop default;

在表中插入数据：

insert into 表名 ([字段名m],[字段名n],) values ([列m的值],[列n的值],);

修改表中的某行某列的数据：

update [表名] set [目标字段名]=[目标值] where [该行特征];

删除表中某行数据：

delete from [表名] where [该行特征];

delete from [表名];--删空整个表

6要注意随时对数据库进行清理、收回磁盘空间并更新统计信息，使用下面的命令就搞定！

vaccumdb -d sales -z

-a 对所有的数据库 *** 作

-z 保证不断地删除失效的行，节约磁盘空间，将统计信息更新为最近的状态

7PostgreSQL用户认证

PostgreSQL数据目录中的pg_hbaconf的作用就是用户认证，可以在/var/lib/pgsql/data中找到。

有以下几个例子可以看看：

(1)允许在本机上的任何身份连接任何数据库

TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

local all all trust(无条件进行连接)

(2)允许IP地址为1921681x的任何主机与数据库sales连接

TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD

host sales all 19216810 2552552550 ident sameuser(表明任何 *** 作系统用户都能够以同名数据库用户进行连接)

8看了那么多，来一个完整的创建PostgreSQL数据库用户的示例吧

(1)进入PostgreSQL高级用户

(2)启用客户端程序，并进入template1数据库

psql template1

(3)创建用户

template1=# CREATE USER hellen WITH ENCRYPED PASSWORD'zhenzhen'

(4)因为设置了密码，所以要编辑pg_hbaconf，使用户和配置文件同步。

在原有记录上面添加md5

local all hellen md5

(4)使用新用户登录数据库

template1=# \q

psql -U hellen -d template1

PS：在一个数据库中如果要切换用户，要使用如下命令：

template1=# \!psql -U tk -d template1

9设定用户特定的权限

还是要用例子来说明：

创建一个用户组：

sales=# CREATE GROUP sale;

添加几个用户进入该组

sales=# ALTER GROUP sale ADD USER sale1,sale2,sale3;

授予用户级sale针对表employee和products的SELECT权限

sales=# GRANT SELECT ON employee,products TO GROUP sale;

在sale中将用户user2删除

sales=# ALTER GROP sale DROP USER sale2;

10备份数据库

可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：

pg_dump sales>/home/tk/pgsql/backup/1bak

以上就是关于客观公正地评价MySQL和PostgreSQL的优劣全部的内容，包括:客观公正地评价MySQL和PostgreSQL的优劣、linux怎么用命令登陆postgres、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！