SQLite 中的自动索引

发表于 2023-03-27 更新于 2024-04-08 分类于数据库本文字数： 712 阅读时长 ≈ 3 分钟

自动索引

SQLite 会在一定情况下创建一个自动索引用于帮助提高一个查询的效率，官方文档说明如下：

https://www.sqlite.org/schematab.html
The sqlite_schema.name column will hold the name of the object. (UNIQUE and PRIMARY KEY constraints on tables cause SQLite to create internal indexes with names of the form “sqlite_autoindex_TABLE_N” where TABLE is replaced by the name of the table that contains the constraint and N is an integer beginning with 1 and increasing by one with each constraint seen in the table definition. In a WITHOUT ROWID table, there is no sqlite_schema entry for the PRIMARY KEY, but the “sqlite_autoindex_TABLE_N” name is set aside for the PRIMARY KEY as if the sqlite_schema entry did exist. This will affect the numbering of subsequent UNIQUE constraints. The “sqlite_autoindex_TABLE_N” name is never allocated for an INTEGER PRIMARY KEY, either in rowid tables or WITHOUT ROWID tables.

简单翻译一下：

sqlite_schema.name 列将保存对象的名称。(表上的 UNIQUE 和 PRIMARY KEY 约束会使 SQLite 创建内部索引，其名称形式为 "sqlite_autoindex_TABLE_N"，其中 TABLE 被包含约束的表的名称所取代，N 是一个从 1 开始的整数，在表定义中看到的每个约束都加 1。在一个 WITHOUT ROWID 表中，没有用于主键的 sqlite_schema 条目，但是 "sqlite_autoindex_TABLE_N" 名称被设置为主键，就好像 sqlite_schema 条目确实存在一样。这将影响后续 UNIQUE 约束的编号。"sqlite_autoindex_TABLE_N" 名称永远不会分配给一个 [INTEGER PRIMARY KEY](https://www.sqlite.org/lang_createtable.html#rowid)，无论是在 rowid 表中还是没有 rowid 表中。

可以总结出来如下几条结论：

表字段如果存在 UNIQUE 和 PRIMARY KEY 约束，那么 SQLite 会自动给字段创建 "sqlite_autoindex_TABLE_N" 索引；
对于 PRIMARY KEY 约束，如果字段类型是 INTEGER，不会添加自动索引；
在一个 WITHOUT ROWID 表中，主键的自动索引不会出现在 sqlite_master 表中 (可以在 pragma_index_list('TABLE') 中查看)。

实际例子看一下，如果本地没有条件，在 https://sqliteonline.com/ 可以在线直接执行查看效果。
先创建 users 和 posts 表：

CREATE TABLE IF NOT EXISTS users
(
    id    integer not null
        primary key autoincrement,
    email TEXT    not null unique,
    name  TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS users_name_index
    ON users (name);


CREATE TABLE IF NOT EXISTS posts
(
    title   TEXT not null
        primary key,
    content TEXT
);

查看下索引情况：

SELECT a.type, a.name, tbl_name, b.name as col_name
FROM sqlite_master a, pragma_index_xinfo(a.name) b
WHERE type = 'index'
  and b.name is not null;

结果如下：

type	name	tbl_name	col_name
index	sqlite_autoindex_users_1	users	email
index	users_name_index	users	name
index	sqlite_autoindex_posts_1	posts	title

可以看到 users.id 并没有创建自动索引，非 integer 类型的 primary key 约束和 unique 约束创建了自动索引。

WITHOUT ROWID

再看 WITHOUT ROWID 情况，先创建表 words。

CREATE TABLE IF NOT EXISTS words
(
    name TEXT PRIMARY KEY,
    desc TEXT UNIQUE
) WITHOUT ROWID;

查询 sqlite_master 表：

1	select * from sqlite_master where tbl_name='words' and type='index';

结果为：

type	name	tbl_name	rootpage	sql
index	sqlite_autoindex_words_2	words	9	null

表中自动索引序号是 2，并不是 1。接着查询：

1	select * from pragma_index_list('words');

结果为：

seq	name	unique	origin	partial
0	sqlite_autoindex_words_2	1	u	0
1	sqlite_autoindex_words_1	1	pk	0

注意 orign 字段，主键 pk 对应的是 sqlite_autoindex_words_1。

自动索引

WITHOUT ROWID

参考