清晨的一缕阳光
返回

MySQL 分页优化方案

核心概念

分页是 Web 应用最常见的功能之一。MySQL 分页性能问题主要集中在深度分页场景。

分页方式对比

分页方式优点缺点适用场景
LIMIT 分页简单直观深度分页性能差浅分页
游标分页性能好不能跳页无限滚动
延迟关联性能提升明显实现稍复杂大表分页
书签分页性能最优只能下一页日志查询

分页性能问题

-- 浅分页（性能好）
SELECT * FROM orders ORDER BY create_time DESC LIMIT 0, 10;
-- 耗时：10ms

-- 深度分页（性能差）
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;
-- 耗时：5000ms（5 秒）

问题原因：

  1. MySQL 需要扫描 1000010 行
  2. 排序 1000010 行
  3. 丢弃前 1000000 行
  4. 返回最后 10 行

分页底层原理

LIMIT 执行流程

LIMIT offset, size 执行过程：
1. 根据 ORDER BY 排序（或索引顺序）
2. 扫描 offset + size 行
3. 丢弃前 offset 行
4. 返回 size 行

时间复杂度：O(offset + size)

索引对分页的影响

-- 无索引排序
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;
-- Extra: Using filesort（文件排序）
-- 需要排序所有数据

-- 有索引排序
CREATE INDEX idx_create_time ON orders(create_time);
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;
-- Extra: Using index（索引排序）
-- 按索引顺序扫描，无需额外排序

分页优化方案

方案 1：延迟关联（推荐）

原理： 先通过覆盖索引获取主键，再回表查询完整数据。

-- 优化前：直接分页
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 1000000, 10;
-- 扫描 1000010 行，回表 1000010 次

-- 优化后：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id;
-- 子查询：扫描 1000010 行（覆盖索引，不回表）
-- 主查询：回表 10 次
-- 性能提升：100 倍+

适用场景：

索引要求：

-- 创建覆盖索引
CREATE INDEX idx_create_time_id ON orders(create_time DESC, id);

方案 2：游标分页（推荐）

原理： 使用上一页最后一条记录的位置作为起点。

-- 第一页
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 10;
-- 记录最后一条 create_time: 2024-01-01 10:00:00

-- 第二页（游标分页）
SELECT * FROM orders 
WHERE create_time < '2024-01-01 10:00:00'
ORDER BY create_time DESC 
LIMIT 10;
-- 直接定位到起点，无需扫描前面的数据

优化：使用主键游标

-- 第一页
SELECT * FROM orders 
ORDER BY create_time DESC, id DESC 
LIMIT 10;
-- 记录最后一条：create_time=2024-01-01 10:00:00, id=12345

-- 第二页
SELECT * FROM orders 
WHERE (create_time, id) < ('2024-01-01 10:00:00', 12345)
ORDER BY create_time DESC, id DESC 
LIMIT 10;

适用场景：

优缺点：

方案 3：限制最大页数

-- 应用层限制最大页码
$maxPage = 100;  // 最多允许 100 页
$page = min($page, $maxPage);
$offset = ($page - 1) * $pageSize;

SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT $offset, $pageSize;

适用场景：

方案 4：使用 ID 范围分页

-- 适用于连续自增主键

-- 第一页
SELECT * FROM orders 
WHERE id > 0 
ORDER BY id ASC 
LIMIT 10;
-- 最大 ID: 100

-- 第二页
SELECT * FROM orders 
WHERE id > 100 
ORDER BY id ASC 
LIMIT 10;
-- 最大 ID: 200

-- 第三页
SELECT * FROM orders 
WHERE id > 200 
ORDER BY id ASC 
LIMIT 10;

适用场景：

方案 5：禁止深度分页

-- 应用层直接拒绝深度分页请求
if ($offset > 10000) {
    // 返回错误提示
    return error("最多查看前 10000 条记录");
}

SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT $offset, $pageSize;

适用场景：

方案 6：使用搜索引擎

-- MySQL 分页性能瓶颈
SELECT * FROM products 
WHERE name LIKE '%手机%' 
ORDER BY sales DESC 
LIMIT 100000, 10;
-- 耗时：10s+

-- Elasticsearch 分页
GET /products/_search
{
  "query": {
    "match": { "name": "手机" }
  },
  "sort": [{ "sales": "desc" }],
  "from": 100000,
  "size": 10
}
-- 耗时：100ms

适用场景：

不同场景分页优化

场景 1：单表排序分页

-- 创建索引
CREATE INDEX idx_create_time ON orders(create_time DESC);

-- 优化前
SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 1000000, 10;

-- 优化 1：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id;

-- 优化 2：游标分页
SELECT * FROM orders 
WHERE create_time < '2024-01-01 10:00:00'
ORDER BY create_time DESC 
LIMIT 10;

场景 2：JOIN 分页

-- 优化前：JOIN + 深度分页
SELECT o.*, u.name FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
ORDER BY o.create_time DESC 
LIMIT 1000000, 10;

-- 优化 1：延迟关联 + JOIN
SELECT o.*, u.name FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id
LEFT JOIN users u ON o.user_id = u.id;

-- 优化 2：先查 ID，再 JOIN
-- 步骤 1：获取 ID 列表
SELECT id FROM orders 
ORDER BY create_time DESC 
LIMIT 1000000, 10;
-- 结果：[1000001, 1000002, ..., 1000010]

-- 步骤 2：JOIN 查询
SELECT o.*, u.name FROM orders o
LEFT JOIN users u ON o.user_id = u.id 
WHERE o.id IN (1000001, 1000002, ..., 1000010)
ORDER BY o.create_time DESC;

场景 3：WHERE 条件分页

-- 创建复合索引
CREATE INDEX idx_status_time ON orders(status, create_time DESC);

-- 优化前
SELECT * FROM orders 
WHERE status = 1 
ORDER BY create_time DESC 
LIMIT 1000000, 10;

-- 优化 1：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE status = 1 
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id;

-- 优化 2：游标分页
SELECT * FROM orders 
WHERE status = 1 AND create_time < '2024-01-01 10:00:00'
ORDER BY create_time DESC 
LIMIT 10;

场景 4：GROUP BY 分页

-- 优化前：GROUP BY + 深度分页
SELECT user_id, COUNT(*) AS order_count
FROM orders 
GROUP BY user_id 
ORDER BY order_count DESC 
LIMIT 100000, 10;

-- 优化 1：子查询 + 延迟关联
SELECT t.user_id, t.order_count FROM (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders 
    GROUP BY user_id
) t
ORDER BY t.order_count DESC 
LIMIT 100000, 10;

-- 优化 2：物化视图
CREATE TABLE user_order_stats (
    user_id BIGINT PRIMARY KEY,
    order_count INT,
    INDEX idx_count (order_count DESC)
);

-- 定期刷新
INSERT INTO user_order_stats 
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
ON DUPLICATE KEY UPDATE order_count = VALUES(order_count);

-- 查询物化视图
SELECT user_id, order_count FROM user_order_stats 
ORDER BY order_count DESC 
LIMIT 100000, 10;

场景 5：LIKE 查询分页

-- 优化前：LIKE + 分页
SELECT * FROM products 
WHERE name LIKE '%手机%' 
LIMIT 100000, 10;
-- 全表扫描，性能极差

-- 优化 1：全文索引
ALTER TABLE products ADD FULLTEXT INDEX idx_name_fulltext(name);
SELECT * FROM products 
WHERE MATCH(name) AGAINST('手机') 
LIMIT 100000, 10;

-- 优化 2：Elasticsearch（推荐）
-- 使用 ES 处理 LIKE 查询 + 分页

场景 6：多条件排序分页

-- 创建复合索引
CREATE INDEX idx_status_time_id ON orders(status, create_time DESC, id DESC);

-- 优化前
SELECT * FROM orders 
WHERE status IN (1, 2, 3) 
ORDER BY create_time DESC, id DESC 
LIMIT 1000000, 10;

-- 优化：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE status IN (1, 2, 3) 
    ORDER BY create_time DESC, id DESC 
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id;

分页性能对比

不同方案性能对比

-- 测试数据：orders 表 1000 万行

-- 方案 1：普通 LIMIT
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;
-- 耗时：5000ms

-- 方案 2：延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 10) tmp ON o.id = tmp.id;
-- 耗时：50ms

-- 方案 3：游标分页
SELECT * FROM orders WHERE create_time < '2024-01-01' ORDER BY create_time DESC LIMIT 10;
-- 耗时：5ms

-- 性能对比：
-- 普通 LIMIT: 5000ms (基准)
-- 延迟关联：50ms (100 倍提升)
-- 游标分页：5ms (1000 倍提升)

不同页码性能对比

-- 页码对性能的影响（orders 表 1000 万行）

-- 第 1 页
LIMIT 0, 10;  -- 10ms

-- 第 1000 页
LIMIT 9990, 10;  -- 50ms

-- 第 10000 页
LIMIT 99990, 10;  -- 500ms

-- 第 100000 页
LIMIT 999990, 10;  -- 5000ms

-- 第 1000000 页
LIMIT 9999990, 10;  -- 50000ms (50 秒)

应用层实现

Java 实现

// 游标分页实现
public class CursorPagination {
    
    public List<Order> getPage(OrderCursor cursor, int limit) {
        StringBuilder sql = new StringBuilder(
            "SELECT * FROM orders WHERE "
        );
        
        if (cursor == null) {
            // 第一页
            sql.append("1=1");
        } else {
            // 后续页
            sql.append("(create_time, id) < (:createTime, :id)");
        }
        
        sql.append(" ORDER BY create_time DESC, id DESC LIMIT :limit");
        
        Query query = entityManager.createNativeQuery(sql.toString());
        if (cursor != null) {
            query.setParameter("createTime", cursor.getCreateTime());
            query.setParameter("id", cursor.getId());
        }
        query.setParameter("limit", limit);
        
        return query.getResultList();
    }
    
    // 返回游标
    public OrderCursor getNextCursor(List<Order> orders) {
        if (orders.isEmpty()) {
            return null;
        }
        Order last = orders.get(orders.size() - 1);
        return new OrderCursor(last.getCreateTime(), last.getId());
    }
}

// 游标对象
@Data
@AllArgsConstructor
public class OrderCursor {
    private Date createTime;
    private Long id;
}

PHP 实现

// 延迟关联分页
class OrderService {
    
    public function getOrders($page, $pageSize = 10) {
        // 限制最大页码
        $maxPage = 100;
        $page = min($page, $maxPage);
        
        $offset = ($page - 1) * $pageSize;
        
        // 延迟关联
        $sql = "SELECT o.* FROM orders o
                INNER JOIN (
                    SELECT id FROM orders 
                    ORDER BY create_time DESC 
                    LIMIT :offset, :limit
                ) tmp ON o.id = tmp.id";
        
        return DB::query($sql, ['offset' => $offset, 'limit' => $pageSize]);
    }
    
    // 游标分页
    public function getOrdersByCursor($cursor, $limit = 10) {
        $sql = "SELECT * FROM orders";
        $params = ['limit' => $limit];
        
        if ($cursor) {
            $sql .= " WHERE create_time < :createTime";
            $params['createTime'] = $cursor['createTime'];
        }
        
        $sql .= " ORDER BY create_time DESC LIMIT :limit";
        
        return DB::query($sql, $params);
    }
}

Go 实现

// 游标分页
type OrderCursor struct {
    CreateTime time.Time
    ID         int64
}

func GetOrders(cursor *OrderCursor, limit int) ([]Order, error) {
    query := "SELECT * FROM orders"
    args := make([]interface{}, 0)
    
    if cursor != nil {
        query += " WHERE (create_time, id) < (?, ?)"
        args = append(args, cursor.CreateTime, cursor.ID)
    }
    
    query += " ORDER BY create_time DESC, id DESC LIMIT ?"
    args = append(args, limit)
    
    rows, err := db.Query(query, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var orders []Order
    for rows.Next() {
        var order Order
        rows.Scan(&order)
        orders = append(orders, order)
    }
    
    return orders, nil
}

最佳实践总结

分页优化选择指南

1. 浅分页（offset < 1000）：
   → 普通 LIMIT

2. 深度分页（offset >= 1000）：
   → 延迟关联

3. 无限滚动/日志查询：
   → 游标分页

4. 后台管理系统：
   → 限制最大页数（如 100 页）

5. 复杂查询 + 分页：
   → Elasticsearch

6. 主键连续自增：
   → ID 范围分页

索引设计原则

-- 排序字段必须有索引
CREATE INDEX idx_create_time ON orders(create_time DESC);

-- 延迟关联需要覆盖索引
CREATE INDEX idx_create_time_id ON orders(create_time DESC, id);

-- WHERE + ORDER BY 使用复合索引
CREATE INDEX idx_status_time ON orders(status, create_time DESC);

性能监控

-- 慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看分页相关慢查询
SELECT query, exec_time 
FROM mysql.slow_log 
WHERE query LIKE '%LIMIT%';

-- 实时分页性能
EXPLAIN SELECT * FROM orders 
ORDER BY create_time DESC 
LIMIT 1000000, 10;

参考资料

分享这篇文章到:
分享到QQ 分享到微博 分享文章到Email
上一篇文章
Helm Chart 打包
下一篇文章
Spring Boot CI/CD 流水线实战