|
在任何数据库中使用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);
至此，虚拟数据创建完毕。
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 ;
3、优化思路，对于%X的列无法使用索引，可以新增一列 反存储列，将%X改为X%。
4、创建覆盖索引，保证复杂SQL中可以尽可能调用该索引。
5、调整业务逻辑，尽量不用LIKE语句或者调整LIKE语句在WHERE中的位置。
到此这篇关于PostgreSQL数据库中如何保证LIKE语句的效率的文章就介绍到这了,更多相关PostgreSQL保证LIKE语句的效率内容请搜索社区以前的文章或继续浏览下面的相关文章希望大家以后多多支持社区！ | 
转播
