PostgreSQL索引使用的一些技巧

白鳝的洞穴 2020-03-26

3957

这些年PostgreSQL社区的发展十分迅速，除了社区版本的PG数据库外，国内的各个数据库厂商也纷纷选择PG作为自己数据库产品的原型，这主要也是因为PG的许可授权模式是BSD授权，所授权的代码可以被任何厂家任意使用，包括改一下LOGO就闭源说是自己的产品也是被认可的。

PG数据库这些年的技术发展确实也十分迅速，其易用性，性能，稳定性都已经达到了在我们生产环境应用的需要。实际上大多数数据库在总体原理上是大同小异的，所以如果你掌握了其他数据库，要学习PG也不是太难的事情。老白这两年也在使用PG数据库，我们的D-SMART系列产品的后台数据库系统就选择了PG 10.3。

闲话少说，我们直入主题，谈谈PG数据库的索引。最主要的PG索引也是B-TREE索引这一点是和ORACLE类似的。关于B-TREE索引的一般性问题我就不多分析了。这里讲几个特殊的地方。

(1) 升序降序NULL记录的位置：一个索引是升序降序可以通过ASC/DESC定义来制定，不过NULL记录所在的位置，PG是可以制定的，缺省情况下的升降序是ASC NULLs LAST，也就是说索引是升序的，NULL记录会放在索引的尾部。我们可以通过NULLs FIRST来调整NULL记录放在索引的位置。这对于经常要扫描NULL记录的应用是有帮助的。

create index emp_idx on emp( emp_stat nulls first);

(2)部分索引（Partial index)：只对部分行建立索引，从而减少索引的大小，提高访问性能

create index emp_idx on emp(emp_stat ) where emp_stat is null;

(3)表达式索引/函数索引：和Oracle类似

(4)附加字段索引：大家还记得Oracle数据库的索引扫描吗，如果查询所需要的字段在索引中都存在了，就不需要访问表了，直接访问索引就行了。这种情况下，我们需要给索引增加字段。PG的附加字段索引就是这种索引的改进型。

create index emp_idx on emp(emp_id) include (emp_name);

这时候如果我们select emp_id,emp_name ...,就直接可以通过索引解决了。而且这里索引字段只有emp_id，emp_name不需要排序，这样就节约了复合索引的开销。

（5）%匹配操作的索引：Oracle的索引是支持%通配符的，如果%是在尾部，索引是可以直接起作用的，不过在PG里这里还有点小差别。如果我们执行：

select emp_id from emp where emp_name like 'jackson%';

普通的

create index emp_idx on emp(emp_name)

是无法起作用的。需要创建一个通配符专用的索引：

create index emp_idx on emp(emp_name varchar_pattern_ops)；

这样上面的操作就可以使用索引了。不过这种索引不支持><等操作，如果需要适应两种情况，那么我们就必须创建两个索引了。

当然如果我们使用反转键索引，那么就可以支持'%jackson'通配符了：

create index emp_idx on emp(reverse(emp_name) varchar_pattern_ops);

PG这些小心思还是挺有效的，如果我们在使用PG数据库，不把这么好的功能用上，是不是有点可惜呢。

postgresql

最后修改时间：2020-03-27 09:02:21

文章转载自白鳝的洞穴，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL索引使用的一些技巧

评论