慢查詢，同時從數百萬行中選擇更高的偏移量

我有一種包含數百萬行的安全日誌文件。在我目前的測試中，我導入了一個月的數據，這是大約 14,000,000 條記錄的總和。該表包含幾組欄位，包括

id為了primary key

log_time index

和其他8列。

當我執行一個log_time偏移量為幾百萬的選擇查詢時，查詢需要大量時間來處理。

我的查詢：

SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;

解釋

mysql> explain SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | securityData | ALL  | NULL          | NULL | NULL    | NULL | 2664456 | Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+

表詳細資訊

mysql> explain securityData;

+-----------+-----------------------+------+-----+---------+----------------+
| Field     | Type                  | Null | Key | Default | Extra          |
+-----------+-----------------------+------+-----+---------+----------------+
| id        | bigint(20)            | NO   | PRI | NULL    | auto_increment |
| log_time  | double(18,6) unsigned | NO   | MUL | NULL    |                |
/// more fields
+-----------+-----------------------+------+-----+---------+----------------+

當我增加偏移值時，處理時間會越來越長。我在 MyISAM 和 INNODB 都試過了

我怎樣才能達到這個查詢的性能？

您看到了pagination的內在缺點OFFSET。使用LIMIT，您可以強制您的數據庫對所有記錄進行排序並將它們一直計數到您的OFFSET基礎。

鍵集分頁可能更符合您的實際需求，並且會產生更好的性能。如上面連結的文章中所述，只需根據key您看到的最後一個頁面進行分頁：

SELECT * 
FROM securityData 
WHERE id > ?last_seen_id
ORDER BY log_time 
LIMIT 50;

這允許您的數據庫利用索引id直接跳轉到第一個相關行，而不是遍歷它之前的所有 5000753 行。

此查詢有兩 (2) 個特性使其難以處理：

• SELECT *
• LIMIT

必須有一個臨時表，需要使用表中的所有列來創建、載入和排序securityData。執行分頁不應該在查詢的一開始就涉及源表中的所有列。

需要什麼？Separation of keys for pagination from the gathering of all columns.

STEP 01 : 收集分頁鍵

SELECT id FROM securityData ORDER BY log_time LIMIT 5000753, 50;

STEP 02 : 使分頁查詢成為子查詢

STEP 03 : 將分頁加入securityData表格

SELECT B.* FROM
(SELECT id FROM securityData ORDER BY log_time LIMIT 5000753, 50) A
INNER JOIN securityData B USING (id)
ORDER BY B.log_time;

主要思想是將 50 個鍵收集到一個臨時表中並連接回源表。

無論任何 EXPLAIN 計劃怎麼說，這都會非常高效。我可以這麼說是因為大約 6 年前，我在 StackOverflow 上一篇類似的文章“從連接表中獲取單行”中解決了這個問題。我回答了它並通過首先收集分頁鍵獲得了不錯的賞金。

我以這種方式重寫查詢的靈感來自一位法國績效顧問的 YouTube 影片。從這個影片中學習，您將永遠不會以相同的方式看待分頁。

如果您決定以這種方式進行分頁，請將任何優化本地化到子查詢中。重點是收集鑰匙。提高收集這些鍵的性能將提高整個查詢的性能。

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