mysql与es列表分页调研

最新推荐文章于 2026-08-05 16:16:35 发布

原创最新推荐文章于 2026-08-05 16:16:35 发布 · 505 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

本文对比了MySQL与Elasticsearch在不同分页策略下的性能表现，包括MySQL的无条件及条件limit分页，以及ES中的from+size、search_after和scroll方法，并探讨了各自的优缺点。

mysql与es列表分页调研

随机深度分页：随机跳转页面
滚动深度分页：只能一页一页往下查询

1. 数据库分页性能

环境：测试环境(172.20.202.220)

数据库：test-data-center

数据表：d_bom

数据量：500W（单表）

1.1 无条件limit分页

select * from d_bom_temp where 1=1 LIMIT 0,10

在这里插入图片描述

当分页条数超过500000（50W）时，耗时已经超过800ms；

在这里插入图片描述

1.2 普通条件查询limit分页

select * from d_bom_temp where main_material_code = ‘BS01XX000001’ order by create_time desc limit 0,10

在这里插入图片描述

因为main_material_code为非索引，并且按创建日期排序；

1.3 limit查询优化

数据特点：1.每张表都有id字段，并且id递增；
还以非索引字段查询为例：

select * from d_bom_temp where main_material_code = ‘BS01XX000001’ order by id asc limit 1000,10

下一页：

srm_data_center_dev> select * from d_bom_temp where main_material_code = ‘BS01XX000001’ and id > 1570 order by id asc limit 0,10

在这里插入图片描述

2. ES分页性能

ES索引：cn_bom_list_test

数据量：1300W

ES提供了三种分页方式：from+size、search_after、scroll

2.1 from+size

使用示例：

GET cn_bom_list_test/_doc/_search
{
  "query": {
    "match_all": {
      }
  },
  "sort": [
    {
      "bom_id": {
        "order": "desc"
      }
    }
  ],
  "from":0,
  "size":10
}

在这里插入图片描述

from+size【m,n】在做分页时，会把[from+size]的记录数加载到内存中，然后过滤出n条；如果当m越来越大的时候，会出现加载大量数据到内存，然后过滤出非常小的一块数据；

2.1.1 优点：

允许在10000记录内进行随机跳页；

2.1.2 缺点：

翻页记录条数不能超过max_result_window，默认1W条；
当深度分页过深时，会浪费大量系统资源，并且响应耗时越来越高；

2.2 from+size优化

2.2.1 数据层面优化

from+size存在深度分页和受限分页条数问题，但可以从ES中存储的数据特征出发，寻找优化点；

比如：bom数据存在创建日期、bomId（增）；

可以考虑在使用from+size分页时，通过上一次的查询结果中的bomId或者create_time，带到下一页的查询条件中再继续查询；但不能支持跳页功能；

缺点：在查询过程中，如果有新数据插入/删除/更新，是否会对查询结果集造成影响？

2.2.2 调大max_result_window

如果真的是需要在一定时间内查看所有数据，ES也提供了search_after和scroll方案；

2.3 search_after [es>5.0]

原理：使用前一页中的一组排序值来检索匹配的下一页(类似于2.2.1方案)；

讨论：使用 search_after 要求后续的多个请求返回与第一次查询相同的排序结果序列。也就是说，即便在后续翻页的过程中，可能会有新数据写入等操作，但这些操作不会对原有结果集构成影响。

比如：每页10条，当前在第2页，当点下一页时，按理说应该查询30-40内的数据时，此时数据0-10被删除，那么分页下一页就查询到了40-50的数据做为第3页；

使用：

POST cn_bom_list_test/_search
{
  "query":{
    "bool":{
      "filter":[
        {
          "term":{
            "going_city_name":"天津市"
          }   
        }
      ]
    }
  },
  "size": 10,
  "sort": [
      {"create_time":"asc"},
      {"_id": "asc"}
  ]
}
下一页
POST cn_bom_list_test/_search
{
  "query":{
    "bool":{
      "filter":[
        {
          "term":{
            "going_city_name":"天津市"
          }   
        }
      ]
    }
  },
  "size": 10,
  "search_after": [ 1620366245162, "yJ5aRXkBLHq_upRIvcAx" ],
  "sort": [
      {"create_time":"asc"},
      {"_id": "asc"}
  ]
}

在1300W数据里面查询"going_city_name":"天津市"约33W，查询耗时约15-20ms，向后翻页一样；

2.3.1 优点

支持无限向后翻页，无性能问题；
向后翻页不会随深度影响性能问题；

2.3.2 缺点

只能向后翻，不能向前翻；

2.4 scroll

scroll查询是非实时的数据，在查询时scroll会创建一份快照；

POST cn_bom_list_test/_search?scroll=3m
{
  "query":{
    "bool":{
      "filter":[
        {
          "term":{
            "going_city_name" : "宜昌市"
          }   
        }
      ]
    }
  },
  "size": 10,
  "sort": [
      {"create_time":"asc"}
  ]
}

下一页：
POST _search/scroll                                   
{
  "scroll" : "3m",
  "scroll_id":"ZWNtLW9mZmxpbmUtdGVzdA==!DnF1ZXJ5VGhlbkZldGNoBAAAAAACfPwEFnRfcnlIY3dWUUwyWDdaNGJnV0JPVGcAAAAAAnz8AxZ0X3J5SGN3VlFMMlg3WjRiZ1dCT1RnAAAAAAJ8_AUWdF9yeUhjd1ZRTDJYN1o0YmdXQk9UZwAAAAACgERhFml5UWROeXpsU1lDZXpOX3Q5Sy1QaXc=" 
}

总量1300w,“going_city_name” : "宜昌市"约17w，分页时间约8-15ms

2.4.1 优点

支持深度分页
比较适合全量数据查询，而不是分页查询；速度超快

2.4.2 缺点

只支持向后翻，并且size是在查询时指定的，下一页的时候不能再改变size的值；超时时间也一样；
size每次不允许超过 max_result_window的限制；
数据不是实时内容；

查询结果集有保留时间限制，超过了保留时间再去查询的话，会报错：

{"error":{"root_cause":[{"type":"search_context_missing_exception","reason":"No search context found for id [41956008]"},{"type":"search_context_missing_exception","reason":"No search context found for id [41956009]"},{"type":"search_context_missing_exception","reason":"No search context found for id [41956010]"},{"type":"search_context_missing_exception","reason":"No search context found for id [41740872]"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"query","grouped":true,"failed_shards":[{"shard":-1,"index":null,"reason":{"type":"search_context_missing_exception","reason":"No search context found for id [41956008]"}},{"shard":-1,"index":null,"reason":{"type":"search_context_missing_exception","reason":"No search context found for id [41956009]"}},{"shard":-1,"index":null,"reason":{"type":"search_context_missing_exception","reason":"No search context found for id [41956010]"}},{"shard":-1,"index":null,"reason":{"type":"search_context_missing_exception","reason":"No search context found for id [41740872]"}}],"caused_by":{"type":"search_context_missing_exception","reason":"No search context found for id [41740872]"}},"status":404}