乾貨！從零開始學習MySQL

關注我們吧，查看更多幹貨文章，視頻。回覆“數據”還有數據分析相關資料領取，每週更有免費直播課，有問題也可私信諮詢小編哦！

一、為什麼要用全文索引

我們在用一個東西前，得知道為什麼要用它，使用全文索引無非有以下原因

1. like查詢太慢、json字段查詢太慢（車太慢了）
2. 沒時間引入ElasticSearch、Solr或者Sphinx這樣的軟件，或者根本就不會用（無法將五菱宏光換成蘭博基尼，即使有蘭博基尼也不會開）
3. 加索引、聯合索引啥的都已經慢得不行了（限速80，車頂蓋都卸了也只能開到30）
4. 為了提升一下自己的逼格（人家問你有沒有開過法拉利，你說開過肯定更有氣質一點）

二、什麼是全文索引

簡單的說，全文索引就相當於大詞典中的目錄，通過查詢目錄可以快速定位到想看的內容。全文索引通過建立倒排索引來快速匹配文檔（僅在mysql5.6版本以上支持）全文索引將連續的字母、數字和下劃線當做一個單詞，分割單詞一般用空格/逗號/句號MySQL的全文索引支持以下3種查詢模式：

1. 自然語言模式（IN NATURAL LANGUAGE MODE）
   通過MATCH AGAINST 傳遞某個特定的字符串來進行檢索
2. 布爾模式（IN BOOLEAN MODE）
   支持操作符，例如+表示包含，-表示不包含
3. 擴展模式（WITH QUERY EXPANSION）
   相當於自然語言模式下的一個擴展，執行兩次檢索，第一次使用給定短語檢索，第二次是結合第一次相關性比較高的行進行檢索.

更多請看：官方文檔

下面教大家如何創建全文索引，並創建測試數據演示三種查詢模式的使用

三、如何創建全文索引

• 方式一：建表時指定

<code>CREATE TABLE light_weight_baby ( 
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    title VARCHAR(200), 
    content TEXT, 
    FULLTEXT(title, content) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; /<code>

• 方式二：ALTER添加

<code> ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column1,column2,...);/<code> 

• 方式三：CRATE INDEX添加

<code>CREATE FULLTEXT INDEX index_name ON table_name (column1,column2,...);/<code>

四、創建測試數據

創建一個數據庫用來演示這三種模式下的檢索

<code>CREATE DATABASE chenqionghe DEFAULT CHARSET utf8;/<code>

創建一個文章表並插入測試數據

<code>CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=InnoDB;/<code>

插入測試數據

<code>INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');/<code>

執行結果如下

五、查詢-使用自然語言模式

這是MySQL的默認查詢模式，簡單示例如下

<code>SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);/<code>

可以看到，不區分大小寫，title或body包含database的都返回了，另外，返回的結果將以相關性進行排序。

相關性：根據行中的字段、唯一單詞的數量、集合中單詞總數和包含特定單詞的行數計算。

下面通過兩種方式統計數量

<code># 第一種方式
 SELECT COUNT(*) FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);
# 第二種方式
SELECT
   COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
    AS count
    FROM articles;/<code>

第一種做了一些額外的工作（按相關性對結果進行排序），但也能使用索引進行查詢。第二種執行了全表掃描，如果搜索項出現在大多數行中，可能比索引查詢更快匹配少數行，第一種快，匹配大多數行，第二種快

下面演示如何檢索相關性，但不會進行排序（因為不包含WHERE和ORDER BY）

<code>SELECT id, MATCH (title,body)
     AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
    FROM articles;/<code>

下面的示例更復雜，返回倒序後的相關性值，分別在SELECT和WHERE語句中使用了MATCH，但是不會導致額外的開銷，因為mysql優化器注意到兩次MATCH是相同的，只會使用一次全文搜索

<code>SELECT id, body, MATCH (title,body) AGAINST
    ('Security implications of running MySQL as root'
    IN NATURAL LANGUAGE MODE) AS score
    FROM articles WHERE MATCH (title,body) AGAINST
    ('Security implications of running MySQL as root'
    IN NATURAL LANGUAGE MODE);/<code>

包含在("")中字符中的會被分解為單詞，然後在全文索引中進行搜索，簡單的說，就是進行OR查詢。

六、查詢-使用布爾模式（強大的語法）

使用布爾模式需要指定IN BOOLEAN MODE，不會自動根據相關性排序，一些字符具有特殊的含義，例如可以通過+或-表示一個單詞必須存在或不存在。下面的sql語句代表查詢必須 包含MySQL但不包含YourSQL

<code>SELECT * FROM articles WHERE MATCH (title,body)
    AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);/<code>

語法

• +
  必須出現
• -
  必須不出現。
  注意：這個操作符是用來排除其他操作符的結果，如果只指定這個，將什麼都不返回
• 無符號
  默認情況，代表或，自動分詞搜索。和沒有指定IN BOOLEAN MODE的結果一樣
• @distance
  用來測試兩個或兩個以上的單詞是否都在一個指定的距離內，在@距離前指定雙引號中的搜索詞，例如MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE
• >
  提高該條匹配數據的權重值
• <
  降低該條匹配數據的權重值
• ()
  相當於表達式分組，和我們數學中的表達式一個道理
• ~
  將其相關性由正轉負，表示擁有該字會降低相關性，例如+apple ~macintosh 先匹配apple，但如果同時包含macintosh，排名會靠後
• *
  通配符，只能在字符串後面使用
• "
  完全匹配，被雙引號包起來的單詞必須整個被匹配

示例

• apple banana
  包含apple或banana其中一個
• +apple +juice
  必須同時包含apple和juice
• +apple macintosh
  包含apple，但是如果同時包含macintosh會給更高的排序
• +apple -macintosh
  包信apple但是不包含macintosh
• +apple ~macintosh
  包含apple，如果同時包含macintosh降低權重
• +apple +(>turnover <strudel>1.包含apple和turnover，或，包含apple和strudel
  2.包含apple和turnover權重高於包含apple和strudel的記錄/<strudel>
• apple*
  包含apple單詞的行， “apple”, “apples”, “applesauce”, “applet”都會被匹配到
• "some words"
  完全匹配some words的行，例如 “some words of wisdom”能匹配但“some noise words”匹配不到

七、查詢-使用擴展模式

當搜索短語很短時非常有用，例如搜索database可能意味著MySQL、Oracle、DB2、RDBMS都要被匹配到，這就是這個模式能做的。添加WITH QUERY EXPANSION或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION啟用，它會執行兩次檢索，第一次使用給定短語檢索，第二次是結合第一次相關性比較高的行進行檢索。例如下面的例子

<code># 自然語言模式
SELECT * FROM articles
     WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);

# 擴展模式
SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' WITH QUERY EXPANSION);/<code>

可以看到第二條語句找到了包含MySQL的行，即使該行不包含database，但是因為在第一次的搜索中搜索引擎判斷MySQL和database的相關性比較高，所以在執第二次搜索的時候返回了。

八、注意事項

1. 只能在類型為CHAR、VARCHAR或者TEXT的字段上創建全文索引
2. MATCH (字段) AGAINST (關鍵詞)，必須和創建時的字段一起，例如MATCH (light,weight,baby)使用的字段名與全文索引muscle(light,weight,baby)定義的字段名一致。如果只對單個字段查詢，需要分別創建全文索引
3. 全文索引是以詞為基礎的，innodb_ft_min_token_size和innodb_ft_max_token_size用來設置單詞的最大和最小長度，不在這個長度區間的將忽略。
4. 在停用詞stopwords中的將被忽略
5. 如果要導入大量數據，先導入數據再建全文索引，比先建全文索引再導入數據的方式快很多。
6. 在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，MySQL 5.7.6後內置了ngram全文解析器，支持中文、日文、韓文分詞。

end.

閱讀更多 中國統計網 的文章

關鍵字: 乾貨 數據庫 學習