postgresql 和postgis區(qū)別是什么？

了存儲(chǔ)、查詢(xún)和修改空間關(guān)系的能力。本文中 ‘PostgreSQL’ 指代基本的關(guān)系數(shù)據(jù)庫(kù)功能，而 ‘PostGIS’ 指代擴(kuò)展的空間操作特性。

商河ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書(shū)銷(xiāo)售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話(huà)聯(lián)系或者加微信：18980820575（備注：SSL證書(shū)合作）期待與您的合作！

客戶(hù)端－服務(wù)器構(gòu)架

PostgreSQL 同眾多數(shù)據(jù)庫(kù)產(chǎn)品一樣，采用客戶(hù)端－服務(wù)器構(gòu)架。客戶(hù)端向服務(wù)器發(fā)出請(qǐng)求并得到響應(yīng)。這種機(jī)制同瀏覽器從網(wǎng)絡(luò)服務(wù)器獲取網(wǎng)頁(yè)類(lèi)似。在 PostgreSQL 中，請(qǐng)求以 SQL 語(yǔ)言發(fā)出，而響應(yīng)多為從數(shù)據(jù)庫(kù)提取的表單。

客戶(hù)端與服務(wù)器可以部署在同一臺(tái)設(shè)備上，即 PostgreSQL 可以在單一的計(jì)算機(jī)上使用。借由系統(tǒng)內(nèi)部的 ‘loopback’ 通信機(jī)制，數(shù)據(jù)庫(kù)系統(tǒng)可以進(jìn)行私密通訊。除非專(zhuān)門(mén)配置，外界是不能訪問(wèn)這些信息的。

本位介紹三種客戶(hù)端：命令行， Quantum GIS ， pgAdmin 圖形化數(shù)據(jù)庫(kù)客戶(hù)端。

創(chuàng)造具有空間信息處理能力的數(shù)據(jù)庫(kù)

命令行客戶(hù)端在終端模擬器（Terminal Emulator）中運(yùn)行。在 Applications 菜單的 Accessories 中打開(kāi)一個(gè)終端模擬器，將顯示一個(gè) Unix 風(fēng)格的命令行界面。輸入：

psql -V

回車(chē)確認(rèn)，將顯示 PostgreSQL 版本號(hào)。

一個(gè) PostgreSQL 服務(wù)器中，可以將不同的任務(wù)組織到不同的數(shù)據(jù)庫(kù)。每個(gè)數(shù)據(jù)庫(kù)獨(dú)立運(yùn)作，擁有專(zhuān)門(mén)的表單、顯示、用戶(hù)等。訪問(wèn) PostgreSQL 數(shù)據(jù)庫(kù)時(shí)將指定一個(gè)數(shù)據(jù)庫(kù)。

服務(wù)器上數(shù)據(jù)庫(kù)列表通過(guò)以下命令查詢(xún)：

psql -l

輸出將羅列 Live 上配置的幾個(gè)數(shù)據(jù)庫(kù)。這里演示新建一個(gè)。

PostgreSQL 使用 createdb 工具創(chuàng)建數(shù)據(jù)庫(kù)。這里建立的數(shù)據(jù)庫(kù)應(yīng)帶有 PostGIS 的擴(kuò)展功能，因此需要指定相應(yīng)的模板。這里將新建數(shù)據(jù)庫(kù)稱(chēng)為 demo 。命令為：

createdb-Ttemplate_postgisdemo

現(xiàn)在執(zhí)行 psql-l 應(yīng)當(dāng)可以看到 demo 數(shù)據(jù)庫(kù)。

也可以使用 SQL 語(yǔ)言創(chuàng)建 PostGIS 數(shù)據(jù)庫(kù)。首先使用 dropdb 命令刪除之前創(chuàng)建的數(shù)據(jù)庫(kù)，然后使用 psql 命令開(kāi)啟 SQL 命令解析器：

dropdbdemopsql-dpostgres

這樣就連接到了一個(gè)通用的系統(tǒng)數(shù)據(jù)庫(kù) postgres 。輸入 SQL 命令建立新數(shù)據(jù)庫(kù)：

postgres=# CREATE DATABASE demo TEMPLATE=template_postgis;

現(xiàn)在可以轉(zhuǎn)換連接到新建的數(shù)據(jù)庫(kù)。若重新連接時(shí)可以使用 psql-ddemo 命令。但在 psql 系統(tǒng)內(nèi)部也可以使用以下命令：

postgres=# \c demo

一個(gè)信息頁(yè)面將顯示當(dāng)前已連接 demo 數(shù)據(jù)庫(kù)。輸入 \dt 列出當(dāng)前數(shù)據(jù)庫(kù)內(nèi)的表單，輸出如下：

demo=# \dtListofrelationsSchema|Name|Type|Owner--------+------------------+-------+-------public|geometry_columns|table|userpublic|spatial_ref_sys|table|user(2rows)

這兩個(gè)表格是 PostGIS 默認(rèn)的。其中 spatial_ref_sys 存儲(chǔ)著合法的空間坐標(biāo)系統(tǒng)。利用 SQL 查詢(xún)查看：

demo=# SELECT srid,auth_name,proj4text FROM spatial_ref_sys LIMIT 10;srid|auth_name|proj4text------+-----------+--------------------------------------3819|EPSG|+proj=longlat+ellps=bessel+towgs...3821|EPSG|+proj=longlat+ellps=aust_SA+no_d...3824|EPSG|+proj=longlat+ellps=GRS80+towgs8...3889|EPSG|+proj=longlat+ellps=GRS80+towgs8...3906|EPSG|+proj=longlat+ellps=bessel+no_de...4001|EPSG|+proj=longlat+ellps=airy+no_defs...4002|EPSG|+proj=longlat+a=6377340.189+b=63...4003|EPSG|+proj=longlat+ellps=aust_SA+no_d...4004|EPSG|+proj=longlat+ellps=bessel+no_de...4005|EPSG|+proj=longlat+a=6377492.018+b=63...(10rows)

以上顯示確認(rèn)了該數(shù)據(jù)庫(kù)已經(jīng)建立空間操作功能。數(shù)據(jù)庫(kù)中的 geometry_columns 用于記錄那些表格是有空間信息的。

手工建立空間數(shù)據(jù)表格

空間數(shù)據(jù)庫(kù)已經(jīng)建立，現(xiàn)在可以建立具有空間信息的表格。

首先建立一個(gè)常規(guī)的表格存儲(chǔ)有關(guān)城市（cities）的信息。這個(gè)表格有兩欄，一個(gè)是 ID 編號(hào)，一個(gè)是城市名：

demo=# CREATE TABLE cities ( id int4, name varchar(50) );

現(xiàn)在添加一個(gè)空間欄用于存儲(chǔ)城市的位置。習(xí)慣上這個(gè)欄目叫做 the_geom 。它記錄了數(shù)據(jù)為什么類(lèi)型（點(diǎn)、線、面）、有幾維（這里是二維）以及空間坐標(biāo)系統(tǒng)。此處使用 EPSG:4326 坐標(biāo)系統(tǒng)：

demo=# SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);

完成后，查詢(xún) cities 表單應(yīng)當(dāng)顯示這個(gè)新欄目。同時(shí)頁(yè)面將顯示當(dāng)前表達(dá)沒(méi)有記錄（0 rows）。

demo=# SELECT * from cities;id|name|the_geom----+------+----------(0rows)

為添加記錄，需要使用 SQL 命令。對(duì)于空間欄，使用 PostGIS 的 ST_GeomFromText 可以將文本轉(zhuǎn)化為坐標(biāo)與參考系號(hào)的記錄：

demo=# INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');demo=# INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');demo=# INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');

當(dāng)然，這樣的輸入方式難以操作。其它方式可以更快的輸入數(shù)據(jù)。就目前來(lái)說(shuō)，表格內(nèi)已經(jīng)有了一些城市數(shù)據(jù)，可以先進(jìn)行查詢(xún)等操作。

簡(jiǎn)單查詢(xún)

標(biāo)準(zhǔn)的 SQL 操作都可以用于 PostGIS 表單：

demo=# SELECT * FROM cities;id|name|the_geom----+-----------------+----------------------------------------------------1|London,England|0101000020E6100000BBB88D06F016C0BF1B2FDD2406C149402|London,Ontario|0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D45403|EastLondon,SA|0101000020E610000040AB064060E93B4059FAD005F58140C0(3rows)

這里的坐標(biāo)是無(wú)法閱讀的 16 進(jìn)制格式。要以 WKT 文本顯示，使用 ST_AsText(the_geom) 或 ST_AsEwkt(the_geom) 函數(shù)。也可以使用 ST_X(the_geom) 和 ST_Y(the_geom) 顯示一個(gè)維度的坐標(biāo)：

demo=# SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM cities;id|st_astext|st_asewkt|st_x|st_y----+------------------------------+----------------------------------------+-------------+-----------1|POINT(-0.125751.508)|SRID=4326;POINT(-0.125751.508)|-0.1257|51.5082|POINT(-81.23342.983)|SRID=4326;POINT(-81.23342.983)|-81.233|42.9833|POINT(27.91162491-33.01529)|SRID=4326;POINT(27.91162491-33.01529)|27.91162491|-33.01529(3rows)

空間查詢(xún)：

PostGIS 為 PostgreSQL 擴(kuò)展了許多空間操作功能。以上已經(jīng)涉及了轉(zhuǎn)換空間坐標(biāo)格式的 ST_GeomFromText 。多數(shù)空間操作以 ST（spatial type）開(kāi)頭，在 PostGIS 文檔相應(yīng)章節(jié)有羅列。這里回答一個(gè)具體的問(wèn)題：以米為單位并假設(shè)地球是完美橢球，上面三個(gè)城市相互的距離是多少？

demo=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id p2.id;name|name|st_distance_sphere-----------------+-----------------+--------------------London,Ontario|London,England|5875766.85191657EastLondon,SA|London,England|9789646.96784908EastLondon,SA|London,Ontario|13892160.9525778(3rows)

輸出顯示了距離數(shù)據(jù)。注意 ‘WHERE’ 部分防止了輸出城市到自身的距離（0）或者兩個(gè)城市不同排列的距離數(shù)據(jù)（London, England 到 London, Ontario 和 London, Ontario 到 London, England 的距離是一樣的）。嘗試取消 ‘WHERE’ 并查看結(jié)果。

這里采取不同的橢球參數(shù)（橢球體名、半主軸長(zhǎng)、扁率）計(jì)算：

demo=# SELECT p1.name,p2.name,ST_Distance_Spheroid(p1.the_geom,p2.the_geom,'SPHEROID["GRS_1980",6378137,298.257222]')FROMcitiesASp1,citiesASp2WHEREp1.idp2.id;name|name|st_distance_spheroid-----------------+-----------------+----------------------London,Ontario|London,England|5892413.63776489EastLondon,SA|London,England|9756842.65711931EastLondon,SA|London,Ontario|13884149.4140698(3rows)

制圖

以 PostGIS 數(shù)據(jù)制圖需要相應(yīng)的客戶(hù)端支持。包括 Quantum GIS、gvSIG、uDig 在內(nèi)的多種客戶(hù)端均可以。以下使用 Quantum GIS：

從 Desktop GIS 菜單啟動(dòng) Quantum GIS 并在其 layer 菜單選擇 AddPostGISlayers 。連接到 Natural Earth PostGIS 數(shù)據(jù)庫(kù)的參數(shù)在 Connections 下拉菜單中有。這里可以定義和儲(chǔ)存其它的配置。點(diǎn)擊 Edit 可以查看具體參數(shù)。點(diǎn)擊 Connect 連接：

?

系統(tǒng)將顯示所有空間信息表供選擇：

?

選擇 lakes 湖泊表單并點(diǎn)擊底部的 Add 添加。頂部的 Load 可以載入新的數(shù)據(jù)庫(kù)連接配置。數(shù)據(jù)將被導(dǎo)入：

?

界面上顯示出湖泊的分布。QGIS 并不理解湖泊一詞的含義，也許不會(huì)自動(dòng)使用藍(lán)色。請(qǐng)查看其手冊(cè)了解如何設(shè)置。這里縮放到加拿大一處著名的湖泊群。

自動(dòng)創(chuàng)建空間數(shù)據(jù)表單

OSGeo Live 的多數(shù)桌面 GIS 系統(tǒng)都可以將 shp 等文件導(dǎo)入數(shù)據(jù)庫(kù)。這里依然使用 QGIS 演示。

QGIS 中導(dǎo)入 shp 可以使用 PostGIS Manager 插件。在 Plugins 菜單選擇 FetchPlugins 導(dǎo)入最新的官方插件列表（需要網(wǎng)絡(luò)連接）。找到 PostGISManager 點(diǎn)擊 Installplugin 安裝。

?

完成后，在 Plugin 菜單點(diǎn)擊 PostGIS Manager 啟動(dòng)。也可以點(diǎn)擊工具欄上大象與地球的圖標(biāo)。

該插件將連接 Natural Earth 數(shù)據(jù)庫(kù)。若提示輸入密碼，留空即可。在開(kāi)啟的界面中，選擇表單可以顯示相應(yīng)的信息。預(yù)覽（Preview）選項(xiàng)卡可以顯示地圖預(yù)覽。這里選擇了 populated places 圖層并縮放到一個(gè)小島：

?

接下來(lái)使用 PostGIS Manager 將 shp 導(dǎo)入數(shù)據(jù)庫(kù)。這里使用 R 統(tǒng)計(jì)擴(kuò)展包含的 North Carolina sudden infant death syndrome (SIDS) 數(shù)據(jù)：

在 Data 菜單選擇 Loaddatafromshapefile 選項(xiàng)。點(diǎn)擊 ... 選中 R maptools 中的 sids.shp 。

分庫(kù)分表 VS newsql數(shù)據(jù)庫(kù)

最近與同行 科技 交流，經(jīng)常被問(wèn)到分庫(kù)分表與分布式數(shù)據(jù)庫(kù)如何選擇，網(wǎng)上也有很多關(guān)于中間件+傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)（分庫(kù)分表）與NewSQL分布式數(shù)據(jù)庫(kù)的文章，但有些觀點(diǎn)與判斷是我覺(jué)得是偏激的，脫離環(huán)境去評(píng)價(jià)方案好壞其實(shí)有失公允。

本文通過(guò)對(duì)兩種模式關(guān)鍵特性實(shí)現(xiàn)原理對(duì)比，希望可以盡可能客觀、中立的闡明各自真實(shí)的優(yōu)缺點(diǎn)以及適用場(chǎng)景。

首先關(guān)于“中間件+關(guān)系數(shù)據(jù)庫(kù)分庫(kù)分表”算不算NewSQL分布式數(shù)據(jù)庫(kù)問(wèn)題，國(guó)外有篇論文pavlo-newsql-sigmodrec，如果根據(jù)該文中的分類(lèi)，Spanner、TiDB、OB算是第一種新架構(gòu)型，Sharding-Sphere、Mycat、DRDS等中間件方案算是第二種（文中還有第三種云數(shù)據(jù)庫(kù)，本文暫不詳細(xì)介紹）。

基于中間件（包括SDK和Proxy兩種形式）+傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)（分庫(kù)分表）模式是不是分布式架構(gòu)？我覺(jué)得是的，因?yàn)榇鎯?chǔ)確實(shí)也分布式了，也能實(shí)現(xiàn)橫向擴(kuò)展。但是不是"偽"分布式數(shù)據(jù)庫(kù)？從架構(gòu)先進(jìn)性來(lái)看，這么說(shuō)也有一定道理。"偽"主要體現(xiàn)在中間件層與底層DB重復(fù)的SQL解析與執(zhí)行計(jì)劃生成、存儲(chǔ)引擎基于B+Tree等，這在分布式數(shù)據(jù)庫(kù)架構(gòu)中實(shí)際上冗余低效的。為了避免引起真?zhèn)畏植际綌?shù)據(jù)庫(kù)的口水戰(zhàn)，本文中NewSQL數(shù)據(jù)庫(kù)特指這種新架構(gòu)NewSQL數(shù)據(jù)庫(kù)。

NewSQL數(shù)據(jù)庫(kù)相比中間件+分庫(kù)分表的先進(jìn)在哪兒？畫(huà)一個(gè)簡(jiǎn)單的架構(gòu)對(duì)比圖：

這些大多也是NewSQL數(shù)據(jù)庫(kù)產(chǎn)品主要宣傳的點(diǎn)，不過(guò)這些看起來(lái)很美好的功能是否真的如此？接下來(lái)針對(duì)以上幾點(diǎn)分別闡述下的我的理解。

這是把雙刃劍。

CAP限制

想想更早些出現(xiàn)的NoSQL數(shù)據(jù)庫(kù)為何不支持分布式事務(wù)（最新版的mongoDB等也開(kāi)始支持了），是缺乏理論與實(shí)踐支撐嗎？并不是，原因是CAP定理依然是分布式數(shù)據(jù)庫(kù)頭上的頸箍咒，在保證強(qiáng)一致的同時(shí)必然會(huì)犧牲可用性A或分區(qū)容忍性P。為什么大部分NoSQL不提供分布式事務(wù)？

那么NewSQL數(shù)據(jù)庫(kù)突破CAP定理限制了嗎？并沒(méi)有。NewSQL數(shù)據(jù)庫(kù)的鼻主Google Spanner（目前絕大部分分布式數(shù)據(jù)庫(kù)都是按照Spanner架構(gòu)設(shè)計(jì)的）提供了一致性和大于5個(gè)9的可用性，宣稱(chēng)是一個(gè)“實(shí)際上是CA”的，其真正的含義是 系統(tǒng)處于 CA 狀態(tài)的概率非常高，由于網(wǎng)絡(luò)分區(qū)導(dǎo)致的服務(wù)停用的概率非常小 ，究其真正原因是其打造私有全球網(wǎng)保證了不會(huì)出現(xiàn)網(wǎng)絡(luò)中斷引發(fā)的網(wǎng)絡(luò)分區(qū)，另外就是其高效的運(yùn)維隊(duì)伍,這也是cloud spanner的賣(mài)點(diǎn)。詳細(xì)可見(jiàn)CAP提出者Eric Brewer寫(xiě)的《Spanner, TrueTime 和CAP理論》。

完備性 ：

兩階段提交協(xié)議是否嚴(yán)格支持ACID，各種異常場(chǎng)景是不是都可以覆蓋？

2PC在commit階段發(fā)送異常，其實(shí)跟最大努力一階段提交類(lèi)似也會(huì)有部分可見(jiàn)問(wèn)題，嚴(yán)格講一段時(shí)間內(nèi)并不能保證A原子性和C一致性（待故障恢復(fù)后recovery機(jī)制可以保證最終的A和C）。完備的分布式事務(wù)支持并不是一件簡(jiǎn)單的事情，需要可以應(yīng)對(duì)網(wǎng)絡(luò)以及各種硬件包括網(wǎng)卡、磁盤(pán)、CPU、內(nèi)存、電源等各類(lèi)異常，通過(guò)嚴(yán)格的測(cè)試。之前跟某友商交流，他們甚至說(shuō)目前已知的NewSQL在分布式事務(wù)支持上都是不完整的，他們都有案例跑不過(guò)，圈內(nèi)人士這么篤定，也說(shuō)明了 分布式事務(wù)的支持完整程度其實(shí)是層次不齊的。

但分布式事務(wù)又是這些NewSQL數(shù)據(jù)庫(kù)的一個(gè)非常重要的底層機(jī)制，跨資源的DML、DDL等都依賴(lài)其實(shí)現(xiàn)，如果這塊的性能、完備性打折扣，上層跨分片SQL執(zhí)行的正確性會(huì)受到很大影響。

性能

傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)也支持分布式事務(wù)XA，但為何很少有高并發(fā)場(chǎng)景下用呢？ 因?yàn)閄A的基礎(chǔ)兩階段提交協(xié)議存在網(wǎng)絡(luò)開(kāi)銷(xiāo)大，阻塞時(shí)間長(zhǎng)、死鎖等問(wèn)題，這也導(dǎo)致了其實(shí)際上很少大規(guī)模用在基于傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)的OLTP系統(tǒng)中。

NewSQL數(shù)據(jù)庫(kù)的分布式事務(wù)實(shí)現(xiàn)也仍然多基于兩階段提交協(xié)議，例如google percolator分布式事務(wù)模型，

采用原子鐘+MVCC+ Snapshot Isolation（SI），這種方式通過(guò)TSO(Timestamp Oracle)保證了全局一致性，通過(guò)MVCC避免了鎖，另外通過(guò)primary lock和secondary lock將提交的一部分轉(zhuǎn)為異步，相比XA確實(shí)提高了分布式事務(wù)的性能。

但不管如何優(yōu)化，相比于1PC，2PC多出來(lái)的GID獲取、網(wǎng)絡(luò)開(kāi)銷(xiāo)、prepare日志持久化還是會(huì)帶來(lái)很大的性能損失，尤其是跨節(jié)點(diǎn)的數(shù)量比較多時(shí)會(huì)更加顯著，例如在銀行場(chǎng)景做個(gè)批量扣款，一個(gè)文件可能上W個(gè)賬戶(hù)，這樣的場(chǎng)景無(wú)論怎么做還是吞吐都不會(huì)很高。

雖然NewSQL分布式數(shù)據(jù)庫(kù)產(chǎn)品都宣傳完備支持分布式事務(wù)，但這并不是說(shuō)應(yīng)用可以完全不用關(guān)心數(shù)據(jù)拆分，這些數(shù)據(jù)庫(kù)的最佳實(shí)踐中仍然會(huì)寫(xiě)到，應(yīng)用的大部分場(chǎng)景盡可能避免分布式事務(wù)。

既然強(qiáng)一致事務(wù)付出的性能代價(jià)太大，我們可以反思下是否真的需要這種強(qiáng)一致的分布式事務(wù)？尤其是在做微服務(wù)拆分后，很多系統(tǒng)也不太可能放在一個(gè)統(tǒng)一的數(shù)據(jù)庫(kù)中。嘗試將一致性要求弱化，便是柔性事務(wù)，放棄ACID(Atomicity,Consistency, Isolation, Durability)，轉(zhuǎn)投BASE(Basically Available,Soft state,Eventually consistent)，例如Saga、TCC、可靠消息保證最終一致等模型，對(duì)于大規(guī)模高并發(fā)OLTP場(chǎng)景，我個(gè)人更建議使用柔性事務(wù)而非強(qiáng)一致的分布式事務(wù)。關(guān)于柔性事務(wù)，筆者之前也寫(xiě)過(guò)一個(gè)技術(shù)組件，最近幾年也涌現(xiàn)出了一些新的模型與框架（例如阿里剛開(kāi)源的Fescar），限于篇幅不再贅述，有空再單獨(dú)寫(xiě)篇文章。

HA與異地多活

主從模式并不是最優(yōu)的方式，就算是半同步復(fù)制，在極端情況下（半同步轉(zhuǎn)異步）也存在丟數(shù)問(wèn)題，目前業(yè)界公認(rèn)更好的方案是基于paxos分布式一致性協(xié)議或者其它類(lèi)paxos如raft方式，Google Spanner、TiDB、cockcoachDB、OB都采用了這種方式，基于Paxos協(xié)議的多副本存儲(chǔ)，遵循過(guò)半寫(xiě)原則，支持自動(dòng)選主，解決了數(shù)據(jù)的高可靠，縮短了failover時(shí)間，提高了可用性，特別是減少了運(yùn)維的工作量，這種方案技術(shù)上已經(jīng)很成熟，也是NewSQL數(shù)據(jù)庫(kù)底層的標(biāo)配。

當(dāng)然這種方式其實(shí)也可以用在傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)，阿里、微信團(tuán)隊(duì)等也有將MySQL存儲(chǔ)改造支持paxos多副本的，MySQL也推出了官方版MySQL Group Cluster，預(yù)計(jì)不遠(yuǎn)的未來(lái)主從模式可能就成為 歷史 了。

需要注意的是很多NewSQL數(shù)據(jù)庫(kù)廠商宣傳基于paxos或raft協(xié)議可以實(shí)現(xiàn)【異地多活】，這個(gè)實(shí)際上是有前提的，那就是異地之間網(wǎng)絡(luò)延遲不能太高 。以銀行“兩地三中心”為例，異地之間多相隔數(shù)千里，延時(shí)達(dá)到數(shù)十毫秒，如果要多活，那便需異地副本也參與數(shù)據(jù)庫(kù)日志過(guò)半確認(rèn)，這樣高的延時(shí)幾乎沒(méi)有OLTP系統(tǒng)可以接受的。

數(shù)據(jù)庫(kù)層面做異地多活是個(gè)美好的愿景，但距離導(dǎo)致的延時(shí)目前并沒(méi)有好的方案。 之前跟螞蟻團(tuán)隊(duì)交流，螞蟻異地多活的方案是在應(yīng)用層通過(guò)MQ同步雙寫(xiě)交易信息，異地DC將交易信息保存在分布式緩存中，一旦發(fā)生異地切換，數(shù)據(jù)庫(kù)同步中間件會(huì)告之?dāng)?shù)據(jù)延遲時(shí)間，應(yīng)用從緩存中讀取交易信息，將這段時(shí)間內(nèi)涉及到的業(yè)務(wù)對(duì)象例如用戶(hù)、賬戶(hù)進(jìn)行黑名單管理，等數(shù)據(jù)同步追上之后再將這些業(yè)務(wù)對(duì)象從黑名單中剔除。由于雙寫(xiě)的不是所有數(shù)據(jù)庫(kù)操作日志而只是交易信息，數(shù)據(jù)延遲只影響一段時(shí)間內(nèi)數(shù)據(jù)，這是目前我覺(jué)得比較靠譜的異地度多活方案。

另外有些系統(tǒng)進(jìn)行了單元化改造，這在paxos選主時(shí)也要結(jié)合考慮進(jìn)去，這也是目前很多NewSQL數(shù)據(jù)庫(kù)欠缺的功能。

Scale橫向擴(kuò)展與分片機(jī)制

paxos算法解決了高可用、高可靠問(wèn)題，并沒(méi)有解決Scale橫向擴(kuò)展的問(wèn)題，所以分片是必須支持的。NewSQL數(shù)據(jù)庫(kù)都是天生內(nèi)置分片機(jī)制的，而且會(huì)根據(jù)每個(gè)分片的數(shù)據(jù)負(fù)載(磁盤(pán)使用率、寫(xiě)入速度等)自動(dòng)識(shí)別熱點(diǎn)，然后進(jìn)行分片的分裂、數(shù)據(jù)遷移、合并，這些過(guò)程應(yīng)用是無(wú)感知的，這省去了DBA的很多運(yùn)維工作量。以TiDB為例，它將數(shù)據(jù)切成region，如果region到64M時(shí)，數(shù)據(jù)自動(dòng)進(jìn)行遷移。

分庫(kù)分表模式下需要應(yīng)用設(shè)計(jì)之初就要明確各表的拆分鍵、拆分方式（range、取模、一致性哈希或者自定義路由表）、路由規(guī)則、拆分庫(kù)表數(shù)量、擴(kuò)容方式等。相比NewSQL數(shù)據(jù)庫(kù)，這種模式給應(yīng)用帶來(lái)了很大侵入和復(fù)雜度，這對(duì)大多數(shù)系統(tǒng)來(lái)說(shuō)也是一大挑戰(zhàn)。

這里有個(gè)問(wèn)題是NewSQL數(shù)據(jù)庫(kù)統(tǒng)一的內(nèi)置分片策略（例如tidb基于range）可能并不是最高效的，因?yàn)榕c領(lǐng)域模型中的劃分要素并不一致，這導(dǎo)致的后果是很多交易會(huì)產(chǎn)生分布式事務(wù)。 舉個(gè)例子，銀行核心業(yè)務(wù)系統(tǒng)是以客戶(hù)為維度，也就是說(shuō)客戶(hù)表、該客戶(hù)的賬戶(hù)表、流水表在絕大部分場(chǎng)景下是一起寫(xiě)的，但如果按照各表主鍵range進(jìn)行分片，這個(gè)交易并不能在一個(gè)分片上完成，這在高頻OLTP系統(tǒng)中會(huì)帶來(lái)性能問(wèn)題。

分布式SQL支持

常見(jiàn)的單分片SQL，這兩者都能很好支持。NewSQL數(shù)據(jù)庫(kù)由于定位與目標(biāo)是一個(gè)通用的數(shù)據(jù)庫(kù)，所以支持的SQL會(huì)更完整，包括跨分片的join、聚合等復(fù)雜SQL。中間件模式多面向應(yīng)用需求設(shè)計(jì)，不過(guò)大部分也支持帶拆分鍵SQL、庫(kù)表遍歷、單庫(kù)join、聚合、排序、分頁(yè)等。但對(duì)跨庫(kù)的join以及聚合支持就不夠了。

NewSQL數(shù)據(jù)庫(kù)一般并不支持存儲(chǔ)過(guò)程、視圖、外鍵等功能，而中間件模式底層就是傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)，這些功能如果只是涉及單庫(kù)是比較容易支持的。

NewSQL數(shù)據(jù)庫(kù)往往選擇兼容MySQL或者PostgreSQL協(xié)議，所以SQL支持僅局限于這兩種，中間件例如驅(qū)動(dòng)模式往往只需做簡(jiǎn)單的SQL解析、計(jì)算路由、SQL重寫(xiě)，所以可以支持更多種類(lèi)的數(shù)據(jù)庫(kù)SQL。

SQL支持的差異主要在于分布式SQL執(zhí)行計(jì)劃生成器，由于NewSQL數(shù)據(jù)庫(kù)具有底層數(shù)據(jù)的分布、統(tǒng)計(jì)信息，因此可以做CBO，生成的執(zhí)行計(jì)劃效率更高，而中間件模式下沒(méi)有這些信息，往往只能基于規(guī)則RBO（Rule-Based-Opimization），這也是為什么中間件模式一般并不支持跨庫(kù)join，因?yàn)閷?shí)現(xiàn)了效率也往往并不高，還不如交給應(yīng)用去做。

存儲(chǔ)引擎

傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)的存儲(chǔ)引擎設(shè)計(jì)都是面向磁盤(pán)的，大多都基于B+樹(shù)。B+樹(shù)通過(guò)降低樹(shù)的高度減少隨機(jī)讀、進(jìn)而減少磁盤(pán)尋道次數(shù)，提高讀的性能，但大量的隨機(jī)寫(xiě)會(huì)導(dǎo)致樹(shù)的分裂，從而帶來(lái)隨機(jī)寫(xiě)，導(dǎo)致寫(xiě)性能下降。NewSQL的底層存儲(chǔ)引擎則多采用LSM，相比B+樹(shù)LSM將對(duì)磁盤(pán)的隨機(jī)寫(xiě)變成順序?qū)懀蟠筇岣吡藢?xiě)的性能。不過(guò)LSM的的讀由于需要合并數(shù)據(jù)性能比B+樹(shù)差，一般來(lái)說(shuō)LSM更適合應(yīng)在寫(xiě)大于讀的場(chǎng)景。當(dāng)然這只是單純數(shù)據(jù)結(jié)構(gòu)角度的對(duì)比，在數(shù)據(jù)庫(kù)實(shí)際實(shí)現(xiàn)時(shí)還會(huì)通過(guò)SSD、緩沖、bloom filter等方式優(yōu)化讀寫(xiě)性能，所以讀性能基本不會(huì)下降太多。NewSQL數(shù)據(jù)由于多副本、分布式事務(wù)等開(kāi)銷(xiāo)，相比單機(jī)關(guān)系數(shù)據(jù)庫(kù)SQL的響應(yīng)時(shí)間并不占優(yōu)，但由于集群的彈性擴(kuò)展，整體QPS提升還是很明顯的，這也是NewSQL數(shù)據(jù)庫(kù)廠商說(shuō)分布式數(shù)據(jù)庫(kù)更看重的是吞吐，而不是單筆SQL響應(yīng)時(shí)間的原因。

成熟度與生態(tài)

分布式數(shù)據(jù)庫(kù)是個(gè)新型通用底層軟件，準(zhǔn)確的衡量與評(píng)價(jià)需要一個(gè)多維度的測(cè)試模型，需包括發(fā)展現(xiàn)狀、使用情況、社區(qū)生態(tài)、監(jiān)控運(yùn)維、周邊配套工具、功能滿(mǎn)足度、DBA人才、SQL兼容性、性能測(cè)試、高可用測(cè)試、在線擴(kuò)容、分布式事務(wù)、隔離級(jí)別、在線DDL等等，雖然NewSQL數(shù)據(jù)庫(kù)發(fā)展經(jīng)過(guò)了一定時(shí)間檢驗(yàn)，但多集中在互聯(lián)網(wǎng)以及傳統(tǒng)企業(yè)非核心交易系統(tǒng)中，目前還處于快速迭代、規(guī)模使用不斷優(yōu)化完善的階段。

相比而言，傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)則經(jīng)過(guò)了多年的發(fā)展，通過(guò)完整的評(píng)測(cè)，在成熟度、功能、性能、周邊生態(tài)、風(fēng)險(xiǎn)把控、相關(guān)人才積累等多方面都具有明顯優(yōu)勢(shì)，同時(shí)對(duì)已建系統(tǒng)的兼容性也更好。

對(duì)于互聯(lián)網(wǎng)公司，數(shù)據(jù)量的增長(zhǎng)壓力以及追求新技術(shù)的基因會(huì)更傾向于嘗試NewSQL數(shù)據(jù)庫(kù)，不用再考慮庫(kù)表拆分、應(yīng)用改造、擴(kuò)容、事務(wù)一致性等問(wèn)題怎么看都是非常吸引人的方案。

對(duì)于傳統(tǒng)企業(yè)例如銀行這種風(fēng)險(xiǎn)意識(shí)較高的行業(yè)來(lái)說(shuō)，NewSQL數(shù)據(jù)庫(kù)則可能在未來(lái)一段時(shí)間內(nèi)仍處于 探索 、審慎試點(diǎn)的階段?；谥虚g件+分庫(kù)分表模式架構(gòu)簡(jiǎn)單，技術(shù)門(mén)檻更低，雖然沒(méi)有NewSQL數(shù)據(jù)庫(kù)功能全面，但大部分場(chǎng)景最核心的訴求也就是拆分后SQL的正確路由，而此功能中間件模式應(yīng)對(duì)還是綽綽有余的，可以說(shuō)在大多數(shù)OLTP場(chǎng)景是夠用的。

限于篇幅，其它特性例如在線DDL、數(shù)據(jù)遷移、運(yùn)維工具等特性就不在本文展開(kāi)對(duì)比。

總結(jié)

如果看完以上內(nèi)容，您還不知道選哪種模式，那么結(jié)合以下幾個(gè)問(wèn)題，先思考下NewSQL數(shù)據(jù)庫(kù)解決的點(diǎn)對(duì)于自身是不是真正的痛點(diǎn)：

如果以上有2到3個(gè)是肯定的，那么你可以考慮用NewSQL數(shù)據(jù)庫(kù)了，雖然前期可能需要一定的學(xué)習(xí)成本，但它是數(shù)據(jù)庫(kù)的發(fā)展方向，未來(lái)收益也會(huì)更高，尤其是互聯(lián)網(wǎng)行業(yè)，隨著數(shù)據(jù)量的突飛猛進(jìn)，分庫(kù)分表帶來(lái)的痛苦會(huì)與日俱增。當(dāng)然選擇NewSQL數(shù)據(jù)庫(kù)你也要做好承擔(dān)一定風(fēng)險(xiǎn)的準(zhǔn)備。

如果你還未做出抉擇，不妨再想想下面幾個(gè)問(wèn)題：

如果這些問(wèn)題有多數(shù)是肯定的，那還是分庫(kù)分表吧。在軟件領(lǐng)域很少有完美的解決方案，NewSQL數(shù)據(jù)庫(kù)也不是數(shù)據(jù)分布式架構(gòu)的銀彈。相比而言分庫(kù)分表是一個(gè)代價(jià)更低、風(fēng)險(xiǎn)更小的方案，它最大程度復(fù)用傳統(tǒng)關(guān)系數(shù)據(jù)庫(kù)生態(tài)，通過(guò)中間件也可以滿(mǎn)足分庫(kù)分表后的絕大多數(shù)功能，定制化能力更強(qiáng)。 在當(dāng)前NewSQL數(shù)據(jù)庫(kù)還未完全成熟的階段，分庫(kù)分表可以說(shuō)是一個(gè)上限低但下限高的方案，尤其傳統(tǒng)行業(yè)的核心系統(tǒng)，如果你仍然打算把數(shù)據(jù)庫(kù)當(dāng)做一個(gè)黑盒產(chǎn)品來(lái)用，踏踏實(shí)實(shí)用好分庫(kù)分表會(huì)被認(rèn)為是個(gè)穩(wěn)妥的選擇。

很多時(shí)候軟件選型取決于領(lǐng)域特征以及架構(gòu)師風(fēng)格，限于筆者知識(shí)與所屬行業(yè)特點(diǎn)所限，以上僅為個(gè)人粗淺的一些觀點(diǎn)，歡迎討論。

數(shù)據(jù)庫(kù)為什么要分庫(kù)分表

1 基本思想之什么是分庫(kù)分表？

從字面上簡(jiǎn)單理解，就是把原本存儲(chǔ)于一個(gè)庫(kù)的數(shù)據(jù)分塊存儲(chǔ)到多個(gè)庫(kù)上，把原本存儲(chǔ)于一個(gè)表的數(shù)據(jù)分塊存儲(chǔ)到多個(gè)表上。

2 基本思想之為什么要分庫(kù)分表？

數(shù)

據(jù)庫(kù)中的數(shù)據(jù)量不一定是可控的，在未進(jìn)行分庫(kù)分表的情況下，隨著時(shí)間和業(yè)務(wù)的發(fā)展，庫(kù)中的表會(huì)越來(lái)越多，表中的數(shù)據(jù)量也會(huì)越來(lái)越大，相應(yīng)地，數(shù)據(jù)操作，增

刪改查的開(kāi)銷(xiāo)也會(huì)越來(lái)越大；另外，由于無(wú)法進(jìn)行分布式式部署，而一臺(tái)服務(wù)器的資源（CPU、磁盤(pán)、內(nèi)存、IO等）是有限的，最終數(shù)據(jù)庫(kù)所能承載的數(shù)據(jù)量、

數(shù)據(jù)處理能力都將遭遇瓶頸。

3 分庫(kù)分表的實(shí)施策略。

分庫(kù)分表有垂直切分和水平切分兩種。

3.1

何謂垂直切分，即將表按照功能模塊、關(guān)系密切程度劃分出來(lái)，部署到不同的庫(kù)上。例如，我們會(huì)建立定義數(shù)據(jù)庫(kù)workDB、商品數(shù)據(jù)庫(kù)payDB、用戶(hù)數(shù)據(jù)

庫(kù)userDB、日志數(shù)據(jù)庫(kù)logDB等，分別用于存儲(chǔ)項(xiàng)目數(shù)據(jù)定義表、商品定義表、用戶(hù)數(shù)據(jù)表、日志數(shù)據(jù)表等。

3.2

何謂水平切分，當(dāng)一個(gè)表中的數(shù)據(jù)量過(guò)大時(shí)，我們可以把該表的數(shù)據(jù)按照某種規(guī)則，例如userID散列，進(jìn)行劃分，然后存儲(chǔ)到多個(gè)結(jié)構(gòu)相同的表，和不同的庫(kù)

上。例如，我們的userDB中的用戶(hù)數(shù)據(jù)表中，每一個(gè)表的數(shù)據(jù)量都很大，就可以把userDB切分為結(jié)構(gòu)相同的多個(gè)userDB：part0DB、

part1DB等，再將userDB上的用戶(hù)數(shù)據(jù)表userTable，切分為很多userTable：userTable0、userTable1等，

然后將這些表按照一定的規(guī)則存儲(chǔ)到多個(gè)userDB上。

3.3 應(yīng)該使用哪一種方式來(lái)實(shí)施數(shù)據(jù)庫(kù)分庫(kù)分表，這要看數(shù)據(jù)庫(kù)中數(shù)據(jù)量的瓶頸所在，并綜合項(xiàng)目的業(yè)務(wù)類(lèi)型進(jìn)行考慮。

如果數(shù)據(jù)庫(kù)是因?yàn)楸硖喽斐珊Ａ繑?shù)據(jù)，并且項(xiàng)目的各項(xiàng)業(yè)務(wù)邏輯劃分清晰、低耦合，那么規(guī)則簡(jiǎn)單明了、容易實(shí)施的垂直切分必是首選。

而

如果數(shù)據(jù)庫(kù)中的表并不多，但單表的數(shù)據(jù)量很大、或數(shù)據(jù)熱度很高，這種情況之下就應(yīng)該選擇水平切分，水平切分比垂直切分要復(fù)雜一些，它將原本邏輯上屬于一體

的數(shù)據(jù)進(jìn)行了物理分割，除了在分割時(shí)要對(duì)分割的粒度做好評(píng)估，考慮數(shù)據(jù)平均和負(fù)載平均，后期也將對(duì)項(xiàng)目人員及應(yīng)用程序產(chǎn)生額外的數(shù)據(jù)管理負(fù)擔(dān)。

在現(xiàn)實(shí)項(xiàng)目中，往往是這兩種情況兼而有之，這就需要做出權(quán)衡，甚至既需要垂直切分，又需要水平切分。我們的游戲項(xiàng)目便綜合使用了垂直與水平切分，我們首先對(duì)數(shù)據(jù)庫(kù)進(jìn)行垂直切分，然后，再針對(duì)一部分表，通常是用戶(hù)數(shù)據(jù)表，進(jìn)行水平切分。

4 分庫(kù)分表存在的問(wèn)題。

4.1 事務(wù)問(wèn)題。

在執(zhí)行分庫(kù)分表之后，由于數(shù)據(jù)存儲(chǔ)到了不同的庫(kù)上，數(shù)據(jù)庫(kù)事務(wù)管理出現(xiàn)了困難。如果依賴(lài)數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價(jià)；如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會(huì)造成編程方面的負(fù)擔(dān)。

4.2 跨庫(kù)跨表的join問(wèn)題。

在執(zhí)行了分庫(kù)分表之后，難以避免會(huì)將原本邏輯關(guān)聯(lián)性很強(qiáng)的數(shù)據(jù)劃分到不同的表、不同的庫(kù)上，這時(shí)，表的關(guān)聯(lián)操作將受到限制，我們無(wú)法join位于不同分庫(kù)的表，也無(wú)法join分表粒度不同的表，結(jié)果原本一次查詢(xún)能夠完成的業(yè)務(wù)，可能需要多次查詢(xún)才能完成。

4.3 額外的數(shù)據(jù)管理負(fù)擔(dān)和數(shù)據(jù)運(yùn)算壓力。

額

外的數(shù)據(jù)管理負(fù)擔(dān)，最顯而易見(jiàn)的就是數(shù)據(jù)的定位問(wèn)題和數(shù)據(jù)的增刪改查的重復(fù)執(zhí)行問(wèn)題，這些都可以通過(guò)應(yīng)用程序解決，但必然引起額外的邏輯運(yùn)算，例如，對(duì)于

一個(gè)記錄用戶(hù)成績(jī)的用戶(hù)數(shù)據(jù)表userTable，業(yè)務(wù)要求查出成績(jī)最好的100位，在進(jìn)行分表之前，只需一個(gè)order

by語(yǔ)句就可以搞定，但是在進(jìn)行分表之后，將需要n個(gè)order

by語(yǔ)句，分別查出每一個(gè)分表的前100名用戶(hù)數(shù)據(jù)，然后再對(duì)這些數(shù)據(jù)進(jìn)行合并計(jì)算，才能得出結(jié)果。

如何使用Navicat for PostgreSQL域

Navicat for PostgreSQL 11.1.13 最新版域常規(guī)屬性：

基礎(chǔ)類(lèi)型類(lèi)別：可選擇的基礎(chǔ)數(shù)據(jù)類(lèi)型有：Base Type、Composite Type、Enum Type 和 Domain。需要注意的是，僅支持 PostgreSQL 8.2 或以上版本。

基礎(chǔ)類(lèi)型模式：選擇基礎(chǔ)數(shù)據(jù)類(lèi)型的模式。

基礎(chǔ)類(lèi)型：從下拉列表選擇域的基礎(chǔ)數(shù)據(jù)類(lèi)型。

維度：數(shù)組符的維度。

長(zhǎng)度和比例：使用“長(zhǎng)度”編輯框定義欄位的長(zhǎng)度，使用“比例”編輯框定義小數(shù)點(diǎn)之后的位數(shù)。

默認(rèn)：DEFAULT 子句為域數(shù)據(jù)類(lèi)型列指定默認(rèn)值，該值是任何沒(méi)有變量的表達(dá)式（但是子查詢(xún)是不允許），默認(rèn)表達(dá)式的數(shù)據(jù)類(lèi)型必須符合域的數(shù)據(jù)類(lèi)型。如果沒(méi)有指定默認(rèn)值，那么默認(rèn)值是 null 值。

默認(rèn)表達(dá)式將用于任何沒(méi)有為列指定值的插入操作，如果一個(gè)特定的列已定義默認(rèn)值，它會(huì)無(wú)視任何關(guān)于域的默認(rèn)。反過(guò)來(lái)，域默認(rèn)會(huì)無(wú)視任何關(guān)于基礎(chǔ)數(shù)據(jù)類(lèi)型的默認(rèn)值。

不是 null：域的值不允許是 null。

擁有者：域函數(shù)的擁有者，定義域的用戶(hù)成為其擁有者。需要注意的是，支持 PostgreSQL 7.4 或以上版本。

檢查

檢查選項(xiàng)卡為管理域檢查提供。方便用戶(hù)創(chuàng)建新的、編輯或刪除選定的檢查。CHECK 子句指定完整性限制或測(cè)試，域的值必須符合。每個(gè)限制必須是一個(gè)生成 Boolean（布爾）結(jié)果的表達(dá)式，它應(yīng)使用 VALUE 關(guān)鍵字來(lái)參照被測(cè)試的值。

postgresql 怎么自動(dòng)創(chuàng)建分區(qū)

在數(shù)據(jù)庫(kù)日漸龐大的今天，為了方便對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的管理，比如按時(shí)間，按地區(qū)去統(tǒng)計(jì)一些數(shù)據(jù)時(shí)，基數(shù)過(guò)于龐大，多有不便。很多商業(yè)數(shù)據(jù)庫(kù)都提供分區(qū)的概念，按不同的維度去存放數(shù)據(jù)，便于后期的管理，PostgreSQL也不例外。

PostgresSQL分區(qū)的意思是把邏輯上的一個(gè)大表分割成物理上的幾塊兒。分區(qū)不僅能帶來(lái)訪問(wèn)速度的提升，關(guān)鍵的是，它能帶來(lái)管理和維護(hù)上的方便。

分區(qū)的具體好處是：

某些類(lèi)型的查詢(xún)性能可以得到極大提升。

更新的性能也可以得到提升，因?yàn)楸淼拿繅K的索引要比在整個(gè)數(shù)據(jù)集上的索引要小。如果索引不能全部放在內(nèi)存里，那么在索引上的讀和寫(xiě)都會(huì)產(chǎn)生更多的磁盤(pán)訪問(wèn)。

批量刪除可以用簡(jiǎn)單的刪除某個(gè)分區(qū)來(lái)實(shí)現(xiàn)。

可以將很少用的數(shù)據(jù)移動(dòng)到便宜的、轉(zhuǎn)速慢的存儲(chǔ)介質(zhì)上。

在PG里表分區(qū)是通過(guò)表繼承來(lái)實(shí)現(xiàn)的，一般都是建立一個(gè)主表，里面是空，然后每個(gè)分區(qū)都去繼承它。無(wú)論何時(shí)，都應(yīng)保證主表里面是空的。

小表分區(qū)不實(shí)際，表在多大情況下才考慮分區(qū)呢？PostgresSQL官方給出的建議是：當(dāng)表本身大小超過(guò)了機(jī)器物理內(nèi)存的實(shí)際大小時(shí)（the size of the table should exceed the physical memory of the database server），可以考慮分區(qū)。

PG目前（9.2.2）僅支持范圍分區(qū)和列表分區(qū)，尚未支持散列分區(qū)。

二、環(huán)境

系統(tǒng)環(huán)境：CentOS release 6.3 (Final)

PostgreSQL版本：PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、實(shí)現(xiàn)分區(qū)

3.1 創(chuàng)建主表

david=# create table tbl_partition (

david(# id integer,

david(# name varchar(20),

david(# gender boolean,

david(# join_date date,

david(# dept char(4));

CREATE TABLE

david=#

3.2 創(chuàng)建分區(qū)表

david=# create table tbl_partition_201211 (

check ( join_date = DATE '2012-11-01' AND join_date DATE '2012-12-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201212 (

check ( join_date = DATE '2012-12-01' AND join_date DATE '2013-01-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201301 (

check ( join_date = DATE '2013-01-01' AND join_date DATE '2013-02-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201302 (

check ( join_date = DATE '2013-02-01' AND join_date DATE '2013-03-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201303 (

check ( join_date = DATE '2013-03-01' AND join_date DATE '2013-04-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201304 (

check ( join_date = DATE '2013-04-01' AND join_date DATE '2013-05-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=# create table tbl_partition_201305 (

check ( join_date = DATE '2013-05-01' AND join_date DATE '2013-06-01' )

) INHERITS (tbl_partition);

CREATE TABLE

david=#

3.3 分區(qū)鍵上建索引

david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);

CREATE INDEX

david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date);

CREATE INDEX

david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);

CREATE INDEX

david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date);

CREATE INDEX

david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date);

CREATE INDEX

david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date);

CREATE INDEX

david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date);

CREATE INDEX

david=#

對(duì)于開(kāi)發(fā)人員來(lái)說(shuō)，希望數(shù)據(jù)庫(kù)是透明的，只管 insert into tbl_partition。對(duì)于數(shù)據(jù)插向哪個(gè)分區(qū)，則希望由DB決定。這點(diǎn)，ORACLE實(shí)現(xiàn)了，但是PG不行，需要前期人工處理下。

3.4 創(chuàng)建觸發(fā)器函數(shù)

david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.join_date = DATE '2012-11-01' AND

NEW.join_date DATE '2012-12-01' ) THEN

INSERT INTO tbl_partition_201211 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2012-12-01' AND

NEW.join_date DATE '2013-01-01' ) THEN

INSERT INTO tbl_partition_201212 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-01-01' AND

NEW.join_date DATE '2013-02-01' ) THEN

INSERT INTO tbl_partition_201301 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-02-01' AND

NEW.join_date DATE '2013-03-01' ) THEN

INSERT INTO tbl_partition_201302 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-03-01' AND

NEW.join_date DATE '2013-04-01' ) THEN

INSERT INTO tbl_partition_201303 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-04-01' AND

NEW.join_date DATE '2013-05-01' ) THEN

INSERT INTO tbl_partition_201304 VALUES (NEW.*);

ELSIF ( NEW.join_date = DATE '2013-05-01' AND

NEW.join_date DATE '2013-06-01' ) THEN

INSERT INTO tbl_partition_201305 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE FUNCTION

david=#

說(shuō)明：如果不想丟失數(shù)據(jù)，上面的ELSE 條件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同時(shí)需要?jiǎng)?chuàng)建一張結(jié)構(gòu)和tbl_partition 一樣的表tbl_partition_error_join_date，這樣，錯(cuò)誤的join_date 數(shù)據(jù)就可以插入到這張表中而不是報(bào)錯(cuò)了。

附上出處鏈接：

postgresql----數(shù)組類(lèi)型和函數(shù)

postgresql支持?jǐn)?shù)組類(lèi)型，可以是基本類(lèi)型，也可以是用戶(hù)自定義的類(lèi)型。日常中使用數(shù)組類(lèi)型的機(jī)會(huì)不多，但還是可以了解一下。不像C或JAVA高級(jí)語(yǔ)言的數(shù)組下標(biāo)從0開(kāi)始，postgresql數(shù)組下標(biāo)從1開(kāi)始，既可以指定長(zhǎng)度，也可以不指定長(zhǎng)度。且postgresql既支持一維數(shù)組，也支持多維數(shù)組，但是平時(shí)二維數(shù)組也就夠用了。

示例1.使用ARRAY構(gòu)建數(shù)組類(lèi)型

---1*4的一維數(shù)組test=#selectarray[1,2,3,4];

array? -----------{1,2,3,4}

(1 row)--2*2的二維數(shù)組test=#selectarray[[1,2],[3,4]];

array? ? ---------------{{1,2},{3,4}}

(1 row)--1*2的二維數(shù)組，基本類(lèi)型為box矩形類(lèi)型，且和上面相比box類(lèi)型元素之間是以分號(hào)分隔的，其他所有類(lèi)型的數(shù)據(jù)都是以逗號(hào)分隔的test=#selectarray[box'(1,1),(0,0)',box'(0,0),(-1,-1)'];

? ? ? ? array? ? ? ? ? ? -----------------------------{(1,1),(0,0);(0,0),(-1,-1)}

(1row)

示例2.創(chuàng)建一張表，字段包含數(shù)組類(lèi)型

其中int[]表示數(shù)組長(zhǎng)度無(wú)限制，int[4]表示數(shù)組長(zhǎng)度為4.

test=#createtabletbl_array(aint[],bvarchar(32)[][],cint);CREATETABLEtest=#insertintotbl_array (a,b,c)values(array[1,2],array[[1,2,3],[4,5,6]],1);INSERT01test=#insertintotbl_array (a,b,c)values(array[1,2,3],array[[1,2],[4,5]],2);INSERT01test=#select*from tbl_array ;

a? ? |b| c ---------+-------------------+---{1,2}|{{1,2,3},{4,5,6}}|1 {1,2,3}|{{1,2},{4,5}}|2(2 rows)

test=#selecta[1],b[2]fromtbl_arraywherec=1;

a | b ---+---1|

(1 row)

test=#selecta[1],b[2][1]fromtbl_arraywherec=1;

a | b ---+---1|4(1 row)

test=#selecta[1],b[2][4]fromtbl_arraywherec=1;

a | b ---+---1|

(1row)

test=#updatetbl_arrayseta[1]=200wherea[1]=1;UPDATE1test=#selecta[1],b[2][4]from tbl_array ;

a? | b -----+---100|200|

(2rows)

也可以使用[下標(biāo):上標(biāo)]方式來(lái)查詢(xún)數(shù)組連續(xù)的某些元素。

test=#selecta[2:3]from tbl_array ;

a? -------{2}

{2,3}

(2 rows)

test=#selecta[1:3]from tbl_array ;

a? ? -----------{100,2}

{200,2,3}

(2rows)

數(shù)組操作符與函數(shù)

操作符

操作符描述示例結(jié)果

=相等?SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3];?t

不等于?select ARRAY[1,2,3] ARRAY[1,2,4];?t

小于?select ARRAY[1,2,3] ARRAY[1,2,4];?t

大于?select ARRAY[1,4,3] ARRAY[1,2,4];?t

=小于或等于?select ARRAY[1,2,3] = ARRAY[1,2,3];?t

=大于或等于?select ARRAY[1,4,3] = ARRAY[1,4,3];?t

@包含?select ARRAY[1,4,3] @ ARRAY[3,1];?t

@包含于?select ARRAY[2,7] @ ARRAY[1,7,4,2,6];?t

重疊（是否有相同元素）?select ARRAY[1,4,3] ARRAY[2,1];?t

||數(shù)組與數(shù)組連接?select ARRAY[1,2,3] || ARRAY[4,5,6];?{1,2,3,4,5,6}

||數(shù)組與數(shù)組連接?select ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]];?{{1,2,3},{4,5,6},{7,8,9}}

||元素與數(shù)組連接?select 3 || ARRAY[4,5,6];?{3,4,5,6}

||數(shù)組與元素連接?select ARRAY[4,5,6] || 7;?{4,5,6,7}

函數(shù)

函數(shù)返回類(lèi)型描述示例結(jié)果

array_append(anyarray,anyelement)anyarray?在數(shù)組末尾追加元素?

SELECT array_append(ARRAY[1,2], 3);

{1,2,3}

array_cat(anyarray,anyarray)anyarray?連接兩個(gè)數(shù)組?SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);?{1,2,3,4,5}

array_ndims(anyarray)int?返回?cái)?shù)組維數(shù)?SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);?2

array_dims(anyarray)text?返回?cái)?shù)組維數(shù)的文本表示?SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);?[1:2][1:3]

array_fill(anyelement,int[], [,int[]])anyarray使用提供的值和維度初始化一個(gè)數(shù)組，其中anyelement是值，第一個(gè)int[]是數(shù)組的長(zhǎng)度，第二個(gè)int[]是數(shù)組下界，下界默認(rèn)是1?SELECT array_fill(7, ARRAY[3], ARRAY[2]);?[2:4]={7,7,7}

array_length(anyarray,int)int?返回?cái)?shù)組指定維度的長(zhǎng)度?SELECT array_length(array[1,2,3], 1);?3

array_lower(anyarray,int)int?返回?cái)?shù)組指定維度的下界?SELECT array_lower('[0:2]={1,2,3}'::int[], 1);?0

array_position(anyarray,anyelement[,int])int?返回?cái)?shù)組元素anyelement從數(shù)組的[,int]位置（默認(rèn)為1）開(kāi)始第一次出現(xiàn)在數(shù)組中的位置，數(shù)組必須是一維的?SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');?2

array_positions(anyarray,anyelement)int[]?返回元素在數(shù)組中的所有位置?SELECT array_positions(ARRAY['A','A','B','A'], 'A');?{1,2,4}

array_prepend(anyelement,anyarray)anyarray?在數(shù)組開(kāi)頭添加新的元素?SELECT array_prepend(1, ARRAY[2,3]);?{1,2,3}

array_remove(anyarray,anyelement)anyarray?從數(shù)組中刪除所有的指定元素，必須是一維數(shù)組?SELECT array_remove(ARRAY[1,2,3,2], 2);?{1,3}

array_replace(anyarray,anyelement,anyelement)anyarray?替換指定數(shù)組元素為新的元素?SELECT array_replace(ARRAY[1,2,5,4], 5, 3);?{1,2,3,4}

array_to_string(anyarray,text[,text])text?將數(shù)組元素使用分隔符連接為文本，NULL可以使用指定元素替換?SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*');?1,2,3,*,5

array_upper(anyarray,int)int?數(shù)組指定維度的上屆?SELECT array_upper(ARRAY[1,8,3,7], 1);?4

cardinality(anyarray)int?返回?cái)?shù)組所有維度的長(zhǎng)度總和，如果是空數(shù)組則返回0?SELECT cardinality(ARRAY[[1,2],[3,4]]);?4

string_to_array(text,text[,text])text[]?將文本使用分隔符分隔后轉(zhuǎn)換為數(shù)組，如果指定第三個(gè)參數(shù)，則第三個(gè)參數(shù)在數(shù)組中被轉(zhuǎn)換為NULL?SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');?{xx,NULL,zz}

unnest(anyarray)setof anyelement?將數(shù)組元素轉(zhuǎn)換為行?SELECT unnest(ARRAY[1,2]);?

1

2

unnest(anyarray,anyarray[, ...])setof anyelement, anyelement [, ...]?將多維數(shù)組轉(zhuǎn)換為行集合，其中第一個(gè)數(shù)組顯示為第一列，第二個(gè)數(shù)組顯示為第二列，以此類(lèi)推。但是這個(gè)函數(shù)只在from子句中使用?SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);?

unnest | unnest

--------+----

1 | foo

2 | bar

| baz

分享文章：包含postgresql維度的詞條
文章來(lái)源：http://jinyejixie.com/article20/dsdgojo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、App設(shè)計(jì)、外貿(mào)網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、App開(kāi)發(fā)、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)