Mysql 分页查询优化
分页查询
在 mysql 中，分页查询主要通过使用 limit 关键字来实现。但是，不同的使用方法，效率却相差甚远。
假设 表 t 有主键 id，我们来看看不同执行方法的差异：
方法一、select * from t order by id limit m, n;
这种写法是能正确返回分页数据的，但是，它却不太适用于大表的查询，因为这个查询越到后面就会越慢。
我们看下它的查询过程： 查询 m+n 行记录，然后把前 m 条去掉，最后返回后 n 条。也就是说，虽然每次都只返回 n 条记录，但是每次查询都要查询 m+n 行数据。所以，对于大表的查询，越到后面 m 越大，要扫描的记录也就越多。
所以我们需要优化下这个多余的查询，即想办法做到不扫描前 m 行记录，只扫描后 n 行记录。我们看下怎么优化：
方法二、select * from t where id > maxId order by id limit n;
如果你能获取到查询的门槛值（上一次查询的最大 id），就可以直接定位到第 m 行记录，这样查询就只需要扫描 n 条记录了。
其实这个语句本身已经是实现分页查询 n 条数据的最优方案了，但是它却不一定能满足所有的业务需求。因为它每次查询都需要获得一个门槛值，也就是说它是 基于上一次的查询结果才能进行本次的查询，换句话说，它只适用于 连续查询 的场景，对于需要跳页的场景就实现不了了（不过这种需求应该很少）。
跳页：比如总共有 100 条数据，每页10 条，那就会分成 10 页返回，而客户端只需要第 1 页和第 10 页的数据，也就是需要从第 1 页跳跃到第 10 页。
那如果有跳页的需求，又该怎么实现呢？
方法三、 select * from t where id > maxId order by id limit m, n;
这个方法其实就是结合了方法一和方法二来满足跳页需求的。它通过获取到最近一次查询的最大 Id 值避免了从第一条记录开始扫描，但是如果跳的页数很大，比如有 100 页的记录，而客户端只需要第 1 页和第 100 页的数据，那在获取完第 1 页 数据后， 用该方法再来获取第 100 页的数据和方法一就没什么差别了。
革命尚未成功，继续优化：
方法四、select * from t where id > (select id from t order by id limit m, 1) limit n;
该方法先通过子查询获取到门槛值，然后再获取跳页的数据。虽然子查询也扫描了 m + 1 行数据，但毕竟只扫描了 id 字段而非全表，在 id 是索引字段的条件下，性能比方式 1 还是要好上不少。
总结一下：
在表数据量较小的情况下，直接使用方法一就可以了；如果表数据量比较大，查询也不存在跳页的需求，那就使用方法二；如果存在跳页，就需要根据跳页情况酌情使用方法三或方法四。
多说一句，现在 MyBatis 常用的分页插件 PageHelper 就是使用的方法一，所以在对大表做分页查询的时候，如果你发现效率低下，可能需要根据实际需求，自己采用其他方法来实现。
延伸
延伸一个问题，以上我们查询数据都在客户端做了接收，所以我们都会考虑客户端能接收的数据量，防止客户端发生 OOM，那你有没有想过一个问题，如果我客户端不做接收，那我直接执行一个全表查询语句，这会不会导致数据库服务器发生 OOM？
现在我们站在数据库服务器的角度来看下查询过程：接收客户端的查询请求，对表数据进行扫描，将满足条件的记录加入到结果集中， 然后返回给客户端。
从描述来看，好似这个结果集保存了所有要返回的数据。但如果每个 SQL 查询，数据库服务端都要保存一个全量数据的结果集，那服务端的内存肯定是不够用的，况且每个数据库还要服务成本上千个客户端，都不知道要死多少回了，对吧。
实际上，服务端并不需要保存一个完整的结果集。一个完整的查询流程是这样的：
获取一行，写到 net_buffer 中。这块内存的大小由参数 net_buffer_length 定义，默认是 16k。
重复获取行，直到 net_buffer 写满，调用网络接口发出去。
如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
从上面流程我们看到，一个查询使用的最大内存也就是 net_buffer_length 的大小，并不等于实际的数据量大小，也就是说，不管查询的数据量有多大，都不会造成服务端出现内存溢出的问题。
此外，受限于网络栈缓存，数据也不能一股脑地全部发出去，它是 边读边发 的，即整个过程是一个同步过程。
但是你要注意的是，查询数据是一个同步过程这一点是很重要的，因为它意味着客户端也可能造成大事务的发生。比如客户端执行了一个较大的查询，然后因为某种原因阻塞了，导致服务端这边的数据都停留在网络发送阶段的 socket send buffer 中，无法完成整个发送过程，这个查询也就成了一个大事务了。
好的，聊完了，感谢我亲爱的测试童鞋，T + 2 后我还是一条好汉。