mysql千万数据查询分页

-- 创建索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查询
SELECT name, age FROM users WHERE name = '张三' ORDER BY id LIMIT 10;

-- 查询第10页，每页10条记录
SELECT * FROM users WHERE id > 90 ORDER BY id LIMIT 10;

-- 查询第10页，每页10条记录
SELECT * FROM (
    SELECT * FROM users ORDER BY id LIMIT 90, 10
) AS subquery;

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);

-- 插入示例数据
INSERT INTO users (name, age) VALUES ('张三', 25), ('李四', 30), ('王五', 35);

-- 使用覆盖索引查询
SELECT name, age FROM users WHERE name = '张三' ORDER BY id LIMIT 10;

-- 使用基于游标的分页查询
SELECT * FROM users WHERE id > 90 ORDER BY id LIMIT 10;

-- 使用子查询优化分页
SELECT * FROM (
    SELECT * FROM users ORDER BY id LIMIT 90, 10
) AS subquery;