為什麼mysql使用錯誤的索引進行查詢排序？

這是我的表，包含約 10,000,000 行數據

CREATE TABLE `votes` (
 `subject_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `subject_id` int(11) NOT NULL,
 `voter_id` int(11) NOT NULL,
 `rate` int(11) NOT NULL,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`subject_name`,`subject_id`,`voter_id`),
 KEY `IDX_518B7ACFEBB4B8AD` (`voter_id`),
 KEY `subject_timestamp` (`subject_name`,`subject_id`,`updated_at`),
 KEY `voter_timestamp` (`voter_id`,`updated_at`),
 CONSTRAINT `FK_518B7ACFEBB4B8AD` FOREIGN KEY (`voter_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

這是索引基數

所以當我做這個查詢時：

SELECT SQL_NO_CACHE * FROM votes WHERE 
   voter_id = 1099 AND 
   rate = 1 AND 
   subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

我期待它使用索引voter_timestamp ，但mysql選擇使用它：

explain select SQL_NO_CACHE * from votes  where subject_name = 'medium' and voter_id = 1001 and rate = 1 order by updated_at desc limit 20 offset 100;`

type:
   index_merge
possible_keys: 
   PRIMARY,IDX_518B7ACFEBB4B8AD,subject_timestamp,voter_timestamp
key:
   IDX_518B7ACFEBB4B8AD,PRIMARY
key_len:
   102,98
ref:
   NULL
rows:
   9255
filtered:
   10.00
Extra:
   Using intersect(IDX_518B7ACFEBB4B8AD,PRIMARY); Using where; Using filesort

我得到了 200-400 毫秒的查詢時間。

如果我強制它使用正確的索引，例如：

SELECT SQL_NO_CACHE * FROM votes USE INDEX (voter_timestamp) WHERE 
   voter_id = 1099 AND 
   rate = 1 AND 
   subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

mysql可以在1-2ms內返回結果

這是解釋：

type:
   ref
possible_keys:
   voter_timestamp
key:
   voter_timestamp
key_len:
   4
ref:
   const
rows:
   18714
filtered:
   1.00
Extra:
   Using where

那麼為什麼mysql沒有voter_timestamp為我的原始查詢選擇索引呢？

我嘗試的是analyze table votes，optimize table votes刪除該索引並再次添加它，但 mysql 仍然使用錯誤的索引。不太明白是什麼問題。

對於該查詢，您需要此索引：

INDEX(voter_id, rate, subject_name, updated_at)

updated_at必須是最後一個；其他三個可以按任何順序排列。（ypercube 的 3 列索引不是很有用，因為它們不會在到達WHERE列之前完成ORDER BY列。）

添加此索引時，您可能可以擺脫所有其他輔助鍵：

KEY IDX_518B7ACFEBB4B8AD( voter_id), – FK 可以使用我的索引 KEY subject_timestamp( subject_name, subject_id, updated_at), – 大部分是多餘的 KEY voter_timestamp( voter_id, updated_at), – 可能是你的嘗試

使用 4 列索引，您有機會優化“分頁”並避免OFFSET. 請參閱此部落格。

關於另一個話題……當我看到X_nameandX_id時，我認為“規範化”正在進行中。我希望在表格中看到這兩列，幾乎沒有其他內容。我不希望在其他表格中看到兩者。

(voter_id, updated_at)不會過去voter_id，因為它還沒有完成過濾（WHERE）。然後，由於另一個索引較小，因此將其選中。我的有 3 列來處理過濾，然後是ORDER BY.

MySQL 使用相對簡單（比其他 RDBMS 更簡單）的成本模型來規劃查詢，其中過濾數據集具有很高的優先級。在您使用合併索引的第一個查詢中，估計需要掃描約 9000 行，而帶有索引提示的第二個查詢將需要 18000 行。我敢打賭，這在計算中的權重足以將比例移向合併. optimizer_trace您可以通過打開、執行查詢並評估結果來確認這一點（或找到其他原因） 。

set global optimizer_trace='enabled=on';

-- run your query 

SELECT SQL_NO_CACHE * FROM votes WHERE 
   voter_id = 1099 AND 
   rate = 1 AND 
   subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

select * from information_schema.`OPTIMIZER_TRACE`;

關於index_merge：在大多數情況下，您會發現它非常昂貴。儘管對於 OLAP 類型的場景非常有用，但它可能不太適合 OLTP，因為該操作可能會佔用大量查詢時間，而且您可以看到有時次優的執行計劃實際上更快。

幸運的是 MySQL 為優化器提供了開關，因此您可以根據需要自定義它。

對於您可以執行的所有選項：

show global variables like 'optimizer_switch';

要更改一個，您不必複製粘貼整個字元串。它可以dict.update()在 python 中工作。

set global optimizer_switch='index_merge=off';

---

如果可能的話，我也會看看你的表結構並改進。不建議使用帶有許多輔助鍵的 ~100 字節主鍵。

您有四個輔助鍵，其中一些是多餘的，例如(voter_id)索引是(voter_id, updated_at)

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