怎么在PostgreSQL數(shù)據(jù)庫中保證LIKE語句的效率-創(chuàng)新互聯(lián)

本篇文章給大家分享的是有關(guān)怎么在PostgreSQL數(shù)據(jù)庫中保證LIKE語句的效率，小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

成都創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站建設(shè)、成都網(wǎng)站建設(shè)、沙坡頭網(wǎng)絡(luò)推廣、重慶小程序開發(fā)、沙坡頭網(wǎng)絡(luò)營銷、沙坡頭企業(yè)策劃、沙坡頭品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們大的嘉獎；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供沙坡頭建站搭建服務(wù)，24小時(shí)服務(wù)熱線：13518219792，官方網(wǎng)址：muchs.cn

實(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)化思路，對于%X的列無法使用索引，可以新增一列反存儲列，將%X改為X%。

4、創(chuàng)建覆蓋索引，保證復(fù)雜SQL中可以盡可能調(diào)用該索引。

5、調(diào)整業(yè)務(wù)邏輯，盡量不用LIKE語句或者調(diào)整LIKE語句在WHERE中的位置。

以上就是怎么在PostgreSQL數(shù)據(jù)庫中保證LIKE語句的效率，小編相信有部分知識點(diǎn)可能是我們?nèi)粘９ぷ鲿姷交蛴玫降摹ＯＭ隳芡ㄟ^這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

當(dāng)前名稱：怎么在PostgreSQL數(shù)據(jù)庫中保證LIKE語句的效率-創(chuàng)新互聯(lián)
本文網(wǎng)址：http://muchs.cn/article10/deidgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、軟件開發(fā)、營銷型網(wǎng)站建設(shè)、建站公司、用戶體驗(yàn)、定制網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容