【W26/2024】MySQL&ES 深翻页

在数据库中，深翻页问题一直是程序运行中的性能杀手，本文将简要分析深翻页的原因，以及如何避免深翻页问题。

MySQL深翻页问题

在 MySQL 中，当查询结果集非常大时（例如，需要获取第10000页的数据，每页显示10条记录），会导致深翻页问题。这是因为 MySQL 需要扫描和跳过大量数据才能到达指定的偏移量，导致性能下降。

问题描述

假设有一个分页查询：

SELECT * FROM table_name ORDER BY id LIMIT 10000, 10;

这个查询会让 MySQL 扫描前 10000 条记录，并跳过它们，然后返回接下来的 10 条记录。随着 OFFSET 值的增加，扫描和跳过的数据量也会增加，导致查询变慢。

解决方案

子查询优化：
通过使用索引列来优化查询，避免全表扫描。例如：
```
SELECT * FROM table_name WHERE id > (
    SELECT id FROM table_name ORDER BY id LIMIT 10000, 1
) ORDER BY id LIMIT 10;
```
这种方法利用子查询先确定起始位置，再获取后续数据，从而减少扫描的数据量。
覆盖索引：
使用覆盖索引来减少数据扫描。例如，如果只需要部分列，可以利用索引来加快查询速度：
```
SELECT id, column1, column2 FROM table_name FORCE INDEX (index_name) ORDER BY id LIMIT 10000, 10;
```

延迟关联（Deferred Join）：
先查询主键，再关联查询所需列：

SELECT a.* FROM table_name a
JOIN (SELECT id FROM table_name ORDER BY id LIMIT 10000, 10) b
ON a.id = b.id;

使用标记记录（Markers）：
通过记录上次查询的最后一条记录的标记，下次从该记录之后开始查询：
```
SELECT * FROM table_name WHERE id > last_seen_id ORDER BY id LIMIT 10;
```

Elasticsearch深翻页问题

在 Elasticsearch 中，深翻页问题是因为 ES 在处理深度分页时需要保持大量的数据在内存中，从而导致性能下降和内存占用问题。

问题描述

假设有一个分页查询：

GET /index_name/_search
{
  "from": 10000,
  "size": 10,
  "query": {
    "match_all": {}
  }
}

当 from 值很大时，ES 需要保留大量的文档在内存中，以便跳过前面的文档。

解决方案

Scroll API：
Scroll API 适用于处理大量数据的场景，允许通过游标逐步获取结果：
```
GET /index_name/_search?scroll=1m
{
  "size": 100,
  "query": {
    "match_all": {}
  }
}
```
通过滚动上下文，ES 会保存搜索上下文并返回批量数据，直到所有数据被取出。
Search After：
search_after 基于排序字段来实现高效的深度分页，适用于实时性要求较高的查询：
```
GET /index_name/_search
{
  "size": 10,
  "sort": [
    {"timestamp": "asc"},
    {"_id": "asc"}
  ],
  "search_after": [1625190653000, "id_value"]
}
```
search_after 的值是上一次查询结果的最后一个文档的排序值。

Point in Time (PIT)：
PIT 用于在长时间运行的搜索中确保一致性：

POST /index_name/_pit?keep_alive=1m
{
  "query": {
    "match_all": {}
  },
  "pit": {
    "id": "PIT_ID"
  },
  "sort": [
    {"timestamp": "asc"},
    {"_id": "asc"}
  ],
  "size": 10,
  "search_after": [1625190653000, "id_value"]
}

这种方法结合 search_after 使用，确保分页查询的一致性和效率。

通过上述方法，MySQL 和 Elasticsearch 都能够有效地解决深翻页问题，提高查询性能和系统稳定性。

cosmoswei go