golang操纵sqlite3加速本地结构化数据查询

玛卡巴卡的卡巴卡玛 · 2024-12-19 14:16:50

摘要

在没有网络支持且仅能使用 Go 语言包的开辟环境中，如果需要频仍查询一个较大的 CSV 文件，有几种优化方案可以考虑。

传统的遍历方法：
- 使用 Go 的 csv 库，针对每次查询都遍历整个 CSV 文件。这种方法简单易懂，但性能较差，尤其在数据量较大时，每次查询都需要重新读取整个文件，效率低下。
将数据加载到内存（Map 存储）：
- 通过一次性读取 CSV 文件并将其存储在内存中的 map 结构中，可以大幅进步查询性能。使用 map 提供的 O(1) 查找时间，查询操纵会变得非常迅速。这种方法实用于数据集较小到中等的情况，且查询操纵较为简单。需要留意的是，数据量过大会导致内存消耗过高。
使用 SQLite 存储数据：
- 如果需要更高效的查询，特别是支持更复杂筛选的场景，可以考虑将 CSV 文件的数据导入到 SQLite 中。SQLite 提供了轻量级的 SQL 查询功能，可以通过创建表和索引来加速数据检索，同时支持复杂的查询操纵（如筛选、排序、聚合等）。SQLite 不需要单独安装任何服务，直接通过 Go 包 github.com/mattn/go-sqlite3 即可进行操纵，得当在 Golang 环境中使用。

每种方法都有其实用场景：对于小数据集和简单查询，map 存储足够快速且轻量；而对于较大数据集或复杂查询，SQLite 提供了更好的查询灵活性和性能。
Sqlite3

SQLite3 是一个开源的、轻量级的关系型数据库管理系统 (RDBMS)，它的重要特点是将数据库引擎嵌入到应用程序中，而无需依赖服务器端数据库系统。SQLite3 以其小巧、高效、易于集成和高可靠性成为许多桌面、移动装备及嵌入式系统中常用的数据库办理方案。
SQLite3 的根本特点

嵌入式数据库
SQLite3 是一个 嵌入式数据库，意味着它是一个库（.dll 或 .so 文件）而非独立的服务器程序。数据库的所有数据都存储在单一的文件中，应用程序通过链接该库来操纵数据库。这使得 SQLite3 在轻量级应用和小型项目中非常流行。
跨平台支持
SQLite3 支持险些所有的操纵系统，包括但不限于 Windows、Linux、macOS、iOS、Android 等。由于它是 C 语言编写的，可以轻松地跨平台移植。
无服务器架构
SQLite3 是 无服务器的，这意味着没有独立的数据库进程或服务。应用程序直接访问数据库文件，且访问数据库不依赖网络连接。它通过文件系统进行 I/O 操纵，查询和写入数据速率非常快。
事务支持
SQLite3 完全支持 ACID（原子性、一致性、隔离性、持久性）事务，这意味着它可以确保数据的完整性。在发生瓦解时，SQLite3 会自动回滚到事务开始之前的状态，确保数据的可靠性。
轻量级
SQLite3 的二进制文件非常小，通常只有几百 KB（详细大小取决于编译选项）。它不需要设置或安装复杂的数据库服务器，得当于嵌入式应用、小型应用、单用户应用和其他资源有限的系统。
零设置
SQLite3 无需安装或设置数据库引擎。只需要包罗数据库文件和链接库即可，完全得当于需要零设置的场景。所有数据库设置都是在数据库文件级别进行的。
自包罗
SQLite3 是 自包罗的，它包罗了所有的功能。SQLite 的操纵和设置不依赖于外部软件或库。SQLite3 将数据库及其管理功能直接集成到应用程序中，进一步简化了使用过程。

SQLite3 的工作原理
SQLite3 将数据存储在单一的文件中，这个文件中不仅存储了数据，还包括了数据库的元数据（如表结构、索引等）。这种计划使得 SQLite3 对于需要将数据库存储在单一文件中的场景非常合适。
SQLite3 的数据库文件通常具有 .sqlite 或 .db 扩展名，且它是 按页存储数据的。SQLite3 使用 1024 字节（默认）为根本存储单元，每个页面可以存储一个数据记载、索引节点、事务日志等。
当应用程序需要实验 SQL 查询时，它通过 SQLite3 的 API 来与数据库文件进行交互。SQLite3 会实验 SQL 语句，操纵数据库文件，然后返回结果。
SQLite3 的上风

性能良好
SQLite3 在单机应用中表现出色。由于 SQLite3 是直接对磁盘文件进行操纵，而且没有复杂的数据库管理层，因此它的 I/O 性能相对较高，尤其在查询操纵时速率较快。对于中小型数据集，SQLite3 险些可以或许提供与传统数据库相媲美的性能。
高效的内存使用
SQLite3 将数据直接存储到文件中，避免了传统数据库系统的内存消耗。在单个数据库连接上，SQLite3 能高效地使用内存，而且能在内存不足时通太过页操纵优化性能。
简单易用
SQLite3 的使用极为简单，不需要任何设置或启动额外的服务。只需要导入相应的 Go 包（如 github.com/mattn/go-sqlite3）即可与 SQLite3 交互。同时，它也支持完整的 SQL 语法，使得它对于熟悉 SQL 的开辟职员来说非常直观。
事务支持和原子性
SQLite3 支持完整的事务机制，它会自动进行事务的回滚，确保数据的一致性。在事务期间的所有操纵都是原子的，要么全部成功，要么全部回滚，避免部门提交导致的数据不一致。
支持嵌入式环境
由于 SQLite3 是嵌入式数据库，它非常得当在嵌入式装备、移动应用、桌面应用、物联网装备等资源有限的环境中使用。

SQLite3 的常见用途

移动应用：SQLite3 是 Android 和 iOS 移动操纵系统的默认数据库。它允许开辟者在本地存储数据，无需依赖外部数据库服务。
桌面应用：许多桌面应用程序（如浏览器、邮件客户端等）都使用 SQLite3 来存储用户数据和设置文件。
嵌入式装备：SQLite3 被广泛用于嵌入式装备和物联网装备中，可以或许在有限的资源下提供数据库功能。
数据存储：它也经常作为数据存储的办理方案，例如用于存储日志、历史记载、设置文件等。
快速原型开辟：SQLite3 得当快速原型开辟和小型项目，开辟者可以快速实现数据库功能，而不必担心数据库服务器的设置和维护。

SQLite3 与其他数据库的比力
特性SQLite3MySQL/PostgreSQL安装与设置零设置，直接使用数据库文件需要安装和设置数据库服务服务器需求无需服务器，嵌入式数据库需要独立的数据库服务器性能对小型数据集性能良好大型数据集查询更高效事务支持完全支持 ACID 事务完全支持 ACID 事务扩展性得当小到中型应用，单用户查询支持高并发、多用户、大数据量使用场景本地存储、嵌入式、移动应用等企业级应用、复杂查询等总而言之，SQLite3 是一个轻量级的、无服务器的数据库系统，非常得当嵌入式应用、桌面应用和移动应用。它通过将数据库引擎嵌入到应用程序中，使得开辟者可以或许快速实现数据存储功能，且不需要额外的数据库服务器或设置。对于小型到中型的数据存储需求，SQLite3 提供了高效、可靠的办理方案，同时也支持事务、ACID 原则等关系型数据库的紧张特性。
SQLite 命令

如果你在sqlite3官网下载了一个sqlite引擎，那么就可以像python一样在cmd敲下一个sqlite3命令，然后在交互式命令行中使用sqlite3。（笔者没有下载）
在 sqlite3 中，SQL 语句需以分号 ; 末端才会实验，允许跨行输入。特殊的点命令（如 .help 和 .tables）以小数点 . 开头，不需要分号末端。

$ sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>

复制代码

sqlite3的命令以.开头，如需获取可用的点命令的清单，可以输入 “.help”。例如：

sqlite>.help

复制代码

上面的命令会显示各种紧张的 SQLite 点命令的列表，如下所示：
命令形貌.backup ?DB? FILE备份 DB 数据库（默认是 "main"）到 FILE 文件。.bail ON|OFF发生错误后停止。默认为 OFF。.databases列出数据库的名称及其所依附的文件。.dump ?TABLE?以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。.echo ON|OFF开启或关闭 echo 命令。.exit退出 SQLite 提示符。.explain ON|OFF开启或关闭得当于 EXPLAIN 的输出模式。如果没有带参数，则为 EXPLAIN on，即开启 EXPLAIN。.header(s) ON|OFF开启或关闭头部显示。.help显示消息。.import FILE TABLE导入来自 FILE 文件的数据到 TABLE 表中。.indices ?TABLE?显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。.load FILE ?ENTRY?加载一个扩展库。.log FILE|off开启或关闭日志。FILE 文件可以是 stderr（尺度错误）/stdout（尺度输出）。.mode MODE设置输出模式，MODE 可以是下列之一：

csv 逗号分隔的值
column 左对齐的列
html HTML 的 <table> 代码
insert TABLE 表的 SQL 插入（insert）语句
line 每行一个值
list 由 .separator 字符串分隔的值
tabs 由 Tab 分隔的值
tcl TCL 列表元素

.nullvalue STRING在 NULL 值的地方输出 STRING 字符串。.output FILENAME发送输出到 FILENAME 文件。.output stdout发送输出到屏幕。.print STRING...逐字地输出 STRING 字符串。.prompt MAIN CONTINUE更换尺度提示符。.quit退出 SQLite 提示符。.read FILENAME实验 FILENAME 文件中的 SQL。.schema ?TABLE?显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。.separator STRING改变输出模式和 .import 所使用的分隔符。.show显示各种设置的当前值。.stats ON|OFF开启或关闭统计。.tables ?PATTERN?列出匹配 LIKE 模式的表的名称。.timeout MS尝试打开锁定的表 MS 毫秒。.width NUM NUM为 "column" 模式设置列宽度。.timer ON|OFF 开启或关闭 CPU 定时器。使用 .show 命令，来检察 SQLite 命令提示符的默认设置。

sqlite>.show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
sqlite>

复制代码

可以使用下列的点命令来格式化输出为本教程下面所列出的格式：

sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>

复制代码

上面设置将产生如下格式的输出：

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
CPU Time: user 0.000000 sys 0.000000

复制代码

SQLite 语法

SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。
SQLite 注释是附加的注释，可以在 SQLite 代码中添加注释以增长其可读性，他们可以出现在任何空白处，包括在表达式内和其他 SQL 语句的中心，但它们不能嵌套。
SQL 注释以两个连续的 “-” 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准。
您也可以使用 C 风格的注释，以 “/" 开始，并扩展至下一个 "/” 字符对或直到输入结束，以先到者为准。SQLite的注释可以超过多行。

sqlite>.help
-- 这是一个简单的注释

复制代码

所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号 ; 结束，如：

SQLite ANALYZE 语句：
ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;
SQLite AND/OR 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQLite ALTER TABLE 语句：
ALTER TABLE table_name ADD COLUMN column_def...;
SQLite ALTER TABLE 语句（Rename）：
ALTER TABLE table_name RENAME TO new_table_name;
SQLite ATTACH DATABASE 语句：
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
SQLite BEGIN TRANSACTION 语句：
BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;
SQLite BETWEEN 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;
SQLite COMMIT 语句：
COMMIT;
SQLite CREATE INDEX 语句：
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );
SQLite CREATE UNIQUE INDEX 语句：
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);
SQLite CREATE TABLE 语句：
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);
SQLite CREATE TRIGGER 语句：
CREATE TRIGGER database_name.trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
stmt1;
stmt2;
....
END;
SQLite CREATE VIEW 语句：
CREATE VIEW database_name.view_name AS
SELECT statement....;
SQLite CREATE VIRTUAL TABLE 语句：
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
SQLite COMMIT TRANSACTION 语句：
COMMIT;
SQLite COUNT 子句：
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;
SQLite DELETE 语句：
DELETE FROM table_name
WHERE {CONDITION};
SQLite DETACH DATABASE 语句：
DETACH DATABASE 'Alias-Name';
SQLite DISTINCT 子句：
SELECT DISTINCT column1, column2....columnN
FROM table_name;
SQLite DROP INDEX 语句：
DROP INDEX database_name.index_name;
SQLite DROP TABLE 语句：
DROP TABLE database_name.table_name;
SQLite DROP VIEW 语句：
DROP VIEW view_name;
SQLite DROP TRIGGER 语句：
DROP TRIGGER trigger_name
SQLite EXISTS 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM table_name );
SQLite EXPLAIN 语句：
EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT statement...;
SQLite GLOB 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };
SQLite GROUP BY 子句：
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;
SQLite HAVING 子句：
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);
SQLite INSERT INTO 语句：
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);
SQLite IN 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);
SQLite Like 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQLite NOT IN 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);
SQLite ORDER BY 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQLite PRAGMA 语句：
PRAGMA pragma_name;
For example:
PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);
SQLite RELEASE SAVEPOINT 语句：
RELEASE savepoint_name;
SQLite REINDEX 语句：
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;
SQLite ROLLBACK 语句：
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;
SQLite SAVEPOINT 语句：
SAVEPOINT savepoint_name;
SQLite SELECT 语句：
SELECT column1, column2....columnN
FROM table_name;
SQLite UPDATE 语句：
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];
SQLite VACUUM 语句：
VACUUM;
SQLite WHERE 子句：
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;

复制代码

SQLite 数据范例

每个存储在 SQLite 数据库中的值都具有以下存储类之一：、
存储类形貌NULL值是一个 NULL 值。INTEGER值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。REAL值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。TEXT值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。BLOB值是一个 blob 数据，完全根据它的输入存储。 SQLite 的数据范例并不是严格的范例约束。每一列的数据范例更准确地说是存储类（storage class）。存储类定义了数据在 SQLite 中如何存储。
亲和范例形貌TEXT数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。NUMERIC当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操纵不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL范例的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB范例的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外阐明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。INTEGER对于亲缘范例为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在实验CAST表达式时。REAL其规则根本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。NONE不做任何的转换，直接以该数据所属的数据范例进行存储。　　 SQLite支持列的亲和范例概念。任何列仍然可以存储任何范例的数据，当数据插入时，该字段的数据将会优先采取亲缘范例作为该值的存储方式，怎么明确呢？
列亲和范例——优先选择机制

SQLite 支持的 列的亲和范例（Affinity）概念是它独特的计划之一，它使得数据库系统具有非常灵活的范例管理机制。你可以将它明确为 范例优先级规则，它规定了在数据插入时，SQLite 会根据列的亲和范例来决定如何存储数据。尽管 SQLite 中的每列可以存储任何范例的数据，但它会尽大概根据列的亲和范例来决定命据的存储方式。这使得 SQLite 的数据范例系统相对宽松，也为灵活的数据存储提供了支持。
在 SQLite 中，每个列都有一个亲和范例，这并不是逼迫要求列只能存储某种数据范例，而是提供了一种“优先选择”机制。SQLite 会尽量将数据存储为列亲和范例所指定的范例，但如果存储的范例与亲和范例不匹配，SQLite 会尝试做须要的范例转换，最终以最得当的方式存储数据。
亲和范例与数据范例的区别
SQLite 的列本身并没有严格的数据范例约束，它的“范例”更多的是与 亲和范例 相关，而不是实际存储的数据范例。亲和范例 是一种范例约束，它定义了 SQLite 如何优先处置惩罚列中的数据。

数据范例 是插入到列中的实际数据范例。
亲和范例 是该列的“范例优先级”，它影响数据存储时的转换方式。

SQLite 的数据范例系统是 动态范例 的，这意味着列的数据范例并不严格限定于某种范例，而是具有灵活性。每列的亲和范例告诉 SQLite 在数据插入时该使用什么优先范例来存储数据，但这并不意味着逼迫要求必须遵循某种数据范例。
亲和范例的优先级
SQLite 中支持的亲和范例包括：

TEXT：优先存储为文本字符串（UTF-8 或 UTF-16 编码）。
NUMERIC：优先存储为数字（整数或浮动点数），如果不能以数字格式存储，将尝试将数据转换为文本。
INTEGER：优先存储为整数。
REAL：优先存储为浮动点数。
BLOB：优先存储为二进制数据。

当你插入数据时，SQLite 会根据列的亲和范例来确定命据存储的方式：
亲和范例的转换规则
SQLite 对每一列的存储范例提供了 范例亲和规则。这些规则决定了当你插入数据时，SQLite 会如何将数据转换为与列的亲和范例相匹配的格式。详细规则如下：

TEXT 亲和范例：
- 如果插入的是一个文本值（如字符串），直接存储为 TEXT。
- 如果插入的是一个整数或浮动点数，SQLite 会将其转换为文本。
NUMERIC 亲和范例：
- 如果插入的是一个有效的整数或浮动点数，SQLite 会存储为 INTEGER 或 REAL。
- 如果插入的数据无法转换为数字（如字符串 “abc”），则 SQLite 会将其作为 TEXT 存储。
INTEGER 亲和范例：
- 如果插入的数据是整数，SQLite 会直接存储为 INTEGER。
- 如果插入的是浮动点数或文本数据，SQLite 会努力转换为整数。
REAL 亲和范例：
- 如果插入的是浮动点数，直接存储为 REAL。
- 如果插入的是整数，SQLite 会将其转换为浮动点数。
- 如果插入的是文本数据（且它是有效的浮动点数），SQLite 会将其转换为浮动点数。
BLOB 亲和范例：
- BLOB 数据不进行范例转换，它将按原始二进制数据存储。

亲和范例示例

文本插入到整数列，假设你有一个列定义如下：
1. CREATE TABLE example (id INTEGER);
复制代码
你可以插入任意数据（比如文本），SQLite 会根据 INTEGER 亲和范例来转换数据：
1. INSERT INTO example (id) VALUES ('123');
复制代码
在这个例子中，尽管插入的值是文本 '123'，SQLite 会将其转换为 INTEGER 范例，并存储为整数 123。
插入浮动点数到 TEXT 列，假设你有一个列定义如下：
1. CREATE TABLE example (name TEXT);
复制代码
你可以插入一个浮动点数，SQLite 会将其转换为 TEXT：
1. INSERT INTO example (name) VALUES (123.45);
复制代码
SQLite 会将浮动点数 123.45 存储为文本字符串 '123.45'。
插入非数字文本到 NUMERIC 列，假设你有一个列定义如下：
1. CREATE TABLE example (value NUMERIC);
复制代码
你插入一个无法转换为数字的文本：
1. INSERT INTO example (value) VALUES ('abc');
复制代码
SQLite 会将 'abc' 存储为 TEXT，因为它无法转换为数字。
插入二进制数据到 BLOB 列，如果你有一个 BLOB 列，数据会被存储为原始二进制数据：
1. CREATE TABLE example (image BLOB);
复制代码
你插入一些二进制数据：
1. INSERT INTO example (image) VALUES (X'89504E470D0A1A0A0000000D494844520000');
复制代码
这将插入原始二进制数据，而不进行任何转换。

SQLite 创建数据库

SQLite 本身不支持用户权限管理或权限控制机制，因此不需要任何特殊的权限即可创建一个数据库。

$ sqlite3 DatabaseName.db

复制代码

通常情况下，数据库名称在 RDBMS 内应该是唯一的。
别的我们也可以在sqlite交互界面使用 .open 来建立新的数据库文件：

sqlite>.open test.db

复制代码

打开已存在数据库也是用 .open 命令，以上命令如果 test.db 存在则直接会打开，不存在就创建它。
一旦数据库被创建，就可以使用 SQLite 的 .databases 命令来查抄它是否在数据库列表中，如下所示：

sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db

复制代码

可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中，如下所示：

$sqlite3 testDB.db .dump > testDB.sql

复制代码

上面的命令将转换整个 testDB.db 数据库的内容到 SQLite 的语句中，并将其转储到 ASCII 文本文件 testDB.sql 中。您可以通过简单的方式从天生的 testDB.sql 恢复，如下所示：

$sqlite3 testDB.db < testDB.sql

复制代码

SQLite 附加数据库

在 SQLite 中，附加数据库（Attach Database）允许在一个数据库连接中打开多个数据库文件。这个特性非常有效，尤其是在需要跨多个数据库进行查询的场景下。通过附加数据库，SQLite 允许你在一个查询中访问多个数据库，而不需要分别打开多个连接。
SQLite 使用 ATTACH DATABASE 命令将一个新的数据库文件附加到当前的数据库连接中。附加的数据库将拥有一个别名，后续的 SQL 查询可以通过该别名来引用附加的数据库。

ATTACH DATABASE file_name AS database_name;

复制代码

如果想附加一个现有的数据库 testDB.db，则 ATTACH DATABASE 语句将如下所示：

sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';

复制代码

使用 SQLite .database 命令来显示附加的数据库。

sqlite> .database
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db

复制代码

数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息，如下所示：

sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database main is already in use；

复制代码

然后，你可以在查询时通过数据库别名来引用附加的数据库：

-- 从主数据库和附加数据库中查询数据
SELECT * FROM main.users;
SELECT * FROM TEST.orders;

复制代码

也可以进行跨表查询：

-- 打开两个数据库并附加
ATTACH DATABASE 'main.db' AS main;
ATTACH DATABASE 'secondary.db' AS secondary;
-- 从不同的数据库查询数据
SELECT users.name, orders.order_date
FROM main.users
JOIN secondary.orders ON users.user_id = orders.user_id;

复制代码

SQLite 分离数据库

SQLite 的 DETACH DATABASE 语句是用来把定名数据库从一个数据库连接分离和游离出来，连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名，DETACH 命令将只断开给定名称的连接，而其余的仍然有效。您无法分离 main 或 temp 数据库。

DETACH DATABASE 'Alias-Name';

复制代码

假设在前面的章节中您已经创建了一个数据库，并给它附加了 ‘test’ 和 ‘currentDB’，使用 .database 命令，我们可以看到：

sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db3 currentDB /home/sqlite/testDB.db

复制代码

现在，让我们尝试把 ‘currentDB’ 从 testDB.db 中分离出来，如下所示：

sqlite> DETACH DATABASE 'currentDB';

复制代码

现在，如果查抄当前附加的数据库，您会发现，testDB.db 仍与 ‘test’ 和 ‘main’ 保持连接。

sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db

复制代码

SQLite 创建表

SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建根本表，涉及到定名表、定义列及每一列的数据范例。

CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);

复制代码

CREATE TABLE 是告诉数据库系统创建一个新表的关键字。CREATE TABLE 语句后跟着表的唯一的名称或标识。您也可以选择指定带有 table_name 的 database_name。
而对于约束项，sqlite和mysql都差不多，可以参考mysql的建表语句。
下面是一个实例，它创建了一个 COMPANY 表，ID 作为主键，NOT NULL 的约束表示在表中创建纪录时这些字段不能为 NULL：

sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

复制代码

可以使用 SQLIte 命令中的 .tables 命令来验证表是否已成功创建，该命令用于列出附加数据库中的所有表。

sqlite>.tables
COMPANY DEPARTMENT

复制代码

可以使用 SQLite .schema 命令得到表的完整信息，如下所示：

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

复制代码

SQLite 删除表

SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。

DROP TABLE database_name.table_name;

复制代码

SQLite Insert 语句

INSERT INTO 语句有两种根本语法，如下所示：

# 在这里，column1, column2,...columnN 是要插入数据的表中的列的名称。
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
# 如果要为表中的所有列添加值，您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下：
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

复制代码

假设已经在 testDB.db 中创建了 COMPANY表，如下所示：

sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

复制代码

现在，下面的语句将在 COMPANY 表中创建六个记载：

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

复制代码

也可以使用第二种语法在 COMPANY 表中创建一个记载，如下所示：

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

复制代码

您可以通过在一个有一组字段的表上使用 select 语句，填凑数据到另一个表中。下面是语法：

INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

复制代码

SQLite Select 语句

SQLite 的 SELECT 语句的根本语法如下：

SELECT column1, column2, columnN FROM table_name;

复制代码

因为所有的点命令只在 SQLite 提示符中可用，以是当您进行带有 SQLite 的编程时，您要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表：

sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';

复制代码

可以列出关于 COMPANY 表的完整信息，如下所示：

sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';

复制代码

CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)

复制代码

SQLite 运算符

运算符是一个保留字或字符，重要用于 SQLite 语句的 WHERE 子句中实验操纵，如比力和算术运算。
SQLite 算术运算符

假设变量 a=10，变量 b=20，则：
运算符形貌实例+加法 - 把运算符双方的值相加 a + b 将得到 30-减法 - 左操纵数减去右操纵数 a - b 将得到 -10*乘法 - 把运算符双方的值相乘 a * b 将得到 200/除法 - 左操纵数除以右操纵数 b / a 将得到 2%取模 - 左操纵数除以右操纵数后得到的余数 b % a 将得到 0 下面是 SQLite 算术运算符的简单实例：

sqlite> .mode line
sqlite> select 10 + 20;
10 + 20 = 30
sqlite> select 10 - 20;
10 - 20 = -10
sqlite> select 10 * 20;
10 * 20 = 200
sqlite> select 10 / 5;
10 / 5 = 2
sqlite> select 12 % 5;
12 % 5 = 2

复制代码

SQLite 比力运算符

假设变量 a=10，变量 b=20，则：
运算符形貌实例==查抄两个操纵数的值是否相等，如果相等则条件为真。 (a == b) 不为真。=查抄两个操纵数的值是否相等，如果相等则条件为真。 (a = b) 不为真。!=查抄两个操纵数的值是否相等，如果不相等则条件为真。 (a != b) 为真。<>查抄两个操纵数的值是否相等，如果不相等则条件为真。 (a <> b) 为真。>查抄左操纵数的值是否大于右操纵数的值，如果是则条件为真。 (a > b) 不为真。<查抄左操纵数的值是否小于右操纵数的值，如果是则条件为真。 (a < b) 为真。>=查抄左操纵数的值是否大于等于右操纵数的值，如果是则条件为真。 (a >= b) 不为真。<=查抄左操纵数的值是否小于等于右操纵数的值，如果是则条件为真。 (a <= b) 为真。!<查抄左操纵数的值是否不小于右操纵数的值，如果是则条件为真。 (a !< b) 为假。!>查抄左操纵数的值是否不大于右操纵数的值，如果是则条件为真。 (a !> b) 为真。 SQLite 逻辑运算符

下面是 SQLite 中所有的逻辑运算符列表。
运算符形貌ANDAND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。BETWEENBETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜刮值。EXISTSEXISTS 运算符用于在满意一定条件的指定表中搜刮行的存在。ININ 运算符用于把某个值与一系列指定列表的值进行比力。NOT ININ 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比力。LIKELIKE 运算符用于把某个值与使用通配符运算符的相似值进行比力。GLOBGLOB 运算符用于把某个值与使用通配符运算符的相似值进行比力。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。NOTNOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。OROR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。IS NULLNULL 运算符用于把某个值与 NULL 值进行比力。ISIS 运算符与 = 相似。IS NOTIS NOT 运算符与 != 相似。||连接两个不同的字符串，得到一个新的字符串。UNIQUEUNIQUE 运算符搜刮指定表中的每一行，确保唯一性（无重复）。假设 COMPANY 表有以下记载：

ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

复制代码

下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记载：

sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0

复制代码

下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记载，结果显示所有的记载，意味着没有一个记载的 AGE 等于 NULL：

sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

复制代码

下面的 SELECT 语句列出了 NAME 以 ‘Ki’ 开始的所有记载，‘Ki’ 之后的字符不做限定：

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0

复制代码

下面的 SELECT 语句列出了 NAME 以 ‘Ki’ 开始的所有记载，‘Ki’ 之后的字符不做限定：

sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0

复制代码

下面的 SELECT 语句列出了 AGE 的值为 25 或 27 的所有记载：

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0

复制代码

面的 SELECT 语句列出了 AGE 的值既不是 25 也不是 27 的所有记载：

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

复制代码

下面的 SELECT 语句列出了 AGE 的值在 25 与 27 之间的所有记载：

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0

复制代码

下面的 SELECT 语句使用 SQL 子查询，子查询查找 SALARY > 65000 的带有 AGE 字段的所有记载，后边的 WHERE 子句与 EXISTS 运算符一起使用，列出了外查询中的 AGE 存在于子查询返回的结果中的所有记载：

sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24

复制代码

SQLite 位运算符

位运算符作用于位，并逐位实验操纵。真值表 & 和 | 如下：
pqp & qp | q0000010111111001 假设如果 A = 60，且 B = 13，现在以二进制格式，它们如下所示：

A = 0011 1100
B = 0000 1101
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011

复制代码

下表中列出了 SQLite 语言支持的位运算符。假设变量 A=60，变量 B=13，则：
运算符形貌实例&如果同时存在于两个操纵数中，二进制 AND 运算符复制一位到结果中。 (A & B) 将得到 12，即为 0000 1100|如果存在于任一操纵数中，二进制 OR 运算符复制一位到结果中。 (A | B) 将得到 61，即为 0011 1101~二进制补码运算符是一元运算符，具有"翻转"位效应，即0酿成1，1酿成0。 (~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码情势。<<二进制左移运算符。左操纵数的值向左移动右操纵数指定的位数。 A << 2 将得到 240，即为 1111 0000>> 二进制右移运算符。左操纵数的值向右移动右操纵数指定的位数。 A >> 2 将得到 15，即为 0000 1111 下面的实例演示了 SQLite 位运算符的用法：

sqlite> .mode line
sqlite> select 60 | 13;
60 | 13 = 61
sqlite> select 60 & 13;
60 & 13 = 12
sqlite> select (~60);
(~60) = -61
sqlite> select (60 << 2);
(60 << 2) = 240
sqlite> select (60 >> 2);
(60 >> 2) = 15

复制代码

SQLite 表达式

…更多内容可以参考官方文档或者菜鸟教程：SQLite 表达式
golang操纵sqlite

golang操纵sqlite有两个比力知名的库：modernc.org/sqlite 和 github.com/mattn/go-sqlite3
modernc.org/sqlite 和 github.com/mattn/go-sqlite3 都是 Go 语言中的 SQLite 库，用于与 SQLite 数据库交互。这两个库尤其各自的特性：

依赖性和编译
- modernc.org/sqlite:
  - 这是一个纯 Go 实现的 SQLite 库，意味着它不依赖于 C 编译器或者 SQLite 的 C 库。
  - 可以轻松地在没有 C 编译器的环境中使用，得当跨平台开辟。
  - 它还支持 SQLite 的大部门功能。
  - 没有 C 语言依赖，避免了 C/C++ 编译的复杂性。
- github.com/mattn/go-sqlite3:
  - 这是一个基于 C 的 SQLite 实现，需要在编译时链接 SQLite 的 C 库。
  - 它会依赖 C 编译器和 SQLite 的 C 库，因此在一些没有 C 编译器的环境下使用起来大概较为复杂。
  - 需要在目标环境中安装 SQLite C 库，大概会碰到与平台或环境相关的题目（例如，在某些系统中大概需要特定版本的 SQLite）。
性能
- modernc.org/sqlite:
  - 由于它是纯 Go 实现，大概会在性能上稍逊色于基于 C 的实现，尤其是在需要处置惩罚大量数据或高并发时。
  - 但是它的性能仍然可以满意大多数应用的需求，尤其是对于小型和中型应用。
- github.com/mattn/go-sqlite3:
  - 基于 C 实现的 SQLite 库，通常会比纯 Go 实现更快，特别是在大规模数据处置惩罚和复杂查询时。
  - 由于使用了原生 C 库，它通常可以或许提供更高的性能，尤其是当涉及到复杂的 SQL 操纵时。
功能支持
- modernc.org/sqlite:
  - 支持大多数 SQLite 的功能，包括事务、索引、查询等。
  - 支持SQLite3的大部门尺度特性。
  - 但大概在某些细节实现上与 SQLite 原生版本有些差距。
- github.com/mattn/go-sqlite3:
  - 完全实现 SQLite 的功能，而且通常会在新版本的 SQLite 发布后迅速进行更新。
  - 因为它是基于 SQLite C 库，以是对于 SQLite 原生功能的支持更完整，更新也更及时。
社区和维护
- modernc.org/sqlite:
  - 作为一个较新的库，它的社区支持和文档大概不如 go-sqlite3 庞大。
  - 由于它是纯 Go 实现，它在一些特殊场景下大概会出现一些性能瓶颈，但这些题目通常会在未来的版本中渐渐得到办理。
- github.com/mattn/go-sqlite3:
  - 这是 Go 中最常用的 SQLite 库之一，社区活泼，维护频仍。
  - 因为它依赖于 SQLite 的 C 库，它的功能和性能通常比其他 Go SQLite 库更为可靠。
跨平台支持
- modernc.org/sqlite:
  - 由于是纯 Go 实现，modernc.org/sqlite 具有更好的跨平台支持，可以在没有 C 编译器的环境中运行，得当容器化和某些轻量级环境。
- github.com/mattn/go-sqlite3:
  - 需要 C 编译器，因此在某些系统或平台上（特别是轻量级系统或容器环境）大概会碰到安装和设置的题目。

选择哪个库取决于你的项目需求、环境限定以及对性能和功能的详细要求：

modernc.org/sqlite 更得当需要完全 Go 实现、避免 C 依赖的项目，尤其是在跨平台开辟和不依赖 C 编译器的情况下。
github.com/mattn/go-sqlite3 更得当需要 SQLite 原生支持、寻求高性能而且没有 C 编译器限定的项目。

golang操纵sqlite3加速本地结构化数据查询

回归正题，假设现在有一个CSV文件，现在为了测试其查询性能我们准备两个大小不一的CSV，而且实现开头提到的三种查询方式。

在此之前，我们先用golang实现一个测时的装饰器：

package main
import (
"fmt"
"time"
)
func WithTimer(f func()) func() {
return func() {
start := time.Now()
// do something
f()
// 打印输出耗时
fmt.Printf("%.2fs elapsed\n", time.Since(start).Seconds())
}
}
func A() {
time.Sleep(time.Second * 3)
}
func main() {
WithTimer(A)()
}
// 程序输出
// 3.00s elapsed

复制代码

然后我们新建一个文件夹准备实现Reader接口，

需要用到的三方库如下：

github.com/mattn/go-sqlite3：Cgo实现的sqlite3 driver，更快！
github.com/jmoiron/sqlx：强化版的database/sql库，封装了一些常用方法！

为了功能的实现，代码坚固性暂时不在考虑范围内，因此笔者会忽略掉大部门error处置惩罚。
SimpleReader

简单的遍历查询：

// simpleReader实现的是普通遍历的方法
type SimpleReader struct {
fd *os.File
rd *csv.Reader
head []string
}
func NewSimpleReader(filename string) *SimpleReader {
fp, _ := os.Open(filename)
reader := csv.NewReader(fp)
head, _ := reader.Read()
return &SimpleReader{fd: fp, rd: reader, head: head}
}
func (r *SimpleReader) Search(field string, value string) []string {
fieldIndex := slices.Index(r.head, field)
for {
record, err := r.rd.Read()
if err == io.EOF {
break
}
if err != nil {
log.Fatal(err)
}
if record[fieldIndex] == value {
return record
}
}
return nil
}
func (r *SimpleReader) Close() {
_ = r.fd.Close()
}

复制代码

我们来测试一下它的1000次查询耗时：

const (
smallField = "field9"
smallValue = "value6_8"
bigField = "field9"
bigValue = "value99855_8"
searchCount = 1000
)
func main() {
WithTimer(func() {
for i := 0; i < searchCount; i++ {
rd := reader.NewSimpleReader("small.csv")
_ = rd.Search(smallField, smallValue)
rd.Close()
}
})()
WithTimer(func() {
for i := 0; i < searchCount; i++ {
rd2 := reader.NewSimpleReader("big.csv")
_ = rd2.Search(bigField, bigValue)
rd2.Close()
}
})()
}
0.03s elapsed
33.64s elapsed

复制代码

因为simpleReader没有持久化数据，以是每次查询都得将数据重新加载后遍历，对于大文件来说查询速率显著降落。
MapReader

相较于SimpleReader，MapReader增长了一个内存缓存，

type MapReader struct {
head []string
cache map[int][]string
}
func NewMapReader(filename string) *MapReader {
fp, _ := os.Open(filename)
defer fp.Close()
reader := csv.NewReader(fp)
head, _ := reader.Read()
cache := make(map[int][]string)
index := 0
for {
record, err := reader.Read()
if err == io.EOF {
break
}
if err != nil {
log.Fatal(err)
}
cache[index] = record
index++
}
return &MapReader{head: head, cache: cache}
}
func (reader *MapReader) Search(field string, value string) []string {
index := slices.Index(reader.head, field)
for _, line := range reader.cache {
if line[index] == value {
return line
}
}
return nil
}
func (reader *MapReader) Close() {
reader.head = nil
reader.cache = nil
}

复制代码

我们来测试一下它的1000次查询耗时：

const (
smallField = "field9"
smallValue = "value6_8"
bigField = "field9"
bigValue = "value99855_8"
searchCount = 1000
)
func main() {
WithTimer(func() {
rd := reader.NewMapReader("small.csv")
for i := 0; i < searchCount; i++ {
rd.Search(smallField, smallValue)
}
rd.Close()
})()
WithTimer(func() {
rd2 := reader.NewMapReader("big.csv")
for i := 0; i < searchCount; i++ {
_ = rd2.Search(bigField, bigValue)
}
rd2.Close()
})()
}
0.05s elapsed
3.04s elapsed

复制代码

可以看到，在大文件的查询上速率显著变快，这归功于运行运行内存的高速运转（和redis一样），在内存中遍历自然比不断从硬盘中取出数据再遍历快的多，缺陷就是占用大量内存，在小内存的机器上大概触发系统级别的“内存逐出”。
sqlReader

sqlReader则将数据存储到sqlite中：

type SqliteReader struct {
rd *csv.Reader
sqliteHandler *sqlx.DB // Sql操作抓手
lineLength int // 行长度
head []string // 头信息
}
const (
sqlitePath = "csv.db"
sqliteTableName = "test"
)
func NewSqliteReader(filename string) *SqliteReader {
// 读权限打开csv
fp, _ := os.Open(filename)
defer fp.Close()
reader := csv.NewReader(fp)
// 增加csv reader容错性
reader.LazyQuotes = true
reader.TrimLeadingSpace = true
// 读取头信息
head, _ := reader.Read()
// 打开sqlite
db, _ := sqlx.Open("sqlite3", sqlitePath)
rd := &SqliteReader{
rd: reader,
head: head,
sqliteHandler: db,
lineLength: len(head),
}
if err := rd.initDB(); err != nil {
log.Fatal(err)
}
fmt.Println("DB构建结束")
return rd
}
func (reader *SqliteReader) initDB() error {
// 拼接建库语句
fieldDefine := make([]string, reader.lineLength)
for index, field := range reader.head {
fieldDefine[index] = fmt.Sprintf("%s TEXT", field)
}
fieldDefinedString := strings.Join(fieldDefine, ",")
// 建立数据库
var createTableSQL = fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s (
id INTEGER PRIMARY KEY AUTOINCREMENT,
%s
);`, sqliteTableName, fieldDefinedString)
fmt.Println(createTableSQL)
_, err := reader.sqliteHandler.Exec(createTableSQL)
if err != nil {
return err
}
// 拼接插入语句
insertSQL := fmt.Sprintf("INSERT INTO %s VALUES (NULL,", sqliteTableName)
for i := 0; i < reader.lineLength; i++ {
insertSQL += "?,"
}
insertSQL = strings.TrimSuffix(insertSQL, ",") + ");"
// 预编译提速
stmt, _ := reader.sqliteHandler.Prepare(insertSQL)
// 使用事务插入数据
tx, _ := reader.sqliteHandler.Begin()
// 写入数据
for {
line, err := reader.rd.Read()
if err == io.EOF {
break
}
if err != nil {
return err
}
// 转为接口类型
values := make([]interface{}, reader.lineLength)
for i, v := range line {
values[i] = v
}
// 插入sql
_, err = stmt.Exec(values...)
if err != nil {
return err
}
}
_ = tx.Commit()
return nil
}
func (reader *SqliteReader) Search(field string, value string) []any {
query := fmt.Sprintf(`SELECT * FROM %s WHERE %s==? LIMIT 1`, sqliteTableName, field)
// 查询
row, _ := reader.sqliteHandler.Queryx(query, value)
defer row.Close()
// 读取结果
for row.Next() {
r, _ := row.SliceScan()
return r
}
return nil
}
func (reader *SqliteReader) Close() {
_ = reader.sqliteHandler.Close()
}

复制代码

我们来测试一下它的1000次查询耗时：

func main() {
//WithTimer(func() {
// rd := reader.NewSqliteReader("small.csv")
// for i := 0; i < searchCount; i++ {
// r := rd.Search(smallField, smallValue)
// fmt.Println(r)
// }
// rd.Close()
//})()
WithTimer(func() {
rd2 := reader.NewSqliteReader("big.csv")
for i := 0; i < searchCount; i++ {
_ = rd2.Search(bigField, bigValue)
}
rd2.Close()
})()
}
0.14s elapsed
143.41s elapsed

复制代码

总结

从上文测试结果来看，sqlite优化后的查询效率甚至都不如遍历查询，而缓存查询的效果虽然最好，但最费内存，尤其是在某些场景下大概机器只有区区1-2G，根本跑不起来。
我们对sqlite进行优化：

批量插入：在大量数据写入的情况下，通过事务和预编译语句批量插入数据，避免频仍的磁盘 I/O。
1. tx, err := db.Begin()
2. stmt, err := tx.Prepare("INSERT INTO ...")
3. for _, row := range data {
4. _, err := stmt.Exec(row[0], row[1])
5. if err != nil {
6. log.Fatalf("Failed to execute statement: %v", err)
7. }
8. }
9. err = tx.Commit()
复制代码
设置性能优化：在 Go 程序中通过 PRAGMA 设置 SQLite 参数以提升性能。
1. _, err = db.Exec(`
2. PRAGMA synchronous = OFF; -- 关闭同步机制
3. PRAGMA journal_mode = MEMORY; -- 将日志存储在内存中
4. PRAGMA temp_store = MEMORY; -- 临时表存储在内存中
5. PRAGMA cache_size = 100000; -- 设置较大的缓存
6. PRAGMA locking_mode = EXCLUSIVE;-- 独占锁模式
7. `)
8. if err != nil {
9. log.Fatalf("Failed to set PRAGMA options: %v", err)
10. }
复制代码
参数解析：
- PRAGMA synchronous = OFF
  - 作用：控制 SQLite 如何在写入数据时与磁盘同步，影响事务的持久性和性能。
  - 解释：
    - OFF：关闭同步机制。写入操纵会更快，但在断电或系统瓦解时大概会丢失一些数据，因为数据尚未完全写入磁盘。
    - NORMAL：这是默认值，SQLite 会确保数据在写入前与磁盘同步，较安全，但稍慢。
    - FULL：最安全的模式，确保所有写入操纵都被完全同步到磁盘，性能最差。
  - 实用场景：如果对数据的可靠性要求不高，可以选择 OFF，以换取写入速率的提升。
- PRAGMA journal_mode = MEMORY
  - 作用：设置事务日志的存储方式，影响数据库的事务处置惩罚效率和恢复能力。
  - 解释：
    - MEMORY：将事务日志存储在内存中，避免磁盘 I/O 操纵，通常在内存数据库或短时间操纵时使用。
    - WAL（Write-Ahead Logging）：将日志存储在磁盘文件中，得当多用户并发读写时使用。
    - DELETE：默认模式，事务日志存储在磁盘文件中，每次写入操纵后会删除日志文件。
    - TRUNCATE：雷同于 DELETE，但不会删除整个日志文件，而是将文件截断。
  - 实用场景：如果数据持久性不紧张（如仅进行短期数据操纵），可以选择 MEMORY 来进步性能。留意，这种模式不得当数据库重启后恢复数据。
- PRAGMA temp_store = MEMORY
  - 作用：设置 SQLite 临时表的存储位置，影响临时表的存储方式。
  - 解释：
    - MEMORY：临时表存储在内存中，性能较快，因为避免了磁盘 I/O 操纵。
    - FILE：临时表存储在磁盘中，实用于大规模的数据操纵或内存不足的情况。
  - 实用场景：如果数据库内存足够，而且希望加速临时数据处置惩罚，可以选择 MEMORY。但对于大数据量的临时表，大概需要使用 FILE。
- PRAGMA cache_size = 100000
  - 作用：控制 SQLite 数据库的页面缓存大小，影响数据库的 I/O 性能。
  - 解释：
    - 该参数控制 SQLite 在内存中使用的页面数量。SQLite 在查询时会缓存数据页，cache_size 越大，数据库可以在内存中保持更多数据，减少磁盘 I/O。
    - 默认情况下，SQLite 使用的缓存大小为 2000 页，每页的大小通常为 1024 字节。设置 cache_size 为 100000 时，数据库可以缓存更多的数据，实用于内存富足的情况。
  - 实用场景：如果你有足够的内存，增长缓存大小可以提升查询性能。对于大数据量操纵，可以进步缓存命中率，减少磁盘读取次数。
- PRAGMA locking_mode = EXCLUSIVE
  - 作用：设置数据库的锁定模式，影响并发访问和事务的锁定方式。
  - 解释：
    - EXCLUSIVE：独占锁模式，表示一个事务在实验期间，其他事务无法对数据库进行写操纵。实用于写操纵密集的场景，可以或许减少冲突，但会降低并发性。
    - NORMAL：默认锁模式，允许多个事务同时读取，但只允许一个事务写入。
    - SHARED：允许多个进程或线程共享读取操纵，但只允许一个进程或线程实验写操纵。
  - 实用场景：如果你希望一个事务可以或许独占对数据库的写入操纵，避免其他写操纵干扰，可以使用 EXCLUSIVE。但如果系统有多个并发写操纵，大概会限定并发性能。
创建索引：在大量查询时，为频仍使用的查询条件列创建索引。创建索引后，sqlite Reader的瓶颈仅仅就在构建数据库上了，查询效率得到了大大进步。
1. _, err = db.Exec("CREATE INDEX IF NOT EXISTS idx_col1 ON my_table (col1)")
2. if err != nil {
3. log.Fatalf("Failed to create index: %v", err)
4. }
复制代码
使用内存模式：如果数据量适中且无需恒久存储，可以将 SQLite 数据库存储在内存中，以避免磁盘 I/O。（这个方法不考虑）
1. db, err := sql.Open("sqlite3", "file::memory:?cache=shared")
2. if err != nil {
3. log.Fatalf("Failed to open memory database: %v", err)
4. }
5. defer db.Close()
复制代码

最后优化如下：

手动操纵事务，防止每次都提交造成插入缓慢
使用sqlx提供的In方法批量插入数据

func (reader *SqliteReader) initDB(handler *csv.Reader) (err error) {
// 拼接建库语句
fieldDefine := make([]string, reader.lineLength)
for index, field := range reader.head {
fieldDefine[index] = fmt.Sprintf("%s TEXT", field)
}
fieldDefinedString := strings.Join(fieldDefine, ",")
// 建立数据库
var createTableSQL = fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s (
id INTEGER PRIMARY KEY AUTOINCREMENT,
%s
);`, sqliteTableName, fieldDefinedString)
csvLogger.Infof("create database [%s] : %s.\n", sqliteTableName, createTableSQL)
_, err = reader.sqliteHandler.Exec(createTableSQL)
if err != nil {
return err
}
// 开启事务
tx, err := reader.sqliteHandler.Begin()
if err != nil {
return err
}
// 此处不能用内部声明的err,因此defer关键词会把此时的快照入栈
// 此时err必然不为空,因此需要声明返回值的err
// golang执行顺序
// 1. returned_value = return 此处是err
// 2. defer出栈
// 3. 返回returned_value 此处是err
defer func() {
if err != nil {
_ = tx.Rollback()
} else {
_ = tx.Commit()
}
}()
// 拼接插入语句
bulkSize, currentSize := 1000, 0
values := make([]any, 0, bulkSize)
for {
line, err := handler.Read()
if err == io.EOF {
break
}
if err != nil {
return err
}
// 追加批量插入的值
values = append(values, line)
// 计数自增
currentSize++
// 判断是否需要批量新增
if currentSize == bulkSize {
condition, args, err := sqlx.In(
fmt.Sprintf(
"INSERT INTO %s (%s) VALUES %s",
sqliteTableName,
strings.Join(reader.head, ","),
strings.Repeat("(?),", currentSize),
),
values...,
)
if err != nil {
csvLogger.Errorln(err)
return err
}
// 构建正确sql语句
condition = strings.TrimSuffix(condition, ",") + ";"
if _, err = reader.sqliteHandler.Exec(condition, args...); err != nil {
return err
}
// 归零计数器
currentSize = 0
// 清空value
values = make([]any, 0, bulkSize)
}
}
// 判断漏网之鱼
if currentSize > 0 {
condition, args, err := sqlx.In(
fmt.Sprintf(
"INSERT INTO %s (%s) VALUES %s",
sqliteTableName,
strings.Join(reader.head, ","),
strings.Repeat("(?),", currentSize),
),
values...,
)
if err != nil {
csvLogger.Errorln(err)
return err
}
// 构建正确sql语句
condition = strings.TrimSuffix(condition, ",") + ";"
if _, err = reader.sqliteHandler.Exec(condition, args...); err != nil {
return err
}
// 清空value
values = nil
}
return nil
}

复制代码

然后根据我的场景增大查询次数到10W（我数据量准备了10W左右的，我的场景是数量等于查询次数）：

CREATE TABLE IF NOT EXISTS test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
field1 TEXT,field2 TEXT,field3 TEXT,field4 TEXT,field5 TEXT,field6 TEXT,field7 TEXT,field8 TEXT,field9 TEXT,field10 TEXT
);
DB构建结束
2.28s elapsed

复制代码

可以看到查询效果非常不错，提速了百倍有余，而遍历查询此时因为太久不出结果被我Ctrl+C掉了。。。
盘算一下二者查询速率的决定因素，假设查询次数为n，单次查询时间为t：

遍历查询= n * t(遍历)
sqlite查询= 建库建表t0 + 批量插入数据t1 + 创建索引t2 + n * t(sqlite)
t(sqlite) << t(遍历）

轶闻趣事：SQLite 背后的故事

SQLite 现在已经是全球用户最多的数据库产品。它非常小巧以及单文件无单独操纵系统进程，就像病毒一样依附在宿主程序的进程里运行。你看不到它，但它却无处不在。汽车，手机，浏览器，以及各类 app 里都能见到 .db 末端的 SQLite 数据库文件。如果 SQLite 出现庞大 bug，或者像平常的数据库那样无法连接，整个地球都会乱套。你身边用的险些所有电子产品（手机，电脑，iPad，笔记本）和嵌入式装备全部都会出题目。它的诞生到大范围全球流行的过程和一般软件有着不太一样的发展进程。
SQLite 诞生的契机就是典型的程序员开辟的故事剧本。作者 Richard 最开始在一艘军舰上做 contractor（就是我们说的外包）。他们程序跑在军舰安装的电脑上，电脑上装的是 informix。Richard 的工作就是把 informix 的数据拿出来进行盘算然后展示到电脑屏幕上（这和我们本日的 CRUD 工作雷同）。比力令人恼火的是 informix 很不稳定，经常瓦解连不上。部队里的铁拳长官可不懂啥 TCP/IP 或者数据库系统知识。他们只看到软件的报错 dialog（对话框）经常弹出来，而这个 dialog 又是 SQLite 的作者 Richard 写的软件画出来的，锅自然从天而降。于是 Richard 决定本身重新写一个无需外部连接的数据库来办理这个题目。
…文章泉源：https://liyafu.com/2022-07-31-sqlite-untold-story/

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

golang操纵sqlite3加速本地结构化数据查询

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云

浏览过的版块