假設我有一個像這樣定義的 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