SQL查询新闻表如何让未过期高亮帖优先排序且支持分页

正确的排序查询逻辑 #

核心是通过条件判断给未过期的高亮帖分配更高的排序优先级，通用SQL（兼容所有关系型数据库）写法如下：

SELECT * FROM post
ORDER BY
  -- 第一排序规则：未过期高亮帖优先级最高
  CASE WHEN highlight_date IS NOT NULL AND NOW() < highlight_date THEN 0 ELSE 1 END ASC,
  -- 第二排序规则：所有帖子统一按发布时间倒序
  post_date DESC
LIMIT 20;

如果是MySQL环境可以简化用IF函数：

SELECT * FROM post
ORDER BY
  IF(highlight_date IS NOT NULL AND NOW() < highlight_date, 0, 1) ASC,
  post_date DESC
LIMIT 20;

效果验证 #

对应给出的示例数据：

• 当前时间早于2021-11-30时：id=3的帖子第一排序值为0，其余为1，因此排在最前，剩余帖子按post_date倒序得到4、2、1，最终结果为[3,4,2,1]
• 当前时间晚于2021-11-30时：所有帖子第一排序值都为1，统一按post_date倒序得到[4,3,2,1]，完全符合需求。

大数据量场景性能优化 #

如果表数据量较大，可通过以下方式优化查询性能，避免全表扫描和文件排序：

1. 增加时间范围过滤条件
   信息流场景通常只需要展示近期发布的帖子，可在WHERE子句中限制post_date的范围，比如只查最近3个月的帖子，大幅减少需要扫描和排序的行数：

SELECT * FROM post
WHERE post_date > DATE_SUB(NOW(), INTERVAL 3 MONTH) -- 按需调整时间范围
ORDER BY
  CASE WHEN highlight_date IS NOT NULL AND NOW() < highlight_date THEN 0 ELSE 1 END ASC,
  post_date DESC
LIMIT 20;

2. 建立联合索引
   创建(post_date DESC, highlight_date)的联合索引，优化过滤和排序效率，对于LIMIT值较小的分页场景（比如每次拉20条），性能几乎无损耗。
3. 优化无限滚动分页逻辑
   如果是无限滚动场景，建议用游标分页代替OFFSET分页，比如上次拉取的最后一条帖子的post_date为last_post_date，下次查询时增加条件post_date < last_post_date，避免OFFSET过大时扫描大量无用数据。

分页兼容性说明 #

该方案完全原生支持LIMIT和OFFSET语法，不需要拆分两次查询再在应用层合并，不会增加分页或无限滚动的实现复杂度。

内容的提问来源于stack exchange，提问作者SteppingHat