Mysql

如何加快 400 萬行表的全文查詢?(瑪麗亞數據庫)

  • March 7, 2022

我有一個 InnoDB 全文表,它為我網站頂部的 Ajax 驅動的搜尋框提供服務。我使用每日腳本生成它,該腳本從網站上的十幾個實體表中提取數據,並將它們全部合併到一個 FT 表中進行搜尋。

為了給使用者最好的體驗(恕我直言),無論他們的輸入是什麼,我都會清除其中的某些字元(例如,所有全文修飾符),然後在每個術語前面加上 + 並用 * 附加它們。

所以搜尋“堆棧溢出”變成了 +stack* +overflow*

我在 FT 表中搜尋的列很小,典型字元長度為 30 個字元。事件名稱,人名,地理位置,諸如此類。不是大段的文字。

它可以工作,但查詢需要 1 秒的時間才能返回。

編輯:就在發布後,我重建了索引,現在它下降到 0.4 秒 - 但如果可能的話,我仍然想改進它。

我怎麼能把它改成 0.1 秒,或者那是白日夢?

我的伺服器是雙 Xeon,具有 16 核/32 執行緒和 128GB 記憶體。我每個月服務一百萬頁左右,很少看到伺服器負載超過 1-2,並且有足夠的空閒記憶體。

我想知道我是否可以以某種方式強制此表永久駐留在記憶體中(在伺服器重新啟動或 MySQL 重新啟動後重建它只需要 30 秒左右),是否有幫助?或者也許 MySQL 已經將它保存在記憶體中 - 我該如何檢查?

我對查詢本身很滿意,我認為我沒有太多可以改進的地方,但我對如何通過配置最大限度地發揮伺服器潛力知之甚少。

FWIWSELECT VERSION()給我10.3.20-MariaDB-log

數據集有多大? SHOW TABLE STATUS.

聽起來它比 小得多innodb_buffer_pool_size,因此一旦載入,它可能會駐留在 RAM 中。

沒有自動載入它的方法。您可以編寫一個腳本來訪問數據中的每一行和每個索引。但這既棘手又緩慢。

另一方面,您可以通過轉動ON這些: innodb_buffer_pool_dump_at_shutdowninnodb_buffer_pool_load_at_shutdown.

我想知道我是否可以以某種方式強制此表永久駐留在記憶體中(在伺服器重新啟動或 mysql 重新啟動後重建它只需要 30 秒左右),如果這有幫助嗎?或者也許 MySQL 已經將它保存在記憶體中 - 我該如何檢查?

我相信有一種簡單的方法 - hack - 假設你有足夠大的innodb_buffer_pool_size系統變數,它可以讓你將整個表載入到記憶體中,即:只需使用備份工具將表轉儲到文件中mysqldump(但是不是mariabackupxtrabackup),所有的表數據將被載入到記憶體中。您不必將其轉儲到實際文件中 - /dev/null 就足夠了:

mysqldump the_database the_table > /dev/null

至於驗證整個表是否載入到記憶體中,我不確定是否只針對一張表,但您可以計算所有 InnoDB 表的總 InnoDB 緩衝池命中率。首先獲取這些值:

SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';

然後 InnoDB 緩衝池命中百分比由下式給出: Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

根據這篇 FromDual 文章

InnoDB 緩衝池命中率低於 99.9% 是一個弱指標,表明您的 InnoDB 緩衝池可以增加。

引用自:https://dba.stackexchange.com/questions/255182