MySql中的Full Text Search全文索引優化
開篇
在我們的生產環境中,有一個模糊檢索的文檔框,但是當數據量級別上去之後,頻繁對數據庫造成壓力,所以想使用Full Text全文索引進行優化 下面是一個總結的簡單案例
一個簡單的DEMO
假設我們有客戶的地址簿,目標是通過他/她的姓名或電子郵件快速找到人。
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 個隨機生成的人填充地址簿。每個人將被插入單獨的查詢中。姓名將始終采用整齊的形式 – 名字和姓氏。電子郵件會更加混亂——名字/姓氏的順序和存在不同,分隔符不同,並且有一些隨機數。
> SELECT `name`, `email` FROM `addressbook` LIMIT 8; +--------------------+---------------------------------+ | name | email | +--------------------+---------------------------------+ | Reed Slavik | [email protected] | | Reilly Isaacson | [email protected] | | Theodore Klosinski | [email protected] | | Duncan Sinke | [email protected] | | Maranda Cabrara | [email protected] | | Hugh Harrop | [email protected] | | Bernard Luetzow | [email protected] | | Niki Manesis | [email protected] | +--------------------+---------------------------------+
測試將在具有默認配置的庫存 MySQL 8.0.32 Docker 映像上執行(除非另有說明)。硬件是 AMD 6800U、32GB RAM、PCIe NVMe 4.0 x4 SSD。操作系統是帶有 BTRFS 和 LUKS 磁盤加密的 vanilla Arch Linux。
天下沒有免費的午餐
天下沒有免費的午餐。索引加快SELECT
但減慢INSERT
//語句,因為計算的額外 CPU 成本以及額外的磁盤傳輸和存儲空間成本UPDATE
。DELETE
我會嘗試寫簡短的總結何時使用每種方法,有什麼好處和缺點。
無索引
最簡單的方法是沒有索引列並使用LIKE '%john%'
語法。
因為沒有索引維護這種方法不會增加數據加載時間和存儲空間。
$ 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 | +-------------+--------------+
性能很差。當沒有使用索引時,MySQL 使用 Turbo Boyer-Moore 算法 來查找匹配的行。
> SELECT * FROM `address_book` WHERE `name` LIKE '%john%' AND `name` LIKE '%doe%'; +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | [email protected] | | 316137 | Johnnie Doepel | [email protected] | +--------+----------------+-------------------------------+ 2 rows in set (0.222 sec)
如查詢所示,所有行都需要從磁盤中提取以進行分析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
使用: 當您的應用程序很少進行全文搜索並且您願意接受低查詢性能時。在小數據集上效果很好。簡單的實施是巨大的好處。
避免: 當頻繁使用全文搜索時——你會在這裡消耗大量的數據庫性能,尤其是在大數據集上。此外,由於全行掃描,它可能會阻止應用程序中需要FOR UPDATE
鎖定此類表的其他查詢。
使用 B 樹索引
不幸的是,在一個字段上打一個索引並稱之為一天是行不通的。在 B 樹索引中,文本從搜索短語的開始到結束被轉換為一系列二元(真/假)測試樹。對於示例數據:
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
你經過no
路徑。然後你測試前兩個字符。因為jo=jo
你從短語中刪除它們並通過yes
路徑。然後你測試下一個不匹配的字符是h
……你繼續執行這些系列的測試,直到你最終到達包含你正在尋找的短語的行列表,在這種情況下是2
和3
。但這表明這種類型的索引必須從短語的開始到結束起作用,這意味著短語不能以通配符開頭。
讓我們把它添加到我們的表中。
> ALTER TABLE `address_book` ADD KEY (`name`), ADD KEY (`email`);
如您所見,當搜索的短語以通配符索引開頭時將不會被使用。
> 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
如果您知道文本具有某種特定結構(在我們的例子中,名稱在前),我們可以利用這些知識並在不使用通配符的情況下詢問名稱。
> SELECT * FROM `address_book` WHERE `name` LIKE 'john%' AND `name` LIKE '%doe%'; +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | [email protected] | | 316137 | Johnnie Doepel | [email protected] | +--------+----------------+-------------------------------+ 2 rows in set (0.003 sec)
Explain 顯示這次使用瞭索引,所有以 開頭的名稱john
都在索引中找到,並且 Boyer-Moore 必須僅用於針對 對該集合進行精細過濾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
當涉及到電子郵件時,這種方法很快就會顯示出局限性。它太混亂瞭——可能以名字開頭,可能以姓氏開頭,甚至可能以完全不同的東西開頭。在這種情況下,查詢時間就像沒有索引的情況一樣。
> SELECT * FROM `address_book` WHERE `email` LIKE '%john%' AND `email` LIKE '%doe%'; +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | [email protected] | | 316137 | Johnnie Doepel | [email protected] | +--------+----------------+-------------------------------+ 2 rows in set (0.314 sec)
在性能方面,它會稍微減慢數據加載速度並使存儲空間增加一倍,但並不是很有用。
$ 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 | +-------------+--------------+
使用: 當您可以將文本拆分為具有自己索引的明確定義的列時。例如重組表以單獨first_name
存儲last_name
。此外,您必須願意犧牲起始通配符。
避免: 當文本太不可預測和無序時,例如email
您name
商店中的各種產品。
註意:從右到左的語言也不例外,搜索的詞組不能以通配符開頭,無論文字的方向是什麼。
引入反向索引
首先讓我們解釋一下什麼是反向索引。B樹索引是對搜索短語從頭到尾的一系列測試。反向索引采用不同的方法,它從單詞創建標記。Token 可以是整個單詞或 n-gram(來自單詞的給定長度的子串,對於Johnie
3 個字母的 n-gram 是:joh
, ohn
, hni
, nie
)。
這允許以稍微不同的方式構建索引。對於示例數據:
1 Paul 2 Roland 3 Carol
3 個字母的 n-gram 標記的索引將如下所示:
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]
現在,如果我們查找,rol
我們會立即知道此標記存在於 rows2
和中3
。如果我們搜索更長的短語,比如roland
數據庫可能會使用這個索引兩次——如果rol
在某個位置找到,那麼and
必須在 3 個字符之後找到。隻有行2
符合此條件。
在默認解析器中使用反向索引
反向索引有它自己的語法,讓我們在我們的表中添加一個。
ALTER TABLE `address_book` ADD FULLTEXT (`name`), ADD FULLTEXT(`email`);
默認分詞器使用詞邊界來查找分詞,這意味著一個連續的詞就是一個分詞。
要利用全文索引MATCH () AGAINST ()
語法必須使用。AGAINST
section 可以在NATURAL LANGUAGE MODE
搜索文本也被標記化的地方工作,或者在BOOLEAN
包含它自己強大的迷你表達式語言的更有用的模式下工作。我不會深入探討BOOLEAN MODE
語法,基本上是+
指AND
.
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+johnie +doemel' IN BOOLEAN MODE); +--------+---------------+------------------------------+ | id | name | email | +--------+---------------+------------------------------+ | 222698 | Johnie Doemel | [email protected] | +--------+---------------+------------------------------+ 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 | [email protected] | +--------+---------------+------------------------------+ 1 row in set (0.001 sec)
哇,真快 比沒有索引的方法快 200 倍以上。我們並不局限於像在 B 樹索引中那樣從短語的開頭進行搜索,這意味著在電子郵件中搜索也可以快速進行。我們的索引根據 過濾行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)
第一個陷阱!您找不到比標記長度短的短語,默認情況下整個單詞都是標記。這是搜索速度和索引構建/存儲成本之間的平衡。
$ time cat address_book.sql | mysql real 29m 34.44s # du -bc /var/lib/mysql/default/fts_* 492453888 total
那是 126% 的未索引加載時間,僅全文索引占用的時間是數據本身的 7 倍。請註意,沒有簡單的方法可以從 中檢查全文索引大小INFORMATION_SCHEMA
,它必須在 MySQL 服務器文件系統上完成。
用途: 當您想按整個單詞進行搜索時。佈爾模式表達式允許執行一些很酷的技巧,例如排除某些單詞或按相關性查找,您可能會發現這些技巧很有用。但是您必須願意接受更高的寫入時間和更高的存儲成本。
在 n-gram 解析器中使用反向索引
這次每個單詞將被拆分成 n-gram。n-gram 的默認長度在服務器配置變量中定義:
> show variables like 'ngram_token_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | ngram_token_size | 2 | +------------------+-------+
索引創建語法必須明確定義分詞器(此處命名為“解析器”)。
ALTER TABLE `address_book` ADD FULLTEXT (`name`) WITH PARSER ngram, ADD FULLTEXT(`email`) WITH PARSER ngram;
這次按預期找到瞭行,即使在搜索中沒有使用整個單詞。
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE); +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | [email protected] | | 316137 | Johnnie Doepel | [email protected] | +--------+----------------+-------------------------------+ 2 rows in set (0.266 sec)
但是這種可怕的表現呢?這比沒有索引要慢!答案在於 n-gram 大小。如果匹配短語與 n-gram 大小不匹配,則數據庫必須查詢索引幾次並合並結果或進行補充的非索引過濾。讓我們重新啟動我們的服務器並--ngram_token_size=3
重建表。
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE); +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | [email protected] | | 316137 | Johnnie Doepel | [email protected] | +--------+----------------+-------------------------------+ 2 rows in set (0.087 sec)
因此,在這種情況下doe
,匹配的標記大小和索引被直接使用,但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 個字符搜索時獲得瞭很大的提升,在其他情況下獲得瞭平庸的提升。
使用這種方法是一堆權衡。不,您不能在同一字段上使用不同 n-gram 大小的索引來解決各種搜索短語長度。更糟的是——配置變量是全局的,所以你甚至不能FULLTEXT
在具有不同 n-gram 大小的不同表上有兩個索引。一個配置必須滿足您在服務器范圍內的所有需求。
寫入性能和存儲損失如何?
$ time cat address_book.sql | mysql real 26m 31.05s # du -bc /var/lib/mysql/default/fts_* 362430464 total
不幸的是它們很大,索引占用的空間是數據的 5 倍。
使用: 當你想按部分單詞進行搜索時。佈爾模式表達式也適用於此。但首先,您必須找到令牌長度在服務器范圍內的正確平衡,並接受更高的寫入時間和更高的存儲成本。長度不同於標記大小的短語仍然比未索引的方法更快,但沒有“哇”因素。
避免: 當您的文本使用表意語言(如中文或日文)並且需要單字符標記時。日語有單獨的 MeCab 分詞器,但這超出瞭本文的范圍。
InnoDB 反向索引性能下降
讓我們使用上一章的數據並刪除所有行。
> DELETE FROM `address_book`; > SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE); Empty set (0.233 sec)
那麼對於有數據的表來說時間是 0.087 秒,但現在對於空表來說是 0.233 秒?這是因為當從 InnoDB 表中刪除行時,它不會從 FULLTEXT 索引中刪除。相反,單獨的隱藏表跟蹤刪除的行,並且在過時的索引中搜索必須將 1_000_000 行的過時結果與已刪除的 1_000_000 行的列表進行比較。這變得越來越糟。讓我們添加、刪除、添加、刪除和添加我們的數據。所以我們回到表中的 1_000_000 個原始行。與我們開始時相同的行數。
> SELECT * FROM `address_book` WHERE MATCH (`name`) AGAINST ('+john +doe' IN BOOLEAN MODE); +--------+----------------+-------------------------------+ | id | name | email | +--------+----------------+-------------------------------+ | 222698 | Johnie Doemel | [email protected] | | 316137 | Johnnie Doepel | [email protected] | +--------+----------------+-------------------------------+ 2 rows in set (7.038 sec)
這種情況迅速升級……現在是時候進入非常迷幻的土地瞭。要重建 InnoDBFULLTEXT
索引並恢復性能,您必須更改整個表。這需要大量的數據庫用戶權限,並且很可能導致應用程序停機。但不要害怕。有全局innodb_optimize_fulltext_only=ON
標志,全局(!)更改ALTER
/ OPTIMIZE
(在 InnoDB 中,它們是同義詞)以僅從FULLTEXT
索引中清除舊條目。您可以通過設置標志來配置清除多少令牌innodb_ft_num_word_optimize
,最大值為 10_000。如果你完成瞭,就沒有反饋。我再重復一次——如果你完成瞭沒有反饋,你應該連續運行ALTER
s 希望在某個時候你的FULLTEXT
索引沒有過時的條目。
那是垃圾UI設計。
治療比疾病更糟糕。MyISAMFULLTEXT
即時清除索引,它不會降低數據保留。因此,您可能會將 InnoDB 表轉換為 MyISAM,從而丟失所有 InnoDB 好東西。或者您可以構建補充 MyISAM 表,如address_book_fts
,在那裡有FULLTEXT
索引並使用觸發器從 InnoDB 表同步數據。當您認為自己很厲害時 – GTID 一致性就會發揮作用。如果您在復制中使用 GTID 事務標識符,則無法在同一事務中更新 InnoDB 和 MyISAM 表,這意味著您必須冒在流程中自動提交寫入的風險。呸。
備選方案
我希望通過這篇文章您能更好地瞭解 MySQL 關於全文搜索的功能。有取舍,也有缺陷。如果您還沒有找到符合您需求的解決方案,我建議:
- 嘗試切換到 PostgreSQL。MySQL 中的全文搜索是一些奇怪的、未完成的拼湊而成。PostgreSQL 解決方案要好得多,也許我會寫這篇文章的後續文章,但使用 Postgres。
- 使用MySQL,但使用Sphinx插件而不是內置解決方案。
- 使用ElasticSearch
以上就是MySql中的Full Text Search全文索引優化的詳細內容,更多關於MySql全文索引優化的資料請關註WalkonNet其它相關文章!