SQLite

SQLite

Tips

-- 导入 csv
create table foo(a, b);
.mode csv
.import test.csv foo
-- 日期函数
-- https://sqlite.org/lang_datefunc.html
-- 转换时间戳
select datetime( 1323648000, 'unixepoch' );
-- 获取时间戳, 带毫秒
SELECT CAST((julianday('now') - 2440587.5) * 86400000 AS INTEGER);
-- 默认值带毫秒
CREATE TABLE IF NOT EXISTS event
(
create_at DATETIME DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
);
-- 附加其他文件到数据库
ATTACH 'cache.db' AS cache;

Notes

The SQLite Query Optimizer Overview

  • WHERE 条件分析
    • 索引选择
  • BETWEEN 优化
    • 转换为 >= <=
    • 与索引判断
  • OR 优化
    • 相同列语意上可转换为 IN
    • 不同列 OR 需要按 Cost 选择
  • LIKE 优化
    • 前缀索引
  • Skip-Scan 优化
    • 条件不以索引最左列开始
    • 尝试限定最左列条件来利用索引
    • 例如当最左列只有固定几个值的时候
  • JOIN
    • JOIN 重排序
    • 基于 SQLITE_STAT 选择顺序
    • 通过 CROSS JOIN 控制查询
  • 多个索引的选择
    • 基于统计
    • 使用 +a = 4 的方式来暗示不实用 a 的索引
    • 范围查询
  • 覆盖索引
    • 全表扫描时选择包含 rowid 的索引
  • ORDER BY 优化
    • 尝试使用符合 ORDER BY 要求的索引
  • 通过索引进行部分 ORDER BY
    • 包含多个 ORDER BY 时
  • 拉平子查询
    • 将子查询优化为 JOIN
  • 协程执行子查询
    • 部分子查询可以与当前查询并行执行
  • MIN/MAX 优化
    • 如果列是某索引的最左列可以使用索引
  • 自动索引
    • 当查询没有索引时,可能会在语句执行期间自动创建一个索引。
    • 创建索引 O(NlogN) 全表扫描 O(N)
  • 下推优化/Push-Down Optimization
    • 一个子查询无法被平整为外部查询,可尝试将外部查询的条件下推到子查询中。
  • LEFT JOIN Strength Reduction Optimization
    • 有时一个 LEFT JOIN 可以转换为一个普通的 JOIN,只要两者结果相同。
    • if any column in the right-hand table of the LEFT JOIN must be non-NULL in order for the WHERE clause to be true, then the LEFT JOIN is demoted to an ordinary JOIN.
  • 忽略 LEFT JOIN
    • 有时 LEFT JOIN 可以完全呗忽略
      • 飞聚合查询
      • DISTINC 查询或使用 ON/USING 来限制 JOIN 只匹配一列
      • LETF JOIN 右边表的列未在外部查询使用

The Next-Generation Query Planner

统计信息 https://www.sqlite.org/fileformat2.html#stat1tab https://www.sqlite.org/lang_analyze.html ANALYZE 会生成统计信息表

sqlite_stat1 可更新或删除,不可 alter 或创建

CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE sqlite_stat2(tbl,idx,sampleno,sample);
CREATE TABLE sqlite_stat3(tbl,idx,nEq,nLt,nDLt,sample);
CREATE TABLE sqlite_stat4(tbl,idx,nEq,nLt,nDLt,sample);

.help

指令描述默认
.backup ?DB? FILE备份数据库(默认为 main)到 FILE
.bail onoff错误时停止
.binary onoff二进制输出
.clone NEWDB克隆现有的数据库到 NEWDB
.databases例举添加的数据库名和文件
.dbinfo ?DB?显示数据库状态信息
.dump ?TABLE? ...以 SQL 的形式转储数据库
如果指定了表,则只转储匹配 LIKE 模式的表.
.echo onoff命令回显
.eqp onoff自动执行 EXPLAIN QUERY PLAN
.exit退出
.explain ?onoff?使用适合 EXPLAIN 的输出模式
如果未指定参数,则为打开
.fullschema显示 sqlite_stat 的 schema 和内容
.headers onoff显示头
.help显示该信息
.import FILE TABLE导入 FILE 内容为 TABLE
.indexes ?TABLE?显示所有索引
如果指定了表,则只显示匹配 LIKE 模式的表.
.limit ?LIMIT? ?VAL?显示或更改 SQLITE_LIMIT 的值
.log FILEoff日志, FILE 可以为 stderr/stdout
.mode MODE ?TABLE?设置输出模式
-ascii 行列使用 0x1F 和 0x1E 分隔
-csv 逗号分隔的值
-column 左对齐列 (参见 .width)
-html HTML <table> 代码
-insert Insert 语句 TABLE
-line 每行只有值
-list 使用 .separator 分隔的值
-tabs 制表符分割的值
-tcl TCL list 元素
.nullvalue STRING使用 STRING 来替代 NULL 值
.once FILENAME输出下一个 SQL 指令到 FILENAME
.open ?FILENAME?便于现有数据库并打开 FILENAME
.output ?FILENAME?发送输出到 FILENAME 或 stdout
.print STRING...显示 STRING
.prompt MAIN CONTINUE修改提示符
.quit退出
.read FILENAME执行 FILENAME 中的 SQL
.restore ?DB? FILE恢复 DB(默认 "main") 的内容到 FILE
.save FILE将内存数据库写入到 FILE
.scanstats onoff打开或关闭 sqlite3_stmt_scanstatus() 监测
.schema ?TABLE?显示 CREATE 语句
如果指定了表,则只显示匹配 LIKE 模式的表.
.separator COL ?ROW?修改列和行的分隔符,会影响输出和 .import
.shell CMD ARGS...在系统 shell 中执行 CMD ARGS...
.show显示当前的各种设置
.stats onoff统计开关
.system CMD ARGS...在系统 shell 中执行 CMD ARGS...
.tables ?TABLE?显示表名
如果指定了表,则只显示匹配 LIKE 模式的表.
.timeout MS打开被锁表的超时时间
.timer onoffSQL 计时器开关
.trace FILEoff输出每个 SQL 语句
.vfsname ?AUX?输出 VFS 栈
.width NUM1 NUM2 ...设置 "column" 模式的宽度,负值为右对齐

Deeper

https://dzone.com/articles/how-sqlite-database-works