SQL反模式學習筆記17 全文搜索

• 2019 年 10 月 4 日
• 筆記

目標：全文搜索

使用SQL搜索關鍵字，同時保證快速和精確，依舊是相當地困難。

SQL的一個基本原理（以及SQL所繼承的關係原理）就是一列中的單個數據是原子性的。

反模式：模式匹配

使用Like 或者正則表達式。

　　缺點：（1）無法使用索引，進行全表遍歷，非常耗時，性能極低。

　　　　　（2）有時候會返回醫療之外的結果。select * from bugs where description like '%one%'，

返回結果可能是money、prone、lonely。

正則表達式可能會為單詞邊界提供一個模式來解決單詞的匹配問題。

如何識別反模式：當出現以下情況時，可能是反模式

　　1、如何在like表達式的2個通配符之間插入一個變量？

　　2、如何寫一個正則表達式來檢查一個字符串是否包含多個單詞、不包含一個特定的單詞，或者包含給定單詞的任意形式？

　　3、網站的搜索功能在增加了很多文檔進去之後慢的不可理喻。

合理使用反模式：

　　1、性能總是最重要的，如果一些查詢過程很少執行，就不必要花很多功夫去對它進行優化

　　2、使用模式匹配操作進行很複雜的查詢是很困難的，但是如果你為了一些簡單的需求設計這樣的模式匹配，它們能幫助你用

最少的工作量獲得正確的結果。

解決方案：使用工具

　　最好的方案就是使用特殊的搜索引擎技術，而不是SQL。

　　另一個方案是將結果保存起來從而減少重複的搜索開銷。

　　1、MySQL中的全文索引：可以再一個類型為Char、varchar或者Text的列上定義一個全文索引。然後使用Match函數來搜索。

　　2、Oracle中的文本索引：Context、Ctxcat、Ctxxpath、Ctxule。

　　3、SQL Server中的全文搜索：使用Contains()操作符來使用全文索引。使用之前需要通過複雜的步驟來配置。

　　4、PostgreSQL的文本搜索：提供一個複雜大可大量配置的方式來將文本轉換為可搜索的詞彙集合，並且讓這些文檔能夠進行

模式匹配搜索。

　　5、SQLite的全文搜索：使用SQLite的擴展組件來實現。

　　6、第三方搜索引擎：

　　　　（1）Sphinx Search：開源的搜索引擎，用於MySQL以及PostgreSQL來配套使用。

　　　　（2）Apache Lucene:是一個針對Java程序的成熟搜索引擎。

　　7、實現自己的搜索引擎：

使用反向索引方案：反向索引就是一個所有可能被搜索的單詞列表。

　　　　（1）定義一個KeyWords表來記錄所有用戶搜索的關鍵字，然後定義一個交叉表來建立多對多的關係。

　　　　（2）將每個關鍵字和匹配的內容添加到交叉表中。

　　　　　　　　當有新的搜索單詞，就使用like查詢結果，並將結果保存到交叉表裡，這樣下次就不必like了。

　　　　 當有新的文檔入庫，就需要用觸發器（或者定時）去填充交叉表。

結論：不必使用SQL來解決所有問題。