1. limit 100000,10 和 limit 10区别
-
LIMIT 100000, 10
:- 这个语句的意思是,从查询结果中跳过前100000条记录,然后返回接下来的10条记录。
- 这通常用于分页查询中,当你需要跳过大量的记录以获取后续的记录时。例如,如果你有一个包含大量数据的查询结果,你可能想要查看第100001页的数据,每页显示10条记录,这时就会使用这种形式的
LIMIT
子句。
-
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
表的回表操作。