4、高性能查询接口：分页查询设计、过滤与排序、字段选择与投影、避免N+1查询

大屏数据接口，说白了就是跟时间赛跑。

用户盯着屏幕，数据如果转半天不出来，那体验就全毁了。我这些年调过不少大屏接口，发现性能瓶颈往往不在数据库本身，而在查询设计上。今天咱们就聊聊怎么把查询接口做到极致。

分页查询设计：别让用户等太久

大屏数据动辄几十万条，你不可能一次全查出来。分页是基本功，但怎么分页有讲究。

传统 limit/offset 的问题

很多同学习惯这么写：

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;

嗯，这里要注意。offset 越大，数据库需要扫描的行数就越多。offset 100000 意味着数据库要先扫 100020 行，再扔掉前 100000 行。我在项目中遇到过，翻到第 500 页时接口直接超时了。

游标分页（Cursor-based Pagination）

我个人习惯用游标分页。原理很简单：记住上一页最后一条记录的 ID，下一页直接从它后面开始查。

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 20;

-- 第二页，假设上一页最后一条 id = 100
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 20;

这样不管翻到多少页，查询都只扫描 20 行。性能稳定得一匹。

核心要点：游标分页适合大数据量场景，但要求排序字段唯一且递增。如果按时间排序，可以用时间戳作为游标。

小技巧：前端传参时，把游标值加密一下。不然用户能猜到你的数据量，有安全风险。

过滤与排序：让数据库干它该干的活

过滤和排序，很多人喜欢在代码里做。你想想看，数据查出来到应用层再过滤，那数据库的索引不就白建了吗？

过滤条件下推

把过滤条件尽量写在 SQL 里，让数据库用索引去筛选。我曾经接手过一个项目，代码里用 Java 的 stream 做过滤，查了 10 万条数据只取 100 条。嗯，那接口响应时间可想而知。

-- 错误做法：查全部再过滤
SELECT * FROM orders;
// 然后在代码里 filter(status == "PAID")

-- 正确做法：让数据库过滤
SELECT * FROM orders WHERE status = 'PAID';

排序的索引利用

排序字段一定要有索引。没有索引的话，数据库会做 filesort，数据量一大就完蛋。

-- 建联合索引，覆盖过滤和排序
CREATE INDEX idx_status_create_time ON orders(status, create_time);

-- 查询时排序字段和索引顺序一致
SELECT * FROM orders 
WHERE status = 'PAID' 
ORDER BY create_time DESC 
LIMIT 20;

避坑指南：我曾经在排序字段上没建索引，结果大屏加载时数据库 CPU 直接飙到 100%。后来加了索引，响应时间从 8 秒降到了 0.2 秒。索引这东西，真不能省。

字段选择与投影：别查不需要的列

很多同学写 SQL 习惯用 SELECT *。在大屏场景下，这简直是灾难。

你想想看，大屏上可能只显示 5 个字段，但你查了 30 个字段回来。网络传输时间、内存占用、序列化开销，全都被放大了。

投影查询

只查需要的字段，这叫投影。我建议在接口设计时就明确返回哪些字段，不要偷懒。

-- 错误做法
SELECT * FROM orders WHERE status = 'PAID';

-- 正确做法
SELECT id, order_no, amount, create_time 
FROM orders 
WHERE status = 'PAID';

动态字段选择

有些大屏支持用户自定义显示字段，这时候可以用参数控制返回哪些列。但要注意，别让用户传字段名直接拼 SQL，会有 SQL 注入风险。

// 安全做法：白名单校验
Set<String> allowedFields = Set.of("id", "order_no", "amount");
String fields = request.getParameter("fields");
// 只从白名单里取字段拼接

数据说话：我曾经把一个接口从 SELECT * 改成只查 5 个字段，响应时间从 1.2 秒降到了 0.3 秒。网络传输量减少了 80%。

避免 N+1 查询：一个循环引发的血案

N+1 查询，说白了就是先查主表，再循环查子表。这是性能杀手，没有之一。

问题场景

假设你要查所有订单及其商品明细：

-- 第一步：查订单列表（1 次查询）
SELECT * FROM orders WHERE status = 'PAID';

-- 第二步：循环查每个订单的商品（N 次查询）
for (Order order : orders) {
    SELECT * FROM order_items WHERE order_id = ?;
}

如果有 1000 个订单，就要查 1001 次数据库。嗯，这接口不慢才怪。

解决方案：批量查询

把 N 次查询合并成 1 次，用 IN 查询搞定。

-- 第一步：查订单列表
SELECT * FROM orders WHERE status = 'PAID';

-- 第二步：收集所有订单 ID，一次查完
SELECT * FROM order_items 
WHERE order_id IN (1001, 1002, 1003, ...);

-- 第三步：在代码里做关联
Map<Long, List<OrderItem>> itemMap = items.stream()
    .collect(Collectors.groupingBy(OrderItem::getOrderId));

ORM 框架的坑

用 JPA 或 Hibernate 的同学要特别注意。懒加载（Lazy Loading）很容易引发 N+1。我建议在查询时直接用 @EntityGraph 或 JOIN FETCH 提前加载关联数据。

@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
List<Order> findOrdersWithItems(@Param("status") String status);

避坑指南：我曾经在一个报表接口里用了 JPA 的懒加载，结果页面加载时发了 2000 多次 SQL。后来改成 JOIN FETCH，一次查询搞定，响应时间从 15 秒降到了 0.5 秒。ORM 虽好，但别滥用懒加载。

总结一下

高性能查询接口，核心就四点：

  • 分页：大数据量用游标分页，别用 offset
  • 过滤排序：条件下推给数据库，排序字段建索引
  • 字段选择：只查需要的列，别用 SELECT *
  • 避免 N+1：批量查询代替循环查询

这些说起来简单，但真正落地时细节很多。我建议你在项目里先抓一个慢接口做优化，把响应时间降下来，那种成就感是很爽的。

下一章咱们聊聊缓存策略，到时候会讲怎么用 Redis 给大屏接口加速。嗯，敬请期待。

← 上一章 📖 返回目录 下一章 →