Hi，亲爱的小伙伴！
欢迎来到社区!
Tools Online | 在线开发工具
RankList | 热门文章
扫码关注 PHP1 官方微信号
Recommend | 推荐阅读
| 中国最专业的PHP中文社区 |
Copyright (C) 1998 - . All Rights Reserved
第一PHP社区－－一步，二步，三步，N步，二行脚印
张映 发表于
分类目录：
标签：, , , , ,
玩了一下pgsql的修改表格的命令，感觉和mysql基本上差不多，唯有一些不同的是，alter 只能添加主键和外键，普通索引，唯一索引不能添加，还不能删除。要想删除，就要删除表，重建表。这个有点坑爹，我用的版本是8.1.13，非常低的版本了。不知道高版本有没有解决这个问题。
playboy=& alter table
//添加一个表字段
ALTER TABLE
playboy=& \d test
Table "public.test"
--------------+-----------------------+---------------------------------------------------
| not null default nextval('seq_test_id'::regclass)
| character varying(32) |
date_created | date
"playboy_id_pk" PRIMARY KEY, btree (id)
playboy=& alter table test alter sex type varchar(1);
//修改表字段类型
ALTER TABLE
playboy=& \d test
Table "public.test"
--------------+-----------------------+---------------------------------------------------
| not null default nextval('seq_test_id'::regclass)
| character varying(32) |
date_created | date
| character varying(1)
"playboy_id_pk" PRIMARY KEY, btree (id)
playboy=& create unique index unique_name on test(name);
//创建唯一索引
CREATE INDEX
playboy=& \d test
Table "public.test"
--------------+-----------------------+---------------------------------------------------
| not null default nextval('seq_test_id'::regclass)
| character varying(32) |
date_created | date
| character varying(1)
"playboy_id_pk" PRIMARY KEY, btree (id)
"unique_name" UNIQUE, btree (name)
playboy=& alter table te
//表字段改名
ALTER TABLE
playboy=& \d test
Table "public.test"
--------------+-----------------------+---------------------------------------------------
| not null default nextval('seq_test_id'::regclass)
| character varying(32) |
date_created | date
| character varying(1)
"playboy_id_pk" PRIMARY KEY, btree (id)
"unique_name" UNIQUE, btree (name)
playboy=& alter
//删除表字段
ALTER TABLE
转载请注明作者:海底苍鹰地址:温馨提示！由于新浪微博认证机制调整，您的新浪微博帐号绑定已过期，请重新绑定！&&|&&
PostgreSQL 高校实验室,企业,社区大联盟才是未来的方向.
LOFTER精选
网易考拉推荐
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
阅读(716)|
用微信&&“扫一扫”
将文章分享到朋友圈。
用易信&&“扫一扫”
将文章分享到朋友圈。
历史上的今天
在LOFTER的更多文章
loftPermalink:'',
id:'fks_',
blogTitle:'深入浅出PostgreSQL B-Tree索引结构',
blogAbstract:'PostgreSQL B-Tree是一种变种(high-concurrency B-tree management algorithm)，算法详情请参考src/backend/access/nbtree/README',
blogTag:'',
blogUrl:'blog/static/',
isPublished:1,
istop:false,
modifyTime:2,
publishTime:8,
permalink:'blog/static/',
commentCount:0,
mainCommentCount:0,
recommendCount:0,
bsrk:-100,
publisherId:0,
recomBlogHome:false,
currentRecomBlog:false,
attachmentsFileIds:[],
groupInfo:{},
friendstatus:'none',
followstatus:'unFollow',
pubSucc:'',
visitorProvince:'',
visitorCity:'',
visitorNewUser:false,
postAddInfo:{},
mset:'000',
remindgoodnightblog:false,
isBlackVisitor:false,
isShowYodaoAd:false,
hostIntro:'PostgreSQL 高校实验室,企业,社区大联盟才是未来的方向.',
hmcon:'1',
selfRecomBlogCount:'0',
lofter_single:''
{list a as x}
{if x.moveFrom=='wap'}
{elseif x.moveFrom=='iphone'}
{elseif x.moveFrom=='android'}
{elseif x.moveFrom=='mobile'}
${a.selfIntro|escape}{if great260}${suplement}{/if}
{list a as x}
推荐过这篇日志的人：
{list a as x}
{if !!b&&b.length>0}
他们还推荐了：
{list b as y}
转载记录：
{list d as x}
{list a as x}
{list a as x}
{list a as x}
{list a as x}
{if x_index>4}{break}{/if}
${fn2(x.publishTime,'yyyy-MM-dd HH:mm:ss')}
{list a as x}
{if !!(blogDetail.preBlogPermalink)}
{if !!(blogDetail.nextBlogPermalink)}
{list a as x}
{if defined('newslist')&&newslist.length>0}
{list newslist as x}
{if x_index>7}{break}{/if}
{list a as x}
{var first_option =}
{list x.voteDetailList as voteToOption}
{if voteToOption==1}
{if first_option==false},{/if}&&“${b[voteToOption_index]}”&&
{if (x.role!="-1") },“我是${c[x.role]}”&&{/if}
&&&&&&&&${fn1(x.voteTime)}
{if x.userName==''}{/if}
网易公司版权所有&&
{list x.l as y}
{if defined('wl')}
{list wl as x}{/list}使用局部索引来提升 PostgreSQL 的性能_数据库技术_Linux公社-Linux系统门户网站
你好，游客
使用局部索引来提升 PostgreSQL 的性能
来源：oschina.net&
作者：Linux
大家可能还不知道 PostgreSQL 支持对表数据进行局部索引吧? &它的好处是既能加快这部分索引过的数据的读取速度, 又不会增加额外开销.& 对于那些反复根据给定的&WHERE&子句读出来的数据, 最好的办法就是对这部分数据索引.&这对某些需要预先进行聚集计算的特定分析工作流来说, 很合适. 本帖中, 我将举一个例子说明如何通过部分索引优化数据查询.
假设有这样一个事件表, 结构如下:
每个事件关联一个用户, 有一个 ID, 一个时间戳, 和一个描述事件的&JSON. JSON 的内容包含页面的路径, 事件的类别 (如: 单击, 网页浏览, 表单提交), 以及其他跟事件相关的属性。
我们使用这个表存储各种事件日志. 假设我们手上有个 , 能自动记录用户的每一个点击, 每一次页面浏览, 每一次表单提交, 以便我们以后做分析.&再假设我们想做个内部用的报表(internal dashboard)显示一些有价值的数据(high-value metrics), 如:每周的注册数量, 每天应收帐款.&那么, 问题就来了. 跟这个报表相关的事件, 只占该事件表数据的一小部分 --&网站的点击量虽然很高, 但是只有很小一部分最终成交! 而这一小部分成交数据跟其他数据混杂放在一起,&也就是说, 它的信噪比很低.&
我们现在想提高报表查询的速度.& 先说注册事件吧, 我们把它定义为:注册页面(/signup/)的一次表单提交. 要获得九月份第一周的注册数量, 可以理解成:
对一个包含1千万条记录, 其中只有 3000 条是注册记录, 并且没有做过索引的数据集, 执行这样的查询需要 45 秒.
对单列做全索引(Full Indexes) : 大杂烩
提高查询速度, 比较傻的办法是: 给事件相关的各种属性创建单列索引(single-column index):(data-&&'type'),(data-&&'path'),&和 time.&通过 , &我们可以把这三个索引扫描结果合并起来.& 如果我们只是有选择地查询其中一部分数据,&而且相关索引依然存在内存中,&查询的速度会变得很快.& 刚开始查询大概用&200&毫秒, 后面会降到&20 毫秒 & 比起要花 45 秒查询的顺序扫描, 确实有明显的提高.
这种索引方式有几个弊端:
数据写入的开销. 这种方式在每次 INSERT/UPDATE/DELETE 操作的时候, 需要修改这三个索引的数据.& 导致像本例这样频需要繁写入数据的更新数据操作代价太高.
数据查询的限制. 这种方式同时也限制了我们自定义有价值(high-value)事件类型的能力.&比方说, 我们无法在 JSON 字段上做比范围查询更复杂的查询.&具体如:通过正则表达式搜索, 或者查找路径是/signup/ 开头的页面.
磁盘空间的使用. 本例中的提到的表占&6660 mb 磁盘空间, 三个索引和起来有 1026 mb, 随着时间的推移, 这些数字还会不断的暴涨.
局部索引（Partial Indexes）
我们分析用的注册事件，只占了表中全部数据的 0.03%。而全索引是对全部数据进行索引， 显然不合适。要提高查询速度， 最好的办法是用局部索引。
以我们对注册事件的定义为过滤条件，创建一个无关列(unrelated column)索引，通过该索引，PostgreSQL 很容易找到注册事件所在的行，查询速度自然要比在相关字段的3个全索引快的多。 尤其是对时间字段进行局部索引。具体用法如下:
CREATE INDEX event_signups ON event (time)WHERE (data-&&'type') = 'submit' AND (data-&&'path') = '/signup/'
这个索引的查询速度，会从刚开始的 200 毫秒, 降到 2 毫秒。只要多运行查询语句，速度自然就会加快。更重要的是，局部索引解决了前面提到的全索引的几个缺点。
索引只占 96 kb 磁盘空间, 是全索引的 1026 mb 的 1/10000。
只有新增的行符合注册事件的过滤条件， 才更新索引。由于符合条件的事件只有&0.03%，数据写入的性能得到很大的提高: 基本上，创建和更新这样的索引没有太大的开销。
这样的局部合并(partial join) 允许我们使用&PostgreSQL&提供的各种表达式作为过滤条件。索引中用到的&WHERE 子句，跟在查询语句中的用法没什么两样，&所以我们可以写出很复杂的过滤条件。 如：正则表达式， 函数返回结果，前面提到的前缀匹配。
不要索引结果是布尔值的断言
我见过有人直接索引布尔表达式：
(data-&&'type') = 'submit' AND (data-&&'path') = '/signup/'
，然后把时间字段放在第二项. 如：
CREATE INDEX event_signup_time ON event(((data-&&'type') = 'submit' AND (data-&&'path') = '/signup/'), time)
这样做的后果，比上面两种方法还要严重，因为&PostgreSQL 的查询规划器（query planner）不会将这个布尔表达式当作过滤条件。也就是说，规划器不会把它当作 WHERE 语句：
WHERE (data-&&'type') = 'submit' AND (data-&&'path') = '/signup/'
所以，我们索引的字段：
((data-&&'type') = 'submit' AND (data-&&'path') = '/signup/')
的值始终为 true。 当我们用这个索引当作条件过滤事件的时候，不管表达式的结果是&true 还是 false，都会先把事件数据读出来，加载完后，再过滤。
这么一来, 索引的时候会从磁盘中读取许多不必要的数据, 此外也要检查每一行数据的有效性.&拿我们例子中的数据集来说, 这样的查询第一次要 25 秒, 之后会降到 8 秒.& 这样的结果比索引整个时间字段还要差一些.
局部索引能在很大程度上, 提高那些通过断言过滤出表中一部分数据的查询的速度. 对于以流量论英雄(Judging by traffic )的 #postgresql IRC 来说, 局部索引显得有些资源利用不足. 对比全索引, 局部索引有适用范围更广的断言(greater range of predicates), 配合高选择性过滤条件(highly selective filters), 写操作和磁盘空间会变得更少. 要是你经常查询某个表中的一小部分数据, 应当优先考虑局部索引.
是不是开始爱上&PostgreSQL 了?& 要了解它的各种功能和特点,&请移步到这里 .
想不想将强大的技术变得更易于使用? 有兴趣就给我们发邮件&.
[1] 这种问题可以通过对表分区来解决.&把表中有价数据(high-value events)和其他数据分开放在不同的子表中.&不过, 如果有价值数据的种类比较多, 这种方法就不适用, 因为, 每次添加一个新的有价值数据的种类,&都要重新对表分区. [2] 通过 &优化, 可以大大的降低更新操作的开销, 但是, 每次 INSERT 或 DELETE 操作依然需要更新3个索引.&[3] 我们可以通过建一个 '多列索引' 对 3 个字段同时索引.&如: on((data-&&'type'), (data-&&'path'), time). 这个索引占 755 mb 磁盘空间,&比建 3 个索引用的磁盘空间也就少了 26%, 而且其他问题依然存在.&此外, 这样的索引可能对同样数据的其他查询, .&所以,&如果我们有几种不同的类型的有价值数据,&节省磁盘空间这点优势也就不存在了.&[4] 相关的查询规划(The relevant query plan):
------------------------------------华丽丽的分割线------------------------------------
6.3环境下yum安装PostgreSQL 9.3
PostgreSQL缓存详述
Windows平台编译 PostgreSQL
下LAPP（Linux+Apache+PostgreSQL+PHP)环境的配置与安装
Ubuntu上的phppgAdmin安装及配置
CentOS平台下安装PostgreSQL9.3
PostgreSQL配置Streaming Replication集群
如何在CentOS 7/6.5/6.4 下安装PostgreSQL 9.3 与 phpPgAdmin&
------------------------------------华丽丽的分割线------------------------------------
PostgreSQL 的详细介绍：PostgreSQL 的下载地址：
本文永久更新链接地址：
相关资讯 & & &
& (06/01/:52)
& (06/18/:36)
& (04/13/:34)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款