postgres怎么在線創(chuàng)建索引

我們知道數(shù)據(jù)庫創(chuàng)建索引可能會鎖住創(chuàng)建索引的表，并且用該表上的一次掃描來執(zhí)行整個(gè)索引的構(gòu)建，這樣在創(chuàng)建索引時(shí)會影響在線業(yè)務(wù)，非常大的表創(chuàng)建索引可能會需要幾個(gè)小時(shí)，這樣阻塞業(yè)務(wù)是不被允許的。商業(yè)數(shù)據(jù)庫一般都提供在線創(chuàng)建索引的能力，PostgreSQL作為開源數(shù)據(jù)庫，也提供了這樣的功能。我們在CREATE INDEX命令中新增CONCURRENTLY選項(xiàng)來實(shí)現(xiàn)索引的在線創(chuàng)建。? ? ?但是concurrently在線創(chuàng)建索引也并不是那么完美，當(dāng)使用這個(gè)選項(xiàng)時(shí)，PostgreSQL必須執(zhí)行該表的兩次掃描，此外它必須等待所有現(xiàn)有可能會修改或者使用該索引的事務(wù)終止，甚至它可能會等待一個(gè)不相干的事務(wù)終止。因此這種方法比起普通的索引創(chuàng)建過程來說要做更多工作并且需要更多時(shí)間。同時(shí)，索引的創(chuàng)建會帶來較大的CPU和I/O消耗。甚至在極端情況下，如果數(shù)據(jù)庫存在長事務(wù)，我們發(fā)現(xiàn)create index命令根本無法結(jié)束。? ? ?從官方文檔中我們可以了解到如下信息，在并發(fā)（concurrently）索引構(gòu)建中，索引實(shí)際上是在事務(wù)中被構(gòu)建的，它在兩個(gè)事務(wù)中發(fā)生兩次表掃描。在每一次表掃描之前，索引構(gòu)建必須等待對該表做過修改的現(xiàn)有事務(wù)終止。在第二次掃描之后，索引構(gòu)建必須等待任何持有早于第二次掃描的快照的事務(wù)終止。然后該索引最終能被標(biāo)記為可用，CREATE INDEX命令完成。

公司主營業(yè)務(wù)：成都網(wǎng)站建設(shè)、做網(wǎng)站、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)建站是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)建站推出奉新免費(fèi)做網(wǎng)站回饋大家。

開啟第一個(gè)事務(wù)，拿到當(dāng)前快照snapshot

2.等待所有修改過該表的事務(wù)結(jié)束

3.掃描該表，第一次創(chuàng)建索引

4.結(jié)束第一個(gè)事務(wù)

5.開啟第二個(gè)事務(wù)，拿到當(dāng)前快照snapshot2

6.等待所有修改過該表的事務(wù)結(jié)束

7.第二次掃描該表，將兩次快照之間變更的記錄，合并到索引

8.上一步更新索引結(jié)束后，等待snapshot2之前開啟的所有事務(wù)結(jié)束

9.結(jié)束索引創(chuàng)建，索引變?yōu)榭捎? ? ?那么這里有個(gè)疑問，為什么需要兩次掃描、兩次創(chuàng)建索引？其實(shí)想想也很好解釋。因?yàn)樵诘谝淮蝿?chuàng)建索引的時(shí)候不阻塞讀寫，這段時(shí)間內(nèi)發(fā)生的變更需要在第二次掃描的時(shí)候合并更新進(jìn)索引。

postgresql怎么解鎖表

--查詢是否鎖表了

1、select oid from pg_class where relname='可能被鎖掉的表的表名'

，會顯示一個(gè)oid

2、select pid from pg_locks where relation='剛剛查出來的oid'

--如果查詢到了結(jié)果（pid），表示該表被鎖 則需要釋放鎖定

select pg_cancel_backend(上面查到的pid)

為什么使用postgresql skip locked加鎖失敗

是因?yàn)橥瑫r(shí)更新事物失誤。

通常在數(shù)據(jù)庫中最小粒度的鎖是行鎖，當(dāng)一個(gè)事務(wù)正在更新某條記錄時(shí)，另一個(gè)事務(wù)如果要更新同一條記錄（或者申請這一條記錄的鎖），則必須等待鎖釋放。

通常持鎖的時(shí)間需要保持到事務(wù)結(jié)束，也就是說，如果一個(gè)長事務(wù)持有了某條記錄的鎖，其他會話要持有這條記錄的鎖，可能要等很久。

PostgresQL建立索引如何避免寫數(shù)據(jù)鎖定

問題源自一個(gè)帥哥在建索引發(fā)生表鎖的問題。先介紹一下Postgresql的建索引語法： Version:9.1 CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ] ( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ WITH ( storage_parameter = value [, ... ] ) ] [ TABLESPACE tablespace ] [ WHERE predicate ] 這里不解釋語法的諸多參數(shù)使用(排序，使用方法，填充因子等)，主要說一下concurrently的使用場景。 正常情況下Postgresql建立普通btree索引時(shí)會阻塞DML(insert,update,delete)操作，直到索引完成，期間讀操作不受阻塞。當(dāng)只有一個(gè)用戶操作這當(dāng)然沒問題，但是在生產(chǎn)環(huán)境，并發(fā)比較高的情況下，特別是大表建索引就不能這么操作了，不然用戶要跳起來罵娘了，點(diǎn)個(gè)按鈕一天還沒反應(yīng)過來。--使用 Postgresql提供了一個(gè)參數(shù)，可以在線建立索引的時(shí)候避免因?qū)憯?shù)據(jù)而鎖表，這個(gè)參數(shù)叫concurrently。使用很簡單，就是用create index concurrently來代替create index即可。--副作用 當(dāng)然了，使用這個(gè)參數(shù)是有副作用的，不使用這個(gè)參數(shù)建索引時(shí)DB只掃描一次表，使用這個(gè)參數(shù)時(shí)，會引發(fā)DB掃兩次表，同時(shí)等待所有潛在會讀到該索引的事務(wù)結(jié)束，這么一來，系統(tǒng)的CPU和IO，內(nèi)存等會受一點(diǎn)影響，所以綜合考慮，仍然讓用戶自行選擇，而不是默認(rèn)。--失敗 在使用concurrently參數(shù)建索引時(shí)，有可能會遇到失敗的情況，比如建唯一索引索引發(fā)現(xiàn)數(shù)據(jù)有重復(fù)，又或者用戶發(fā)現(xiàn)建索引時(shí)建錯(cuò)字段的，取消建索引操作了。此時(shí)該表上會存在一個(gè)索引，這是因?yàn)閹н@個(gè)參數(shù)的建索引命令一經(jīng)發(fā)出，就首先會在系統(tǒng)的日志表里先插一個(gè)索引記錄進(jìn)去，又因?yàn)檫@個(gè)索引最終建失敗了，所以會被標(biāo)記一個(gè)INVALID的狀態(tài)，如下： postgres=# \d t_kenyon Table public.t_kenyon Column | Type | Modifiers --------+---------+----------- col | integer |Indexes:idx btree (col) INVALID--重建 遇到上述失效的索引重建時(shí)兩個(gè)辦法，一個(gè)是drop index index_name，然后再執(zhí)行create index concurrently。還有一個(gè)是執(zhí)行reindex index_name命令，但是后者不支持concurrent參數(shù)。--總結(jié)