数据库专题11分页、搜索与排序——从 OFFSET 到稳定游标文章列表是访问量最大的查询。初学时写limit 20 offset 1000很直观但页码越深扫描越多而且新文章插入会让用户看到重复或漏掉数据。本篇实现带签名游标的分页、参数化搜索和白名单排序并用执行计划解释为什么索引顺序要和访问模式一致。上一篇练习讲解上一篇将发布、标签和 outbox 放在一个 PostgreSQL 事务Mongo 失败只留下 pending 事件for update防止并发发布重复。幂等键练习的关键是 commit 后断网仍能用 key 找回结果不能盲目重做写操作。本篇列表查询只读不需要长事务锁。1. OFFSET 为什么会变慢explain(analyze,buffers)selectid,title,created_atfromarticleswherestatuspublishedorderbycreated_atdesc,iddesclimit20offset50000;数据库需要找到并丢弃前 50000 行。数据量小的时候看不出问题十万或百万行时 p95 会明显升高。若后台确实需要跳页可接受近似页码或限制最大 offset面向用户的时间线使用游标更合适。2. 游标查询实现frombase64importurlsafe_b64encode,urlsafe_b64decodeimportjson,hmac,hashlib SECRETbdevelopment-only-change-medefencode_cursor(created_at:str,article_id:int)-str:payloadjson.dumps({t:created_at,id:article_id},separators(,,:)).encode()sighmac.new(SECRET,payload,hashlib.sha256).digest()returnurlsafe_b64encode(payloadb.sig).decode()defdecode_cursor(token:str)-tuple[str,int]:rawurlsafe_b64decode(token.encode())payload,sigraw.rsplit(b.,1)ifnothmac.compare_digest(sig,hmac.new(SECRET,payload,hashlib.sha256).digest()):raiseValueError(游标无效)datajson.loads(payload)returndata[t],int(data[id])游标签名防止用户修改时间和 id 跳过权限范围。生产把密钥放环境变量密钥轮换时考虑旧游标的过渡期。selectid,title,created_atfromarticleswherestatuspublishedand(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):orderSORT_MAP.get(sort,SORT_MAP[new])wherestatuspublishedparams{size:20}ifkeyword:where and (title ilike :pattern or content ilike :pattern)params[pattern]f%{keyword[:100]}%returnfselect 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_ida.idjointags tont.idat.tag_idwherea.statuspublishedandt.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 请求不重复最后一行 sortdrop table - 回退 new不执行任意 SQL keyword OR 11 -- - 只作为普通文本搜索 EXPLAIN复合索引命中OFFSET 深页明显慢于游标若游标解码失败返回 400并记录 request_id不要把异常堆栈返回客户端若翻页漏数据确认写入与读取使用同一时区/UTC并在 ORDER BY 中包含 id。课后练习实现多标签 AND 筛选和has_next为articles(status,created_at,id)建部分索引并提交前后 EXPLAIN 文本写测试保证篡改 cursor 签名会返回 400。下一篇用 JOIN 详情和 N1 实验说明批量读取的重要性。