mysql count分页

-- 查询第一页，每页显示10条记录
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;

-- 查询第二页，每页显示10条记录
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

-- 查询第一页，每页显示10条记录，假设游标为order_date
SET @page_size = 10;
SET @page_number = 1;
SET @offset = (@page_number - 1) * @page_size;

SELECT * FROM orders 
WHERE order_date > (SELECT MIN(order_date) FROM orders ORDER BY order_date LIMIT @offset, 1) 
ORDER BY order_date 
LIMIT @page_size;

-- 使用索引优化查询
CREATE INDEX idx_order_date ON orders(order_date);

-- 查询第一页，每页显示10条记录
SELECT * FROM orders 
WHERE order_date > (SELECT MIN(order_date) FROM orders ORDER BY order_date LIMIT @offset, 1) 
ORDER BY order_date 
LIMIT @page_size;

-- 使用乐观锁
SELECT * FROM orders 
WHERE order_date > (SELECT MIN(order_date) FROM orders ORDER BY order_date LIMIT @offset, 1) 
AND version = (SELECT version FROM orders ORDER BY order_date LIMIT @offset, 1) 
ORDER BY order_date 
LIMIT @page_size;