主頁 > 資料庫 > 單表千萬行資料庫 LIKE 搜索優化手記

單表千萬行資料庫 LIKE 搜索優化手記

2020-09-10 06:13:25 資料庫

我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索,LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式,

如果需要查找客戶表中所有姓氏是“張”的資料,可以使用下面的 SQL 陳述句:

SELECT * FROM Customer WHERE Name LIKE '張%'

如果需要查找客戶表中所有手機尾號是“1234”的資料,可以使用下面的 SQL 陳述句:

SELECT * FROM Customer WHERE Phone LIKE '%123456'

如果需要查找客戶表中所有名字中包含“秀”的資料,可以使用下面的 SQL 陳述句:

SELECT * FROM Customer WHERE Name LIKE '%秀%'

以上三種分別對應了:左前綴匹配、右后綴匹配和模糊查詢,并且對應了不同的查詢優化方式,

資料概覽

現在有一張名為 tbl_like 的資料表,表中包含了四大名著中的全部陳述句,資料條數上千萬:

左前綴匹配查詢優化

如果要查詢所有以“孫悟空”開頭的句子,可以使用下面的 SQL 陳述句:

SELECT * FROM tbl_like WHERE txt LIKE '孫悟空%'

SQL Server 資料庫比較強大,耗時八百多毫秒,并不算快:

我們可以在 txt 列上建立索引,用于優化該查詢:

CREATE INDEX tbl_like_txt_idx ON [tbl_like] ( [txt] )

應用索引后,查詢速度大大加快,僅需 5 毫秒:

由此可知:對于左前綴匹配,我們可以通過增加索引的方式來加快查詢速度,

右后綴匹配查詢優化

在右后綴匹配查詢中,上述索引對右后綴匹配并不生效,使用以下 SQL 陳述句查詢所有以“孫悟空”結尾的資料:

SELECT * FROM tbl_like WHERE txt LIKE '%孫悟空'

效率十分低下,耗時達到了 2.5秒:

我們可以采用“以空間換時間”的方式來解決右后綴匹配查詢時效率低下的問題,

簡單來說,我們可以將字串倒過來,讓右后綴匹配變成左前綴匹配,以“防著古海回來再抓孫悟空”為例,將其倒置之后的字串是“空悟孫抓再來回海古著防”,當需要查找結尾為“孫悟空”的資料時,去查找以“空悟孫”開頭的資料即可,

具體做法是:在該表中增加“txt_back”列,將“txt”列的值倒置后,填入“txt_back”列中,最后為 “txt_back”列增加索引,

ALTER TABLE tbl_like ADD txt_back nvarchar(1000);-- 增加資料列
UPDATE tbl_like SET txt_back = reverse(txt); -- 填充 txt_back 的值
CREATE INDEX tbl_like_txt_back_idx ON [tbl_like] ( [txt_back] );--  txt_back 列增加索引

資料表調整之后,我們的 SQL 陳述句也需要調整:

SELECT * FROM tbl_like WHERE txt_back LIKE '空悟孫%'

此番操作下來,執行速度就非常迅速了:

由此可知:對于右后綴匹配,我們可以建立倒序欄位將右后綴匹配變成左前綴匹配來加快查詢速度,

模糊查詢優化

在查詢所有包含“悟空”的陳述句時,我們使用以下的 SQL 陳述句:

SELECT * FROM tbl_like WHERE txt LIKE '%悟空%'

該陳述句無法利用到索引,所以查詢非常慢,需要 2.7 秒:

遺憾的是,我們并沒有一個簡單的辦法可以優化這個查詢,但沒有簡單的辦法,并不代表沒有辦法,解決辦法之一就是:分詞+倒排索引,

分詞就是將連續的字序列按照一定的規范重新組合成詞序列的程序,我們知道,在英文的行文中,單詞之間是以空格作為自然分界符的,而中文只是字、句和段能通過明顯的分界符來簡單劃界,唯獨詞沒有一個形式上的分界符,雖然英文也同樣存在短語的劃分問題,不過在詞這一層上,中文比之英文要復雜得多、困難得多,

倒排索引源于實際應用中需要根據屬性的值來查找記錄,這種索引表中的每一項都包括一個屬性值和具有該屬性值的各記錄的地址,由于不是由記錄來確定屬性值,而是由屬性值來確定記錄的位置,因而稱為倒排索引(inverted index),帶有倒排索引的檔案我們稱為倒排索引檔案,簡稱倒排檔案(inverted file),

以上兩段讓人摸不著頭腦的文字來自百度百科,你可以和我一樣選擇忽略他,

我們不需要特別高超的分詞技巧,因為漢語的特性,我們只需“二元”分詞即可,

所謂二元分詞,即將一段話中的文字每兩個字符作為一個詞來分詞,還是以“防著古海回來再抓孫悟空”這句話為例,進行二元分詞之后,得到的結果是:防著、著古、古海,海回,回來,來再,再抓,抓孫,孫悟,悟空,使用 C# 簡單實作一下:

public static List<String> Cut(String str)
{
       var list = new List<String>();
       var buffer = new Char[2];
       for (int i = 0; i < str.Length - 1; i++)
       {
             buffer[0] = str[i];
             buffer[1] = str[i + 1];
             list.Add(new String(buffer));
       }
       return list;
}

測驗一下結果:

我們需要一張資料表,把分詞后的詞條和原始資料對應起來,為了獲得更好的效率,我們還用到了覆寫索引:

CREATE TABLE tbl_like_word (
  [id] int identity,
  [rid] int NOT NULL,
  [word] nchar(2) NOT NULL,
  PRIMARY KEY CLUSTERED ([id])
);
CREATE INDEX tbl_like_word_word_idx ON tbl_like_word(word,rid);-- 覆寫索引(Covering index

以上 SQL 陳述句創建了一張名為 ”tbl_like_word“的資料表,并為其 ”word“和“rid”列增加了聯合索引,這就是我們的倒排表,接下來就是為其填充資料,

為了便于演示,筆者使用了 LINQPad 來做資料處理,對該工具感興趣的朋友,可以參看筆者之前的文章:《.NET 程式員的 Playground :LINQPad》,文章中對 LINQPad 做了一個簡要的介紹,鏈接地址是:https://www.coderbusy.com/archives/432.html ,

我們需要先用 LINQPad 自帶的資料庫鏈接功能鏈接至資料庫,之后就可以在 LINQPad 中與資料庫互動了,首先按 Id 順序每 3000 條一批讀取 tbl_like 表中的資料,對 txt 欄位的值分詞后生成 tbl_like_word 所需的資料,之后將資料批量入庫,完整的 LINQPad 代碼如下:

void Main()
{
       var maxId = 0;
       const int limit = 3000;
       var wordList = new List<Tbl_like_word>();
       while (true)
       {
             $"開始處理:{maxId} 之后 {limit} 條".Dump("Log");
             //分批次讀取
             var items = Tbl_likes
             .Where(i => i.Id > maxId)
             .OrderBy(i => i.Id)
             .Select(i => new { i.Id, i.Txt })
             .Take(limit)
             .ToList();
             if (items.Count == 0)
             {
                    break;
             }
             //逐條生產
             foreach (var item in items)
             {
                    maxId = item.Id;
                    //單個字的資料跳過
                    if (item.Txt.Length < 2)
                    {
                           continue;
                    }
                    var words = Cut(item.Txt);
                    wordList.AddRange(words.Select(str => new Tbl_like_word {  Rid = item.Id, Word = str }));
             }
       }
       "處理完畢,開始入庫,".Dump("Log");
       this.BulkInsert(wordList);
       SaveChanges();
       "入庫完成".Dump("Log");
}
// Define other methods, classes and namespaces here
public static List<String> Cut(String str)
{
       var list = new List<String>();
       var buffer = new Char[2];
       for (int i = 0; i < str.Length - 1; i++)
       {
             buffer[0] = str[i];
             buffer[1] = str[i + 1];
             list.Add(new String(buffer));
       }
       return list;
}
以上 LINQPad 腳本使用 Entity Framework Core 連接到了資料庫,并參考了 NuGet 包“EFCore.BulkExtensions”來做資料批量插入,

之后,就可以把查詢安排上,先查詢倒排索引,然后關聯到主表:

SELECT TOP 10 * FROM tbl_like WHERE id IN (
SELECT rid FROM tbl_like_word WHERE word IN ('悟空'))

查詢速度很快,僅需十幾毫秒:

因為我們將所有的陳述句分成了二字符詞組,所以當需要對單個字符模糊查詢時,直接使用 LIKE 是一個更加經濟的方案,如果需要查詢的字符多于兩個時,就需要對查詢詞進行分詞,如需查詢“東土大唐”一詞,構造出的查詢陳述句可能會是這樣:

SELECT TOP 10*FROM tbl_like WHERE id IN (
SELECT rid FROM tbl_like_word WHERE word IN ('東土','土大','大唐'))

但是,該查詢并不符合我們的預期,因為其將只包含“土大”的陳述句也篩選了出來:

我們可以采取一些技巧來解決這個問題,比如先 GROUP 一下:

SELECT TOP
    10 *
FROM
    tbl_like
WHERE
    id IN (
    SELECT
        rid
    FROM
        tbl_like_word
    WHERE
        word IN ( '東土', '土大', '大唐' )
    GROUP BY
        rid
    HAVING
    COUNT ( DISTINCT ( word ) ) = 3
    )

在上述 SQL 陳述句中,我們對 rid 進行了分組,并篩選出了不重復的詞組數量是三個(即我們的查詢詞數量)的,于是,我們可以得到正確的結果:

由此可知:對于模糊查詢,我們可以通過分詞+倒排索引的方式優化查詢速度,

后記

雖然在講述時使用的是 SQL Server 資料庫,但是以上優化經驗對大部分關系型資料庫來說是通用的,比如 MySQL、Oracle 等,

如果你和筆者一樣在實際作業中使用 PostgreSQL 資料庫,那么在做倒排索引時可以直接使用陣列型別并配置 GiN 索引,以獲得更好的開發和使用體驗,需要注意的是,雖然 PostgreSQL 支持函式索引,但是如果對函式結果進行 LIKE 篩選時,索引并不會命中,

對于 SQLite 這種小型資料庫,模糊搜索并不能使用到索引,所以左前綴搜索和右后綴搜索的優化方式對其不生效,不過,一般我們不會使用 SQLite 去存盤大量的資料,盡管分詞+倒排索引的優化方式也可以在 SQLite 中實作,

轉載請註明出處,本文鏈接:https://www.uj5u.com/shujuku/1086.html

標籤:大數據

上一篇:滴滴資料倉庫指標體系建設實踐

下一篇:滴滴Ceph分布式存盤系統優化之鎖優化

標籤雲
其他(123570) Java(13369) Python(12729) C(7542) 區塊鏈(7372) JavaScript(7049) 基礎類(6313) AI(6244) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4120) MySQL(4012) Linux(3394) C語言(3288) C++語言(3117) Java相關(2746) 疑難問題(2699) 單片機工控(2479) Web開發(1951) 網絡通信(1793) 數據庫相關(1767) VB基礎類(1755) PHP(1727) 開發(1646) 系統維護與使用區(1617) .NETCore(1586) 基礎和管理(1579) JavaEE(1566) C++(1527) 專題技術討論區(1515) Windows客戶端使用(1484) HtmlCss(1466) ASP.NET(1428) Unity3D(1354) VCL組件開發及應用(1353) HTML(CSS)(1220) 其他技術討論專區(1200) WindowsServer(1192) .NET技术(1165) 交換及路由技術(1149) 語言基礎算法系統設計(1133) WindowsSDKAPI(1124) 界面(1088) JavaSE(1075) Qt(1074) VBA(1048) 新手樂園(1016) 其他開發語言(947) Go(907) HTML5(901) 新技術前沿(898) 硬件設計(872) 區塊鏈技術(860) 網絡編程(857) 非技術版(846) 一般軟件使用(839) 網絡協議與配置(835) Eclipse(790) Spark(750) 下載資源懸賞專區(743)

熱門瀏覽
  • GPU虛擬機創建時間深度優化

    **?桔妹導讀:**GPU虛擬機實體創建速度慢是公有云面臨的普遍問題,由于通常情況下創建虛擬機屬于低頻操作而未引起業界的重視,實際生產中還是存在對GPU實體創建時間有苛刻要求的業務場景。本文將介紹滴滴云在解決該問題時的思路、方法、并展示最終的優化成果。 從公有云服務商那里購買過虛擬主機的資深用戶,一 ......

    uj5u.com 2020-09-10 06:09:13 more
  • 可編程網卡芯片在滴滴云網路的應用實踐

    **?桔妹導讀:**隨著云規模不斷擴大以及業務層面對延遲、帶寬的要求越來越高,采用DPDK 加速網路報文處理的方式在橫向縱向擴展都出現了局限性。可編程芯片成為業界熱點。本文主要講述了可編程網卡芯片在滴滴云網路中的應用實踐,遇到的問題、帶來的收益以及開源社區貢獻。 #1. 資料中心面臨的問題 隨著滴滴 ......

    uj5u.com 2020-09-10 06:10:21 more
  • 滴滴資料通道服務演進之路

    **?桔妹導讀:**滴滴資料通道引擎承載著全公司的資料同步,為下游實時和離線場景提供了必不可少的源資料。隨著任務量的不斷增加,資料通道的整體架構也隨之發生改變。本文介紹了滴滴資料通道的發展歷程,遇到的問題以及今后的規劃。 #1. 背景 資料,對于任何一家互聯網公司來說都是非常重要的資產,公司的大資料 ......

    uj5u.com 2020-09-10 06:11:05 more
  • 滴滴AI Labs斬獲國際機器翻譯大賽中譯英方向世界第三

    **桔妹導讀:**深耕人工智能領域,致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎,這次獲獎的專案是什么呢?一起來看看詳細報道吧! 近日,由國際計算語言學協會ACL(The Association for Computational Linguistics)舉辦的世界最具影響力的機器 ......

    uj5u.com 2020-09-10 06:11:29 more
  • MPP (Massively Parallel Processing)大規模并行處理

    1、什么是mpp? MPP (Massively Parallel Processing),即大規模并行處理,在資料庫非共享集群中,每個節點都有獨立的磁盤存盤系統和記憶體系統,業務資料根據資料庫模型和應用特點劃分到各個節點上,每臺資料節點通過專用網路或者商業通用網路互相連接,彼此協同計算,作為整體提供 ......

    uj5u.com 2020-09-10 06:11:41 more
  • 滴滴資料倉庫指標體系建設實踐

    **桔妹導讀:**指標體系是什么?如何使用OSM模型和AARRR模型搭建指標體系?如何統一流程、規范化、工具化管理指標體系?本文會對建設的方法論結合滴滴資料指標體系建設實踐進行解答分析。 #1. 什么是指標體系 ##1.1 指標體系定義 指標體系是將零散單點的具有相互聯系的指標,系統化的組織起來,通 ......

    uj5u.com 2020-09-10 06:12:52 more
  • 單表千萬行資料庫 LIKE 搜索優化手記

    我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索,LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料,可以使用下面的 SQL 陳述句: SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......

    uj5u.com 2020-09-10 06:13:25 more
  • 滴滴Ceph分布式存盤系統優化之鎖優化

    **桔妹導讀:**Ceph是國際知名的開源分布式存盤系統,在工業界和學術界都有著重要的影響。Ceph的架構和演算法設計發表在國際系統領域頂級會議OSDI、SOSP、SC等上。Ceph社區得到Red Hat、SUSE、Intel等大公司的大力支持。Ceph是國際云計算領域應用最廣泛的開源分布式存盤系統, ......

    uj5u.com 2020-09-10 06:14:51 more
  • es~通過ElasticsearchTemplate進行聚合~嵌套聚合

    之前寫過《es~通過ElasticsearchTemplate進行聚合操作》的文章,這一次主要寫一個嵌套的聚合,例如先對sex集合,再對desc聚合,最后再對age求和,共三層嵌套。 Aggregations的部分特性類似于SQL語言中的group by,avg,sum等函式,Aggregation ......

    uj5u.com 2020-09-10 06:14:59 more
  • 爬蟲日志監控 -- Elastc Stack(ELK)部署

    傻瓜式部署,只需替換IP與用戶 導讀: 現ELK四大組件分別為:Elasticsearch(核心)、logstash(處理)、filebeat(采集)、kibana(可視化) 下載均在https://www.elastic.co/cn/downloads/下tar包,各組件版本最好一致,配合fdm會 ......

    uj5u.com 2020-09-10 06:15:05 more
最新发布
  • 如何宣告一個接受任何型別方法參考的變數Function<>?

    我試圖宣告一個變數,它接受任何型別的方法參考Function<AnyObject, AnyObject or Any Enum>。
    這個方法參考將在映射器中使用,我接受一些輸入并通過呼叫另一個物件的方法來映...

    uj5u.com 2021-10-16 15:27:18 more
  • 為什么方差會對flatmapto函式產生影響?

    我想了解這個函式: 我想了解這個函式。
    public inline fun <T, R, C : MutableCollection<in R>> Array<out T>。 flatMapTo(目的地。C, transform: (T) -> Iterable<R> ): C...

    uj5u.com 2021-10-16 15:26:04 more
  • 我如何使用一個`任務<string>`實體作為方法的任務<string?>引數?

    我如何使用一個Task<string>實體作為一個方法的Task<string?>引數?
    如果我使用啟用的nullability,并且我有兩個異步方法,比如...... 如果我使用啟用的nullability,并且我有兩個...

    uj5u.com 2021-10-16 15:26:00 more
  • Webpack沒有捆綁匯入和使用的功能

    我試圖在我的函式中包含和使用BootstrapCookieConsentSettingsnpm 包bootstrap-cookie-consent-settings中的函式,initConsentBanner但 webpack 總是將其丟棄并且不包含它,即...

    uj5u.com 2021-10-16 15:25:31 more
  • 如何用Swift泛型處理成功和錯誤的API回應?

    我正試圖撰寫一個簡單的函式來處理回傳 JWT 令牌的認證 POST 請求。
    我的LoopBack 4 API將令牌作為一個JSON資料包回傳,其格式如下:
    { "token"/span>: "my. jwt.token" }

    如...

    uj5u.com 2021-10-16 15:25:24 more
  • 從抽象的通用方法中回傳派生類的實體

    我想在這里做的事情有點難以描述。我目前的需求要求我有一個可以實作介面的列舉型別。雖然不是最漂亮的解決方案,但這是我想出的辦法; )。

    我的問題本質上是,是否有任何方法來...

    uj5u.com 2021-10-16 15:24:59 more
  • Java自參考的通用型別

    雖然這個問題的核心已經被問過很多次了,但是有一個問題還沒有被問到(或者說我還沒有找到)。
    在 Java 中,沒有辦法擁有一個參考型別本身的泛型。你可能會說 "如果你最終嘗試了,那...

    uj5u.com 2021-10-16 15:24:06 more
  • Kotlin中的泛型:如何實體化實作了介面的泛型類

    我的問題是,當一個通用類實作了一個介面時,我無法將其實體化。
    實體化代碼如下;
    class MainClass {
    fun mainMethod(){
    val access = EADBAccess<AppUserModel> (Ap...

    uj5u.com 2021-10-16 15:24:00 more
  • 在CSS檔案中使用:local有什么好處?

    我一直在學習在 React 中使用 CSS 并遇到了 CSS 模塊的想法,作為其中的一部分,我遇到了這篇文章https://blog.fearcat.in/a?ID=00550-af5ece9b-eb49-4e13 -8711-26e00c48c84e...

    uj5u.com 2021-10-16 15:22:38 more
  • POST后的PHP重定向

    我正在使用谷歌登錄,并且大部分時間都在那里。用戶成功登錄,我可以訪問我的 mysql 用戶表來查找用戶記錄。在完成用戶處理并設定 $_SESSION 變數后,我想從 POST 頁面重定向回我...

    uj5u.com 2021-10-16 13:40:46 more