1. 前言
前段时间，我司会员表有600多w数据分页查询特别的慢，于是乎我去explain了一下分页查询的sql是由于没有走索引，导致全表扫描，查询特别的慢，由于会员表有一个逻辑删除的字段，所以查询的时候sql条件会默认第一条件就是逻辑删除的字段，这个之前的文章也有分享过这个逻辑删除字段在查询第一个导致索引失效的坑，于是乎在之前加的索引上把逻辑删除字段加在索引第一列之后，分页查询sql就走了索引，没有之前那么慢；索引修改、表结构修改，字段类型修改还是要小心，有可能会导致锁表，把业务系统拖崩溃，所以在表数据量大的情况下还是要评估一下风险，在业务低峰期搞；言归正传，MybatisPlus分页查询在单表600w数据量下后面页查询时间会非常的慢，转圈圈要转20多秒甚至更多更多时间才有查询结果返回，这就是Mysql的Limit深度分页的问题，MyBatisPlus默认分页方式也是使用的是Limit语法，所以在单表大数据量的情况下会有深度分页问题。
深度分页问题不仅仅是Mysql有，还有ES也有，其它数据库也会有，ES的深度分页问题可以参看下面一篇文章：
https://zhuanlan.zhihu.com/p/5836392792. MyBatisPlus普通分页姿势
2.1 业务分页方法
@Overridepublic PagepageList(MemberV2PageDTO memberV2PageDTO) { log.info("pageList:{}", JSON.toJSONString(memberV2PageDTO));Pagepage = new Page<>(); page.setCurrent(memberV2PageDTO.getCurrent() != null ? memberV2PageDTO.getCurrent() : 1);page.setSize(memberV2PageDTO.getSize() != null ? memberV2PageDTO.getSize() : 100);QueryWrapperqueryWrappe = new QueryWrapper<>(); LambdaQueryWrapperlambda = queryWrappe.lambda(); lambda.eq(Member::getDeleted, 0);lambda.eq(Objects.nonNull(memberV2PageDTO.getUid()), Member::getMemberId, memberV2PageDTO.getUid()).eq(StringUtils.isNotBlank(memberV2PageDTO.getMobile()), Member::getMobile, memberV2PageDTO.getMobile()).eq(StringUtils.isNotBlank(memberV2PageDTO.getNickname()), Member::getNickname, memberV2PageDTO.getNickname()).eq(Objects.nonNull(memberV2PageDTO.getVip()), Member::getVip, memberV2PageDTO.getVip()).eq(Objects.nonNull(memberV2PageDTO.getStatus()), Member::getStatus, memberV2PageDTO.getStatus());if (CollectionUtil.isNotEmpty(memberV2PageDTO.getCredit())) {Listcredits = memberV2PageDTO.getCredit(); if (Objects.nonNull(credits.get(0)) && Objects.nonNull(credits.get(1))) {log.info("会员分页查询积分区间条件====>credit0:{},credit1:{}", credits.get(0), credits.get(1));lambda.between(Member::getCredit1, credits.get(0), credits.get(1));}}PagememberV2VOPage = memberMapper.pageList(page, queryWrappe); //数据逻辑处理memberV2VOPage.getRecords().forEach(m -> {String mobile = m.getMobile();if (StringUtils.isNotBlank(mobile) && mobile.length() == 11) {m.setHideMobile(mobile.substring(0, 3) + "****" + mobile.substring(7));}String credit4Str = m.getCredit4();if (StringUtils.isNotBlank(credit4Str)) {String vipTitle = memberExecutor.getVipTitle(BigDecimal.valueOf(Double.valueOf(credit4Str)));if (StringUtils.isNotBlank(vipTitle)) {m.setVipTitle(vipTitle);} else {m.setVipTitle("普通会员");}}if (Objects.nonNull(m.getStatus())) {if (MemberStatusEnum.DISABLE.getCode() == m.getStatus()) {m.setStatusStr(MemberStatusEnum.DISABLE.getName());} else if (MemberStatusEnum.NORMAL.getCode() == m.getStatus()) {m.setStatusStr(MemberStatusEnum.NORMAL.getName());} else if (MemberStatusEnum.FREEZE.getCode() == m.getStatus()) {m.setStatusStr(MemberStatusEnum.FREEZE.getName());} else if (MemberStatusEnum.CANCELLING_IN_PROGRESS.getCode() == m.getStatus()) {m.setStatusStr(MemberStatusEnum.CANCELLING_IN_PROGRESS.getName());} else if (MemberStatusEnum.CANCELLED.getCode() == m.getStatus()) {m.setStatusStr(MemberStatusEnum.CANCELLED.getName());}}});//PagememberV2VOPage = this.pageListV2(memberV2PageDTO); return memberV2VOPage;}
2.2 Mapper接口
("SELECT \n" +"mb.member_id AS uid,\n" +"mb.vip AS vip,\n" +"mb.mobile AS mobile,\n" +"mb.credit1 AS credit1,\n" +"mb.nickname AS nickname,\n" +"mb.credit4 AS credit4,\n" +"mb.avatar AS avatar,\n" +"mb.gender AS gender,\n" +"mb.unionid AS unionid,\n" +"mb.create_time AS createTime,\n" +"mb.register_time AS registerTime,\n" +"mb.status AS status\n" +"FROM\n" +"member mb ${ew.customSqlSegment} ")(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)/* 关联一对多查询@Results(value = {@Result(id = true, property = "uid", column = "memberId"),@Result(property = "fans", column = "memberId",many = @Many(select = "xxx.xxxxx.mapper.FansMapper.getByUid", fetchType = FetchType.LAZY)),@Result(property = "wxps", column = "memberId",many = @Many(select = "xxx.xxxxx.mapper.WxMappingMapper.getByUid", fetchType = FetchType.LAZY))})*/Page<MemberV2VO> pageList(("page") Page<Member> page, (Constants.WRAPPER) QueryWrapper<Member> queryWrapper);
以上是Mapper接口注解方式，也可以在xml中的select标签中写。
3. MyBatisPlus百万数据分页优化新姿势
3.1 姿势一
需要主键是自增有序，不一定要主键连续，但是必须要有序自增。
3.1.1 业务分页方法
public PagepageListV2(MemberV2PageDTO dto) { if (Objects.isNull(dto)) {throw new RuntimeException("参数不为空!");}if (Objects.isNull(dto.getCurrent())) {dto.setCurrent(1L);}if (Objects.isNull(dto.getSize())) {dto.setSize(100L);}if (CollectionUtil.isNotEmpty(dto.getCredit()) && dto.getCredit().size() == 2) {if (dto.getCredit().get(0) > dto.getCredit().get(1)) {dto.setCreditStart(dto.getCredit().get(1));dto.setCreditEnd(dto.getCredit().get(0));} else {dto.setCreditStart(dto.getCredit().get(0));dto.setCreditEnd(dto.getCredit().get(1));}}Pagepage = new Page<>(dto.getCurrent(), dto.getSize()); Long count = memberMapper.pageListV2Count(dto);if (count == 0) {page = new Page<>();return page;}page.setTotal(count);Long pages = (count + dto.getSize() - 1) / dto.getSize();page.setPages(pages);if (page.getCurrent() > pages) {page.setCurrent(pages);}Long pageSize = page.getSize();Long start = (page.getCurrent() - 1) * page.getSize();if (count < pageSize) {pageSize = count;}Long mod = count % pageSize;if (mod != 0 && Objects.equals(dto.getCurrent(), pages)) {pageSize = mod;}//Long end = page.getCurrent() * page.getSize();ListmemberV2VOList = memberMapper.pageListV2(start, pageSize, dto); page.setRecords(memberV2VOList);return page;}
3.1.2 Mapper接口
Long pageListV2Count( MemberV2PageDTO dto);ListpageListV2( Long start, Long pageSize, MemberV2PageDTO dto);
3.1.3 xml的sql写法
<select id="pageListV2Count" resultType="java.lang.Long">SELECTCOUNT(*)FROMdyict_member mbWHERE mb.deleted = 0<if test="dto != null and dto.uid != null ">AND mb.member_id = #{dto.uid}if><if test="dto != null and dto.mobile != null and dto.mobile.trim() neq '' ">AND mb.mobile = #{dto.mobile}if><if test="dto != null and dto.nickname != null and dto.nickname.trim() neq '' ">AND mb.nickname = #{dto.nickname}if><if test="dto != null and dto.creditStart !=null and dto.creditEnd !=null ">AND mb.credit1 >= #{dto.creditStart}AND mb.credit1 <= #{dto.creditEnd}]]>if><if test="dto != null and dto.vip !=null ">AND mb.vip = #{dto.vip}if><if test="dto != null and dto.status !=null ">AND mb.status = #{dto.status}if>ORDER BY mb.member_id DESCselect><select id="pageListV2" resultType="com.dy.member.vo.MemberV2VO">SELECTmb.member_id AS uid,mb.vip AS vip,mb.mobile AS mobile,mb.credit1 AS credit1,mb.nickname AS nickname,mb.credit4 AS credit4,mb.avatar AS avatar,mb.gender AS gender,mb.unionid AS unionid,mb.create_time AS createTime,mb.register_time AS registerTime,mb.STATUS AS STATUSFROM//需要注意下面这个是小于等于，主键排序就是DESCdyict_member mbWHERE mb.member_id (SELECTmember_idFROMdyict_memberORDER BYmember_id DESCLIMIT#{start},1)AND mb.deleted = 0<if test="dto != null and dto.uid != null ">AND mb.member_id = #{dto.uid}if><if test="dto != null and dto.mobile != null and dto.mobile.trim() neq '' ">AND mb.mobile = #{dto.mobile}if><if test="dto != null and dto.nickname != null and dto.nickname.trim() neq '' ">AND mb.nickname = #{dto.nickname}if><if test="dto != null and dto.creditStart !=null and dto.creditEnd !=null ">AND mb.credit1 >= #{dto.creditStart}AND mb.credit1 <= #{dto.creditEnd}]]>if><if test="dto != null and dto.vip !=null ">AND mb.vip = #{dto.vip}if><if test="dto != null and dto.status !=null ">AND mb.status = #{dto.status}if>ORDER BY mb.member_id DESCLIMIT #{pageSize};select>
pageListV2的条件查询需要放在外层，如果放在内层就会导致查询结果不正确。
3.2 姿势二
3.2.1 业务分页方法
public PagepageListV2(MemberV2PageDTO dto) { if (Objects.isNull(dto)) {throw new RuntimeException("参数不为空!");}if (Objects.isNull(dto.getCurrent())) {dto.setCurrent(1L);}if (Objects.isNull(dto.getSize())) {dto.setSize(100L);}if (CollectionUtil.isNotEmpty(dto.getCredit()) && dto.getCredit().size() == 2) {if (dto.getCredit().get(0) > dto.getCredit().get(1)) {dto.setCreditStart(dto.getCredit().get(1));dto.setCreditEnd(dto.getCredit().get(0));} else {dto.setCreditStart(dto.getCredit().get(0));dto.setCreditEnd(dto.getCredit().get(1));}}Pagepage = new Page<>(dto.getCurrent(), dto.getSize());Long count = memberMapper.pageListV2Count(dto);if(count == 0){page = new Page<>();return page;}page.setTotal(count);Long pages = (count + dto.getSize() - 1) / dto.getSize();page.setPages(pages);if (page.getCurrent() > pages) {page.setCurrent(pages);}Long start = (page.getCurrent() - 1) * page.getSize();//Long end = page.getCurrent() * page.getSize();ListmemberV2VOList = memberMapper.pageListV2(start, page.getSize(), dto); page.setRecords(memberV2VOList);return page;}
3.2.2 Mapper接口
Mapper接口同上姿势一
3.2.3 xml的sql写法
<select id="pageListV2Count" resultType="java.lang.Long">SELECTCOUNT(*)FROMdyict_member mbWHERE mb.deleted = 0<if test="dto != null and dto.uid != null ">AND mb.member_id = #{dto.uid}if><if test="dto != null and dto.mobile != null and dto.mobile.trim() neq '' ">AND mb.mobile = #{dto.mobile}if><if test="dto != null and dto.nickname != null and dto.nickname.trim() neq '' ">AND mb.nickname = #{dto.nickname}if><if test="dto != null and dto.creditStart !=null and dto.creditEnd !=null ">AND mb.credit1 >= #{dto.creditStart}AND mb.credit1 <= #{dto.creditEnd}]]>if><if test="dto != null and dto.vip !=null ">AND mb.vip = #{dto.vip}if><if test="dto != null and dto.status !=null ">AND mb.status = #{dto.status}if>ORDER BY mb.member_id DESCselect><select id="pageListV2" resultType="com.dy.member.vo.MemberV2VO">SELECTmb.member_id AS uid,mb.vip AS vip,mb.mobile AS mobile,mb.credit1 AS credit1,mb.nickname AS nickname,mb.credit4 AS credit4,mb.avatar AS avatar,mb.gender AS gender,mb.unionid AS unionid,mb.create_time AS createTime,mb.register_time AS registerTime,mb.STATUS AS STATUSFROMdyict_member mbinner join(SELECTmember_idFROMdyict_memberWHEREdeleted = 0<if test="dto != null and dto.uid != null ">AND member_id = #{dto.uid}if><if test="dto != null and dto.mobile != null and dto.mobile.trim() neq '' ">AND mobile = #{dto.mobile}if><if test="dto != null and dto.nickname != null and dto.nickname.trim() neq '' ">AND nickname = #{dto.nickname}if><if test="dto != null and dto.creditStart !=null and dto.creditEnd !=null ">AND credit1 >= #{dto.creditStart}AND credit1 <= #{dto.creditEnd}]]>if><if test="dto != null and dto.vip !=null ">AND vip = #{dto.vip}if><if test="dto != null and dto.status !=null ">AND status = #{dto.status}if>ORDER BYmember_id DESCLIMIT#{start},#{pageSize}) AS mb3 ON mb.member_id = mb3.member_idLIMIT #{pageSize}; --这个可以不用要select>
姿势二pageListV2的查询条件放外层或内层效果是一样的，测试了没有多大的差别，还有就是最后的LIMIT #{pageSize}; --这个可以不用要，测试也是可有可无。
上面两种方式：姿势一是根据主键找到开始或结束值来分页取数据的，姿势二是内连接的方式，姿势二比姿势一多一层外层的临时表的，两个的性能差不多，推荐使用姿势一，姿势一走主键索引和子查询还有名命中索引，减少回表次数，如果索引设计的好两种方式查询都会命中索引，尽量让离散区分度大的列条件走索引，避免索引失效，索引失效需要排查失效原因，上面两种姿势的pageListV2Count的COUNT()不要写成COUNT(DISTINCT mb.member_id)，如果写成COUNT(DISTINCT mb.member_id)这种，会导致pageListV2Count这个查询慢，索引失效，全表扫描，亲测如果写成COUNT(DISTINCT mb.member_id)这种，这个统计查询耗时达到30多秒，非常的慢，所以COUNT()是标准的统计sql写法，直接使用COUNT(*)来统计行数一点问题都没有的，写其它写法会有一些奇怪的问题出现。
4. 总结
结论：上面两种姿势，推荐使用姿势一，这种优化下来，600多W的单表使用MyBatisPlus优化分页查询点击前面的页非常快，毫秒级，中间页平均3秒，最有一页第一次耗时长一点，之后预热了索引数据之后也是平均3秒耗时，前中后分页查询平均耗时3s左右，从之前的好几十秒，可以说是性能提升了10多倍，这个优化已经是优化到极致了，单个条件命中索引数据量大一点就会慢一点，命中索引数据量少一点就会非常的快，组合条件查询走索引也是同样的，耗时取决于索引命中的的数据量大小， 数据量大耗时长，数据量小耗时短，别看这点东西，还是要一点一点的优化分析的，也不简单的，还是要费一点时间和功夫的，所以说姿势很重要，姿势不对努力白费，姿势一对是无比的丝滑流畅，直接起飞，由此可见，Mysql在数据量很大的情况下，上到600w到1000w基本上是遇到瓶颈了，可以考虑使用其它的一些更猛更强更快的数据库产品和大数据技术栈来解决业务的数据查询分析等痛点问题，本次分享到此结束，创作不易，请尊重一下作者，禁止抄袭，发现抄袭直接举报，转载请注明作者及出处，这也是一种开源精神，互学互鉴，共同繁荣，有思考有深度有实践不坑的文章百里挑一，千篇一律复制粘贴的水文坑文烂文百度一大堆，希望我的分享对你有所启发和帮助，请一键三连，么么么哒！