假設我有一個像這樣定義的 MySQL 表：
CREATE TABLE big_table (
primary_1 varbinary(1536),
primary_2 varbinary(1536),
ts timestamp(6),
...
PRIMARY KEY (primary_1, primary_2),
KEY ts_idx (ts),
)
我想實作本博客文章中所述的高效分頁（尋求分頁）https://use-the-index-luke.com/sql/partial-results/top-n-queries
如果我只使用主鍵的第一部分，流水線執行會按預期快速運行：
mysql> explain select * from big_table order by ts, primary_1 limit 5;
---- ------------- ------------------------------------- ------------ ------- --------------- -------- --------- ------ ------ ---------- -------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------------------------------------- ------------ ------- --------------- -------- --------- ------ ------ ---------- -------
| 1 | SIMPLE | big_table | NULL | index | NULL | ts_idx | 7 | NULL | 5 | 100.00 | NULL |
---- ------------- ------------------------------------- ------------ ------- --------------- -------- --------- ------ ------ ---------- -------
但是，如果我將主鍵的第二部分添加到 ORDER BY 子句中，一切都會變慢并且開始使用檔案排序：
mysql> explain select * from big_table order by ts, primary_1, primary_2 limit 5;
---- ------------- ------------------------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- ----------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------------------------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- ----------------
| 1 | SIMPLE | big_table | NULL | ALL | NULL | NULL | NULL | NULL | 6388499 | 100.00 | Using filesort |
---- ------------- ------------------------------------- ------------ ------ --------------- ------ --------- ------ --------- ---------- ----------------
是否無法在復合主節點上進行這種流水線執行和排序？還是應該以某種特殊方式撰寫查詢？
uj5u.com熱心網友回復：
如果沒有關于 MySQL 內部如何作業的先驗知識，就沒有理由假設 上的索引ts可以使用而order by ts, primary_1無需對primary_1. 想象一下，例如所有值ts都相同的邊緣情況- 索引只會給你所有的行，然后你必須按 排序primary_1。
然而，MySQL 可以利用一些附加資訊： InnoDB 以包含主鍵列的方式存盤二級索引（以便能夠在表中找到實際行）。由于該資訊無論如何都在那里，MySQL 可以使用它 - 通過使用Index Extensions確實可以使用它。這基本上將索引擴展ts到索引ts, primary_1, primary_2。
所以這個技術技巧允許你使用索引 onts來 order by ts, primary_1, primary_2。但既然總有一個“但是”，這里是“但是”：
優化器對索引擴展的使用受到索引中關鍵部分數量 (16) 和最大密鑰長度 (3072 位元組) 的通常限制。
上的索引ts, primary_1, primary_2將長于 3072 位元組。例如，您也可以不手動創建這樣的索引。所以這個擴展不再起作用，MySQL 回退到把索引ts當作ts.
那么它為什么有效order by ts, primary_1呢？好吧，即使由于這些技術原因，MySQL 無法在 上創建內部索引ts, primary_1, primary_2，它至少可以在不遇到ts, primary_1技術問題的情況下做到這一點。MySQL 實際上并沒有這樣做 - 但是 MariaDB 開發人員實作了這個技巧，所以我假設您實際上正在使用 MariaDB。盡管如此，3072 的長度限制仍然適用，因此您對兩個主列的排序仍然無效。
你能做什么？
如果您可以稍微縮短主鍵，索引擴展將再次起作用。長的（和那種型別的）主鍵無論如何都不常見和不切實際（不僅對于這個用例），所以也許你可以為你的表找到一個不同的主鍵。
如果這不是一個選項，您可以利用一些關于您的資料分布的先驗知識，例如，如果您知道最多 10 個值ts可以相同，您可以首先選擇前 n 10 行（使用索引)，然后僅按主鍵排序。如果您通常只顯示前幾頁，這可能會加快您的特定情況。但是您可能想針對它提出一個單獨的問題，并提供具體的細節。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/318726.html