sqlite

select datetime('now', 'localtime');
select strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime');

CREATE TABLE whatever (
     ...
     ts DATETIME DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')),
     ...
);

参考 https://stackoverflow.com/questions/381371/sqlite-current-timestamp-is-in-gmt-not-the-timezone-of-the-machine

签到逻辑例子

create table user (
  id integer primary key,
  name varchar(64) not null,
  ts datetime default (datetime('now', 'localtime'))
);

create table checkin (
  id integer primary key,
  user integer not null references "user" ("id"),
  date date not null default (date('now', 'localtime')),
  ts datetime default (datetime('now', 'localtime')),
  unique (user, date)
);

-- create index idx_checkin__user on checkin (user);

insert into user (name) values('q1');
insert into user (name) values('q2');
insert into user (name) values('q3');

insert into checkin (user) values(1);
insert into checkin (user) values(1);
insert into checkin (user) values(2);
insert into checkin (user) values(3);

对应 ponyorm

class User(Entity):
    name = Required(str, 64)
    ts = Optional(datetime)
    checkins = Set(lambda: Checkin)

class Checkin(Entity):
    user = Required(lambda: User)
    date = Required(date, sql_default="(date('now', 'localtime'))")
    ts = Optional(datetime)
    composite_key(user, date)

两个表：main, patch，结构一样，main 数据量很大，patch 数据量一般，现在需要合并 patch 的数据至 main，对已存在的老数据做覆盖操作，对不存在的数据做新增操作，尽量不要做无用功，尽可能地收集到更新结果如修改数和新增数。

CREATE TABLE IF NOT EXISTS "main" (
    k primary key,
    v
) without rowid;
CREATE TABLE IF NOT EXISTS "patch" (
    k primary key,
    v
) without rowid;

-- main 是老数据，把 patch 作为补丁更新至 main , 参考下面
-- 执行速度只和 patch 的数据量有关 O(n)，和目标 main (with index) 的数据量呈对数增长关系 O(log n)，patch 数据量有 100W 时，2 秒搞定
-- patch 可以不要索引，数据录入速度更快，如果保证数据是排序好的，速度和索引版相差不大

select 'edit';
replace into main (k, v) 
    select patch.k, patch.v from patch inner join main on patch.k = main.k where patch.v != main.v;  -- update exists
select changes();

select 'new';
insert into main (k, v) 
    select patch.k, patch.v from patch left join main on patch.k = main.k where main.v is null;  -- find new
select changes();


-- 也可把数据输出到临时表，然后再变更或新增

create temporary table tmp (
    k primary key,
    v,
    p
);

insert into tmp
    select patch.k, patch.v, main.v from patch left join main on patch.k = main.k where patch.v != main.v or main.v is null;

一次写入或修改数据太多，在这过程中，别的进程读数据会发生这种错误，暂时没有深入研究。目前的解决方案是，一次性修改数据超过 10000，就做一次 commit，这样，无论是在什么阶段，都不会阻塞其它进程的读操作。

Sqlite 可以在 NFS 上运行，建议只读，而且主动设置：

PRAGMA query_only = yes;
PRAGMA locking_mode = exclusive;

如果不设置，那么用不到本机的内存缓存，会一直读网络。但是又有另一个问题了，读的时候，会禁止写入。

http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html https://sqlite.org/docs.html https://sqlite.org/lang.html

Sqlite 方案单进程可以达到万级别的查询速度。

在内存占用上，此方案不强求内存配置。内存小，能缓存的数据少，磁盘 IO 多，速度较慢。但是充分缓存了文件的情况下，能做到少于 Redis 的内存占用(cache)，和与 Redis 同级别 的查询速度。

先来一个小目标测试一下，这里我们模拟常见的场景，md5 值作为 key：

ins.py:

#!/usr/bin/env python3

import sqlite3
import os
import sys
import time

from faker import Faker


fake = Faker()

db = sqlite3.connect("kv")
db.execute("pragma synchronous = off")
db.execute("pragma temp_store = memory")
db.execute("pragma journal_mode = memory")
db.execute("pragma busy_timeout = 60000")

db.execute("""
CREATE TABLE IF NOT EXISTS "test" (
    k primary key,
    v
) without rowid;
""")


def insert(n):
    t0 = time.time()
    c = db.cursor()
    l = [(os.urandom(16), fake.name()) for _ in range(n)]
    c.executemany("insert into test values(?,?)", l)
    db.commit()
    print(time.time() - t0)


if __name__ == '__main__':
    for i in range(int(sys.argv[1])):
        insert(10000)

表定义有两处特殊：

k, v 没有指定类型
加了 without rowid

具体作用去翻文档吧。

一次插入 10k，循环 500 次，20 并行，总计一个亿：

$ seq 20 | time xargs -P 20 -I _ ./ins.py 500
real    45m 56.80s
user    10h 29m 48s
sys     22m 46.14s

差不多一个小时内搞定，每秒能插 30k，是不是挺快。还有不用担心随机生成数据的冲突的问题，就上面生成一个小目标的例子，有兴趣可以算一下，遇到主键冲突异常的概率。

来看看文件大小：

$ ls -lh kv
-rw-r--r--    1 foo     foo        3.7G Apr  4 15:16 kv

VACUUM 后，文件更小，只有 3.3G。原始数据也才 2.9G。

$ sqlite3 kv 'pragma synchronous = off; VACUUM'
$ ls -lh kv
-rw-r--r--    1 foo     foo        3.3G Apr  4 15:16 kv
$ sqlite3 kv 'select * from test' >kv.raw
$ ls -lh kv.raw
-rw-r--r--    1 foo     foo        2.9G Apr  4 15:16 kv.raw

可以直接推断出，你的机器有 4G 内存，就能缓存全部的数据，包括索引，也就是可以搞个海量数据加极限高速的 k-v 服务。有多快呢，来个 python 版的：

#!/usr/bin/env python3

import sqlite3
import os
import time


db = sqlite3.connect("kv")
t = time.monotonic()
n = 0
c = db.cursor()

while True:
    if time.monotonic() - t > 1:
        print(n, end='\r')
        t = time.monotonic()
        n = 0
    n += 1

    o = c.execute("select * from test where k = ?", (os.urandom(16),)).fetchone()
    if o:
        print(*o)
        break

一般，也就 45k/s 左右吧。这是 python 版 + 单进程，多进程情况下，不说性能 *N 吧，基本上接近线性增长，5 进程 200k/s 没问题。

再来个 Golang 版的，55k/s 左右:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"math/rand"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	n := 0
	t := time.Now()

	db, err := sql.Open("sqlite3", "kv")

	if err != nil {
		log.Fatal(err)
	}

	stmt, _ := db.Prepare("select v from test where k = ?")

	for {
		k := make([]byte, 16)
		v := ""
		rand.Read(k)
		row := stmt.QueryRow(k)
		row.Scan(&v)
		if v != "" {
			break
		}
		t2 := time.Now()
		if t2.Sub(t) > time.Second {
			fmt.Printf("%v\r", n)
			t = t2
			n = 0
		}
		n++
	}
}

:memory: 纯内存版本, 80k/s 左右：

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"math/rand"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	n := 0
	t := time.Now()

	db, err := sql.Open("sqlite3", ":memory:")

	if err != nil {
		log.Fatal(err)
	}

	conn, err := db.Conn(context.TODO())
	if err != nil {
		log.Fatal(err)
	}
	conn.ExecContext(context.TODO(), `
		attach database 'kv' as t;
		create table test(k primary key, v) without rowid;
		insert into test select k, v from t.test;
	`)
	fmt.Println("loaded")

	stmt, _ := conn.PrepareContext(context.TODO(), "select v from test where k = ?")

	for {
		k := make([]byte, 16)
		v := ""
		rand.Read(k)
		row := stmt.QueryRow(k)
		row.Scan(&v)
		if v != "" {
			break
		}
		t2 := time.Now()
		if t2.Sub(t) > time.Second {
			fmt.Printf("%v\r", n)
			t = t2
			n = 0
		}
		n++
	}
}

（奇怪的是，:memory:，Python 的性能更好）

是不是挺恐怖的速度，就算把 Redis 的多线程版本拿来对比，也是不虚的。还有内存方面优势：

贫瘠的内存也能跑，最差情况也就回退到依赖磁盘 4k random read 的性能，这年头谁没个 SSD 是吧。就算是穷到只买得起阿里云 *普通云盘 5GiB (200 ~ 500 IOPS)*，也能做到 250qps
相当于利用操作系统做了个 LRU 缓存，内存越大缓存命中率越高，磁盘 IO 压力越小
极限速度也无需太大内存，内存比数据库文件大一丢丢，全部数据到缓存页之后，就没磁盘 IO 什么事了，全内存 IO，怕不怕～

todo, kv 一亿+vmtouch + ali 普通硬盘 + swapon + 限制内存使用 + 生成图表

说到内存，不得不说说 Redis，内存占用太多（hash 表的特性如此），况且这么一坨数据的维护也是麻烦。来试试：

import sqlite3
import time
import redis

db = sqlite3.connect("kv")
rd = redis.Redis()

t = time.monotonic()
n = 0
d = {}

for k, v in db.execute("select * from test "):
    d[k] = v
    if len(d) >= 1000:
        rd.mset(d)
        d.clear()
    if time.monotonic() - t > 1:
        print(n, end='\r')
        t = time.monotonic()
        n = 0
    n += 1

if d:
    rd.mset(d)

导入这一亿数据后，看内存：

used_memory:11002201279
used_memory_human:10.25G
used_memory_rss:20296654848
used_memory_rss_human:18.90G

hash 表的内存消耗不容小觑。哪位英雄站出来维护下 新增/更新/备份/恢复 ？

拆分 256 份：

conv.sh

#!/bin/sh
db=$1
sqlite3 tmp/$db <<EOF
attach database kv as kv;
.databases
CREATE TABLE IF NOT EXISTS test (
    k primary key,
    v
) without rowid;
delete from test;
insert into test select * from kv.test where k > x'${db}' and k < x'${db}ffffffffffffffffffffffffffffff';
VACUUM;
EOF

python3 -c 'for n in range(256): print(f"{n:02x}")' | xargs -n1 -P20 ./conv.sh

对于顺序读取，磁盘很欢迎，操作系统对此也有预读优化（多往后面读一些 pages），而随机读，对于非 SSD 来说，就很痛苦了。

#!/usr/bin/env python3

import mmap
import random

n = 4096

with open("big-file", "rb") as f:
    mm = mmap.mmap(f.fileno(), 0, prot=mmap.PROT_READ)
    l = list(range(len(mm) // n))
    random.shuffle(l)
    for i in l:
        mm[i * n]

上面的例子会很慢很慢，操作系统甚至会放弃治疗（不预读），如果注释掉 shuffle，则为顺序读，速度杠杠的。

Sqlite 使用技巧拾零

时间格式

高速合并数据

OperationalError: database is locked

NFS

其它参考学习

K-V

磁盘的随机读取