前言大多数人都是这样使用分页的：SELECT*FROMtableLIMIT20OFFSET50可能有些朋友还不清楚LIMIT和OFFSET的具体含义和用法。介绍一下：LIMITX的意思是：读取X条数据LIMITX,Y的意思是：跳过X条数据，读取Y条数据LIMITYOFFSETX的意思是：跳过X条数据，读取Y条数据对于简单的小对于应用程序和数据量都不是很大的场景，这种方式还是没问题的。但是如果你想建立一个可靠高效的系统，你必须从一开始就做好。今天我们将讨论目前广泛使用的分页方式存在的问题，以及如何实现高性能的分页。LIMIT和OFFSET有什么问题？OFFSET和LIMIT对于数据量小的项目没有问题。但是，当数据库中的数据量超过了服务器内存所能存储的容量，需要对所有数据进行分页时，问题就会出现，为了实现分页，每次收到分页请求时，数据库都需要进行一次低效的全表遍历。全表遍历是一个全表扫描的过程，就是把磁盘上的数据页按照双向链表加载到磁盘的缓存页中，然后在缓存页里面查找那条数据。这个过程很慢，所以当数据量很大的时候，全表遍历的性能很低，时间特别长，所以要尽量避免全表遍历。也就是说，如果你有1亿用户，OFFSET是5000万，那么它需要把那些记录（包括那么多根本不需要的数据）全部取出来，放到内存中，然后取出指定的20条结果通过限制。为了获取一页数据：100,000行中的第50,000行到第50,020行需要先获取50,000行，效率很低！LIMIT查询效率初步探索数据准备本次测试使用的环境：[root@zhyno1~]#cat/etc/system-releaseCentOSLinuxrelease7.9.2009(Core)[root@zhyno1~]#uname-aLinuxzhyno13.10。0-1160.62.1.el7.x86_64#1SMPTueApr516:57:59UTC2022x86_64x86_64x86_64GNU/Linux测试数据库使用（存储引擎使用InnoDB，其他参数默认）：mysql>selectversion();+-----------+|版本()|+------------+|8.0.25-16|+------------+1Therowinset(0.00sec)表结构如下：CREATETABLE`limit_test`(`id`int(11)NOTNULLAUTO_INCREMENT,`column1`decimal(11,2)NOTNULLDEFAULT'0.00',`column2`decimal(11,2)NOTNULLDEFAULT'0.00',`column3`decimal(11,2)NOTNULLDEFAULT'0.00',PRIMARYKEY(`id`))ENGINE=InnoDBmysql>DESClimit_test;+---------+----------------+------+-----+--------+--------------+|领域|类型|空|键|默认|额外|+--------+----------------+------+-----+---------+----------------+|编号|整数|否|优先级|空|A自动增量||专栏1|十进制（11,2）|否||0.00|||专栏2|十进制（11,2）|否||0.00|||专栏3|十进制（11,2）|否||+--------+----------------+-----+-----+---------+--------------+4rowsinset(0.00sec)插入350万条数据作为测试：mysql>SELECTCOUNT(*)FROMlimit_test;+----------+|计数(*)|+----------+|3500000|+------------+1rowinset(0.47sec)先开始测试设置移位量为0，取20条数据（省略中间输出）：mysql>SELECT*FROMlimit_testLIMIT0,20;+----+--------+------------+---------+|编号|专栏1|专栏2|第3列|+----+------------+----------+--------+|1|50766.34|43459.36|56186.44|#...省略中间输出|20|66969.53|8144.93|77600.55|+----+----------+---------+---------+20行集合（0.00秒）你可以看到查询时间基本可以忽略不计，所以我们要一步步增加offset再测试，先把offset改成10000（省略中间输出）：mysql>SELECT*FROMlimit_testLIMIT10000,20;+------+----------+---------+--------+|编号|专栏1|专栏2|column3|+--------+----------+----------+----------+|10001|96945.17|33579.72|58460.97|#...省略中间输出|10020|1129.85|27087.06|97340.04|+------+--------+----------+------------+20rowsinset(0.00sec)可以看到查询时间还是很短的，几乎可以忽略不计，所以我们直接增加offset到340W（中间输出省略）：mysql>SELECT*FROMlimit_testLIMIT3400000,20;+--------+--------+--------+----------+|编号|专栏1|专栏2|column3|+--------+--------+--------+----------+|3400001|5184.99|67179.02|56424.95|#...省略中间输出|3400020|8732.38|71035.71|52750.14|+--------+--------+--------+----------+20行集合(0.73sec)此时可以看到非常明显的变化，查询时间飙升至0.73s耗时原因分析根据如下结果可以看出，三个查询语句都进行了全表扫描：mysql>EXPLAINSELECT*FROMlimit_testLIMIT0,20;+----+-------------+------------+------------+------+--------------+------+--------+------+--------+---------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+---+-------------+------------+------------+-----+--------------+------+--------+-----+--------+--------+-------+|1|简单|极限测试|空|所有|空|空|空|空|3491695|100.00|空|+----+------------+------------+------------+------+----------------+------+--------+------+---------+------------+--------+1行集合，1个警告（0.00秒）mysql>EXPLAINSELECT*FROMlimit_testLIMIT10000,20;+----+------------+-------------+------------+------+--------------+------+-------+------+--------+----------+--------+|编号|选择类型|表|分区|体育|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+------------+------------+------------+------+----------------+------+--------+------+---------+------------+--------+|1|简单|极限测试|空|所有|空|空|空|空|3491695|100.00|空|+----+------------+------------+------------+------+----------------+------+--------+------+---------+------------+--------+1行集合，1个警告（0.00秒）mysql>EXPLAINSELECT*FROMlimit_testLIMIT3400000,20;+----+------------+------------+------------+------+--------------+------+--------+------+--------+----------+--------+|编号|选择类型|表|分区|类型|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+------------+------------+------------+------+----------------+------+--------+------+---------+------------+--------+|1|简单|极限测试|空|所有|无效的|空|空|空|3491695|100.00|空|+----+------------+------------+-------------+------+----------------+-----+--------+------+---------+------------+--------+1rowinset,1warning(0.00sec)ThisisfineWhatweknowisthatwhentheoffsetisverylarge,对于像LIMIT3400000,20这种情况的查询，MySQL需要查询3400020行数据，然后返回最后20条数据。前面查询到的340W数据会全部丢弃，这样的执行结果不是我们想要的。下一步是优化大偏移量优化的性能。您可以这样做：SELECT*FROMlimit_testWHEREid>10limit20这是一个基于指针的分页。您想将最后收到的主键（通常是一个ID）和LIMIT保存在本地，而不是OFFSET和LIMIT，因此每次查询可能与此类似。为什么？因为通过显式地告诉数据库最新的行，数据库确切地知道从哪里开始搜索（基于有效的索引），而不管目标范围之外的记录。我们再测试一下（省略中间输出）：mysql>SELECT*FROMlimit_testWHEREid>3400000LIMIT20;+--------+--------+----------+------------+|编号|专栏1|专栏2|column3|+--------+--------+--------+--------+|3400001|5184.99|67179.02|56424.95|#...省略中间输出|3400020|8732.38|71035.71|-----+------------+------------+---------+20行在集合中（0.00秒）mysql>EXPLAINSELECT*FROMlimit_testWHEREid>3400000LIMIT20;+----+------------+------------+------------+--------+----------------+--------+--------+------+--------+----------+------------+|编号|选择类型|表|分区|类型|可能的键|钥匙|密钥长度|参考|行|过滤|额外|+----+------------+------------+----------+-------+----------------+--------+---------+------+-------+------------+------------+|1|简单|极限测试|空|范围|初级|初级|4|空|185828|100.00|使用where|+----+------------+------------+------------+-------+----------------+--------+--------+------+--------+------------+------------+1行在集合中，1个警告（0.00秒）返回相同的作为a结果，第一次查询用了0.73秒，而第二次只用了0.00秒注：如果我们的表没有主键，比如多对多关系的表，那么用传统的OFFSET/LIMIT方式，只是这样做有一个潜在的慢查询问题。所以建议在需要分页的表中使用自增主键，即使只是为了分页。重新优化类似于查询SELECT*FROMtable_nameWHEREid>3400000LIMIT20;这样效率很快，因为主键上有索引，但是这样有一个缺点，就是ID必须是连续的，查询不能有WHERE语句。因为WHERE语句会导致过滤数据。使用场景非常有限，所以我们可以这样做：当使用覆盖索引优化MySQL查询完全命中索引时，称为覆盖索引，速度非常快，因为查询只需要在索引上搜索，然后就可以直接Return了，不用回数据表去取数据。因此，我们可以先找出索引的ID，然后根据Id获取数据。ELECT*FROM(SELECTidFROMtable_nameLIMIT3400000,20)aLEFTJOINtable_namebONa.id=b.id;#orSELECT*FROMtable_nameaINNERJOIN(SELECTidFROMtable_nameLIMIT3400000,20)bUSING(id);总结当数据量很大时，不能使用OFFSET/LIMIT进行分页，因为OFFSET越大，查询时间越长。当然也不能说所有的寻呼都不行。如果你的数据只有几千、几万，那也没关系，随便用。如果我们的表没有主键，比如是多对多关系的表，那就用传统的OFFSET/LIMIT方式。该方法适用于要求ID为数值类型，且检测到的数据ID连续且不能按其他字段排序的场景。
