MySQL分页查询随页码增加变慢问题求助（数据导出场景）

问题描述 #

执行数据导出操作时，多次执行仅OFFSET参数不同的MySQL查询，发现PDOStatement->execute的耗时随页码增加而持续上升，超过200页后该现象尤为显著。即使是无关联的简单查询、使用mysqli驱动时也存在同样问题。

示例查询语句 #

SELECT `main_table`.*,
       `p`.`method`      AS `payment_method`,
       `ba`.`country_id` AS `billing_country`,
       `ba`.`region`     AS `billing_region`,
       `ba`.`city`       AS `billing_city`,
       `ba`.`postcode`   AS `billing_postcode`,
       `ba`.`vat_id`     AS `billing_vat_id`,
       `sa`.`country_id` AS `shipping_country`,
       `sa`.`region`     AS `shipping_region`,
       `sa`.`city`       AS `shipping_city`,
       `sa`.`postcode`   AS `shipping_postcode`,
       `sa`.`vat_id`     AS `shipping_vat_id`
FROM `sales_order` AS `main_table`
         INNER JOIN `sales_order_payment` AS `p` ON main_table.entity_id = p.parent_id
         INNER JOIN `sales_order_address` AS `ba` ON main_table.entity_id = ba.parent_id AND ba.address_type = "billing"
         INNER JOIN `sales_order_address` AS `sa`
                    ON main_table.entity_id = sa.parent_id AND sa.address_type = "shipping"
WHERE (`updated_at` <= '2024-09-03 07:59:33')
  AND (`status` IN ('complete', 'sent', 'closed'))
LIMIT 100 OFFSET 1500

调试代码（Magento框架内） #

if ($specialExecute) {
    return $this->_executeWithBinding($params);
} else {
    return $this->tryExecute(function () use ($params) {
        $startTimex = microtime(true);

        if (!empty($params)) {
            $res = $this->_stmt->execute($params);
        } else {
            $res = $this->_stmt->execute();
        }

        echo 'Time X: ' . (int)((microtime(true) - $startTimex) * 1000000) . PHP_EOL;

        return $res;
        //return !empty($params) ? $this->_stmt->execute($params) : $this->_stmt->execute();
    });
}

问题根源 #

MySQL使用OFFSET分页时，需要先扫描并跳过OFFSET指定的所有行，才能返回LIMIT数量的结果。随着OFFSET值增大，需要扫描的行数越来越多，导致查询耗时线性增长。

解决方案 #

1. 基于主键/唯一键的游标分页（推荐） #

利用主键（或唯一键）的有序性，每次查询记录最后一条的主键值，下一次查询通过WHERE 主键 > 上一次主键替代OFFSET，直接定位起始位置，避免扫描多余行。

示例代码

第一次查询：

SELECT `main_table`.*,
       `p`.`method`      AS `payment_method`,
       `ba`.`country_id` AS `billing_country`,
       -- 其他字段省略
FROM `sales_order` AS `main_table`
         INNER JOIN `sales_order_payment` AS `p` ON main_table.entity_id = p.parent_id
         INNER JOIN `sales_order_address` AS `ba` ON main_table.entity_id = ba.parent_id AND ba.address_type = "billing"
         INNER JOIN `sales_order_address` AS `sa` ON main_table.entity_id = sa.parent_id AND sa.address_type = "shipping"
WHERE (`updated_at` <= '2024-09-03 07:59:33')
  AND (`status` IN ('complete', 'sent', 'closed'))
ORDER BY main_table.entity_id ASC
LIMIT 100

获取结果中最后一条的entity_id（比如1000），下一次查询：

SELECT `main_table`.*,
       `p`.`method`      AS `payment_method`,
       `ba`.`country_id` AS `billing_country`,
       -- 其他字段省略
FROM `sales_order` AS `main_table`
         INNER JOIN `sales_order_payment` AS `p` ON main_table.entity_id = p.parent_id
         INNER JOIN `sales_order_address` AS `ba` ON main_table.entity_id = ba.parent_id AND ba.address_type = "billing"
         INNER JOIN `sales_order_address` AS `sa` ON main_table.entity_id = sa.parent_id AND sa.address_type = "shipping"
WHERE (`updated_at` <= '2024-09-03 07:59:33')
  AND (`status` IN ('complete', 'sent', 'closed'))
  AND main_table.entity_id > 1000
ORDER BY main_table.entity_id ASC
LIMIT 100

注意：如果需要按非主键字段排序，需结合主键一起排序（ORDER BY sort_field, entity_id），避免因排序字段重复导致数据遗漏或重复。

2. 使用子查询定位起始行 #

先通过子查询（仅扫描主键索引）找到OFFSET对应的主键集合，再关联主表获取数据，减少全表扫描的开销。

示例代码

SELECT `main_table`.*,
       `p`.`method`      AS `payment_method`,
       `ba`.`country_id` AS `billing_country`,
       -- 其他字段省略
FROM `sales_order` AS `main_table`
         INNER JOIN `sales_order_payment` AS `p` ON main_table.entity_id = p.parent_id
         INNER JOIN `sales_order_address` AS `ba` ON main_table.entity_id = ba.parent_id AND ba.address_type = "billing"
         INNER JOIN `sales_order_address` AS `sa` ON main_table.entity_id = sa.parent_id AND sa.address_type = "shipping"
WHERE main_table.entity_id IN (
    SELECT entity_id
    FROM sales_order
    WHERE (`updated_at` <= '2024-09-03 07:59:33')
      AND (`status` IN ('complete', 'sent', 'closed'))
    ORDER BY entity_id ASC
    LIMIT 100 OFFSET 1500
)
ORDER BY main_table.entity_id ASC

3. 预生成临时表（适合大规模导出） #

如果导出数据量极大，先将符合条件的数据写入带索引的临时表，再从临时表分页查询，保证分页速度稳定。

步骤示例

1. 创建临时表：

CREATE TEMPORARY TABLE temp_export_orders (
    entity_id INT PRIMARY KEY,
    payment_method VARCHAR(255),
    billing_country VARCHAR(2),
    -- 其他需要的字段
) ENGINE=InnoDB;

2. 插入符合条件的数据：

INSERT INTO temp_export_orders
SELECT main_table.entity_id,
       p.method AS payment_method,
       ba.country_id AS billing_country,
       -- 其他字段省略
FROM `sales_order` AS `main_table`
         INNER JOIN `sales_order_payment` AS `p` ON main_table.entity_id = p.parent_id
         INNER JOIN `sales_order_address` AS `ba` ON main_table.entity_id = ba.parent_id AND ba.address_type = "billing"
         INNER JOIN `sales_order_address` AS `sa` ON main_table.entity_id = sa.parent_id AND sa.address_type = "shipping"
WHERE (`updated_at` <= '2024-09-03 07:59:33')
  AND (`status` IN ('complete', 'sent', 'closed'));

3. 从临时表分页查询：

SELECT * FROM temp_export_orders LIMIT 100 OFFSET 1500;

4. 优化查询索引 #

给查询中用到的过滤、排序、关联字段创建合适的索引，减少基础查询耗时：

• 给sales_order表创建联合索引：

CREATE INDEX idx_sales_order_updated_at_status_entity ON sales_order(updated_at, status, entity_id);

• 检查sales_order_payment.parent_id、sales_order_address.parent_id+address_type的索引是否存在（可通过SHOW INDEX FROM table_name验证）

内容的提问来源于stack exchange，提问作者Igor Galczak