1. 深度分页
1.1. 定义
早些年在企业表格类的页面中，咱们总能见到分页的那种表格设计。当分页的页数太多，还设计了“跳转到x页”的性能。
咱们这里讲的“深度分页”，就是指当分页太多（假如有几十万页），当拜访第几万页时，必然会查问很慢。因为针对通常对于分页的搜寻，都是将几万页的数据查出来，而后取差集，只返回当前页的数据。只为了返回一页的数据，却查问的很深刻，带来的性能代价很大。
1.2. 需要上杜绝
1. 少分页
首先，咱们先想想这样的需要设计是否正当，谁会在一个表格中跳到第几万页查看数据。咱们如果想要找到想要的内容，应该是通过搜寻，而非每页都点进去去看。
所以看看百度、谷歌搜索引擎的设计，都是最多只展现10页。
2. “翻页”代替“跳页”
那如果有些场景，我就须要查看所有页面的内容呢？例如：贴吧的评论等。ok，就算有这种状况，也不应该存在“跳页”的需要，能够设计成一页一页的“翻页”。
例如咱们身边很多app的设计：下拉刷新更多。每下拉一次，就多展现一页数据。
总结来看，切实想不到须要深度“跳页”的需要，那就应该在需要上杜绝。如果为了一个没必要的需要，须要对研发、服务器投入大量的资源耗费，是很节约的。
1.3. 技术实现
上面是从 mysql 和 elasticsearch 的维度讲讲深度分页的实现。这里先总结一下：
- mysql：深度分页，包含跳页也能做，能够通过缩小回表进步性能
- elasticsearch：人造不倡议跳页，但提供了翻页的优化计划
2. mysql深度分页优化
2.1. 缩小回表
优化前
表格查问，基本上查问的字段都要求很多。依照通常分页查问的场景，假如失常分页查问的sql如下：
select * from table_name where userId = 'xxx' limit 10000, 10就算咱们基于 userId 创立一般索引，因为 select * ，咱们仍然要先基于一般索引查问 10010 条数据，而后回表查问 10010 次，但最初再将前 10000条数据丢掉，只取10条。
优化后
优化的sql是：
select * from table_name where id in (select id from table_name where userId = 'xxx' limit 10000, 10)这里建设子查问，子查问中只查问了主键，能够走一般索引间接查到。而最终的回表查问，只是查 10 条数据。所以，防止了 10000 次的回表，而且防止了 10000 次 select * 带来的 io 节约。
这里的优化，实用于翻页、跳页的场景。那么上面针对于翻页，还能够进一步优化。
2.2. 翻页优化
如果是翻页，那每一次查问，都能拿到上一次查问的最初一条数据。这里有个前提，就看咱们分页搜寻是依据什么字段排序的。这里先假如是依据id排序的，id是程序的（自增长或雪花ID），假如上一次查问的id为 15000，那么sql能够是：
select * from table_name where userId = 'xxx' and id > 15000 limit 0, 103. es深度分页
3.1. from/size
这个是咱们最罕用的 es 分页语法了。然而它人造不反对深度分页。
要求：from + size < max_result_window ，否则es查问就会报错。而es max_result_window默认值为 10000。为什么呢？
假如 es 索引创立了8个分片，假如咱们查问 from=1000,size=10，第101页的10条数据，理论须要查问多少数据呢？
因为 es 不晓得这第 101页的数据在哪个分片，所以协调节点发申请命令到每个分片对应的数据节点上，从每个分片都获取了 1010 条数据。
此时协调节点中一共获取到了 1010 * 8 = 8080 条数据，再基于协调节点的内存做排序，拿到合乎 from=1000,size=10 的10条数据，最终将 8070 条数据抛弃。
这里就分页到了101页，如果分页上万呢，如果es的索引分片更多呢？就为了获取这10条数据，对es的内存等性能侵害太大了，所以才有了 max_result_window 的限度。
3.2. scroll遍历
ES官网不再举荐应用Scroll API 进行深度分页。 如果您须要在分页超过 10,000 个点击时保留索引状态，请应用带有工夫点 (PIT) 的 search_after 参数。所以这里就不多说了。
Scroll API 原理上是对某次查问生成一个游标 scroll_id ， 后续的查问只须要依据这个游标去取数据，直到后果集中返回的 hits 字段为空，就示意遍历完结。scroll_id 的生成能够了解为建设了一个长期的历史快照，在此之后的增删改查等操作不会影响到这个快照的后果。
所有文档获取结束之后，须要手动清理掉 scroll_id 。尽管es 会有主动清理机制，然而 srcoll_id 的存在会消耗大量的资源来保留一份以后查问后果集映像，并且会占用文件描述符。所以用完之后要及时清理。应用 es 提供的 CLEAR_API 来删除指定的 scroll_id。
3.3. search after
当咱们在es搜寻时用到 sort 排序，默认返回数据中每个对象都会带上 sort值，如：
1. 全副数据
申请：
GET operation_log/_search{ "query": { "match_all": {} }, "sort": [ { "operation_time": { "order": "desc" } } ]}返回：
{ "took" : 0, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 7, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "operation_log", "_type" : "_doc", "_id" : "0nPH_4MBMgWicIn2Nxwj", "_score" : null, "_source" : { "ip" : "22.1.11.0", "trace_id" : "780821e89b2dc653", "operation_time" : "2022-10-02 12:31:10", "module" : "资源核心", "action_code" : "DELETE", "location" : "资源核心->文件治理", "object_id" : "fffff-1", "object_name" : "《2022员工绩效打分细则》", "operator_id" : "operator_id-3", "operator_name" : "王五", "operator_dept_id" : "operator_dept_id-2", "operator_dept_name" : "人力资源部", "changes" : [ ] }, "sort" : [ 1664713870000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "1nPI_4MBMgWicIn2-hzS", "_score" : null, "_source" : { "ip" : "10.0.0.0", "trace_id" : "670021ff9a28768", "operation_time" : "2022-10-02 09:32:00", "module" : "资源核心", "action_code" : "DELETE", "location" : "资源核心->文件治理", "object_id" : "fffff-b", "object_name" : "《有NULL的文档》", "operator_id" : "operator_id-b", "operator_name" : "路人B", "changes" : [ ] }, "sort" : [ 1664703120000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "1XPI_4MBMgWicIn21xwt", "_score" : null, "_source" : { "ip" : "10.0.0.0", "trace_id" : "670021ff9a28ei6", "operation_time" : "2022-10-02 09:31:00", "module" : "资源核心", "action_code" : "DELETE", "location" : "资源核心->文件治理", "object_id" : "fffff-a", "object_name" : "《有空字符串的文档》", "operator_id" : "operator_id-a", "operator_dept_id" : "", "operator_dept_name" : "", "operator_name" : "路人A", "changes" : [ ] }, "sort" : [ 1664703060000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "0XPG_4MBMgWicIn2_Bxn", "_score" : null, "_source" : { "trace_id" : "990821e89a2dc653", "operator_id" : "operator_id-2", "ip" : "22.1.11.0", "module" : "资源核心", "action_code" : "UPDATE", "changes" : [ { "old_value" : "仅李四可查看", "new_value" : "全员可查看", "field_name" : "查看权限" }, { "old_value" : "仅李四可查看", "new_value" : "人力资源部可查看", "field_name" : "编辑权限" } ], "operator_dept_id" : "operator_dept_id-2", "object_id" : "fffff-1", "operator_dept_name" : "人力资源部", "operator_name" : "李四", "operation_time" : "2022-09-05 11:31:10", "object_name" : "《2022员工绩效打分细则》", "location" : "资源核心->文件治理->文件权限" }, "sort" : [ 1662377470000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "1HPI_4MBMgWicIn2rhyD", "_score" : null, "_source" : { "trace_id" : "670021e89a2dc655", "operator_id" : "operator_id-2", "ip" : "10.1.11.5", "module" : "企业组织", "action_code" : "DELETE", "changes" : [ ], "operator_dept_id" : "operator_dept_id-2", "object_id" : "xxxxx-1", "operator_dept_name" : "人力资源部", "operator_name" : "李四", "operation_time" : "2022-05-05 10:35:12", "object_name" : "成德善", "location" : "企业组织->员工治理->身份治理" }, "sort" : [ 1651746912000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "03PI_4MBMgWicIn2chxb", "_score" : null, "_source" : { "ip" : "10.1.11.1", "trace_id" : "670021e89a2dc7b6", "operation_time" : "2022-05-03 09:35:10", "module" : "企业组织", "action_code" : "ADD", "location" : "企业组织->员工治理->身份治理", "object_id" : "xxxxx-2", "object_name" : "成宝拉", "operator_id" : "operator_id-1", "operator_name" : "张三", "operator_dept_id" : "operator_dept_id-1", "operator_dept_name" : "研发核心-后端一部", "changes" : [ { "field_name" : "姓名", "new_value" : "成宝拉" }, { "field_name" : "性别", "new_value" : "女" }, { "field_name" : "手机号码", "new_value" : "13055770002" }, { "field_name" : "邮箱", "new_value" : "baola@qq.com" } ] }, "sort" : [ 1651570510000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "0HPG_4MBMgWicIn2yxxE", "_score" : null, "_source" : { "ip" : "10.1.11.1", "trace_id" : "670021ff9a2dc6b7", "operation_time" : "2022-05-02 09:31:18", "module" : "企业组织", "action_code" : "UPDATE", "location" : "企业组织->员工治理->身份治理", "object_id" : "xxxxx-1", "object_name" : "成德善", "operator_id" : "operator_id-1", "operator_name" : "张三", "operator_dept_id" : "operator_dept_id-1", "operator_dept_name" : "研发核心-后端一部", "changes" : [ { "field_name" : "手机号码", "old_value" : "13055660000", "new_value" : "13055770001" }, { "field_name" : "姓名", "old_value" : "成德善", "new_value" : "成秀妍" } ] }, "sort" : [ 1651483878000 ] } ] }}能够查到一共7条数据，而后每条数据的 sort。
2. 查 0,3 条数据
申请：
GET operation_log/_search{ "query": { "match_all": {} }, "sort": [ { "operation_time": { "order": "desc" } } ], "from": 0, "size": 3}返回：
{ "took" : 1, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 7, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "operation_log", "_type" : "_doc", "_id" : "0nPH_4MBMgWicIn2Nxwj", "_score" : null, "_source" : { "ip" : "22.1.11.0", "trace_id" : "780821e89b2dc653", "operation_time" : "2022-10-02 12:31:10", "module" : "资源核心", "action_code" : "DELETE", "location" : "资源核心->文件治理", "object_id" : "fffff-1", "object_name" : "《2022员工绩效打分细则》", "operator_id" : "operator_id-3", "operator_name" : "王五", "operator_dept_id" : "operator_dept_id-2", "operator_dept_name" : "人力资源部", "changes" : [ ] }, "sort" : [ 1664713870000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "1nPI_4MBMgWicIn2-hzS", "_score" : null, "_source" : { "ip" : "10.0.0.0", "trace_id" : "670021ff9a28768", "operation_time" : "2022-10-02 09:32:00", "module" : "资源核心", "action_code" : "DELETE", "location" : "资源核心->文件治理", "object_id" : "fffff-b", "object_name" : "《有NULL的文档》", "operator_id" : "operator_id-b", "operator_name" : "路人B", "changes" : [ ] }, "sort" : [ 1664703120000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "1XPI_4MBMgWicIn21xwt", "_score" : null, "_source" : { "ip" : "10.0.0.0", "trace_id" : "670021ff9a28ei6", "operation_time" : "2022-10-02 09:31:00", "module" : "资源核心", "action_code" : "DELETE", "location" : "资源核心->文件治理", "object_id" : "fffff-a", "object_name" : "《有空字符串的文档》", "operator_id" : "operator_id-a", "operator_dept_id" : "", "operator_dept_name" : "", "operator_name" : "路人A", "changes" : [ ] }, "sort" : [ 1664703060000 ] } ] }}拿到最初一条（第3条）数据的 sort值 [1664703060000]，查问后续的3条。
3. 查 3,6 条数据
申请：
GET operation_log/_search{ "query": { "match_all": {} }, "sort": [ { "operation_time": { "order": "desc" } } ], "from": 0, "size": 3, "search_after": [ 1664703060000 ]}返回：
{ "took" : 1, "timed_out" : false, "_shards" : { "total" : 1, "successful" : 1, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : { "value" : 7, "relation" : "eq" }, "max_score" : null, "hits" : [ { "_index" : "operation_log", "_type" : "_doc", "_id" : "0XPG_4MBMgWicIn2_Bxn", "_score" : null, "_source" : { "trace_id" : "990821e89a2dc653", "operator_id" : "operator_id-2", "ip" : "22.1.11.0", "module" : "资源核心", "action_code" : "UPDATE", "changes" : [ { "old_value" : "仅李四可查看", "new_value" : "全员可查看", "field_name" : "查看权限" }, { "old_value" : "仅李四可查看", "new_value" : "人力资源部可查看", "field_name" : "编辑权限" } ], "operator_dept_id" : "operator_dept_id-2", "object_id" : "fffff-1", "operator_dept_name" : "人力资源部", "operator_name" : "李四", "operation_time" : "2022-09-05 11:31:10", "object_name" : "《2022员工绩效打分细则》", "location" : "资源核心->文件治理->文件权限" }, "sort" : [ 1662377470000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "1HPI_4MBMgWicIn2rhyD", "_score" : null, "_source" : { "trace_id" : "670021e89a2dc655", "operator_id" : "operator_id-2", "ip" : "10.1.11.5", "module" : "企业组织", "action_code" : "DELETE", "changes" : [ ], "operator_dept_id" : "operator_dept_id-2", "object_id" : "xxxxx-1", "operator_dept_name" : "人力资源部", "operator_name" : "李四", "operation_time" : "2022-05-05 10:35:12", "object_name" : "成德善", "location" : "企业组织->员工治理->身份治理" }, "sort" : [ 1651746912000 ] }, { "_index" : "operation_log", "_type" : "_doc", "_id" : "03PI_4MBMgWicIn2chxb", "_score" : null, "_source" : { "ip" : "10.1.11.1", "trace_id" : "670021e89a2dc7b6", "operation_time" : "2022-05-03 09:35:10", "module" : "企业组织", "action_code" : "ADD", "location" : "企业组织->员工治理->身份治理", "object_id" : "xxxxx-2", "object_name" : "成宝拉", "operator_id" : "operator_id-1", "operator_name" : "张三", "operator_dept_id" : "operator_dept_id-1", "operator_dept_name" : "研发核心-后端一部", "changes" : [ { "field_name" : "姓名", "new_value" : "成宝拉" }, { "field_name" : "性别", "new_value" : "女" }, { "field_name" : "手机号码", "new_value" : "13055770002" }, { "field_name" : "邮箱", "new_value" : "baola@qq.com" } ] }, "sort" : [ 1651570510000 ] } ] }}3.4. pit（search after）
应用 search_after 须要具备雷同查问和排序值的多个搜寻申请。 如果在这些申请之间产生刷新，后果的程序可能会发生变化，从而导致跨页面的后果不统一。 为防止出现这种状况，您能够创立一个工夫点 (PIT) 以保留搜寻中的以后索引状态。
1. 创立pit
申请
POST operation_log/_pit?keep_alive=10m返回：
{ "id" : "i9W1AwENb3BlcmF0aW9uX2xvZxZYRllDdWw4d1NFbTZDaXJhYXU1VGhnABY1SVZXdWt3Y1JEaUNndzMwNHFZNkhBAAAAAAAAACTZFmtMZ2xzTTVZVGI2V1JxaDV6dFdXMVEAARZYRllDdWw4d1NFbTZDaXJhYXU1VGhnAAA="}2. 带上pit去查问 0,3=
GET _search{ "query": { "match_all": {} }, "sort": [ { "operation_time": { "order": "desc" } } ], "from": 0, "size": 3, "pit": { "id": "i9W1AwENb3BlcmF0aW9uX2xvZxZYRllDdWw4d1NFbTZDaXJhYXU1VGhnABY1SVZXdWt3Y1JEaUNndzMwNHFZNkhBAAAAAAAAACTZFmtMZ2xzTTVZVGI2V1JxaDV6dFdXMVEAARZYRllDdWw4d1NFbTZDaXJhYXU1VGhnAAA=", "keep_alive": "10m" }}这里要留神的是 GET _search，并没有指定索引。因为从设计上来看，通过 pit id，就曾经能找到当初创立pit的索引了。
2. 带上pit去查问 3,6=
GET _search{ "query": { "match_all": {} }, "sort": [ { "operation_time": { "order": "desc" } } ], "from": 0, "size": 3, "search_after": [ 1664703060000 ], "pit": { "id": "i9W1AwENb3BlcmF0aW9uX2xvZxZYRllDdWw4d1NFbTZDaXJhYXU1VGhnABY1SVZXdWt3Y1JEaUNndzMwNHFZNkhBAAAAAAAAACTZFmtMZ2xzTTVZVGI2V1JxaDV6dFdXMVEAARZYRllDdWw4d1NFbTZDaXJhYXU1VGhnAAA=", "keep_alive": "10m" }}3.5. 性能比照
别离分页获取1 - 10，49000 - 49010，99000 - 99010范畴各10条数据（前提10w条），性能大抵是这样：
| 分页形式 | 1~10 | 49000~49010 | 99000~99010 |
|---|---|---|---|
| from/size | 8ms | 30ms | 117ms |
| scroll | 7ms | 66ms | 36ms |
| search_after | 5ms | 8ms | 7ms |