MYSQL limt随着offset增大效率变低

最近在做一些大数据库的操作维护的过程中，发现limt随着offset也就是偏移量增大效率越来越慢，以前也发现过这个问题，但是数据量不大，所以就放那了，但是这次数据量有几个表是5000到一亿的数据量，查一次卡爆了有没有，好了废话不多说，让我们一起来探讨下。

 limt 是MySQL 提供的一个方便的分页查询的语句，使用起来也很方便

select * from A limt 5       #取A表中的前5条记录

select * from A limt 5,2    #从A表的第5条之后开始取两条记录

我们前面说的偏移量就是limit 的第一个参数，这个参数越大，执行效率越低，我们来试验下，我这边有一个表大概有5万多记录

SELECT *from `fa_user_prod_account_detail用户产品账户明细` LIMIT 10000,5 #受影响的行: 0时间: 0.005s
SELECT *from `fa_user_prod_account_detail用户产品账户明细` LIMIT 20000,5 #受影响的行: 0 时间: 0.013s
SELECT *from `fa_user_prod_account_detail用户产品账户明细` LIMIT 30000,5 #受影响的行: 0 时间: 0.017s
SELECT *from `fa_user_prod_account_detail用户产品账户明细` LIMIT 40000,5 #受影响的行: 0 时间: 0.024s
SELECT *from `fa_user_prod_account_detail用户产品账户明细` LIMIT 50000,5  #受影响的行: 0 时间: 0.035s

可见确实随着偏移量的增大查询的时间也逐步增大。这是因为MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，当offset特别大，然后单条数据也很大的时候，每次查询需要获取的数据就越多，自然就会很慢。那我们怎么优化这条SQL语句呢，说到查询，我们肯定会第一时间，想到索引，可是我们要怎么样使用索引呢？何况索引也不是越多越好，越多的话在修改和插入的时候会给sql带来更大的消耗这个时候我们会想到主键，因为每个表都会有一个主键，并且主键是默认的唯一索引。我们是不是可以先把要取的这五条的ID查出来，然后再根据ID去取它的值呢？有了思路，那样这就好办了。我们可以这样写：

select * from `fa_user_prod_account_detail用户产品账户明细` a  inner join (select b.ID from `fa_user_prod_account_detail用户产品账户明细` as b limit 30000,5) c on a.ID=c.ID;
或者
SELECT * FROM `fa_user_prod_account_detail用户产品账户明细`  WHERE ID >=(select  b.ID  from `fa_user_prod_account_detail用户产品账户明细` as b limit 30000, 1) limit 5

第一个是使用了内链接查询，第二个是子查询，其实也是变相的内链接查询，

不同之处是：

第一个先查出要找的五条数据的ID，然后根据5条ID，去查所有的的信息，

第二个是先查出第一条的ID，然后根据where 条件删选掉前面的偏移量，取5条数据。

第二条语句可以这么写：

SELECT * FROM `fa_user_prod_account_detail用户产品账户明细`  WHERE ID in (  SELECT c.ID from ( (select  b.ID  from `fa_user_prod_account_detail用户产品账户明细` as b limit 30000, 5))as c)

SELECT*FROM `fa_user_prod_account_detail用户产品账户明细` as a  WHERE EXISTS(SELECT c.ID from (select  b.ID  from `fa_user_prod_account_detail用户产品账户明细` as b limit 30000, 5 )as c WHERE a.ID=c.ID)

这样写的效率并不高因为in（里面的sql并不会用到索引），还有这个SQL语句子查询之所以嵌套了一个是因为limit不能使用在IN/ALL/ANY/SOME中

所以推荐内链接查询

select * from `fa_user_prod_account_detail用户产品账户明细` a  inner join (select b.ID from `fa_user_prod_account_detail用户产品账户明细` as b limit 30000,5) c on a.ID=c.ID;
或者
SELECT * FROM `fa_user_prod_account_detail用户产品账户明细`  WHERE ID >=(select  b.ID  from `fa_user_prod_account_detail用户产品账户明细` as b limit 30000, 1) limit 5

这两个查询都差不多，但是更推荐第一个

下面让我们看下执行的结果

select * from `fa_user_prod_account_detail用户产品账户明细` a  inner join (select b.ID from `fa_user_prod_account_detail用户产品账户明细` as b limit 10000,5) c on a.ID=c.ID;#时间: 0.004s
select * from `fa_user_prod_account_detail用户产品账户明细` a  inner join (select b.ID from `fa_user_prod_account_detail用户产品账户明细` as b limit 20000,5) c on a.ID=c.ID;#受影响的行: 0时间: 0.004s
select * from `fa_user_prod_account_detail用户产品账户明细` a  inner join (select b.ID from `fa_user_prod_account_detail用户产品账户明细` as b limit 30000,5) c on a.ID=c.ID;#受影响的行: 0时间: 0.006s
select * from `fa_user_prod_account_detail用户产品账户明细` a  inner join (select b.ID from `fa_user_prod_account_detail用户产品账户明细` as b limit 40000,5) c on a.ID=c.ID;#受影响的行: 0时间: 0.008s
select * from `fa_user_prod_account_detail用户产品账户明细` a  inner join (select b.ID from `fa_user_prod_account_detail用户产品账户明细` as b limit 50000,5) c on a.ID=c.ID;#受影响的行: 0时间: 0.007s

可以看到效率确实提升了不止一点点