mysql百萬級全文索引及match快速查詢

火鳥網路發表於2018-10-10
MySql索引

建立全文索引的表的儲存引擎型別必須為MyISAM

問題是match   against對中文模糊搜尋支援不是太好

新建一個utf8 MyISAM型別的表並建立一個全文索引  ：

  1. CREATE TABLE articles (
  2.     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  3.     title VARCHAR(200),
  4.     body TEXT,
  5.     FULLTEXT (title,body)
  6. ) ENGINE=MyISAM  DEFAULT >

其中FULLTEXT(title, body) 給title和body這兩列建立全文索引，之後檢索的時候注意必須同時指定這兩列。

給這個表新增點測試資料

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

3. 全文檢索測試

  1. SELECT * FROM articles   WHERE MATCH (title,body) AGAINST (`database`); 

注意 MATCH (title,body) 裡面的值必須是前面建立全文索引的兩個欄位不能少。

 

mysql 預設支援全文檢索的字元長度是4，可以用SHOW VARIABLES LIKE `ft_min_word_len` 來檢視指定的字元長度，也可以在mysql配置檔案my.ini 更改最小字元長度，方法是在my.ini 增加一行 比如：ft_min_word_len = 2，改完後重啟mysql即可。  

另外，MySQL還會計算一個詞的權值，以決定是否出現在結果集中，具體如下：

mysql在集和查詢中的對每個合適的詞都會先計算它們的權重，一個出現在多個文件中的詞將有較低的權重(可能甚至有一個零權重)，因為在這個特定的集中，它有較低的語義值。否則，如果詞是較少的，它將得到一個較高的權重，mysql預設的閥值是50%，上面‘you’在每個文件都出現，因此是100%，只有低於50%的才會出現在結果集中。

全文檢索語法

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST (`+apple -banana` IN BOOLEAN MODE);   + 表示AND，即必須包含。- 表示NOT，即不包含。

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST (`apple banana` IN BOOLEAN MODE);   apple和banana之間是空格，空格表示OR，即至少包含apple、banana中的一個。

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST (`+apple banana` IN BOOLEAN MODE);   必須包含apple，但是如果同時也包含banana則會獲得更高的權重。

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST (`+apple ~banana` IN BOOLEAN MODE);   ~ 是我們熟悉的異或運算子。返回的記錄必須包含apple，但是如果同時也包含banana會降低權重。但是它沒有 +apple -banana 嚴格，因為後者如果包含banana壓根就不返回。

SELECT * FROM articles WHERE MATCH (title,body)     AGAINST (`+apple +(>banana <orange)` IN BOOLEAN MODE);   返回同時包含apple和banana或者同時包含apple和orange的記錄。但是同時包含apple和banana的記錄的權重高於同時包含apple和orange的記錄

最後如果要模糊搜尋只要在在搜尋的詞後面加上*號就可以，如

“SELECT * FROM table_name WHERE MATCH(column) AGAINST(`search*` IN BOOLEAN MODE)”

經測試，非常快速！

相關文章