关于mysql:MySQLElasticsearch-深度分页

0次阅读

共计 10659 个字符，预计需要花费 27 分钟才能阅读完成。

早些年在企业表格类的页面中，咱们总能见到分页的那种表格设计。当分页的页数太多，还设计了“跳转到 x 页”的性能。

咱们这里讲的“深度分页”，就是指当分页太多（假如有几十万页），当拜访第几万页时，必然会查问很慢。因为针对通常对于分页的搜寻，都是将几万页的数据查出来，而后取差集，只返回当前页的数据。只为了返回一页的数据，却查问的很深刻，带来的性能代价很大。

1. 少分页

首先，咱们先想想这样的需要设计是否正当，谁会在一个表格中跳到第几万页查看数据。咱们如果想要找到想要的内容，应该是通过搜寻，而非每页都点进去去看。

所以看看百度、谷歌搜索引擎的设计，都是最多只展现 10 页。

2.“翻页”代替“跳页”

那如果有些场景，我就须要查看所有页面的内容呢？例如：贴吧的评论等。ok，就算有这种状况，也不应该存在“跳页”的需要，能够设计成一页一页的“翻页”。

例如咱们身边很多 app 的设计：下拉刷新更多。每下拉一次，就多展现一页数据。

总结来看，切实想不到须要深度“跳页”的需要，那就应该在需要上杜绝。如果为了一个没必要的需要，须要对研发、服务器投入大量的资源耗费，是很节约的。

上面是从 mysql 和 elasticsearch 的维度讲讲深度分页的实现。这里先总结一下：

mysql：深度分页，包含跳页也能做，能够通过缩小回表进步性能
elasticsearch：人造不倡议跳页，但提供了翻页的优化计划

优化前

表格查问，基本上查问的字段都要求很多。依照通常分页查问的场景，假如失常分页查问的 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 节约。

这里的优化，实用于翻页、跳页的场景。那么上面针对于翻页，还能够进一步优化。

如果是翻页，那每一次查问，都能拿到上一次查问的最初一条数据。这里有个前提，就看咱们分页搜寻是依据什么字段排序的。这里先假如是依据 id 排序的，id 是程序的（自增长或雪花 ID），假如上一次查问的 id 为 15000，那么 sql 能够是：

select * from table_name where userId = 'xxx' and id > 15000  limit 0, 10

这个是咱们最罕用的 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 的限度。

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。

当咱们在 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]
      }
    ]
  }
}

应用 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"
  }
}

别离分页获取 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

正文完

mysql

发表至： mysql

2023-04-10

0

关于mysql:MySQL角色role功能介绍

关于mysql:Mysql普通索引和唯一索引

关于mysql:一次-MySQL-线上死锁分析实战

关于mysql:处理mysql无法启动且启动时服务没报错的异常情况

关于server:Server版vs数据中心版二者的区别在哪里

关于mysql:MySQLElasticsearch-深度分页

1. 深度分页

1.1. 定义

1.2. 需要上杜绝

1.3. 技术实现

2. mysql 深度分页优化

2.1. 缩小回表

2.2. 翻页优化

3. es 深度分页

3.1. from/size

3.2. scroll 遍历

3.3. search after

3.4. pit（search after）

3.5. 性能比照

站内搜索