limit 1000000,0加载很慢如何优化？

一、问题分析

在MySQL中，limit ${offset},${row}表示查询前面offset+row条记录，然后抛弃前offset条记录，读取后row条记录，所以offset越大，偏移量越大，查询性能也就越差

二、优化方案

优化前：select * from user limit 1000000,10;

• ID连续优化：如果ID连续且不会中断，可以通过>运算符过滤

  -- 优化后
  select * from user where id > 1000000 limit 10;

• order by优化：结合order by使用，MySQL会找到指定行数的排序结果后就停止排序，而不是对整个结果进行排序，如果用的是主键ID，效率会更快

  -- 优化后
  select * from user order by id limit 1000000,10;

• 子查询优化：如果存在主键ID，可以先通过子查询返回的主键ID作为过滤条件，再进行回表查询（这种方式可以减少回表次数）

  select * from user
  where id >=
  (select id from user limit 1000000,1)
  limit 10;

• 上下文优化：如果业务场景允许，可以记录上一页分页的最后一个主键ID，然后作为查询条件去进行分页

  select * from user
  where id >= ${max_id_in_last_page}
  limit 10;

• 设计优化：从业务场景来看，一般是不可能出现深度分页查询的，所以我们可以对分页参数进行校验，返回非法参数提示（如果真的存在这种场景，可以考虑使用es等组件提供大数量查询的支持）

三、Q&A

1、order by+limit查询时返回数据结果有时候跟单独使用order by查询时返回结果不同，是正常的吗？

是正常的，举个例子，比如以下两个查询的结果就可以不一样：

-- order by
mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

-- order by + limit
mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

分析：原因是MySQL是使用order by查询时，如果如果结果存在重复值，那么它会按照不同的执行计划可能返回不同的数据表现
解决方案：可以考虑使用用几个字段参与order by排序，减少组合数据重复的可能性，如

mysql> SELECT * FROM ratings ORDER BY category,id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+