分页查询优化的基本原理
分页查询简单说就是把一堆数据切成一小块一小块的，用户一次看一小块，比如刷商品列表时每页显示20件，点下一页就加载后20件，这种操作在日常开发里太常见了，但要是数据量大了还瞎搞，用户就得等半天，体验直接拉胯。分页查询优化的核心就是让数据库少干活，快速定位到用户要的那部分数据，别让它傻乎乎地把所有数据都翻一遍，就像你去图书馆找书，直接按编号找比一本本翻书架快多了，分页查询优化干的就是这事儿——给数据库指条明路,让它别走冤枉路。
分页查询常见的性能问题
最容易踩的坑就是用limit offset时瞎设大offset，比如查第1000页数据，写limit 9990,10，数据库得先把前9990条数据找出来，再扔掉，只留最后10条，这不是纯纯浪费时间吗？之前我见过一个项目，数据量才50万，用户翻到第50页就卡得动不了，一查SQL，limit 490,10，数据库扫描了500条数据，慢得像蜗牛。另一个坑是没给排序字段加索引，比如按create_time排序分页，结果create_time没索引，数据库得先把所有数据捞出来排个序，再分页，数据量大的时候能把CPU跑满，还有人喜欢用select *，把所有字段都查出来，其实列表页可能只需要id和title，多余的字段传输起来占带宽,也拖慢速度。
分页查询优化的实用方法
第一个要学的就是索引优化，给排序和过滤的字段加索引，数据库就能直接通过索引定位数据，不用全表扫描，比如按id排序分页，id是主键默认有索引，查select id,title from goods order by id limit 20就很快，要是按其他字段排序，比如price，就得给price加索引，不然排序的时候数据库得“手动”排,慢得不行。
然后是别用大offset，改用keyset分页，keyset分页就是记住上一页最后一条数据的某个标记，下一页直接从这个标记往后查，比如上一页最后一条数据的id是100，下一页就查id > 100 order by id limit 20，这样数据库直接用id索引定位到100，往后取20条，比limit 100,20快多了，我之前帮朋友改一个项目，他用limit 10000,20查第501页，要3秒多，改成keyset分页后，0.03秒就出来了,用户当场惊掉下巴。
还有延迟加载，就是列表页只查必要的字段，比如商品列表页，用户只需要看id、title、price、img_url，就别查description、detail这些又长又占地方的字段，数据传输量小了，加载自然快，之前我做的一个电商项目，把select *改成只查5个必要字段，接口响应时间从800ms降到了300ms,用户滑动都变丝滑了。
对热门分页还能缓存结果，比如前10页数据访问量最大，就把这些页的查询结果缓存到Redis里，用户再访问直接从缓存拿，不用再查数据库，不过缓存要注意过期时间，数据更新了就得同步更新缓存,不然用户看到的就是旧数据了。
不同数据库的分页查询优化差异
MySQL和MongoDB虽然都能做分页，但优化思路不太一样，MySQL里用limit offset时，小offset还行，大offset就废了，所以深分页必须用keyset，比如基于主键id或者唯一索引字段，InnoDB的主键索引是B+树结构，有序排列,按主键分页天然适合keyset。
MongoDB用skip()和limit()，但skip(n)和MySQL的offset n一样，n越大越慢，所以MongoDB也推荐用keyset，不过它的_id字段自带时间戳和机器标识，天生有序，直接用_id > last_id就能定位下一页，比MySQL还方便点，我之前用MongoDB存日志，数据量过亿，用skip(100000)查第10001页要10秒，改成_id > ObjectId("...")后，0.1秒就搞定了。
PostgreSQL更高级点，除了keyset，还支持cursor分页，就像数据库帮你记着查询位置，下次直接从上次的位置继续查，适合需要连续翻页的场景，不过用起来稍微复杂点，小项目可能用不上,但数据量大的时候是真好用。
分页查询优化的实际案例分享
去年我接了个外包项目，是个小说网站，用户反馈“翻到第50章就卡得要死”，我看了下数据库，小说章节表有200万条数据，分页SQL是select * from chapters where novel_id=123 order by chapter_num limit 490,10，这SQL问题太大了：一是用了select *content字段特别长；二是novel_id+chapter_num没建联合索引，排序的时候全表扫描；三是offset 490,得扫500条数据。
我先把select *改成只查id, chapter_num, title等用户点进去再单独查，然后给novel_id和chapter_num建了联合索引，最后把分页改成keyset：前端记住上一页最后一章的chapter_num，下一页查where novel_id=123 and chapter_num > last_num order by chapter_num limit 10，改完之后，原来500ms的查询变成了20ms，用户翻页再也不卡了，甲方老板当场给我加了奖金。这个案例告诉我们，优化分页不能只改SQL，得从字段、索引、分页方式一起下手。
分页查询优化的注意事项
排序字段必须有索引，不然数据库排序时会用临时表或者文件排序，速度慢得离谱，比如按update_time分页，就得给update_time加索引，要是还按id排序，那就给update_time+id建联合索引,确保排序和筛选都能用到索引。
别在分页查询里做复杂计算，比如select (price*discount) as final_price from goods limit 10，如果能在应用层算就别让数据库算，数据库算多了会累，之前见过有人在分页SQL里加了三个函数计算，查询时间直接从100ms飙到1秒,移到代码里算之后立刻恢复正常。
还有，深分页和浅分页要区别对待，浅分页（前20页）用limit offset问题不大，简单好写；深分页（超过20页）必须用keyset，不然性能掉成渣，不过keyset有个缺点，不能直接跳转到第100页，只能一页页翻，所以得根据产品需求选——如果用户需要随便跳页，可能得用游标或者其他方案,不过这种场景其实不多见。
一定要测试！测试！测试！不同数据量、不同索引情况下，优化效果天差地别，我习惯用线上真实数据量的测试库，模拟用户翻到第10页、第100页、第1000页，看看每个场景的查询时间，再调整优化方案，别光靠理论,实际跑一跑才知道好不好使。
常见问题解答
分页查询为什么翻到后面越来越慢？
就像你翻一本超厚的漫画书，翻前几页唰唰快，翻到几百页的时候，你得一页页往后翻，手指都酸了，分页查询用limit offset的时候也这样，比如查第100页，offset是990，数据库得先找到前990条数据，再扔掉，只留最后10条，offset越大，找的过程就越长，自然越来越慢，就像你数1到1000,数到990的时候肯定比数到10的时候累多了呀。
没索引的话分页查询能优化吗？
基本很难！索引就像游戏里的传送门，没有索引，数据库就得在数据海里游泳找数据，游得慢死了，要是数据量小，比如只有几千条，没索引可能感觉不到慢，但数据量过万，尤其是深分页，没索引绝对卡，所以哪怕麻烦点，也得给排序和筛选的字段建索引，不然用户等不及就跑啦，实在没法建索引，就只能少返回点数据，或者用缓存把热门页存起来，不过这都是临时办法,长远看还是得有索引。
keyset分页和limit offset哪个更好用？
看情况！浅分页（前20页）用limit offset就行，简单好写，比如查前10页数据，offset小，性能没啥问题，但深分页（比如第100页）必须用keyset，不然慢到哭，不过keyset有个小缺点，不能直接跳转到指定页码，只能一页页翻，就像楼梯只能一级级上，不能直接蹦到10楼，所以如果产品允许用户只能上一页下一页，keyset YYDS；要是用户非要直接点第50页，可能得想别的办法,比如结合缓存或者预计算。
分页查询返回字段越多越慢吗？
对！就像你背书包，装的书越多越重，跑不动，分页查询返回的字段越多，数据库要传输的数据量就越大，网络传输慢，应用层处理也慢，比如查商品列表，只需要id、标题、价格就够了，非要把商品详情、规格参数都查出来，那数据量可能翻10倍，加载能不慢吗？所以一定要按需查字段，别图省事用select *,不然用户等得花儿都谢了。
优化后分页查询能快多少？
这得看原来多慢！要是原来深分页要3秒，优化后可能0.1秒都不到，快30倍都有可能！我之前改一个项目，原来查第100页要5秒，加了索引、改用keyset、只查必要字段，一顿操作下来，0.05秒就搞定了，用户都不敢相信翻页这么快，不过要是原来就很快，比如浅分页0.1秒，优化后可能0.05秒，提升没那么夸张，但蚊子再小也是肉,快一点用户体验就好一点嘛。