在MySQL数据库中，面对千万级别的数据量，分页查询是一个重要的性能挑战。传统的LIMIT分页方式在数据量巨大时，尤其是进行深度分页（比如跳过大量行后取少量数据），会导致查询效率低下，因为MySQL需要扫描并忽略大量的行来找到需要的数据。这不仅消耗CPU资源，还会增加磁盘I/O负担，进而影响系统的整体性能。
我们来看一个具体的案例。假设有一个电商订单详情表`cps_user_order_detail`，包含用户ID、订单ID、商品ID和下单时间等字段，表中存储了120万条数据。当需要按照下单时间倒序分页展示用户的订单详情时，简单的LIMIT分页查询会出现问题。例如，从第100条记录开始分页与从第100万条记录开始分页，查询速度会有显著差异，后者可能因大量数据的回表操作和文件排序（filesort）而变得非常慢。
针对这个问题，我们可以采取以下两种优化策略：
1. **利用主键索引优化**：
可以通过查询上一页最后一个记录的主键ID（maxId），然后结合其他条件（如下单时间）来优化查询。查询语句改为：
```sql
SELECT * FROM cps_user_order_detail d
WHERE d.id > #{maxId} AND d.order_time > '2020-8-5 00:00:00'
ORDER BY d.order_time LIMIT 6;
```
这样，MySQL只需要查找大于maxId的记录，再根据下单时间排序，避免了大量行的扫描。
2. **使用ROW_NUMBER()函数或用户自定义变量**：
MySQL 8.0引入了窗口函数ROW_NUMBER()，可以为每行分配一个行号，然后基于行号进行分页。而在较旧的版本中，可以使用用户自定义变量模拟这一功能。这种方法可以有效地减少数据扫描范围。
例如，使用窗口函数的示例：
```sql
WITH ordered_rows AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY order_time DESC) as row_num
FROM cps_user_order_detail
WHERE user_id = #{userId}
)
SELECT * FROM ordered_rows
WHERE row_num BETWEEN #{startRow} AND #{endRow};
```
这里，`#{startRow}`和`#{endRow}`分别是分页的起始和结束行号。
3. **数据归档和分区**：
对于历史数据，可以考虑定期归档，将长时间未更新的数据移动到单独的表或分区中，这样主表的大小会减少，分页查询会更快。同时，可以考虑按时间对表进行分区，如按年、月或日，进一步优化查询效率。
4. **优化索引**：
除了主键索引，还可以根据查询条件创建覆盖索引，使得MySQL可以直接在索引中完成查询，无需回表。例如，如果经常根据下单时间和用户ID进行查询，可以创建一个(`order_time`, `user_id`)的联合索引。
5. **减少返回字段**：
在不影响业务的情况下，尽量减少SELECT语句中选择的字段数量，降低数据传输和处理的成本。
6. **缓存和预加载**：
对于常用且计算成本高的分页结果，可以考虑缓存部分数据，或者采用预加载技术，提前加载临近的几页数据，减少实时查询的压力。
优化MySQL的千万级数据分页需要综合考虑索引设计、查询语句优化、数据结构调整等多个方面，通过这些方法，可以在保证查询效率的同时，提供良好的用户体验。在实际开发中，应根据具体场景选择合适的优化策略。
