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

1. limit 100000,10 和 limit 10区别

1. LIMIT 100000, 10：

   • 这个语句的意思是，从查询结果中跳过前100000条记录，然后返回接下来的10条记录。
   • 这通常用于分页查询中，当你需要跳过大量的记录以获取后续的记录时。例如，如果你有一个包含大量数据的查询结果，你可能想要查看第100001页的数据，每页显示10条记录，这时就会使用这种形式的LIMIT子句。

2. LIMIT 10：

   • 这个语句的意思是，返回查询结果的前10条记录。
   • 这是一个非常常见的用法，用于限制查询结果的数量，特别是在你只需要一小部分数据时。例如，你可能只想查看表中的前10条记录，或者在进行测试时限制返回的数据量。

2. 超大分页优化思路

覆盖索引加子查询

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

举例：

假设有一个 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

• 这个子查询的作用是从 users 表中获取 id 从 1000001 到 1000010 的记录。
• 这里会使用到 id 的索引来快速定位到对应的位置及1000001，而不会从头开始遍历所有记录

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

• 外部查询使用内层子查询的结果，获取这些 id 对应的用户 name。

覆盖索引的使用

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

3.面试题

3.1 什么是覆盖索引?

3.2 MySQL超大分页怎么处理?