PostgreSQL数据库中如何保证LIKE语句的效率(推荐)
在任何数据库中使用LIKE语句往往都是令人头疼的一件事，因为不少用户发现LIKE语句效率极低，查看执行计划后发现原来没有走索引，那么在Postgresql数据中LIKE语句的执行效率又是怎样的呢？我们又该如何提高LIKE语句的执行效率呢？
实验环境
数据库环境：PostgreSQL12.3 X86_64
创建虚拟环境：
postgres=#createdatabasetestdb01ownerhighgo; CREATEDATABASE postgres=#\ctestdb01highgo testdb01=#createtabletestliketb01(useridintprimarykey,usernamevarchar(20),passwordvarchar(60),descriptiontext); CREATETABLE
为何保证测试效果更直观，我们使用随机数据填充一下该表
testdb01=#insertintotestliketb01selectgenerate_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*fromtestliketb01limit10;
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
(10rows)
此时数据库的状态：
testdb01=#\l+testdb01
Listofdatabases
Name |Owner |Encoding| Collate | Ctype |Accessprivileges|Size |Tablespace|Description
----------+--------+----------+-------------+-------------+-------------------+-------+------------+-------------
testdb01|highgo|UTF8 |en_US.UTF-8|en_US.UTF-8| |59MB|pg_default|
(1row)
简单LIKE语句查询：
testdb01=#explainanalyzeselect*fromtestliketb01whereusernamelike'王%';
QUERYPLAN
-----------------------------------------------------------------------------------------------------------------------
SeqScanontestliketb01 (cost=0.00..11405.00rows=125350width=52)(actualtime=0.014..177.571rows=124952loops=1)
Filter:((username)::text~~'王%'::text)
RowsRemovedbyFilter:375048
PlanningTime:0.121ms
ExecutionTime:190.554ms
(5rows)
结论：LIKE查询没有走索引 创建普通索引：testdb01=#createindexidx_testliketb01_usernameontestliketb01(username);CREATEINDEX执行三遍：analyzetestliketb01;重新执行LIKE语句，发现还是没有走索引 创建包含operatorclass的索引：testdb01=#createindexidx_testliketb01_usernameontestliketb01(usernamevarchar_pattern_ops);CREATEINDEX执行三遍：analyzetestliketb01;
testdb01=#explainanalyzeselect*fromtestliketb01whereusernamelike'王%';
QUERYPLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
BitmapHeapScanontestliketb01 (cost=2665.26..9387.14rows=125350width=52)(actualtime=31.383..94.745rows=124952loops=1)
Filter:((username)::text~~'王%'::text)
HeapBlocks:exact=5155
-> BitmapIndexScanonidx_testliketb01_username (cost=0.00..2633.92rows=125350width=0)(actualtime=29.730..29.730rows=124952loops=1)
IndexCond:(((username)::text~>=~'王'::text)AND((username)::text~<~'玌'::text))
PlanningTime:0.111ms
ExecutionTime:107.030ms
(7rows)
结论：在创建完普通索引并收集统计信息后数据库在执行LIKE语句时有可能仍然无法使用索引。在创建完带有操作类的索引收集完统计信息后，执行LIKE语句可以看到正常使用索引，且执行效率有了不小提升。
PS：operatorclass是Postgresql新版中创建索引的新选项，旨在通过制定索引的操作类可以更精准的收集统计信息。
为了更精准的收集统计信息，我们也可以在初始化或者创建数据库时将Collate设置为"C"，这也是Postgresql数据中常用的优化手段。 我们来测试一下将Collate设置为"C"的效果：
testdb01=#createdatabasetestdb02withTEMPLATEtemplate0 LC_COLLATE='C' LC_CTYPE='C'ownerhighgo;
CREATEDATABASE
testdb02=#\l+testdb02
Listofdatabases
Name |Owner |Encoding|Collate|Ctype|Accessprivileges|Size |Tablespace|Description
----------+--------+----------+---------+-------+-------------------+-------+------------+-------------
testdb02|highgo|UTF8 |C |C | |59MB|pg_default|
(1row)
testdb02=#createindexidx_testliketb01_usernameontestliketb01(username);
CREATEINDEX
testdb02=#analyzetestliketb01;
ANALYZE
testdb02=#analyzetestliketb01;
ANALYZE
testdb02=#analyzetestliketb01;
ANALYZE
testdb02=# explainanalyzeselect*fromtestliketb01whereusernamelike'王%';
QUERYPLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
BitmapHeapScanontestliketb01 (cost=2680.26..9410.67rows=126033width=52)(actualtime=35.262..99.052rows=124992loops=1)
Filter:((username)::text~~'王%'::text)
HeapBlocks:exact=5155
-> BitmapIndexScanonidx_testliketb01_username (cost=0.00..2648.75rows=126033width=0)(actualtime=33.920..33.920rows=124992loops=1)
IndexCond:(((username)::text>='王'::text)AND((username)::text<'玌'::text))
PlanningTime:0.276ms
ExecutionTime:111.578ms
(7rows)
结论：创建数据库时将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语句的效率的文章就介绍到这了,更多相关PostgreSQL保证LIKE语句的效率内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。