嘗試搜尋字元串/單詞的一部分時出現 MySQL 索引問題

我們遇到了什麼：

發動機：MySQL。

我們一直在開發一個過濾器，使用者可以在其中搜尋參考號。但是，我們在更大的數據集上遇到了性能問題。

我們需要搜尋部分單詞；假設我們有引用AB12345678- 使用者希望通過搜尋AB,B123或1234包含此字元串的一部分的任何其他組合來找到此引用。

我們有一個INDEX參考日期和日期

我們目前正在使用LIKE %STRING%，但這不能被索引 - 在某些情況下性能很差：

**(1)**當我們搜尋時，AB我們得到一個快速的結果。

**(2)**當我們搜尋時，AB12345678我們得到一個緩慢的結果。

兩種情況都有ORDER BY date& LIMIT。

當我們關閉ORDER BY或關閉LIMIT情況**(2)**時，我們也會得到一個快速的結果。

一個緩慢的結果是大約 14-15 秒的查詢執行時間。

該數據集包含大約 300k 個結果。

到目前為止，我們已經嘗試過：

我們嘗試實現FULLTEXT索引和MATCH .. AGAINST查詢，但是MATCH .. AGAINST不允許我們搜尋兩邊，*萬用字元只允許在輸入字元串的末尾。

我們還嘗試刪除INDEXon date。這給了我們更快的結果（大約 1/3 的時間）但是，查詢仍然需要大約 4-5 秒。

我們有點迷失在可以提高我們在此查詢上的性能以及我們現在如何最好地解決它的最佳實現上。我們應該使用什麼方法來恢復我們的性能並能夠在兩側進行搜尋？

下面是我們的查詢的一部分，它的結果很慢（我們刪除了這SELECT部分）：

SELECT SQL_NO_CACHE *
FROM orders o0_
WHERE 1=1
AND o0_.customer_id = 130
AND (o0_.reference LIKE '%AB12345678%')
ORDER BY date4 DESC
LIMIT 50
OFFSET 0;

如果您可以擺脫前導萬用字元（％），請這樣做；然後我們可以討論更好的索引。

同時，使用INDEX(customer_id, date4); 它有一些加快查詢速度的機會。

你執行的是什麼版本？

SHOW CREATE TABLE如果您想進一步討論，請提供。

三元索引是索引部分字元串的一種解決方案，但是您必須自己實現很多程式碼。MySQL 對此沒有內置支持。

PostgreSQL 在擴展中支持三元組索引。參見例如https://www.dbrnd.com/2016/08/postgresql-example-of-trigram-index-for-full-text-search-using-pg_trgm-extension/。但我不知道是否支持擴展。一些超過五年或更長時間的關於擴展功能的部落格通常最終被廢棄。當然，我了解您正在使用 MySQL，因此您要麼需要自己為 MySQL 開發一個等效的擴展，要麼將您的數據庫切換到 PostgreSQL，這是一個挑戰。

除此之外，我只能建議退後一步，重新考慮如何儲存和搜尋數據。這可能需要進行重大的重新思考。

引用自：https://dba.stackexchange.com/questions/129816