本篇文章给大家分享的是有关怎么在数据库中保证like语句的效率，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。
实验环境
数据库环境： postgresql 12.3 x86_64
创建虚拟环境：
postgres=# create database testdb01 owner highgo; create database postgres=# \c testdb01 highgo testdb01=# create table testtb01 (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);
至此，虚拟数据创建完毕。
testdb01=# select * from testliketb01 limit 10; userid | username | password | description -------- ---------- ---------------------------------- ------------- 1 | 王五 | 4f2bca371b42abd1403d5c20c4542dff | highgo 2 | 李四 | 2a978c605188770c5ed162889fff189e | highgo02 3 | 李四 | f5d129ab728b72ac6f663fe544bc7c16 | highgo 4 | 小明 | 53134fa1022c58e65168b6aa1fbe5e39 | highgo02 5 | 王五 | 2cf9abb2a8b676a626fa2c317d401ed8 | highgo02 6 | 王五 | 2247a0cfda1f2819554d6e8e454622eb | highgo02 7 | 张三 | 59dfdc680c17533dfba1c72c9ce0bf76 | highgo02 8 | 王五 | 87db4258236a3826259dcc3e7cb5fc63 | highgo02 9 | 王五 | baaf7a2f7027df9aaeb665121432b6e2 | highgo02 10 | 王五 | 2f8fb36b3227c795b111b9bd5b031a76 | highgo02 (10 rows) 此时数据库的状态： testdb01=# \l testdb01 list of databases name | owner | encoding | collate | ctype | access privileges | size | tablespace | description ---------- -------- ---------- ------------- ------------- ------------------- ------- ------------ ------------- testdb01 | highgo | utf8 | en_us.utf-8 | en_us.utf-8 | | 59 mb | pg_default | (1 row)
简单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=# \l testdb02 list of databases name | owner | encoding | collate | ctype | access privileges | size | tablespace | description ---------- -------- ---------- --------- ------- ------------------- ------- ------------ ------------- testdb02 | highgo | utf8 | c | c | | 59 mb | pg_default | (1 row) 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)
结论：创建数据库时将collate设置为"c"，即便索引为普通索引，like语句也可以使用索引提升查询效率。
优化建议：
1、初始化数据库或者创建数据库时将collate设置为"c"。
2、创建索引时指定索引的操作类。（text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops分别支持类型text、varchar和 char上的b-tree索引）
3、优化思路，对于%x的列无法使用索引，可以新增一列 反存储列，将%x改为x%。
4、创建覆盖索引，保证复杂sql中可以尽可能调用该索引。
5、调整业务逻辑，尽量不用like语句或者调整like语句在where中的位置。
以上就是怎么在postgresql数据库中保证like语句的效率，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注本站行业资讯频道。
