MySQL全文索引 FULLTEXT索引和like的區別

1.概要 InnoDB引擎對FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。對於FULLTEXT索引的內容可以使用MATCH()…AGAINST語法進行查詢。為了在InnoDB驅動的表中使用FULLTEXT索引MySQL5.6引入了一些新 ...

1.概要 InnoDB引擎對FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。對於FULLTEXT索引的內容可以使用MATCH()…AGAINST語法進行查詢。為了在InnoDB驅動的表中使用FULLTEXT索引MySQL5.6引入了一些新的配置選項和INFORMATION_SCHEMA表。比如，為了監視一個FULLTEXT索引中文本處理過程的某一方面可以查詢INNODB_FT_CONFIG，INNODB_FT_INDEX_TABLE，INNODB_FT_INDEX_CACHE,INNODB_FT_DEFAULT_STOPWORD，INNODB_FT_DELETED和INNODB_FT_BEING_DELETED這些表。可以通過innodb_ft_num_word_optimize和innodb_optimize_fulltext_only選項控制OPTIMIZETABLE命令對InnoDB FULLTEXT索引的更新。 2.相關庫表 INFORMATION_SCHEMA庫中與InnoDB全文索引相關的表如下：

INNODB_SYS_INDEXES

INNODB_SYS_TABLES

INNODB_FT_CONFIG

INNODB_FT_INDEX_TABLE

INNODB_FT_INDEX_CACHE

INNODB_FT_DEFAULT_STOPWORD

INNODB_FT_DELETED

INNODB_FT_BEING_DELETED

? INNODB_SYS_INDEXES:提供了InnoDB索引的狀態信息。 ? INNODB_SYS_TABLES：提供了InnoDB表的狀態信息。 ? INNODB_FT_CONFIG：顯示一個InnoDB表的FULLTEXT索引及其相關處理的元數據。 ? INNODB_FT_INDEX_TABLE：轉化後的索引信息用於處理基於InnoDB表FULLTEXT索引的文本搜索。一般用於調試診斷目的。使用該表前需先配置innodb_ft_aux_table配置選項,將其指定為想要查看的含FULLTEXT索引的InnoDB表，選項值的格式為database_name/table_name。配置了該選項後INNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表將被填充與innodb_ft_aux_table配置選項指定的表關聯的搜索索引相關信息。 ? INNODB_FT_INDEX_CACHE：向含FULLTEXT索引的InnoDB表插入數據後新插入數據轉後的索引信息。表結構與INNODB_FT_INDEX_TABLE一致。為含FULLTEXT索引的InnoDB表執行DML操作期間重組索引開銷很大，因此將新插入的被索引的詞單獨存儲於該表中，當且僅當為InnoDB表執行OPTIMIZE TABLE語句後才將新的轉換後的索引信息與原有的主索引信息合併。使用該表前需先配置innodb_ft_aux_table配置選項。 ? INNODB_FT_DEFAULT_STOPWORD：在InnoDB表上創建FULLTEXT索引所使用的預設停止字表。 ? INNODB_FT_DELETED：記錄了從InnoDB表FULLTEXT索引中刪除的行。為了避免為InnoDB的FULLTEXT索引執行DML操作期間重組索引的高開銷，新刪除的詞的信息單獨存儲於此表。當且僅當為此InnoDB表執行了OPTIMIZE TABLE操作後才會從主搜索索引中移除已刪除的詞信息。使用該表前需先配置innodb_ft_aux_table選項。 ? INNODB_FT_BEING_DELETED：為含FULLTEXT索引的InnoDB表執行OPTIMIZE TABLE操作時會根據INNODB_FT_DELETED表中記錄的文檔ID從InnoDB表的FULLTEXT索引中刪除相應的索引信息。而INNOFB_FT_BEING_DELETED表用於記錄正在被刪除的信息，用於監控和調試目的。 3.相關配置選項

Name	Cmd- Line	Option file	System Var	Status Var	Scope	Dynamic
innodb_ft_aux_table	Yes	Yes	Yes		Global	Yes
innodb_ft_cache_size	Yes	Yes	Yes		Global	No
innodb_ft_enable_diag_print	Yes	Yes	Yes		Global	Yes
innodb_ft_enable_stopword	Yes	Yes	Yes		Global	Yes
innodb_ft_max_token_size	Yes	Yes	Yes		Global	No
innodb_ft_min_token_size	Yes	Yes	Yes		Global	No
innodb_ft_num_word_optimize	Yes	Yes	Yes		Global	Yes
innodb_ft_server_stopword_table	Yes	Yes	Yes		Global	Yes
innodb_ft_sort_pll_degree	Yes	Yes	Yes		Global	No
innodb_ft_user_stopword_table	Yes	Yes	Yes		Both	Yes
innodb_optimize_fulltext_only	Yes	Yes	Yes		Global	Yes

? innodb_ft_aux_table:指定包含FULLTEXT索引的InnoDB表的的名稱。該變數在運行時設置用於診斷目的。設置該值後INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG,INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表將被填充與innodb_ft_aux_table指定的表關聯的搜索索引相關信息。 ? innodb_ft_cache_size:當創建一個InnoDB FULLTEXT索引時在記憶體中存儲已解析文檔的緩存大小。 ? innodb_ft_enable_diag_print:是否開啟額外的全文搜索診斷輸出。 ? innodb_ft_enable_stopword:是否開啟停止字。InnoDB FUllTEXT索引被創建時為其指定一個關聯的停止字集。（若設置了innodb_ft_user_stopword_table則停止字由該選項指定的表獲取，若沒有設置innodb_ft_user_stopword_table而設置了innodb_ft_server_stopword_table則停止字由該選項指定的表獲取，否則使用內置的停止字。） ? innodb_ft_max_token_size:存儲在InnoDB的FULLTEXT索引中的最大詞長。設置這樣一個限制後可通過忽略過長的關鍵字等有效降低索引大小從而加速查詢。 ? innodb_ft_min_token_size:存儲在InnoDB的FULLTEXT索引中的最小詞長。增加該值後會忽略掉一些通用的沒有顯著意義的辭彙從而降低索引大小繼而加速查詢。 ? innodb_ft_num_word_optimize:為InnoDB FULLTEXT索引執行OPTIMIZE操作每次所處理的詞數。因為在含有全文搜索索引的表中執行批量的插入或更新操作需要大量的索引維護操作來合併所有的變化。因此，一般會運行一系列OPTIMIZE TABLE語句，每次從上一次的位置開始，處理指定數目的詞，知道搜索索引被完全更新。 ? innodb_ft_server_stopword_table:含有停止字的表，在創建InnoDB FULLTEXT索引時或忽略表中的停止字。停止字表需為InnoDB表，且在指定前應當已存在。 ? innodb_ft_sort_pll_degree:為較大的表構建搜索索引時用於索引和記號化文本的並行線程數。 ? innodb_ft_user_stopword_table:含有停止字的表，在創建InnoDB FULLTEXT索引時或忽略表中的停止字。停止字表需為InnoDB表，且在指定前應當已存在。 ? innodb_optimize_fulltext_only:改變OPTIMIZE TABLE語句對InnoDB表操作的方式。對含FULLTEXT 索引的InnoDB表進行維護操作期間，一般臨時的開啟該選項。預設情況下，OPTIMIZE TABLE語句會重組表的聚集索引中的數據。若開啟了該選項則該語句會跳過表數據的重組，而是只處理FULLTEXT索引中新插入的、刪除的、更新的標記數據。（在對作為FULLTEXT索引的一部分的InnoDB表列進行了大量的插入、更新或刪除操作後，先將innodb_optimize_fulltext_only設置為on以改變OPTIMIZE TABLE的預設行為，然後設置innodb_ft_num_word_optimize為合適的值以將索引維護時間控制在一個合理的可接受範圍內，最後執行一系列的OPTIMIZE語句知道搜索索引被完全更新。） 4.全文搜索功能全文搜索的語法：MATCH(col1,col2,…) AGAINST (expr[search_modifier])。其中MATCH中的內容為已建立FULLTEXT索引並要從中查找數據的列，AGAINST中的expr為要查找的文本內容，search_modifier為可選搜索類型。search_modifier的可能取值有：IN NATURAL LANGUAGEMODE、IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION、IN BOOLEAN MODE、WITH QUERY EXPANSION。search_modifier的每個取值代表一種類型的全文搜索，分別為自然語言全文搜索、帶查詢擴展的自然語言全文搜索、布爾全文搜索、查詢擴展全文搜索（預設使用IN NATURAL LANGUAGE MODE）。 MySQL中全文索引的關鍵字為FULLTEXT，目前可對MyISAM表和InnoDB表的CHAR、VARCHAR、TEXT類型的列創建全文索引。全文索引同其他索引一樣，可在創建表是由CREATE TABLE語句創建也可以在表創建之後用ALTER TABLE或者CREATE INDEX命令創建(對於要導入大量數據的表先導入數據再創建FULLTEXT索引比先創建索引後導入數據會更快)。 4.1自然語言全文搜索自然語言全文搜索是MySQL全文搜索的預設搜索方式，實現從一個文本集合中搜索給定的字元串。這裡，文本集合指的是指由FULLTEXT索引的一個或者多個列。建表，並給title，body欄位加FULLTEXT索引 CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) ENGINE=InnoDB; 導入數據 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 ...'); 例1： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); 可以看到，語句查找到了包含指定內容的行。實際上，返回的行是按與所查找內容的相關度由高到低的順序排列的。這個相關度的值由WHERE語句中的MATCH (…) AGAINST (…)計算所得，是一個非負浮點數。該值越大表明相應的行與所查找的內容越相關，0值表明不相關。該值基於行中的單詞數、行中不重覆的單詞數、文本集合中總單詞數以及含特定單詞的行數計算得出。例2：由上例可知MATCH (…) AGAINST (…)實際上會計算一個相關值，可通過下例來驗證。 SELECT id, MATCH (title,body) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score FROM articles; 可以看到，所得結果的第二列即為改行與查找內容的相關度。上例1中所得結果的順序就是按此相關度排列的。例3：若想既看到查找到的結果又需要瞭解具體的相關度，可用下述方法達成。 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);

可以看到，通過在查找部分和條件部分分別使用相同的MATCH(…) AGAINST(…)可以同時獲取兩方面的內容（不會增加額外開銷，優化器知道兩個MATCH(…) AGAINST(..)是相同的，只會執行一次該語句）註意事項預設情況下全文搜索大小寫不敏感，如上例1，查找的內容為‘database’但含有‘DataBase’的行也會返回。可以通過為FULLTEXT索引列所使用的字元集指定一個特定的校對集來改變這種行為。考慮下述兩個SELECT語句： 1. SELECTCOUNT(*) FROM articles WHEREMATCH (title,body) AGAINST('database' IN NATURAL LANGUAGE MODE); 2. SELECTCOUNT(IF(MATCH (title,body) AGAINST('database' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count FROMarticles; 這兩條查詢語句均可返回匹配的行數。但第一條語句可以利用基於WHERE從句的索引查找，因此在匹配的行數較少時速度較第二句更快。第二句執行了全表掃描，因此在匹配的行數較多時較第一句更快。 MATCH()函數中的列必須與FULLTEXT索引中的列相同。如MATCH(title,body)與FULLTEXT(title,body)。若要單獨搜索某列，如body列，則需另外單獨為該列建全文索引FULLTEXT(body)，然後用MATCH(body)搜索。對於InnoDB表MATCH()中的列僅能來自於同一個表，因為索引不能快多張表（MyISAM表的的布爾搜索因為可以不使用索引所以可以跨多張表中的列，但速度很慢）。全文搜索不僅可以搜索類似例1中‘database’這樣的單個的單詞，還可以搜索句子（這才是其被稱為‘全文搜索‘的關鍵），如例3。全文搜索把任何數字、字母、下劃線序列看作是單詞，還可以包含“’”如aaa’bbb備解析為一個單詞，但aaa’’bbb備解析為兩個單詞，FULLTEXT解析器自動移除首尾的“’”，如’aaa’bbb’被解析為aaa’bbb。FULLTEXT解析器用“ ”（空格）、“,”（逗號）“.”（點號）作為預設的單詞分隔符，因此對於不使用這些分隔符的語言如漢語來說FULLTEXT解析器不能正確的識別單詞，對於這種情況需做額外處理。全文搜索中一些單詞會被忽略。首先是過短的單詞，InnoDB全文搜索中預設為3個字元，MyISAM預設4個字元，可通過在創建FULLTEXT索引前改變配置參數來改變預設行為，對於InnoDB該參數為：innodb_ft_min_token_size，對於MyISAM為ft_min_word_len；另外stopword列表中的單詞會被忽略。stopword列表包含諸如“the”、“or”、“and”等常用單詞，這些詞通常被認為沒有什麼語義價值。MySQL由內建的停止字列表，但是可以所使用自定義的停止字列表來覆蓋預設列表。對於InnoDB控制停止字的配置參數為innodb_ft_enable_stopword，innodb_ft_server_stopword_table， innodb_ft_user_stopword_table對於MyISAM參數為ft_stopword_file。文本集合和查詢語句中的單詞的權重由該單詞在集合或語句中的重要性確定。單詞在越多的行中出現則該單詞的權重越低，因為這表明其在文本集合中的語義價值較小。反之權重越高。例1中提到的相關度計算也與此值有關。 4.2布爾全文搜索如果在AAGAINST()函數中指定了INBOOLEN MODE模式，則MySQL會執行布爾全文搜索。在該搜索模式下，待搜索單詞前或後的一些特定字元會有特殊的含義。例1： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL-YourSQL' IN BOOLEAN MODE);

該查詢語句中“MySQL”前的“+”表明結果中必須包含“MySQL”而“YourSQL”前的“-”表明所得結果中不能含有“YourSQL”。除了“+”和“-”外還有其他一些特定的字元。如空字元表明後跟的單詞是可選的，但出現的話會增加該行的相關性；“@distance”用於指定兩個或多個單詞相互之間的距離（以單詞度量）需在指定的範圍內；“>”用於增加後跟單詞對其所在行的相關性的貢獻“<”用於降低該貢獻；“（）”用於將單詞分組為子表達式且可以嵌套；“~”是後跟單詞對其所在行的相關性的貢獻值為負；“*”為普通的通配符，若為單詞指定了通配符，那麼即使該單詞過短或者出現在了停止字列表中它也不會被移除；“””，括在雙引號中的短語指明行必須在字面上包含指定的短語，全文搜索將短語分割為詞後在FULLTEXT索引中搜索。非字字元無需完全匹配，如”test phrase”可以匹配含”test phrase”和”test phrase”的行，但匹配含”phrase test”的行。例2： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL YourSQL' IN BOOLEAN MODE);

找到包含MySQL或者YourSQL的行例3： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL+YourSQL' IN BOOLEAN MODE);

找到包含同時MySQL和YourSQL的行例4： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE);

找到必須包含MySQl的行，YourSQL可有可無，但有YourSQL會增加相關性。例5： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL ~YourSQL' INBOOLEAN MODE); 找到包含必須包含MySQL的行，YourSQL可有可無，若出現了YourSQL則會降低其所在行的相關性。例6： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL +(>Security <Optimizing)' IN BOOLEANMODE); 找到必須同時包含MySQL以及Security或Optimizing的行Security會增加所在行的相關性，而Optimizing會降低所在行的相關性。例7： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('da*' IN BOOLEAN MODE); 找到包含da*的行。如包含DataBase、database等。例8： SELECT * FROM articles WHERE MATCH (title,body) AGAINST('"MySQL,Tutorial"' IN BOOLEAN MODE); 找到包含“MySQL Tutorial”短語的行。布爾全文搜索的一些特點 ? MyISAM全文搜索會忽略至少在一半以上數據行中出現的單詞（也即所謂的50%閾值），InnoDB無此限制。而在布爾全文搜索中MyISAM的50%閾值不生效。 ? 停止字列表也適用於布爾全文搜索。 ? 最小和最大詞長全文搜索參數也適用於布爾全文搜索 ? MyISAM中的布爾搜索在FULLTEXT索引不存在的時候仍可工作，但速度很慢。而InnoDB表的各類全文搜索必須有FULLTEXT索引，否則會出現找不到與指定列相匹配的FULLTEXT索引的錯誤 ? InnoDB中的全文搜索不支持在單一搜索單詞前使用多個操作符如“++MySQL”。MyISAM中全文搜索可以處理這種情況，但是會忽略除了緊鄰單詞之外的其他操作符。 4.3查詢擴展全文搜索某些時候我們通過全文搜索來查找包含某方面內容的行，比如我們搜索“database”，實際上我們期望返回結果不僅僅是僅包含“database”單詞的行，一些包含“MySQL”、“SQLServer”、“Oracle”、“DB2”、“RDBMS”等的行也期望被返回。這個時候查詢擴展全文搜索就能大顯身手。通過在AGAINST()函數中指定WITHQUERY EXPANSION 或者IN NATURAL MODE WITH QUERY EXPANSION可以開啟查詢擴展全文搜索模式。其工作原理是執行兩次搜索，第一次用給定的短語搜索，第二次使用給定的短語結合第一次搜索返回結果中相關性非常高的一些行進行搜索。例1： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE); 使用自然語言搜索返回了包含“database”的行。例2： SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION); 使用查詢擴展全文搜索，不進返回了包含“database”的行，也返回了與例1中返回的行的內容相關的行。註意事項因為查詢擴展會返回一些不相關的內容，因此會顯著的引入雜訊。索引僅當要查詢的短語較短時才在考慮使用查詢擴展全文搜索。 4.4全文搜索的停止字上文已經簡單介紹過了停止字列表，這裡做詳細介紹。停止字列表用MySQL Server所使用的字元集和校對集（分別由character_set_server和collation_server兩個參數控制）載入並執行搜索。若用於全文索引和搜索的停止字文件或者停止字表使用了與MySQL Server不同的字元集和校對集會則導致查找停止字時錯誤的命中或未命中。停止字查找的大小寫敏感性也依賴於MySQL Server所使用的校對集，例如校對集為latin1_swedish_ci則查找是大小寫不敏感的，若校對集為latin1_geberal_cs或者latin1_bin則查找是大小寫敏感的。 InnoDB預設的停止字列表相對較短（因為技術上的或者文學等方面的文檔常使用較短的詞作為關鍵字或者有其他顯著意義）。InnoDB預設的停止字列表存儲在information_schema.innodb_ft_default_stopword表中。當然也可以通過自定義與innodb_ft_default_stopword表結構相同的表，填充期望的停止字，然後通過innodb_ft_server_stopword_table選項指定自定義的停止字表db_name/table_name，來改變預設的行為。另外還可以為innodb_ft_user_stopword_table選項指定含停止字的表，若同時指定了innodb_ft_default_stopword和innodb_ft_user_stopword_table則將使用後者指定的停止字表。上述操作改變所使用停止字表的操作需在創建全文索引前完成。且在指定所使用的停止字表時，表必須已經存在。對於MyISAM可通過 ft_stopword_file選項指定所使用的停止字列表。MyISAM預設的停止字列表可在MySQL源碼的 storage/myisam/ft_static.c文件中找到。 4.5全文搜索的限制 ? 目前只有InnoDB和MyISAM引擎支持全文搜索。其中InnodB表對FULLTEXT索引的支持從MySQL5.6.4開始。 ? 分區表不支持全文搜索。 ? 全文索引適用於多數多位元組字元集。例外情況是：對於Unicode，utf8字元集可用但ucs2字元集不適用。儘管不能在ucs2列建立FULLTEXT索引，但可以在MyISAM表IN BOOLEAN MODE模式的搜索中搜索沒有建立FULLTEXT索引的列。utf8的特性適用於utf8mb4，ucs2的特性適用於utf16、utf16e和utf32。 ? 表意型語言如漢語、日語沒有諸如空格之類的單詞定界符。因此FULLTEXT解析器不能確定此類語言中詞的起止。對於此種情況要特殊處理（比如將中文轉換成一種單位元組類似英文習慣的存儲方式）。 ? 允許在同一表中使用多種字元集，但FULLTEXT索引中的列必須使用同一字元集和校對集。 ? MATCH()函數中的列必須與FULLTEXT索引中定義的列完全一致，除非是在MyISAM表中使用IN BOOLEAN MODE模式的全文搜索（可在沒有建立索引的列執行搜索，但速度很慢）。 ? AGAINST()函數中的參數需為在查詢評估期間保持不變的字元串常量。 ? FULLTEXT搜索的索引提示比non-FULLTEXT搜索的索引提示要多一些限定：對於自然語言模式的全文搜索，索引提示會被忽略而不給出任何提示，比如雖明確在查詢語句中給出了IGNORE INDEX(i)指明不使用i索引，但是該索引提示會被忽略掉，最終的查詢中仍會使用索引i；對於布爾模式的全文搜索，FOR ORDER BY和FOR GROUP BY的索引提示會被忽略，FOR JOIN和不帶FOR修飾符的索引提示不被忽略。 4.6全文搜索參數調整僅有少量的用戶可調參數用於調整MySQL的全文搜索能力。可以通過修改源碼來獲取更多對MySQL全文搜索行為的控制。但一般情況下不推薦這麼做，除非很清楚自己在做什麼，因為這些參數已經針對效率做過調整，修改預設的行為多數情況下反而會帶來性能下降。多數全文搜索相關的變數不能在Server運行的時候修改。需在Server啟動時指定這些參數，或者修改完參數之後重新啟動Server。另外，某些變數修改後需要重建FULLTEXT索引。控制最小、最大字長的配置選項對於InnoDB為：innodb_ft_min_token_size和innodb_ft_max_tok