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

简介

大家好，我是一个捉蜗牛的小男孩。

我们在做日常的分页需求时，通常会使用limit来实现，但是当offset特别大的时候，查询效率就会变低。
本文将分四个方案来讨论如何优化MySQL百万级数据深度迁移的问题，并附上最近的一个实际案例来优化生产中的慢SQL。

为什么深度迁移速度减慢？

先看一下下面的表结构：

CREATETABLEaccount(idint(11)NOTNULLAUTO_INCRMENTCOMMENT'primarykeyId',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_INCRMENT=1570068DEFAULTCHARSET=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行符合的行条件好了，然后把前十万扔掉就OK了，再回来。

SQL执行流程

执行计划如下：

SQL慢的原因有两个：

limit语句会首先检查offset+n行，然后忽略第一个offset行并返回接下来的n行数据行。
换句话说，Limit100000.10将扫描100010行，而Limit0.10将仅扫描10行。

limit100000,10扫描更多行，这也意味着返回表的次数更多。

通过子查询优化

由于上面的SQL返回表100010次，实际上我们只需要10条数据，即只需要返回表10次。
因此，我们可以通过减少返回表的次数来改进这一点。

回顾B+树结构

那么如何减少表的返回次数呢？我们先回顾一下B+树索引结构~

在InnoDB中，索引分为主键索引（聚集索引）和辅助索引。

主键索引、叶子节点它存储整行的数据

二级索引存储主键的叶节点值。

将条件移至主键索引树

如果我们将查询项移回主键索引树，我们将无法减少表返回的次数以及频率。
如果导航到主键索引树查询，则必须将查询条件更改为主键ID。
对于之前的SQL更新时间条件我该怎么办？如何提取子查询？

如何提取子查询？由于二级索引的叶子节点有主键ID，所以我们可以直接根据update_time查看主键ID，同时我们还将Limit100000条件传递给子查询，完整的SQL如下：

selectid，名称，BalanceFROMaccuntwhereid>=(selecta.idfromaccountawherea.update_time>='2020-09-19'limit100000,1)LIMIT10;

查询效果是一样的，执行时间只需要0.038秒！

我们来看看实施方案

从执行计划中我们知道子查询表查询使用了idx_update_time索引。
首先，我们获取索引中聚集索引的主键ID，无需返回表。
那么第二个查询就可以根据第一个查询的ID直接查10了！

所以这个方案是可以的~

INNERJOIN延迟配对延迟耦合的优化思想其实和子查询的优化思想是一样的：将状态转移到主键索引树上，然后归约回表。
不同之处在于延迟耦合使用内连接而不是子查询。

优化后的SQL如下：以下内容：

SELECTacct1.id,acct1.name,acct1.balanceFROMaccuntacct1INNERJOIN(SELECTa.idFROMaccountaWHEREa.update_time>='2020-09-19'ORDERBYa.update_timeLIMIT100000,10)ASacct2onacct1.id=acct2.id;

完成同样利用查询效果，只需要0.034秒

实现方案如下：

查询的思路是先通过二级索引树idx_update_time查询符合条件的主键ID，然后通过这样后面直接用主键索引，表就返回Reduce了。
还。

标记方法

深度分页限制问题的根本原因是：偏移量大小越大，MySQL将扫描然后丢弃的行数就越多。
这会导致性能下降询问。

其实我们可以使用标签记录的方式，就是标识上次查询到的元素。
下次我们回来查看时，我们将从此项向下扫描。
就像读一本书一样。
只需折叠它或将其添加到您上次看到它的位置即可。
下次你阅读它时，你可以回来阅读它。

假设最后一条记录达到100000，则SQL可修改为：

selectid,name,balanceFROMaccountwhereid>100000orderbyidlimit10;

这样的话，无论翻多少页都会被翻页稍后，性能会很好，因为按了ID指示灯。
但这种方法有局限性：它需要类似的字段来连续自动增量。

在...和...之间的用法

很多情况下，可以将绑定查询转换为已知位置的查询，这样MySQL就可以通过检查...之间的范围来得到相应的结果。
和。

如果知道限制值为100000、100010，可以按如下方式优化：

selectid,name,balanceFROMaccountwhereidBetween100000and100010orderbyiddesc;实际案例一步一步

让我们看一个实际案例。
假设表结构如下，有200万条数据。

CREATETABLEaccount(idvarchar(32)COLLATEutf8_binNOTNULLCOMMENT'主键',account_novarchar(64)COLLATEutf8_binNOTNULLDEFAULT''COMMENT'账号'amountdecimal(20,2)DEFAULTNULLCOMMENT'数量金额'typevarchar(10)COLLATEutf8_binDEFAULTNULLCOMMENT'类型A,B'create_timedatetimeDEFAULTNULLCOMMENT'创建时间',update_timedatetimeDEFAULTNULLCOMMENT'更新时间',PRIMARYKEY(id),KEY`idx_account_no`(account_no),KEY`idx_create_time`(create_time))ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_binCOMMENT='计算表'

工作要求如下：获取2021年最新的A类计算数据，上报到大数据平台。

总体思路如何实现

很多合作伙伴收到这样的订单后会直接实现：

//查询总量ReportIntegertotal=accountDAO.countAccount();//查询总量报告数量SQL对应selctcount(1)fromaccountwherecreate_time>='2021-01-0100:00:00'andtype='A'</select>//计算页数intpageNo=total%pageSize==0?total/pageSize:(total/pageSize+1）；//页面查询、报表for(inti=0;ilist=accountDAO.listAccountByPage(startRow,pageSize);startRow=(pageNo-1)*pageSize;//报表数据postBigData(list);}//SQL分页查询（深度分页问题可能存在限制，因为账户表数据量在百万级）seelct*fromaccountwherecreate_time>='2021-01-0100:00:00'andtype='A'limit#{startRow},#{pageSize}</select>实际优化方案

实施上面的解决方案，对于深度迁移问题会有一个限制，因为大小计算表数据为百万。
那么如何改进呢？

其实，可以使用评分的方法。
有些小伙伴可能会感到困惑。
商标注册真的能用吗？

当然，标识符不是连续的，我们可以通过排序使它们连续。
优化方案如下：

//查询最小IDStringlastId=accountDAO.queryMinId();//查询最大ID对应的SQLselect*fromaccountwherecreate_time>='2021-01-0100:00:00'andid>#{lastId}andtype='A'orderbyidasclimit#{pageSize}</select>常规编号:捡蜗牛的小男孩

MySQL索引条件下推--优化实战

本文主要讨论MySQL索引条件下推（ICP）的概念及其在优化实践中的应用。
ICP的作用是利用二级索引尽可能过滤掉不满足WHERE条件的记录，从而减少存储引擎访问底层表的次数，降低执行成本。
当执行ICP时，如果某些WHERE条件可以仅使用索引列来评估，则MySQL服务器会将这些条件推送到存储引擎，并且引擎将使用条目索引来评估推送的索引条件。
如果满足，则读取对应的行，从而减少存储引擎对底层表的访问次数以及MySQL服务器对存储引擎的访问次数。
当执行使用ICP的查询时，ExplainSQL语句的输出部分的Extra中将显示“Usingindexcondition”。
根据此信息，您可以确定查询是否使用ICP。
为了说明ICP的使用，请考虑一个具有全局索引（idx_zipcode、lastname、firstname）的表。
使用ICP时，MySQL在读取完整行之前检查familyLIKE'%etrunia%'，从而避免读取与邮政编码条件匹配但不满足family条件的行。
创建示例查询并使用解释结果来验证是否实现了预期的ICP优化。
ICP的本质是通过二级索引尽可能过滤掉不符合条件的记录。
即使不完全遵循最左匹配原则，也可以减少回表操作，降低实施成本。
在线遇到的问题描述了查询速度慢。
监控显示该查询应该使用ICP，但实际上并未执行。
该表用于记录好友关系，查询语句为user_idin(...)andfollower_id=26407612。
通用索引idx_query(user_id,event_id,follower_id)理论上支持使用ICP，但由于使用分区表，MySQL5.6版本不支持ICP。
调查中发现MySQL5.7版本支持ICP，但问题是在线数据库版本是5.6并且使用了分区表。
官方文档明确指出ICP仅在InnoDB和MyISAM表中可用。
MySQL5.6版本的分区表不支持ICP。
这个问题在MySQL5.7版本中已经得到解决。
要在MySQL5.6版本中使用分区表，可能的解决方案之一是将数据库升级到版本5.7。
另一种方法是将业务脚本与MySQL的范围搜索功能结合起来，添加条件ANDevent_idin(1,3)，保证全局索引的所有字段都参与过滤，从而减少返回表的记录数。
通过改变Explanation中的key_len值，直观地验证优化效果。
在实际应用中，通过将数据复制到启用ICP的测试数据库（MySQL版本5.7），您可以验证ICP是否存在。
实施是否正确。
更深入地了解ICP的使用方式，例如used_key_parts和index_condition的区别，以及MySQL中分区表的ICP支持问题，将有助于更深入地了解实现策略和ICP优化。