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

問題源自一個帥哥在建索引發(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索引時會阻塞DML(insert,update,delete)操作，直到索引完成，期間讀操作不受阻塞。當只有一個用戶操作這當然沒問題，但是在生產(chǎn)環(huán)境，并發(fā)比較高的情況下，特別是大表建索引就不能這么操作了，不然用戶要跳起來罵娘了，點個按鈕一天還沒反應過來。

--使用
Postgresql提供了一個參數(shù)，可以在線建立索引的時候避免因?qū)憯?shù)據(jù)而鎖表，這個參數(shù)叫concurrently。使用很簡單，就是用create index concurrently來代替create index即可。

--副作用
當然了，使用這個參數(shù)是有副作用的，不使用這個參數(shù)建索引時DB只掃描一次表，使用這個參數(shù)時，會引發(fā)DB掃兩次表，同時等待所有潛在會讀到該索引的事務結(jié)束，這么一來，系統(tǒng)的CPU和IO，內(nèi)存等會受一點影響，所以綜合考慮，仍然讓用戶自行選擇，而不是默認。

--失敗
在使用concurrently參數(shù)建索引時，有可能會遇到失敗的情況，比如建唯一索引索引發(fā)現(xiàn)數(shù)據(jù)有重復，又或者用戶發(fā)現(xiàn)建索引時建錯字段的，取消建索引操作了。此時該表上會存在一個索引，這是因為帶這個參數(shù)的建索引命令一經(jīng)發(fā)出，就首先會在系統(tǒng)的日志表里先插一個索引記錄進去，又因為這個索引最終建失敗了，所以會被標記一個INVALID的狀態(tài)，如下：

postgres=# /d t_kenyon Table "public.t_kenyon" Column | Type | Modifiers --------+---------+----------- col | integer | Indexes: "idx" btree (col) INVALID

--重建
遇到上述失效的索引重建時兩個辦法，一個是drop index index_name，然后再執(zhí)行create index concurrently。還有一個是執(zhí)行reindex index_name命令，但是后者不支持concurrent參數(shù)。

--總結(jié)
在生產(chǎn)上執(zhí)行創(chuàng)建索引命令時最好帶上此參數(shù)，因為多消耗一點系統(tǒng)資源和時間來換取用戶的不間斷訪問更新是相對值得的。 如果是索引重建，可以再在原基礎上建立一個不同名的相同索引，然后取消老的索引。



參考： http://www.postgresql.org/docs/9.1/static/sql-createindex.html

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com