MySQL超大分页怎么优化处理？limit 1000000,10 和 limit 10区别?覆盖索引、面试题

1. limit 100000,10 和 limit 10区别

LIMIT 100000, 10：
- 这个语句的意思是，从查询结果中跳过前100000条记录，然后返回接下来的10条记录。
- 这通常用于分页查询中，当你需要跳过大量的记录以获取后续的记录时。例如，如果你有一个包含大量数据的查询结果，你可能想要查看第100001页的数据，每页显示10条记录，这时就会使用这种形式的LIMIT子句。
LIMIT 10：
- 这个语句的意思是，返回查询结果的前10条记录。
- 这是一个非常常见的用法，用于限制查询结果的数量，特别是在你只需要一小部分数据时。例如，你可能只想查看表中的前10条记录，或者在进行测试时限制返回的数据量。

覆盖索引加子查询

优化思路：分页查询时通过创建覆盖索引提高性能，再通过覆盖索引加子查询的形式进行优化

举例：

假设有一个 users 表，结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP
);

我们想要获取从第 1000001 条到第 1000010 条记录的用户名字（即分页）

优化前的 SQL 查询

SELECT name FROM users
ORDER BY id
LIMIT 1000000, 10;

全表扫描：这个查询没有使用子查询，因此 MySQL 必须从第一条记录开始，扫描直到跳过前 1000000 条记录，然后返回接下来的 10 条记录。

性能问题：当表中的记录数非常大时，这种方式会导致显著的性能下降，因为数据库需要遍历大量的记录，导致高的 IO 开销。

优化后的 SQL 查询

-- 创建覆盖索引，包含需要查询的字段(id和name)

CREATE INDEX idx_id_name ON users(id, name);

然后使用子查询优化

SELECT name FROM users
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM users ORDER BY id LIMIT 1000000, 10
    ) AS temp
);

内部子查询：SELECT id FROM users ORDER BY id LIMIT 1000000, 10

外部查询：SELECT name FROM users WHERE id IN (...)

覆盖索引的使用

覆盖索引：在 CREATE INDEX idx_id_name ON users(id, name); 中创建的索引包含了查询需要的所有列 id 和 name。
由于外部查询只选择了 name 列，且查询条件中使用了 id，MySQL 可以直接从索引中获取 id 和 name，避免了对 users 表的回表操作。