1. limit分页的原理

先来看一下分页的基本原理

mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20
***************** 1. row **************
id: 1
select_type: SIMPLE
table: message
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 10020
Extra:
1 row in set (0.00 sec)

对上面的mysql语句说明：limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。

2. 优化方法

2.1 最大ID法

举个例子，我查询第一页的时候是limit 0,10 查询到的最后一条id是10，那么下一页的查询只需要查询id大于10的19条数据即可。

explain select * from user where id > 4000000  limit 10;

es解决mysql大数据分页 mysql大数据分页查询_分表

我们看到rows=1949780，这个表示可能扫描了这么多行，这个行数是因为扫描id>400w的记录，后面还有两百万，而这里表示可能扫描了1949780行，但是由于limit 10的存在，所以扫描了10行之后就停止扫描了，我们也可以对比一下使用这种方式和直接使用limit 4000000，10的效率，千差万别。但是这种比较局限，只能适用于自增组件，uuid生成的主键这种方式不适用。

2.2 BETWEEN … AND

select * from user where id BETWEEN 4000000 and 4000010

es解决mysql大数据分页 mysql大数据分页查询_分表_02

es解决mysql大数据分页 mysql大数据分页查询_es解决mysql大数据分页_03

这种方式也只能适用于自增主键，并且id没有断裂，否者不推荐这种方式，我们发现使用BETWEEN AND的时候查询出来11条记录，也就是说BETWEEN AND包含了两边的边间条件。使用的时候需要特别注意一下。

2.3 利用覆盖索引优化

我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

2.3.1 只有主键id

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。

select id from product limit 866613

这次我们直接查询最后一页的数据（利用覆盖索引，只包含id列）耗时0.2秒，相对于查询了所有列的37.44秒，提升了大概100多倍的速度。

那么如果要查询所有列有两种方法，

  • id>=的形式：SELECT * FROM product WHERE ID >= (select id from product limit 866613, 1) limit 20
  • 利用join：SELECT * FROM product a INNER JOIN (select id from product limit 866613, 20) b ON  = 

2.3.2 利用复合索引

利用主键id虽然查询效率很高，但是通常我们的sql是带有各种查询条件的。假设有个数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text，id 是主键，vtype是tinyint，vtype有索引。

当有10万级以上的数据量时，执行 select id from collect where vtype=1 order by id limit 90000,10 时，虽然vtype字段有索引，大概耗时还是会在8~9秒。

这里我们需要设计一个复合索引cmp_idx(vtype, id)，再次执行select id from collect where vtype=1 limit 90000,10;耗时会降低到大概0.04秒左右！

如果对于有where 条件，又想走索引用limit的，必须设计一个复合索引，将where条件的字段放第一位，limit用到的主键放第2位，而且只能select 主键！

2.4 分表

MySQL推荐一张表的存储不要超过500w数据。如果在100万以内的数据，利用2.3.2的复合索引方式，还可以支撑limit分页查询。300万以内就很勉强，需要进行分表设计。

  • 按时间分表：对于订单流水数据，可以考虑按时间分表，因为通常用户对最近一定时间内的数据比较感兴趣，可能经常查询使用，而超过一定时间的历史数据就很少查询。按时间分表，可以排除大量的历史数据，提升查询效率。
  • 垂直分表：对于表的字段超多，而且还有很多text类型的字段，这个时候我们可以将占用空间比较小的字段分在一张表，占用空间比较大的字段分在另一张表，两张表一一关联，这样，查询的时候就会快很多了。
  • 水平分表：按一定分表规则将数据拆分到多个表里，每个表数据量减少，自然查询效率就提升了。