一文給你講清楚MySql全文索引實戰和原理

語言: CN / TW / HK

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不同,全文索引有自己的格式,使用matchagainst關鍵字,如下:

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%'; 檢視全文索引的資訊

image.png

為什麼在查詢 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

為什麼在查詢 aaa的時候。name = aaaa的記錄找不到

在全文索引底層是有一個切詞的概念的,比如 祝中國越來越強大 全文索引按照一個規則切詞,有可能會被切成 中國越來越強大。那麼切詞的依據是什麼呢?全文索引又是怎麼切詞的呢??

image.png

在這裡有個很重要的引數:ft_boolean_syntax,就是第一項,後面有很多內容,全文索引就是按照這些來切詞的。現在表的記錄如下

image.png

將記錄更新如下:

image.png

```js 現在我們再來執行 select * from people where match(name) against('aaa');

```

image.png

現在就能把所有的記錄都找出來了吧,但是細心的你會發現,id = 6 的這條記錄還是沒找出來,這是為什麼??

全文索引為什麼預設不支援模糊查詢

為什麼是預設呢,因為至少到現在我們發現全文索引都是 等值查詢 的,這是因為全文索引預設支援的就是等值查詢,如果要支援模糊查詢需要改寫SQL如下:

js select * from people where match(name) against('aaa*' in boolean mode);

image.png

現在就可以把所有符合的資料都查出來了,* 表示一個或多個字元