# ❌ 逐条写入 — 性能差
for i in $(seq 1 100); do
    curl -s -XPOST 'localhost:4001/db/execute' \
        -H 'Content-Type: application/json' \
        -d "[[\"INSERT INTO logs (message) VALUES (?)\", \"log entry $i\"]]"
done
# 耗时：~200ms × 100 = ~20s

# ✅ 批量写入 — 性能好
stmts=""
for i in $(seq 1 100); do
    stmts+="{\"q\": \"INSERT INTO logs (message) VALUES (?)\", \"v\": [\"log entry $i\"]},"
done
stmts="${stmts%,}"  # 去掉最后的逗号

curl -s -XPOST 'localhost:4001/db/execute' \
    -H 'Content-Type: application/json' \
    -d "[${stmts}]"
# 耗时：~20ms

-- 将多条写入放在一个事务中
BEGIN;
INSERT INTO logs (ts, msg) VALUES ('2026-05-10 10:00:00', 'msg1');
INSERT INTO logs (ts, msg) VALUES ('2026-05-10 10:00:01', 'msg2');
INSERT INTO logs (ts, msg) VALUES ('2026-05-10 10:00:02', 'msg3');
COMMIT;

# Python — 复用 requests.Session
import requests

session = requests.Session()
session.auth = ('admin', 'password')

# 复用 TCP 连接
for batch in batches:
    session.post('http://localhost:4001/db/execute', json=batch)

# 场景 1: 统计查询（允许短暂不一致）→ none
curl -G 'localhost:4001/db/query' \
    --data-urlencode 'q=SELECT COUNT(*) FROM orders' \
    --data-urlencode 'level=none'

# 场景 2: 一般业务查询 → weak
curl -G 'localhost:4001/db/query' \
    --data-urlencode 'q=SELECT * FROM users WHERE id = 1' \
    --data-urlencode 'level=weak'

# 场景 3: 写后读 → strong
curl -G 'localhost:4001/db/query' \
    --data-urlencode 'q=SELECT * FROM users WHERE id = LAST_INSERT_ID()' \
    --data-urlencode 'level=strong'

# 分析查询执行计划
rq 'q=EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 1 AND status = "pending"'

# 创建优化索引
rxe '[
    ["CREATE INDEX IF NOT EXISTS idx_orders_user_status ON orders(user_id, status)"],
    ["CREATE INDEX IF NOT EXISTS idx_orders_created ON orders(created_at DESC)"],
    ["CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)"]
]'

# ❌ 避免 SELECT * — 只查需要的列
rq 'q=SELECT id, username, email FROM users WHERE id = 1'

# ❌ 避免 LIKE 前缀通配符
rq 'q=SELECT * FROM users WHERE username LIKE "zhang%"'  # ✅ 可以用索引
rq 'q=SELECT * FROM users WHERE username LIKE "%san%"'   # ❌ 无法用索引

# ✅ 使用 LIMIT 限制返回数据
rq 'q=SELECT * FROM orders ORDER BY created_at DESC LIMIT 20'

# ✅ 使用覆盖索引
rq 'q=SELECT user_id, status, COUNT(*) FROM orders GROUP BY user_id, status'

# Nginx 配置示例 — 读写分流
upstream rqlite_write {
    server 10.0.1.10:4001;  # Leader
}

upstream rqlite_read {
    server 10.0.1.10:4001;  # Leader
    server 10.0.1.11:4001;  # Follower
    server 10.0.1.12:4001;  # Follower
}

server {
    # 写请求路由到 Leader
    location /db/execute {
        proxy_pass http://rqlite_write;
    }
    
    # 读请求路由到任意节点
    location /db/query {
        proxy_pass http://rqlite_read;
    }
}

# 无索引查询
rxe '[["DROP INDEX IF EXISTS idx_orders_user_status"]]'
# 10 万行数据，查询耗时 ~50ms

# 有索引查询
rxe '[["CREATE INDEX idx_orders_user_status ON orders(user_id, status)"]]'
# 10 万行数据，查询耗时 ~1ms

# 查看表的索引
rq 'q=SELECT name, sql FROM sqlite_master WHERE type="index" AND tbl_name="orders"'

# 重建索引（碎片整理）
rxe '[["REINDEX idx_orders_user_status"]]'

# 删除未使用的索引
rxe '[["DROP INDEX IF EXISTS idx_unused"]]'

# EXPLAIN QUERY PLAN
curl -s -G 'localhost:4001/db/query' \
    --data-urlencode 'q=EXPLAIN QUERY PLAN SELECT u.username, o.product FROM users u JOIN orders o ON u.id = o.user_id WHERE u.id = 1'

# 查看当前配置
rq 'q=PRAGMA cache_size'
rq 'q=PRAGMA page_size'
rq 'q=PRAGMA journal_mode'
rq 'q=PRAGMA synchronous'
rq 'q=PRAGMA temp_store'

# Python — 使用 Session 复用连接
import requests
from requests.adapters import HTTPAdapter

session = requests.Session()
adapter = HTTPAdapter(
    pool_connections=10,    # 连接池大小
    pool_maxsize=20,        # 最大连接数
    max_retries=3           # 重试次数
)
session.mount('http://', adapter)
session.mount('https://', adapter)
session.auth = ('admin', 'password')

// Go — 自定义 Transport
import (
    "net/http"
    "time"
)

client := &http.Client{
    Transport: &http.Transport{
        MaxIdleConns:        100,
        MaxIdleConnsPerHost: 20,
        IdleConnTimeout:     90 * time.Second,
    },
    Timeout: 30 * time.Second,
}

# 设置合理的超时
curl --connect-timeout 5 --max-time 30 \
    -XPOST 'localhost:4001/db/execute' \
    -H 'Content-Type: application/json' \
    -d '[["INSERT INTO ..."]]'

# 查询超时参数
curl -G 'localhost:4001/db/query' \
    --data-urlencode 'q=SELECT * FROM large_table' \
    --data-urlencode 'timeout=10s'

# 将旧数据归档
rxe '[
    ["CREATE TABLE IF NOT EXISTS orders_archive AS SELECT * FROM orders WHERE 0=1"],
    ["INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < \"2025-01-01\""],
    ["DELETE FROM orders WHERE created_at < \"2025-01-01\""],
    ["VACUUM"]
]'

# VACUUM 回收已删除数据占用的空间
rxe '[["VACUUM"]]'

#!/bin/bash
# rqlite-bench.sh — 简单的 rqlite 性能测试
HOST="${RQLITE_HOST:-localhost:4001}"
TOTAL=${1:-1000}
BATCH=${2:-50}

echo "=== rqlite 性能测试 ==="
echo "目标: $HOST"
echo "总记录数: $TOTAL"
echo "批次大小: $BATCH"
echo ""

# 准备表
curl -s -XPOST "http://$HOST/db/execute" \
    -H 'Content-Type: application/json' \
    -d '[["CREATE TABLE IF NOT EXISTS bench (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT, ts DATETIME DEFAULT CURRENT_TIMESTAMP)"]]' > /dev/null

# 批量写入测试
echo "--- 批量写入测试 ---"
start=$(date +%s%N)
inserted=0
while [ $inserted -lt $TOTAL ]; do
    remaining=$((TOTAL - inserted))
    current_batch=$((remaining < BATCH ? remaining : BATCH))
    
    stmts=""
    for i in $(seq 1 $current_batch); do
        stmts+="{\"q\": \"INSERT INTO bench (data) VALUES (?)\", \"v\": [\"data-$inserted-$i\"]},"
    done
    stmts="${stmts%,}"
    
    curl -s -XPOST "http://$HOST/db/execute" \
        -H 'Content-Type: application/json' \
        -d "[${stmts}]" > /dev/null
    
    inserted=$((inserted + current_batch))
done
end=$(date +%s%N)
write_time=$(( (end - start) / 1000000 ))
write_tps=$(( TOTAL * 1000 / write_time ))
echo "  写入 $TOTAL 条: ${write_time}ms (${write_tps} ops/s)"

# 查询测试（不同一致性级别）
for level in none weak strong; do
    start=$(date +%s%N)
    for i in $(seq 1 100); do
        curl -s -G "http://$HOST/db/query" \
            --data-urlencode "q=SELECT COUNT(*) FROM bench" \
            --data-urlencode "level=$level" > /dev/null
    done
    end=$(date +%s%N)
    read_time=$(( (end - start) / 1000000 ))
    read_tps=$(( 100 * 1000 / read_time ))
    echo "  查询(100次,$level): ${read_time}ms (${read_tps} ops/s)"
done

# 清理
curl -s -XPOST "http://$HOST/db/execute" \
    -H 'Content-Type: application/json' \
    -d '[["DROP TABLE bench"]]' > /dev/null

echo ""
echo "测试完成"