数据库专题11：分页、搜索与排序——从 OFFSET 到稳定游标
文章列表是访问量最大的查询。初学时写limit 20 offset 1000很直观，但页码越深扫描越多，而且新文章插入会让用户看到重复或漏掉数据。本篇实现带签名游标的分页、参数化搜索和白名单排序，并用执行计划解释为什么索引顺序要和访问模式一致。
上一篇练习讲解
上一篇将发布、标签和 outbox 放在一个 PostgreSQL 事务，Mongo 失败只留下 pending 事件；for update防止并发发布重复。幂等键练习的关键是 commit 后断网仍能用 key 找回结果，不能盲目重做写操作。本篇列表查询只读，不需要长事务锁。
1. OFFSET 为什么会变慢
explain(analyze,buffers)selectid,title,created_atfromarticleswherestatus='published'orderbycreated_atdesc,iddesclimit20offset50000;数据库需要找到并丢弃前 50000 行。数据量小的时候看不出问题，十万或百万行时 p95 会明显升高。若后台确实需要跳页，可接受近似页码或限制最大 offset；面向用户的时间线使用游标更合适。
2. 游标查询实现
frombase64importurlsafe_b64encode,urlsafe_b64decodeimportjson,hmac,hashlib SECRET=b"development-only-change-me"defencode_cursor(created_at:str,article_id:int)->str:payload=json.dumps({"t":created_at,"id":article_id},separators=(",",":")).encode()sig=hmac.new(SECRET,payload,hashlib.sha256).digest()returnurlsafe_b64encode(payload+b"."+sig).decode()defdecode_cursor(token:str)->tuple[str,int]:raw=urlsafe_b64decode(token.encode())payload,sig=raw.rsplit(b".",1)ifnothmac.compare_digest(sig,hmac.new(SECRET,payload,hashlib.sha256).digest()):raiseValueError("游标无效")data=json.loads(payload)returndata["t"],int(data["id"])游标签名防止用户修改时间和 id 跳过权限范围。生产把密钥放环境变量，密钥轮换时考虑旧游标的过渡期。
selectid,title,created_atfromarticleswherestatus='published'and(created_at,id)<(:last_time,:last_id)orderbycreated_atdesc,iddesclimit:size;第一页不带last_time，后续把上一页最后一行编码成 cursor。排序必须有唯一的第二列，否则两个相同时间的文章无法确定先后。
3. 搜索与排序白名单
SORT_MAP={"new":"created_at desc, id desc","old":"created_at asc, id asc","title":"title asc, id asc",}defbuild_list_sql(sort:str,keyword:str|None):order=SORT_MAP.get(sort,SORT_MAP["new"])where="status='published'"params={"size":20}ifkeyword:where+=" and (title ilike :pattern or content ilike :pattern)"params["pattern"]=f"%{keyword[:100]}%"returnf"select id,title,created_at from articles where{where}order by{order}limit :size",params排序字段不能使用 SQL 参数，因此必须使用内部白名单。关键词走参数绑定；截断长度防止用户提交几十 MB 的搜索字符串。ILIKE '%词%'在大表上会慢，中文全文搜索应在后续引入专门索引或搜索引擎，不要宣称普通 B-tree 能解决它。
4. 标签过滤和计数
selecta.id,a.title,count(*)over()astotalfromarticles ajoinarticle_tags atonat.article_id=a.idjointags tont.id=at.tag_idwherea.status='published'andt.name=:tagorderbya.created_atdesc,a.iddesclimit:size;count(*) over()方便返回总数，但会让数据库计算完整结果；高流量接口可改为单独的近似计数或只返回has_next。多标签过滤要group by a.id having count(distinct t.id)=:tag_count，避免一篇文章只匹配其中一个标签。
验收与排错
第一页返回 20 行和 next_cursor 带 cursor 请求不重复最后一行 sort=drop table -> 回退 new，不执行任意 SQL keyword=' OR 1=1 -- -> 只作为普通文本搜索 EXPLAIN：复合索引命中，OFFSET 深页明显慢于游标若游标解码失败返回 400，并记录 request_id，不要把异常堆栈返回客户端；若翻页漏数据，确认写入与读取使用同一时区/UTC，并在 ORDER BY 中包含 id。
课后练习
实现多标签 AND 筛选和has_next；为articles(status,created_at,id)建部分索引并提交前后 EXPLAIN 文本；写测试保证篡改 cursor 签名会返回 400。下一篇用 JOIN 详情和 N+1 实验说明批量读取的重要性。
