title: "Postgresql存儲(chǔ)二進(jìn)制大數(shù)據(jù)文件"
在信豐等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì) 網(wǎng)站設(shè)計(jì)制作按需策劃,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),高端網(wǎng)站設(shè)計(jì),網(wǎng)絡(luò)營(yíng)銷(xiāo)推廣,外貿(mào)營(yíng)銷(xiāo)網(wǎng)站建設(shè),信豐網(wǎng)站建設(shè)費(fèi)用合理。
date: 2021-02-02T20:46:31+08:00
draft: true
tags: ['postgres','binary']
author: "dadigang"
author_cn: "大地缸"
personal: " "
如果想把整個(gè)文件或圖片存儲(chǔ)在數(shù)據(jù)表的一個(gè)字段內(nèi)，該字段可以選擇二進(jìn)制類(lèi)型，然后將文件按二進(jìn)制存儲(chǔ)起來(lái)，文本文件也可以存在text字段內(nèi)。
示例如下：
二進(jìn)制類(lèi)型bytea的操作（在最大值內(nèi)，有內(nèi)存限制）
1、 創(chuàng)建表
2、 將文件放到coordinator目錄下/mnt/postgresql/coord
通過(guò)pg_read_binary_file()函數(shù)，插入一張圖片- 目錄：/mnt/postgresql/coord/1.jpg
3、 也可以調(diào)用pg_read _file()將一個(gè)文本文件存儲(chǔ)在一個(gè)text字段內(nèi)
注意：函數(shù)pg_read_binary_file()和pg_read_file()中的路徑必須是相對(duì)路徑，默認(rèn)路徑是coordinator目錄下，并且必須在coordinator目錄下或者coordinator目錄的子目錄下。
Name
Return Type
Description
pg_read_file(filename text [, offset bigint, length bigint])
text
Return the contents of a text file
pg_read_binary_file(filename text [, offset bigint, length bigint])
bytea
Return the contents of a file
PostgreSQL有pldbgapi擴(kuò)展，先安裝此擴(kuò)展。
首先，需要將debug的模組載入到PostgreSQL服務(wù)器中去。做法是: 在pgAdminIII中以管理員登錄，然后選擇菜單“工具-服務(wù)器配置-postgresql.conf”，
在配置窗口中，雙擊項(xiàng)目"shared_preload_libraries"，
在其設(shè)定窗口中，將值設(shè)置成plugin_debugger的路徑(Windows 9.3版本的為$libdir/plugin_debugger.dll, $libdir一般為PostgreSQL安裝目錄下的lib文件夾)。非Windows系統(tǒng)的后綴名會(huì)有所不同，可能為plugin_debugger.so。
注意，在做這一步時(shí)最好先備份配置文件，以防指定文件找不到而導(dǎo)致服務(wù)器不能啟動(dòng)。
在設(shè)定完成之后，重新啟動(dòng)PostgreSQL服務(wù)(以啟動(dòng)debug插件)。
然后在pgAdminIII中擴(kuò)展安裝時(shí)選擇如下圖項(xiàng)目，
這樣在函數(shù)的右鍵菜單中就有調(diào)試選項(xiàng)了，
當(dāng)然，最常用的調(diào)試方式就是自己直接調(diào)用函數(shù)來(lái)查看執(zhí)行結(jié)果是否正確。
如果想要在PostgreSQL中存儲(chǔ)二進(jìn)制數(shù)據(jù)，例如存儲(chǔ)Word、Excel文檔，圖片文件等，可以使用bytea類(lèi)型的列。bytea類(lèi)型是PostgreSQL特有的存儲(chǔ)二進(jìn)制數(shù)據(jù)的字段類(lèi)型，與SQL標(biāo)準(zhǔn)中的BLOB和BINARY
LARGE OBJECT類(lèi)型異曲同工。這在PostgreSQL文檔的bytea類(lèi)型介紹中有所說(shuō)明。
接下來(lái)先說(shuō)說(shuō)如何向表中插入、更新bytea數(shù)據(jù)。
PostgreSQL允許在SQL命令中包含bytea類(lèi)型的數(shù)據(jù)，以便能夠使用INSERT向表中插入含有二進(jìn)制數(shù)據(jù)的記錄，使用UPDATE和調(diào)用與bytea類(lèi)型相關(guān)的函數(shù)更新和操作bytea類(lèi)型數(shù)據(jù)。二進(jìn)制數(shù)據(jù)是一個(gè)字節(jié)序列，然而SQL命令是文本字符串，怎樣在SQL中寫(xiě)入二進(jìn)制數(shù)據(jù)呢？答案很簡(jiǎn)單，把每一個(gè)字節(jié)轉(zhuǎn)換成對(duì)應(yīng)的三位十進(jìn)制數(shù)字的八進(jìn)制數(shù)字符串表示，以雙斜線做為前綴，即0x00表示為\\000、0x2C表示為\\02C、0xFF表示為\\377，并按照bytea類(lèi)型的要求在字符串前端的單引號(hào)外注明E。舉例如下：
INSERT INTO table1 (fileid, filename, content) VALUES (1, 'filename.doc', E'\\000\\001\\002');
INSERT INTO table1 (fileid, filename, content) VALUES (2, 'anotherfile.jpg', E'\\000\\377');
UPDATE table1 SET content = E'\\000\\000\\000' WHERE fileid
= 1;
UPDATE table1 SET content = content || E'\\377\\377\\377' WHERE fileid
= 2;
可以在INSERT
INTO中包含整個(gè)文件的bytea類(lèi)型字符串，也可以像上面第四行那樣，分塊追加。對(duì)于短小的二進(jìn)制數(shù)據(jù)，在命令控制臺(tái)中編輯SQL命令也未嘗不可。但是如果要存儲(chǔ)一個(gè)圖片文件或者Word文檔之類(lèi)的大型二進(jìn)制數(shù)據(jù)的時(shí)候，就需要借助數(shù)據(jù)訪問(wèn)借口，或者自己寫(xiě)一個(gè)字節(jié)轉(zhuǎn)換程序，直接操作SQL語(yǔ)句。
插入bytea數(shù)據(jù)后可以使用SELECT語(yǔ)句獲取它。如下所示：
SELECT content FROM table1;
在命令控制臺(tái)中，我們會(huì)看到以輸入時(shí)的字符串格式輸出二進(jìn)制數(shù)據(jù)，這是PostgreSQL做的轉(zhuǎn)換。在Python中使用psycopg2模塊，執(zhí)行上述SELECT語(yǔ)句后能夠獲得原始的二進(jìn)制字節(jié)字符串，可以直接寫(xiě)入二進(jìn)制文件。
順便說(shuō)明一下。對(duì)于字節(jié)的轉(zhuǎn)換，PostgreSQL的文檔說(shuō)的非常詳細(xì)，按照零字節(jié)、單引號(hào)、斜線，以及字符的可打印性分別作了討論。原因是需要逃逸單引號(hào)和斜線字符，另外可打印字符可以不作轉(zhuǎn)換，直接出現(xiàn)。
MySQL
MySQL聲稱(chēng)自己是最流行的開(kāi)源數(shù)據(jù)庫(kù)。LAMP中的M指的就是MySQL。構(gòu)建在LAMP上的應(yīng)用都會(huì)使用MySQL，如WordPress、Drupal等大多數(shù)php開(kāi)源程序。MySQL最初是由MySQL AB開(kāi)發(fā)的，然后在2008年以10億美金的價(jià)格賣(mài)給了Sun公司，Sun公司又在2010年被Oracle收購(gòu)。Oracle支持MySQL的多個(gè)版本：Standard、Enterprise、Classic、Cluster、Embedded與Community。其中有一些是免費(fèi)下載的，另外一些則是收費(fèi)的。其核心代碼基于GPL許可，由于MySQL被控制在Oracle，社區(qū)擔(dān)心會(huì)對(duì)MySQL的開(kāi)源會(huì)有影響，所以開(kāi)發(fā)了一些分支，比如： MariaDB和Percona。
PostgreSQL
PostgreSQL標(biāo)榜自己是世界上最先進(jìn)的開(kāi)源數(shù)據(jù)庫(kù)。PostgreSQL的一些粉絲說(shuō)它能與Oracle相媲美，而且沒(méi)有那么昂貴的價(jià)格和傲慢的客服。最初是1985年在加利福尼亞大學(xué)伯克利分校開(kāi)發(fā)的，作為Ingres數(shù)據(jù)庫(kù)的后繼。PostgreSQL是完全由社區(qū)驅(qū)動(dòng)的開(kāi)源項(xiàng)目。它提供了單個(gè)完整功能的版本，而不像MySQL那樣提供了多個(gè)不同的社區(qū)版、商業(yè)版與企業(yè)版。PostgreSQL基于自由的BSD/MIT許可，組織可以使用、復(fù)制、修改和重新分發(fā)代碼，只需要提供一個(gè)版權(quán)聲明即可。
MySQL與PostgreSQL的對(duì)比
MySQL的背后是一個(gè)成熟的商業(yè)公司，而PostgreSQL的背后是一個(gè)龐大的志愿開(kāi)發(fā)組。這使得MySQL的開(kāi)發(fā)過(guò)程更為慎重，而PostgreSQL的反應(yīng)更為迅速。這樣的兩種背景直接導(dǎo)致了各自固有的優(yōu)點(diǎn)和缺點(diǎn)。
PostgreSQL相對(duì)于MySQL的優(yōu)勢(shì)
1)不僅僅是關(guān)系型數(shù)據(jù)庫(kù)
除了存儲(chǔ)正常的數(shù)據(jù)類(lèi)型外，還支持存儲(chǔ)：
array，不管是一位數(shù)組還是多為數(shù)組均支持
json(hStore)和jsonb，相比使用text存儲(chǔ)接送要高效很多
json和jsonb之間的區(qū)別
jsonb和json在更高的層面上看起來(lái)幾乎是一樣的，但在存儲(chǔ)實(shí)現(xiàn)上是不同的。
json存儲(chǔ)完的文本，json列會(huì)每次都解析存儲(chǔ)的值，它不支持索引，但你可以為查詢創(chuàng)建表達(dá)式索引。
jsonb存儲(chǔ)的二進(jìn)制格式，避免了重新解析數(shù)據(jù)結(jié)構(gòu)。它支持索引，這意味著你可以不使用指定的索引就能查詢?nèi)魏温窂健?/p>
當(dāng)我們比較寫(xiě)入數(shù)據(jù)速度時(shí)，由于數(shù)據(jù)存儲(chǔ)的方式的原因，jsonb會(huì)比json稍微的慢一點(diǎn)。json列會(huì)每次都解析存儲(chǔ)的值，這意味著鍵的順序要和輸入的時(shí)候一樣。但jsonb不同，以二進(jìn)制格式存儲(chǔ)且不保證鍵的順序。因此，如果你有軟件需要依賴鍵的順序，jsonb可能不是你的應(yīng)用的最佳選擇。使用jsonb的優(yōu)勢(shì)還在于你可以輕易的整合關(guān)系型數(shù)據(jù)和非關(guān)系型數(shù)據(jù)， PostgreSQL對(duì)于mongodb這類(lèi)的基于文檔的數(shù)據(jù)庫(kù)是個(gè)不小的威脅，畢竟如果一個(gè)表中只有一列數(shù)據(jù)的類(lèi)型是半結(jié)構(gòu)化的，沒(méi)有必要為了遷就它而整個(gè)表的設(shè)計(jì)采用schemaless的結(jié)構(gòu)。
2)支持地理信息處理擴(kuò)展
PostGIS 為PostgreSQL提供了存儲(chǔ)空間地理數(shù)據(jù)的支持，使PostgreSQL成為了一個(gè)空間數(shù)據(jù)庫(kù)，能夠進(jìn)行空間數(shù)據(jù)管理、數(shù)量測(cè)量與幾何拓?fù)浞治觥Ｔ诠δ苌?，和MYSQL對(duì)比，PostGIS具有下列優(yōu)勢(shì)：
O2O業(yè)務(wù)場(chǎng)景中的LBS業(yè)務(wù)使用PostgreSQL + PostGIS有無(wú)法比擬的優(yōu)勢(shì)。
3)可以快速構(gòu)建REST API
PostgREST 可以方便的為任何 PostgreSQL 數(shù)據(jù)庫(kù)提供完全的 RESTful API 服務(wù)。
4)支持樹(shù)狀結(jié)構(gòu)
支持R-trees這樣可擴(kuò)展的索引類(lèi)型，可以更方便地處理一些特殊數(shù)據(jù)。MySQL 處理樹(shù)狀的設(shè)計(jì)會(huì)很復(fù)雜, 而且需要寫(xiě)很多代碼, 而 PostgreSQL 可以高效處理樹(shù)結(jié)構(gòu)。
5)有極其強(qiáng)悍的 SQL 編程能力
支持遞歸，有非常豐富的統(tǒng)計(jì)函數(shù)和統(tǒng)計(jì)語(yǔ)法支持。
MySQL：支持 CREATE PROCEDURE 和 CREATE FUNCTION 語(yǔ)句。存儲(chǔ)過(guò)程可以用 SQL 和 C++ 編寫(xiě)。用戶定義函數(shù)可以用 SQL、C 和 C++ 編寫(xiě)。
PostgreSQL：沒(méi)有單獨(dú)的存儲(chǔ)過(guò)程，都是通過(guò)函數(shù)實(shí)現(xiàn)的。用戶定義函數(shù)可以用 PL/pgSQL(專(zhuān)用的過(guò)程語(yǔ)言)、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 編寫(xiě)。
6)外部數(shù)據(jù)源支持
可以把 70 種外部數(shù)據(jù)源 (包括 Mysql, Oracle, CSV, hadoop …) 當(dāng)成自己數(shù)據(jù)庫(kù)中的表來(lái)查詢。Postgres有一個(gè)針對(duì)這一難題的解決方案：一個(gè)名為“外部數(shù)據(jù)封裝器(Foreign Data Wrapper，F(xiàn)DW)”的特性。該特性最初由PostgreSQL社區(qū)領(lǐng)袖Dave Page四年前根據(jù)SQL標(biāo)準(zhǔn)SQL/MED(SQL Management of External Data)開(kāi)發(fā)。FDW提供了一個(gè)SQL接口，用于訪問(wèn)遠(yuǎn)程數(shù)據(jù)存儲(chǔ)中的遠(yuǎn)程大數(shù)據(jù)對(duì)象，使DBA可以整合來(lái)自不相關(guān)數(shù)據(jù)源的數(shù)據(jù)，將它們存入Postgres數(shù)據(jù)庫(kù)中的一個(gè)公共模型。這樣，DBA就可以訪問(wèn)和操作其它系統(tǒng)管理的數(shù)據(jù)，就像在本地Postgres表中一樣。例如，使用FDW for MongoDB，數(shù)據(jù)庫(kù)管理員可以查詢來(lái)自文檔數(shù)據(jù)庫(kù)的數(shù)據(jù)，并使用SQL將它與來(lái)自本地Postgres表的數(shù)據(jù)相關(guān)聯(lián)。借助這種方法，用戶可以將數(shù)據(jù)作為行、列或JSON文檔進(jìn)行查看、排序和分組。他們甚至可以直接從Postgres向源文檔數(shù)據(jù)庫(kù)寫(xiě)入(插入、更細(xì)或刪除)數(shù)據(jù)，就像一個(gè)一體的無(wú)縫部署。也可以對(duì)Hadoop集群或MySQL部署做同樣的事。FDW使Postgres可以充當(dāng)企業(yè)的中央聯(lián)合數(shù)據(jù)庫(kù)或“Hub”。
7)沒(méi)有字符串長(zhǎng)度限制
一般關(guān)系型數(shù)據(jù)庫(kù)的字符串有限定長(zhǎng)度8k左右，無(wú)限長(zhǎng) TEXT 類(lèi)型的功能受限，只能作為外部大數(shù)據(jù)訪問(wèn)。而PostgreSQL的 TEXT 類(lèi)型可以直接訪問(wèn)，SQL語(yǔ)法內(nèi)置正則表達(dá)式，可以索引，還可以全文檢索，或使用xml xpath。MySQL 的各種text字段有不同的限制，要手動(dòng)區(qū)分 small text, middle text, large text… PostgreSQL 沒(méi)有這個(gè)限制，text 能支持各種大小。
8)支持圖結(jié)構(gòu)數(shù)據(jù)存儲(chǔ)
沒(méi)有具體使用過(guò)，具體可以自己搜索下。參考鏈接：
9)支持窗口函數(shù)
窗口函數(shù)提供跨行相關(guān)的當(dāng)前查詢行集執(zhí)行計(jì)算的能力。僅當(dāng)調(diào)用跟著OVER子句的聚集函數(shù)，作為窗口函數(shù);否則它們作為常規(guī)的聚合函數(shù)。窗口也是一種分組，但和 group by 的分組不同。窗口，可以提供分組之外，還可以執(zhí)行對(duì)每個(gè)窗口進(jìn)行計(jì)算?？梢韵嘞癯墒莋roup by 后，然后對(duì)每個(gè)分組進(jìn)行計(jì)算，而不像Group by ，只是單純地分組。MySQL 不支持 OVER 子句, 而PostgreSQL支持。OVER 子句能簡(jiǎn)單的解決 “每組取 top 5” 的這類(lèi)問(wèn)題。MySQL支持的SQL語(yǔ)法(ANSI SQL標(biāo)準(zhǔn))的很小一部分。不支持遞歸查詢、通用表表達(dá)式(Oracle的with 語(yǔ)句)或者窗口函數(shù)(分析函數(shù))。
10)對(duì)索引的支持更強(qiáng)
PostgreSQL 的可以使用函數(shù)和條件索引，這使得PostgreSQL數(shù)據(jù)庫(kù)的調(diào)優(yōu)非常靈活，mysql就沒(méi)有這個(gè)功能，條件索引在web應(yīng)用中很重要。對(duì)于索引類(lèi)型：
MySQL：取決于存儲(chǔ)引擎。MyISAM：BTREE，InnoDB：BTREE。
PostgreSQL：支持 B-樹(shù)、哈希、R-樹(shù)和 Gist 索引。
InnoDB的表和索引都是按相同的方式存儲(chǔ)。也就是說(shuō)表都是索引組織表。這一般要求主鍵不能太長(zhǎng)而且插入時(shí)的主鍵最好是按順序遞增，否則對(duì)性能有很大影響。PostgreSQL不存在這個(gè)問(wèn)題。
索引類(lèi)型方面，MySQL取決于存儲(chǔ)引擎。MyISAM：BTREE，InnoDB：BTREE。PostgreSQL支持 B-樹(shù)、哈希、R-樹(shù)和 Gist 索引。
11)集群支持更好
Mysql Cluster可能與你的想象有較大差異。開(kāi)源的cluster軟件較少。復(fù)制(Replication)功能是異步的并且有很大的局限性。例如，它是單線程的(single-threaded)，因此一個(gè)處理能力更強(qiáng)的Slave的恢復(fù)速度也很難跟上處理能力相對(duì)較慢的Master。
PostgreSQL有豐富的開(kāi)源cluster軟件支持。plproxy 可以支持語(yǔ)句級(jí)的鏡像或分片，slony 可以進(jìn)行字段級(jí)的同步設(shè)置，standby 可以構(gòu)建WAL文件級(jí)或流式的讀寫(xiě)分離集群，同步頻率和集群策略調(diào)整方便，操作非常簡(jiǎn)單。
另外，PostgreSQL的主備復(fù)制屬于物理復(fù)制，相對(duì)于MySQL基于binlog的邏輯復(fù)制，數(shù)據(jù)的一致性更加可靠，復(fù)制性能更高，對(duì)主機(jī)性能的影響也更小。對(duì)于WEB應(yīng)用來(lái)說(shuō)，復(fù)制的特性很重要，mysql到現(xiàn)在也是異步復(fù)制，pgsql可以做到同步，異步，半同步復(fù)制。還有mysql的同步是基于binlog復(fù)制，類(lèi)似oracle golden gate,是基于stream的復(fù)制，做到同步很困難，這種方式更加適合異地復(fù)制，pgsql的復(fù)制基于wal，可以做到同步復(fù)制。同時(shí)，pgsql還提供stream復(fù)制。
12)事務(wù)隔離做的更好
MySQL 的事務(wù)隔離級(jí)別 repeatable read 并不能阻止常見(jiàn)的并發(fā)更新, 得加鎖才可以, 但悲觀鎖會(huì)影響性能, 手動(dòng)實(shí)現(xiàn)樂(lè)觀鎖又復(fù)雜. 而 PostgreSQL 的列里有隱藏的樂(lè)觀鎖 version 字段, 默認(rèn)的 repeatable read 級(jí)別就能保證并發(fā)更新的正確性, 并且又有樂(lè)觀鎖的性能。
13)對(duì)于字符支持更好一些
MySQL 里需要 utf8mb4 才能顯示 emoji 的坑, PostgreSQL 沒(méi)這個(gè)坑。
14)對(duì)表連接支持較完整
對(duì)表連接支持較完整，MySQL只有一種表連接類(lèi)型:嵌套循環(huán)連接(nested-loop),不支持排序-合并連接(sort-merge join)與散列連接(hash join)。PostgreSQL都支持。
15)存儲(chǔ)方式支持更大的數(shù)據(jù)量
PostgreSQL主表采用堆表存放，MySQL采用索引組織表，能夠支持比MySQL更大的數(shù)據(jù)量。
16)時(shí)間精度更高
MySQL對(duì)于時(shí)間、日期、間隔等時(shí)間類(lèi)型沒(méi)有秒以下級(jí)別的存儲(chǔ)類(lèi)型，而PostgreSQL可以精確到秒以下。
17)優(yōu)化器的功能較完整
MySQL對(duì)復(fù)雜查詢的處理較弱，查詢優(yōu)化器不夠成熟，explain看執(zhí)行計(jì)劃的結(jié)果簡(jiǎn)單。性能優(yōu)化工具與度量信息不足。
PostgreSQL很強(qiáng)大的查詢優(yōu)化器，支持很復(fù)雜的查詢處理。explain返回豐富的信息。提供了一些性能視圖，可以方便的看到發(fā)生在一個(gè)表和索引上的select、delete、update、insert統(tǒng)計(jì)信息，也可以看到cache命中率。網(wǎng)上有一個(gè)開(kāi)源的pgstatspack工具。
18)序列支持更好
MySQL 不支持多個(gè)表從同一個(gè)序列中取 id, 而 PostgreSQL 可以。
19)對(duì)子查詢支持更好
對(duì)子查詢的支持。雖然在很多情況下在SQL語(yǔ)句中使用子查詢效率低下，而且絕大多數(shù)情況下可以使用帶條件的多表連接來(lái)替代子查詢，但是子查詢的存在在很多時(shí)候仍然不可避免。而且使用子查詢的SQL語(yǔ)句與使用帶條件的多表連接相比具有更高的程序可讀性。幾乎任何數(shù)據(jù)庫(kù)的子查詢 (subquery) 性能都比 MySQL 好。
20)增加列更加簡(jiǎn)單
MySQL表增加列，基本上是重建表和索引，會(huì)花很長(zhǎng)時(shí)間。PostgreSQL表增加列，只是在數(shù)據(jù)字典中增加表定義，不會(huì)重建表.
MySQL相對(duì)于PostgreSQL的優(yōu)勢(shì)
1)MySQL比PostgreSQL更流行
流行對(duì)于一個(gè)商業(yè)軟件來(lái)說(shuō)，也是一個(gè)很重要的指標(biāo)，流行意味著更多的用戶，意味著經(jīng)受了更多的考驗(yàn)，意味著更好的商業(yè)支持、意味著更多、更完善的文檔資料。易用，很容易安裝。第三方工具，包括可視化工具，讓用戶能夠很容易入門(mén)。
2)回滾實(shí)現(xiàn)更優(yōu)
innodb的基于回滾段實(shí)現(xiàn)的MVCC機(jī)制，相對(duì)PG新老數(shù)據(jù)一起存放的基于XID的MVCC機(jī)制，是占優(yōu)的。新老數(shù)據(jù)一起存放，需要定時(shí)觸發(fā)VACUUM，會(huì)帶來(lái)多余的IO和數(shù)據(jù)庫(kù)對(duì)象加鎖開(kāi)銷(xiāo)，引起數(shù)據(jù)庫(kù)整體的并發(fā)能力下降。而且VACUUM清理不及時(shí)，還可能會(huì)引發(fā)數(shù)據(jù)膨脹。
3)在Windows上運(yùn)行更可靠
與PostgreSQL相比，MySQL更適宜在Windows環(huán)境下運(yùn)行。MySQL作為一個(gè)本地的Windows應(yīng)用程序運(yùn)行(在 NT/Win2000/WinXP下，是一個(gè)服務(wù))，而PostgreSQL是運(yùn)行在Cygwin模擬環(huán)境下。PostgreSQL在Windows下運(yùn)行沒(méi)有MySQL穩(wěn)定，應(yīng)該是可以想象的。
4)線程模式相比進(jìn)程模式的優(yōu)勢(shì)
MySQL使用了線程，而PostgreSQL使用的是進(jìn)程。在不同線程之間的環(huán)境轉(zhuǎn)換和訪問(wèn)公用的存儲(chǔ)區(qū)域顯然要比在不同的進(jìn)程之間要快得多。
進(jìn)程模式對(duì)多CPU利用率比較高。進(jìn)程模式共享數(shù)據(jù)需要用到共享內(nèi)存，而線程模式數(shù)據(jù)本身就是在進(jìn)程空間內(nèi)都是共享的，不同線程訪問(wèn)只需要控制好線程之間的同步。
線程模式對(duì)資源消耗比較少。所以MySQL能支持遠(yuǎn)比PostgreSQL多的更多的連接。但PostgreSQL中有優(yōu)秀的連接池軟件軟件，如pgbouncer和pgpool，所以通過(guò)連接池也可以支持很多的連接。
5)權(quán)限設(shè)置上更加完善
MySQL在權(quán)限系統(tǒng)上比PostgreSQL某些方面更為完善。PostgreSQL只支持對(duì)于每一個(gè)用戶在一個(gè)數(shù)據(jù)庫(kù)上或一個(gè)數(shù)據(jù)表上的 INSERT、SELECT和UPDATE/DELETE的授權(quán)，而MySQL允許你定義一整套的不同的數(shù)據(jù)級(jí)、表級(jí)和列級(jí)的權(quán)限。對(duì)于列級(jí)的權(quán)限， PostgreSQL可以通過(guò)建立視圖，并確定視圖的權(quán)限來(lái)彌補(bǔ)。MySQL還允許你指定基于主機(jī)的權(quán)限，這對(duì)于目前的PostgreSQL是無(wú)法實(shí)現(xiàn)的，但是在很多時(shí)候，這是有用的。
6)存儲(chǔ)引擎插件化機(jī)制
MySQL的存儲(chǔ)引擎插件化機(jī)制，使得它的應(yīng)用場(chǎng)景更加廣泛，比如除了innodb適合事務(wù)處理場(chǎng)景外，myisam適合靜態(tài)數(shù)據(jù)的查詢場(chǎng)景。
7)適應(yīng)24/7運(yùn)行
MySQL可以適應(yīng)24/7運(yùn)行。在絕大多數(shù)情況下，你不需要為MySQL運(yùn)行任何清除程序。PostgreSQL目前仍不完全適應(yīng)24/7運(yùn)行，這是因?yàn)槟惚仨毭扛粢欢螘r(shí)間運(yùn)行一次VACUUM。
8)更加試用于簡(jiǎn)單的場(chǎng)景
PostgreSQL只支持堆表，不支持索引組織表，Innodb只支持索引組織表。
索引組織表的優(yōu)勢(shì)：表內(nèi)的數(shù)據(jù)就是按索引的方式組織，數(shù)據(jù)是有序的，如果數(shù)據(jù)都是按主鍵來(lái)訪問(wèn)，那么訪問(wèn)數(shù)據(jù)比較快。而堆表，按主鍵訪問(wèn)數(shù)據(jù)時(shí)，是需要先按主鍵索引找到數(shù)據(jù)的物理位置。
索引組織表的劣勢(shì)：索引組織表中上再加其它的索引時(shí)，其它的索引記錄的數(shù)據(jù)位置不再是物理位置，而是主鍵值，所以對(duì)于索引組織表來(lái)說(shuō)，主鍵的值不能太大，否則占用的空間比較大。
對(duì)于索引組織表來(lái)說(shuō)，如果每次在中間插入數(shù)據(jù)，可能會(huì)導(dǎo)致索引分裂，索引分裂會(huì)大大降低插入的性能。所以對(duì)于使用innodb來(lái)說(shuō)，我們一般最好讓主鍵是一個(gè)無(wú)意義的序列，這樣插入每次都發(fā)生在最后，以避免這個(gè)問(wèn)題。
由于索引組織表是按一個(gè)索引樹(shù)，一般它訪問(wèn)數(shù)據(jù)塊必須按數(shù)據(jù)塊之間的關(guān)系進(jìn)行訪問(wèn)，而不是按物理塊的訪問(wèn)數(shù)據(jù)的，所以當(dāng)做全表掃描時(shí)要比堆表慢很多，這可能在OLTP中不明顯，但在數(shù)據(jù)倉(cāng)庫(kù)的應(yīng)用中可能是一個(gè)問(wèn)題。
總結(jié)
MySQL從一開(kāi)始就沒(méi)有打算做所有事情，因而它在功能方面有一定的局限性，并不能滿足一些先進(jìn)應(yīng)用程序的要求。MySQL對(duì)某些功能(例如引用、事務(wù)、審計(jì)等)的實(shí)現(xiàn)方式使得它與其他的關(guān)系型數(shù)據(jù)庫(kù)相比缺少了一些可靠性。對(duì)于簡(jiǎn)單繁重的讀取操作，使用PostgreSQL可能有點(diǎn)小題大做，同時(shí)性能也比MySQL這樣的同類(lèi)產(chǎn)品要差。除非你需要絕對(duì)的數(shù)據(jù)完整性，ACID遵從性或者設(shè)計(jì)復(fù)雜，否則PostgreSQL對(duì)于簡(jiǎn)單的場(chǎng)景而言有點(diǎn)多余。
如何你確定只在MySQL和PostgreSQL中進(jìn)行選擇，以下規(guī)則總是有效的：
如果你的操作系統(tǒng)是Windows，你應(yīng)該使用MySQL。
當(dāng)絕對(duì)需要可靠性和數(shù)據(jù)完整性的時(shí)候，PostgreSQL是更好的選擇。
如果需要數(shù)據(jù)庫(kù)執(zhí)行定制程序，那么可擴(kuò)展的PostgreSQL是更好的選擇。
你的應(yīng)用處理的是地理數(shù)據(jù)，由于R-TREES的存在，你應(yīng)該使用PostgreSQL。
如果你對(duì)數(shù)據(jù)庫(kù)并不了十分了解，甚至不知道事務(wù)、存儲(chǔ)過(guò)程等究竟是什么，你應(yīng)該使用MySQL。
重慶分公司
重慶分公司
