您好,登錄后才能下訂單哦!
這篇“mysql全文索引的概念是什么”文章的知識點大部分人都不太理解,所以小編給大家總結(jié)了以下內(nèi)容,內(nèi)容詳細,步驟清晰,具有一定的借鑒價值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來看看這篇“mysql全文索引的概念是什么”文章吧。
在mysql中,全文索引是將存儲于數(shù)據(jù)庫中的整本書或整篇文章中的任意信息查找出來的技術(shù)。通過數(shù)值比較、范圍過濾等就可以完成絕大多數(shù)我們需要的查詢,但是,如果希望通過關(guān)鍵字的匹配來進行查詢過濾,那么就需要基于相似度的查詢,而不是原來的精確數(shù)值比較;而全文索引就是為這種場景設(shè)計的。
概念
全文索引(Full-Text Search)是將存儲于數(shù)據(jù)庫中的整本書或整篇文章中的任意信息查找出來的技術(shù)。它可以根據(jù)需要獲得全文中有關(guān)章、節(jié)、段、句、詞等信息,也可以進行各種統(tǒng)計和分析。全文索引一般是通過倒排索引實現(xiàn)的。
通過數(shù)值比較、范圍過濾等就可以完成絕大多數(shù)我們需要的查詢,但是,如果希望通過關(guān)鍵字的匹配來進行查詢過濾,那么就需要基于相似度的查詢,而不是原來的精確數(shù)值比較。全文索引就是為這種場景設(shè)計的。
你可能會說,用 like + % 就可以實現(xiàn)模糊匹配了,為什么還要全文索引?like + % 在文本比較少時是合適的,但是對于大量的文本數(shù)據(jù)檢索,是不可想象的。全文索引在大量的數(shù)據(jù)面前,能比 like + % 快 N 倍,速度不是一個數(shù)量級,但是全文索引可能存在精度問題。
你可能沒有注意過全文索引,不過至少應(yīng)該對一種全文索引技術(shù)比較熟悉:各種的搜索引擎。雖然搜索引擎的索引對象是超大量的數(shù)據(jù),并且通常其背后都不是關(guān)系型數(shù)據(jù)庫,不過全文索引的基本原理是一樣的。
版本支持
開始之前,先說一下全文索引的版本、存儲引擎、數(shù)據(jù)類型的支持情況
MySQL 5.6 以前的版本,只有 MyISAM 存儲引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存儲引擎均支持全文索引;
只有字段的數(shù)據(jù)類型為 char、varchar、text 及其系列才可以建全文索引。
測試或使用全文索引時,要先看一下自己的 MySQL 版本、存儲引擎和數(shù)據(jù)類型是否支持全文索引。
索引的操作隨便一搜都是,這里還是再啰嗦一遍。
創(chuàng)建
創(chuàng)建表時創(chuàng)建全文索引
create table fulltext_test ( id int(11) NOT NULL AUTO_INCREMENT, content text NOT NULL, tag varchar(255), PRIMARY KEY (id), FULLTEXT KEY content_tag_fulltext(content,tag) // 創(chuàng)建聯(lián)合全文索引列 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
在已存在的表上創(chuàng)建全文索引
create fulltext index content_tag_fulltext on fulltext_test(content,tag);
通過 SQL 語句 ALTER TABLE 創(chuàng)建全文索引
alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);
修改
修改個 O,直接刪掉重建。
刪除
直接使用 DROP INDEX 刪除全文索引
drop index content_tag_fulltext on fulltext_test;
通過 SQL 語句 ALTER TABLE 刪除全文索引
alter table fulltext_test drop index content_tag_fulltext;
和常用的模糊匹配使用 like + % 不同,全文索引有自己的語法格式,使用 match 和 against 關(guān)鍵字,比如
select * from fulltext_test where match(content,tag) against('xxx xxx');
注意: match() 函數(shù)中指定的列必須和全文索引中指定的列完全相同,否則就會報錯,無法使用全文索引,這是因為全文索引不會記錄關(guān)鍵字來自哪一列。如果想要對某一列使用全文索引,請單獨為該列創(chuàng)建全文索引。
添加測試數(shù)據(jù)
有了上面的知識,就可以測試一下全文索引了。
首先創(chuàng)建測試表,插入測試數(shù)據(jù)
create table test ( id int(11) unsigned not null auto_increment, content text not null, primary key(id), fulltext key content_index(content) ) engine=MyISAM default charset=utf8;insert into test (content) values ('a'),('b'),('c');insert into test (content) values ('aa'),('bb'),('cc');insert into test (content) values ('aaa'),('bbb'),('ccc');insert into test (content) values ('aaaa'),('bbbb'),('cccc');
按照全文索引的使用語法執(zhí)行下面查詢
select * from test where match(content) against('a');select * from test where match(content) against('aa');select * from test where match(content) against('aaa');
根據(jù)我們的慣性思維,應(yīng)該會顯示 4 條記錄才對,然而結(jié)果是 1 條記錄也沒有,只有在執(zhí)行下面的查詢時
select * from test where match(content) against('aaaa');
才會搜到 aaaa 這 1 條記錄。
為什么?這個問題有很多原因,其中最常見的就是 最小搜索長度 導(dǎo)致的。另外插一句,使用全文索引時,測試表里至少要有 4 條以上的記錄,否則,會出現(xiàn)意想不到的結(jié)果。
MySQL 中的全文索引,有兩個變量,最小搜索長度和最大搜索長度,對于長度小于最小搜索長度和大于最大搜索長度的詞語,都不會被索引。通俗點就是說,想對一個詞語使用全文索引搜索,那么這個詞語的長度必須在以上兩個變量的區(qū)間內(nèi)。
這兩個的默認值可以使用以下命令查看
show variables like '%ft%';
可以看到這兩個變量在 MyISAM 和 InnoDB 兩種存儲引擎下的變量名和默認值
// MyISAM ft_min_word_len = 4; ft_max_word_len = 84; // InnoDB innodb_ft_min_token_size = 3; innodb_ft_max_token_size = 84;
可以看到最小搜索長度 MyISAM 引擎下默認是 4,InnoDB 引擎下是 3,也即,MySQL 的全文索引只會對長度大于等于 4 或者 3 的詞語建立索引,而剛剛搜索的只有 aaaa 的長度大于等于 4。
配置最小搜索長度
全文索引的相關(guān)參數(shù)都無法進行動態(tài)修改,必須通過修改 MySQL 的配置文件來完成。修改最小搜索長度的值為 1,首先打開 MySQL 的配置文件 /etc/my.cnf,在 [mysqld] 的下面追加以下內(nèi)容
[mysqld]innodb_ft_min_token_size = 1ft_min_word_len = 1
然后重啟 MySQL 服務(wù)器,并修復(fù)全文索引。注意,修改完參數(shù)以后,一定要修復(fù)下索引,不然參數(shù)不會生效。
兩種修復(fù)方式,可以使用下面的命令修復(fù)
repair table test quick;
或者直接刪掉重新建立索引,再次執(zhí)行上面的查詢,a、aa、aaa 就都可以查出來了。
但是,這里還有一個問題,搜索關(guān)鍵字 a 時,為什么 aa、aaa、aaaa 沒有出現(xiàn)結(jié)果中,講這個問題之前,先說說兩種全文索引。
自然語言的全文索引
默認情況下,或者使用 in natural language mode 修飾符時,match() 函數(shù)對文本集合執(zhí)行自然語言搜索,上面的例子都是自然語言的全文索引。
自然語言搜索引擎將計算每一個文檔對象和查詢的相關(guān)度。這里,相關(guān)度是基于匹配的關(guān)鍵詞的個數(shù),以及關(guān)鍵詞在文檔中出現(xiàn)的次數(shù)。在整個索引中出現(xiàn)次數(shù)越少的詞語,匹配時的相關(guān)度就越高。相反,非常常見的單詞將不會被搜索,如果一個詞語的在超過 50% 的記錄中都出現(xiàn)了,那么自然語言的搜索將不會搜索這類詞語。上面提到的,測試表中必須有 4 條以上的記錄,就是這個原因。
這個機制也比較好理解,比如說,一個數(shù)據(jù)表存儲的是一篇篇的文章,文章中的常見詞、語氣詞等等,出現(xiàn)的肯定比較多,搜索這些詞語就沒什么意義了,需要搜索的是那些文章中有特殊意義的詞,這樣才能把文章區(qū)分開。
布爾全文索引
在布爾搜索中,我們可以在查詢中自定義某個被搜索的詞語的相關(guān)性,當(dāng)編寫一個布爾搜索查詢時,可以通過一些前綴修飾符來定制搜索。
MySQL 內(nèi)置的修飾符,上面查詢最小搜索長度時,搜索結(jié)果 ft_boolean_syntax 變量的值就是內(nèi)置的修飾符,下面簡單解釋幾個,更多修飾符的作用可以查手冊
+ 必須包含該詞
- 必須不包含該詞
> 提高該詞的相關(guān)性,查詢的結(jié)果靠前
< 降低該詞的相關(guān)性,查詢的結(jié)果靠后
(*)星號 通配符,只能接在詞后面
對于上面提到的問題,可以使用布爾全文索引查詢來解決,使用下面的命令,a、aa、aaa、aaaa 就都被查詢出來了。
select * test where match(content) against('a*' in boolean mode);
以上就是關(guān)于“mysql全文索引的概念是什么”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對大家有幫助,若想了解更多相關(guān)的知識內(nèi)容,請關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。