# SQL、索引与查询计划 ## 本章解决什么问题 知识库列表最初只有几十条记录，任意查询都很快；积累到几十万条后，同一个页面可能因为深分页、错误索引或重复查询而变慢。本章目标是从访问模式设计查询，写出安全、稳定的分页 SQL，并用真实执行计划判断瓶颈。前置是表、约束、pg 查询和租户字段的含义。 前端常把筛选条件理解为表单状态；后端要把它翻译成可验证的查询合同：每次最多多少条，按什么顺序，游标如何表示，空筛选如何处理，用户能看哪些租户。SQL 既影响性能，也决定资源边界，不能为了优化速度漏掉 tenant_id 条件。 ## 查询如何一步步形成结果 先理解逻辑含义：FROM 与 JOIN 确定候选数据，WHERE 过滤行，GROUP BY 聚合，HAVING 过滤聚合结果，SELECT 投影字段，ORDER BY 建立顺序，LIMIT 限制返回数量。这不是数据库内部必须逐步执行的物理顺序，优化器会重排合法操作。懂得逻辑含义，才能判断结果是否正确；理解执行计划，才能判断成本在哪里。 列表查询只选择需要的字段，避免 SELECT * 把长文档正文、内部备注或敏感字段一起送出。JOIN 用于关联数据，聚合时需注意一对多关系会放大行数：文档连接片段后 count(*) 数到的是连接行，不一定是文档数。前端显示“文档总数”时，不能拿一个碰巧看似正确的计数凑合。 OFFSET 分页容易理解，但数据库仍需处理被跳过的部分。更关键的是并发插入或删除会改变偏移位置，让后续页重复或遗漏。游标分页把“从第几条开始”换成“从上一页最后一条的排序位置之后开始”。排序键必须形成唯一顺序，例如 created_at 加 id；只有时间相同的记录若没有补充键，翻页边界就不稳定。[PostgreSQL LIMIT/OFFSET](https://www.postgresql.org/docs/18/queries-limit.html) 强调了确定顺序的必要性。 ## 参数、索引与计划 API `client.query(text, values)` 将 SQL 结构与值分开。占位符能代替值，不能替代表名、列名或 ASC/DESC。若页面允许按标题或时间排序，应把用户选项映射到预先写好的 SQL 分支，不能直接拼接未经白名单约束的 sort 参数。[node-postgres 查询说明](https://node-postgres.com/features/queries) 明确区分了值参数与标识符。 复合 B-tree 索引 `(tenant_id, created_at DESC, id DESC)` 服务于“某租户、按时间与 id 倒序”的访问模式。索引增加写入与存储成本，不是每列建一个就一定更快。列顺序、比较条件、排序方向、返回行比例和统计信息都会影响选择，不能把经验规则简化成“不是最左列就绝不使用”。 `EXPLAIN` 返回计划估计；`EXPLAIN (ANALYZE, BUFFERS)` 会真的执行 SQL，并报告实际行数、耗时与缓冲访问。对 UPDATE、DELETE 使用 ANALYZE 也会产生真实修改，所以应在隔离练习库或可安全回滚的受控场景操作。cost 是规划器的相对成本单位，不是毫秒；actual time 才是该次执行测量，且仍受缓存、负载与硬件影响。[PostgreSQL EXPLAIN](https://www.postgresql.org/docs/18/using-explain.html) 是计划字段的直接参考。 ## 完整示例：稳定游标与索引计划 环境：Node 22.22 或 24、PostgreSQL 16+、pg 8。练习目录执行 `npm.cmd init -y` 与 `npm.cmd install pg@8`，配置自己的 DATABASE_URL；例如 PowerShell 使用 `$env:DATABASE_URL='postgresql://course:course_password@127.0.0.1:5432/ai_course'`。保存为 `query-plan.mjs`，运行 `node query-plan.mjs`。只创建当前连接的临时表与索引，不修改现有业务表。 ```js query-plan.mjs // query-plan.mjs import pg from 'pg'; import assert from 'node:assert/strict'; if (!process.env.DATABASE_URL) throw new Error('请设置 DATABASE_URL'); const client = new pg.Client({ connectionString: process.env.DATABASE_URL }); await client.connect(); try { await client.query(` CREATE TEMP TABLE query_documents ( id integer PRIMARY KEY, tenant_id integer NOT NULL, title text NOT NULL, created_at timestamptz NOT NULL ); INSERT INTO query_documents SELECT n, n % 5, '文档 ' || n, timestamptz '2026-01-01 00:00:00+00' + (n / 10) * interval '1 second' FROM generate_series(1, 40000) AS n; CREATE INDEX query_documents_page_idx ON query_documents (tenant_id, created_at DESC, id DESC); ANALYZE query_documents; `); async function listPage(tenantId, limit, cursor = null) { if (!Number.isInteger(limit) || limit < 1 || limit > 100) { throw new RangeError('每页数量必须在 1 到 100 之间'); } // 两个固定查询分支：用户只能提供值，不能提供 SQL 结构。 const sql = cursor ? `SELECT id, title, created_at::text AS cursor_time FROM query_documents WHERE tenant_id = $1 AND (created_at, id) < ($2::timestamptz, $3::integer) ORDER BY created_at DESC, id DESC LIMIT $4` : `SELECT id, title, created_at::text AS cursor_time FROM query_documents WHERE tenant_id = $1 ORDER BY created_at DESC, id DESC LIMIT $2`; const values = cursor ? [tenantId, cursor.cursor_time, cursor.id, limit] : [tenantId, limit]; return (await client.query(sql, values)).rows; } const first = await listPage(2, 20); const second = await listPage(2, 20, first.at(-1)); assert.equal(first.length, 20); assert.equal(second.length, 20); assert.equal(new Set([...first, ...second].map((row) => row.id)).size, 40); assert.ok([...first, ...second].every((row) => row.id % 5 === 2)); console.log('两页共 40 条，无重复且都属于租户 2'); const plan = await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT id, title FROM query_documents WHERE tenant_id = $1 ORDER BY created_at DESC, id DESC LIMIT $2`, [2, 20]); console.log(JSON.stringify(plan.rows[0]['QUERY PLAN'][0], null, 2)); } finally { await client.end(); } ``` 预期先输出两页验证说明，再输出一份 JSON 执行计划。计划通常包含 Limit 及索引访问节点，但不把具体节点、耗时或缓冲命中数硬编码成必然结果；统计信息、版本和环境可能让规划器选择不同路径。验收应先证明结果正确，再阅读实际计划判断是否满足工作负载。 ## 逐段理解查询与性能证据 固定 tenant_id 放在每次查询条件中，游标只决定该租户内的位置。真实 API 中 tenantId 来自已经验证的成员上下文，不能仅从查询字符串照单全收。游标采用时间与 id 的组合，并保留数据库时间的文本精度；直接把数据库微秒时间转成 JavaScript Date 再序列化，可能丢失精度并破坏边界。对外游标可以编码成不透明字符串，但编码不等于签名或授权。 元组比较 `(created_at, id) < (...)` 与倒序排列对应：下一页取比上一页末尾更小的位置。若换成正序，要同时改变比较方向与排序方向。改变筛选条件时应丢弃旧游标，或在游标中关联筛选摘要，避免把一个搜索条件的边界用于另一组结果。 阅读计划时先找实际行数偏差，再看耗时较大的节点、重复 loops、排序溢出和缓冲读取。估计十行实际百万行，往往提示统计信息或数据分布问题；不能只看到 Seq Scan 就宣布索引失效，小表或大比例扫描采用顺序扫描可能更合理。优化应围绕真实慢查询、代表性参数和可比较的数据规模。 ## 把团队文档列表翻译成可验证的问题 “团队文档与任务助手”的列表需求可以写成一句精确的话：“当前成员所在团队中，取状态为 ready 的最新二十篇文档，时间相同时按编号倒序，并返回标题与片段数量。”这里已经包含权限范围、过滤、顺序、数量和投影。先把这句话写清楚，再选择 SQL，能够避免只因页面上有五个输入框就把五个条件机械拼起来。 还应说明空条件和非法条件的含义。没有关键词表示不筛选，关键词为空白是否同样处理；limit 缺失使用什么默认值，超过上限是拒绝还是截断；游标来自不同筛选条件时如何响应。这些是接口合同，数据库不会替产品做决定。前端可以提前约束用户输入，服务端仍要独立执行同样的边界。 SQL 正确性先于速度。一个漏掉 tenant_id 的快速查询是越权，一个重复计数的索引查询是错误统计。调优时应保留结果断言，包括返回数量、排序、归属和聚合语义；不能仅比较“加索引后快了多少”，却没有发现查询条件已经被删掉。 ## JOIN 与聚合为什么容易让数量变大 一篇文档有三个片段，连接后会出现三行带同一个文档编号的数据。再连接两条标签关系，组合可能继续扩大成六行。此时 count(*) 计算的是连接结果行，不是文档数，也不一定是片段数。需要先确认聚合目标，必要时先分别聚合子表再连接，而不是在最后加 DISTINCT 期待所有重复都消失。 LEFT JOIN 保留没有匹配子记录的主表行，但子表字段为 NULL。count(*) 仍会把这行计入，count(child.id) 才只统计非空子编号。SUM 在没有有效输入时也可能返回 NULL，展示零需要显式 COALESCE。前端如果把所有 null 都格式化成零，可能掩盖查询漏掉关联或数据状态异常，所以零与未知的语义应先在服务端确定。 多条简单查询有时比一条巨大 JOIN 更清楚，但不要变成每篇文档再查一次片段数的 N+1 模式。可以先取一页文档，再用一条按这些文档编号聚合的查询获得统计，或在数据库中完成清楚的预聚合。判断依据包括一致性要求、返回规模与实际计划，而不是绝对地认为查询越少越好。 ## 最小实验：无片段文档的数量为什么不是一 保存为 `query-counts.mjs`，安装 pg 8，设置专用练习库 DATABASE_URL 后运行 `node query-counts.mjs`。只创建临时表。实验比较两种 COUNT，并验证用户输入中的引号不会改变参数化 SQL 结构。 ```js query-counts.mjs // query-counts.mjs import pg from 'pg'; import assert from 'node:assert/strict'; if(!process.env.DATABASE_URL)throw new Error('请设置 DATABASE_URL'); const db=new pg.Client({connectionString:process.env.DATABASE_URL});await db.connect(); try{ await db.query(`CREATE TEMP TABLE count_docs(id integer PRIMARY KEY,title text NOT NULL); CREATE TEMP TABLE count_chunks(id integer PRIMARY KEY,document_id integer REFERENCES count_docs(id)); INSERT INTO count_docs VALUES(1,'有片段'),(2,'无片段'); INSERT INTO count_chunks VALUES(10,1),(11,1);`); const rows=(await db.query(`SELECT d.id,count(*)::int AS joined_rows,count(c.id)::int AS chunk_count FROM count_docs d LEFT JOIN count_chunks c ON c.document_id=d.id GROUP BY d.id ORDER BY d.id`)).rows; assert.deepEqual(rows,[{id:1,joined_rows:2,chunk_count:2},{id:2,joined_rows:1,chunk_count:0}]); const suspicious="' OR 1=1 --"; const found=await db.query('SELECT id FROM count_docs WHERE title=$1',[suspicious]); assert.equal(found.rowCount,0); console.log('无片段文档的连接行数为 1，片段数为 0；引号输入仅作为普通值'); }finally{await db.end();} ``` 预期输出一条说明。参数化保护的是值不被解释成 SQL 结构，但它不会自动限制搜索范围、验证权限或处理 LIKE 模式语义。若使用 ILIKE，百分号和下划线可能表示通配符；是否允许用户使用它们，要按搜索产品合同处理，而不是把通配匹配与 SQL 注入混为一谈。 ## API 细读：参数、准备语句与超时 `query(text, values)` 的 text 应是开发者控制的 SQL，values 按占位符顺序排列。没有传参数时不能凭空使用 `$1`；值数量或类型不合要求会被数据库拒绝。错误代码例如 22P02 可表示无效文本表示，应用应把已经能提前确定的输入问题挡在查询前，同时保留数据库作为最终验证者。 pg 还接受查询配置对象，包括 text、values 和可选 name。name 可以创建按连接管理的命名准备语句，不表示整个连接池共享一份万能计划。准备语句可能减少重复解析工作，但实际计划是否适合不同参数分布，需要观察；某个租户只有十行，另一个有千万行，同一查询的最佳访问路径可能不同。不要把“用了预编译”当成完成性能调优。 结果 rows 默认是对象数组，字段名来自查询投影或别名。重复字段名可能覆盖或造成理解混乱，因此 JOIN 时显式选列并命名。rowCount 与 rows.length 的用途仍不同，聚合 count 的数据库返回类型也需要注意；本章练习将小规模计数显式转为 integer 方便断言，真实大表不能为了方便随意把可能超范围的 bigint 压缩成小整数。 数据库 statement_timeout 控制语句执行预算，连接池获取等待与客户端总超时则是另外两层。客户端不再等待，不必然意味着数据库已经停止执行。需要检查驱动和服务端实际取消合同，避免用户取消后昂贵查询继续占用连接。临时调整性能参数应限定到练习连接或事务，不能为了一条慢查询随手修改整个生产数据库。 ## 从执行计划中读出因果 计划是一棵树，底部节点产生行，父节点过滤、连接、排序或聚合这些行。先找实际行数与估计行数差异大的位置，再看是否导致错误的连接策略或大量重复执行。Nested Loop 本身不是坏词，外侧很少而内侧有高效索引时它可以很合适；外侧远大于估计时，内侧被重复执行很多次才可能成为瓶颈。 actual rows 与 actual time 在多次 loops 场景中需要按文档定义理解，不应把每层时间直接相加。父节点通常已经包含获取子节点结果的时间，简单求和会重复计入。观察 loops、扫描行、过滤掉的行和最终返回行之间的差距，可以判断系统是在有效定位数据，还是处理了大量最终被丢弃的候选。 BUFFERS 中的命中说明页面在数据库共享缓冲中被找到，不代表没有任何成本；读取也不必然等于物理磁盘实际读取，因为操作系统还有缓存。排序使用临时空间时，可能说明结果过大或内存预算不足，但增加 work_mem 会影响并发下多个操作的总内存。应该先减少不必要行和列，再根据负载讨论内存，而不是只对一个查询把参数加大。 ANALYZE 更新统计信息，帮助规划器估计分布；它不是执行计划命令中的同义修饰。`ANALYZE table` 收集统计，`EXPLAIN ANALYZE SELECT` 真正执行并测量查询。名称相似容易混淆，调试记录应写出实际执行了哪一个命令，以及数据量和参数是什么。 ## 原练习的完整参考：只分页 ready 文档 保存为 `ready-page.mjs`，安装 pg 8、配置 DATABASE_URL 后执行。状态是此接口固定的业务条件，SQL 中直接使用受控字面量 ready，租户、游标和页大小仍采用参数。示例创建部分索引，并验证两页没有混入 draft；不强行断言规划器必须选择某种节点。 完整代码已收录在本章末尾的练习参考答案中；可先阅读说明，再展开复制运行。 部分索引只包含满足条件的行，可以减少特定查询的索引体积；代价是它不服务所有状态查询，规划器还必须能够判断查询条件满足索引条件。如果接口允许任意状态并采用不同准备计划，适用性需要重新观察。与其记住“部分索引更快”，不如明确它优化的是哪条受控访问路径。 ## 索引顺序与查询形状的权衡 租户等值过滤加时间范围和排序，适合考虑以 tenant_id 开头的复合索引；只按全站时间查询则是另一种访问模式。给每个筛选字段单独建立索引，未必替代一个匹配过滤与排序的复合索引。返回大量行时，即使有索引，顺序扫描也可能更划算；选择取决于比例与实际成本。 表达式也会影响可用路径。对索引列先调用函数、隐式类型转换或使用不匹配的排序规则，可能无法利用原来设想的索引。应尽量让参数按列类型传入，并理解需要表达式索引时它服务的准确表达式。不要为了让 EXPLAIN 出现 Index Scan 就在练习里关闭顺序扫描，这只改变选择，不证明生产查询更好。 每个索引都增加插入、更新和清理成本。文档频繁更新状态，索引包含 status 或采用状态部分条件时，也会参与维护。评估时同时观察写入延迟、索引大小和读取收益。生产建索引还有锁与构建方式的考虑，不能把练习临时表上瞬间完成的 CREATE INDEX 直接当成大表上线方案。 ## 分页一致性与导出不是同一个问题 游标分页避免了深 OFFSET 的一类成本，并使向后遍历更稳定，但不会自动冻结数据库。用户修改 created_at 或排序字段，记录可能跨过已经使用的游标边界；新记录插入也会改变第一页。对于普通列表，这种实时变化可能可接受；对于“导出某个时刻的全部文档”，就需要固定截止时间、快照或后台任务策略。 大规模导出不应反复查询第一页并在内存中去重，也不应一次把所有记录加载成数组。可以按稳定键分批读取，记录最后完成位置，并把输出写成流或对象文件。导出任务恢复时仍要维持相同筛选与权限快照语义，否则重试后可能混入范围外记录。数据库分页、任务恢复和资源授权在这一场景中必须一起设计。 ## 调优记录应能被别人复现 保存查询文本、去敏后的代表参数、表规模、索引、统计更新时间和执行计划，比保存一张“十毫秒”的截图更有价值。比较前后变化时，应尽量保持数据和负载条件一致，并说明缓存是否已经预热。只执行一次就得出的巨大提升，可能只是第二次命中了缓存。 最终用用户视角验收：列表是否更快出现，筛选是否仍正确，翻页是否稳定，服务器在多用户同时访问时是否保持可接受等待。数据库计划是解释原因的工具，正确数据与稳定体验才是目标。对无法连接数据库的教材环境，只能声称语法和结构检查，不能把预期计划写成已经在生产负载验证的事实。 ## 批量参数与搜索类型的边界 一次查询多个文档时，可以把受验证的编号数组作为参数，再使用 ANY 与明确数组类型比较。空数组应得到空结果，而不是因为拼接条件为空就退化成查询全表；null 是否允许则应在接口层决定。不要把数组 join 成一串 SQL 值，即使它们看起来都是数字，也应保持查询结构与业务值分离。批量大小仍要有限制，否则一个请求可能携带几十万个编号占用解析、计划和内存资源。 标题精确匹配、包含关键词、全文检索和语义向量检索解决不同问题。B-tree 索引适合的比较方式，不代表任意包含匹配都能同样高效；全文检索又涉及分词、语言配置和排序。团队文档助手使用中文内容时，不能把一个英文默认配置的演示当作中文搜索已经验收。先定义用户到底要找标题、原文词语还是相关含义，再选择与验证相应查询机制。 搜索结果的相关性排序也要有稳定的补充键。如果多篇文档分数相同，翻页仍需要明确次序；查询条件变化后旧游标也应失效。搜索往往还需要显示匹配摘要，但摘要不能在未经授权的全库内容上生成后再过滤。性能、分页语义和权限边界从设计开始就应该同时成立，不能在最后把三者当作独立补丁拼起来。 ## 边界、练习与参考解答 游标分页改善顺序遍历，不支持随意跳到第九百页；并发更新排序字段仍会让记录移动。若需要严格可重复导出，应考虑固定快照、截止时间或后台导出任务。列表总数也可能比取一页更昂贵，产品是否真的需要精确总数，是可以讨论的需求，而不是默认每次都 count 全表。 练习：增加状态筛选，只显示 ready 文档，要求游标分页稳定且 SQL 不出现用户拼接。提示：数据模型增加 status，查询固定加入条件，再用实际计划判断是否值得调整索引。
参考答案（含完整可运行实现） 给临时表加受 CHECK 限制的 status，造数时按可解释规则分配 ready 和 draft。两个查询分支都加入 `status = $n` 并传递参数，确保游标分支和第一页一致。根据访问模式比较 `(tenant_id, status, created_at DESC, id DESC)` 与只覆盖 ready 的部分索引；部分索引的适用性还与查询条件及规划方式有关，必须看计划。测试同一时间多条数据、跨页无重复、切换筛选重新开始。下面的完整文件可用于运行并观察这些行为。 ```js ready-page.mjs // ready-page.mjs import pg from 'pg'; import assert from 'node:assert/strict'; if(!process.env.DATABASE_URL)throw new Error('请设置 DATABASE_URL'); const db=new pg.Client({connectionString:process.env.DATABASE_URL});await db.connect(); async function page(tenantId,limit=10,cursor=null){ if(!Number.isInteger(limit)||limit<1||limit>100)throw new RangeError('页大小无效'); const first=`SELECT id,status,created_at::text AS cursor_time FROM ready_docs WHERE tenant_id=$1 AND status='ready' ORDER BY created_at DESC,id DESC LIMIT $2`; const next=`SELECT id,status,created_at::text AS cursor_time FROM ready_docs WHERE tenant_id=$1 AND status='ready' AND (created_at,id)<($2::timestamptz,$3::integer) ORDER BY created_at DESC,id DESC LIMIT $4`; return(await db.query(cursor?next:first,cursor?[tenantId,cursor.cursor_time,cursor.id,limit]:[tenantId,limit])).rows; } try{ await db.query(`CREATE TEMP TABLE ready_docs(id integer PRIMARY KEY,tenant_id integer NOT NULL, status text NOT NULL CHECK(status IN ('draft','ready')),created_at timestamptz NOT NULL); INSERT INTO ready_docs SELECT n,n%3,CASE WHEN n%4=0 THEN 'draft' ELSE 'ready' END, timestamptz '2026-01-01 00:00:00+00'+(n/10)*interval '1 second' FROM generate_series(1,3000) AS n; CREATE INDEX ready_page_idx ON ready_docs(tenant_id,created_at DESC,id DESC) WHERE status='ready'; ANALYZE ready_docs;`); const first=await page(1);const second=await page(1,10,first.at(-1)); const all=[...first,...second]; assert.equal(all.length,20);assert.equal(new Set(all.map(x=>x.id)).size,20); assert.ok(all.every(x=>x.status==='ready'&&x.id%3===1)); const plan=await db.query(`EXPLAIN (ANALYZE,BUFFERS) SELECT id FROM ready_docs WHERE tenant_id=$1 AND status='ready' ORDER BY created_at DESC,id DESC LIMIT $2`,[1,10]); console.log('两页 20 条，全部 ready 且无重复'); console.log(plan.rows.map(x=>x['QUERY PLAN']).join('\n')); }finally{await db.end();} ```
## 可验证的验收标准 每页上限可控；两页无重复且无跨租户记录；相同时间的数据仍稳定排序；用户输入只进入 values；能区分计划估计与实际执行；记录一次实际查询计划，并说明优化依据而不是只展示“用了索引”。 ## 三个自测问题与答案 1. `$1` 能替换 ORDER BY 的列名吗？答案：不能，值参数不是 SQL 标识符，应使用固定白名单分支。 2. EXPLAIN ANALYZE 是否只读分析文本？答案：不是，它真正执行语句，写语句也会产生修改。 3. 为什么游标包含 id？答案：时间可能相同，补充唯一键才能构成确定的排序与翻页边界。 ## 本章验证记录 编写时使用 Node 22.22.0 对本章全部 3 个 JavaScript 完整文件执行了语法检查。以下文件未连接 PostgreSQL 实际执行，SQL 行为、查询计划与并发保证仍需在专用练习数据库验证：`query-plan.mjs`、`query-counts.mjs`、`ready-page.mjs`。 ## 本章官方参考 - [PostgreSQL Using EXPLAIN](https://www.postgresql.org/docs/18/using-explain.html)：成本、实际执行与缓冲信息。 - [PostgreSQL Multicolumn Indexes](https://www.postgresql.org/docs/18/indexes-multicolumn.html)：复合索引如何匹配查询。 - [PostgreSQL LIMIT and OFFSET](https://www.postgresql.org/docs/18/queries-limit.html)：稳定排序与分页。 - [node-postgres Queries](https://node-postgres.com/features/queries)：SQL 值参数化。