MySQL 如何管理與索引相關的記憶體？
首先，我問這個的原因是因為我覺得我有一個數據庫 - 根據我自己的估計 - 應該已經殺死具有大量 I/O 的磁碟，因為索引不適合記憶體，但在實際上它仍然表現良好。
讓我們從相關表開始：
CREATE TABLE `search` ( `a` bigint(20) unsigned NOT NULL, `b` int(10) unsigned NOT NULL, `c` int(10) unsigned DEFAULT NULL, `d` int(10) unsigned DEFAULT NULL, `e` varchar(255) DEFAULT NULL, `f` varchar(255) DEFAULT NULL, `g` varchar(255) DEFAULT NULL, `h` varchar(255) DEFAULT NULL, `i` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;該
a列是一個 8 字節的數字，其中編碼了時間戳（以秒為單位）。該表有一個PARTITION BY RANGE (a), 將表分成每月分區。這是因為我們在數據庫中只保留了 24 個月，其餘的都被清除了。該表每月增長大約 2 億行；整個表包含大約 50 億行。
它執行的伺服器有大約 360GB 的記憶體，其中 300GB 是為 MySQL 保留的。我發現有趣的是，不久前，磁碟使用率開始上升一點。現在，我相信這是因為某些索引不再適合記憶體，導致 MySQL 從磁碟載入它們，但這只是猜測；我不熟悉 MySQL 的內部結構。
有沒有辦法查看在給定時間或特定查詢將哪些頁面/塊載入到記憶體中？
這些是實際使用的三個表：
CREATE TABLE `search` ( `a` bigint(20) unsigned NOT NULL, `b` int(10) unsigned NOT NULL, `c` int(10) unsigned DEFAULT NULL, `d` int(10) unsigned DEFAULT NULL, `e` varchar(255) DEFAULT NULL, `f` varchar(255) DEFAULT NULL, `g` varchar(255) DEFAULT NULL, `h` varchar(255) DEFAULT NULL, `i` varchar(255) DEFAULT NULL, KEY `a_idx` (`a`), KEY `b_idx` (`b`), KEY `c_idx` (`c`, `a`), KEY `d_idx` (`d`, `a`), KEY `e_idx` (`e`, `a`), KEY `f_idx` (`f`, `a`), KEY `g_idx` (`g`, `a`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `channels` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `clients` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `client_hash` varchar(4095) NOT NULL, PRIMARY KEY (`id`), KEY `hash_idx` (`client_hash`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8這些是目前正在執行的查詢：
SELECT S.a, S.b, S.e, S.f, S.g, S.h, S.i, C1.client_hash, C2.name FROM search S LEFT JOIN clients C1 ON S.c = C1.id LEFT JOIN channels C2 ON S.d = C2.id WHERE S.e = "foo" AND S.a >= 6409642363135721472 AND S.a <= 6443039964404908032 AND S.b >= 1492361157 AND S.b <= 1500137142 ORDER BY S.a DESC LIMIT 50 SELECT S.a, S.b, S.e, S.f, S.g, S.h, S.i, C1.client_hash, C2.name FROM search S LEFT JOIN clients C1 ON S.c = C1.id LEFT JOIN channels C2 ON S.d = C2.id WHERE S.f = "bar" AND S.a >= 6409642363135721472 AND S.b >= 1492361157 ORDER BY S.a DESC LIMIT 50 SELECT S.a, S.b, S.e, S.f, S.g, S.h, S.i, C1.client_hash, C2.name FROM search S LEFT JOIN clients C1 ON S.c = C1.id LEFT JOIN channels C2 ON S.d = C2.id WHERE S.g = "baz" AND S.a >= 6409642363135721472 AND S.b >= 1492361157 ORDER BY S.a DESC LIMIT 50 SELECT S.a, S.b, S.e, S.f, S.g, S.h, S.i, C1.client_hash, C2.name FROM search S LEFT JOIN clients C1 ON S.c = C1.id LEFT JOIN channels C2 ON S.d = C2.id WHERE S.g LIKE "baz%" AND S.a >= 6409642363135721472 AND S.b >= 1492361157 ORDER BY S.a DESC LIMIT 50
有哪些指標？你沒有索引！所以任何查詢都會掃描整個表——所有分區。一旦整個表大於
innodb_buffer_pool_size，表掃描將無法完成而無需訪問磁碟。下一次表掃描將從磁碟重新讀取所有內容。索引不需要保存在記憶體中。它就像一張表——它由 16KB 塊組成，這些塊根據需要記憶體到緩衝池中，然後在“舊”時被淘汰（想想“最近最少使用”的記憶體方案）。
同樣，如果您進行全索引掃描，並且索引不適合緩衝池，那麼記憶體將變得無用，您將一直訪問磁碟。
但是……索引的正確定義和使用不必以這種命運告終。我已經看到 TB 大小的表在 32GB 的 RAM 中執行良好。特別是“點查詢”（
... WHERE primary_key = constant ...）將花費不到 1 秒的時間，無論表有多大或 buffer_pool 有多小。在最壞的情況下（冷記憶體），一個十億行的表可能需要在 BTree 中獲取 5 個塊才能找到您要求的單行。
PARTITION BY RANGE(id)幾乎總是無用的。相反，PRIMARY KEY(id)在沒有分區的情況下，通過 定位行會更好id。有一些工具可以查看 buffer_pool 中的內容，但我不想處理 2000 萬個塊號來處理您的要求！
相反，讓我們看看你的實際
SHOW CREATE TABLE（所以我們可以看到索引/分區）和一些SELECTs. 從這些我們可以討論幕後發生的事情。這可能更快，資訊量更大。
（我之前的答案仍然適用，但它是在
INDEXes和SELECTs可用之前編寫的。）最佳指標
所有 4 個查詢看起來都是這樣的，對嗎？
SELECT S.a, S.b, S.e, S.f, S.g, S.h, S.i, C1.client_hash, C2.name FROM search S LEFT JOIN clients C1 ON S.c = C1.id LEFT JOIN channels C2 ON S.d = C2.id WHERE S.<some-column> = "..." -- or LIKE AND S.a >= 6409642363135721472 AND S.b ... (some range) ORDER BY S.a DESC LIMIT 50哪裡是（至少）e，f，g。
我認為這些是唯一有用的索引
S：INDEX(e, a) INDEX(f, a) INDEX(g, a)在將 e/f/g 與常數進行比較時，所有這些都由以下各項處理
INDEX(g,a)：WHERE S.g = "baz" AND S.a >= constant ORDER BY S.a LIMIT 50測試
S.b >= constant會導致它超過 50 行，但希望不是整個表？至少避免了文件排序。LIKE 也不起作用
對於
S.g LIKE "baz%"，以下 3 個索引中的任何一個都可能有用。優化器可能會AND根據對每個子句需要多少行的估計來選擇最好的。INDEX(g, a) -- already asked for this; it will use only the `g` part INDEX(a) -- hoping to get `S.a >= constant ORDER BY S.a LIMIT` INDEX(b) -- in case it filters well (but not if partitioned by b)所以，我推薦5個索引。
減至 50
由於
LIMIT 50，我將進行以下更改。基本原理是，進行該操作ORDER BY .. LIMIT可能需要收集超過 50 行。在這樣做時，它將比 50JOINs和. 因此，這種重新表述將這些查找限制為 50：clients``channelsSELECT S.a, S.b, S.e, S.f, S.g, S.h, S.i, ( SELECT client_hash FROM clients WHERE id = S.c ) AS client_hash, ( SELECT name FROM channels WHERE id = S.d ) AS channel_name FROM search S WHERE S.<some-column> =/LIKE ... AND S.a .. some range AND S.b .. some range ORDER BY S.a DESC LIMIT 50注意
LEFT JOINs變成子查詢。結果應該是相同的。劃分
您有一個 2 維或 3 維問題（範圍在
a並且b可能g（當LIKE））。2D 是PARTITIONing. 現在關於它是否適用於您的查詢的問題。這是我最好的猜測，基於對您的數據集的了解很少：
PARTITION BY RANGE(b)並有 20-50 個分區。希望範圍測試
b會將所需數據限制在一個（或很少）分區，從而減少工作量。你問了
PARTITION BY RANGE(id)，但我仍然id在表格中看到沒有。您有任何獨特的列（或列的組合）嗎？你有PRIMARY KEY嗎？請回答這些；關於如何利用 PK 對數據進行分群，我可能有一個有用的提示。（如果我們使用分區進行分區，我可能會修改我的索引建議。）
因為
aorb是多餘的假設您保留
a但刪除b，WHERE S.<some-column> =/LIKE ... AND S.a .. some range AND S.b .. some range ORDER BY S.a DESC應該成為
WHERE S.<some-column> =/LIKE ... AND S.a .. some range ORDER BY S.a DESC然後
INDEX(b)走開。這留下了提供的查詢所需的 4 個索引。我建議進行這些更改，然後重新評估
LIKE查詢是否足夠好，以及是否需要將任何其他查詢納入討論。也就是說，PARTITIONing在我們看到它是否值得添加之前，不要。與分區相關的更多問題：是否不斷添加新行？是舊的時間戳
DELETEd嗎？哪個更有選擇性？
S.g LIKE "baz%"? 或者S.a >= 6409642363135721472？