前言最近做项目涉及MySQL的join分页显示实际上大部分场景都是单表分页展示一些数据。当然也有时候需要多表join等操作多表分页时就比较复杂而且具有表结构连接特定性一般我们可以使用代码拆分使操作尽可能的单表查询优化分享一下单表分页的一些经验。demo分页显示代表某类数据比较多一次性看不完如果数据量在一个范围以内比如1000条等那么MySQL自带的分页也是可以的。笔者以100w条数据为例笔者的demo表字段比较少如果字段更多则更明显而且生产数据会更大。使用上次jpa的user表字段5个但是效果已经很明显了不过即使是60w数据笔者电脑的nvme ssd上还是很快如果是sata或者机械磁盘差距将放大很多MySQL在批量查询时使用了叶子结点的顺序链表尤其是笔者的demo肯定会使用如果加个条件那么走的是条件索引回表查询。使用MySQL的自带分页逻辑试试1000条基本上不怎么耗时其实即使是for循环也很快同理笔者这里使用了主键索引但是即使这样在offset变大时耗时指数增长变成10w试试基本肯定为线性增长比如50w其实性能还行不过生产上肯定不止这点数据比如跑批来个100w分析cost情况这......全靠硬件的顺序读取支持速度否则惨不忍睹笔者ssd顺序读为3GB/s左右所以没啥问题。优化优化方式有很多1. 覆盖索引子查询笔者demo非常合适来试试每页1000增大难度时间大幅度降低差不多少了40%多的耗时看看cost情况对比从索引分析其实MySQL默认写法效率更优但是考虑b树的遍历深度回表情况(走index lookup)由于在数据量大时大幅度减少回表所以使用id内连接耗时更低也是推荐的均衡用法一般数据量大且需要跳页的都是使用这种方式。2. in模式本来参考oracle使用in先把id查出来然后in实际上MySQL的in走索引效率也很高但是MySQL不支持这种方式subquery-errors.html报错网上说低版本的MySQL不支持这种方式但是根据上面的官方文档最新的MySQL9.6也不支持可以再次嵌套但是性能太差得不偿失当然可以使用临时表只是比较麻烦还是上面的join操作舒服。3. 游标模式游标其实就是offset条件写进where里面效率极高。但是缺点很明显不能跳页其实还有between and这个缺点就更明显了因为数据是有空缺的比如某条记录被删除这种适合不会存在删除的情况只有新增和查询时效率极高。试一下性能极强看看执行计划这个执行计划本身没有问题但是实际场景耗时的原因在分页场景并不在索引环节而是从主键索引怎么快速的取数才是关键所以所有的优化方式核心都是缩小主键id的范围从而快速的从聚簇索引取到数据。试试between and虽然在分页时需要连续的记录但是看看性能如何执行计划cost明显较低between and适合条件搜索效率命中索引时很高一般在删数场景大量广泛使用。总结其实这些在很多场景已经广泛应用了主要是大数据存储和查询的要求尤其是批处理根据根据实际情况来处理移动端的刷新大部分都不支持跳页所以基本上使用的游标分页模式速度极快而PC端的一些操作或者需要跳页的场景一般使用覆盖索引子查询方式性能均衡支持跳页而默认的limit实际上使用最多基本上大部分系统表不超过10w的表占得比例是最高的。方案适合场景性能跳页支持备注limit offset数据量较小的场景比如10w以下在数据量较大时很弱是根据实际场景数据量较少非常方便适合后管系统用的也多覆盖索引子查询数据量大需要跳页较好是优化回表成本但仍有索引扫描开销游标分页适合性能要求高且不需要跳页的场景比如手机app刷新极好否适合拉取类场景范围查找连续数据或者能接受不对等的场景极好是一般不用于分页用于删数或者在处理数据时接受数据不均匀