1 使用嵌入式数据库

Sqlite 在 2000，8 月发布进程内数据库,非常稳定，安全. 现在已经发展到了 sqlite3，它有哪些特征有何用处？

1.1 sqlite3 配置

1 日志模式配置 SQLite 如何写入事务

WAL write ahead log 提前写日志安全写入磁盘

  PRAGMA  journal_model = WAL;   

它将让你获得每个事件的快照，和一些读写性能，允许你同时写入和读取。

同时只能有一个写入器。

2 写超时配置

繁忙超时设置写入事务将等待多长时间开始 ，如果不设置，那么在有其他写入器工作时，将立即失败， 推荐5秒

                PRAGMA busy_timeout = 5000;

3 外键配置 Foreign keys

由于历史原因，默认情况下不强制执行外键， 请使用外键

  PRAGMA forgien_keys = ON;

1.2 sqlite3 类型系统

5个默认类型

        INTEGER -- integral nunmbers
        REAL -- floating point numbers
        TEXT -- readable text data
        BLOB -- binary data
        NULL -- no data

sqlite3 数据库中的存储内容，每个值都与上面的某一个类型关联，类型定义在表格上 大多没有意义。

STRICT mode 严格模式 在 3.37.0才加入 （2021年底）

        CREATE TABLE t (x INTEGER, Y TEXT);

等同于

       CREAET TABLE t(x,y);

缺失的类型

  TIMESTAMP 时间类型。

SQLite有内建函数转换这几种格式

时间类型 typically stored 通常存储为3个形式

最早的格式ISO 8601，但需要更多空间

                          ISO 8601 /RFC 3339(2000-0101T00:00:00Z) 

unix时间

     Seconds since Unix Epoch(1637621759) 

时间戳

      Julian data(2459541.45578376)  

十进制

      DECIMAL  

1.3 sqlite3 测试经验

SQLite 可以按内存模式在后端运行，这样避免磁盘读写 访问调用的 延时

	func TestDB(t *testing.T) {
		db, err := sql.Open("sqlite3", ":memory:")
		if err != nil {
			t.Fatal(err)
		}
		defer db.Close()
		////do things at here
	}

性能指标 go-sqlite3 pgsql

    DROP IF EXIST     -             5681us
    CREATE TABLE      801us        2887us
    INSERT            25us          640us
    SELECT            24us          298us

参考仓库

    github.com/benjohnson/production-sqlite-go
    2.3GHz 8-core intel Core 19 macbook pro

1.4 并行处理 sqlite3 parallelizing in-memory

使用 testin.T.Parallel() 执行内存模式的sqlite3 并行。

go-sqlite3 有一个全局锁，这将减慢 并行测试。

tailscale 实现可以很好地与 cpu 内核一起扩展。

不同的数据库 之间没有共享机制。

• 性能

数据耐用性，可靠性

可靠的复制数据， 数据安全性 ，10万次读写有一次失败

  <-------------------------------->>
  hard drive          EBS(gp3/io1)     EBS io2     S3
  99%                 99.9%            99.999%       99.99999999999%

• 数据丢失 并非总是灾难性的

在 2017年，Gitlab 丢失了 6小时数据， Postgre 主副 配置，保证损失更小。数据也可能被操作者误删除。

所以在数据可靠性，耐用性，性能 成本之间进行权衡 是有必要的。

1.5 sqlite 数据持久性存储

sqlite 中的数据持久性

1, 常规备份 .backup, mybackup.db

  命令: sqlite3 my.db ".backup 'mybackup0430.db'"
  
      Pros   fast cheap hard to mess up # 快速 便宜  不易出错
      Cons  larger data loss 大数据容易丢失
      Backup daily 日常备份 或 每小时发送到 S3
      Use time-based file naming 以时间基线为基础的多份备份 和回滚快照
      B-tree 数据库 压缩较好 compress well

2, 数据可靠性 Litestream 几乎实时的监听数据

     Pros  很小的丢失窗口，便宜
     Cons  比常规备份 操作更复杂
     Continuous   流式备份到 S3，streaming backup to s3
     Automatically 自动化，发生灾难时自动恢复 
     cost： 价格： $0.03/month 

指令: listestream replicate db s3://mybucket/db

3, 基于raft的分布式 共识协议

 Pros优点： 非常耐用 very durable
 Cons缺点:   昂贵,更复杂
 Raft-based： 基于raft 共识协议，Philip 贡献了 rqlite，Canonical贡献了dqlite
 Primary/replica: 主从复制 精简
 Blockchain-based: 以量化费用 为基准

• 查询性能 performance

大多数数据库 是 以B-tree为 基础，有许多具体优化，但是都有一个类似的性能曲线。
网络延时是一个较大的优化点，特别对于 点查询 和 简单范围查询，SQLite 没有网络功能。

点查询 对比：

  sqlite     18us 微秒
  pg local   171us
  pg AZ       321 us
  pg region    区域 902us

1.6 Sqlite3 扩展

sqlite 没有内建的 水平扩展方案，但它是灵活的，通过分片或每个数据库拥有一个 用户权限 来隔离用户，使用一致的哈希算法 分配用户。

sqlite3 有以下特点

没有额外的依赖
启动和运行速度快
只有很少的配置
要小心 类型系统
时间类型 和 十进制 数字类型 的几个表示方式

并行性能

 支持 内建的内存模式
 支持上千个并发 在使用 t.Parallel() 时
 并行化需要 tailscale的实现版本

可靠性 集群

 备份
 流式备份，使用Litestream
 集群SQLite clustered 使用 dqlite，LiteReplica， 或 Bedrock

单点性能高。
没有讨厌的网络延迟或序列化开销。
随着CPU RAM 开销的增加，垂直扩展。

• 作为嵌入式数据库 embedded databases

符号ACID标准操作

Extensive 广泛的 SQL支持

全文搜索

支持存储 JSON

1.7 使用实例

type Database struct {
    DB                  *sql.DB
    Writer              Writer
}

type Writer interface { 
    Do(db *sql.DB, txn *sql.Tx, f func(txn *sql.Tx) error) error
}

type StreamStatements struct {
	db                   *sql.DB
	increaseStreamIDStmt *sql.Stmt
}

type Datasource struct {
	Database
	db       *sql.DB
	writer   Writer
	streamID StreamStatements
}

type Base struct {
	Cfg                  *config.Global   //Version int `yaml:"version"`
	Database               *sql.DB
	DatabaseWriter         Writer

} 

type DBOptions struct {
	ConnectionString DataSource `yaml:"connection_string"`
 	MaxOpenConnections int `yaml:"max_open_conns"`
 	MaxIdleConnections int `yaml:"max_idle_conns"`
 	ConnMaxLifetimeSeconds int `yaml:"conn_max_lifetime"`
}

type ExcWriter struct {
	running atomic.Bool
	todo    chan WriterTask
}

func NewExcWriter() Writer {
	return &ExcWriter{
		todo: make(chan ExcWriter),
	}
}

 type WriterTask struct {
	db   *sql.DB
	txn  *sql.Tx
	f    func(txn *sql.Tx) error
	wait chan error
}


func NewDatabase(base *Base, dbProperties *DBOptions) (*Datasource, error) {
	var d Datasource
	var err error
	if d.db, d.writer, err = base.DatabaseConnection(dbProperties, NewExcWriter()); err != nil {
		return nil, err
	}
	if err = d.prepare(base.Context()); err != nil {
		return nil, err
	}
	return &d, nil
}

2 小结

1 何时避免使用：

• 如果需要多方协作，可能需要避免使用sqlite3
• 经常有长时间运行的写操作 并发也比较高的需要避免sqlite3
• 有较多事务操作 时，需要避免使用sqlite3. 因为它是 单进程的

2 什么应用程序适合SQLlite,

• 嵌入式软件.

• 并发性的改进使其更通用的程序.非常适合读取频繁的工作负载.

• 小到中等的请求负载.

    100‘s of requests/sec
  

• 在大多数应用程序它都是不错的候选 。

资源；

   首页
    github.com/mattn/go-sqlite3
    transpiled 转译库
            modernc.org/sqlite
    底层SQLITE库
            crawshaw.io/sqlite
    进程内SQLite库
            github.com/tailscale/sqlite

开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 17 天，点击查看活动详情