实战！谈谈如何解决MySQL深度分页问题

前言大家好，我是捡蜗牛的小男孩。（求星标置顶）我们在做日常分页需求的时候，一般都是使用limit来实现，但是当offset特别大的时候，查询效率就变低了。本文将讨论如何通过四种方案优化MySQL百万数据的深度分页问题，??并附上近期优化慢SQL生产的实际案例。为什么限制深度分页会变慢？先看下表结构：CREATETABLEaccount(idint(11)NOTNULLAUTO_INCREMENTCOMMENT'主keyId',namevarchar(255)DEFAULTNULLCOMMENT'账户名',balanceint(11)DEFAULTNULLCOMMENT'余额',create_timedatetimeNOTNULLCOMMENT'创建时间',update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'updatetime',PRIMARYKEY(id),KEYidx_name(name),KEYidx_update_time(update_time)//index)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANT下表的执行是假设子账户表是深的',name,balancefromaccountwhereupdate_time>'2020-09-19'limit100000,10;这条SQL的执行时间如下：执行需要0.742秒，为什么深度分页变慢了？如果改成limit0,10，只需要0.006秒。我们先来看看这条SQL的执行过程：通过普通的二级索引树idx_update_time，过滤update_time条件，找到满足条件的记录ID。通过ID，返回主键索引树，找到满足记录的行，然后取出显示的列（返回表）扫描满足条件的100010行，然后丢弃前100000行，然后返回。SQL执行流程执行计划如下：SQL变慢的原因有两个：limit语句会先扫描offset+n行，然后丢弃最前面的offset行，返回最后n行数据。也就是说limit100000,10会扫描100010行，而limit0,10只会扫描10行。limit100000,10扫描更多的行，也意味着更多的回表次数。通过子查询优化，因为上面的SQL，返回了100010次表。其实我们只需要10条数据，也就是只需要回表10次。因此，我们可以通过减少回表次数来进行优化。回过头来看B+树结构，如何减少返表次数？我们先回顾一下B+树索引结构。在InnoDB中，索引分为主键索引（聚集索引）和二级索引主键索引。叶子节点存储整行数据的二级索引，叶子节点存储主键的值。将条件传回主键索引树如果我们将查询条件传回主键索引树，那么就可以减少回表的次数。如果转为主键索引树查询，查询条件就得改成主键id。前面SQL的update_time条件呢？如何从子查询中提取它？因为二级索引中有叶子节点的主键ID，所以我们可以直接根据update_time查看主键ID。同时我们把limit100000这个条件传给子查询。完整的SQL如下：selectid,name,balanceFROMaccountwhereid>=(selecta.idfromaccountawherea.update_time>='2020-09-19'limit100000,1)LIMIT10;查询效果是一样的，执行时间只需要0.038秒！让我们看看执行计划。从执行计划中，我们知道子查询表一次查询使用了idx_update_time索引。首先在索引上获取聚簇索引的主键ID，免去回表，然后第二次查询可以直接根据第一次查询的ID多查10个！所以，这个方案是可以的~INNERJOINdelayassociationdelayassociation的优化思路其实和子查询的优化思路是一样的：两个条件都转移到主键索引树上，再缩回表.不同的是delayedjoin使用的是innerjoin而不是subquery。优化后的SQL如下：SELECTacct1.id,acct1.name,acct1.balanceFROMaccountacct1INNERJOIN(SELECTa.idFROMaccountaWHEREa.update_time>='2020-09-19'ORDERBYa.update_timeLIMIT100000,10)ASacct2onacct1.id=acct2.id;查询效果也是杠杆的，只需要0.034秒。执行计划如下：查询思路是先通过idx_update_time二级索引树查询出满足条件的主键ID，再通过主键ID与原表连接，这样主键索引就会后面直接用，同时Reducedbacktables。labelrecordmethodlimit深度分页问题的本质原因是：偏移量（offset）越大，mysql扫描然后丢弃的行就越多。这会导致查询性能下降。其实我们可以使用标签记录的方式，就是标记上次查询的是哪一项，下次再查询的时候从这一项往下扫描。就像看书一样，你可以把它折叠起来，或者在上次看到的地方放个书签，下次看的时候翻过来就可以了。假设最后一条记录达到100000条，SQL可以修改为：selectid,name,balanceFROMaccountwhereid>100000orderbyidlimit10；在这种情况下，无论后面翻多少页，性能都会很好，因为命中了id索引。但是这种方法有局限性：需要一个类似于连续自增的字段。使用between...and...在很多情况下，limit查询可以转换为已知位置的查询，这样MySQL就可以通过扫描between...and范围得到相应的结果。如果知道边界值为100000，100010之后，可以这样优化：selectid,name,balanceFROMaccountwhereidbetween100000and100010orderbyid；动手实战案例下面我们一起来看一个实战案例。假设表结构如下，有200万条数据。CREATETABLEaccount(idvarchar(32)COLLATEutf8_binNOTNULLCOMMENT'primarykey',account_novarchar(64)COLLATEutf8_binNOTNULLDEFAULT''COMMENT'account'amountdecimal(20,2)DEFAULTNULLCOMMENT'amount'ULULDEMENTF8_'TypeA,B'create_timedatetimeDEFAULTNULLCOMMENT'create_datetimeDEULTCOMMENT',update_timedatetimeDEULTCOMMENT更新时间,PRIMARYKEY(id),KEY`idx_account_no`(account_no),KEY`idx_create_time`(create_time))ENGINE=InnoDBDEFAULTCHARSET=utf8binCOMLLATE=utf8_'业务需求是这样的：获取到2021年的A类账户数据，上报给大数据平台。总体思路的实现很多小伙伴接到这样的需求，会直接这样实现：//查询总上报金额Integertotal=accountDAO.countAccount();//查询上报总金额对应的SQLseelctcount(1)fromaccountwherecreate_time>='2021-01-0100:00:00'andtype='A'//计算页数intpageNo=total%pageSize==0?total/pageSize:(total/pageSize+1);//分页查询，上报for(inti=0;ilist=accountDAO.listAccountByPage(startRow,pageSize);startRow=(pageNo-1)*pageSize;//上报大数据postBigData(list);}//分页查询SQL（可能存在limitdeeppaging问题，因为account表数据量几百万）seelct*fromaccountwherecreate_time>='2021-01-0100:00:00'andtype='A'limit#{startRow},#{pageSize}以上实战优化方案实现方案无线会出现limitdeeppaging的问题，因为account表的数据量是百万级别的。如何优化呢？其实可以用标签记录的方法。有的小伙伴可能会有疑惑。id主键不连续。你真的可以使用标签记录吗？当然id不是连续的。我们可以通过orderby让它连续。优化方案如下：//查询最小IDStringlastId=accountDAO.queryMinId();//查询最大ID对应的SQLselectMIN(id)fromaccountwherecreate_time>='2021-01-0100:00:00'andtype='A'//一个页面的项目数IntegerpageSize=100;Listlist;do{list=listAccountByPage(lastId,pageSize);//标签记录方法，记录最后一次查询到的idlastId=list.get(list,size()-1).getId();//上报大数据postBigData(list);}while(CollectionUtils.isNotEmpty(list));select*fromaccountwherecreate_time>='2021-01-0100:00:00'andid>#{lastId}andtype='A'orderbyidasclimit#{pageSize}小男孩"，可以通过以下二维码关注，转载请联系捡蜗牛的小男孩公众号。