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

摘要

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

1. 传统的遍历方法：

   • 使用 Go 的 csv 库，针对每次查询都遍历整个 CSV 文件。这种方法简单易懂，但性能较差，尤其在数据量较大时，每次查询都需要重新读取整个文件，效率低下。

2. 将数据加载到内存（Map 存储）：

   • 通过一次性读取 CSV 文件并将其存储在内存中的 map 结构中，可以大幅提高查询性能。利用 map 提供的 O(1) 查找时间，查询操作会变得非常迅速。这种方法适用于数据集较小到中等的情况，且查询操作较为简单。需要注意的是，数据量过大会导致内存消耗过高。

3. 使用 SQLite 存储数据：

   • 如果需要更高效的查询，特别是支持更复杂筛选的场景，可以考虑将 CSV 文件的数据导入到 SQLite 中。SQLite 提供了轻量级的 SQL 查询功能，可以通过创建表和索引来加速数据检索，同时支持复杂的查询操作（如筛选、排序、聚合等）。SQLite 不需要单独安装任何服务，直接通过 Go 包 github.com/mattn/go-sqlite3 即可进行操作，适合在 Golang 环境中使用。

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

Sqlite3

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

SQLite3 的基本特点

1. 嵌入式数据库
   SQLite3 是一个 嵌入式数据库，意味着它是一个库（.dll 或 .so 文件）而非独立的服务器程序。数据库的所有数据都存储在单一的文件中，应用程序通过链接该库来操作数据库。这使得 SQLite3 在轻量级应用和小型项目中非常流行。

2. 跨平台支持
   SQLite3 支持几乎所有的操作系统，包括但不限于 Windows、Linux、macOS、iOS、Android 等。由于它是 C 语言编写的，可以轻松地跨平台移植。

3. 无服务器架构
   SQLite3 是 无服务器的，这意味着没有独立的数据库进程或服务。应用程序直接访问数据库文件，且访问数据库不依赖网络连接。它通过文件系统进行 I/O 操作，查询和写入数据速度非常快。

4. 事务支持
   SQLite3 完全支持 ACID（原子性、一致性、隔离性、持久性）事务，这意味着它可以确保数据的完整性。在发生崩溃时，SQLite3 会自动回滚到事务开始之前的状态，确保数据的可靠性。

5. 轻量级
   SQLite3 的二进制文件非常小，通常只有几百 KB（具体大小取决于编译选项）。它不需要配置或安装复杂的数据库服务器，适合于嵌入式应用、小型应用、单用户应用和其他资源有限的系统。

6. 零配置
   SQLite3 无需安装或配置数据库引擎。只需要包含数据库文件和链接库即可，完全适合于需要零配置的场景。所有数据库设置都是在数据库文件级别进行的。

7. 自包含
   SQLite3 是 自包含的，它包含了所有的功能。SQLite 的操作和配置不依赖于外部软件或库。SQLite3 将数据库及其管理功能直接集成到应用程序中，进一步简化了使用过程。

SQLite3 的工作原理

SQLite3 将数据存储在单一的文件中，这个文件中不仅存储了数据，还包括了数据库的元数据（如表结构、索引等）。这种设计使得 SQLite3 对于需要将数据库存储在单一文件中的场景非常合适。

SQLite3 的数据库文件通常具有 .sqlite 或 .db 扩展名，且它是 按页存储数据的。SQLite3 使用 1024 字节（默认）为基本存储单元，每个页面可以存储一个数据记录、索引节点、事务日志等。

当应用程序需要执行 SQL 查询时，它通过 SQLite3 的 API 来与数据库文件进行交互。SQLite3 会执行 SQL 语句，操作数据库文件，然后返回结果。

SQLite3 的优势

1. 性能优越
   SQLite3 在单机应用中表现出色。由于 SQLite3 是直接对磁盘文件进行操作，并且没有复杂的数据库管理层，因此它的 I/O 性能相对较高，尤其在查询操作时速度较快。对于中小型数据集，SQLite3 几乎能够提供与传统数据库相媲美的性能。

2. 高效的内存使用
   SQLite3 将数据直接存储到文件中，避免了传统数据库系统的内存消耗。在单个数据库连接上，SQLite3 能高效地使用内存，并且能在内存不足时通过分页操作优化性能。

3. 简单易用
   SQLite3 的使用极为简单，不需要任何配置或启动额外的服务。只需要导入相应的 Go 包（如 github.com/mattn/go-sqlite3）即可与 SQLite3 交互。同时，它也支持完整的 SQL 语法，使得它对于熟悉 SQL 的开发人员来说非常直观。

4. 事务支持和原子性
   SQLite3 支持完整的事务机制，它会自动进行事务的回滚，确保数据的一致性。在事务期间的所有操作都是原子的，要么全部成功，要么全部回滚，避免部分提交导致的数据不一致。

5. 支持嵌入式环境
   由于 SQLite3 是嵌入式数据库，它非常适合在嵌入式设备、移动应用、桌面应用、物联网设备等资源有限的环境中使用。

SQLite3 的常见用途

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

SQLite3 与其他数据库的比较

特性	SQLite3	MySQL/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>.showecho: offexplain: offheaders: offmode: 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_nameSQLite 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 亲和类型来转换数据：

   INSERT INTO example (id) VALUES ('123');
   

   在这个例子中，尽管插入的值是文本 '123'，SQLite 会将其转换为 INTEGER 类型，并存储为整数 123。

2. 插入浮动点数到 TEXT 列，假设你有一个列定义如下：

   CREATE TABLE example (name TEXT);
   

   你可以插入一个浮动点数，SQLite 会将其转换为 TEXT：

   INSERT INTO example (name) VALUES (123.45);
   

   SQLite 会将浮动点数 123.45 存储为文本字符串 '123.45'。

3. 插入非数字文本到 NUMERIC 列，假设你有一个列定义如下：

   CREATE TABLE example (value NUMERIC);
   

   你插入一个无法转换为数字的文本：

   INSERT INTO example (value) VALUES ('abc');
   

   SQLite 会将 'abc' 存储为 TEXT，因为它无法转换为数字。

4. 插入二进制数据到 BLOB 列，如果你有一个 BLOB 列，数据会被存储为原始二进制数据：

   CREATE TABLE example (image BLOB);
   

   你插入一些二进制数据：

   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.db
3    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 = 30sqlite> select 10 - 20;
10 - 20 = -10sqlite> select 10 * 20;
10 * 20 = 200sqlite> select 10 / 5;
10 / 5 = 2sqlite> 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 中所有的逻辑运算符列表。

运算符	描述
AND	AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN	BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS	EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
IN	IN 运算符用于把某个值与一系列指定列表的值进行比较。
NOT IN	IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKE	LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOB	GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT	NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR	OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL	NULL 运算符用于把某个值与 NULL 值进行比较。
IS	IS 运算符与 = 相似。
IS NOT	IS NOT 运算符与 != 相似。
||	连接两个不同的字符串，得到一个新的字符串。
UNIQUE	UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

假设 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 位运算符

位运算符作用于位，并逐位执行操作。真值表 & 和 | 如下：

p	q	p & q	p | q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

假设如果 A = 60，且 B = 13，现在以二进制格式，它们如下所示：

A = 0011 1100B = 0000 1101A&B = 0000 1100A|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 = 61sqlite> select 60 & 13;
60 & 13 = 12sqlite>  select  (~60);
(~60) = -61sqlite>  select  (60 << 2);
(60 << 2) = 240sqlite>  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 数据库交互。这两个库尤其各自的特性：

1. 依赖性和编译

   • 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）。

2. 性能

   • modernc.org/sqlite:

     • 由于它是纯 Go 实现，可能会在性能上稍逊色于基于 C 的实现，尤其是在需要处理大量数据或高并发时。
     • 但是它的性能仍然可以满足大多数应用的需求，尤其是对于小型和中型应用。

   • github.com/mattn/go-sqlite3:

     • 基于 C 实现的 SQLite 库，通常会比纯 Go 实现更快，特别是在大规模数据处理和复杂查询时。
     • 由于使用了原生 C 库，它通常能够提供更高的性能，尤其是当涉及到复杂的 SQL 操作时。

3. 功能支持

   • modernc.org/sqlite:

     • 支持大多数 SQLite 的功能，包括事务、索引、查询等。
     • 支持SQLite3的大部分标准特性。
     • 但可能在某些细节实现上与 SQLite 原生版本有些差距。

   • github.com/mattn/go-sqlite3:

     • 完全实现 SQLite 的功能，并且通常会在新版本的 SQLite 发布后迅速进行更新。
     • 因为它是基于 SQLite C 库，所以对于 SQLite 原生功能的支持更完整，更新也更及时。

4. 社区和维护

   • modernc.org/sqlite:

     • 作为一个较新的库，它的社区支持和文档可能不如 go-sqlite3 庞大。
     • 由于它是纯 Go 实现，它在一些特殊场景下可能会出现一些性能瓶颈，但这些问题通常会在未来的版本中逐步得到解决。

   • github.com/mattn/go-sqlite3:

     • 这是 Go 中最常用的 SQLite 库之一，社区活跃，维护频繁。
     • 因为它依赖于 SQLite 的 C 库，它的功能和性能通常比其他 Go SQLite 库更为可靠。

5. 跨平台支持

   • 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 mainimport ("fmt""time"
)func WithTimer(f func()) func() {return func() {start := time.Now()// do somethingf()// 打印输出耗时fmt.Printf("%.2fs elapsed\n", time.Since(start).Seconds())}
}func A() {time.Sleep(time.Second * 3)
}func main() {WithTimer(A)()
}// 程序输出
// 3.00s elapsed

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

需要用到的三方库如下：

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

为了功能的实现，代码健壮性暂时不在考虑范围内，因此笔者会忽略掉大部分error处理。

SimpleReader

简单的遍历查询：

// simpleReader实现的是普通遍历的方法
type SimpleReader struct {fd   *os.Filerd   *csv.Readerhead []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  []stringcache 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 := 0for {record, err := reader.Read()if err == io.EOF {break}if err != nil {log.Fatal(err)}cache[index] = recordindex++}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 = nilreader.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.ReadersqliteHandler *sqlx.DB // Sql操作抓手lineLength    int      // 行长度head          []string // 头信息
}const (sqlitePath      = "csv.db"sqliteTableName = "test"
)func NewSqliteReader(filename string) *SqliteReader {// 读权限打开csvfp, _ := os.Open(filename)defer fp.Close()reader := csv.NewReader(fp)// 增加csv reader容错性reader.LazyQuotes = truereader.TrimLeadingSpace = true// 读取头信息head, _ := reader.Read()// 打开sqlitedb, _ := 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进行优化：

1. 批量插入：在大量数据写入的情况下，通过事务和预编译语句批量插入数据，避免频繁的磁盘 I/O。

   tx, err := db.Begin()
   stmt, err := tx.Prepare("INSERT INTO ...")
   for _, row := range data {_, err := stmt.Exec(row[0], row[1])if err != nil {log.Fatalf("Failed to execute statement: %v", err)}
   }
   err = tx.Commit()
   

2. 配置性能优化：在 Go 程序中通过 PRAGMA 配置 SQLite 参数以提升性能。

   _, err = db.Exec(`PRAGMA synchronous = OFF;       -- 关闭同步机制PRAGMA journal_mode = MEMORY;   -- 将日志存储在内存中PRAGMA temp_store = MEMORY;     -- 临时表存储在内存中PRAGMA cache_size = 100000;     -- 设置较大的缓存PRAGMA locking_mode = EXCLUSIVE;-- 独占锁模式
   `)
   if err != nil {log.Fatalf("Failed to set PRAGMA options: %v", err)
   }
   

   参数解析：

   1. PRAGMA synchronous = OFF

      • 作用：控制 SQLite 如何在写入数据时与磁盘同步，影响事务的持久性和性能。

      • 解释：

        • OFF：关闭同步机制。写入操作会更快，但在断电或系统崩溃时可能会丢失一些数据，因为数据尚未完全写入磁盘。
        • NORMAL：这是默认值，SQLite 会确保数据在写入前与磁盘同步，较安全，但稍慢。
        • FULL：最安全的模式，确保所有写入操作都被完全同步到磁盘，性能最差。

      • 适用场景：如果对数据的可靠性要求不高，可以选择 OFF，以换取写入速度的提升。

   2. PRAGMA journal_mode = MEMORY

      • 作用：设置事务日志的存储方式，影响数据库的事务处理效率和恢复能力。

      • 解释：

        • MEMORY：将事务日志存储在内存中，避免磁盘 I/O 操作，通常在内存数据库或短时间操作时使用。
        • WAL（Write-Ahead Logging）：将日志存储在磁盘文件中，适合多用户并发读写时使用。
        • DELETE：默认模式，事务日志存储在磁盘文件中，每次写入操作后会删除日志文件。
        • TRUNCATE：类似于 DELETE，但不会删除整个日志文件，而是将文件截断。

      • 适用场景：如果数据持久性不重要（如仅进行短期数据操作），可以选择 MEMORY 来提高性能。注意，这种模式不适合数据库重启后恢复数据。

   3. PRAGMA temp_store = MEMORY

      • 作用：设置 SQLite 临时表的存储位置，影响临时表的存储方式。

      • 解释：

        • MEMORY：临时表存储在内存中，性能较快，因为避免了磁盘 I/O 操作。
        • FILE：临时表存储在磁盘中，适用于大规模的数据操作或内存不足的情况。

      • 适用场景：如果数据库内存足够，并且希望加速临时数据处理，可以选择 MEMORY。但对于大数据量的临时表，可能需要使用 FILE。

   4. PRAGMA cache_size = 100000

      • 作用：控制 SQLite 数据库的页面缓存大小，影响数据库的 I/O 性能。

      • 解释：

        • 该参数控制 SQLite 在内存中使用的页面数量。SQLite 在查询时会缓存数据页，cache_size 越大，数据库可以在内存中保持更多数据，减少磁盘 I/O。
        • 默认情况下，SQLite 使用的缓存大小为 2000 页，每页的大小通常为 1024 字节。设置 cache_size 为 100000 时，数据库可以缓存更多的数据，适用于内存充足的情况。

      • 适用场景：如果你有足够的内存，增加缓存大小可以提升查询性能。对于大数据量操作，可以提高缓存命中率，减少磁盘读取次数。

   5. PRAGMA locking_mode = EXCLUSIVE

      • 作用：设置数据库的锁定模式，影响并发访问和事务的锁定方式。

      • 解释：

        • EXCLUSIVE：独占锁模式，表示一个事务在执行期间，其他事务无法对数据库进行写操作。适用于写操作密集的场景，能够减少冲突，但会降低并发性。
        • NORMAL：默认锁模式，允许多个事务同时读取，但只允许一个事务写入。
        • SHARED：允许多个进程或线程共享读取操作，但只允许一个进程或线程执行写操作。

      • 适用场景：如果你希望一个事务能够独占对数据库的写入操作，避免其他写操作干扰，可以使用 EXCLUSIVE。但如果系统有多个并发写操作，可能会限制并发性能。

3. 创建索引：在大量查询时，为频繁使用的查询条件列创建索引。创建索引后，sqlite Reader的瓶颈仅仅就在构建数据库上了，查询效率得到了大大提高。

   _, err = db.Exec("CREATE INDEX IF NOT EXISTS idx_col1 ON my_table (col1)")
   if err != nil {log.Fatalf("Failed to create index: %v", err)
   }
   

4. 使用内存模式：如果数据量适中且无需长期存储，可以将 SQLite 数据库存储在内存中，以避免磁盘 I/O。（这个方法不考虑）

   db, err := sql.Open("sqlite3", "file::memory:?cache=shared")
   if err != nil {log.Fatalf("Failed to open memory database: %v", err)
   }
   defer db.Close()
   

然后根据我的场景增大查询次数到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构建结束
402.28s elapsed

可以看到查询效果非常不错，而遍历查询此时因为太久不出结果被我Ctrl+C掉了。。。

计算一下二者查询速率的决定因素，假设查询次数为n，单次查询时间为t：

1. 遍历查询= n * t(遍历)
2. sqlite查询= 建库建表t0 + 批量插入数据t1 + 创建索引t2 + n * t(sqlite)
3. 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/