MySQL FullText索引 實踐。

走在雲端的小黑發表於2020-11-08
MySql索引

首先MySQL5.7版本開始提供了對 FullText 全文索引的支援，全文索引其實就是對模糊 查詢及跨列查詢的一個優化方案

比如我們有一個資訊表 Z 我們要查詢 資訊表中A列含“xx”字元和B列含有“xx”字元的所有行 我們通常是這樣:select * from Z where A like ‘%xx%’ and B like’%xx%’;
這樣一條SQL一句 效能是相當低下的 而且 不能命中索引，資料量龐大的話根本沒得玩。所以MySQL5.7版本開始對這方面的需求提出了優化方案，即引入FullText 全文索引的支援。

用法：

建立一張測試表
CREATE TABLE index_test
(
id INT PRIMARY KEY AUTO_INCREMENT,
fullname VARCHAR(255),
title  VARCHAR(255),
body VARCHAR(3000)
-- FULLTEXT(title, body)`d_test``d_test`
);

給表新增索引：
-- 建立全文索引
CREATE FULLTEXT INDEX fullname_title_index ON index_test(fullname,title);
-- 查詢fullname,title欄位以 “j”開頭的所有記錄
SELECT * FROM `index_test` WHERE MATCH(fullname,title) AGAINST('j*' IN BOOLEAN MODE);

在語法上其實FullText和我們傳統的查詢還是有很多區別的

語法如下：

 '資料庫 管理'     包含'資料庫'或包含'管理'的資料
 '"資料庫 管理"'   不會因為空格而分隔，搜尋包含'資料庫 管理'的資料
 '+資料庫 +管理'   同時包含'資料庫'和'管理'的資料
 '+資料庫 -管理'   包含'資料庫'但沒有'管理'的資料
 '+資料庫 管理'    包含'資料庫'的資料，有'管理'的資料優先顯示
 '+資料庫 >管理'   包含'資料庫'的資料，有'管理'的資料優先顯示
 '+資料庫 <管理'   包含'資料庫'的資料，有'管理'的資料優先顯示,但排位低於'>'的資料

如果需求設計到分詞的話，其實我們可以直接寫一套方案用於分詞，然後結合FullText做一個分詞查詢 就像現在的 ES框架結合IK分詞器那樣，如果全文搜尋 的場景不是特別多或者無法引入ES框架 那完全可以按這個 思路直接實現一套分詞邏輯然後配合FullText使用。

相關文章