写点什么

解读数仓常用模糊查询的优化方法

  • 2022-11-15
    贵州
  • 本文字数:3973 字

    阅读完需:约 13 分钟

本文分享自华为云社区《GaussDB(DWS) 模糊查询性能优化》,作者: 黎明的风。


在使用 GaussDB(DWS)时,通过 like 进行模糊查询,有时会遇到查询性能慢的问题。


(一)LIKE 模糊查询


通常的查询语句如下:


select  * from t1 where c1 like 'A123%';
复制代码


当表 t1 的数据量大时,使用 like 进行模糊查询,查询的速度非常慢。


通过 explain 查看该语句生成的查询计划:


test=# explain select * from t1 where c1 like 'A123%';                                 QUERY PLAN                                  -----------------------------------------------------------------------------  id |          operation           | E-rows | E-memory | E-width | E-costs  ----+------------------------------+--------+----------+---------+---------   1 | ->  Streaming (type: GATHER) |      1 |          |       8 | 16.25      2 |    ->  Seq Scan on t1        |      1 | 1MB      |       8 | 10.25     Predicate Information (identified by plan id) ---------------------------------------------   2 --Seq Scan on t1         Filter: (c1 ~~ 'A123%'::text)
复制代码


查询计划显示对表 t1 进行了全表扫描,因此在表 t1 数据量大的时候执行速度会比较慢。


上面查询的模糊匹配条件 'A123%',我们称它为后模糊匹配。这种场景,可以通过建立一个 BTREE 索引来提升查询性能。


建立索引时需要根据字段数据类型设置索引对应的 operator,对于 text,varchar 和 char 分别设置和 text_pattern_ops,varchar_pattern_ops 和 bpchar_pattern_ops。


例如上面例子里的 c1 列的类型为 text,创建索引时增加 text_pattern_ops,建立索引的语句如下:


CREATE INDEX ON t1 (c1 text_pattern_ops);
复制代码


增加索引后打印查询计划:


test=# explain select * from t1 where c1 like 'A123%';                                       QUERY PLAN                                       ----------------------------------------------------------------------------------------  id |                operation                | E-rows | E-memory | E-width | E-costs  ----+-----------------------------------------+--------+----------+---------+---------   1 | ->  Streaming (type: GATHER)            |      1 |          |       8 | 14.27      2 |    ->  Index Scan using t1_c1_idx on t1 |      1 | 1MB      |       8 | 8.27                  Predicate Information (identified by plan id)              ----------------------------------------------------------------------   2 --Index Scan using t1_c1_idx on t1         Index Cond: ((c1 ~>=~ 'A123'::text) AND (c1 ~<~ 'A124'::text))         Filter: (c1 ~~ 'A123%'::text)        
复制代码


在创建索引后,可以看到语句执行时会使用到前面创建的索引,执行速度会变快。


前面遇到的问题使用的查询条件是后缀的模糊查询,如果使用的是前缀的模糊查询,我们可以看一下查询计划是否有使用到索引。


test=# explain select * from t1 where c1 like '%A123';                                 QUERY PLAN                                  -----------------------------------------------------------------------------  id |          operation           | E-rows | E-memory | E-width | E-costs  ----+------------------------------+--------+----------+---------+---------   1 | ->  Streaming (type: GATHER) |      1 |          |       8 | 16.25      2 |    ->  Seq Scan on t1        |      1 | 1MB      |       8 | 10.25     Predicate Information (identified by plan id) ---------------------------------------------   2 --Seq Scan on t1         Filter: (c1 ~~ '%A123'::text)
复制代码


如上图所示,当查询条件变成前缀的模糊查询,之前建的索引将不能使用到,查询执行时进行了全表的扫描。


这种情况,我们可以使用翻转函数(reverse),建立一个索引来支持前模糊的查询,建立索引的语句如下:


CREATE INDEX ON t1 (reverse(c1) text_pattern_ops);
复制代码


将查询语句的条件采用 reverse 函数进行改写之后,输出查询计划:


test=# explain select * from t1 where reverse(c1) like 'A123%';                                        QUERY PLAN                                        ------------------------------------------------------------------------------------------  id |           operation           | E-rows | E-memory | E-width | E-costs  ----+-------------------------------+--------+----------+---------+---------   1 | ->  Streaming (type: GATHER)  |      5 |          |       8 | 14.06      2 |    ->  Bitmap Heap Scan on t1 |      5 | 1MB      |       8 | 8.06       3 |       ->  Bitmap Index Scan   |      5 | 1MB      |       0 | 4.28                           Predicate Information (identified by plan id)                       ----------------------------------------------------------------------------------------   2 --Bitmap Heap Scan on t1         Filter: (reverse(c1) ~~ 'A123%'::text)   3 --Bitmap Index Scan         Index Cond: ((reverse(c1) ~>=~ 'A123'::text) AND (reverse(c1) ~<~ 'A124'::text))
复制代码


语句经过改写后,可以走索引, 查询性能得到提升。


(二)指定 collate 来创建索引


如果使用默认的 index ops class 时,要使 b-tree 索引支持模糊的查询,就需要在查询和建索引时都指定 collate="C"。


注意:索引和查询条件的 collate 都一致的情况下才能使用索引。


创建索引的语句为:


CREATE INDEX ON t1 (c1 collate "C");
复制代码


查询语句的 where 条件中需要增加 collate 的设置:


test=# explain select * from t1 where c1 like 'A123%' collate "C";                                       QUERY PLAN                                       ----------------------------------------------------------------------------------------  id |                operation                | E-rows | E-memory | E-width | E-costs  ----+-----------------------------------------+--------+----------+---------+---------   1 | ->  Streaming (type: GATHER)            |      1 |          |       8 | 14.27      2 |    ->  Index Scan using t1_c1_idx on t1 |      1 | 1MB      |       8 | 8.27                Predicate Information (identified by plan id)            ------------------------------------------------------------------   2 --Index Scan using t1_c1_idx on t1         Index Cond: ((c1 >= 'A123'::text) AND (c1 < 'A124'::text))         Filter: (c1 ~~ 'A123%'::text COLLATE "C")
复制代码


(三)GIN 倒排索引


GIN(Generalized Inverted Index)通用倒排索引。设计为处理索引项为组合值的情况,查询时需要通过索引搜索出出现在组合值中的特定元素值。例如,文档是由多个单词组成,需要查询出文档中包含的特定单词。


下面举例说明 GIN 索引的使用方法:


create table gin_test_data(id int, chepai varchar(10), shenfenzheng varchar(20), duanxin text) distribute by hash (id);
create index chepai_idx on gin_test_data using gin(to_tsvector('ngram', chepai)) with (fastupdate=on);
复制代码


上述语句在车牌的列上建立了一个 GIN 倒排索引。


如果要根据车牌进行模糊查询,可以使用下面的语句:


select count(*) from gin_test_data where to_tsvector('ngram', chepai) @@ to_tsquery('ngram', '湘F');
复制代码


这个语句的查询计划如下:


test=# explain select count(*) from gin_test_data where to_tsvector('ngram', chepai) @@ to_tsquery('ngram', '湘F');                                            QUERY PLAN                                           ------------------------------------------------------------------------------------------------  id |                   operation                    | E-rows | E-memory | E-width | E-costs  ----+------------------------------------------------+--------+----------+---------+---------   1 | ->  Aggregate                                  |      1 |          |       8 | 18.03      2 |    ->  Streaming (type: GATHER)                |      1 |          |       8 | 18.03      3 |       ->  Aggregate                            |      1 | 1MB      |       8 | 12.03      4 |          ->  Bitmap Heap Scan on gin_test_data |      1 | 1MB      |       0 | 12.02      5 |             ->  Bitmap Index Scan              |      1 | 1MB      |       0 | 8.00                              Predicate Information (identified by plan id)                          ----------------------------------------------------------------------------------------------   4 --Bitmap Heap Scan on gin_test_data         Recheck Cond: (to_tsvector('ngram'::regconfig, (chepai)::text) @@ '''湘f'''::tsquery)   5 --Bitmap Index Scan         Index Cond: (to_tsvector('ngram'::regconfig, (chepai)::text) @@ '''湘f'''::tsquery)
复制代码


查询中使用了倒排索引,因此有比较的好的执行性能。


点击关注,第一时间了解华为云新鲜技术~

发布于: 刚刚阅读数: 4
用户头像

提供全面深入的云计算技术干货 2020-07-14 加入

华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态,方便开发者快速成长与发展,欢迎提问、互动,多方位了解云计算! 传送门:https://bbs.huaweicloud.com/

评论

发布
暂无评论
解读数仓常用模糊查询的优化方法_数据库_华为云开发者联盟_InfoQ写作社区