mysql order by limit 排序分页数据重复问题

BUG总结 专栏收录该内容
132 篇文章 0 订阅

其实今天遇到的最大问题还是mysql order by limit 排序分页数据重复的问题,昨天晚上搞了3个小时都没处理,因为不管怎么查数据也总会重复,查询了好多,都反映说在 order by上加一个主键UUID唯一的排序,然后再分页就不会重复了,但是我试了不行,然后又有说,要加索引的,我就看了一下,这两个字段都加了索引了,然后没办法我就又加了个联合索引,把时间字段和唯一的UUID都加到索引中,然后再执行依旧是不行。
好崩溃,终于把迁移的程序写出来了,结果败到这个问题上。
之前迁移数据的时候其实并没有考虑分页问题,因为拿测试库测得,数据量少,一下就跑通了,然后把生产数据的备份拷了一下,又试的时候,程序连查询都没过,就提示,内存溢出,JVM无法处理等等。。我自己查了一下一共有70W条数据, 这样这个程序肯定是不满足的,连查询都没过,就算插入我设置的是一次1000条数据插入,但是都到不了这一步就报错了。。。
之前在这里插入图片描述现在肯定是不能用了,就想,那数据量大,查询过不了,那我何不设置创建时间为查询条件,1年1年的来,如果1年数据量还大的话,那我就半年半年的来吧,ennnn好像是没毛病,不过人工操作的步骤比较多,还容易出错,怎么办,那就试试分页吧,然后就出来上面的那个问题:mysql order by limit 排序分页数据重复问题
关于 这个问题讲的比较详细如下:

 参考链接:https://my.oschina.net/linxxbaobao/blog/1628027

然后其他大部分讲的都还是上面我所说的两种情况。
然后就寻求帮助,问了好多人后,都说,如果用uuid主键做排序的话,再分页肯定不可能的,就让我把SQL发给他们,看了一下,得出结论如下:
在这里插入图片描述
然后按照这个修改后,依旧还是重复,然后就又请教大佬
在这里插入图片描述
在这里插入图片描述
然后我又再程序中加了一步,去重的操作
在这里插入图片描述
再尝试依旧不行,还是主键重复问题,
在这里插入图片描述
对,肯定是我太着急了,一心都以为是数据重复的问题,没有想其他的,然后我就静了静心,加了处理保存了插入的id,然后再执行到这一步的时候,提示主键重复,我就拿程序重复的数据,开始查id,结果发现并未重复,好了,那就可以排除分页数据重复问题了

询问度娘以后,找到相似的问题如下


mysql主键设置成auto_increment时,进行并发性能测试出现主键反复Duplicate entry 'xxx' for key 'PRIMARY'
解决方法:
在my.cnf的[mysqld]片段中加入设置innodb_autoinc_lock_mode=0
同一时候注意调大jdbc的活跃链接数,如设置 jdbc.maxActive=300,由于设置innodb_autoinc_lock_mode=0可能导致链接过多。
注意,这样的方式仅仅须要在并发性能测试时设置,由于这样的方式在插入记录时需进行全表锁定,性能较差,平时生产环境中仅仅需使用默认的设置innodb_autoinc_lock_mode=1就可以,mysql的官方文档有说明:
1、innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
这样的方式就和mysql5.1.22曾经一样,这样的方式的特点就是“表级锁定”,并发性较差
2、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
这样的方式是新版本号中的默认方式,推荐使用,并发性相对较高,特点是“consecutive”,即保证同一条insert语句中新插入的auto_increment id都是连续的。
这样的模式下:
“Simple inserts”:直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,仅仅会将整个分配的过程锁住。
“Bulk inserts”:由于不能确定插入的数量,因此使用和曾经的模式同样的表级锁定。
“Mixed-mode inserts”:直接分析语句,获得最坏情况下须要插入的数量,然后一次性分配足够的auto_increment id,仅仅会将整个分配的过程锁住。须要注意的是,这样的方式下,会分配过多的id,而导致”浪费“。比方INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');会一次性的分配5个id,而无论用户是否指定了部分id;INSERT … ON DUPLICATE KEY UPDATE一次性分配,而无论将来插入过程中是否会由于duplicate key而只运行update操作。
注意:当master mysql版本号<5.1.22,slave mysql版本号>=5.1.22时,slave须要将innodb_autoinc_lock_mode设置为0,由于默认的 innodb_autoinc_lock_mode为1,对于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的运行结果不同,现实环境通常会使用INSERT … ON DUPLICATE KEY UPDATE。
3、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
这样的模式是来一个分配一个,而不会锁表,仅仅会锁住分配id的过程,和innodb_autoinc_lock_mode = 1的差别在于,不会预分配多个,这样的方式并发性最高。可是在replication中当binlog_format为statement-based时 (简称SBR statement-based replication)存在问题,由于是来一个分配一个,这样当并发运行时,“Bulk inserts”在分配的时会同一时候向其它的INSERT分配,会出现主从不一致(从库运行结果和主库运行结果不一样),由于binlog仅仅会记录开始的 insert id

可能是mysql机制吗?
不太清楚,因为我们是数据迁移为了数据完整性就拿原数据表中的主键UUID作为新的表主键UUID了,现在提示这个主键重复并且排查的日志中并未有重复数据,这反正就都是跟这个主键有问题,那我不用以前的UUID了,我重新生成一下试试
处理修改后,在执行,程序没问题了,开心,这样就解决了人工操作过多的问题,然后就看明天校验更多数据的时候,性能问题了。

  • 1
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值