面试题手册

SQLite 的连接池对于提高应用性能很重要：连接池的概念SQLite 本身不提供内置的连接池连接池需要在应用层实现目的是重用数据库连接，减少创建和销毁连接的开销连接池的优势提高性能：避免频繁创建和销毁连接减少资源消耗：降低内存和 CPU 使用提高并发能力：管理有限数量的连接统一配置：集中管理连接参数实现连接池的要点维护一个连接队列提供获取和释放连接的方法处理连接超时和错误管理连接的最大数量Python 连接池示例 import sqlite3 from queue import Queue class SQLiteConnectionPool: def __init__(self, db_path, max_connections=5): self.db_path = db_path self.max_connections = max_connections self.pool = Queue(max_connections) for _ in range(max_connections): self.pool.put(sqlite3.connect(db_path, check_same_thread=False)) def get_connection(self): return self.pool.get() def return_connection(self, conn): self.pool.put(conn) def close_all(self): while not self.pool.empty(): conn = self.pool.get() conn.close()连接池配置参数最大连接数：根据应用并发需求设置最小空闲连接数：保持一定数量的空闲连接连接超时时间：获取连接的最大等待时间连接最大生命周期：定期刷新连接使用连接池的最佳实践使用后及时释放连接使用 try-finally 确保连接释放处理连接异常情况定期检查连接有效性监控连接池使用情况连接池的挑战线程安全：确保多线程环境下的安全访问连接泄漏：防止连接未正确释放连接过期：处理长时间未使用的连接资源限制：避免创建过多连接第三方库支持各语言都有成熟的连接池实现Python：SQLAlchemy、DBUtilsJava：HikariCP、Apache DBCPNode.js：generic-pool连接池是提高 SQLite 应用性能的重要技术手段。

SQLite 的约束机制确保数据的完整性和一致性：NOT NULL 约束确保列不接受 NULL 值插入或更新时如果违反约束会报错 CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL );UNIQUE 约束确保列中的所有值都是唯一的可以应用于单列或多列组合自动创建索引以提高性能 CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT UNIQUE );PRIMARY KEY 约束唯一标识表中的每一行自动创建 NOT NULL 和 UNIQUE 约束可以是单列或复合主键 CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT );FOREIGN KEY 约束建立表之间的关系确保引用的完整性需要启用外键约束：PRAGMA foreign_keys = ON; CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER, FOREIGN KEY (user_id) REFERENCES users(id) );CHECK 约束定义列值必须满足的条件可以包含复杂的表达式 CREATE TABLE products ( id INTEGER PRIMARY KEY, price REAL CHECK(price > 0), quantity INTEGER CHECK(quantity >= 0) );DEFAULT 约束为列指定默认值当插入时未提供值时使用默认值 CREATE TABLE users ( id INTEGER PRIMARY KEY, status TEXT DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );约束冲突处理使用 ON CONFLICT 子句处理约束冲突可选值：ROLLBACK、ABORT、FAIL、IGNORE、REPLACE INSERT OR REPLACE INTO users (id, name) VALUES (1, 'John');合理使用约束可以确保数据质量，防止无效数据的插入和更新。

SQLite 的并发控制机制是其核心特性之一：锁级别SQLite 使用不同级别的锁来管理并发访问：UNLOCKED：未锁定状态SHARED：共享锁，允许多个读操作同时进行RESERVED：预留锁，表示准备写入，但仍允许读操作PENDING：等待锁，阻止新的读操作，等待现有读操作完成EXCLUSIVE：排他锁，独占访问，阻止所有其他操作读写并发模型多读单写：SQLite 支持多个读操作同时进行，但同一时间只允许一个写操作读操作不会阻塞其他读操作写操作需要获得排他锁，会阻塞所有其他操作事务隔离模式 -- DEFERRED（默认）：第一次读操作时获取共享锁，第一次写操作时升级为排他锁 BEGIN DEFERRED TRANSACTION; -- IMMEDIATE：立即获取预留锁，防止其他写操作 BEGIN IMMEDIATE TRANSACTION; -- EXCLUSIVE：立即获取排他锁，阻止所有其他操作 BEGIN EXCLUSIVE TRANSACTION;WAL 模式（Write-Ahead Logging）WAL 模式显著提高了并发性能读操作不会阻塞写操作，写操作不会阻塞读操作写操作将更改写入 WAL 文件，而不是直接修改数据库文件检查点（Checkpoint）时将 WAL 文件的内容合并到主数据库文件死锁处理SQLite 会自动检测死锁并回滚其中一个事务应用程序应捕获 SQLITE_BUSY 错误并重试使用 sqlite3_busy_timeout() 设置忙等待超时并发优化建议使用 WAL 模式提高并发性能保持事务简短，减少锁持有时间将读操作和写操作分离到不同的事务中使用适当的隔离模式避免不必要的阻塞SQLite 的并发控制机制在保证数据一致性的同时，提供了合理的并发性能，适合中小型应用的并发需求。

SQLite 的窗口函数（Window Functions）提供了强大的数据分析能力：窗口函数的概念窗口函数对一组行执行计算，但不会将多行合并为一行类似于聚合函数，但保留了原始行的详细信息SQLite 3.25.0+ 支持窗口函数窗口函数语法 function_name(expression) OVER ( [PARTITION BY partition_expression] [ORDER BY sort_expression] [frame_clause] )常用窗口函数聚合函数：SUM()、AVG()、COUNT()、MAX()、MIN()排名函数：ROWNUMBER()、RANK()、DENSERANK()偏移函数：LAG()、LEAD()分析函数：FIRSTVALUE()、LASTVALUE()排名函数示例 -- ROW_NUMBER：连续排名 SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) as rank_num FROM students; -- RANK：相同分数相同排名，跳过后续排名 SELECT name, score, RANK() OVER (ORDER BY score DESC) as rank_num FROM students; -- DENSE_RANK：相同分数相同排名，不跳过后续排名 SELECT name, score, DENSE_RANK() OVER (ORDER BY score DESC) as rank_num FROM students;偏移函数示例 -- LAG：获取前一行的值 SELECT date, sales, LAG(sales) OVER (ORDER BY date) as prev_sales, sales - LAG(sales) OVER (ORDER BY date) as growth FROM sales_data; -- LEAD：获取后一行的值 SELECT date, sales, LEAD(sales) OVER (ORDER BY date) as next_sales FROM sales_data;分区示例 -- 按部门分区，计算每个部门内的排名 SELECT department, name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank FROM employees; -- 计算每个部门的平均工资 SELECT department, name, salary, AVG(salary) OVER (PARTITION BY department) as dept_avg_salary FROM employees;窗口帧（Frame）示例 -- 计算移动平均（3天） SELECT date, sales, AVG(sales) OVER ( ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_avg FROM sales_data; -- 计算累计总和 SELECT date, sales, SUM(sales) OVER ( ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as running_total FROM sales_data;实际应用场景 -- 计算销售额排名 SELECT product_name, sales_amount, RANK() OVER (ORDER BY sales_amount DESC) as sales_rank FROM sales; -- 计算同比增长 SELECT year, month, revenue, LAG(revenue) OVER (ORDER BY year, month) as prev_revenue, (revenue - LAG(revenue) OVER (ORDER BY year, month)) / LAG(revenue) OVER (ORDER BY year, month) * 100 as growth_rate FROM monthly_revenue; -- 找出每个类别的前3名产品 WITH ranked_products AS ( SELECT category, product_name, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) as rank_num FROM products ) SELECT * FROM ranked_products WHERE rank_num <= 3;窗口帧类型ROWS：基于物理行数RANGE：基于值范围GROUPS：基于分组性能优化为窗口函数的 ORDER BY 和 PARTITION BY 列创建索引避免在窗口函数中使用复杂表达式考虑使用子查询或 CTE 优化复杂窗口函数窗口函数是 SQLite 进行复杂数据分析的重要工具。

SQLite 的扩展机制允许开发者添加自定义功能：扩展概述SQLite 支持通过扩展添加自定义函数、聚合函数、虚拟表等扩展可以动态加载或静态链接扩展使用 C/C++ 编写，遵循 SQLite 的 API 规范加载扩展 -- 启用扩展加载 PRAGMA load_extension = ON; -- 加载扩展 SELECT load_extension('extension_path'); -- 加载内置扩展（如 FTS5） SELECT load_extension('sqlite3ext');创建标量函数 // C 代码示例：创建自定义函数 #include <sqlite3ext.h> SQLITE_EXTENSION_INIT1 static void my_function(sqlite3_context *context, int argc, sqlite3_value **argv) { if (argc != 1) { sqlite3_result_error(context, "Invalid argument count", -1); return; } const char *text = (const char *)sqlite3_value_text(argv[0]); // 处理逻辑... sqlite3_result_text(context, "result", -1, SQLITE_TRANSIENT); } #ifdef _WIN32 __declspec(dllexport) #endif int sqlite3_myextension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi); sqlite3_create_function(db, "my_function", 1, SQLITE_UTF8, NULL, my_function, NULL, NULL); return SQLITE_OK; }创建聚合函数 // C 代码示例：创建自定义聚合函数 typedef struct { double sum; int count; } AvgCtx; static void avg_step(sqlite3_context *context, int argc, sqlite3_value **argv) { AvgCtx *p = (AvgCtx *)sqlite3_aggregate_context(context, sizeof(AvgCtx)); if (p->count == 0) { p->sum = 0; } p->sum += sqlite3_value_double(argv[0]); p->count++; } static void avg_final(sqlite3_context *context) { AvgCtx *p = (AvgCtx *)sqlite3_aggregate_context(context, sizeof(AvgCtx)); if (p->count > 0) { sqlite3_result_double(context, p->sum / p->count); } else { sqlite3_result_null(context); } } int sqlite3_myextension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi); sqlite3_create_function(db, "my_avg", 1, SQLITE_UTF8, NULL, NULL, avg_step, avg_final); return SQLITE_OK; }创建虚拟表 // C 代码示例：创建虚拟表模块 typedef struct MyVtab MyVtab; struct MyVtab { sqlite3_vtab base; // 自定义数据... }; static int myCreate(sqlite3 *db, void *pAux, int argc, const char *const *argv, sqlite3_vtab **ppVtab, char **pzErr) { MyVtab *pVtab = (MyVtab *)sqlite3_malloc(sizeof(MyVtab)); // 初始化虚拟表... *ppVtab = (sqlite3_vtab *)pVtab; return SQLITE_OK; } static int myConnect(sqlite3 *db, void *pAux, int argc, const char *const *argv, sqlite3_vtab **ppVtab, char **pzErr) { // 连接虚拟表... return SQLITE_OK; } static int myBestIndex(sqlite3_vtab *pVtab, sqlite3_index_info *pIdxInfo) { // 优化查询计划... return SQLITE_OK; } static int myFilter(sqlite3_vtab_cursor *pCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv) { // 过滤数据... return SQLITE_OK; } static int myNext(sqlite3_vtab_cursor *pCursor) { // 移动到下一行... return SQLITE_OK; } static int myEof(sqlite3_vtab_cursor *pCursor) { // 检查是否到达末尾... return 0; } static int myColumn(sqlite3_vtab_cursor *pCursor, sqlite3_context *context, int i) { // 返回列值... return SQLITE_OK; } static int myRowid(sqlite3_vtab_cursor *pCursor, sqlite3_int64 *pRowid) { // 返回行 ID... return SQLITE_OK; } static sqlite3_module myModule = { .iVersion = 0, .xCreate = myCreate, .xConnect = myConnect, .xBestIndex = myBestIndex, .xDisconnect = myDisconnect, .xDestroy = myDestroy, .xOpen = myOpen, .xClose = myClose, .xFilter = myFilter, .xNext = myNext, .xEof = myEof, .xColumn = myColumn, .xRowid = myRowid }; int sqlite3_myextension_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) { SQLITE_EXTENSION_INIT2(pApi); sqlite3_create_module(db, "my_module", &myModule, 0); return SQLITE_OK; }常用内置扩展FTS3/FTS4/FTS5：全文搜索RTREE：空间索引STAT4：查询优化统计JSON1：JSON 支持GEOPOLY：地理多边形扩展的应用场景自定义业务逻辑函数集成第三方库实现特殊数据类型优化特定查询模式添加加密支持扩展开发注意事项遵循 SQLite 的内存管理规则正确处理错误情况确保线程安全提供完整的文档进行充分的测试跨语言扩展Python：使用 sqlite3.create_function()JavaScript：使用 Database.createFunction()Java：使用 Function.create()Go：使用 RegisterFunc()SQLite 的扩展机制提供了强大的扩展能力，使开发者能够根据需求定制数据库功能。

SQLite 的生成列（Generated Columns）提供了一种自动计算列值的方式：生成列的概念生成列的值由表达式自动计算得出不能直接插入或更新生成列的值分为 STORED（存储）和 VIRTUAL（虚拟）两种类型生成列类型STORED：计算结果存储在磁盘上，占用空间但查询快VIRTUAL：每次查询时计算，不占用空间但查询慢创建生成列 -- STORED 生成列 CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT, price REAL, quantity INTEGER, total REAL GENERATED ALWAYS AS (price * quantity) STORED ); -- VIRTUAL 生成列 CREATE TABLE users ( id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL );生成列的约束表达式必须确定，不能包含随机函数不能引用其他生成列不能引用同一表的其他行不能包含子查询不能使用非确定性函数（如 random()）使用生成列的优势数据一致性：自动保持数据同步减少冗余：避免手动维护计算字段性能优化：STORED 列可以建立索引代码简化：减少应用层计算逻辑为生成列创建索引 CREATE TABLE orders ( id INTEGER PRIMARY KEY, order_date TEXT, status TEXT, year INTEGER GENERATED ALWAYS AS (CAST(strftime('%Y', order_date) AS INTEGER)) STORED ); -- 为生成列创建索引 CREATE INDEX idx_year ON orders(year);修改生成列SQLite 不支持直接修改生成列，需要： -- 创建新表 CREATE TABLE products_new ( id INTEGER PRIMARY KEY, name TEXT, price REAL, quantity INTEGER, total REAL GENERATED ALWAYS AS (price * quantity * 1.1) STORED ); -- 迁移数据 INSERT INTO products_new (id, name, price, quantity) SELECT id, name, price, quantity FROM products; -- 删除旧表，重命名新表 DROP TABLE products; ALTER TABLE products_new RENAME TO products;生成列的使用场景计算字段（总价、平均值等）数据转换（日期格式转换、类型转换）数据提取（从 JSON 中提取字段）数据验证（检查条件）派生数据（基于其他列计算）性能考虑STORED 列：占用存储空间，查询快，写入慢VIRTUAL 列：不占用存储空间，查询慢，写入快根据查询频率选择合适的类型为频繁查询的 STORED 列创建索引与其他特性结合与 JSON 扩展结合：提取 JSON 字段与全文搜索结合：生成搜索文本与约束结合：验证数据完整性生成列是 SQLite 提供自动化数据维护的强大工具。

SQLite 的触发器（Trigger）机制允许在特定数据库事件发生时自动执行操作：触发器类型BEFORE 触发器：在操作执行前触发AFTER 触发器：在操作执行后触发INSTEAD OF 触发器：替代原始操作执行（主要用于视图）触发事件INSERT：插入数据时触发UPDATE：更新数据时触发DELETE：删除数据时触发创建触发器 -- BEFORE INSERT 触发器 CREATE TRIGGER update_timestamp BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.created_at = CURRENT_TIMESTAMP; END; -- AFTER UPDATE 触发器 CREATE TRIGGER log_update AFTER UPDATE ON users FOR EACH ROW BEGIN INSERT INTO audit_log (table_name, operation, user_id) VALUES ('users', 'UPDATE', NEW.id); END;触发器中的特殊引用NEW.column：引用 INSERT 或 UPDATE 操作后的新值OLD.column：引用 UPDATE 或 DELETE 操作前的旧值NEW 只在 INSERT 和 UPDATE 中可用OLD 只在 UPDATE 和 DELETE 中可用触发器应用场景自动维护时间戳字段实现审计日志数据验证和约束级联操作数据同步管理触发器 -- 查看触发器 SELECT * FROM sqlite_master WHERE type = 'trigger'; -- 删除触发器 DROP TRIGGER trigger_name; -- 列出表的触发器 PRAGMA trigger_list(table_name);限制和注意事项触发器不能直接调用其他触发器（递归）触发器执行会影响性能复杂的触发器逻辑难以调试避免在触发器中执行耗时操作触发器是 SQLite 实现自动化数据维护和业务逻辑的重要工具。

SQLite 的视图（View）提供了一种虚拟表的概念：视图的概念视图是基于 SQL 查询结果的虚拟表不存储实际数据，只存储查询定义每次查询视图时都执行底层的 SQL 语句创建视图 -- 创建简单视图 CREATE VIEW user_orders AS SELECT u.id, u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name; -- 创建带过滤条件的视图 CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';视图的优势简化复杂查询：将复杂的 JOIN 和聚合操作封装在视图中数据安全性：限制用户访问特定列或行逻辑抽象：隐藏底层表结构的变化代码重用：避免重复编写相同的查询查询视图 -- 查询视图就像查询普通表一样 SELECT * FROM user_orders WHERE order_count > 5;管理视图 -- 查看视图定义 SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'user_orders'; -- 删除视图 DROP VIEW view_name; -- 修改视图（需要先删除再创建） DROP VIEW IF EXISTS user_orders; CREATE VIEW user_orders AS ...;视图的限制SQLite 的视图是只读的（默认情况下）不能直接对视图执行 INSERT、UPDATE、DELETE 操作可以使用 INSTEAD OF 触发器实现可更新视图 CREATE TRIGGER update_user_orders INSTEAD OF UPDATE ON user_orders BEGIN UPDATE users SET name = NEW.name WHERE id = NEW.id; END;视图的使用场景报表和数据分析数据权限控制简化应用层的查询逻辑跨表查询的抽象性能考虑视图本身不存储数据，每次查询都执行底层 SQL复杂视图可能影响查询性能可以考虑使用物化视图（通过表定期刷新）视图是 SQLite 提供数据抽象和安全控制的重要机制。

SQLite 的预编译语句（Prepared Statements）是提高性能和安全性的重要机制：预编译语句的概念预编译语句是 SQL 模板，包含参数占位符SQL 语句只编译一次，可以多次执行参数值在执行时绑定，而不是编译时预编译语句的优势性能提升：避免重复解析和编译 SQL防止 SQL 注入：参数化查询自动转义特殊字符代码可读性：SQL 语句更清晰易读类型安全：参数类型自动处理使用预编译语句 # Python 示例 import sqlite3 conn = sqlite3.connect('database.db') cursor = conn.cursor() # 创建预编译语句 stmt = "INSERT INTO users (name, email) VALUES (?, ?)" # 多次执行 users = [('Alice', 'alice@example.com'), ('Bob', 'bob@example.com')] cursor.executemany(stmt, users) conn.commit()参数占位符问号占位符（?）：SQLite 标准占位符命名占位符（:name）：使用名称引用参数 -- 问号占位符 SELECT * FROM users WHERE id = ? AND status = ? -- 命名占位符 SELECT * FROM users WHERE id = :id AND status = :status预编译语句的生命周期准备（Prepare）：编译 SQL 语句绑定（Bind）：设置参数值执行（Execute）：执行语句重置（Reset）：重置语句以便再次执行释放（Finalize）：释放语句资源性能优化技巧在循环外准备语句，循环内执行使用 executemany 批量操作重用预编译语句而不是每次重新创建及时释放不再使用的语句安全最佳实践始终使用参数化查询，避免字符串拼接不要将用户输入直接拼接到 SQL 中验证参数类型和范围使用最小权限原则不同语言的实现Python：sqlite3 模块的 execute() 和 executemany()Java：PreparedStatement 接口C/C++：sqlite3preparev2() 和 sqlite3bind*()Node.js：db.prepare() 和 stmt.run()预编译语句是 SQLite 开发中不可或缺的技术，既提高了性能又增强了安全性。

SQLite 的索引机制对查询性能有重要影响：索引类型B-Tree 索引：默认索引类型，适合范围查询和排序哈希索引：仅在内存数据库中可用，适合等值查询R-Tree 索引：用于空间数据，支持地理信息查询全文索引（FTS）：用于全文搜索功能索引创建 -- 创建单列索引 CREATE INDEX idx_name ON table_name(column_name); -- 创建复合索引 CREATE INDEX idx_composite ON table_name(col1, col2); -- 创建唯一索引 CREATE UNIQUE INDEX idx_unique ON table_name(column_name);索引使用原则在 WHERE 子句、JOIN 条件、ORDER BY、GROUP BY 中频繁使用的列上创建索引选择性高的列（唯一值多）更适合建索引避免在小表上创建索引复合索引遵循最左前缀原则索引优化技巧使用 EXPLAIN QUERY PLAN 分析查询执行计划避免在索引列上使用函数或表达式使用 LIKE 'prefix%' 可以利用索引，LIKE '%suffix' 不能合理使用覆盖索引（Covering Index）避免回表索引维护索引会增加 INSERT、UPDATE、DELETE 操作的开销定期使用 ANALYZE 命令更新统计信息使用 REINDEX 重建碎片化的索引删除不再使用的索引以节省空间和提高写入性能主键和自动索引创建表时声明 PRIMARY KEY 会自动创建唯一索引UNIQUE 约束也会自动创建索引WITHOUT ROWID 表可以避免创建隐藏的 rowid 索引合理使用索引可以显著提高查询性能，但需要在查询性能和写入性能之间找到平衡。