如何在MariaDB中基于recno高效查询指定排序的前后行？

可以直接通过SQL查询实现需求，核心是利用目标行的排序键（id1, id2, date）结合recno精准定位前后行，同时通过索引优化保证大表下的查询效率。

核心思路 #

1. 先获取目标recno对应的id1, id2, date值，作为排序基准；
2. 基于排序键和recno，分别查询排序在目标行之前的x行和排序在目标行之后的x行；
3. 利用联合索引让查询快速定位范围，避免全表扫描。

具体查询步骤 #

1. 存储目标行的基准值 #

先把目标行的排序键值存入用户变量，避免重复查询：

SET @target_recno = 1234567;
SELECT id1, id2, date INTO @target_id1, @target_id2, @target_date
FROM my_table WHERE recno = @target_recno;

2. 查询后x行（排序在目标行之后的记录） #

-- 将x替换为需要的行数，比如5
SELECT *
FROM my_table
WHERE 
  (id1 > @target_id1)
  OR (id1 = @target_id1 AND id2 > @target_id2)
  OR (id1 = @target_id1 AND id2 = @target_id2 AND date > @target_date)
  OR (id1 = @target_id1 AND id2 = @target_id2 AND date = @target_date AND recno > @target_recno)
ORDER BY id1, id2, date, recno
LIMIT x;

3. 查询前x行（排序在目标行之前的记录） #

先倒序取前x行，再重新按正序排列，保证结果顺序正确：

-- 将x替换为需要的行数，比如5
SELECT *
FROM (
  SELECT *
  FROM my_table
  WHERE 
    (id1 < @target_id1)
    OR (id1 = @target_id1 AND id2 < @target_id2)
    OR (id1 = @target_id1 AND id2 = @target_id2 AND date < @target_date)
    OR (id1 = @target_id1 AND id2 = @target_id2 AND date = @target_date AND recno < @target_recno)
  ORDER BY id1 DESC, id2 DESC, date DESC, recno DESC
  LIMIT x
) AS prev_rows
ORDER BY id1, id2, date, recno;

4. 合并前后x行+目标行 #

如果需要一次性获取目标行及其前后x行，用UNION ALL拼接：

-- 将x替换为需要的行数，比如5
SELECT * FROM (
  -- 前x行
  SELECT *
  FROM my_table
  WHERE 
    (id1 < @target_id1)
    OR (id1 = @target_id1 AND id2 < @target_id2)
    OR (id1 = @target_id1 AND id2 = @target_id2 AND date < @target_date)
    OR (id1 = @target_id1 AND id2 = @target_id2 AND date = @target_date AND recno < @target_recno)
  ORDER BY id1 DESC, id2 DESC, date DESC, recno DESC
  LIMIT x

  UNION ALL

  -- 目标行
  SELECT *
  FROM my_table
  WHERE recno = @target_recno

  UNION ALL

  -- 后x行
  SELECT *
  FROM my_table
  WHERE 
    (id1 > @target_id1)
    OR (id1 = @target_id1 AND id2 > @target_id2)
    OR (id1 = @target_id1 AND id2 = @target_id2 AND date > @target_date)
    OR (id1 = @target_id1 AND id2 = @target_id2 AND date = @target_date AND recno > @target_recno)
  ORDER BY id1, id2, date, recno
  LIMIT x
) AS combined
ORDER BY id1, id2, date, recno;

索引优化（关键） #

为了让查询在大表下高效执行，必须创建匹配排序条件的联合索引：

CREATE INDEX idx_my_table_sort ON my_table (id1, id2, date, recno);

这个索引完全覆盖了排序和查询的WHERE条件，数据库可以直接通过索引快速定位前后行的范围，不需要全表扫描。

另外，recno本身已有索引，所以获取目标行的查询是O(1)级别的。

边界情况处理 #

• 如果目标行是排序后的第一行，前x行查询会返回空结果；
• 如果目标行是排序后的最后一行，后x行查询会返回空结果；
• 当存在多个行的id1, id2, date完全相同时，通过recno（唯一值）区分前后顺序，避免结果混乱。

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