|
|
|
|
移动端

PostgreSQL建立索引如何避免写数据锁定

写这篇blog源自一个帅哥在建索引发生了表锁的问题。正常情况下Postgresql建立普通btree索引时会阻塞DML(insert,update,delete)操作,直到索引完成,期间读操作不受阻塞。

作者:kenyon(君羊)来源:OsChina|2012-12-04 10:29

年前最后一场技术盛宴 | 1月27日与京东、日志易技术大咖畅聊智能化运维发展趋势!


写这篇blog源自一个帅哥在建索引发生了表锁的问题。先介绍一下Postgresql的建索引语法:

Version:9.1

  1. CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]  
  2.     ( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )  
  3.     [ WITH ( storage_parameter = value [, ... ] ) ]  
  4.     [ TABLESPACE tablespace ]  
  5.     [ WHERE predicate ] 

这里不解释语法的诸多参数使用(排序,使用方法,填充因子等),主要说一下concurrently的使用场景。

正常情况下Postgresql建立普通btree索引时会阻塞DML(insert,update,delete)操作,直到索引完成,期间读操作不受阻塞。当只有一个用户操作这当然没问题,但是在生产环境,并发比较高的情况下,特别是大表建索引就不能这么操作了,不然用户要跳起来骂娘了,点个按钮一天还没反应过来。

--使用

Postgresql提供了一个参数,可以在线建立索引的时候避免因写数据而锁表,这个参数叫concurrently。使用很简单,就是用create index concurrently来代替create index即可。

--副作用

当然了,使用这个参数是有副作用的,不使用这个参数建索引时DB只扫描一次表,使用这个参数时,会引发DB扫两次表,同时等待所有潜在会读到该索引的事务结束,这么一来,系统的CPU和IO,内存等会受一点影响,所以综合考虑,仍然让用户自行选择,而不是默认。

--失败

在使用concurrently参数建索引时,有可能会遇到失败的情况,比如建唯一索引索引发现数据有重复,又或者用户发现建索引时建错字段的,取消建索引操作了。此时该表上会存在一个索引,这是因为带这个参数的建索引命令一经发出,就首先会在系统的日志表里先插一个索引记录进去,又因为这个索引最终建失败了,所以会被标记一个INVALID的状态,如下:

  1. postgres=# \d t_kenyon  
  2.        Table "public.t_kenyon" 
  3.  Column |  Type   | Modifiers   
  4. --------+---------+-----------  
  5.  col    | integer |   
  6. Indexes:  
  7.     "idx" btree (col) INVALID 

--重建

遇到上述失效的索引重建时两个办法,一个是drop index index_name,然后再执行create index concurrently。还有一个是执行reindex index_name命令,但是后者不支持concurrent参数。

--总结

在生产上执行创建索引命令时最好带上此参数,因为多消耗一点系统资源和时间来换取用户的不间断访问更新是相对值得的。 如果是索引重建,可以再在原基础上建立一个不同名的相同索引,然后取消老的索引。

英文原文: http://www.postgresql.org/docs/9.1/static/sql-createindex.html

译文链接:http://my.oschina.net/Kenyon/blog/93465

【编辑推荐】

  1. PostgreSQL 9.1正式版发布
  2. MySQL中创建及优化索引组织结构的思路
  3. 解析索引中数据列顺序的选择问题
  4. PostgreSQL的数组
  5. MongoDB范围查询的索引优化
【责任编辑:小林 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

计算机网络原理与实践标准教程

本书深入浅出地阐述了计算机网络技术的基本原理,介绍了当前常用的先进网络技术以及网络的实际应用知识。全书主要内容包括计算机网络概述、...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊