一文給你講清楚MySql全文索引實戰和原理_我想漲工資

theme: channing-cyan

1：什麼是全文索引

在當我們進行模糊查詢的時候，一般都是使用 like 關鍵字來實現，在資料量小的情況下效率確實可以。有的人會說了，資料量大的時候我加索引啊。確實可以加索引，而且 like name% 是可以使用到索引的，但是當你要 like %name% 這樣使用就會導致索引無效。在某些情況下就造成了侷限性。

關於索引失效的原理可以移步這裡：https://juejin.cn/post/7044793268780924935

在資料量大的情況下，全文索引的效率會比like高，在MySql5.6版本以前，只有MyISAM索引支援全文索引，在這之後Innodb也支援。

2：全文索引的建立和使用

```js //建立全文索引 CREATE FULLTEXT INDEX on tableName(欄位名)

ALTER TABLE tableName ADD FULLTEXTindex_name;

CREATE TABLE tableName([....],FULLTEXT KEYindex_name) ```

和常用的like不同，全文索引有自己的格式，使用match和against關鍵字，如下：

js select * from user where match(name) against('aaa');

3：全文索引實戰

建立一張使用者表people，主鍵ID和使用者名稱name

``js //插入四條記錄 insert into people (name) VALUES ('a'); insert into people (name) VALUES ('aa'); insert into people (name) VALUES ('aaa'); insert into people (name`) VALUES ('aaaa');

//在欄位name上建立全文索引 create fulltext index name_index_ft on people(name);

//開始查詢 // 分別執行下面這4條SQL select * from people where match(name) against('a'); // 沒記錄 select * from people where match(name) against('aa'); // 沒記錄 select * from people where match(name) against('aaa'); // 1條記錄 select * from people where match(name) against('aaaa'); // 1條記錄 ```

很多人可能會問，為什麼我資料庫有 name = a 和 name = aa的記錄，為什麼會找不到，而且全文索引不是跟like一樣可以模糊查詢的嗎？？當我執行 select * from people where match(name) against('aaa'); 應該有二條記錄啊，為什麼只有一條??,接下來就要去了解全文的原理了

4：全文索引原理

在MySql客戶端執行 show variables like '%ft%'; 檢視全文索引的資訊

為什麼在查詢 a 和 aa的時候會沒有記錄

先解釋下其中幾個引數

ft_min_word_len：針對MyISAM引擎的，也就是在你建立的全文索引的欄位的內容最大長度
ft_max_word_len：針對MyISAM引擎的，也就是在你建立的全文索引的欄位的內容最小長度也就是說，只有在內容長度為 4 ~ 84（圖中數值）的時候，你的全文索引才會有效,但是我們使用的Innodb儲存引擎，所以你要看的是這二項
innodb_ft_min_token_size：針對Innodb引擎的，也就是在你建立的全文索引的欄位的內容最小長度
innodb_ft_max_token_size：針對Innodb引擎的，也就是在你建立的全文索引的欄位的內容最大長度

也就是說你的全文索引欄位的內容長度必須在 3 ~ 84之間才會有效，現在能明白為什麼在查 a 和 aa的時候找不到記錄，但是在查 aaa 的時候就會有記錄了吧，因為 aaa 長度剛好等於 3