PostgreSQL数据库中如何保证LIKE语句的效率(推荐)

随心笔谈2年前发布编辑

167 0

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

在任何数据库中使用LIKE语句往往都是令人头疼的一件事，因为不少用户发现LIKE语句效率极低，查看执行计划后发现原来没有走索引，那么在Postgresql数据中LIKE语句的执行效率又是怎样的呢？我们又该如何提高LIKE语句的执行效率呢？

数据库环境： PostgreSQL 12.3 X86_64

创建虚拟环境：

postgres=# create database testdb01 owner highgo;
CREATE DATABASE
postgres=# \c testdb01 highgo

testdb01=# create table testliketb01 (userid int primary key,username varchar(20),password varchar(60),description text);
CREATE TABLE

为何保证测试效果更直观，我们使用随机数据填充一下该表

testdb01=# insert into testliketb01 select generate_series(1,500000),split_part(‘张三,李四,王五,小明,小红’,’,’,(random()*(5-1)+1)::int),md5((random()*(5-1)+1)::varchar),split_part(‘highgo,highgo02,highgo03′,’,’,(random()*(3-1)+1)::int);

至此，虚拟数据创建完毕。

简单LIKE语句查询：

testdb01=# explain analyze select * from testliketb01 where username like ‘王%’;
                                                      QUERY PLAN
———————————————————————————————————————–
Seq Scan on testliketb01  (cost=0.00..11405.00 rows=125350 width=52) (actual time=0.014..177.571 rows=124952 loops=1)
   Filter: ((username)::text ~~ ‘王%’::text)
   Rows Removed by Filter: 375048
Planning Time: 0.121 ms
Execution Time: 190.554 ms
(5 rows)

结论：LIKE查询没有走索引创建普通索引： testdb01=# create index idx_testliketb01_username on testliketb01(username); CREATE INDEX 执行三遍：analyze testliketb01 ; 重新执行LIKE语句，发现还是没有走索引创建包含operator class的索引： testdb01=# create index idx_testliketb01_username on testliketb01(username varchar_pattern_ops); CREATE INDEX 执行三遍：analyze testliketb01 ;

testdb01=# explain analyze select * from testliketb01 where username like ‘王%’;
                                                                   QUERY PLAN
————————————————————————————————————————————————-
Bitmap Heap Scan on testliketb01  (cost=2665.26..9387.14 rows=125350 width=52) (actual time=31.383..94.745 rows=124952 loops=1)
   Filter: ((username)::text ~~ ‘王%’::text)
   Heap Blocks: exact=5155
   ->  Bitmap Index Scan on idx_testliketb01_username  (cost=0.00..2633.92 rows=125350 width=0) (actual time=29.730..29.730 rows=124952 loops=1)
         Index Cond: (((username)::text ~>=~ ‘王’::text) AND ((username)::text ~<~ ‘玌’::text))
Planning Time: 0.111 ms
Execution Time: 107.030 ms
(7 rows)

结论：在创建完普通索引并收集统计信息后数据库在执行LIKE语句时有可能仍然无法使用索引。在创建完带有操作类的索引收集完统计信息后，执行LIKE语句可以看到正常使用索引，且执行效率有了不小提升。

PS：operator class是Postgresql新版中创建索引的新选项，旨在通过制定索引的操作类可以更精准的收集统计信息。

为了更精准的收集统计信息，我们也可以在初始化或者创建数据库时将Collate设置为”C”，这也是Postgresql数据中常用的优化手段。我们来测试一下将Collate设置为”C”的效果：

testdb01=# create database testdb02 with TEMPLATE template0 LC_COLLATE=’C’ LC_CTYPE=’C’ owner highgo;
CREATE DATABASE

testdb02=# create index idx_testliketb01_username on testliketb01(username);
CREATE INDEX
testdb02=# analyze testliketb01 ;
ANALYZE
testdb02=# analyze testliketb01 ;
ANALYZE
testdb02=# analyze testliketb01 ;
ANALYZE
testdb02=#  explain analyze select * from testliketb01 where username like ‘王%’;
                                                                   QUERY PLAN
————————————————————————————————————————————————-
Bitmap Heap Scan on testliketb01  (cost=2680.26..9410.67 rows=126033 width=52) (actual time=35.262..99.052 rows=124992 loops=1)
   Filter: ((username)::text ~~ ‘王%’::text)
   Heap Blocks: exact=5155
   ->  Bitmap Index Scan on idx_testliketb01_username  (cost=0.00..2648.75 rows=126033 width=0) (actual time=33.920..33.920 rows=124992 loops=1)
         Index Cond: (((username)::text >=’王’::text) AND ((username)::text < ‘玌’::text))
Planning Time: 0.276 ms
Execution Time: 111.578 ms
(7 rows)