从0.742秒到0.006秒，MySQL百万数据深度分页优化实践

1.前言我们在日常做分页需求的时候，一般都是使用limit来实现，但是当offset特别大的时候，查询效率就变低了。本文将讨论如何通过四种方案优化MySQL百万数据的深度分页问题，??并附上近期优化慢SQL生产的实际案例。2、为什么limitdeeppaging变慢了？先看下表结构：CREATETABLEaccount(idint(11)NOTNULLAUTO_INCREMENTCOMMENT'主键Id',namevarchar(255)DEFAULTNULLCOMMENT'账户名',balanceint(11)DEFAULTNULLCOMMENT'balance',create_timedatetimeNOTNULLCOMMENT'创建时间',update_timedatetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(id),KEYidx_name(name),KEYidx_update_time(update_time)//index)ENGINE=InnoDBAUTO_INCREMENT08=157DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='账户表';假设深度分页的执行SQL如下：selectid,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扫描更多的行，也意味着更多的回表次数。三、优化方案1、通过子查询优化因为上面的SQL，表被返回了100010次。其实我们只需要10条数据，也就是只需要返回表10次。因此，我们可以通过减少回表次数来进行优化。1）回顾B+树结构那么，如何减少返回表的次数呢？先回顾一下B+树的索引结构吧~在InnoDB中，索引分为主键索引（聚集索引）和二级索引主键索引。叶子节点存放整行数据的二级索引，叶子节点存放主键的值。.2）将条件传回主键索引树如果我们将查询条件传回主键索引树，那么可以减少回表的次数。如果转为主键索引树查询，则必须将查询条件改为主键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个！所以，这个方案是可以的~2.INNERJOINdelayedassociationdelayassociation的优化思路其实和子查询的优化思路是一样的：两个条件都转移到主键索引树上，然后再缩减回桌子。不同的是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秒。原表通过主键ID内联，这样后面直接使用主键索引，也减少了返回表。3、标签记录方式的limitdeeppaging问题的本质原因是：偏移量（offset）越大，mysql扫描然后丢弃的行就越多。这会导致查询性能下降。其实我们可以使用标签记录的方式，就是标记上次查询的是哪一项，下次再查询的时候从这一项往下扫描。就像看书一样，你可以把它折叠起来，或者在上次看到的地方放个书签，下次看的时候翻过来就可以了。假设最后一条记录达到100000条，SQL可以修改为：selectid,name,balanceFROMaccountwhereid>100000orderbyidlimit10;在这种情况下，无论后面翻多少页，性能都会很好，因为id索引被命中了。但是这种方法有局限性：需要一个类似于连续自增的字段。4、使用between...and...在很多情况下，可以将limit查询转换为已知位置的查询，这样MySQL就可以通过扫描between...and范围得到相应的结果。如果知道边界值为100000，在100010之后，可以这样优化：selectid,name,balanceFROMaccountwhereidbetween100000and100010orderbyid;4.动手实践案例下面我们一起来看一个实际案例。假设现在有如下表结构，有200万条数据。CREATETABLEaccount(idvarchar(32)COLLATEutf8_binNOTNULLCOMMENT'primarykey',account_novarchar(64)COLLATEutf8_binNOTNULLDEFAULT''COMMENT'account'amountdecimal(20,2)DEFAULTNULLCOMMENT'amount'类型varchar(10)COLLATEutf8_binDEFAULTNULLCOMMENT'typeA,B'create_timedatetimeDEFAULTNULLCOMMENT'创建时间',update_timedatetimeDEFAULTNULLCOMMENT'更新时间',PRIMARYKEY(id),KEY`idx_account_no`(account_no),KEYtimeidx_create_`(create_time))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='AccountTable'业务需求如下：获取截至2021年的A类账户数据，上报大数据平台。1.实现思路很多小伙伴接到这样的请求，会直接这样实现：//查询上报总数Integertotal=accountDAO.countAccount();//查询上报总数对应的SQL从帐户中选择count(1)wherecreate_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（深度分页可能存在限制问题，因为account表的数据量有几百万）seelct*fromaccountwherecreate_time>='2021-01-0100:00:00'andtype='A'limit#{startRow},#{pageSize}2、实战优化方案上面的实现方案会有limitdeeppagination的问题，因为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}