MySQL带LIMIT分页查询性能优化及单语句实现方案咨询

算力阿峰

2026-9-30

单查询实现方案（MySQL 8.0+ 适用）

通过CTE+窗口函数可在单次查询中完成结果过滤、总条数计算、动态分页截断，避免多次扫描全表：

WITH filtered_result AS (
    SELECT
        `data`,
        ROW_NUMBER() OVER (ORDER BY `heure` DESC) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM mytable
    WHERE (conditions...)
)
SELECT `data`
FROM filtered_result
WHERE row_num > varOffset
  AND row_num <= varOffset + LEAST(varLimit, total_rows - varOffset);

该方案仅触发一次全表扫描，同时自动完成LIMIT值的动态调整，从根源避免LIMIT设置大于实际结果数带来的性能损耗。

更低版本/超大数据量场景的更优方案

方案1：缓存总条数减少重复扫描

当前实现需要两次执行相同的WHERE过滤逻辑，全表扫描成本直接翻倍。可将首次查询得到的varCount结果按查询条件维度缓存，缓存有效期可根据数据更新频率调整，后续同条件分页查询时直接使用缓存的总条数计算动态LIMIT，无需重复执行COUNT查询，性能可提升50%以上。

方案2：预处理语句实现动态LIMIT

如果使用的是MySQL 5.x版本不支持CTE和窗口函数，可通过预处理语句在单次交互内完成逻辑：

-- 提前赋值变量
SET @varLimit = 20;
SET @varOffset = 0;

-- 动态拼接SQL
SET @query_sql = CONCAT(
    'SELECT `data` FROM mytable WHERE (conditions...) ORDER BY `heure` DESC LIMIT ',
    LEAST(@varLimit, (SELECT COUNT(*) FROM mytable WHERE (conditions...)) - @varOffset),
    ' OFFSET ',
    @varOffset
);

-- 执行查询
PREPARE stmt FROM @query_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;