如何加快 400 萬行表的全文查詢?(瑪麗亞數據庫)
我有一個 InnoDB 全文表,它為我網站頂部的 Ajax 驅動的搜尋框提供服務。我使用每日腳本生成它,該腳本從網站上的十幾個實體表中提取數據,並將它們全部合併到一個 FT 表中進行搜尋。
為了給使用者最好的體驗(恕我直言),無論他們的輸入是什麼,我都會清除其中的某些字元(例如,所有全文修飾符),然後在每個術語前面加上 + 並用 * 附加它們。
所以搜尋“堆棧溢出”變成了 +stack* +overflow*
我在 FT 表中搜尋的列很小,典型字元長度為 30 個字元。事件名稱,人名,地理位置,諸如此類。不是大段的文字。
它可以工作,但查詢需要 1 秒的時間才能返回。
編輯:就在發布後,我重建了索引,現在它下降到 0.4 秒 - 但如果可能的話,我仍然想改進它。
我怎麼能把它改成 0.1 秒,或者那是白日夢?
我的伺服器是雙 Xeon,具有 16 核/32 執行緒和 128GB 記憶體。我每個月服務一百萬頁左右,很少看到伺服器負載超過 1-2,並且有足夠的空閒記憶體。
我想知道我是否可以以某種方式強制此表永久駐留在記憶體中(在伺服器重新啟動或 MySQL 重新啟動後重建它只需要 30 秒左右),是否有幫助?或者也許 MySQL 已經將它保存在記憶體中 - 我該如何檢查?
我對查詢本身很滿意,我認為我沒有太多可以改進的地方,但我對如何通過配置最大限度地發揮伺服器潛力知之甚少。
FWIW
SELECT VERSION()
給我10.3.20-MariaDB-log
。
數據集有多大?
SHOW TABLE STATUS
.聽起來它比 小得多
innodb_buffer_pool_size
,因此一旦載入,它可能會駐留在 RAM 中。沒有自動載入它的方法。您可以編寫一個腳本來訪問數據中的每一行和每個索引。但這既棘手又緩慢。
另一方面,您可以通過轉動
ON
這些:innodb_buffer_pool_dump_at_shutdown
和innodb_buffer_pool_load_at_shutdown
.
我想知道我是否可以以某種方式強制此表永久駐留在記憶體中(在伺服器重新啟動或 mysql 重新啟動後重建它只需要 30 秒左右),如果這有幫助嗎?或者也許 MySQL 已經將它保存在記憶體中 - 我該如何檢查?
我相信有一種簡單的方法 - hack - 假設你有足夠大的
innodb_buffer_pool_size
系統變數,它可以讓你將整個表載入到記憶體中,即:只需使用備份工具將表轉儲到文件中mysqldump
(但是不是mariabackup
或xtrabackup
),所有的表數據將被載入到記憶體中。您不必將其轉儲到實際文件中 - /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
InnoDB 緩衝池命中率低於 99.9% 是一個弱指標,表明您的 InnoDB 緩衝池可以增加。