Skip to main content

SQLite索引:SQLite索引的基本指南

什么是索引?

在关系数据库中,表是行的列表。同时,每一行都具有由单元格组成的相同列结构。每一行都有一个连续的rowid序列号,用于标识该行。因此,可以将表视为一对列表:(RoWID,ROW)。

与表不同,索引具有相反的关系:(行,rowid)。索引是有助于提高查询性能的附加数据结构。

SQLite Index

SQLite使用B树来组织索引。注意,B代表平衡,B-树是平衡树,而不是二叉树。

B-树使树两侧的数据量保持平衡,以便定位行时必须遍历的级别数始终处于相同的近似数。此外,在B树索引上使用等式(=)和范围(>,>=,<,<=)进行查询非常有效。

索引是如何工作的

每个索引必须与特定的表相关联。索引由一列或多列组成,但索引的所有列必须在同一个表中。一个表可以有多个索引。

无论何时创建索引,SQLite都会创建一个B树结构来保存索引数据。

索引包含来自您在索引中指定的列的数据以及相应的rowid值。这有助于SQLite根据索引列的值快速定位行。

想象一下数据库中的索引就像一本书的索引。通过查看索引,您可以根据关键字快速识别页码。

SQLite创建索引语句

要创建索引,请使用具有以下语法的create index语句:

CREATE [UNIQUE] INDEX index_name 
ON table_name(column_list);

要创建索引,请指定三个重要信息:

如果要确保一列或多列中的值是唯一的,如电子邮件和电话,请使用CREATE INDEX语句中的unique选项。创建唯一索引创建一个新的唯一索引。

SQLite唯一索引示例

让我们创建一个名为contacts的新表进行演示。

CREATE TABLE contacts (
first_name text NOT NULL,
last_name text NOT NULL,
email text NOT NULL
);

试试看

假设您希望强制电子邮件是唯一的,您可以创建一个唯一的索引,如下所示:

CREATE UNIQUE INDEX idx_contacts_email 
ON contacts (email);

试试看

来测试一下。

首先,在contacts表中插入一行。

INSERT INTO contacts (first_name, last_name, email)
VALUES('John','Doe','john.doe@sqlitetutorial.net');

试试看

第二,插入另一行重复的电子邮件。

INSERT INTO contacts (first_name, last_name, email)
VALUES('Johny','Doe','john.doe@sqlitetutorial.net');

试试看

SQLite发出了一条错误消息,表明已违反唯一索引。因为当您插入第二行时,SQLite会检查并确保电子邮件在联系人表的电子邮件行中是唯一的。

让我们在contacts表中再插入两行。

INSERT INTO contacts (first_name, last_name, email)
VALUES('David','Brown','david.brown@sqlitetutorial.net'),
('Lisa','Smith','lisa.smith@sqlitetutorial.net');

试试看

如果您根据特定的电子邮件从contacts表中查询数据,SQLite将使用索引来定位数据。见以下声明:

SELECT
first_name,
last_name,
email
FROM
contacts
WHERE
email = 'lisa.smith@sqlitetutorial.net';

试试看

SQLite index example

要检查SQLite是否使用索引,可以使用EXPLAIN查询计划语句,如下所示:

EXPLAIN QUERY PLAN 
SELECT
first_name,
last_name,
email
FROM
contacts
WHERE
email = 'lisa.smith@sqlitetutorial.net';

试试看

SQLite Index Explain example

SQLite多列索引示例

如果创建的索引由一列组成,SQLite将该列用作排序键。如果您创建了一个包含多列的索引,SQLite会将额外的列用作第二列、第三列……作为排序键。

SQLite按照CREATEINDEX语句中指定的第一列对多列索引上的数据进行排序。然后,它按第二列对重复的值进行排序,依此类推。

因此,在创建多列索引时,列顺序非常重要。

要使用多列索引,查询必须包含与索引中定义的列顺序相同的条件。

下面的语句在contacts表的first_name和last_name列上创建一个多列索引:

CREATE INDEX idx_contacts_name 
ON contacts (first_name, last_name);

试试看

如果在WHERE子句中使用以下条件之一查询contacts表,SQLite将利用多列索引来搜索数据。

1) 按“第一个名称”列筛选数据。

WHERE
first_name = 'John';

2) 按名字和姓氏列筛选数据:

WHERE
first_name = 'John' AND last_name = 'Doe';

但是,如果使用以下条件之一,SQLite将不使用多列索引。

1) 仅按姓氏列筛选。

WHERE
last_name = 'Doe';

2) 按名字或姓氏列筛选。

last_name = 'Doe' OR first_name = 'John';

SQLite显示索引

要查找与表关联的所有索引,请使用以下命令:

PRAGMA index_list('table_name');

例如,此语句显示contacts表的所有索引:

PRAGMA index_list('playlist_track');

以下是输出:

SQLite index - show indexes

要获取有关索引中列的信息,请使用以下命令:

PRAGMA index_info('idx_contacts_name');

此示例返回索引idx_contacts_name的列列表:

从数据库获取所有索引的另一种方法是从sqlite_主表进行查询:

SELECT
type,
name,
tbl_name,
sql
FROM
sqlite_master
WHERE
type= 'index';

SQLite DROP INDEX语句

要从数据库中删除索引,请使用DROP index语句,如下所示:

DROP INDEX [IF EXISTS] index_name;

在这种语法中,在drop index关键字之后指定要删除的索引的名称。IF EXISTS选项仅在索引存在时删除索引。

例如,您可以使用以下语句删除idx_contacts_name索引:

DROP INDEX idx_contacts_name;

试试看

idx_contacts_name索引将从数据库中完全删除。

在本教程中,您了解了SQLite索引,以及如何利用索引来提高查询性能或强制执行唯一约束。