在任何數據庫中使用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 ;
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語句的效率的文章就介紹到這了,更多相關PostgreSQL保證LIKE語句的效率內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- postgresql 中的 like 查詢優化方案
- PostgreSQL regexp_matches替換like模糊查詢的操作
- postgresql關于like%xxx%的優化操作
- PostgreSQL LIKE 大小寫實例
- Postgresql中LIKE和ILIKE操作符的用法詳解
