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

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

根据你的问题描述,你所需要的模糊查询的正确写法有如下几种:1、查询以某字符开头的数据例如,查询teacher表中[姓名]字段所有以'李'开头的记录可写为:select*from teacher where 姓名 like '李%'2、查询以某字符结尾的数据例如,查询teacher表中[姓名]字段所有以'李'结尾的记录可写为:select*from teacher where 姓名 like '%李'3、查询包含某字符的数据例如,查询teacher表中[姓名]字段所有包含'李'的记录(不论该字符在开头、结尾、中间)可写为:select*from teacher where 姓名 like '%李%'注:以上SQL写法是通用性的写法,像Oracle、Sqlserver、Mysql等数据库都是支持的。拓展资料SQL语句的各种模糊查询:一、语法:SELECT 字段 FROM 表 WHERE 某字段 Like 条件二、关于语法中的条件,SQL提供了四种匹配模式:1、%:表示任意0个或多个字符。可匹配任意类型和长度的字符。2、_:表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。3、[]:表示括号内所列字符中的一个(类似正则表达式)。指定一个字符、字符串或范围,要求所匹配对象为它们中的任一个。4、[^]:表示不在括号所列之内的单个字符。其取值和[]相同,但它要求所匹配对象为指定字符以外的任一个字符。5、查询内容包含通配符时:由于通配符的缘故,导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现,而把特殊字符用“[]”括起便可正常查询www.zgxue.com防采集请勿采集本网。

前言

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

如果仅仅是匹配这两个的话 使用 xxx=“北京海淀”or xxx=“北京市海淀区” 如果要匹配带有北京海淀的所有词组只能 like‘%北京海淀?

案例

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

如果可以的话写成like 'XXX%',前面没有那个%的话也可以走索引 前面那个%去不掉的话,oracle有个叫全文索引的可以用,就是很耗空间

CREATE INDEX lab_report_report_name_index ON lab_report USING btree (report_name);

如果仅仅是匹配这两个的话 使用 xxx=“北京海淀”or xxx=“北京市海淀区” 如果要匹配带有北京海淀的所有词组只能 like‘%北京海淀?

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

1、查询语句的词法、语法检查 2、将语句提交给DBMS的查询优化器 3、优化器做代数优化和存取路径的优化 4、由预编译模块生成查询规划 5、然后在合适的时间提交给系统处理执行 6、最后将执行结果返回给用户

查阅文档后发现,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)

一、使用两边加‘%’号查询Oracle通索引所查询效率低 例:select count(*)from lui_user_base t where t.user_name like '%cs%';二、like '.%' like'%.'虽走索引效率依低 三、说使用sql效率提高一0倍数据

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。

以上

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

总结

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

string sql="SELECT Cards.CardNumber,CardTypes.CardTypeName,Clients.ClientName,ConsumeDetails.CdCount,Employee.EmpName,ServiceItems.SiName,ServiceItems.SiPrice,Cards.ClientCreateTime FROM Cards INNER JOIN CardTypes ON Cards.CardTypeID=CardTypes.CardTypeID INNER JOIN Clients ON Cards.ClientID=Clients.ClientID INNER JOIN Consumes ON Clients.ClientID=Consumes.ClientID INNER JOIN ConsumeDetails ON Consumes.ConsumeID=ConsumeDetails.ConsumeID INNER JOIN Employee ON dbo.ConsumeDetails.EmpID=Employee.EmpID INNER JOIN ServiceItems ON ConsumeDetails.SiID=ServiceItems.SiID;string wheresql=";if(string.IsNullOrEmpty(CardNumber)=false){wheresql+string.Format("and Consumes.CardNumber like '%{0}%'",CardNumber);}if(string.IsNullOrEmpty(ClientName)=false){wheresql+string.Format("and Clients.ClientName like '%{0}%'",ClientName);} 展开内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • postgresql中使用dblink实现跨库查询的方法
  • 在postgresql中实现递归查询的教程
  • postgresql树形结构的递归查询示例
  • postgresql查询效率计算初探
  • postgresql教程(十七):客户端命令(1)
  • 深入解读postgresql中的序列及其相关函数的用法
  • postgresql中使用dblink实现跨库查询的方法
  • postgresql教程(八):索引详解
  • postgresql教程(十一):服务器配置
  • postgresql 如何获取当前日期时间及注意事项
  • postgresql 角色与用户管理介绍
  • postgresql之分区表(partitioning)
  • 初识postgresql存储过程
  • postgresql中的ltree类型使用方法
  • MSSQL模糊查询优化
  • 模糊查询
  • SQL 模糊查询如何优化查询速度
  • 大数据量下如何优化全数据模糊查询
  • 求助oracle like%.%模糊查询优化
  • 模糊查询的问题
  • 求解oracle模糊查询优化 like'%a%b%c%'
  • sql server有哪些查询优化方法
  • 求助oracle like%.%模糊查询优化
  • 关于模糊查询
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页postgresqlpostgresql中使用dblink实现跨库查询的方法在postgresql中实现递归查询的教程postgresql树形结构的递归查询示例postgresql查询效率计算初探深入解读postgresql中的序列及其相关函数的用法postgresql中使用dblink实现跨库查询的方法postgresql教程(八):索引详解postgresql教程(十一):服务器配置postgresql 如何获取当前日期时间及注意事项postgresql 角色与用户管理介绍postgresql之分区表(partitioning)初识postgresql存储过程postgresql中的ltree类型使用方法postgresql 角色与用户管理介绍windows下postgresql数据库的下载windows下postgresql安装图解15个postgresql数据库实用命令分postgresql中的oid和xid 说明windows postgresql 安装图文教程postgresql alter语句常用操作小postgresql 安装和简单使用postgresql 创建表分区postgresql新手入门教程postgresql pg_ctl start启动超时实例分析postgresql中的xml操作函数代码初识postgresql存储过程freebsd安装postgresql笔记在windows下手动初始化postgresql数据库教postgresql教程(十三):数据库管理详解postgresql开启远程访问的步骤全纪录postgresql教程(三):表的继承和分区表详postgresql管理工具phppgadmin入门指南postgresql教程(四):数据类型详解
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved