首页 > 程序开发 > Web开发 > PHP教程 > PHP与数据库 > MYSQL > 正文
PostgresQL建立索引如何避免写数据锁定
2016-01-06       个评论      
收藏    我要投稿

问题源自一个帅哥在建索引发生表锁的问题。先介绍一下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 ]

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

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

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

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

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

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

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

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

点击复制链接 与好友分享!回本站首页
相关TAG标签 索引 数据
上一篇:快速的 MySQL 本地和远程密码破解
下一篇:Navicat数据建模 for PostgreSQL 支持跨平台
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站