問題描述

簡而言之場景:一個包含超過 1600 萬條記錄 [2GB 大小] 的表.當使用 ORDER BY *primary_key*

Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key*

所以

SELECT * FROM large ORDER BY `id`  LIMIT 0, 30 

遠遠少于

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30 

那只訂購了 30 條記錄，無論如何都是一樣的.所以這不是來自 ORDER BY 的開銷.
現在獲取最新的 30 行大約需要 180 秒.如何優化這個簡單的查詢?

That only orders 30 records and same eitherway. So it's not the overhead from ORDER BY.
Now when fetching the latest 30 rows it takes around 180 seconds. How can I optimize that simple query?

推薦答案

較高的偏移量會使查詢變慢是正常的，因為查詢需要計算第一個 OFFSET + LIMIT 記錄(并取只有 LIMIT 個).此值越高，查詢運行的時間越長.

It's normal that higher offsets slow the query down, since the query needs to count off the first OFFSET + LIMIT records (and take only LIMIT of them). The higher is this value, the longer the query runs.

查詢不能直接轉到OFFSET，因為首先，記錄的長度可能不同，其次，刪除的記錄可能存在間隙.它需要檢查和統計途中的每條記錄.

The query cannot go right to OFFSET because, first, the records can be of different length, and, second, there can be gaps from deleted records. It needs to check and count each record on its way.

假設 id 是 MyISAM 表的主鍵，或 InnoDB 表上唯一的非主鍵字段，您可以使用以下技巧加快速度:

Assuming that id is the primary key of a MyISAM table, or a unique non-primary key field on an InnoDB table, you can speed it up by using this trick:

SELECT  t.* 
FROM    (
        SELECT  id
        FROM    mytable
        ORDER BY
                id
        LIMIT 10000, 30
        ) q
JOIN    mytable t
ON      t.id = q.id

請看這篇文章:

• MySQL ORDERBY/LIMIT 性能:延遲行查找

這篇關于為什么 MYSQL 較高的 LIMIT 偏移量會減慢查詢速度?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！