為什麼引用缺失列時 MySQL 全文搜尋“IN BOOLEAN MODE”不會失敗？

首先，我是如何發現這個問題的背景。這發生在使用 MySQL 5.7 和 DB — 以及相關表 — 使用 MyISAM 時。

我在我管理的一個網站上調試了一些程式碼——以及相關的 MySQL 查詢——並發現了瓶頸：結果發現全文搜尋正在執行一個MATCH引用的列，該列不屬於具有 100,000 多行的表的全文索引的一部分。萬歲！我重建了全文索引，添加了缺失的列，突然一個需要 4.7 秒才能執行的查詢在 0.0007 秒內執行！

但讓我感到困惑的是：為什麼IN BOOLEAN MODE引用缺失列時 MySQL 全文搜尋沒有失敗？相反——在這種情況下——它只是執行緩慢但給出了正確的結果。為什麼它沒有失敗？這是錯誤還是功能？

我的意思是，當我在其他全文模式下執行相同的查詢時，例如：

• AGAINST ('Happy' IN NATURAL LANGUAGE MODE)
• AGAINST ('Happy' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
• AGAINST ('Happy' WITH QUERY EXPANSION)

我立即得到了這個 MySQL 錯誤：

#1191 - Can't find FULLTEXT index matching the column list

對我來說，這樣的失敗會比 MySQL 看似進行全表搜尋更有用。相反，查詢被簡單地降級為準確但極其緩慢。

最初的全文索引是這樣建構的；注意缺少的MOOD列：

ALTER TABLE `bigmoods`
 DROP INDEX `bigmoods_fulltext_idx`,
 ADD FULLTEXT `bigmoods_fulltext_idx`
 (
   `FIRSTNAME`,
   `LASTNAME`
 );

查詢是這樣的：

SELECT
 SQL_CALC_FOUND_ROWS
 FIRSTNAME,
 LASTNAME,
 MOOD,
 MATCH (
   `FIRSTNAME`,
   `LASTNAME`,
   `MOOD`
 )
 AGAINST ('Happy' IN BOOLEAN MODE)
 AS search_score
FROM
 bigmoods
WHERE
 MATCH (
   `FIRSTNAME`,
   `LASTNAME`,
   `MOOD`
 )
 AGAINST ('Happy' IN BOOLEAN MODE)
GROUP BY
 LASTNAME
ORDER BY
 LASTNAME ASC
LIMIT 0,100
;

這是記錄在案的行為：

InnoDB 表需要 MATCH() 表達式的所有列上的 FULLTEXT 索引來執行布爾查詢。即使沒有 FULLTEXT 索引，針對 MyISAM 搜尋索引的布爾查詢也可以工作，儘管以這種方式執行的搜尋會很慢。

為什麼存在此異常（這是您在評論中提出的問題）是推測，原始開發人員需要回答一些問題，但是如果沒有此異常，您將無法做到：

跨越多個表的布爾搜尋。

對於自然搜尋，您可以or通過單獨的匹配（每個表一個）來補償，對於布爾搜尋，它將落入“更難”到“不可能”的範圍內，這可以解釋不對稱性。

如果這是作為 (MyISAM) 布爾搜尋的一項不錯的功能添加的，並且至少有一個使用者依賴它，那麼您可以提出不刪除它的論點，因為不強制索引的唯一缺點是它可能很慢如果您犯了忘記索引的錯誤（普通索引也可能而且將會發生這種情況，請參閱 Stack Overflow 上 90% 的 SQL 性能問題）。

MySQL 確實不是在保護使用者免受自己的侵害，請參見例如only_full_group_by=0（如果您不小心，可能會給您帶來意想不到的結果）或grant（在 MySQL 8.0 之前，如果您不小心，可能會創建一個沒有密碼的新使用者） .

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