解析 PostgreSQL 模式和 PostgreSQL 表
創(chuàng)新互聯(lián)公司是專業(yè)的港口網(wǎng)站建設(shè)公司，港口接單;提供網(wǎng)站制作、網(wǎng)站建設(shè),網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進行港口網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團隊,希望更多企業(yè)前來合作!
PostgreSQL 模式包含有名的對象（表、數(shù)據(jù)類型、函數(shù)及運算符），其名可能會和其他模式的現(xiàn)有對象相同。表是由行和列，以及行列相交的欄位組成，每一個在行中的欄位是和該行的其他欄位含蓄地相關(guān)。
PostgreSQL 模式
PostgreSQL 模式基本上是一個名空間：它包含有名的對象（表、數(shù)據(jù)類型、函數(shù)及運算符），其名可能會和其他模式的現(xiàn)有對象相同。
模式名：創(chuàng)建的模式名。名不能以 pg_ 開頭，因為這些名是保留給系統(tǒng)模式。
擁有者：擁有模式的用戶名。如果省略，默認為運行命令的用戶。
PostgreSQL 表
關(guān)聯(lián)式數(shù)據(jù)庫使用表來保存數(shù)據(jù)，全部數(shù)據(jù)操作都在表上完成或生成另一個表作為結(jié)果。表是由行和列，以及行列相交的欄位組成。從一般的角度來看，列在一個表中描述數(shù)據(jù)的名和類型；行在一個表中代表列組成的記錄，從左至右由相應(yīng)列的名和類型描述。每一個在行中的欄位是和該行的其他欄位含蓄地相關(guān)。點擊“表”按鈕即可打開表的對象列表。
當要打開有圖形欄位的表時，在表上右擊并在彈出菜單中選擇“打開表（快速）”，用更快的性能打開圖形表，BLOB 欄位（圖片）將不會被加載直到點擊單元格。如果需要在打開表時 Navicat 加載全部的圖片需要點擊“打開表”。
可以創(chuàng)建一個表快捷方式，在對象列表中的表上右擊并在彈出菜單中選擇“創(chuàng)建打開表快捷方式”。這個選項是用來提供一個打開表的便捷方式，可以直接輸入數(shù)據(jù)而無需打開主 Navicat。
要清空一個表，在已選擇的表上右擊并在彈出菜單中選擇“清空表”。此選項僅適用于清除全部現(xiàn)有記錄而不重設(shè)自動遞增值。如果需要在清除表的同時重設(shè)自動遞增值，請使用“截斷表”。
1、通過命令行查詢
\d 數(shù)據(jù)庫 —— 得到所有表的名字
\d 表名 —— 得到表結(jié)構(gòu)
2、通過SQL語句查詢
"select * from pg_tables" —— 得到當前db中所有表的信息（這里pg_tables是系統(tǒng)視圖）
"select tablename from pg_tables where schemaname='public'" —— 得到所有用戶自定義表的名字（這里"tablename"字段是表的名字，"schemaname"是schema的名字。用戶自定義的表，如果未經(jīng)特殊處理，默認都是放在名為public的schema下）
一、索引的類型：
PostgreSQL提供了多種索引類型：B-Tree、Hash、GiST和GIN，由于它們使用了不同的算法，因此每種索引類型都有其適合的查詢類型，缺省時，CREATE INDEX命令將創(chuàng)建B-Tree索引。
1. B-Tree:
CREATE TABLE test1 (
id integer,
content varchar
);
CREATE INDEX test1_id_index ON test1 (id);
B-Tree索引主要用于等于和范圍查詢，特別是當索引列包含操作符" 、=和"作為查詢條件時，PostgreSQL的查詢規(guī)劃器都會考慮使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查詢中，PostgreSQL也可以使用B-Tree索引。然而對于基于模式匹配操作符的查詢，如LIKE、ILIKE、~和 ~*，僅當模式存在一個常量，且該常量位于模式字符串的開頭時，如col LIKE 'foo%'或col ~ '^foo'，索引才會生效，否則將會執(zhí)行全表掃描，如：col LIKE '%bar'。
2. Hash：
CREATE INDEX name ON table USING hash (column);
散列(Hash)索引只能處理簡單的等于比較。當索引列使用等于操作符進行比較時，查詢規(guī)劃器會考慮使用散列索引。
這里需要額外說明的是，PostgreSQL散列索引的性能不比B-Tree索引強，但是散列索引的尺寸和構(gòu)造時間則更差。另外，由于散列索引操作目前沒有記錄WAL日志，因此一旦發(fā)生了數(shù)據(jù)庫崩潰，我們將不得不用REINDEX重建散列索引。
3. GiST：
GiST索引不是一種單獨的索引類型，而是一種架構(gòu)，可以在該架構(gòu)上實現(xiàn)很多不同的索引策略。從而可以使GiST索引根據(jù)不同的索引策略，而使用特定的操作符類型。
4. GIN：
GIN索引是反轉(zhuǎn)索引，它可以處理包含多個鍵的值(比如數(shù)組)。與GiST類似，GIN同樣支持用戶定義的索引策略，從而可以使GIN索引根據(jù)不同的索引策略，而使用特定的操作符類型。作為示例，PostgreSQL的標準發(fā)布中包含了用于一維數(shù)組的GIN操作符類型，如：、=、等。
二、復(fù)合索引：
PostgreSQL中的索引可以定義在數(shù)據(jù)表的多個字段上，如：
CREATE TABLE test2 (
major int,
minor int,
name varchar
}
CREATE INDEX test2_mm_idx ON test2 (major, minor);
1. B-Tree類型的復(fù)合索引：
在B-Tree類型的復(fù)合索引中，該索引字段的任意子集均可用于查詢條件，不過，只有當復(fù)合索引中的第一個索引字段(最左邊)被包含其中時，才可以獲得最高效率。
2. GiST類型的復(fù)合索引：
在GiST類型的復(fù)合索引中，只有當?shù)谝粋€索引字段被包含在查詢條件中時，才能決定該查詢會掃描多少索引數(shù)據(jù)，而其他索引字段上的條件只是會限制索引返回的條目。假如第一個索引字段上的大多數(shù)數(shù)據(jù)都有相同的鍵值，那么此時應(yīng)用GiST索引就會比較低效。
3. GIN類型的復(fù)合索引：
與B-Tree和GiST索引不同的是，GIN復(fù)合索引不會受到查詢條件中使用了哪些索引字段子集的影響，無論是哪種組合，都會得到相同的效率。
使用復(fù)合索引應(yīng)該謹慎。在大多數(shù)情況下，單一字段上的索引就已經(jīng)足夠了，并且還節(jié)約時間和空間。除非表的使用模式非常固定，否則超過三個字段的索引幾乎沒什么用處。
三、組合多個索引：
PostgreSQL可以在查詢時組合多個索引(包括同一索引的多次使用)，來處理單個索引掃描不能實現(xiàn)的場合。與此同時，系統(tǒng)還可以在多個索引掃描之間組成AND和OR的條件。比如，一個類似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查詢，可以被分解成四個獨立的基于x字段索引的掃描，每個掃描使用一個查詢子句，之后再將這些掃描結(jié)果OR在一起并生成最終的結(jié)果。另外一個例子是，如果我們在x和y上分別存在獨立的索引，那么一個類似WHERE x = 5 AND y = 6的查詢，就會分別基于這兩個字段的索引進行掃描，之后再將各自掃描的結(jié)果進行AND操作并生成最終的結(jié)果行。
為了組合多個索引，系統(tǒng)掃描每個需要的索引，然后在內(nèi)存里組織一個BITMAP，它將給出索引掃描出的數(shù)據(jù)在數(shù)據(jù)表中的物理位置。然后，再根據(jù)查詢的需要，把這些位圖進行AND或者OR的操作并得出最終的BITMAP。最后，檢索數(shù)據(jù)表并返回數(shù)據(jù)行。表的數(shù)據(jù)行是按照物理順序進行訪問的，因為這是位圖的布局，這就意味著任何原來的索引的排序都將消失。如果查詢中有ORDER BY子句，那么還將會有一個額外的排序步驟。因為這個原因，以及每個額外的索引掃描都會增加額外的時間，這樣規(guī)劃器有時候就會選擇使用簡單的索引掃描，即使有多個索引可用也會如此。
四、唯一索引：
CREATE UNIQUE INDEX name ON table (column [, ...]);
五、表達式索引：
表達式索引主要用于在查詢條件中存在基于某個字段的函數(shù)或表達式的結(jié)果與其他值進行比較的情況，如：
SELECT * FROM test1 WHERE lower(col1) = 'value';
此時，如果我們僅僅是在col1字段上建立索引，那么該查詢在執(zhí)行時一定不會使用該索引，而是直接進行全表掃描。如果該表的數(shù)據(jù)量較大，那么執(zhí)行該查詢也將會需要很長時間。解決該問題的辦法非常簡單，在test1表上建立基于col1字段的表達式索引，如：
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
和上面的例子一樣，盡管我們可能會為first_name和last_name分別創(chuàng)建獨立索引，或者是基于這兩個字段的復(fù)合索引，在執(zhí)行該查詢語句時，這些索引均不會被使用，該查詢能夠使用的索引只有我們下面創(chuàng)建的表達式索引。
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
CREATE INDEX命令的語法通常要求在索引表達式周圍書寫圓括弧，就像我們在第二個例子里顯示的那樣。如果表達式只是一個函數(shù)調(diào)用，那么可以省略，就像我們在第一個例子里顯示的那樣。
從索引維護的角度來看，索引表達式要相對低效一些，因為在插入數(shù)據(jù)或者更新數(shù)據(jù)的時候，都必須為該行計算表達式的結(jié)果，并將該結(jié)果直接存儲到索引里。然而在查詢時，PostgreSQL就會把它們看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于簡單索引的查詢。通常而言，我們只是應(yīng)該在檢索速度比插入和更新速度更重要的場景下使用表達式索引。
六、部分索引：
部分索引(partial index)是建立在一個表的子集上的索引，而該子集是由一個條件表達式定義的(叫做部分索引的謂詞)。該索引只包含表中那些滿足這個謂詞的行。
由于不是在所有的情況下都需要更新索引，因此部分索引會提高數(shù)據(jù)插入和數(shù)據(jù)更新的效率。然而又因為部分索引比普通索引要小，因此可以更好的提高確實需要索引部分的查詢效率。見以下三個示例：
1. 索引字段和謂詞條件字段一致：
CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)
WHERE NOT (client_ip inet '192.168.100.0' AND client_ip inet '192.168.100.255');
下面的查詢將會用到該部分索引：
SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';
下面的查詢將不會用該部分索引：
一個不能使用這個索引的查詢可以是
SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';
2. 索引字段和謂詞條件字段不一致：
PostgreSQL支持帶任意謂詞的部分索引，唯一的約束是謂詞的字段也要來自于同樣的數(shù)據(jù)表。注意，如果你希望你的查詢語句能夠用到部分索引，那么就要求該查詢語句的條件部分必須和部分索引的謂詞完全匹配。 準確說，只有在PostgreSQL能夠識別出該查詢的WHERE條件在數(shù)學上涵蓋了該索引的謂詞時，這個部分索引才能被用于該查詢。
CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;
下面的查詢一定會用到該部分索引：
SELECT * FROM orders WHERE billed is not true AND order_nr 10000;
那么對于如下查詢呢？
SELECT * FROM orders WHERE billed is not true AND amount 5000.00;
這個查詢將不像上面那個查詢這么高效，畢竟查詢的條件語句中沒有用到索引字段，然而查詢條件"billed is not true"卻和部分索引的謂詞完全匹配，因此PostgreSQL將掃描整個索引。這樣只有在索引數(shù)據(jù)相對較少的情況下，該查詢才能更有效一些。
下面的查詢將不會用到部分索引。
SELECT * FROM orders WHERE order_nr = 3501;
3. 數(shù)據(jù)表子集的唯一性約束：
CREATE TABLE tests (
subject text,
target text,
success boolean,
...
);
CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;
該部分索引將只會對success字段值為true的數(shù)據(jù)進行唯一性約束。在實際的應(yīng)用中，如果成功的數(shù)據(jù)較少，而不成功的數(shù)據(jù)較多時，該實現(xiàn)方法將會非常高效。
七、檢查索引的使用：
見以下四條建議：
1. 總是先運行ANALYZE。
該命令將會收集表中數(shù)值分布狀況的統(tǒng)計。在估算一個查詢返回的行數(shù)時需要這個信息，而規(guī)劃器則需要這個行數(shù)以便給每個可能的查詢規(guī)劃賦予真實的開銷值。如果缺乏任何真實的統(tǒng)計信息，那么就會使用一些缺省數(shù)值，這樣肯定是不準確的。因此，如果還沒有運行ANALYZE就檢查一個索引的使用狀況，那將會是一次失敗的檢查。
2. 使用真實的數(shù)據(jù)做實驗。
用測試數(shù)據(jù)填充數(shù)據(jù)表，那么該表的索引將只會基于測試數(shù)據(jù)來評估該如何使用索引，而不是對所有的數(shù)據(jù)都如此使用。比如從100000行中選1000行，規(guī)劃器可能會考慮使用索引，那么如果從100行中選1行就很難說也會使用索引了。因為100行的數(shù)據(jù)很可能是存儲在一個磁盤頁面中，然而沒有任何查詢規(guī)劃能比通過順序訪問一個磁盤頁面更加高效了。與此同時，在模擬測試數(shù)據(jù)時也要注意，如果這些數(shù)據(jù)是非常相似的數(shù)據(jù)、完全隨機的數(shù)據(jù)，或按照排序順序插入的數(shù)據(jù)，都會令統(tǒng)計信息偏離實際數(shù)據(jù)應(yīng)該具有的特征。
3. 如果索引沒有得到使用，那么在測試中強制它的使用也許會有些價值。有一些運行時參數(shù)可以關(guān)閉各種各樣的查詢規(guī)劃。
4. 強制使用索引用法將會導(dǎo)致兩種可能：一是系統(tǒng)選擇是正確的，使用索引實際上并不合適，二是查詢計劃的開銷計算并不能反映現(xiàn)實情況。這樣你就應(yīng)該對使用和不使用索引的查詢進行計時，這個時候EXPLAIN ANALYZE命令就很有用了。
@這個字符。
Postgresql數(shù)據(jù)庫密碼不能包含@這個字符。
導(dǎo)入時，如果數(shù)據(jù)庫密碼本來帶有@（這里為aspire@2020），估計不會識別出來。
導(dǎo)致執(zhí)行不下去或執(zhí)行失敗。
PostgreSQL自帶有一個簡易的全文檢索引擎，可以實現(xiàn)小規(guī)模數(shù)據(jù)量的全文檢索功能。本文我們將引導(dǎo)介紹一下這個功能，對于小數(shù)據(jù)量的搜索這個功能是足夠使用的，而無需搭建額外的ES等重量級的全文檢索服務(wù)器。
PG的全文檢索操作符是 @@ ，當一個 tsvector (文檔)和 tsquery (條件)匹配時返回 true ，并且前后順序無影響:
和普通的SQL查詢一樣，只要在 WHERE 條件中使用這個符號就代表使用全文檢索條件篩選文檔了。如:
@@ 操作符支持隱式轉(zhuǎn)換，對于 text 類型可以無需強類型轉(zhuǎn)換( ::tsvector 或 to_tsvector(config_name, text) )，所以這個操作符實際支持的參數(shù)類型是這樣的:
tsquery 查詢條件并不是簡單的正則，而是一組搜索術(shù)語，使用并且使用布爾操作符 （AND）、 | （OR）和 ! （NOT）來組合它們，還有短語搜索操作符 - （FOLLOWED BY）。更詳細的語法參見 此文檔 。
此外，PostgreSQL還提供了兩個相對簡化的版本 plainto_tsquery 和 phraseto_tsquery 。
plainto_tsquery ( plainto_tsquery([ config regconfig, ] querytext text) returns tsquery )用戶將未格式化的 text 經(jīng)過分詞之后，插入 符號轉(zhuǎn)為 tsquery :
phraseto_tsquery ( phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery )行為和 plainto_tsquery 行為類似，但是分詞之后不是插入 而是 - (FOLLOWED BY):
使用索引可以加快全文檢索的速度。對于全文檢索來說，可選的索引類型是 GIN (通用倒排索引)和 GIST (通用搜索樹)，官方文檔更推薦使用 GIN索引 。創(chuàng)建一個 GIN 索引的范例:
也可以是一個連接列:
還可以單獨創(chuàng)建一個 tsvector 列，為這個列創(chuàng)建索引:
除了普通的 ORDER BY 條件之外，PostgreSQL為全文檢索提供了兩個可選的排序函數(shù) ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 和 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 ，以便實現(xiàn)基于 權(quán)重 的排序。
此外，對于PostgreSQL 9.6以上的版本還可以使用 RUM index 排序。(注意，這個是擴展，默認不包含)。
PostgreSQL默認的分詞字典中并不包含中文分詞字典，因此我們必須手工引入。目前一個比較好的項目是 zhparser ，同時這個插件也是阿里云的RDS默認包含的。安裝和啟用沒什么好說的。值得一提的是分詞配置參數(shù)。
在 CREATE EXTENSION 之后，必須配置分詞參數(shù)才能正確進行分詞和查找，否則什么都查不到。官方文檔提供的一個配置策略是:
n,v,a,i,e,l 這幾個字母分別表示一種token策略，只啟用了這幾種token mapping，其余則被屏蔽。具體支持的參數(shù)和含義可以用 \dFp+ zhparser 顯示:
WITH simple 表示詞典使用的是內(nèi)置的simple詞典，即僅做小寫轉(zhuǎn)換。根據(jù)需要可以靈活定義詞典和token映射，以實現(xiàn)屏蔽詞和同義詞歸并等功能。
比如我們看下面這個例子:
可以看到 江淮 這個詞組在查詢的時候被忽略了，我們啟用 j (abbreviation,簡稱)再看看結(jié)果:
所以實際使用中要設(shè)置合理的token types，過少將導(dǎo)致搜索結(jié)果不準確，過多將導(dǎo)致性能下降。此外，還有一些諸如 短詞復(fù)合: zhparser.multi_short = f 這一類的控制分詞結(jié)果的選項，根據(jù)實際使用酌情開啟。
pg_dump
-u
database_username
database
-t
tablename
-f
/tmp/tablename.sql恢復(fù)數(shù)據(jù)表，包含表結(jié)構(gòu)。登入數(shù)據(jù)庫后
\i
/tmp/tablename.sql即可創(chuàng)建表。注意此方式恢復(fù)在恢復(fù)已有表時可能會提示錯誤表已經(jīng)存在，但應(yīng)該無大礙，備份時可以嘗試只備份數(shù)據(jù)
重慶分公司
重慶分公司
