MySQL 模糊查詢再也不用like+%了

大家好，我是鵬磊。

我們都知道 InnoDB 在模糊查詢數據時使用 "%xx" 會導致索引失效，但有時需求就是如此，類似這樣的需求還有很多。

例如，搜尋引擎需要根基使用者數據的關鍵字進行全文尋找，電子商務網站需要根據使用者的查詢條件，在可能需要在商品的詳細介紹中進行尋找，這些都不是 B+ 樹索引能很好完成的工作。

透過數值比較，範圍過濾等就可以完成絕大多數我們需要的查詢了。但是，如果希望透過關鍵字的匹配來進行查詢過濾，那麽就需要基於相似度的查詢，而不是原來的精確數值比較，全文索引就是為這種場景設計的。

全文索引（Full-Text Search）是將儲存於資料庫中的整本書或整篇文章中的任意資訊尋找出來的技術。它可以根據需要獲得全文中有關章、節、段、句、詞等資訊，也可以進行各種統計和分析。

如果你近期準備面試跳槽，建議在ddkk.com線上刷題，涵蓋 一萬+ 道 Java 面試題，幾乎覆蓋了所有主流技術面試題，還有市面上最全的技術五百套，精品系列教程，免費提供。

在早期的 MySQL 中，InnoDB 並不支持全文檢索技術，從 MySQL 5.6 開始，InnoDB 開始支持全文檢索

倒排索引

全文檢索通常使用倒排索引（inverted index）來實作，倒排索引同 B+Tree 一樣，也是一種索引結構。它在輔助表中儲存了單詞與單詞自身在一個或多個文件中所在位置之間的對映。

這通常利用關聯陣列實作，擁有兩種表現形式：

上圖為 inverted file index 關聯陣列，可以看到其中單詞"code"存在於文件1,4中，這樣儲存再進行全文查詢就簡單了，可以直接根據 Documents 得到包含查詢關鍵字的文件。

如果你近期準備面試跳槽，建議在ddkk.com線上刷題，涵蓋 一萬+ 道 Java 面試題，幾乎覆蓋了所有主流技術面試題，還有市面上最全的技術五百套，精品系列教程，免費提供。

而 full inverted index 儲存的是對，即（DocumentId，Position），因此其儲存的倒排索引如下圖，如關鍵字"code"存在於文件 1 的第 6 個單詞和文件 4 的第 8 個單詞。

相比之下，full inverted index 占用了更多的空間，但是能更好的定位數據，並擴充一些其他搜尋特性。

全文檢索

| 建立全文索引

①建立表時建立全文索引語法如下：

CREATETABLE table_name ( idINTUNSIGNED AUTO_INCREMENT NOTNULL PRIMARY KEY, author VARCHAR(200), 
title VARCHAR(200), contentTEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;

輸入查詢語句：

SELECT table_id, name, spacefrom INFORMATION_SCHEMA.INNODB_TABLES
WHEREnameLIKE'test/%';

上述六個索引表構成倒排索引，稱為輔助索引表。當傳入的文件被標記化時，單個詞與位置資訊和關聯的 DOC_ID，根據單詞的第一個字元的字元集排序權重，在六個索引表中對單詞進行完全排序和分區。

②在已建立的表上建立全文索引語法如下：

CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);

| 使用全文索引

MySQL 資料庫支持全文檢索的查詢，全文索引只能在 InnoDB 或 MyISAM 的表上使用，並且只能用於建立 char，varchar，text 型別的列。

其語法如下：

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}

全文搜尋使用 MATCH() AGAINST() 語法進行，其中，MATCH() 采用逗號分隔的列表，命名要搜尋的列。

AGAINST() 接收一個要搜尋的字串，以及一個要執行的搜尋型別的可選修飾詞。全文檢索分為三種型別：自然語言搜尋、布爾搜尋、查詢擴充套件搜尋，下面將對各種查詢模式進行介紹。

Natural Language

自然語言搜尋將搜尋字串解釋為自然人類語言中的短語，MATCH() 預設采用 Natural Language 模式，其表示查詢帶有指定關鍵字的文件。

接下來結合 demo 來更好的理解 Natural Language：

SELECT
count(*) AScount
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL' );

上述語句，查詢 title，body 列中包含 'MySQL' 關鍵字的行數量。上述語句還可以這樣寫：

SELECT
count(IF(MATCH ( title, body ) 
against ( 'MySQL' ), 1, NULL )) AScount
FROM
`fts_articles`;

上述兩種語句雖然得到的結果是一樣的，但從內部執行來看，第二句 SQL 的執行速度更快些，因為第一句 SQL（基於 where 索引查詢的方式）還需要進行相關性的排序統計，而第二種方式是不需要的。

還可以透過 SQL 語句查詢相關性：

SELECT
*,
MATCH ( title, body ) against ( 'MySQL' ) AS Relevance 
FROM
fts_articles;

相關性的計算依據以下四個條件：

對於 InnoDB 儲存引擎的全文檢索，還需要考慮以下的因素：

如果詞在 stopword 中，則不對該詞進行查詢，如對 'for' 這個詞進行查詢，結果如下所示：

SELECT
*,
MATCH ( title, body ) against ( 'for' ) AS Relevance 
FROM
fts_articles;

可以看到，'for'雖然在文件 2，4 中出現，但由於其是 stopword，故其相關性為 0。

參數 innodb_ft_min_token_size 和 innodb_ft_max_token_size 控制 InnoDB 引擎查詢字元的長度。

當長度小於 innodb_ft_min_token_size 或者長度大於 innodb_ft_max_token_size 時，會忽略該詞的搜尋。

在 InnoDB 引擎中，參數 innodb_ft_min_token_size 的預設值是 3，innodb_ft_max_token_size 的預設值是 84。

Boolean

布爾搜尋使用特殊查詢語言的規則來解釋搜尋字串，該字串包含要搜尋的詞，它還可以包含指定要求的運算子，例如匹配行中必須存在或不存在某個詞，或者它的權重應高於或低於通常情況。

如果你近期準備面試跳槽，建議在ddkk.com線上刷題，涵蓋 一萬+ 道 Java 面試題，幾乎覆蓋了所有主流技術面試題，還有市面上最全的技術五百套，精品系列教程，免費提供。

例如，下面的語句要求查詢有字串"Pease"但沒有"hot"的文件，其中+和-分別表示單詞必須存在，或者一定不存在。

select * from fts_test whereMATCH(content) AGAINST('+Pease -hot'INBOOLEANMODE);

Boolean 全文檢索支持的型別包括：

下面是一些 demo，看看 Boolean Mode 是如何使用的。

demo1：+ -

SELECT
* 
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL'INBOOLEANMODE );

上述語句，查詢的是包含 'MySQL' 但不包含 'YourSQL' 的資訊。

demo2：no operator

SELECT
* 
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL IBM'INBOOLEANMODE );

上述語句，查詢的 'MySQL IBM' 沒有 '+'，'-'的標識，代表 word 是可選的，如果出現，其相關性會更高。

demo3：@

SELECT
* 
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3'INBOOLEANMODE );


上述語句，代表 "DB2" ，"IBM"兩個詞之間的距離在 3 字節之內。

demo4：> <

SELECT
* 
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)'INBOOLEANMODE );

上述語句，查詢同時包含 'MySQL'，'database'，'DBMS' 的行資訊，但不包含'DBMS'的行的相關性高於包含'DBMS'的行。

demo5: ~

SELECT
* 
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL ~database'INBOOLEANMODE );

上述語句，查詢包含 'MySQL' 的行，但如果該行同時包含 'database'，則降低相關性。

demo6：*

SELECT
* 
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'My*'INBOOLEANMODE );

上述語句，查詢關鍵字中包含'My'的行資訊。

demo7："

SELECT
* 
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '"MySQL Security"'INBOOLEANMODE );

上述語句，查詢包含確切短語 'MySQL Security' 的行資訊。

Query Expansion

查詢擴充套件搜尋是對自然語言搜尋的修改，這種查詢通常在查詢的關鍵詞太短，使用者需要 implied knowledge（隱含知識）時進行。

如果你近期準備面試跳槽，建議在ddkk.com線上刷題，涵蓋 一萬+ 道 Java 面試題，幾乎覆蓋了所有主流技術面試題，還有市面上最全的技術五百套，精品系列教程，免費提供。

例如，對於單詞 database 的查詢，使用者可能希望查詢的不僅僅是包含 database 的文件，可能還指那些包含 MySQL、Oracle、RDBMS 的單詞，而這時可以使用 Query Expansion 模式來開啟全文檢索的 implied knowledge。

透過在查詢語句中添加 WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以開啟 blind query expansion（又稱為 automatic relevance feedback）。

該查詢分為兩個階段：

接著來看一個例子，看看 Query Expansion 是如何使用的。

-- 建立索引
create FULLTEXT INDEX title_body_index on fts_articles(title,body);

-- 使用 Natural Language 模式查詢
SELECT
* 
FROM
`fts_articles`
WHERE
MATCH(title,body) AGAINST('database');

使用 Query Expansion 前查詢結果如下：

-- 當使用 Query Expansion 模式查詢
SELECT
* 
FROM
`fts_articles`
WHERE
MATCH(title,body) AGAINST('database'WITHQUERY expansion);

使用 Query Expansion 後查詢結果如下：

由於 Query Expansion 的全文檢索可能帶來許多非相關性的查詢，因此在使用時，使用者可能需要非常謹慎。

| 刪除全文索引

①直接刪除全文索引語法如下：

DROPINDEX full_idx_name ON db_name.table_name;

②使用 alter table 刪除全文索引語法如下：

ALTERTABLE db_name.table_name DROPINDEX full_idx_name;

小結

本文從理論與實踐結合的角度對 fulltext index 做了介紹。InnoDB 的全文檢索在一些簡單的搜尋場景下還是比較實用的，可以替代 like+%，並且不需要額外依賴其他服務。復雜搜尋場景的話，我們還是需要使用 ES 這類搜尋引擎。

🔥 磊哥私藏精品 熱門推薦 🔥