優化對字元串中多個術語中的任何一個的查詢

我一直在思考什麼是搜尋非結構化文本欄位中包含的多個 url 中的任何一個的最有效方法。

假設我想查找包含https://stackexchange.com/tour或 https://stackoverflow.com/tour的文章

顯而易見的查詢是做

WHERE body LIKE '%https://stackexchange.com/tour%' OR body LIKE '%https://stackoverflow.com/tour%';

但是，我想知道這是否會導致對值進行兩次完整搜尋時效率低下。

在 MySQL 系列上，我們可以選擇使用 REGEXP 來確保一次通過檢查：

WHERE body REGEXP 'https://stack(exchange|overflow)\.com/tour';

但是匹配正則表達式的成本可能（遠）高於兩個簡單的字元串搜尋，因此會適得其反。

還有更多選項，例如使用INSTR()/LOCATE()而不是LIKE，但它們仍然必須是OR -ed。

執行此類查詢的更有效方法是哪種？優化器會將多個 s 折疊LIKE成一個“動作”嗎？

請注意，在許多情況下，FULLTEXT 索引將是首選解決方案

WHERE MATCH (body) AGAINST ('https://stackexchange.com/tour https://stackoverflow.com/tour' IN BOOLEAN MODE);

但在這裡不適合，因為 url 被解釋為多個單詞。引用它們會有所幫助，但仍會返回非 URL：

WHERE MATCH (body) AGAINST ('"https://stackexchange.com/tour" "https://stackoverflow.com/tour"' IN BOOLEAN MODE);

（儘管全文索引可以與其他方法結合起來過濾搜尋結果）

可以玩的玩具桌

CREATE TABLE post (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  body TEXT,
  FULLTEXT (body)
)  ENGINE=InnoDB;

INSERT INTO post (body) VALUES ('See https://stackexchange.com/tour for the kind of questions we like'), ('Look at https://stackoverflow.com/tour'), ('I have read https://stackexchange.com/tour'), ('https://example.com'), ('The HTTPS stackexchange.com tour explains all about TLS');

如果不能使用全文索引，則無法優化子字元串搜尋。

無論您是否執行LIKE上述評論，REGEXP所有這些搜尋都將執行表掃描。LOCATE()``INSTR()

因此，使其具有更好性能的唯一方法是首先刪除大部分數據，這樣表就可以掃描更少的行。

另一種方法是使用反向索引。也就是說，預先掃描您的文本並查找您打算搜尋的關鍵詞。用關鍵詞填充另一個表，第三個多對多表將關鍵詞映射到它們出現的文本文件。

CREATE TABLE KeywordInPost (
 keyword_id INT NOT NULL,
 post_id INT NOT NULL,
 PRIMARY KEY (keyword_id, post_id),
 FOREIGN KEY (keyword_id) REFERENCES Keywords(keyword_id),
 FOREIGN KEY (post_id) REFERENCES post(id)
);

然後可以查詢：

SELECT post.*
FROM Keywords
JOIN KeywordInPost ON Keywords.keyword_id = KeywordInPost.keyword_id
JOIN post ON KeywordInPost.post_id = post.id
WHERE Keywords.word = '<word you want>';

至少這樣您就不必進行子字元串搜尋。您可以將所需的任何字元串儲存在“關鍵字”表中並使用 搜尋它=。然後在連接中使用有效的索引查找來查找出現該關鍵字的文章。

當然，您可以填充 KeywordInPost 表並使其保持最新狀態。

你的問題讀起來像是一個問題和一個自我答案的組合。你的結論隱藏在中間：“全文索引可以結合其他方法來過濾搜尋結果”這避免了比爾所說的觀點：“所有這些搜尋

$$ except the combined $$將進行表掃描”。 我相信這是一個罕見的情況，條款的順序WHERE很重要，我們可以利用它。（或者也許優化器會先做 FULLTEXT？）

WHERE MATCH (...) AGAINST ('...' IN BOOLEAN MODE)  -- first
 AND ... REGEXP ...   -- second

這使用 FULLTEXT，避免全表掃描，然後過濾掉多餘的行。

我認為（沒有足夠的證據）REGEXP 比單個 LIKE 慢一點。您的範例每行有兩個 LIKE，因此它可能比每行一個 REGEXP 慢。

您可能會受益於使用+：

AGAINST("+https stackexchange stackoverflow +com +tour")

但請注意：常用詞（‘and’ 和 ’the’，可能還有數據集中的 ‘https’ 和 ‘com’）未編入索引。那就是+將決定沒有行匹配。這可能會更安全，也可能更快：

AGAINST("stackexchange stackoverflow +tour")

（除非這個數據庫都是關於“旅遊”的。）

（而且，是的，Bill 手動建構的反向索引可能會更好。）

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