#頭條創作挑戰賽#

我們都知道 InnoDB 在模糊查詢資料時使用 "%xx" 會導緻索引失效，但有時需求就是如此，類似這樣的需求還有很多，例如，搜尋引擎需要根基使用者資料的關鍵字進行全文查找，電子商務網站需要根據使用者的查詢條件，在可能需要在商品的詳細介紹中進行查找，這些都不是B+樹索引能很好完成的工作。

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

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

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

反向索引

全文檢索通常使用反向索引（inverted index）來實作，反向索引同 B+Tree 一樣，也是一種索引結構。它在輔助表中存儲了單詞與單詞自身在一個或多個文檔中所在位置之間的映射，這通常利用關聯數組實作，擁有兩種表現形式：

inverted file index：{單詞，單詞所在文檔的id}
full inverted index：{單詞，（單詞所在文檔的id，再具體文檔中的位置）}

對于 inverted file index 的關聯數組

上圖為 inverted file index 關聯數組，可以看到其中單詞"code"存在于文檔1,4中，這樣存儲再進行全文查詢就簡單了，可以直接根據 Documents 得到包含查詢關鍵字的文檔；而 full inverted index 存儲的是對，即（DocumentId,Position），是以其存儲的反向索引如下圖，如關鍵字"code"存在于文檔1的第6個單詞和文檔4的第8個單詞。相比之下，full inverted index 占用了更多的空間，但是能更好的定位資料，并擴充一些其他搜尋特性。

全文檢索

建立全文索引

1、建立表時建立全文索引文法如下：

CREATE TABLE table_name ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, author VARCHAR(200), 
title VARCHAR(200), content TEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;

輸入查詢語句：

SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE 'test/%';

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

2、在已建立的表上建立全文索引文法如下：

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()`[1]文法進行，其中，MATCH() 采用逗号分隔的清單，命名要搜尋的列。AGAINST()接收一個要搜尋的字元串，以及一個要執行的搜尋類型的可選修飾符。全文檢索分為三種類型：自然語言搜尋、布爾搜尋、查詢擴充搜尋，下面将對各種查詢模式進行介紹。

Natural Language

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

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

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

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

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

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

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

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

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

word 是否在文檔中出現
word 在文檔中出現的次數
word 在索引列中的數量
多少個文檔包含該 word

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

查詢的 word 在 stopword 列中，忽略該字元串的查詢
查詢的 word 的字元長度是否在區間 [innodb_ft_min_token_size,innodb_ft_max_token_size] 内

如果詞在 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

布爾搜尋使用特殊查詢語言的規則來解釋搜尋字元串，該字元串包含要搜尋的詞，它還可以包含指定要求的運算符，例如比對行中必須存在或不存在某個詞，或者它的權重應高于或低于通常情況。例如，下面的語句要求查詢有字元串"Pease"但沒有"hot"的文檔，其中+和-分别表示單詞必須存在，或者一定不存在。

select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);

Boolean 全文檢索支援的類型包括：

+：表示該 word 必須存在
-：表示該 word 必須不存在
(no operator)表示該 word 是可選的，但是如果出現，其相關性會更高
@distance表示查詢的多個單詞之間的距離是否在 distance 之内，distance 的機關是位元組，這種全文檢索的查詢也稱為 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)語句表示字元串 Pease 和 hot 之間的距離需在30位元組内
：表示出現該單詞時增加相關性
<：表示出現該單詞時降低相關性
~：表示允許出現該單詞，但出現時相關性為負

：表示以該單詞開頭的單詞，如 lik*,表示可以是 lik，like，likes

" ：表示短語

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

demo1：+ -

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

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

demo2：no operator

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

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

demo3：@

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

上述語句，代表 "DB2" ，"IBM"兩個詞之間的距離在3位元組之内

demo4：> <

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

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

demo5: ~

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

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

demo6：*

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

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

demo7："

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

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

Query Expansion

查詢擴充搜尋是對自然語言搜尋的修改，這種查詢通常在查詢的關鍵詞太短，使用者需要 implied knowledge（隐含知識）時進行，例如，對于單詞 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' WITH QUERY expansion);

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

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

删除全文索引

1、直接删除全文索引文法如下：

DROP INDEX full_idx_name ON db_name.table_name;

2、使用 alter table 删除全文索引文法如下：

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

小結

本文從理論與實踐結合的角度對 fulltext index 做了介紹

連結：juejin.cn/post/6989871497040887845

求求你MySQL模糊查詢别再用like+% 了

全文檢索

建立全文索引

使用全文索引

Natural Language

Boolean

Query Expansion

删除全文索引

小結

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案