数据库 | 从100W 条数据中毫秒级找到90W后的10条网站首页 其他

数据库 | 从100W 条数据中毫秒级找到90W后的10条

小Mie不吃饭 2025-04-13 12:01:03

简介数据库 | 从100W 条数据中毫秒级找到90W后的10条

* 本篇文章仅代表个人所遇到的问题 *

* 创作时间：2025.2.9 *

* 公棕号： wmcode *

这是一个关于“从100万条数据中毫秒级找到第90万后的10条数据”的解决方案，结合了多种技术手段，包括条件查询、子查询、跨表查询、游标分页、ES查询以及限定业务最大页数等。

一、数据库设计与索引优化

数据库设计
- 确保数据表结构合理，字段类型合适。例如，假设有一个表data_table，包含字段id（主键）、content（数据内容）等。
- 示例表结构：
  
  sql复制
```
CREATE TABLE data_table (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
索引优化
- 对id字段建立主键索引（自动建立）。
- 如果查询条件涉及其他字段（如created_at），可以考虑建立复合索引。例如：
  
  sql复制
```
CREATE INDEX idx_created_at ON data_table (created_at);
```

二、SQL查询实现

1. 条件查询（普通分页）

使用LIMIT和OFFSET实现分页查询。但这种方式在大数据量下效率较低，因为OFFSET会导致数据库扫描大量数据。
```
SELECT * FROM data_table ORDER BY id LIMIT 10 OFFSET 900000;
```
优化思路：尽量避免使用OFFSET，改用其他方法。

2. 子查询优化

使用子查询找到第90万条记录的id，然后查询其后的10条数据。

SELECT * FROM data_table
WHERE id > (
    SELECT id FROM data_table ORDER BY id LIMIT 1 OFFSET 899999
)
ORDER BY id LIMIT 10;

优化思路：子查询部分可以通过索引快速定位，减少扫描范围。

3. 跨表查询（如果涉及多表关联）

假设数据分布在多个表中，可以使用JOIN操作。，例如data_table和metadata_table：

SELECT dt.*
FROM data_table dt
JOIN metadata_table mt ON dt.id = mt.data_id
WHERE dt.id > (
    SELECT dt.id
    FROM data_table dt
    JOIN metadata_table mt ON dt.id = mt.data_id
    ORDER BY dt.id LIMIT 1 OFFSET 899999
)
ORDER BY dt.id LIMIT 10;

优化思路：确保关联字段有索引，减少关联查询的开销。

三、游标分页

使用游标分页
- 游标分页是一种高效的分页方式，适用于大数据量场景。
- 实现步骤：
  - 第一次查询：获取第90万条记录的id。
```
SELECT id FROM data_table ORDER BY id LIMIT 1 OFFSET 899999;
```
  - 假设返回的id为900000。
  - 第二次查询：获取其后的10条数据。
```
SELECT * FROM data_table WHERE id > 900000 ORDER BY id LIMIT 10;
```

四、ES查询

使用Elasticsearch（ES）

如果数据量非常大，可以考虑将数据同步到ES中，利用ES的高效检索能力。

ES索引创建：

PUT /data_index
{
    "mappings": {
        "properties": {
            "id": { "type": "long" },
            "content": { "type": "text" },
            "created_at": { "type": "date" }
        }
    }
}

ES查询：

POST /data_index/_search
{
    "size": 10,
    "query": {
        "range": {
            "id": {
                "gt": 900000
            }
        }
    },
    "sort": [
        { "id": { "order": "asc" } }
    ]
}

五、限定业务最大页数

业务逻辑限制

在应用层限制分页的最大页数。例如，只允许查询前100页（每页10条，共1000条）。
如果用户请求超过最大页数，返回错误提示。

代码示例（Python）：

MAX_PAGE = 100
page = int(request.args.get('page', 1))
if page > MAX_PAGE:
    return "Page out of range", 400
offset = (page - 1) * 10
query = f"SELECT * FROM data_table ORDER BY id LIMIT 10 OFFSET {offset}"
results = execute_query(query)
return results