MySQL 查找的行數超出了需要（索引問題）

在我們的 MySQL 5.5 數據庫中，我們有以下具有 30M+ 行的 InnoDB 表：

+----------------+-------------+-----------------+--------+
| participant_id | question_id | given_answer_id | status |
+----------------+-------------+-----------------+--------+
|       500      |      12     |        25       |    0   |
+----------------+-------------+-----------------+--------+

參與者id + question_id + given_answer_id 的組合是唯一的。

目前我們有以下鍵：

主鍵（按此順序）

• 參與者ID
• question_id
• given_answer_id

索引鍵

• question_id

對於這個表，我們的應用程序中有兩種選擇查詢：

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

和

[...] WHERE question_id = x;

通常，每個參與者 ID 都有 0 到 <100 行不同的 question_id。反過來說，每個 question_id 可以有無限的（通常不超過 100 000）行具有不同的參與者 ID。第一個查詢比第二個查詢更頻繁地執行。

當我們執行以下查詢時，它向我們顯示了查找的32096 行：

EXPLAIN SELECT * FROM example WHERE question_id = 500;

+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows  | Extra |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+
| 1  | SIMPLE      | example   | ref  | question_id   | question_id | 8       | const | 32096 |       |
+----+-------------+-----------+------+---------------+-------------+---------+-------+-------+-------+

然而，當我們在沒有 EXPLAIN的情況下執行相同的查詢時，只返回 18732 行。

我們需要在這個表上使用什麼索引來防止這種成本，但仍然對這兩種查詢都執行？

---

這是創建此表的程式碼：

創建表`範例`（
`participant_id` BIGINT(20) UNSIGNED NOT NULL,
`question_id` BIGINT(20) UNSIGNED NOT NULL,
`given_answer_id` BIGINT(20) UNSIGNED NOT NULL,
`status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
主鍵（`participant_id`、`question_id`、`given_answer_id`），
索引`question_id`（`question_id`）
)
引擎=InnoDB；

您的索引適用於您提到的兩種類型的查詢。

這個查詢將通過遍歷主鍵上的聚集索引來滿足……

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

……這對’question_id’的索引很滿意：

[...] WHERE question_id = x;

的輸出EXPLAIN SELECT並沒有告訴您您認為它在告訴您什麼，因為顯示的值rows是伺服器需要考慮的行數的*估計值，而不是它將檢查的實際行數。*因為InnoDB這些是基於索引統計的。

行

rows 列表示 MySQL 認為它必須檢查以執行查詢的行數。

對於 InnoDB 表，這個數字是一個估計值，可能並不總是準確的。

— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows

優化器收集有關不同可能查詢計劃的資訊，並選擇成本最低的一個。中顯示EXPLAIN的資訊是優化器收集的關於它選擇的計劃的資訊。

當typeisref和keyis notNULL時，這意味著列中列出key的名稱是優化器選擇用於查找所需行的索引的名稱，因此您的查詢計劃看起來完全符合它應該的樣子。

請注意，有時您會Using index在Extra列中看到很多人認為這意味著正在使用索引，或者當沒有出現索引時沒有使用索引，但這也不正確。 Using index描述了一種稱為“覆蓋索引”的特殊情況——它不指示是否使用索引來定位感興趣的行。

執行ANALYZE [LOCAL] TABLE可能會導致rows顯示的數字EXPLAIN不同，但這是一個簡單的查詢，選擇此索引是優化器的明顯選擇，因此ANALYZE TABLE不太可能對性能產生任何實際影響。

但是，您的整體性能可能會偶爾出現一些邊際OPTIMIZE [LOCAL] TABLE改進，因為您沒有按主鍵順序插入行（就像主鍵的情況一樣auto_increment）……但在大型表上，這可能是耗時，因為它會重建表的新副本……但是，我不希望有任何重大變化。

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