面试官问：MySQL中百万级数据量，如何分页查询？

尽管它会带来性能挑战。
通过精心设计的索引和合理的查询策略，例如复合索引和只读取需要的列，MySQL可以保持相对较高的性能，尽管限制不是1亿。
数据库设计和优化技能。

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

行，并且limit0,10-仅10行。

limit100000.10扫描更多行，这也意味着更多表返回。

使用子查询优化

由于上面的SQL返回表100010次，所以我们实际上只需要10条数据，也就是说我们只需要返回表10次。
因此，我们可以通过减少表返回次数来进行优化。

B+树结构概述

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

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

主键索引，叶子节点存储所有行数据。

辅助索引，叶子节点存储主键的值。

将条件移至主键索引树

如果我们将查询条件移回主键索引树，我们将无法减少表返回Number的次数。
如果去查询主键索引树，需要将查询词改为主键ID。
之前的SQLupdate_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个！

因此这个方案是可以的~

INNERJOIN延迟关联

优化延迟关联的思想其实是与子查询优化思想相同：两个条件都传递到主键索引树，然后折叠回表。
不同之处在于延迟关联使用内部联接而不是子查询。

优化后的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连接到源表。
所以后面直接使用主键索引，返回表。
也在减少。

标签写入方法

交换深度限制问题的主要原因是：偏移量（offset）越大，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'编号amount'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类账户数据，上报给大数据平台。

总体思路如何实现

很多合作伙伴收到这样的请求后立即实现：

//请求上报的总数量Integertotal=accountDAO.countAccount();//请求上报的总数量对应SQLselctcount(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>实际优化plan实现上面的解决方案：深度交换会出现问题，因为账户表数据量很大百万。
那么如何优化它呢？

其实，可以使用标签写法。
这可能会让一些合作伙伴感到困惑。
标识符的主键不连续。
标签书写真的可以用吗？

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

//查询最小IDStringlastId=accountDAO.queryMinId();//查询最大ID对应的SQLselectMIN(id)fromaccountwherecreate_time>='2021-01-0100:00:00'andtype='A'</select>//一页元素数量IntegerpageSize=100;List

MySQL数据库limit分页、排序-SQL语句示例

MySQL数据库限制分页、排序-SQL语句示例

select*frompersonslimitA,B;

说明：

A、查询入口点

B，所需行数

示例：

select*fromompersonslimit0,4;

说明：

起始位置为0，开始查询返回4条数据。

select*frompersonslimit4,4;

说明：

起始点为4，开始查询。
并返回4天的数据。

特殊：

select*frompersonslimit10;

表示查询从0开始，有10条记录。
被返回。

与select*frompersonslimit0,10相同；

按规则排序时进行分页：

select*frompersons

orderbylastname

limit0,10;