由于最近我司业务量上涨,数据量剧增,数据库查询速度明显变慢,单次查询居然达到1800ms以上,急需优化。待查阅一番后,我知道了LIKE查询正确的索引使用姿势,特别是一些符合字段索引。
模糊查询的使用场景实在太多了,正确使用索引实在是太重要了。或许一个关键的优化就能为你节省更多的资源,带来更好的用户体验。
少啰嗦,我们看实例。
由于未经我司同意,数据不能用真实了,下面数据是我盗来的😢
首先,我们这里有一张存了一百万条信息的users
表。
Table "public.users"Column | Type | Modifiers id | integer | not null default nextval('users_id_seq' ::regclass) username | character varying | first_name | character varying | last_name | character varying |
普通查询 在不添加任何索引的情况下,我们查询一次。
1 2 3 4 5 6 7 8 9 10 11 12 > EXPLAIN ANALYSE SELECT COUNT (* ) FROM users WHERE username ILIKE '%foo%' ; QUERY PLAN Aggregate (cost= 21927.24 ..21927 .25 rows = 1 width= 0 ) (actual time = 737.523 ..737 .523 rows = 1 loops= 1 ) - > Seq Scan on users (cost= 0.00 ..21927 .00 rows = 96 width= 0 ) (actual time = 737.520 ..737 .520 rows = 0 loops= 1 ) Filter : ((username)::text ~ ~ * '%foo%' ::text) Rows Removed by Filter : 1000000 Planning time : 0.373 ms Execution time : 737.593 ms (6 rows )Time : 738.404 ms
结果,如大家所想一样,顺序扫描,很慢。
btree 索引 很多童鞋会想到,那就加个索引呗。我们知道,pg里面一般默认都是btree索引,我们加一个试试。
1 2 > CREATE INDEX idx_users_username ON users (username);Time : 15987.751 ms
查询一下:
1 2 3 4 5 6 7 8 9 10 11 12 13 > EXPLAIN ANALYSE SELECT COUNT (* ) FROM users WHERE username ILIKE '%foo%' ; QUERY PLAN Aggregate (cost= 21927.24 ..21927 .25 rows = 1 width= 0 ) (actual time = 752.271 ..752 .271 rows = 1 loops= 1 ) - > Seq Scan on users (cost= 0.00 ..21927 .00 rows = 96 width= 0 ) (actual time = 752.268 ..752 .268 rows = 0 loops= 1 ) Filter : ((username)::text ~ ~ * '%foo%' ::text) Rows Removed by Filter : 1000000 Planning time : 0.599 ms Execution time : 752.318 ms (6 rows )Time : 753.251 ms
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 2 3 4 5 > CREATE EXTENSION pg_trgm;Time : 42.206 ms> CREATE INDEX trgm_idx_users_username ON users USING gin (username gin_trgm_ops);Time : 7082.474 ms
我们再次分析查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 > EXPLAIN ANALYSE SELECT COUNT (* ) FROM users WHERE username ILIKE '%foo%' ; QUERY PLAN Aggregate (cost= 369.12 ..369 .13 rows = 1 width= 0 ) (actual time = 0.030 ..0 .030 rows = 1 loops= 1 ) - > Bitmap Heap Scan on users (cost= 12.75 ..368 .88 rows = 96 width= 0 ) (actual time = 0.026 ..0 .026 rows = 0 loops= 1 ) Recheck Cond: ((username)::text ~ ~ * '%foo%' ::text) - > Bitmap Index Scan on trgm_idx_users_username (cost= 0.00 ..12 .72 rows = 96 width= 0 ) (actual time = 0.024 ..0 .024 rows = 0 loops= 1 ) Index Cond: ((username)::text ~ ~ * '%foo%' ::text) Planning time : 0.636 ms Execution time : 0.095 ms (7 rows )Time : 2.333 ms
哇,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 2 3 4 > CREATE INDEX trgm_idx_users_first ON users USING gin (first_name gin_trgm_ops);Time : 4577.637 ms> CREATE INDEX trgm_idx_users_last ON users USING gin (last_name gin_trgm_ops);Time : 4770.507 ms
查询
1 2 3 4 5 6 7 8 9 10 11 12 13 > SELECT COUNT (* ) FROM users WHERE first_name || ' ' || last_name ILIKE '%foo%' QUERY PLAN Aggregate (cost= 27027.00 ..27027 .01 rows = 1 width= 0 ) (actual time = 1025.543 ..1025 .544 rows = 1 loops= 1 ) - > Seq Scan on users (cost= 0.00 ..26927 .00 rows = 40000 width= 0 ) (actual time = 1025.539 ..1025 .539 rows = 0 loops= 1 ) Filter : ((((first_name)::text || ' ' ::text) || (last_name)::text) ~ ~ * '%foo%' ::text) Rows Removed by Filter : 1000000 Planning time : 0.273 ms Execution time : 1025.591 ms (6 rows )Time : 1027.547 ms
看来还是没有用到索引,由于查询的时候使用的是联合的字段,单独的索引并不起作用。那么我们可以考虑将字段联合起来,添加一个索引。
1 CREATE INDEX index_users_full_name ON users using gin ((first_name || ' ' || last_name) gin_trgm_ops);
我们再次尝试查询。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 > EXPLAIN ANALYSE SELECT COUNT (* ) FROM users WHERE first_name || ' ' || last_name ILIKE '%foo%' ; QUERY PLAN Aggregate (cost= 10605.00 ..10605 .01 rows = 1 width= 0 ) (actual time = 0.020 ..0 .020 rows = 1 loops= 1 ) - > Bitmap Heap Scan on users (cost= 378.01 ..10505 .00 rows = 40000 width= 0 ) (actual time = 0.018 ..0 .018 rows = 0 loops= 1 ) Recheck Cond: ((((first_name)::text || ' ' ::text) || (last_name)::text) ~ ~ * '%foo%' ::text) - > Bitmap Index Scan on index_users_full_name (cost= 0.00 ..368 .01 rows = 40000 width= 0 ) (actual time = 0.016 ..0 .016 rows = 0 loops= 1 ) Index Cond: ((((first_name)::text || ' ' ::text) || (last_name)::text) ~ ~ * '%foo%' ::text) Planning time : 0.338 ms Execution time : 0.080 ms (7 rows )Time : 0.975 ms
喔😯,相当的快。使用了Bitmap Index
扫描的,看来索引很有效。
最后 勇敢上文,我们一起了解了一下pg里利用pg_trgm
模块来建立索引优化模糊查询。其实pg里买还有更多好用实用的功能,期待与大家一起探索~。
本文参考