mysql limit 分页数据丢失问题测试

背景

前几天看到有说mysql使用 limit 0,10 这种方式分页会丢失数据，有人质疑说不会，动手验证一下。

操作步骤

表结构如下：

create table `test`.`t_model`(  
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `uid` bigint COMMENT '业务主键',
  `modelid` varchar(50) COMMENT '字符主键',
  `modelname` varchar(50) COMMENT '名称',
  `desc` varchar(50) COMMENT '描述',
  primary key (`id`),
  UNIQUE index `uid_unique` (`uid`),
  key `modelid_index` (`modelid`) USING BTREE
) ENGINE=InnoDB charset=utf8 collate=utf8_bin;

构造测试数据语法如下：

drop procedure IF EXISTS u_head_and_low_pro;

delimiter $$
create procedure u_head_and_low_pro()
begin
  DECLARE n int DEFAULT 1;
  set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';
  set @exedata = '';

  WHILE n < 10001 DO
    set @exedata = concat(@exedata,"(",n,",","'id20170831",n,"','","name",n,"','","desc'",")");

    if n % 1000 = 0 
    then
      set @exesql = concat(@exesql,@exedata,";");

      prepare stmt from @exesql;
      execute stmt;
      DEALLOCATE prepare stmt;
      commit;  

      set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ';
      set @exedata = "";
    else
      set @exedata = concat(@exedata,',');
    end if;

    set n = n + 1;
  END WHILE;
end;$$ 
delimiter ;

查询语法如下：

-- 第一页
select * from t_model limit 0, 10;

-- 第二页
select * from t_model limit 10, 10;

-- 第三页
select * from t_model limit 20, 10;

-- 第四页
select * from t_model limit 30, 10;

-- 第五页
select * from t_model limit 40, 10;

在查询时，第一页是1~10，第二页是11~20，第四页时，查询结果为：
id uid modelid modelname desc

---

31      31  id2017083131  name31     desc    
32      32  id2017083132  name32     desc    
33      33  id2017083133  name33     desc    
34      34  id2017083134  name34     desc    
35      35  id2017083135  name35     desc    
36      36  id2017083136  name36     desc    
37      37  id2017083137  name37     desc    
38      38  id2017083138  name38     desc    
39      39  id2017083139  name39     desc    
40      40  id2017083140  name40     desc  

此时，查询第5页应该是 41~50。如果某人A在界面上查询数据，现在查询到第4页，在显示完第4页数据后，另外一个人B（或程序）将第11~20的数据删除掉了，A再翻页到第5页时，此时数据为：
id uid modelid modelname desc

---

51      51  id2017083151  name51     desc    
52      52  id2017083152  name52     desc    
53      53  id2017083153  name53     desc    
54      54  id2017083154  name54     desc    
55      55  id2017083155  name55     desc    
56      56  id2017083156  name56     desc    
57      57  id2017083157  name57     desc    
58      58  id2017083158  name58     desc    
59      59  id2017083159  name59     desc    
60      60  id2017083160  name60     desc   

也就是说，A在本次操作过程中，如果一直往下翻页到最后一页的话，41~50 这页的数据就无法看到，数据丢失了。
当然，如果刷新一下界面，重新翻页就没有问题了。

结论

如果分页查询的表的数据插入和删除等操作频繁的话，建议还是使用 limit 10 这种语法方式分页查询，可以提升性能，也更准确。