面试题手册

SQLite 窗口函数适合做“保留明细行的统计”。普通 GROUP BY 会把多行压成一行，而窗口函数会在每一行旁边补一个计算结果。比如每个员工仍然是一行，但旁边多出部门排名、部门平均工资、累计销售额。SQLite 从 3.25.0 开始支持窗口函数。它常用于报表、排行榜、增长率、移动平均、每组 Top N。只要你发现自己为了“既要明细又要汇总”写了很多自连接，窗口函数就值得考虑。它能把原本拆成多条 SQL 的报表逻辑收回到一条查询里，既减少应用层循环，也更容易让数据库统一排序和过滤。基本语法怎么看？窗口函数写在 OVER (...) 后面，里面通常有三件事：按什么分组、按什么排序、窗口范围有多大。SELECT department, name, salary, RANK() OVER ( PARTITION BY department ORDER BY salary DESC ) AS dept_rankFROM employees;PARTITION BY department 表示每个部门单独排名，ORDER BY salary DESC 表示按工资从高到低。它不会减少行数，所以很适合列表页和分析报表。ROWNUMBER、RANK、DENSERANK 怎么选？三个排名函数最容易混。ROW_NUMBER 不管分数是否相同，都给连续序号；RANK 遇到并列会跳号；DENSE_RANK 遇到并列不跳号。SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_no, RANK() OVER (ORDER BY score DESC) AS rank_no, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_noFROM students;如果你要“取每个分类前 3 条记录”，通常用 ROW_NUMBER，因为它能保证每组最多 3 行。如果业务要求并列第三都算第三名，那就用 RANK 或 DENSE_RANK，但结果行数可能超过 3。LAG 和 LEAD 怎么算环比？LAG 取前一行，LEAD 取后一行。它们常用来计算增长额、增长率、状态变化。SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue, ROUND( (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2 ) AS growth_rateFROM monthly_revenue;这里用了 NULLIF(..., 0)，否则上一期收入为 0 时会出现除零问题。第一行没有上一期，结果是 NULL，这是正常边界，不要随手填 0，除非业务明确要求。窗口帧决定了累计还是移动窗口帧用来控制当前行能看到哪些行。累计求和通常从第一行到当前行，移动平均则看当前行和前几行。SELECT date, sales, SUM(sales) OVER ( ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total, AVG(sales) OVER ( ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3FROM sales_data;ROWS 按物理行数算，RANGE 按排序值范围算。多数日常报表用 ROWS 更直观，尤其日期可能缺天、金额可能重复时，RANGE 的结果容易和预期不一样。性能优化要从排序和分区下手窗口函数通常离不开排序，排序就是成本。优先给 PARTITION BY 和 ORDER BY 中的列设计复合索引。比如部门内按工资排名，可以考虑 (department, salary DESC)。CREATE INDEX idx_emp_dept_salary ON employees(department, salary DESC);但索引不是万能的。窗口函数如果要扫大量行，仍然会消耗内存和临时排序空间。报表类 SQL 可以先用 CTE 缩小数据范围，再做窗口计算；实时接口则要谨慎，别把复杂分析直接放在高并发路径上。追问窗口函数和 GROUP BY 最大区别是什么？GROUP BY 会改变结果粒度，把多行聚合成一行；窗口函数保留原始行，只是在每行旁边加计算结果。要看部门平均工资和员工明细，用窗口函数更自然。要只看每个部门一个汇总值，用 GROUP BY 更简单。踩坑点是把窗口函数当 GROUP BY 用，结果重复行很多，还以为数据错了。每组 Top N 应该用 RANK 还是 ROW_NUMBER？如果业务要求每组严格返回 N 条，用 ROW_NUMBER。如果并列名次都要保留，用 RANK 或 DENSE_RANK。取舍在于“行数稳定”还是“排名语义准确”。排行榜页面经常要保留并列，后台批处理取样通常更需要固定行数。为什么窗口函数里 ORDER BY 很重要？没有稳定排序，排名、前后行、累计值都可能不稳定。即使按日期排序，也要考虑同一天多条记录的情况，必要时加上主键做第二排序字段。边界是 LAG 和 LEAD：排序不唯一时，上一行到底是哪一行可能每次执行都不一样。生产 SQL 里不要依赖数据库“刚好按插入顺序返回”。移动平均为什么推荐 ROWS 而不是 RANGE？ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 明确表示当前行和前两行，共最多三行。RANGE 按排序值范围算，遇到重复日期、重复金额时会把同值行一起纳入，结果可能突然变大。取舍上，按最近 N 条记录算就用 ROWS，按最近 N 天或金额区间算才考虑 RANGE。很多报表误差就来自把这两个概念混在一起。窗口函数慢了怎么排查？先用 EXPLAIN QUERY PLAN 看是否出现大范围扫描和临时排序。再检查过滤条件能否提前放进 CTE 或子查询，减少参与窗口计算的行数。必要时给分区和排序列加复合索引，但别为了一个低频报表加太多索引拖慢写入。边界是超大数据分析，SQLite 可以做，但不一定应该在 App 或接口请求里实时做。

SQLite 的 CTE，也就是公用表表达式，最直接的价值是把一段复杂查询拆成几个有名字的中间结果。它只在当前 SQL 语句里有效，不会真的创建一张持久表。SQLite 从 3.8.3 开始支持 CTE，后续版本还补充了更多优化能力。如果一个 SQL 已经套了两三层子查询，或者你需要写递归查询，CTE 往往比继续堆括号更好读。但它不是性能灵药，有些场景会被优化器内联，有些场景可能被物化，真正上线前还是要看查询计划。尤其在移动端或嵌入式场景里，少一次无谓扫描就能少一次明显卡顿。基础 CTE 怎么写？最基本的写法是 WITH 名称 AS (...) SELECT ...。比如先算每个部门的平均工资，再筛出高于 60000 的部门：WITH dept_avg AS ( SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department)SELECT department, avg_salaryFROM dept_avgWHERE avg_salary > 60000ORDER BY avg_salary DESC;这段 SQL 用子查询也能写，但 CTE 的好处是中间结果有名字，读代码的人不用在嵌套括号里来回找。多个 CTE 可以连续定义，后面的 CTE 能引用前面的 CTE。WITH paid_orders AS ( SELECT * FROM orders WHERE status = 'paid' ), user_total AS ( SELECT user_id, SUM(amount) AS total FROM paid_orders GROUP BY user_id )SELECT * FROM user_total WHERE total >= 1000;递归 CTE 能解决什么问题？递归 CTE 用 WITH RECURSIVE，通常由两部分组成：锚点查询负责给出起始行，递归查询负责一层层往下找。组织架构、分类树、菜单树、图路径、数字序列都可以用它。WITH RECURSIVE org(id, name, manager_id, depth) AS ( SELECT id, name, manager_id, 0 FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, org.depth + 1 FROM employees e JOIN org ON e.manager_id = org.id WHERE org.depth < 10)SELECT * FROM org ORDER BY depth, id;这里的 WHERE org.depth < 10 不是装饰，它是安全边界。真实数据里可能有环，比如 A 的上级是 B，B 的上级又被错误地改成 A。没有终止条件，递归查询会消耗大量资源。CTE 和子查询怎么取舍？如果查询只用一次、逻辑也短，子查询没问题。如果中间结果会被多处引用，或者你希望按步骤表达业务逻辑，CTE 更清楚。CTE 还适合和窗口函数配合，比如先汇总月销售额，再计算移动平均。WITH monthly_sales AS ( SELECT strftime('%Y-%m', order_date) AS month, SUM(amount) AS total FROM orders GROUP BY month)SELECT month, total, AVG(total) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avgFROM monthly_sales;如果数据量很大，并且中间结果会被反复使用，临时表有时比 CTE 更合适。临时表可以建索引，也能拆开执行和排查；代价是多了写入和生命周期管理。写 CTE 时有哪些边界？CTE 只在当前语句中可见，下一条 SQL 就不能用了。递归 CTE 必须有明确停止条件。SQLite 的递归查询默认也有深度限制，别把它当成无限图计算引擎。还有一个常见误解：用了 CTE 就一定更快。事实上，CTE 更像可读性工具。是否更快取决于索引、过滤条件、SQLite 版本和优化器决策。遇到慢查询时，用 EXPLAIN QUERY PLAN 看它是否走了预期索引，比凭感觉改写更可靠。EXPLAIN QUERY PLANWITH duplicate_emails AS ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1)SELECT u.*FROM users uJOIN duplicate_emails d ON u.email = d.email;追问CTE 会不会真的创建临时表？不一定。SQLite 可能把 CTE 内联成子查询，也可能选择物化成临时结果，取决于版本、引用次数和查询形态。不要把 CTE 理解成固定的临时表语法，它更像给优化器的一段命名查询。边界是性能敏感 SQL：如果你依赖某种执行方式，必须看 EXPLAIN QUERY PLAN，不能只看语法。递归 CTE 为什么一定要写终止条件？递归查询会一轮接一轮地产生新行，如果数据有环或条件写错，就可能跑很久。终止条件可以是深度限制、路径去重、目标节点命中，也可以组合使用。比如图遍历时常用 instr(path, next_id) = 0 避免回到旧节点。踩坑点是 ID 为数字时拼接字符串容易误判，生产场景最好加分隔符。CTE 和视图有什么区别？CTE 只在单条 SQL 中存在，适合一次性拆分复杂查询。视图是数据库对象，适合多个查询复用同一段逻辑。取舍上，如果逻辑和当前查询强绑定，用 CTE；如果多处都要用，并且希望统一维护，用视图。边界是视图会隐藏复杂度，排查性能问题时可能比显式 CTE 更绕。大数据量下 CTE 慢怎么办？先看查询计划，确认过滤条件和 JOIN 条件有没有走索引。再判断中间结果是否过大，是否需要把高选择性的过滤提前。若同一个中间结果被多次引用，可以考虑落到临时表并加索引。不要盲目把所有子查询改成 CTE，很多慢 SQL 的根因是缺索引或排序范围太大。递归 CTE 能不能用来做图最短路径？可以做简单路径探索，但不适合复杂的大规模图算法。SQLite 不是图数据库，递归 CTE 缺少专门的剪枝和优先队列机制，数据量上来后会很吃力。小型权限树、菜单树、分类树没问题；社交关系、路线规划这类场景要谨慎。真正需要最短路径时，更建议在应用层或专门的图计算工具里处理。

SQLite 在移动开发里常见，是因为它刚好踩中了移动端本地数据的几个硬需求：不需要单独部署服务、文件就是数据库、离线可用、系统和生态支持成熟。iOS、Android、Flutter、React Native 都能用 SQLite，只是上层封装不同。它适合保存用户资料、缓存列表、草稿箱、离线队列、搜索索引和少量分析数据。它不适合替代服务端数据库，也不适合在多设备之间自动解决复杂冲突。移动端用 SQLite 的关键，不是会不会 CREATE TABLE，而是能不能处理升级、并发、同步和数据安全。表结构应该怎么设计？移动端数据库要尽量围绕页面和同步模型设计。过度规范化会让查询依赖很多 JOIN，在低端设备上更容易卡顿；完全反规范化又会让更新和冲突处理很痛苦。比较稳的做法是：核心实体单独建表，列表页常用字段适度冗余。CREATE TABLE notes ( id TEXT PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL, updated_at INTEGER NOT NULL, sync_state TEXT NOT NULL DEFAULT 'pending', deleted_at INTEGER);CREATE INDEX idx_notes_updated ON notes(updated_at);CREATE INDEX idx_notes_sync ON notes(sync_state);时间字段建议用 Unix 时间戳或标准 ISO 字符串，并全项目统一。同步状态不要靠“有没有上传过”猜，明确记录 pending、synced、failed 会省很多排查时间。移动端写入为什么必须重视事务？单条插入没问题，批量写入如果不用事务，SQLite 会频繁落盘，速度差距可能非常明显。事务还能保证应用崩溃、电量耗尽时不留下半批数据。db.beginTransaction()try { val stmt = db.compileStatement( "INSERT OR REPLACE INTO notes(id,title,body,updated_at,sync_state) VALUES(?,?,?,?,?)" ) for (note in notes) { stmt.bindString(1, note.id) stmt.bindString(2, note.title) stmt.bindString(3, note.body) stmt.bindLong(4, note.updatedAt) stmt.bindString(5, "synced") stmt.executeInsert() stmt.clearBindings() } db.setTransactionSuccessful()} finally { db.endTransaction()}不要用字符串拼接 SQL，移动端一样会遇到引号、换行、emoji、恶意输入。预编译语句既安全，也能减少重复解析 SQL 的开销。离线同步怎么做才不乱？最简单的模型是“本地先写，后台再同步”。用户编辑时先写 SQLite，并把记录标为 pending；网络恢复后上传变更，成功后标为 synced。如果服务端返回更新后的版本号或时间戳，也要一起落库。冲突处理要提前定规则。常见方案有：服务端时间戳覆盖、本地优先、字段级合并、保留冲突副本。选择哪一种取决于业务：购物车可以合并数量，文档编辑不能简单覆盖，金融类数据则更不应该在端上自行合并。性能和稳定性要看哪些点？开启 WAL 模式可以改善读写并发，尤其是一边读列表、一边后台同步的场景。分页查询要避免大 OFFSET，数据量上来后可以改成基于游标的分页。PRAGMA journal_mode = WAL;SELECT * FROM notesWHERE updated_at < ?ORDER BY updated_at DESCLIMIT 30;索引不是越多越好。读多写少的缓存表可以多建一点索引，写入频繁的事件表则要克制。每个索引都会增加写入成本和数据库体积，移动端存储空间、闪存寿命、冷启动时间都要考虑。数据安全和升级怎么处理？敏感数据不要明文裸放。可以用系统 Keychain/Keystore 保存密钥，再配合 SQLCipher 等方案加密数据库。注意密钥轮换、备份恢复和性能开销，别只在 demo 里跑通就上线。版本升级必须写成可重复、可追踪的迁移脚本。Android 的 onUpgrade、iOS 的迁移框架或 Room/GRDB 的 migration 都应该按版本递增执行，不能简单 DROP TABLE。线上用户可能从很旧的版本直接升级到最新版本，跳版本迁移是移动端最常见的坑。追问SQLite、Room/Core Data、Realm 应该怎么选？直接用 SQLite 控制力最强，但样板代码多，适合对 SQL 和迁移有明确要求的团队。Room、GRDB 这类封装保留 SQL 能力，同时减少游标和对象映射代码，是多数业务 App 的折中选择。Core Data 和 Realm 更偏对象模型，开发体验好，但复杂查询、跨端一致性和迁移细节要额外评估。取舍关键不是谁更先进，而是团队是否能长期维护它的迁移和调试成本。移动端为什么常建议开启 WAL？WAL 让读和写更容易并行，后台同步写入时，前台列表读取不容易被锁住。代价是会多出 wal/shm 文件，数据库备份、导出和清理时要一起考虑。低端设备或异常退出后，WAL 文件可能短时间变大，需要合理 checkpoint。不要只开 PRAGMA journal_mode=WAL 就不管，最好观察真实设备上的文件大小和锁等待。离线同步冲突应该在端上解决还是服务端解决？简单业务可以端上先做本地体验，最终以服务端规则为准。复杂业务最好让服务端生成权威版本，客户端只负责展示冲突和提交用户选择。边界在于数据是否可逆、是否涉及金额或权限：越重要的数据，越不能让客户端“猜”。常见踩坑是用 updated_at 谁大谁赢，结果用户在两个设备上分别编辑不同字段，后保存的把先保存的全部覆盖。数据库升级最危险的地方是什么？最危险的是只测试相邻版本升级，没有测试跨版本升级。真实用户可能半年没更新，一次从 v3 升到 v9，中间每个迁移都要能顺序执行。另一个坑是迁移脚本里改了列名或索引，却没有处理旧数据默认值，导致新版本查询直接崩溃。上线前至少准备几份老版本数据库样本跑自动化迁移测试。日期和主键在移动端有什么讲究？跨端同步时，自增整数主键容易和服务端 ID、其他设备数据冲突，客户端生成 UUID 或雪花类 ID 更稳。日期字段要统一 UTC 时间戳或 ISO 字符串，不要一会儿本地时区、一会儿服务端时区。踩坑最多的是夏令时和手动改系统时间，排序、过期判断、增量同步都会受影响。业务上真正需要可信时间时，应以服务端时间为准。

SQLite 扩展机制的核心作用，是在不改 SQLite 源码的前提下，把业务需要的函数、聚合、排序规则或虚拟表挂进数据库引擎。常见场景包括：给 SQL 增加一个字符串清洗函数、接入全文搜索或空间索引、把外部文件伪装成表查询，甚至接入加密、压缩、地理计算这类 SQLite 默认不负责的能力。扩展有哪些使用方式？SQLite 扩展主要有两种接入方式：动态加载和静态链接。动态加载适合桌面工具、内部脚本、可控的服务端环境；静态链接适合移动端、嵌入式或安全策略比较严格的应用。动态加载通常这样写：SELECT load_extension('/path/to/my_extension');SELECT my_function('hello');不少运行环境默认会关闭扩展加载，因为它本质上是在进程里加载本地动态库。命令行工具可以用 .load，C API 里要显式调用 sqlite3_enable_load_extension(db, 1)。如果是线上应用，别把扩展路径暴露给用户输入，否则很容易变成安全事故。自定义标量函数怎么写？标量函数是一进一出或多进一出的函数，最容易落地。下面这个例子把字符串转成小写，重点是入口函数名必须符合约定：sqlite3_xxx_init。#include <sqlite3ext.h>SQLITE_EXTENSION_INIT1static void lower_safe(sqlite3_context *ctx, int argc, sqlite3_value **argv) { if (argc != 1 || sqlite3_value_type(argv[0]) == SQLITE_NULL) { sqlite3_result_null(ctx); return; } const unsigned char *s = sqlite3_value_text(argv[0]); char buf[256]; snprintf(buf, sizeof(buf), "%s", s); for (char *p = buf; *p; ++p) *p = (char)tolower(*p); sqlite3_result_text(ctx, buf, -1, SQLITE_TRANSIENT);}int sqlite3_myext_init(sqlite3 *db, char **err, const sqlite3_api_routines *api) { SQLITE_EXTENSION_INIT2(api); return sqlite3_create_function(db, "lower_safe", 1, SQLITE_UTF8, 0, lower_safe, 0, 0);}实际开发里要注意两个坑：一是返回字符串时别把栈内存交给 SQLite 后马上失效，通常用 SQLITE_TRANSIENT；二是参数类型要检查，NULL、BLOB、非法 UTF-8 都可能出现。聚合函数和虚拟表适合什么场景？聚合函数适合“多行合成一个结果”，比如自定义中位数、百分位数、业务评分。它由 step 和 final 两段组成，step 逐行收集状态，final 输出结果。typedef struct { double sum; int n; } AvgCtx;static void avg_step(sqlite3_context *ctx, int argc, sqlite3_value **argv) { AvgCtx *p = sqlite3_aggregate_context(ctx, sizeof(AvgCtx)); if (sqlite3_value_type(argv[0]) != SQLITE_NULL) { p->sum += sqlite3_value_double(argv[0]); p->n++; }}static void avg_final(sqlite3_context *ctx) { AvgCtx *p = sqlite3_aggregate_context(ctx, 0); p && p->n ? sqlite3_result_double(ctx, p->sum / p->n) : sqlite3_result_null(ctx);}虚拟表更重，它不是补一个函数，而是实现一套表接口。FTS5、RTREE、CSV 虚拟表都属于这个方向。只有当你确实想把外部数据源当表查，或者需要深度参与查询计划时，才值得写虚拟表；普通业务逻辑用函数或预处理数据更省心。常用内置扩展怎么取舍？FTS5 适合全文搜索，JSON1 适合处理 JSON 字段，RTREE 适合二维范围检索，STAT4 能改善部分复杂查询的统计信息。不要一看到扩展就自己写 C，SQLite 已经内置或可编译开启的扩展优先级更高。移动端尤其要谨慎。iOS 和 Android 对动态库加载、包体大小、审核策略都有约束，很多团队会选择静态编译 SQLite，并在构建阶段打开需要的扩展。服务端脚本则可以动态加载，但要锁死扩展目录和文件白名单。追问动态加载和静态链接该怎么选？动态加载的优势是灵活，扩展可以单独升级，适合内部工具、数据清洗脚本和可控服务器。静态链接的优势是可部署性和安全性更好，适合移动端、嵌入式设备和不允许运行时加载动态库的环境。取舍点在于“是否需要运行时替换扩展”：如果不需要，静态链接通常更稳。踩坑最多的是本地能 load，线上容器或 App 沙盒里找不到动态库路径。自定义函数为什么要特别注意内存管理？SQLite 的 C API 不会替你判断返回值生命周期。比如把栈上的 char buf[256] 返回出去，如果没有用 SQLITE_TRANSIENT，调用结束后结果可能变成脏数据。更稳的做法是明确告诉 SQLite 复制结果，或者使用 SQLite 提供的内存分配函数。边界情况还包括超长输入、NULL 参数和编码不一致，这些都要在函数入口处理。什么情况下不该写 SQLite 扩展？如果逻辑只服务一两个查询，而且可以在应用层完成，通常不必写扩展。扩展会增加编译、发布、跨平台调试成本，尤其 C 代码崩溃会直接拖垮宿主进程。只有当逻辑需要贴近查询执行、复用频率很高，或者应用层处理会造成大量数据搬运时，扩展才划算。很多性能问题先用索引、CTE、临时表就能解决，不要过早上虚拟表。虚拟表开发最容易踩什么坑？虚拟表的坑主要在 xBestIndex。如果没有正确告诉优化器哪些约束可用、成本大概多少，SQLite 可能全表扫描你的外部数据源，慢到像卡死。另一个边界是事务和并发：外部数据源未必支持 SQLite 的事务语义，写入型虚拟表尤其麻烦。建议先做只读虚拟表，并用 EXPLAIN QUERY PLAN 观察查询计划。扩展会影响数据库安全性吗？会，而且影响很直接。动态扩展是本地代码，权限和宿主进程一样，不能把 load_extension 开给不可信 SQL。线上系统一般禁用任意路径加载，只允许白名单扩展，甚至直接静态链接。另一个踩坑点是 SQL 注入：即使扩展函数本身安全，拼接 SELECT load_extension(...) 的路径也可能被利用。