為什麼/如何匹配列的數量影響執行查詢的方式

想像以下情況：

表 A 使用 MyISAM 並包含 4 個欄位（文本）和一個組合的 FULLTEXT-index。

FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4

表 B 使用 InnoDB 並包含一些其他欄位，其中 5 個被索引為“ORD”。

ORD order1 | order2 | order3 | order4 | order5

我喜歡通過在其外部 id 上加入 tableB 並按 tableB 的索引列對結果進行排序來對 tableA 進行全文搜尋。

---

Query1 - 匹配所有 4 個全文列：

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id` 

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`, `tableA`.`fulltext4`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
) 
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

需要 1.6565 秒。

解釋查詢1：

select_type  table   type     possible_keys     key      key_len  ref   rows            Extra
SIMPLE       tableA  fulltext PRIMARY,FULLTEXT  FULLTEXT    0     1                     Using where; Using temporary; Using filesort
SIMPLE       tableB  eq_ref   PRIMARY           PRIMARY     4     db.tableA.tableB_id   1 

沒有使用索引，需要臨時表。我不知道第二行中的“Extra 1”是什麼意思。

---

Query2 - 僅匹配 3 列：

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id` 

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
) 
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

耗時 0.0114 秒。

解釋查詢2：

select_type table   type    possible_keys  key     key_len  ref           rows  Extra
SIMPLE      tableB  index   PRIMARY        ORD     783      NULL          15    Using index
SIMPLE      tableA  eq_ref  PRIMARY        PRIMARY 4        db.tableB.id  1     Using where

列出的表格的順序已更改。對於 tableB，使用了索引，對於 tableA，不需要臨時表。

---

我從查詢中排除哪個全文列無關緊要 - 只要我匹配少於 4 個，我就會得到顯示的 Query2 的持續時間和解釋。

也許很有趣：兩個表中的行數相等，大約 180k。

我想知道這種行為發生的原因。我的意思是，整個執行方式似乎取決於匹配列的數量。

**編輯：**現在我完全糊塗了。我刪除了全文索引。我匹配了所有 4 個（前）全文列（Query1）。現在需要 0.1205 秒。EXPLAIN 告訴我不需要臨時表；但我想知道如何在沒有全文索引的情況下在布爾模式下更快地匹配。

我可以提供一般解釋，但它可能不適用於您的特定情況：

決策的工作方式是評估執行計劃的成本，然後選擇最便宜的計劃。這個你已經知道了。

但是，在索引方面，事情變得越來越有趣。評估指數有用性或可行性的方法是在給定某個值的情況下估計選擇性。

目前，忘記你的 FULLTEXT 索引，讓我們假設 some column 上有一個簡單的索引，而 some columncol1上有另一個索引col2。給定以下兩個查詢：

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

在這兩種情況下，查詢的評估可能不同。為什麼？因為它可能會col2 = 4返回比更多的行col1 < 10，在這種情況下我們更喜歡使用 index on col1。但是，它可能返回的行數少於*，*col1 BETWEEN 100 AND 110在這種情況下，我們更喜歡 上的索引col2。

你的情況並沒有太大的不同。MySQL估計某些索引查詢返回的行數。當您使用更多列時，MySQL 會產生您的索引可能會導致幾行的印象。所以它選擇從 開始TableA，然後加入應該很少的行TableB。

但是如果 MySQL 認為索引返回很多行，它可能更喜歡以TableB. 這是為什麼？因為您正在對TableB. 排序也是一項繁重的工作。所以 MySQL 可能會選擇先對行進行排序，然後加入TableA全文索引並進行過濾。無論如何，如果全文搜尋產生許多行，這可能不是一個壞主意。

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