【postgresql初级使用】基于表达式或者函数的索引，字符串拼接可以使用索引了，带来不一样的优化效果
带表达式的索引
专栏内容：
- postgresql使用入门基础
- 手写数据库toadb
- 并发编程
个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.
文章目录
- 带表达式的索引
- 概述
- 创建语法
- 场景分析
- 函数表达式
- 普通表达式
- 总结
- 结尾
概述
在postgresql 中，一个索引不仅仅是基于表的一列或多列来创建，还可以基于函数，或者一个表达式来创建。
本文就来分享在postgresql 如何基于表达式来创建索引。
创建语法
基于表达式创建索引，它的SQL语法如下所示：
CREATE INDEX index_name ON table_name (expression);
- index_name 指定当前索引的名称 ；
- ON子句 指定当前索引 引用的数据表；
- expression 指定表达式内容；普通索引这里指定的是列名；
场景分析
在大数据时代，查询语句各式各样，过滤条件中带有函数，字符拼接等等，组成各种条件变量，下面我们按不同场景来举例说明。
函数表达式
经常会遇到将字符串转换为小字，或者在大小写不敏感时，就可以转换为大写或者小写，再来比较。
有一张人员信息表，名字分为first_name,last_name两部分，而名字又是大小字不敏感，所以经常转换为小写字符来比较。
postgres=> create table userInfo (uid integer primary key, first_name varchar, last_name varchar); CREATE TABLE postgres=> INSERT INTO userinfo(uid, first_name, last_name) select id, 'firstname' || id::int, 'lastname'||id::int FROM generate_series(1, 100000) as id; INSERT 0 100000
表中插入了10万条测试数据。
经常使用的SQL查询如下。
select * from userinfo where lower(first_name) = 'mar';
其中就用到了函数转换，先将first_name转为小写，再参与条件比较。
看一下它的执行计划。
postgres=> explain select * from userinfo where lower(first_name) = 'mar'; QUERY PLAN -------------------------------------------------------------- Seq Scan on userinfo (cost=0.00..2324.00 rows=500 width=31) Filter: (lower((first_name)::text) = 'mar'::text) (2 rows)可以看到它使用了seq scan也就是顺序扫描，从表起始一条条进行遍历，如果此类查询非常频繁的话，相当损耗性能。
这里使用带有表达式的索引尝试来优化一下。
postgres=> explain select * from userinfo where lower(first_name) = 'mar'; QUERY PLAN ------------------------------------------------------------------------------------ Index Scan using idx_expre_userinfo on userinfo (cost=0.42..8.44 rows=1 width=31) Index Cond: (lower((first_name)::text) = 'mar'::text) (2 rows)可以看到执行计划中，使用到了刚才创建的索引，而且执行估算时间也是大幅提升。
普通表达式
继续使用上面的测试数据来看另外一种场景。
当我们需要查询某个用户名是否存在时，会经常使用如下SQL语句。
postgres=> select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999'; uid | first_name | last_name ------+---------------+-------------- 9999 | firstname9999 | lastname9999 (1 row) Time: 7.905 ms postgres=> explain select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999'; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Seq Scan on userinfo (cost=0.00..2574.00 rows=500 width=31) Filter: ((((first_name)::text || ' '::text) || (last_name)::text) = 'firstname9999 lastname9999'::text) (2 rows) Time: 0.234 ms
筛选条件中，先将first_name和last_name拼接起来，再进行比较。
可以看到执行计划中使用了顺序扫描方式，执行时间也到了毫秒级，同样使用表达式索引来优化一下。
postgres=> create index idx_userinfo_name on userinfo ((first_name || ' ' || last_name)); CREATE INDEX Time: 307.842 ms
创建一个基于名字拼接表达式的索引。
下面再来看一下查询计划的情况。
postgres=> explain select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on userinfo (cost=20.29..778.62 rows=500 width=31) Recheck Cond: ((((first_name)::text || ' '::text) || (last_name)::text) = 'firstname9999 lastname9999'::text) -> Bitmap Index Scan on idx_userinfo_name (cost=0.00..20.17 rows=500 width=0) Index Cond: ((((first_name)::text || ' '::text) || (last_name)::text) = 'firstname9999 lastname9999'::text) (4 rows) Time: 0.366 ms可以看到刚才创建的索引被使用了 Bitmap Index Scan on idx_userinfo_name, 采用了bitmap扫描的方式；
下面看一下执行时间的变化。
postgres=> select * from userinfo where (first_name || ' ' || last_name) = 'firstname9999 lastname9999'; uid | first_name | last_name ------+---------------+-------------- 9999 | firstname9999 | lastname9999 (1 row) Time: 0.274 ms
执行时间的提升，真得令人惊㤉，提升了二十来倍。
总结
以上就是本节的全部内容，在复杂的SQL查询中，经常会用到各种表达式，字符运算，时间运算等，此时可以使用基于表达式或者函数的索引，使用索引进行优化效率。
结尾
非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！
作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。
注：未经同意，不得转载！
