• 售前

  • 售后

热门帖子
入门百科

PostgreSQL数据库中怎样保证LIKE语句的服从(保举)

[复制链接]
老橡树1 显示全部楼层 发表于 2021-8-14 14:48:24 |阅读模式 打印 上一主题 下一主题
在任何数据库中使用LIKE语句往往都是令人头疼的一件事,因为不少用户发现LIKE语句效率极低,查看实行筹划后发现原来没有走索引,那么在Postgresql数据中LIKE语句的实行效率又是怎样的呢?我们又该怎样进步LIKE语句的实行效率呢?
  实验环境

数据库环境: PostgreSQL 12.3  X86_64  
创建假造环境:
  1. postgres=# create database testdb01 owner highgo;
  2. CREATE DATABASE
  3. postgres=# \c testdb01 highgo
  4. testdb01=# create table testliketb01 (userid int primary key,username varchar(20),password varchar(60),description text);
  5. CREATE TABLE
复制代码
为何保证测试结果更直观,我们使用随机数据填充一下该表
  1. 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);
复制代码
至此,假造数据创建完毕。
  1. testdb01=# select * from testliketb01 limit 10;
  2. userid | username |             password             | description
  3. --------+----------+----------------------------------+-------------
  4.       1 | 王五     | 4f2bca371b42abd1403d5c20c4542dff | highgo
  5.       2 | 李四     | 2a978c605188770c5ed162889fff189e | highgo02
  6.       3 | 李四     | f5d129ab728b72ac6f663fe544bc7c16 | highgo
  7.       4 | 小明     | 53134fa1022c58e65168b6aa1fbe5e39 | highgo02
  8.       5 | 王五     | 2cf9abb2a8b676a626fa2c317d401ed8 | highgo02
  9.       6 | 王五     | 2247a0cfda1f2819554d6e8e454622eb | highgo02
  10.       7 | 张三     | 59dfdc680c17533dfba1c72c9ce0bf76 | highgo02
  11.       8 | 王五     | 87db4258236a3826259dcc3e7cb5fc63 | highgo02
  12.       9 | 王五     | baaf7a2f7027df9aaeb665121432b6e2 | highgo02
  13.      10 | 王五     | 2f8fb36b3227c795b111b9bd5b031a76 | highgo02
  14. (10 rows)
  15. 此时数据库的状态:
  16. testdb01=# \l+ testdb01
  17.                                                 List of databases
  18.    Name   | Owner  | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description
  19. ----------+--------+----------+-------------+-------------+-------------------+-------+------------+-------------
  20. testdb01 | highgo | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 59 MB | pg_default |
  21. (1 row)
复制代码
简朴LIKE语句查询:
  1. testdb01=# explain analyze select * from testliketb01 where username like '王%';
  2.                                                       QUERY PLAN                                                       
  3. -----------------------------------------------------------------------------------------------------------------------
  4. Seq Scan on testliketb01  (cost=0.00..11405.00 rows=125350 width=52) (actual time=0.014..177.571 rows=124952 loops=1)
  5.    Filter: ((username)::text ~~ '王%'::text)
  6.    Rows Removed by Filter: 375048
  7. Planning Time: 0.121 ms
  8. Execution Time: 190.554 ms
  9. (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 ;   
  1. testdb01=# explain analyze select * from testliketb01 where username like '王%';
  2.                                                                    QUERY PLAN                                                                    
  3. -------------------------------------------------------------------------------------------------------------------------------------------------
  4. Bitmap Heap Scan on testliketb01  (cost=2665.26..9387.14 rows=125350 width=52) (actual time=31.383..94.745 rows=124952 loops=1)
  5.    Filter: ((username)::text ~~ '王%'::text)
  6.    Heap Blocks: exact=5155
  7.    ->  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)
  8.          Index Cond: (((username)::text ~>=~ '王'::text) AND ((username)::text ~<~ '玌'::text))
  9. Planning Time: 0.111 ms
  10. Execution Time: 107.030 ms
  11. (7 rows)
复制代码
结论:在创建完普通索引并网络统计信息后数据库在实行LIKE语句时有大概仍旧无法使用索引。在创建完带有操纵类的索引网络完统计信息后,实行LIKE语句可以看到正常使用索引,且实行效率有了不小提升。  
PS:operator class是Postgresql新版中创建索引的新选项,旨在通过制定索引的操纵类可以更精准的网络统计信息。
  为了更精准的网络统计信息,我们也可以在初始化大概创建数据库时将Collate设置为"C",这也是Postgresql数据中常用的优化手段。   我们来测试一下将Collate设置为"C"的结果:
  1. testdb01=# create database testdb02 with TEMPLATE template0  LC_COLLATE='C'  LC_CTYPE ='C' owner highgo;
  2. CREATE DATABASE
  3. testdb02=# \l+ testdb02
  4.                                            List of databases
  5.    Name   | Owner  | Encoding | Collate | Ctype | Access privileges | Size  | Tablespace | Description
  6. ----------+--------+----------+---------+-------+-------------------+-------+------------+-------------
  7. testdb02 | highgo | UTF8     | C       | C     |                   | 59 MB | pg_default |
  8. (1 row)
  9. testdb02=# create index idx_testliketb01_username on testliketb01(username);
  10. CREATE INDEX
  11. testdb02=# analyze testliketb01 ;
  12. ANALYZE
  13. testdb02=# analyze testliketb01 ;
  14. ANALYZE
  15. testdb02=# analyze testliketb01 ;
  16. ANALYZE
  17. testdb02=#  explain analyze select * from testliketb01 where username like '王%';
  18.                                                                    QUERY PLAN                                                                    
  19. -------------------------------------------------------------------------------------------------------------------------------------------------
  20. Bitmap Heap Scan on testliketb01  (cost=2680.26..9410.67 rows=126033 width=52) (actual time=35.262..99.052 rows=124992 loops=1)
  21.    Filter: ((username)::text ~~ '王%'::text)
  22.    Heap Blocks: exact=5155
  23.    ->  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)
  24.          Index Cond: (((username)::text >= '王'::text) AND ((username)::text < '玌'::text))
  25. Planning Time: 0.276 ms
  26. Execution Time: 111.578 ms
  27. (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语句的效率内容请搜刮草根技术分享从前的文章或继续欣赏下面的相关文章盼望大家以后多多支持草根技术分享!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作