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.13333515.buzz防采集請勿采集本網。

前言

一直以來,對于搜索時模糊匹配的優化一直是個讓人頭疼的問題,好在強大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.13333515.buzz請勿采集。


  • 本文相關:
  • 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.13333515.buzz All Rights Reserved
    3排列五开奖结果