MySQL的全文索引Fulltext Index | 包括ngram

InnoDB的全文索引使用反向索引的設(shè)計(jì)。反向索引存儲(chǔ)了一個(gè)單詞（word）列表，對(duì)于每個(gè)單詞，都有一個(gè)文檔的列表，來(lái)標(biāo)識(shí)這個(gè)單詞出現(xiàn)的地方。為了支持臨近搜索（proximity search），每個(gè)單詞的位置信息也以字節(jié)偏移的方式存儲(chǔ)。

創(chuàng)新互聯(lián)建站長(zhǎng)期為1000+客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開(kāi)放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為永春企業(yè)提供專業(yè)的網(wǎng)站設(shè)計(jì)制作、成都網(wǎng)站制作，永春網(wǎng)站改版等技術(shù)服務(wù)。擁有十多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開(kāi)發(fā)。

當(dāng)創(chuàng)建了InnoDB全文索引，一系列的索引表會(huì)一同被創(chuàng)建，見(jiàn)下面的例子：

最前面的六個(gè)表包含了反向索引，它們被稱作附屬索引表（auxiliary index table）。當(dāng)輸入的表被索引（tokenized）后，每個(gè)獨(dú)立的單詞（亦稱作“tokens”）會(huì)被攜帶其DOC_ID和位置信息插入到索引表中。根據(jù)單詞第一個(gè)字符的字符集排序權(quán)重，在六個(gè)索引表中對(duì)單詞進(jìn)行完全排序和分區(qū)。

反向索引分區(qū)到六個(gè)附屬索引表以支持并行的索引創(chuàng)建。默認(rèn)有2個(gè)線程復(fù)制索引（Tokenize）、排序、插入單詞和關(guān)聯(lián)數(shù)據(jù)到索引表中。工作的線程的數(shù)量由 innodb_ft_sort_pll_degree 配置項(xiàng)控制的。對(duì)于大表的全文索引，可以考慮增加線程數(shù)量。

如果主表創(chuàng)建在 xx表空間，索引表存儲(chǔ)在它們自己的表空間中。反之，索引表存儲(chǔ)于其索引的表空間中。

前面例子展示的另外一種索引表被稱作通用索引表，它們被用于全文索引的“刪除處理（deletion handing）”和存儲(chǔ)內(nèi)部狀態(tài)。不同于為每個(gè)全文索引都各自創(chuàng)建的反向索引表，這組表對(duì)特定表的所有全文索引都是共用的。

即使全文索引刪掉了，通用索引（Common Index）也會(huì)被保留，當(dāng)全文索引刪除后，為這個(gè)索引而創(chuàng)建的FTS_DOC_ID列依然保留，因?yàn)橐瞥鼺TS_DOC_ID列會(huì)導(dǎo)致重構(gòu)之前被索引的表。管理FTS_DOC_ID列需要用到通用索引表。

為了防止大量并發(fā)讀寫附屬表，InnoDB使用全文索引緩存去臨時(shí)緩存最近的插入行。在存滿并刷入磁盤之前，緩存的內(nèi)容一直存儲(chǔ)在內(nèi)存之中，可以通過(guò)查詢 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表去查看最近緩存的插入行。

緩存和批處理刷新行為避免了對(duì)輔助索引表的頻繁更新，頻繁更新可能會(huì)在繁忙的插入和更新期間導(dǎo)致并發(fā)訪問(wèn)問(wèn)題。批處理還避免了對(duì)同一個(gè)word的多次插入，最大化的減少了重復(fù)的條目。相同的word會(huì)先merge再刷入到磁盤中，而不是為每個(gè)word單獨(dú)插入，這樣提高了插入效率并且使得索引附屬表盡可能的小。

全文索引緩存只緩存最近插入的行，查詢時(shí)，已經(jīng)刷入磁盤（附屬索引表）的數(shù)據(jù)不會(huì)再回到索引緩存中。附屬索引表中的內(nèi)容是直接查詢的，最終返回的結(jié)果返回前需要將附屬索引表的結(jié)果和索引緩存中的結(jié)果合并。

InnoDB使用被稱作DOC_ID的唯一文件描述符，將全文索引中的單詞與該單詞在文檔中的記錄映射起來(lái)。映射關(guān)系需要索引表中的 FTS_DOC_ID 列。在創(chuàng)建全文索引時(shí)，如果沒(méi)有定義 FTS_DOC_ID 列，InnoDB會(huì)自動(dòng)的加入一個(gè)隱藏的 FTS_DOC_ID 列。下面是一個(gè)例子，

CREATE FULLTEXT INDEX ft_index ON xxxxxxxx(CONTEXT)

[2021-11-12 18:14:04] [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID

重點(diǎn)看一下這一行： [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID ，InnoDB重新構(gòu)建了這個(gè)表，并且添加了一個(gè)列 FTS_DOC_ID 。

在CREATE TABLE的過(guò)程中添加 FTS_DOC_ID 的時(shí)間成本要低于在已經(jīng)有數(shù)據(jù)的表上建立全文索引。如果在表加載數(shù)據(jù)之前定義 FTS_DOC_ID 列，這個(gè)表和它的索引都不需要為了新增列而重新構(gòu)建。如果你不需要考慮 CREATE FULLTEXT INDEX 的性能，可以讓InnoDB為你創(chuàng)建 FTS_DOC_ID 列。InnoDB會(huì)新增一個(gè)隱藏的 FTS_DOC_ID 列，并且在 FTS_DOC_ID 上建立唯一索引（FTS_DOC_ID_INDEX）。如果你想自行創(chuàng)建 FTS_DOC_ID 列，這個(gè)列必須定義為 BIGINT UNSIGNED NOT NULL 且命名為FTS_DOC_ID（全大寫），如下例子：

如果你自行定義 FTS_DOC_ID 列的話，你需要負(fù)責(zé)管理這個(gè)列，避免空值（empty）或者重復(fù)值。 FTS_DOC_ID 的值是不能被重復(fù)利用的，所以也就是說(shuō) FTS_DOC_ID 的值是需要一直增加的。

或者，你可以在 FTS_DOC_ID 列上創(chuàng)建所必須的唯一索引FTS_DOC_ID_INDEX（全大寫）。

mysql CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

如果你沒(méi)有創(chuàng)建FTS_DOC_ID_INDEX，InnoDB會(huì)自動(dòng)創(chuàng)建。

在MySQL 5.7.13前，允許最大FTS_DOC_ID與最新的FTS_DOC_ID之間的間隔為10000，在MySQL 5.7.13及之后的版本中，這個(gè)允許的間隔為65535。

為了避免重新構(gòu)建表，F(xiàn)TS_DOC_ID列在刪除了全文索引之后依然被保留。

刪除被索引文件的一個(gè)記錄，可能會(huì)在附屬索引表中產(chǎn)生非常多的小的刪除項(xiàng)，在并發(fā)訪問(wèn)時(shí)，會(huì)產(chǎn)生熱點(diǎn)問(wèn)題。為了避免這個(gè)問(wèn)題，每當(dāng)被索引表中的記錄被刪除時(shí)，會(huì)將被刪文檔的DOC_ID記錄在一個(gè)特別的 FTS_*_DELETED 表中，同時(shí)全文索引中已經(jīng)索引了的記錄依然被保存。在返回查詢結(jié)果前，使用 FTS_*_DELETED 中的信息去過(guò)濾掉已經(jīng)刪除掉了的DOC_ID。這種設(shè)計(jì)的優(yōu)勢(shì)在于刪除速度快且消耗低。不好的地方在于索引的大小不能隨著記錄的刪除而立即減少。為了刪除已刪除記錄在全文索引中的項(xiàng)，需要對(duì)被索引的表執(zhí)行OPTIMIZE TABLE，配合[ innodb_optimize_fulltext_only=ON ]，去重構(gòu)全文索引。

細(xì)節(jié)略，有例子：

全文搜索只能看到已經(jīng)提交了的數(shù)據(jù)。

你可以通過(guò)查詢下面的INFORMATION_SCHEMA表，來(lái)監(jiān)控或測(cè)試InnoDB的一些特殊文本處理。

默認(rèn)的分詞器不支持中文，不能檢索到中文中的英文單詞。

InnoDB默認(rèn)的Stopwords：

select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;

SQL中的關(guān)鍵詞（保留關(guān)鍵字）：

Shell中的關(guān)鍵詞：for，while，echo

其他：###, ***, --,

被索引表數(shù)據(jù)量、索引表數(shù)據(jù)量

模糊匹配與嚴(yán)格匹配的性能差距

需要將 innodb_optimize_fulltext_only 配置為ON，這里是否需要DBA在MySQL鏡像中修改？

innodb_optimize_fulltext_only 設(shè)置為ON后，對(duì)系統(tǒng)有何影響需要評(píng)估。

innodb_optimize_fulltext_only

執(zhí)行的時(shí)間、頻率。

MySQL內(nèi)建的全文檢索解析器使用單詞之間的空白作為分隔符以標(biāo)識(shí)單詞的頭尾，但是這里有個(gè)限制，對(duì)于表意文字，它是沒(méi)有單詞分隔符的。為了解決這個(gè)限制，MySQL提供了支持中文、日語(yǔ)、韓語(yǔ)的 ngram 解析器。ngram解析器支持InnoDB和MyISAM。

Ngram是內(nèi)建在服務(wù)中的插件，像其他自建在服務(wù)中的插件一樣，服務(wù)啟動(dòng)時(shí)會(huì)自動(dòng)加載它。全文檢索的語(yǔ)法參考上面（ Section 12.10, “Full-Text Search Functions” ），這里只討論一些不同的地方。除了單詞的最小、最大長(zhǎng)度配置項(xiàng)（[ innodb_ft_min_token_size ]innodb_ft_max_token_size,ft_min_word_len,ft_max_word_len，全文檢索依賴一些配置項(xiàng)都是可以使用的。

Ngram默認(rèn)索引的單詞（token）的大小為2（ 2bigram ）。例如，索引的大小為2，Ngram解析器解析字符串“abc def”為四個(gè)單詞元素(tokens)：“ab”, “bc”, “de” and “ef”。

ngram token size is configurable using the ngram_token_size configuration option, which has a minimum value of 1 and maximum value of 10.

作為只讀變量, ngram_token_size 只能在啟動(dòng)配置或者配置文件中指定

與默認(rèn)的解析器相差不大，多了一句： xxx WITH PARSER ngram

Ngram在解析時(shí)去除空格，如

MySQL內(nèi)建的默認(rèn)全文檢索解析器將單詞與Stopword列表中的做對(duì)比，如果單詞與Stopword列表中的元素相同的話，這個(gè)單詞則不會(huì)被索引。對(duì)于Ngram解析器，Stopword的處理方式不同。Ngram解析器不排除與stopword列表中的條目相等的token，而是排除包含stopwords的token。例如，假設(shè) ngram_token_size=2 ，包含“a,b”的文檔將被解析為 “a,” h和“,b”。如果將逗號(hào)（“,”）定義為停止字，則 “a,”和“,b”都將不會(huì)加入索引中，因?yàn)樗鼈儼禾?hào)。

例子：

默認(rèn)Ngram解析器使用默認(rèn)的Stopword列表，這里面含有英文的Stopword。如果需要中文的Stopword，需要你自己創(chuàng)建。

Stopword的長(zhǎng)度超過(guò) ngram_token_size則會(huì)被忽略。

有兩個(gè)文檔，一個(gè)包含“ab”，另一個(gè)包含“abc”。對(duì)于搜索文本“abc”將轉(zhuǎn)換成“ab”，“bc”。

略。

For example, The search phrase “abc” is converted to “ab bc”, which returns documents containing “abc” and “ab bc”.

The search phrase “abc def” is converted to “ab bc de ef”, which returns documents containing “abc def” and “ab bc de ef”. A document that contains “abcdef” is not returned.

使用Ngram解析器好處是支持了中文的檢索

mysql進(jìn)行全文檢索并高亮顯示

軟件中的設(shè)置。MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQLAB公司開(kāi)發(fā)，目前屬于Oracle旗下產(chǎn)品。mysql進(jìn)行全文檢索的時(shí)候，并且高亮顯示，是這款軟件中的設(shè)置，是在設(shè)置中將開(kāi)通了高亮顯示。

怎么MySql添加全文索引

使用索引是數(shù)據(jù)庫(kù)性能優(yōu)化的必備技能之一。在MySQL數(shù)據(jù)庫(kù)中，有四種索引：聚集索引(主鍵索引)、普通索引、唯一索引以及我們這里將要介紹的全文索引(FULLTEXT INDEX)。

全文索引(也稱全文檢索)是目前搜索引擎使用的一種關(guān)鍵技術(shù)。它能夠利用「分詞技術(shù)「等多種算法智能分析出文本文字中關(guān)鍵字詞的頻率及重要性，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果。在這里，我們就不追根究底其底層實(shí)現(xiàn)原理了，現(xiàn)在我們來(lái)看看在MySQL中如何創(chuàng)建并使用全文索引。

在MySQL中，創(chuàng)建全文索引相對(duì)比較簡(jiǎn)單。例如，我們有一個(gè)文章表(article)，其中有主鍵ID(id)、文章標(biāo)題(title)、文章內(nèi)容(content)三個(gè)字段。現(xiàn)在我們希望能夠在title和content兩個(gè)列上創(chuàng)建全文索引，article表及全文索引的創(chuàng)建SQL語(yǔ)句如下：

--創(chuàng)建article表

CREATE TABLE article (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

content TEXT,

FULLTEXT (title, content) --在title和content列上創(chuàng)建全文索引

);

上面就是在創(chuàng)建表的同時(shí)建立全文索引的SQL示例。此外，如果我們想要給已經(jīng)存在的表的指定字段創(chuàng)建全文索引，同樣以article表為例，我們可以使用如下SQL語(yǔ)句進(jìn)行創(chuàng)建：

--給現(xiàn)有的article表的title和content字段創(chuàng)建全文索引

--索引名稱為fulltext_article

ALTER TABLE article

ADD FULLTEXT INDEX fulltext_article (title, content)

在MySQL中創(chuàng)建全文索引之后，現(xiàn)在就該了解如何使用了。眾所周知，在數(shù)據(jù)庫(kù)中進(jìn)行模糊查詢是使用LIKE關(guān)鍵字進(jìn)行查詢，例如：

SELECT * FROM article WHERE content LIKE '%查詢字符串%'

那么，我們使用全文索引也是這樣用的嗎？當(dāng)然不是，我們必須使用特有的語(yǔ)法才能使用全文索引進(jìn)行查詢。例如，我們想要在article表的title和content列中全文檢索指定的查詢字符串，可以如下編寫SQL語(yǔ)句：

SELECT * FROM article WHERE MATCH(title, content) AGAINST('查詢字符串')

強(qiáng)烈注意：MySQL自帶的全文索引只能用于數(shù)據(jù)庫(kù)引擎為MyISAM的數(shù)據(jù)表，如果是其他數(shù)據(jù)引擎，則全文索引不會(huì)生效。此外，MySQL自帶的全文索引只能對(duì)英文進(jìn)行全文檢索，目前無(wú)法對(duì)中文進(jìn)行全文檢索。如果需要對(duì)包含中文在內(nèi)的文本數(shù)據(jù)進(jìn)行全文檢索，我們需要采用Sphinx(斯芬克斯)/Coreseek技術(shù)來(lái)處理中文。本站將會(huì)在后續(xù)文章中對(duì)Sphinx以及Coreseek進(jìn)行介紹。

備注1：目前，使用MySQL自帶的全文索引時(shí)，如果查詢字符串的長(zhǎng)度過(guò)短將無(wú)法得到期望的搜索結(jié)果。MySQL全文索引所能找到的詞的默認(rèn)最小長(zhǎng)度為4個(gè)字符。另外，如果查詢的字符串包含停止詞，那么該停止詞將會(huì)被忽略。

備注2：如果可能，請(qǐng)盡量先創(chuàng)建表并插入所有數(shù)據(jù)后再創(chuàng)建全文索引，而不要在創(chuàng)建表時(shí)就直接創(chuàng)建全文索引，因?yàn)榍罢弑群笳叩娜乃饕室摺?/p>

如何使用mysql的全文索引搜索

你有沒(méi)有想過(guò)如何使用搜索功能在所有整站中實(shí)現(xiàn)！互聯(lián)網(wǎng)博客和網(wǎng)站，大多數(shù)都采用MySQL數(shù)據(jù)庫(kù)。MySQL提供了一個(gè)美妙的方式實(shí)施一個(gè)小的搜索引擎，在您的網(wǎng)站（全文檢索）。所有您需要做的是擁有的MySQL 4.x及以上。MySQL提供全文檢索功能，我們可以用它來(lái) ??實(shí)現(xiàn)搜索功能。

首先，讓我們?yōu)槲覀兊睦又性O(shè)置一個(gè)示例表。我們將創(chuàng)建一個(gè)名為第一個(gè)表。

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

);

在此表中還可以添加一些示例數(shù)據(jù)。執(zhí)行后，插入查詢。

INSERT INTO articles (title,body) VALUES

('MySQL Tutorial','DBMS stands for DataBase ...'),

('How To Use MySQL Well','After you went through a ...'),

('Optimizing MySQL','In this tutorial we will show ...'),

('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs. YourSQL','In the following database comparison ...'),

('MySQL Security','When configured properly, MySQL ...');

一旦樣本數(shù)據(jù)是準(zhǔn)備好，我們可以開(kāi)始我們的全文檢索功能。

自然語(yǔ)言全文搜索

嘗試我們的示例表上執(zhí)行下面的SELECT查詢。

SELECT * FROM articles

WHERE MATCH (title,body) AGAINST ('database');

你就能看到結(jié)果如下：

在下面的數(shù)據(jù)庫(kù)比較5 MySQL與YourSQL的...

MySQL教程DBMS 1代表數(shù)據(jù)庫(kù)...

我們?cè)谏厦娴腟QL查詢（標(biāo)題，正文）反對(duì)（“數(shù)據(jù)庫(kù)”）的比賽，選擇所有的記錄，列標(biāo)題和正文進(jìn)行全文搜索。

您可以修改該查詢，并創(chuàng)建您自己的版本，以自己的數(shù)據(jù)庫(kù)中執(zhí)行全文搜索。

布爾全文搜索

它可能發(fā)生，你要指定某些關(guān)鍵字在您的搜索條件。此外，您可能要忽略某些關(guān)鍵字。布爾全文搜索可以用來(lái)執(zhí)行這些要求的全文檢索。

檢查下面的SELECT查詢。

SELECT * FROM articles WHERE MATCH (title,body)

AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

如果您發(fā)現(xiàn)上述選擇查詢，我們?cè)黾恿瞬紶朚ODE反對(duì)（）。這個(gè)查詢將獲取MySQL的關(guān)鍵字，但不YourSQL關(guān)鍵字的所有記錄。請(qǐng)注意+和-我們以前指定的關(guān)鍵字！

在執(zhí)行此功能，MySQL使用什么有時(shí)也被稱為布爾邏輯作為暗示，其中：+代表與-代表不是[無(wú)操作員]暗示或

以下是幾個(gè)例子布爾搜索條件。

“蘋果香蕉

查找行至少包含兩個(gè)詞之一。

“+蘋果+果汁”

尋找包含兩個(gè)單詞的行。

“+蘋果Macintosh

查找行包含“蘋果”，但排名的行，如果它們也包含“麥金塔”。

“+蘋果Macintosh的”

查找行包含“蘋果”這個(gè)詞，而不是“麥金塔”。

'+蘋果Macintosh的“

查找包含單詞“蘋果”的行，但如果該行也包含單詞“麥金塔”，速度比如果行不低。這是“軟”比“+蘋果Macintosh電腦”，為“麥金塔”的存在，導(dǎo)致該行不能在所有返回的搜索。

'+蘋果+（營(yíng)業(yè)額餡餅）“

行包含“蘋果”和“營(yíng)業(yè)額”，或“蘋果”和“餡餅”（任何順序）的話，但排名“蘋果的營(yíng)業(yè)額”比“蘋果餡餅“。

限制

支持全文檢索的MyISAM表只。MySQL 4.1中，使用多個(gè)字符設(shè)置一個(gè)單一的表內(nèi)的支持。然而，在一個(gè)FULLTEXT索引的所有列，必須使用相同的字符集和校對(duì)規(guī)則。MATCH（）列列表必須匹配完全在一些列清單表的FULLTEXT索引定義，除非這場(chǎng)比賽（）是在布爾模式。布爾模式搜索，可以做非索引列，雖然他們很可能是緩慢的。

網(wǎng)頁(yè)名稱：mysql怎么弄全文搜索 Mysql在全文搜索的函數(shù)中
標(biāo)題來(lái)源：http://chinadenli.net/article2/hepgic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供建站公司、用戶體驗(yàn)、網(wǎng)站內(nèi)鏈、手機(jī)網(wǎng)站建設(shè)、微信小程序、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)