使用局部索引来提升 PostgreSQL 的性能 已翻译 100%

oschina 投递于 2014/12/11 08:37 (共 9 段, 翻译完成于 12-19)
阅读 6468
收藏 71
5
加载中

大家可能还不知道 PostgreSQL 支持对表数据进行局部索引吧?  它的好处是既能加快这部分索引过的数据的读取速度, 又不会增加额外开销.  对于那些反复根据给定的 WHERE 子句读出来的数据, 最好的办法就是对这部分数据索引. 这对某些需要预先进行聚集计算的特定分析工作流来说, 很合适. 本帖中, 我将举一个例子说明如何通过部分索引优化数据查询.

假设有这样一个事件表, 结构如下:

每个事件关联一个用户, 有一个 ID, 一个时间戳, 和一个描述事件的 JSON. JSON 的内容包含页面的路径, 事件的类别 (如: 单击, 网页浏览, 表单提交), 以及其他跟事件相关的属性。

BreakingBad
翻译于 2014/12/11 16:07
4

我们使用这个表存储各种事件日志. 假设我们手上有个事件自动跟踪器 , 能自动记录用户的每一个点击, 每一次页面浏览, 每一次表单提交, 以便我们以后做分析. 再假设我们想做个内部用的报表(internal dashboard)显示一些有价值的数据(high-value metrics), 如:每周的注册数量, 每天应收帐款. 那么, 问题就来了. 跟这个报表相关的事件, 只占该事件表数据的一小部分 -- 网站的点击量虽然很高, 但是只有很小一部分最终成交! 而这一小部分成交数据跟其他数据混杂放在一起, 也就是说, 它的信噪比很低. 

我们现在想提高报表查询的速度.[1]  先说注册事件吧, 我们把它定义为:注册页面(/signup/)的一次表单提交. 要获得九月份第一周的注册数量, 可以理解成:

BreakingBad
翻译于 2014/12/12 19:18
3

对一个包含1千万条记录, 其中只有 3000 条是注册记录, 并且没有做过索引的数据集, 执行这样的查询需要 45 秒.

对单列做全索引(Full Indexes) : 大杂烩

提高查询速度, 比较傻的办法是: 给事件相关的各种属性创建单列索引(single-column index):(data->>'type'),(data->>'path'), 和 time. 通过 bitmap,  我们可以把这三个索引扫描结果合并起来.  如果我们只是有选择地查询其中一部分数据, 而且相关索引依然存在内存中, 查询的速度会变得很快.  刚开始查询大概用 200 毫秒, 后面会降到 20 毫秒 — 比起要花 45 秒查询的顺序扫描, 确实有明显的提高.

BreakingBad
翻译于 2014/12/13 10:04
2

这种索引方式有几个弊端:

  • 数据写入的开销. 这种方式在每次 INSERT/UPDATE/DELETE 操作的时候, 需要修改这三个索引的数据.[2]  导致像本例这样频需要繁写入数据的更新数据操作代价太高.

  • 数据查询的限制. 这种方式同时也限制了我们自定义有价值(high-value)事件类型的能力. 比方说, 我们无法在 JSON 字段上做比范围查询更复杂的查询. 具体如:通过正则表达式搜索, 或者查找路径是/signup/ 开头的页面.

  • 磁盘空间的使用. 本例中的提到的表占 6660 mb 磁盘空间, 三个索引和起来有 1026 mb, 随着时间的推移, 这些数字还会不断的暴涨.[3]

BreakingBad
翻译于 2014/12/14 10:01
2

局部索引(Partial Indexes)

我们分析用的注册事件,只占了表中全部数据的 0.03%。而全索引是对全部数据进行索引, 显然不合适。要提高查询速度, 最好的办法是用局部索引。

以我们对注册事件的定义为过滤条件,创建一个无关列(unrelated column)索引,通过该索引,PostgreSQL 很容易找到注册事件所在的行,查询速度自然要比在相关字段的3个全索引快的多。 尤其是对时间字段进行局部索引。具体用法如下:

CREATE INDEX event_signups ON event (time)
WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

BreakingBad
翻译于 2014/12/15 15:35
1

这个索引的查询速度,会从刚开始的 200 毫秒, 降到 2 毫秒。只要多运行查询语句,速度自然就会加快。更重要的是,局部索引解决了前面提到的全索引的几个缺点。

  • 索引只占 96 kb 磁盘空间, 是全索引的 1026 mb 的 1/10000。

  • 只有新增的行符合注册事件的过滤条件, 才更新索引。由于符合条件的事件只有 0.03%,数据写入的性能得到很大的提高: 基本上,创建和更新这样的索引没有太大的开销。

  • 这样的局部合并(partial join) 允许我们使用 PostgreSQL 提供的各种表达式作为过滤条件。索引中用到的 WHERE 子句,跟在查询语句中的用法没什么两样, 所以我们可以写出很复杂的过滤条件。 如:正则表达式, 函数返回结果,前面提到的前缀匹配。

BreakingBad
翻译于 2014/12/16 17:41
2

不要索引结果是布尔值的断言

我见过有人直接索引布尔表达式:

(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,都会先把事件数据读出来,加载完后,再过滤。[4]

BreakingBad
翻译于 2014/12/17 15:54
2

这么一来, 索引的时候会从磁盘中读取许多不必要的数据, 此外也要检查每一行数据的有效性. 拿我们例子中的数据集来说, 这样的查询第一次要 25 秒, 之后会降到 8 秒.  这样的结果比索引整个时间字段还要差一些.

局部索引能在很大程度上, 提高那些通过断言过滤出表中一部分数据的查询的速度. 对于以流量论英雄(Judging by traffic )的 #postgresql IRC 来说, 局部索引显得有些资源利用不足. 对比全索引, 局部索引有适用范围更广的断言(greater range of predicates), 配合高选择性过滤条件(highly selective filters), 写操作和磁盘空间会变得更少. 要是你经常查询某个表中的一小部分数据, 应当优先考虑局部索引.

是不是开始爱上 PostgreSQL 了?  要了解它的各种功能和特点, 请移步到这里 @danlovesproofs.

BreakingBad
翻译于 2014/12/18 19:40
2

想不想将强大的技术变得更易于使用? 有兴趣就给我们发邮件 jobs@heapanalytics.com.

[1] 这种问题可以通过对表分区来解决. 把表中有价数据(high-value events)和其他数据分开放在不同的子表中. 不过, 如果有价值数据的种类比较多, 这种方法就不适用, 因为, 每次添加一个新的有价值数据的种类, 都要重新对表分区.
[2] 通过 heap-only tuples 优化, 可以大大的降低更新操作的开销, 但是, 每次 INSERT 或 DELETE 操作依然需要更新3个索引. 
[3] 我们可以通过建一个 '多列索引' 对 3 个字段同时索引. 如: on((data->>'type'), (data->>'path'), time). 这个索引占 755 mb 磁盘空间, 比建 3 个索引用的磁盘空间也就少了 26%, 而且其他问题依然存在. 此外, 这样的索引可能对同样数据的其他查询, 没什么用处. 所以, 如果我们有几种不同的类型的有价值数据, 节省磁盘空间这点优势也就不存在了. 
[4] 相关的查询规划(The relevant query plan):

BreakingBad
翻译于 2014/12/19 19:15
2
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(14)

冰封天下99999
冰封天下99999
恩,感觉确实对sql的优化能够起到帮助,这个要去研究下,帖子很好,赞X32
甘薯
甘薯

引用来自“CLLam”的评论

PG真的强大。

引用来自“BaiYang”的评论

基本功能而已吧?partial index 连 SQLite 这种嵌入式引擎都支持呀 :-)

引用来自“ZeroOne”的评论

看到这句话赶紧去查了一下, partial index是sqlite3.8才开始支持的, 3.8才发布了没多久不到2年.这个应该不算是"基本功能"...

引用来自“BaiYang”的评论

我的角度是:连 sqlite 这样的嵌入式引擎这两年都有此功能了,那么对于 SQL Server、MySQL、DB2 和 PostgreSQL 这种全量级的 RDBMS 就完全属于基本功能了。:-P
倒也是这么个理儿. 技术发展实在是太快了.
BaiYang
BaiYang

引用来自“CLLam”的评论

PG真的强大。

引用来自“BaiYang”的评论

基本功能而已吧?partial index 连 SQLite 这种嵌入式引擎都支持呀 :-)

引用来自“ZeroOne”的评论

看到这句话赶紧去查了一下, partial index是sqlite3.8才开始支持的, 3.8才发布了没多久不到2年.这个应该不算是"基本功能"...
我的角度是:连 sqlite 这样的嵌入式引擎这两年都有此功能了,那么对于 SQL Server、MySQL、DB2 和 PostgreSQL 这种全量级的 RDBMS 就完全属于基本功能了。:-P
甘薯
甘薯

引用来自“CLLam”的评论

PG真的强大。

引用来自“BaiYang”的评论

基本功能而已吧?partial index 连 SQLite 这种嵌入式引擎都支持呀 :-)
看到这句话赶紧去查了一下, partial index是sqlite3.8才开始支持的, 3.8才发布了没多久不到2年.这个应该不算是"基本功能"...
zouqilin
zouqilin
太赞了13
matthewrod
matthewrod

引用来自“CLLam”的评论

PG真的强大。

引用来自“mark35”的评论

基本功能,mysql也有的。不过pgsql还有函数索引

引用来自“matthewrod”的评论

mysql支持json嘛?用了这么多年还真不知道。mysql也有局部索引嘛,要找找资料看看

引用来自“mark35”的评论

mysql不支持json,它也有部分索引不过好像只支持字符串类型列(比如对字符串前n个字符做部分索引),而不支持条件表达式约束。
那个是BTREE 吧,还是全表索引。
mark35
mark35

引用来自“CLLam”的评论

PG真的强大。

引用来自“mark35”的评论

基本功能,mysql也有的。不过pgsql还有函数索引

引用来自“matthewrod”的评论

mysql支持json嘛?用了这么多年还真不知道。mysql也有局部索引嘛,要找找资料看看
mysql不支持json,它也有部分索引不过好像只支持字符串类型列(比如对字符串前n个字符做部分索引),而不支持条件表达式约束。
matthewrod
matthewrod

引用来自“CLLam”的评论

PG真的强大。

引用来自“mark35”的评论

基本功能,mysql也有的。不过pgsql还有函数索引
mysql支持json嘛?用了这么多年还真不知道。mysql也有局部索引嘛,要找找资料看看
mark35
mark35

引用来自“CLLam”的评论

PG真的强大。
基本功能,mysql也有的。不过pgsql还有函数索引
BaiYang
BaiYang

引用来自“CLLam”的评论

PG真的强大。
基本功能而已吧?partial index 连 SQLite 这种嵌入式引擎都支持呀 :-)
返回顶部
顶部