如何实现千万级数据表的快速分页查询

一、模拟数据

为了解决这个问题，我首先需要模拟生成三千万条数据，本来是想用Python生成的，但是最后发现生成效率太低了，最后我改用“随机生成+批量插入”的方案，来生成大量模拟数据。

具体实现思路如下：

DELIMITER //CREATE PROCEDURE GenerateRandomData(IN target_count INT, IN batch_size INT)
BEGINDECLARE current_count INT DEFAULT 0;DECLARE num_batches INT DEFAULT target_count DIV batch_size;DECLARE remainder INT DEFAULT target_count MOD batch_size;DECLARE i INT DEFAULT 0;-- 插入完整批次WHILE i < num_batches DOINSERT INTO orders (customer_id, order_date, origin, destination, status)SELECTFLOOR(RAND() * 1000000) + 1,CURDATE() - INTERVAL FLOOR(RAND() * 365 * 3) DAY,SUBSTRING(SHA(RAND()), 1, 10),SUBSTRING(SHA(RAND()), 1, 10),ELT(FLOOR(RAND() * 4) + 1, 'Pending', 'Shipped', 'Delivered', 'Cancelled')FROM(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t3,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t4,(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t5;SET i = i + 1;END WHILE;-- 插入剩余的记录IF remainder > 0 THENINSERT INTO orders (customer_id, order_date, origin, destination, status)SELECTFLOOR(RAND() * 1000000) + 1,CURDATE() - INTERVAL FLOOR(RAND() * 365 * 3) DAY,SUBSTRING(SHA(RAND()), 1, 10),SUBSTRING(SHA(RAND()), 1, 10),ELT(FLOOR(RAND() * 4) + 1, 'Pending', 'Shipped', 'Delivered', 'Cancelled')FROM(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1LIMIT remainder;  -- 使用LIMIT来插入剩余的记录数END IF;
END //DELIMITER ;

生成数据

CALL GenerateRandomData(32420006, 3125);

一共花了510秒，生成了三千多万条数据，相比于使用Python生成，效率还是挺高的。

SELECT COUNT(*) FROM orders

二、正常分页查询

如果直接使用正常的分页查询查询最后一页，总用时花费了22.7秒

SELECT * FROM orders
ORDER BY order_id
LIMIT 10 OFFSET 32402071;

为什么简单的分页查询会这么慢？

当我们使用 LIMIT 和 OFFSET 进行分页时，数据库需要扫描和跳过大量的行来找到偏移位置。在上面的代码中，数据库必须扫描超过 3240 万行才能返回 10 行。正是因为这个，导致我们的分页查询变得非常慢。

三、优化方法

要优化分页查询的速度，关键在于解决数据库为找到偏移位置而进行的大量扫描问题。 我们可以通过减少数据扫描量和优化数据访问路径来有效提升分页查询的性能。

1、利用主键的顺序性

通过利用主键的有序性来避免使用 OFFSET，可以有效提高查询效率。我们先按主键 id 排序，从表中跳过前面的记录，获取第 32402072 条记录的 id，并将其作为外层查询的基准起点。

SELECT order_id 
FROM orders 
ORDER BY order_id 
LIMIT 1 OFFSET 32402071

子查询返回第 32402072 条记录的 order_id，作为外层查询的起点。外层查询从这个 order_id 开始，选取所有大于或等于该 order_id 的记录，并按 order_id 升序排序。然后通过 LIMIT 10 限制结果，只返回从第 32402072 条记录开始的 10 条记录。

SELECT * 
FROM orders 
WHERE order_id >= (SELECT order_id FROM orders ORDER BY order_id LIMIT 1 OFFSET 32402071
)
ORDER BY order_id 
LIMIT 10;

如果MySQL版本是8.0以上，还可以这样写：

SELECT *
FROM orders
WHERE order_id IN (SELECT order_idFROM ordersORDER BY order_idLIMIT 10 OFFSET 32402071
);

2、使用分页表

创建一个临时表来存储分页查询的主键，然后再根据这些主键查询完整记录：

-- 创建一个临时表来存储分页的 order_id
CREATE TEMPORARY TABLE temp_order_ids AS
SELECT order_id
FROM orders
ORDER BY order_id
LIMIT 10 OFFSET 32402071;-- 使用临时表中的 order_id 来查询完整的记录
SELECT *
FROM orders
WHERE order_id IN (SELECT order_id FROM temp_order_ids);

如果是频繁使用分页查询，可以创建一个固定的表来存储分页的 id。

首先，创建一个固定表来存储分页的order_id：

CREATE TABLE order_page_ids (order_id INT PRIMARY KEY
);

在进行分页查询之前，将所需的 order_id 插入到固定表中：

INSERT INTO order_page_ids (order_id)
SELECT order_id
FROM orders
ORDER BY order_id
LIMIT 10 OFFSET 32402071;

使用固定表中的 order_id 来获取完整的订单记录：

SELECT *
FROM orders
WHERE order_id IN (SELECT order_id FROM order_page_ids);