PostgreSQL數(shù)據(jù)庫中如何保證LIKE語句的效率(推薦)
在任何數(shù)據(jù)庫中使用LIKE語句往往都是令人頭疼的一件事，因?yàn)椴簧儆脩舭l(fā)現(xiàn)LIKE語句效率極低，查看執(zhí)行計(jì)劃后發(fā)現(xiàn)原來沒有走索引，那么在Postgresql數(shù)據(jù)中LIKE語句的執(zhí)行效率又是怎樣的呢？我們又該如何提高LIKE語句的執(zhí)行效率呢？
實(shí)驗(yàn)環(huán)境
數(shù)據(jù)庫環(huán)境： PostgreSQL 12.3 X86_64
創(chuàng)建虛擬環(huán)境：
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
為何保證測試效果更直觀，我們使用隨機(jī)數(shù)據(jù)填充一下該表
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);
至此，虛擬數(shù)據(jù)創(chuàng)建完畢。
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) 此時(shí)數(shù)據(jù)庫的狀態(tài)： 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)
結(jié)論：LIKE查詢沒有走索引 創(chuàng)建普通索引： testdb01=# create index idx_testliketb01_username on testliketb01(username); CREATE INDEX 執(zhí)行三遍：analyze testliketb01 ; 重新執(zhí)行LIKE語句，發(fā)現(xiàn)還是沒有走索引 創(chuàng)建包含operator class的索引： testdb01=# create index idx_testliketb01_username on testliketb01(username varchar_pattern_ops); CREATE INDEX 執(zhí)行三遍：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)
結(jié)論：在創(chuàng)建完普通索引并收集統(tǒng)計(jì)信息后數(shù)據(jù)庫在執(zhí)行LIKE語句時(shí)有可能仍然無法使用索引。在創(chuàng)建完帶有操作類的索引收集完統(tǒng)計(jì)信息后，執(zhí)行LIKE語句可以看到正常使用索引，且執(zhí)行效率有了不小提升。
PS：operator class是Postgresql新版中創(chuàng)建索引的新選項(xiàng)，旨在通過制定索引的操作類可以更精準(zhǔn)的收集統(tǒng)計(jì)信息。
為了更精準(zhǔn)的收集統(tǒng)計(jì)信息，我們也可以在初始化或者創(chuàng)建數(shù)據(jù)庫時(shí)將Collate設(shè)置為"C"，這也是Postgresql數(shù)據(jù)中常用的優(yōu)化手段。 我們來測試一下將Collate設(shè)置為"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)
結(jié)論：創(chuàng)建數(shù)據(jù)庫時(shí)將Collate設(shè)置為"C"，即便索引為普通索引，LIKE語句也可以使用索引提升查詢效率。
優(yōu)化建議：
1、初始化數(shù)據(jù)庫或者創(chuàng)建數(shù)據(jù)庫時(shí)將Collate設(shè)置為"C"。
2、創(chuàng)建索引時(shí)指定索引的操作類。（text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops分別支持類型text、varchar和 char上的B-tree索引）
3、優(yōu)化思路，對(duì)于%X的列無法使用索引，可以新增一列 反存儲(chǔ)列，將%X改為X%。
4、創(chuàng)建覆蓋索引，保證復(fù)雜SQL中可以盡可能調(diào)用該索引。
5、調(diào)整業(yè)務(wù)邏輯，盡量不用LIKE語句或者調(diào)整LIKE語句在WHERE中的位置。
到此這篇關(guān)于PostgreSQL數(shù)據(jù)庫中如何保證LIKE語句的效率的文章就介紹到這了,更多相關(guān)PostgreSQL保證LIKE語句的效率內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
解析PostgreSQL中Oid和Relfilenode的映射問題
這篇文章主要介紹了PostgreSQL中Oid和Relfilenode的映射問題,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了Postgresql中xlog生成和清理邏輯操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL時(shí)間日期的語法及注意事項(xiàng)
在開發(fā)過程中,經(jīng)常要取日期的年,月,日,小時(shí)等值,PostgreSQL 提供一個(gè)非常便利的EXTRACT函數(shù),這篇文章主要給大家介紹了關(guān)于PostgreSQL時(shí)間日期的語法及注意事項(xiàng)的相關(guān)資料,需要的朋友可以參考下2023-01-01
這篇文章主要介紹了PostgreSQL教程(十七)：客戶端命令（1）,本文講解了createdb、dropdb、reindexdb、vacuumdb、createuser、dropuser等命令,需要的朋友可以參考下2015-05-05
Postgresql數(shù)據(jù)庫中的json類型字段使用示例詳解
JSON的主要用于在服務(wù)器與web應(yīng)用之間傳輸數(shù)據(jù)，這篇文章主要介紹了Postgresql數(shù)據(jù)庫中的json類型字段使用,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-02-02
PostgreSQL 主備數(shù)據(jù)宕機(jī)恢復(fù)測試方案
這篇文章主要介紹了PostgreSQL 主備數(shù)據(jù)宕機(jī)恢復(fù)測試方案，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL 序列增刪改案例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出的操作代碼
在數(shù)據(jù)庫管理中,數(shù)據(jù)的導(dǎo)入和導(dǎo)出是非常常見的操作,特別是在 PostgreSQL 中,提供了多種工具和方法來實(shí)現(xiàn)數(shù)據(jù)的有效管理,本文將詳細(xì)介紹在 PostgreSQL 中如何進(jìn)行數(shù)據(jù)導(dǎo)入和導(dǎo)出,并給出具體的命令及示例,需要的朋友可以參考下2024-10-10
postgresql之使用lsn 獲取 wal文件名的實(shí)例
這篇文章主要介紹了postgresql之使用lsn 獲取 wal文件名的實(shí)例，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL設(shè)置時(shí)區(qū)、時(shí)間/日期函數(shù)匯總大全
PostgreSQL是一款簡介而又性能強(qiáng)大的數(shù)據(jù)庫應(yīng)用程序,其在日期時(shí)間數(shù)據(jù)方面所支持的功能也都非常給力,這篇文章主要給大家介紹了關(guān)于PostgreSQL設(shè)置時(shí)區(qū)、時(shí)間/日期函數(shù)的相關(guān)資料,需要的朋友可以參考下2023-09-09