MySql中的Full?Text?Search全文索引優(yōu)化

更新時間：2023年05月11日 15:22:29 作者：飽飽巴士

這篇文章主要為大家介紹了MySql中的Full?Text?Search全文索引優(yōu)化示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

開篇

在我們的生產(chǎn)環(huán)境中,有一個模糊檢索的文檔框,但是當(dāng)數(shù)據(jù)量級別上去之后,頻繁對數(shù)據(jù)庫造成壓力,所以想使用Full Text全文索引進(jìn)行優(yōu)化下面是一個總結(jié)的簡單案例

一個簡單的DEMO

假設(shè)我們有客戶的地址簿，目標(biāo)是通過他/她的姓名或電子郵件快速找到人。

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 個隨機(jī)生成的人填充地址簿。每個人將被插入單獨的查詢中。姓名將始終采用整齊的形式 - 名字和姓氏。電子郵件會更加混亂——名字/姓氏的順序和存在不同，分隔符不同，并且有一些隨機(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            |
+--------------------+---------------------------------+

測試將在具有默認(rèn)配置的庫存 MySQL 8.0.32 Docker 映像上執(zhí)行（除非另有說明）。硬件是 AMD 6800U、32GB RAM、PCIe NVMe 4.0 x4 SSD。操作系統(tǒng)是帶有 BTRFS 和 LUKS 磁盤加密的 vanilla Arch Linux。

天下沒有免費的午餐

天下沒有免費的午餐。索引加快SELECT但減慢INSERT//語句，因為計算的額外 CPU 成本以及額外的磁盤傳輸和存儲空間成本UPDATE。DELETE我會嘗試寫簡短的總結(jié)何時使用每種方法，有什么好處和缺點。

無索引

最簡單的方法是沒有索引列并使用LIKE '%john%'語法。

因為沒有索引維護(hù)這種方法不會增加數(shù)據(jù)加載時間和存儲空間。

$ 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)沒有使用索引時，MySQL 使用 Turbo Boyer-Moore 算法來查找匹配的行。

> 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)

如查詢所示，所有行都需要從磁盤中提取以進(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ù)據(jù)集上效果很好。簡單的實施是巨大的好處。

避免： 當(dāng)頻繁??使用全文搜索時——你會在這里消耗大量的數(shù)據(jù)庫性能，尤其是在大數(shù)據(jù)集上。此外，由于全行掃描，它可能會阻止應(yīng)用程序中需要FOR UPDATE鎖定此類表的其他查詢。

使用 B 樹索引

不幸的是，在一個字段上打一個索引并稱之為一天是行不通的。在 B 樹索引中，文本從搜索短語的開始到結(jié)束被轉(zhuǎn)換為一系列二元（真/假）測試樹。對于示例數(shù)據(jù)：

1 John
2 Joseph
3 Joseph
4 Ann

它看起來像這樣。

                   <="a"?
                    /  \
                  yes   no
                  /       \
             <="nn"?     <="jo"
               /          /
             yes        yes
             /          /
           [4]      <="h"?
                     /  \
                   yes   no
                   /      \
                <="n"?    <="seph"?
                 /          /
               yes        yes
               /          /
             [1]        [2,3]

如果你正在尋找Joseph你測試第一個字符。因為j>a你經(jīng)過no路徑。然后你測試前兩個字符。因為jo=jo你從短語中刪除它們并通過yes路徑。然后你測試下一個不匹配的字符是h......你繼續(xù)執(zhí)行這些系列的測試，直到你最終到達(dá)包含你正在尋找的短語的行列表，在這種情況下是2和3。但這表明這種類型的索引必須從短語的開始到結(jié)束起作用，這意味著短語不能以通配符開頭。

讓我們把它添加到我們的表中。

> ALTER TABLE `address_book` ADD KEY (`name`), ADD KEY (`email`);

如您所見，當(dāng)搜索的短語以通配符索引開頭時將不會被使用。

> 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)（在我們的例子中，名稱在前），我們可以利用這些知識并在不使用通配符的情況下詢問名稱。

> 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 顯示這次使用了索引，所有以開頭的名稱john都在索引中找到，并且 Boyer-Moore 必須僅用于針對對該集合進(jìn)行精細(xì)過濾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)涉及到電子郵件時，這種方法很快就會顯示出局限性。它太混亂了——可能以名字開頭，可能以姓氏開頭，甚至可能以完全不同的東西開頭。在這種情況下，查詢時間就像沒有索引的情況一樣。

> 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)

在性能方面，它會稍微減慢數(shù)據(jù)加載速度并使存儲空間增加一倍，但并不是很有用。

$ 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)您可以將文本拆分為具有自己索引的明確定義的列時。例如重組表以單獨first_name存儲last_name。此外，您必須愿意犧牲起始通配符。

避免： 當(dāng)文本太不可預(yù)測和無序時，例如email您name商店中的各種產(chǎn)品。

注意：從右到左的語言也不例外，搜索的詞組不能以通配符開頭，無論文字的方向是什么。

引入反向索引

首先讓我們解釋一下什么是反向索引。B樹索引是對搜索短語從頭到尾的一系列測試。反向索引采用不同的方法，它從單詞創(chuàng)建標(biāo)記。Token 可以是整個單詞或 n-gram（來自單詞的給定長度的子串，對于Johnie3 個字母的 n-gram 是：joh, ohn, hni, nie）。

這允許以稍微不同的方式構(gòu)建索引。對于示例數(shù)據(jù)：

1 Paul
2 Roland
3 Carol

3 個字母的 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我們會立即知道此標(biāo)記存在于 rows2和中3。如果我們搜索更長的短語，比如roland數(shù)據(jù)庫可能會使用這個索引兩次——如果rol在某個位置找到，那么and必須在 3 個字符之后找到。只有行2符合此條件。

在默認(rèn)解析器中使用反向索引

反向索引有它自己的語法，讓我們在我們的表中添加一個。

ALTER TABLE `address_book` ADD FULLTEXT (`name`), ADD FULLTEXT(`email`);

默認(rèn)分詞器使用詞邊界來查找分詞，這意味著一個連續(xù)的詞就是一個分詞。

要利用全文索引MATCH () AGAINST ()語法必須使用。AGAINSTsection 可以在NATURAL LANGUAGE MODE搜索文本也被標(biāo)記化的地方工作，或者在BOOLEAN包含它自己強(qiáng)大的迷你表達(dá)式語言的更有用的模式下工作。我不會深入探討BOOLEAN MODE語法，基本上是+指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)

哇，真快比沒有索引的方法快 200 倍以上。我們并不局限于像在 B 樹索引中那樣從短語的開頭進(jìn)行搜索，這意味著在電子郵件中搜索也可以快速進(jìn)行。我們的索引根據(jù) 過濾行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)

第一個陷阱！您找不到比標(biāo)記長度短的短語，默認(rèn)情況下整個單詞都是標(biāo)記。這是搜索速度和索引構(gòu)建/存儲成本之間的平衡。

$ time cat address_book.sql | mysql
real    29m 34.44s
# du -bc /var/lib/mysql/default/fts_*
492453888       total

那是 126% 的未索引加載時間，僅全文索引占用的時間是數(shù)據(jù)本身的 7 倍。請注意，沒有簡單的方法可以從中檢查全文索引大小INFORMATION_SCHEMA，它必須在 MySQL 服務(wù)器文件系統(tǒng)上完成。

用途： 當(dāng)您想按整個單詞進(jìn)行搜索時。布爾模式表達(dá)式允許執(zhí)行一些很酷的技巧，例如排除某些單詞或按相關(guān)性查找，您可能會發(fā)現(xiàn)這些技巧很有用。但是您必須愿意接受更高的寫入時間和更高的存儲成本。

在 n-gram 解析器中使用反向索引

這次每個單詞將被拆分成 n-gram。n-gram 的默認(rèn)長度在服務(wù)器配置變量中定義：

> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

索引創(chuàng)建語法必須明確定義分詞器（此處命名為“解析器”）。

ALTER TABLE `address_book` ADD FULLTEXT (`name`) WITH PARSER ngram, ADD FULLTEXT(`email`) WITH PARSER ngram;

這次按預(yù)期找到了行，即使在搜索中沒有使用整個單詞。

> 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)呢？這比沒有索引要慢！答案在于 n-gram 大小。如果匹配短語與 n-gram 大小不匹配，則數(shù)據(jù)庫必須查詢索引幾次并合并結(jié)果或進(jìn)行補充的非索引過濾。讓我們重新啟動我們的服務(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必須在該索引中間接找到。如果我們要求，這一點就更明顯了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 個字符搜索的能力，在按 3 個字符搜索時獲得了很大的提升，在其他情況下獲得了平庸的提升。

使用這種方法是一堆權(quán)衡。不，您不能在同一字段上使用不同 n-gram 大小的索引來解決各種搜索短語長度。更糟的是——配置變量是全局的，所以你甚至不能FULLTEXT在具有不同 n-gram 大小的不同表上有兩個索引。一個配置必須滿足您在服務(wù)器范圍內(nèi)的所有需求。

寫入性能和存儲損失如何？

$ 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)行搜索時。布爾模式表達(dá)式也適用于此。但首先，您必須找到令牌長度在服務(wù)器范圍內(nèi)的正確平衡，并接受更高的寫入時間和更高的存儲成本。長度不同于標(biāo)記大小的短語仍然比未索引的方法更快，但沒有“哇”因素。

避免： 當(dāng)您的文本使用表意語言（如中文或日文）并且需要單字符標(biāo)記時。日語有單獨的 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)

那么對于有數(shù)據(jù)的表來說時間是 0.087 秒，但現(xiàn)在對于空表??來說是 0.233 秒？這是因為當(dāng)從 InnoDB 表中刪除行時，它不會從 FULLTEXT 索引中刪除。相反，單獨的隱藏表跟蹤刪除的行，并且在過時的索引中搜索必須將 1_000_000 行的過時結(jié)果與已刪除的 1_000_000 行的列表進(jìn)行比較。這變得越來越糟。讓我們添加、刪除、添加、刪除和添加我們的數(shù)據(jù)。所以我們回到表中的 1_000_000 個原始行。與我們開始時相同的行數(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)

這種情況迅速升級……現(xiàn)在是時候進(jìn)入非常迷幻的土地了。要重建 InnoDBFULLTEXT索引并恢復(fù)性能，您必須更改整個表。這需要大量的數(shù)據(jù)庫用戶權(quán)限，并且很可能導(dǎo)致應(yīng)用程序停機(jī)。但不要害怕。有全局innodb_optimize_fulltext_only=ON標(biāo)志，全局（?。└?code>ALTER/ OPTIMIZE（在 InnoDB 中，它們是同義詞）以僅從FULLTEXT索引中清除舊條目。您可以通過設(shè)置標(biāo)志來配置清除多少令牌innodb_ft_num_word_optimize，最大值為 10_000。如果你完成了，就沒有反饋。我再重復(fù)一次——如果你完成了沒有反饋，你應(yīng)該連續(xù)運行ALTERs 希望在某個時候你的FULLTEXT索引沒有過時的條目。

那是垃圾UI設(shè)計。

治療比疾病更糟糕。MyISAMFULLTEXT即時清除索引，它不會降低數(shù)據(jù)保留。因此，您可能會將 InnoDB 表轉(zhuǎn)換為 MyISAM，從而丟失所有 InnoDB 好東西。或者您可以構(gòu)建補充 MyISAM 表，如address_book_fts，在那里有FULLTEXT索引并使用觸發(fā)器從 InnoDB 表同步數(shù)據(jù)。當(dāng)您認(rèn)為自己很厲害時 - GTID 一致性就會發(fā)揮作用。如果您在復(fù)制中使用 GTID 事務(wù)標(biāo)識符，則無法在同一事務(wù)中更新 InnoDB 和 MyISAM 表，這意味著您必須冒在流程中自動提交寫入的風(fēng)險。呸。