PostgreSQL建立索引如何避免寫(xiě)數(shù)據(jù)鎖定
寫(xiě)這篇blog源自一個(gè)帥哥在建索引發(fā)生了表鎖的問(wèn)題。先介紹一下Postgresql的建索引語(yǔ)法：
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 ]
這里不解釋語(yǔ)法的諸多參數(shù)使用(排序，使用方法，填充因子等)，主要說(shuō)一下concurrently的使用場(chǎng)景。
正常情況下Postgresql建立普通btree索引時(shí)會(huì)阻塞DML(insert,update,delete)操作，直到索引完成，期間讀操作不受阻塞。當(dāng)只有一個(gè)用戶操作這當(dāng)然沒(méi)問(wèn)題，但是在生產(chǎn)環(huán)境，并發(fā)比較高的情況下，特別是大表建索引就不能這么操作了，不然用戶要跳起來(lái)罵娘了，點(diǎn)個(gè)按鈕一天還沒(méi)反應(yīng)過(guò)來(lái)。
--使用
Postgresql提供了一個(gè)參數(shù)，可以在線建立索引的時(shí)候避免因?qū)憯?shù)據(jù)而鎖表，這個(gè)參數(shù)叫concurrently。使用很簡(jiǎn)單，就是用create index concurrently來(lái)代替create index即可。
--副作用
當(dāng)然了，使用這個(gè)參數(shù)是有副作用的，不使用這個(gè)參數(shù)建索引時(shí)DB只掃描一次表，使用這個(gè)參數(shù)時(shí)，會(huì)引發(fā)DB掃兩次表，同時(shí)等待所有潛在會(huì)讀到該索引的事務(wù)結(jié)束，這么一來(lái)，系統(tǒng)的CPU和IO，內(nèi)存等會(huì)受一點(diǎn)影響，所以綜合考慮，仍然讓用戶自行選擇，而不是默認(rèn)。
--失敗
在使用concurrently參數(shù)建索引時(shí)，有可能會(huì)遇到失敗的情況，比如建唯一索引索引發(fā)現(xiàn)數(shù)據(jù)有重復(fù)，又或者用戶發(fā)現(xiàn)建索引時(shí)建錯(cuò)字段的，取消建索引操作了。此時(shí)該表上會(huì)存在一個(gè)索引，這是因?yàn)閹н@個(gè)參數(shù)的建索引命令一經(jīng)發(fā)出，就首先會(huì)在系統(tǒng)的日志表里先插一個(gè)索引記錄進(jìn)去，又因?yàn)檫@個(gè)索引最終建失敗了，所以會(huì)被標(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é)
在生產(chǎn)上執(zhí)行創(chuàng)建索引命令時(shí)最好帶上此參數(shù)，因?yàn)槎嘞囊稽c(diǎn)系統(tǒng)資源和時(shí)間來(lái)?yè)Q取用戶的不間斷訪問(wèn)更新是相對(duì)值得的。 如果是索引重建，可以再在原基礎(chǔ)上建立一個(gè)不同名的相同索引，然后取消老的索引。
英文原文： http://www.postgresql.org/docs/9.1/static/sql-createindex.html
