Mysql--实战篇--大数据量表的分页优化（自增长主键，子查询主键主查询全部，查询条件加索引，覆盖索引等）

当Mysql数据表存储大量数据时（百万级别数据），分页查询的性能问题是一个常见的挑战。特别是当使用LIMIT和OFFSET时，随着OFFSET的增加，查询性能会显著下降。原因在于MySQL需要扫描并跳过前面的行，这会导致I/O操作和CPU使用率增加。
OFFSET是导致分页查询变慢的主要原因之一。随着OFFSET的增大，MySQL需要扫描并跳过越来越多的行，这会导致查询时间线性增长。因此，应该尽量避免使用OFFSET。

1、索引优化

对于大数据量的分页查询，建议使用表的主键（如id）或唯一列来进行分页。通过这种方式，MySQL可以直接从指定的记录开始读取，而不需要扫描和跳过前面的行。
这就要求主键列或者唯一的列不为空，且是连续的整数最合适。

示例：
假设我们有一个orders表，包含大量订单数据。我们希望每次返回10条记录，并且从上次查询的结果之后继续获取下一页的数据。
sql示例：

-- 第一次查询（获取第 1-10 条记录）
SELECT order_id, order_date, amount 
FROM orders 
WHERE user_id = 123 
ORDER BY order_id 
LIMIT 10;-- 下一页查询（从上次查询的最大order_id开始）
SELECT order_id, order_date, amount 
FROM orders 
WHERE user_id = 123 
AND order_id > 1000  -- 假设上一页的最大order_id是1000
ORDER BY order_id 
LIMIT 10;

优点：

高效：MySQL可以直接从指定的order_id开始读取，而不需要扫描和跳过前面的行。
可扩展：即使数据量非常大，查询性能也不会随着页码的增加而显著下降。

注意事项：

确保order_id列上有索引，以便查询能够快速定位到指定的记录。
如果order_id不是唯一的，或者有重复值，可以考虑使用复合条件（如order_id和created_at）来确保唯一性。

2、覆盖索引

覆盖索引是指索引中包含了查询所需的所有列，这样查询可以直接从索引中获取数据，而不需要访问表的数据页。对于分页查询，覆盖索引可以显著减少I/O操作，提升查询性能。

示例：
假设我们经常对orders表进行分页查询，并且每次都查询order_id、order_date和amount列。我们可以在这些列上创建一个组合索引。
sql示例：

CREATE INDEX idx_order_id_date_amount ON orders (order_id, order_date, amount);

优点：

减少I/O操作：查询可以直接从索引中获取所有需要的数据，而不需要访问表的数据页。
提高查询速度：覆盖索引可以显著加快分页查询的速度，尤其是在数据量较大的情况下。

3、延迟关联（Deferred Join）

对于多表联合查询，先查询主键集合，再根据主键查询完整数据。

sql示例：

第一步：
select id from articles order by id limit 100000, 10;
第二步：
select * from articles where id in （主键集合）;

优点：
减少数据扫描量，适用于多表复杂查询。仅查询id不会回表查询，性能相对很快。

缺点：
需要多次查询。

4、伪分页

当翻页至极深处时，可以限制查询范围，提示用户返回首页或前几页。

sql示例：

select * from articles order by id limit 1000;

优点：
用户体验较好，避免性能瓶颈。

缺点：
牺牲极深分页的需求。

5、最终优化方案

（1）、自增长主键

mysql推荐使用自增id作为数据表的主键，不要使用uuid作为数据表的主键。使用uuid作为主键不仅会带来性能上的问题，在查询时也会遇到问题。因为在使用select id from table limit 10000,10 查询id数据时，默认是对id进行排序，返回的是排序后的id结果，如果我们想按插入顺序查询结果，这样查询出来的结果就与我们的需求不相符。
Mysql表的数据行是按照聚簇索引（通常是主键）的顺序存储。uuid则是无序的会增加查询和插入数据的消耗。

（2）、覆盖索引

仅返回查询必要的字段，如果字段少的话，可以创建组合索引实现覆盖索引的效果，避免回表查询。

（3）、子查询

使用子查询仅查询需要的主键id，在对目标id进行查询必要的字段。

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

（4）、包含方法的优化

对于查询中包含统计函数的方法，性能上从高到低count()≈count(1)>count(id)>count(field)，因为mysql()做过优化，会自动选择成本最小的方式查询,前提是只有在Mysql5.6之后的版本才有优化。

综上所述，在查询条件的列上添加索引，自增长主键和子查询的方式是优化大数量表分页查询慢问题的必选方案。如果返回的数量列较少，可以考虑使用覆盖索引进行优化。如果查询包含方法，可以考虑多个方法之间性能的问题作出最优的选择。

乘风破浪会有时，直挂云帆济沧海！！！