pgsql查询优化之模糊查询实例详解

来源:脚本之家  责任编辑:小易  

解决办法:首先尽量避免模糊查询,如果因为业务需要一定要使用模糊查询,则至少保证不要使用全模糊查询,对于右模糊查询,即like ‘…%’,是会使用索引的;左模糊like ‘%’无法直接使用索引,但可以利用reverse + function index 的形式,变化成

前言

一直以来,对于搜索时模糊匹配的优化一直是个让人头疼的问题,好在强大pgsql提供了优化方案,下面就来简单谈一谈如何通过索引来优化模糊匹配

  “火烧红”也叫焗色红翡,是一种人工着色的红翡。翡翠的焗红色,就是对翡翠样品进行加热,使灰黄、褐黄等颜色的翡翠改变成红色的工艺,是一种加热处理。火烧红翡的“色根”常无定向性,杂乱无章或者呈放射状,质地干燥,透明度差结构破裂。烧红一般是大片的均匀红皮,而且薄厚也很均匀,天然红就很少见。  2烧红一般颜色很死,不像天然红那样活--这个需要长期观察才可以。  3天然红带有像绿色色根一样的走向或者点,而且不规则,这个是烧红做不出来的。  4用放大镜或者肉眼看烧红,会发现红色顺着那些表皮的幼裂(不是大裂)走,这是因为烧的时候有裂的部位更容易氧化所致。      5、在横截面上观察会发现烧红没有颜色由深

案例

我们有一张千万级数据的检查报告表,需要通过检查报告来模糊搜索某个条件,我们先创建如下索引:

曾几何时,东北小品几乎是喜剧小品的代名词,作品丰富多彩,明星熠熠生辉,以赵本山和他的一系列经典作品为代表,给观众带来那么多欢乐。那时候,东北小品的选材风格是什么呢?它们所表现的主题大多来源于现实生活,把生活中的细节进行发掘、夸大,表现出底层百姓的喜怒哀乐,其中有底层百姓的小自私,小狡猾,还有小智慧,观众看起来容易产生共鸣。表演风格呢?东北人说话有天生的喜感,到舞台上特别容易放得开,加上社会上流行的俗语歇后语,编剧们创作的类似顺口溜的台词,表演大多热闹夸张,但还没有到喧嚣的程度。随着赵本山退出舞台,何庆魁等人也逐渐淡出,赵本山的徒弟们开始独挡一面,纷纷占据各大喜剧节目的舞台。尽管宋小宝、小沈阳、

CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name);

当然是洋河了,围绕“健康”,洋河在继承优势的基础上,不断创新,攻克技术难题,着力酒的品质提升,如今的洋河微分子、梦之蓝手工班不仅仅是绵柔,更是健康的保证,是蓝色经典的的拓深,洋河倡导的健康饮酒理念将是行业发展的一大趋势。如今,提到绵柔,就会想到洋河;提到健康白酒,就会想到洋河,说明在产品结构不断升级、消费理念不断升级的当下,洋河不断创新为自己注入了源源不断的生命力。聚焦消费升级,推动产品结构优化,聚焦“522”极致化工程,推进渠道极致化落地。洋河把握机会,从新参者到搅局者。短短几年内,梦之蓝如利剑一般成功捅破高端市场,成为“新国酒”的代表。至于未来格局如何,洋河凭借自身的创新能力,使得“茅五洋

然后搜个简单的模糊匹配条件如 LIKE "血常规%",可以发现查询计划生成如下,索引并没有被使用上,这是因为传统的btree索引并不支持模糊匹配

我觉得麻豆穿的最美啊,将星空裙演绎得最好!真的有那仙气的感觉~作为Dior形象大使的Anglelababy拍摄时装杂志也穿了这款裙子,也是美的不可方物!宋智孝台湾国际中文版《Vogue》,这穿的是什么鬼啦?为什么会有一双长筒靴?这样的时尚我真的不懂!讲真,我觉得毫无美感啊~~

查阅文档后发现,pgsql可以在Btree索引上指定操作符:text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops,它们分别对应字段类型text、varchar和char,官方解释为“它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时被使用在涉及模式匹配表达式(LIKE或POSIX正则表达式)的查询中。”, 有些抽象,我们先试试看。创建如下索引并查询刚才的条件 LIKE"血常规%":(参考pgsql的文档https://www.postgresql.org/docs/10/indexes-opclass.html)

CREATE INDEX lab_report_report_name_index ON lab.lab_report (report_name varchar_pattern_ops);

发现确实可以走索引扫描 ,执行时间也从213ms优化到125ms,但是,如果搜索LIKE "%血常规%"就又会走全表扫描了! 这里我们引入本篇博客的主角"pg_trgm"和"pg_bigm"。

创建这两个索引前分别需要引入如下两个扩展包 :

CREATE EXTENSION pg_trgm; CREATE EXTENSION pg_bigm;

这两个索引的区别是:“pg_tigm”为pgsql官方提供的索引,"pg_tigm"为日本开发者提供。下面是详细的对比:(参考pg_bigm的文档http://pgbigm.osdn.jp/pg_bigm_en-1-2.html)

Comparison with pg_trgm

Thepg_trgmcontrib module which provides full text search capability using 3-gram (trigram) model is included in PostgreSQL. The pg_bigm was developed based on the pg_trgm. They have the following differences:

Functionalities and Features pg_trgm pg_bigm
Phrase matching method for full text search 3-gram 2-gram
Available index GIN and GiST GIN only
Available text search operators LIKE (~~), ILIKE (~~*), ~, ~* LIKE only
Full text search for non-alphabetic language

(e.g., Japanese)

Not supported (*1) Supported
Full text search with 1-2 characters keyword Slow (*2) Fast
Similarity search Supported Supported (version 1.1 or later)
Maximum indexed column size 238,609,291 Bytes (~228MB) 107,374,180 Bytes (~102MB)

(*1) You can use full text search for non-alphabetic language by commenting out KEEPONLYALNUM macro variable in contrib/pg_trgm/pg_trgm.h and rebuilding pg_trgm module. But pg_bigm provides faster non-alphabetic search than such a modified pg_trgm.

(*2) Because, in this search, only sequential scan or index full scan (not normal index scan) can run.

pg_bigm 1.1 or later can coexist with pg_trgm in the same database, but pg_bigm 1.0 cannot.

如无特殊要求推荐使用"pg_bigm",我们测试一下效果:

可以使用位图索引扫描,对于本次案例,使用pg_trgm效果同pg_bigm。

以上

本文只是简单的介绍许多细节并未做深入的分析,欢迎留言指教或者讨论

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

扩展阅读,根据您访问的内容系统为您准备了以下内容,希望对您有帮助。

pgsql中模糊查询的字段是计算出来的参数,不是表中的字段该如何解决

select 表1.num,count(表2.num) as 次数 from 表1 left outer join 表2 on 表2.num=表1.num group by 表1.num

如上SQL可以实现,假设表2的字段也是num,希望可以帮到你

补充:

如果表1的123对应表2的12345这样也符合的话:

下面SQL可以实现:

select 表1.num,count(表2.num) as 次数 from 表1 left outer join 表2 on charindex(cast(表1.num as varchar), cast(表2.num as varchar))>0 group by 表1.num

postgresql 模糊查询命令???

也支持 like '%xx%', full text 是内建在SQL 引擎中的,不需要另外开启,速度可以提升30x左右。

postgresql 模糊查询

如果是oracle 的话 试试拼接,like '%'||aip||'%' 我没遇到过这种,模糊查询的值来源于其他表的字段,感觉你可以试试这种思路。

PostgreSQL sql 时间模糊查询

select * from leve a where a.add_date >= '2012-10-01' and a.add_date < '2012-11-01';

在postgresql中测试通过。时间是按范围查询,不是模糊。

postgresql 模糊查询为什么查不了?

你觉得可能存在一条记录即属于2011-10-11同时又属于2011-12-11吗,如果是日期类型最好用日期查询的表达方法,字符查询是sql中效率最低的更多追问追答追问不懂!那我应该怎么写????兄弟在吗》怎么解决!我想查询10月11号和12月11号的数据我这么写报错!请问我应该怎么写才不能报错????追答如果是时间类型的应该这样写

select * FROM Counter_Day_Table WHERE GateNo = 1 and DateTime between '2011-10-11' and '2011-12-11'

或者:

select * FROM Counter_Day_Table WHERE GateNo = 1 and DateTime >='2011-10-11' and DateTime <= '2011-12-11%'追问我想查询单个俩天的数据!假如:2011年10月11号的数据和2012年01月02号的数据追答select * FROM Counter_Day_Table WHERE GateNo = 1 and (DateTime ='2011-10-11' or DateTime = '2011-12-11')

  • 本文相关:
  • postgresql中使用dblink实现跨库查询的方法
  • 在postgresql中实现递归查询的教程
  • postgresql树形结构的递归查询示例
  • postgresql查询效率计算初探
  • postgresql实现批量插入、更新与合并操作的方法
  • 在ubuntu中安装postgresql数据库的步骤详解
  • postgresql中slony-i同步复制部署教程
  • postgresql 安装和简单使用
  • postgresql教程(五):函数和操作符详解(1)
  • postgresql教程(四):数据类型详解
  • postgresql中的ltree类型使用方法
  • postgresql数据库中窗口函数的语法与使用
  • 初识postgresql存储过程
  • postgre数据库insert 、query性能优化详解
  • pgsql中模糊查询的字段是计算出来的参数,不是表中...
  • postgresql 模糊查询命令???
  • postgresql 模糊查询
  • PostgreSQL sql 时间模糊查询
  • postgresql 模糊查询为什么查不了?
  • Postgresql慢查询原因查找
  • 免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved