[转帖]PostgresQL建立索引如何避免写数据锁定_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
3
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2786 | 回复: 2   主题: [转帖]PostgresQL建立索引如何避免写数据锁定        下一篇 
xuguiju
注册用户
等级:新兵
经验:76
发帖:10
精华:0
注册:2013-7-26
状态:离线
发送短消息息给xuguiju 加好友    发送短消息息给xuguiju 发消息
发表于: IP:您无权察看 2015-1-5 11:40:55 | [全部帖] [楼主帖] 楼主

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

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

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
aven
版主
等级:列兵
经验:97
发帖:0
精华:0
注册:2015-12-8
状态:离线
发送短消息息给aven 加好友    发送短消息息给aven 发消息
发表于: IP:您无权察看 2015-12-11 18:09:44 | [全部帖] [楼主帖] 2  楼   主页

drop index也支持concurrently哦。

重建完索引还需要执行analyze,否则查询可能用不上新的index,从而导致查询变慢负载变高。



赞(0)    操作        顶端 
蜀山战纪
版主
等级:中校
经验:2051
发帖:38
精华:6
注册:2015-10-23
状态:离线
发送短消息息给蜀山战纪 加好友    发送短消息息给蜀山战纪 发消息
发表于: IP:您无权察看 2015-12-11 18:26:19 | [全部帖] [楼主帖] 3  楼

drop index也支持concurrently哦。

重建完索引还需要执行analyze,否则查询可能用不上新的index,从而导致查询变慢负载变高。


支持,加油哦


QQ图片20160129113517.jpg


赞(0)    操作        顶端 
总帖数
3
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论