由于最近我司业务量上涨,数据量剧增,数据库查询速度明显变慢,单次查询居然达到1800ms以上,急需优化。待查阅一番后,我知道了LIKE查询正确的索引使用姿势,特别是一些符合字段索引。
模糊查询的使用场景实在太多了,正确使用索引实在是太重要了。或许一个关键的优化就能为你节省更多的资源,带来更好的用户体验。
少啰嗦,我们看实例。
由于未经我司同意,数据不能用真实了,下面数据是我盗来的😢
首先,我们这里有一张存了一百万条信息的users
表。
1 | Table "public.users" |
普通查询
在不添加任何索引的情况下,我们查询一次。
1 | > EXPLAIN ANALYSE SELECT COUNT(*) FROM users WHERE username ILIKE '%foo%'; |
结果,如大家所想一样,顺序扫描,很慢。
btree 索引
很多童鞋会想到,那就加个索引呗。我们知道,pg里面一般默认都是btree索引,我们加一个试试。
1 | > CREATE INDEX idx_users_username ON users (username); |
查询一下:
1 | > EXPLAIN ANALYSE SELECT COUNT(*) FROM users WHERE username ILIKE '%foo%'; |
emm, 好家伙,居然还是顺序扫描。 这是由于一般的索引只能优化 LIKE foo%
这类的向后的模糊查询。所以btree索引这里也达不到优化的效果。
gin 索引 & pg_trgm 模块
pg_trgm模块提供函数和操作符测定字母,数字,文本基于三元模型匹配的相似性, 还有支持快速搜索相似字符串的索引操作符类。
这里提到了一个三元模型,可能有童鞋不理解,其实很简单。打个比方
foo
的三元模型的集合为{" f"," fo","foo","oo "}
,foo|bar
的三元模型的集合为{" f"," fo","foo","oo "," b"," ba","bar","ar "}
。也就是说将字符串拆解成三个字符一组,每个字符串被认为有两个空格前缀和一个空格后缀。
Postgres使用trigram将字符串分解成更小的单元便于有效地索引它们。pg_trgm模块支持GIST或GIN索引,从9.1开始,这些索引支持LIKE/ILIKE查询。
要使用pg_trgm模块,首先要启用该扩展,然后使用gin_trgm_ops
创建索引。
1 | > CREATE EXTENSION pg_trgm; |
我们再次分析查询
1 | > EXPLAIN ANALYSE SELECT COUNT(*) FROM users WHERE username ILIKE '%foo%'; |
哇,2.33ms!!!
从查询过程上看,索引已经起作用了。这将我们的查询直接提高了两个数量级,作用很大。我在我司使用该索引后,查询从1800ms提升到了10ms左右,也算是效果惊人了。
这里出现了个”Bitmap Heap Scan”,这是什么呢?
pg里面常见到的对表扫描的计划有这四种:
- Seq Scan
- Index Scan
- Bitmap Heap Scan
- Index Only Scan
前面两个你也许一看就懂。
第一个无非是按照表的记录顺序从头到尾依次检索扫描,全表扫描,代价大;
第二个先扫描索引,从索引找到数据位置,再准备获取数据,索引扫描,快,I/O少;
第三个一次性将满足条件的索引项全部取出,并在内存中进行排序, 然后根据取出的索引项访问表数据。一般需要合并索引访问的结果子集时会用到这种方式。
第四个就是查询的字段直接就在索引中了,直接扫描索引即可。
联合字段LIKE查询
有时候,我们可能需要联合多个字段来查询数据。如,我们需要搜索一位可能叫John Do
的人,这时候就需要我们将first_name
与last_name
联合作为条件查询了。
一般我们首先想到的是,在first_name
与last_name
都加上索引。那么可行么?我们先试一下。
1 | > CREATE INDEX trgm_idx_users_first ON users USING gin (first_name gin_trgm_ops); |
查询
1 | > SELECT COUNT(*) FROM users WHERE first_name || ' ' || last_name ILIKE '%foo%' |
看来还是没有用到索引,由于查询的时候使用的是联合的字段,单独的索引并不起作用。那么我们可以考虑将字段联合起来,添加一个索引。
1 | CREATE INDEX index_users_full_name ON users using gin ((first_name || ' ' || last_name) gin_trgm_ops); |
我们再次尝试查询。
1 |
|
喔😯,相当的快。使用了Bitmap Index
扫描的,看来索引很有效。
最后
勇敢上文,我们一起了解了一下pg里利用pg_trgm
模块来建立索引优化模糊查询。其实pg里买还有更多好用实用的功能,期待与大家一起探索~。
本文参考