MySql中的Full Text Search全文索引優(yōu)化
目錄
- 開(kāi)篇
- 一個(gè)簡(jiǎn)單的DEMO
- 天下沒(méi)有免費(fèi)的午餐
- 無(wú)索引
- 使用 B 樹(shù)索引
- 引入反向索引
- 在默認(rèn)解析器中使用反向索引
- 在 n-gram 解析器中使用反向索引
- InnoDB 反向索引性能下降
- 備選方案
開(kāi)篇
在我們的生產(chǎn)環(huán)境中,有一個(gè)模糊檢索的文檔框,但是當(dāng)數(shù)據(jù)量級(jí)別上去之后,頻繁對(duì)數(shù)據(jù)庫(kù)造成壓力,所以想使用Full Text全文索引進(jìn)行優(yōu)化 下面是一個(gè)總結(jié)的簡(jiǎn)單案例
一個(gè)簡(jiǎn)單的DEMO
假設(shè)我們有客戶的地址簿，目標(biāo)是通過(guò)他/她的姓名或電子郵件快速找到人。
CREATE TABLE `address_book` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(128) NOT NULL,
`email` VARCHAR(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;
我們將用 1_000_000 個(gè)隨機(jī)生成的人填充地址簿。每個(gè)人將被插入單獨(dú)的查詢中。姓名將始終采用整齊的形式 - 名字和姓氏。電子郵件會(huì)更加混亂——名字/姓氏的順序和存在不同，分隔符不同，并且有一些隨機(jī)數(shù)。
> SELECT `name`, `email` FROM `addressbook` LIMIT 8; +--------------------+---------------------------------+ | name | email | +--------------------+---------------------------------+ | Reed Slavik| 664-slavik-reed@example.com | | Reilly Isaacson | reilly972isaacson@example.com | | Theodore Klosinski | 942.klosinski@example.com | | Duncan Sinke | 912.duncan@example.com | | Maranda Cabrara | cabrara-809-maranda@example.com | | Hugh Harrop| hugh765@example.com | | Bernard Luetzow | bernard887luetzow@example.com | | Niki Manesis | niki-247@example.com | +--------------------+---------------------------------+
測(cè)試將在具有默認(rèn)配置的庫(kù)存 MySQL 8.0.32 Docker 映像上執(zhí)行（除非另有說(shuō)明）。硬件是 AMD 6800U、32GB RAM、PCIe NVMe 4.0 x4 SSD。操作系統(tǒng)是帶有 BTRFS 和 LUKS 磁盤(pán)加密的 vanilla Arch Linux。
天下沒(méi)有免費(fèi)的午餐
天下沒(méi)有免費(fèi)的午餐。索引加快SELECT但減慢INSERT//語(yǔ)句，因?yàn)橛?jì)算的額外 CPU 成本以及額外的磁盤(pán)傳輸和存儲(chǔ)空間成本UPDATE。DELETE我會(huì)嘗試寫(xiě)簡(jiǎn)短的總結(jié)何時(shí)使用每種方法，有什么好處和缺點(diǎn)。
無(wú)索引
最簡(jiǎn)單的方法是沒(méi)有索引列并使用LIKE '%john%'語(yǔ)法。
因?yàn)闆](méi)有索引維護(hù)這種方法不會(huì)增加數(shù)據(jù)加載時(shí)間和存儲(chǔ)空間。
$ time cat address_book.sql | mysql real 23m 31.43s
> SELECT data_length, index_length FROM information_schema.tables WHERE table_name = "address_book"; +-------------+--------------+ | DATA_LENGTH | INDEX_LENGTH | +-------------+--------------+ | 71942144 | 0 | +-------------+--------------+
性能很差。當(dāng)沒(méi)有使用索引時(shí)，MySQL 使用 Turbo Boyer-Moore 算法 來(lái)查找匹配的行。
> SELECT * FROM `address_book` WHERE `name` LIKE "%john%" AND `name` LIKE "%doe%"; +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | doemel.36.johnie@example.com | | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com | +--------+----------------+-------------------------------+ 2 rows in set (0.222 sec)
如查詢所示，所有行都需要從磁盤(pán)中提取以進(jìn)行分析EXPLAIN。
> EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE "%john%" AND `name` LIKE "%doe%"\G
id: 1
select_type: SIMPLE
table: address_book
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 996458
filtered: 1.23
Extra: Using where
使用： 當(dāng)您的應(yīng)用程序很少進(jìn)行全文搜索并且您愿意接受低查詢性能時(shí)。在小數(shù)據(jù)集上效果很好。簡(jiǎn)單的實(shí)施是巨大的好處。
避免： 當(dāng)頻繁??使用全文搜索時(shí)——你會(huì)在這里消耗大量的數(shù)據(jù)庫(kù)性能，尤其是在大數(shù)據(jù)集上。此外，由于全行掃描，它可能會(huì)阻止應(yīng)用程序中需要FOR UPDATE鎖定此類(lèi)表的其他查詢。
使用 B 樹(shù)索引
不幸的是，在一個(gè)字段上打一個(gè)索引并稱之為一天是行不通的。在 B 樹(shù)索引中，文本從搜索短語(yǔ)的開(kāi)始到結(jié)束被轉(zhuǎn)換為一系列二元（真/假）測(cè)試樹(shù)。對(duì)于示例數(shù)據(jù)：
1 John 2 Joseph 3 Joseph 4 Ann
它看起來(lái)像這樣。
<="a"?
/ \
yes no
/ \
<="nn"? <="jo"
/ /
yesyes
/ /
[4] <="h"?
/ \
yes no
/ \
<="n"? <="seph"?
/ /
yesyes
/ /
[1][2,3]
如果你正在尋找Joseph你測(cè)試第一個(gè)字符。因?yàn)?code>j>a你經(jīng)過(guò)no路徑。然后你測(cè)試前兩個(gè)字符。因?yàn)?code>jo=jo你從短語(yǔ)中刪除它們并通過(guò)yes路徑。然后你測(cè)試下一個(gè)不匹配的字符是h......你繼續(xù)執(zhí)行這些系列的測(cè)試，直到你最終到達(dá)包含你正在尋找的短語(yǔ)的行列表，在這種情況下是2和3。但這表明這種類(lèi)型的索引必須從短語(yǔ)的開(kāi)始到結(jié)束起作用，這意味著短語(yǔ)不能以通配符開(kāi)頭。
讓我們把它添加到我們的表中。
> ALTER TABLE `address_book` ADD KEY (`name`), ADD KEY (`email`);
如您所見(jiàn)，當(dāng)搜索的短語(yǔ)以通配符索引開(kāi)頭時(shí)將不會(huì)被使用。
> EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE "%john%" AND `name` LIKE "%doe%"\G
id: 1
select_type: SIMPLE
table: address_book
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 996458
filtered: 1.23
Extra: Using where
如果您知道文本具有某種特定結(jié)構(gòu)（在我們的例子中，名稱在前），我們可以利用這些知識(shí)并在不使用通配符的情況下詢問(wèn)名稱。
> SELECT * FROM `address_book` WHERE `name` LIKE "john%" AND `name` LIKE "%doe%"; +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | doemel.36.johnie@example.com | | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com | +--------+----------------+-------------------------------+ 2 rows in set (0.003 sec)
Explain 顯示這次使用了索引，所有以 開(kāi)頭的名稱john都在索引中找到，并且 Boyer-Moore 必須僅用于針對(duì) 對(duì)該集合進(jìn)行精細(xì)過(guò)濾doe。
> EXPLAIN SELECT * FROM `address_book` WHERE `name` LIKE "john%" AND `name` LIKE "%doe%"\G
id: 1
select_type: SIMPLE
table: address_book
partitions: NULL
type: range
possible_keys: name
key: name
key_len: 514
ref: NULL
rows: 3602
filtered: 100.00
Extra: Using index condition
當(dāng)涉及到電子郵件時(shí)，這種方法很快就會(huì)顯示出局限性。它太混亂了——可能以名字開(kāi)頭，可能以姓氏開(kāi)頭，甚至可能以完全不同的東西開(kāi)頭。在這種情況下，查詢時(shí)間就像沒(méi)有索引的情況一樣。
> SELECT * FROM `address_book` WHERE `email` LIKE "%john%" AND `email` LIKE "%doe%"; +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | doemel.36.johnie@example.com | | 316137 | Johnnie Doepel | johnnie-doepel-72@example.com | +--------+----------------+-------------------------------+ 2 rows in set (0.314 sec)
在性能方面，它會(huì)稍微減慢數(shù)據(jù)加載速度并使存儲(chǔ)空間增加一倍，但并不是很有用。
$ time cat address_book.sql | mysql real 24m 12.81s
> SELECT data_length, index_length FROM information_schema.tables WHERE table_name = "address_book"; +-------------+--------------+ | DATA_LENGTH | INDEX_LENGTH | +-------------+--------------+ | 71942144 | 112623616 | +-------------+--------------+
使用： 當(dāng)您可以將文本拆分為具有自己索引的明確定義的列時(shí)。例如重組表以單獨(dú)first_name存儲(chǔ)last_name。此外，您必須愿意犧牲起始通配符。
避免： 當(dāng)文本太不可預(yù)測(cè)和無(wú)序時(shí)，例如email您name商店中的各種產(chǎn)品。
注意：從右到左的語(yǔ)言也不例外，搜索的詞組不能以通配符開(kāi)頭，無(wú)論文字的方向是什么。
引入反向索引
首先讓我們解釋一下什么是反向索引。B樹(shù)索引是對(duì)搜索短語(yǔ)從頭到尾的一系列測(cè)試。反向索引采用不同的方法，它從單詞創(chuàng)建標(biāo)記。Token 可以是整個(gè)單詞或 n-gram（來(lái)自單詞的給定長(zhǎng)度的子串，對(duì)于Johnie3 個(gè)字母的 n-gram 是：joh, ohn, hni, nie）。
這允許以稍微不同的方式構(gòu)建索引。對(duì)于示例數(shù)據(jù)：
1 Paul 2 Roland 3 Carol
3 個(gè)字母的 n-gram 標(biāo)記的索引將如下所示：
pau => [p1r1] # that means this n-gram is at position 1 in row 1 aul => [p2r1] rol => [p1r2,p3r3] ola => [p2r2] lan => [p3r2] and => [p4r2] car => [p1r3] aro => [p2r3]
現(xiàn)在，如果我們查找，rol我們會(huì)立即知道此標(biāo)記存在于 rows2和中3。如果我們搜索更長(zhǎng)的短語(yǔ)，比如roland數(shù)據(jù)庫(kù)可能會(huì)使用這個(gè)索引兩次——如果rol在某個(gè)位置找到，那么and必須在 3 個(gè)字符之后找到。只有行2符合此條件。
在默認(rèn)解析器中使用反向索引
反向索引有它自己的語(yǔ)法，讓我們?cè)谖覀兊谋碇刑砑右粋€(gè)。
ALTER TABLE `address_book` ADD FULLTEXT (`name`), ADD FULLTEXT(`email`);
默認(rèn)分詞器使用詞邊界來(lái)查找分詞，這意味著一個(gè)連續(xù)的詞就是一個(gè)分詞。
要利用全文索引MATCH () AGAINST ()語(yǔ)法必須使用。AGAINSTsection 可以在NATURAL LANGUAGE MODE搜索文本也被標(biāo)記化的地方工作，或者在BOOLEAN包含它自己強(qiáng)大的迷你表達(dá)式語(yǔ)言的更有用的模式下工作。我不會(huì)深入探討BOOLEAN MODE語(yǔ)法，基本上是+指AND.
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+johnie +doemel" IN BOOLEAN MODE);
+--------+---------------+------------------------------+
| id | name | email|
+--------+---------------+------------------------------+
| 222698 | Johnie Doemel | doemel.36.johnie@example.com |
+--------+---------------+------------------------------+
1 row in set (0.001 sec)
> SELECT * FROM `address_book` WHERE MATCH (`email`) AGAINST ("+johnie +doemel" IN BOOLEAN MODE);
+--------+---------------+------------------------------+
| id | name | email|
+--------+---------------+------------------------------+
| 222698 | Johnie Doemel | doemel.36.johnie@example.com |
+--------+---------------+------------------------------+
1 row in set (0.001 sec)
哇，真快 比沒(méi)有索引的方法快 200 倍以上。我們并不局限于像在 B 樹(shù)索引中那樣從短語(yǔ)的開(kāi)頭進(jìn)行搜索，這意味著在電子郵件中搜索也可以快速進(jìn)行。我們的索引根據(jù) 過(guò)濾行EXPLAIN。
> EXPLAIN SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+johnie +doemel" IN BOOLEAN MODE)\G
id: 1
select_type: SIMPLE
table: address_book
partitions: NULL
type: fulltext
possible_keys: name
key: name
key_len: 0
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Ft_hints: no_ranking
生活是美好的。或者是嗎？
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
Empty set (0.002 sec)
第一個(gè)陷阱！您找不到比標(biāo)記長(zhǎng)度短的短語(yǔ)，默認(rèn)情況下整個(gè)單詞都是標(biāo)記。這是搜索速度和索引構(gòu)建/存儲(chǔ)成本之間的平衡。
$ time cat address_book.sql | mysql real 29m 34.44s # du -bc /var/lib/mysql/default/fts_* 492453888 total
那是 126% 的未索引加載時(shí)間，僅全文索引占用的時(shí)間是數(shù)據(jù)本身的 7 倍。請(qǐng)注意，沒(méi)有簡(jiǎn)單的方法可以從 中檢查全文索引大小INFORMATION_SCHEMA，它必須在 MySQL 服務(wù)器文件系統(tǒng)上完成。
用途： 當(dāng)您想按整個(gè)單詞進(jìn)行搜索時(shí)。布爾模式表達(dá)式允許執(zhí)行一些很酷的技巧，例如排除某些單詞或按相關(guān)性查找，您可能會(huì)發(fā)現(xiàn)這些技巧很有用。但是您必須愿意接受更高的寫(xiě)入時(shí)間和更高的存儲(chǔ)成本。
在 n-gram 解析器中使用反向索引
這次每個(gè)單詞將被拆分成 n-gram。n-gram 的默認(rèn)長(zhǎng)度在服務(wù)器配置變量中定義：
> show variables like "ngram_token_size"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | ngram_token_size | 2 | +------------------+-------+
索引創(chuàng)建語(yǔ)法必須明確定義分詞器（此處命名為“解析器”）。
ALTER TABLE `address_book` ADD FULLTEXT (`name`) WITH PARSER ngram, ADD FULLTEXT(`email`) WITH PARSER ngram;
這次按預(yù)期找到了行，即使在搜索中沒(méi)有使用整個(gè)單詞。
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
+--------+----------------+-------------------------------+
| id | name | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel | doemel.36.johnie@example.com |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (0.266 sec)
但是這種可怕的表現(xiàn)呢？這比沒(méi)有索引要慢！答案在于 n-gram 大小。如果匹配短語(yǔ)與 n-gram 大小不匹配，則數(shù)據(jù)庫(kù)必須查詢索引幾次并合并結(jié)果或進(jìn)行補(bǔ)充的非索引過(guò)濾。讓我們重新啟動(dòng)我們的服務(wù)器并--ngram_token_size=3重建表。
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
+--------+----------------+-------------------------------+
| id | name | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel | doemel.36.johnie@example.com |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (0.087 sec)
因此，在這種情況下doe，匹配的標(biāo)記大小和索引被直接使用，但john必須在該索引中間接找到。如果我們要求 ，這一點(diǎn)就更明顯了COUNT。
> SELECT COUNT(*) FROM `address_book` WHERE MATCH (`email`) AGAINST ("+john" IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 3563 |
+----------+
1 row in set (0.064 sec) # phrase longer than token
> SELECT COUNT(*) FROM `address_book` WHERE MATCH (`email`) AGAINST ("+doe" IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 431 |
+----------+
1 row in set (0.003 sec) # phrase equal to token
所以我們犧牲了使用索引按 2 個(gè)字符搜索的能力，在按 3 個(gè)字符搜索時(shí)獲得了很大的提升，在其他情況下獲得了平庸的提升。
使用這種方法是一堆權(quán)衡。不，您不能在同一字段上使用不同 n-gram 大小的索引來(lái)解決各種搜索短語(yǔ)長(zhǎng)度。更糟的是——配置變量是全局的，所以你甚至不能FULLTEXT在具有不同 n-gram 大小的不同表上有兩個(gè)索引。一個(gè)配置必須滿足您在服務(wù)器范圍內(nèi)的所有需求。
寫(xiě)入性能和存儲(chǔ)損失如何？
$ time cat address_book.sql | mysql real 26m 31.05s # du -bc /var/lib/mysql/default/fts_* 362430464 total
不幸的是它們很大，索引占用的空間是數(shù)據(jù)的 5 倍。
使用： 當(dāng)你想按部分單詞進(jìn)行搜索時(shí)。布爾模式表達(dá)式也適用于此。但首先，您必須找到令牌長(zhǎng)度在服務(wù)器范圍內(nèi)的正確平衡，并接受更高的寫(xiě)入時(shí)間和更高的存儲(chǔ)成本。長(zhǎng)度不同于標(biāo)記大小的短語(yǔ)仍然比未索引的方法更快，但沒(méi)有“哇”因素。
避免： 當(dāng)您的文本使用表意語(yǔ)言（如中文或日文）并且需要單字符標(biāo)記時(shí)。日語(yǔ)有單獨(dú)的 MeCab 分詞器，但這超出了本文的范圍。
InnoDB 反向索引性能下降
讓我們使用上一章的數(shù)據(jù)并刪除所有行。
> DELETE FROM `address_book`;
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
Empty set (0.233 sec)
那么對(duì)于有數(shù)據(jù)的表來(lái)說(shuō)時(shí)間是 0.087 秒，但現(xiàn)在對(duì)于空表??來(lái)說(shuō)是 0.233 秒？這是因?yàn)楫?dāng)從 InnoDB 表中刪除行時(shí)，它不會(huì)從 FULLTEXT 索引中刪除。相反，單獨(dú)的隱藏表跟蹤刪除的行，并且在過(guò)時(shí)的索引中搜索必須將 1_000_000 行的過(guò)時(shí)結(jié)果與已刪除的 1_000_000 行的列表進(jìn)行比較。這變得越來(lái)越糟。讓我們添加、刪除、添加、刪除和添加我們的數(shù)據(jù)。所以我們回到表中的 1_000_000 個(gè)原始行。與我們開(kāi)始時(shí)相同的行數(shù)。
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ("+john +doe" IN BOOLEAN MODE);
+--------+----------------+-------------------------------+
| id | name | email |
+--------+----------------+-------------------------------+
| 222698 | Johnie Doemel | doemel.36.johnie@example.com |
| 316137 | Johnnie Doepel | johnnie-doepel-72@example.com |
+--------+----------------+-------------------------------+
2 rows in set (7.038 sec)
這種情況迅速升級(jí)……現(xiàn)在是時(shí)候進(jìn)入非常迷幻的土地了。要重建 InnoDBFULLTEXT索引并恢復(fù)性能，您必須更改整個(gè)表。這需要大量的數(shù)據(jù)庫(kù)用戶權(quán)限，并且很可能導(dǎo)致應(yīng)用程序停機(jī)。但不要害怕。有全局innodb_optimize_fulltext_only=ON標(biāo)志，全局（！）更改ALTER/ OPTIMIZE（在 InnoDB 中，它們是同義詞）以僅從FULLTEXT索引中清除舊條目。您可以通過(guò)設(shè)置標(biāo)志來(lái)配置清除多少令牌innodb_ft_num_word_optimize，最大值為 10_000。如果你完成了，就沒(méi)有反饋。我再重復(fù)一次——如果你完成了沒(méi)有反饋，你應(yīng)該連續(xù)運(yùn)行ALTERs 希望在某個(gè)時(shí)候你的FULLTEXT索引沒(méi)有過(guò)時(shí)的條目。
那是垃圾UI設(shè)計(jì)。
治療比疾病更糟糕。MyISAMFULLTEXT即時(shí)清除索引，它不會(huì)降低數(shù)據(jù)保留。因此，您可能會(huì)將 InnoDB 表轉(zhuǎn)換為 MyISAM，從而丟失所有 InnoDB 好東西。或者您可以構(gòu)建補(bǔ)充 MyISAM 表，如address_book_fts，在那里有FULLTEXT索引并使用觸發(fā)器從 InnoDB 表同步數(shù)據(jù)。當(dāng)您認(rèn)為自己很厲害時(shí) - GTID 一致性就會(huì)發(fā)揮作用。如果您在復(fù)制中使用 GTID 事務(wù)標(biāo)識(shí)符，則無(wú)法在同一事務(wù)中更新 InnoDB 和 MyISAM 表，這意味著您必須冒在流程中自動(dòng)提交寫(xiě)入的風(fēng)險(xiǎn)。呸。
備選方案
我希望通過(guò)這篇文章您能更好地了解 MySQL 關(guān)于全文搜索的功能。有取舍，也有缺陷。如果您還沒(méi)有找到符合您需求的解決方案，我建議：
- 嘗試切換到 PostgreSQL。MySQL 中的全文搜索是一些奇怪的、未完成的拼湊而成。PostgreSQL 解決方案要好得多，也許我會(huì)寫(xiě)這篇文章的后續(xù)文章，但使用 Postgres。
- 使用MySQL，但使用Sphinx插件而不是內(nèi)置解決方案。
- 使用ElasticSearch
以上就是MySql中的Full Text Search全文索引優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于MySql全文索引優(yōu)化的資料請(qǐng)關(guān)注其它相關(guān)文章！
網(wǎng)公網(wǎng)安備