SQLite 索引优化 - 提升数据库性能

介绍

在这个实验中，你将学习如何使用索引来优化 SQLite 数据库的性能。你将创建单列索引来提高查询速度，重点关注实际应用和分析。你还将学习分析查询计划并删除冗余索引。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") sqlite/SQLiteGroup -.-> sqlite/build_index("Create Single Index") sqlite/SQLiteGroup -.-> sqlite/clear_index("Drop Single Index") sqlite/SQLiteGroup -.-> sqlite/verify_table("Check Table Existence") subgraph Lab Skills sqlite/query_where -.-> lab-552552{{"SQLite 索引优化"}} sqlite/sort_data -.-> lab-552552{{"SQLite 索引优化"}} sqlite/build_index -.-> lab-552552{{"SQLite 索引优化"}} sqlite/clear_index -.-> lab-552552{{"SQLite 索引优化"}} sqlite/verify_table -.-> lab-552552{{"SQLite 索引优化"}} end

创建数据库和表

在这一步中，你将创建一个 SQLite 数据库和一个 employees （员工）表。然后，你将向表中插入一些示例数据。

首先，在 LabEx 虚拟机（VM）中打开你的终端。你的默认路径是 /home/labex/project。

要创建一个名为 my_database.db 的 SQLite 数据库，请运行以下命令：

sqlite3 my_database.db

此命令将在你的项目目录中创建一个名为 my_database.db 的新 SQLite 数据库文件，并打开 SQLite shell。

接下来，创建具有以下结构的 employees 表：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    department TEXT
);

这个 SQL 语句创建了一个名为 employees 的表，其中包含五列：id、first_name（名）、last_name（姓）、email（电子邮件）和 department（部门）。id 列被设置为主键（primary key），这意味着它必须包含唯一值。

现在，将一些示例数据插入到 employees 表中：

INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', '[email protected]', 'Sales'),
('Jane', 'Smith', '[email protected]', 'Marketing'),
('Robert', 'Jones', '[email protected]', 'Engineering'),
('Emily', 'Brown', '[email protected]', 'Sales'),
('Michael', 'Davis', '[email protected]', 'Marketing');

这将把五行数据插入到 employees 表中。

要验证数据是否已正确插入，请运行以下命令：

SELECT * FROM employees;

你应该看到以下输出：

1|John|Doe|[email protected]|Sales
2|Jane|Smith|[email protected]|Marketing
3|Robert|Jones|[email protected]|Engineering
4|Emily|Brown|[email protected]|Sales
5|Michael|Davis|[email protected]|Marketing

创建索引

在这一步中，你将在 employees 表的 last_name（姓）列上创建一个索引。

索引是特殊的查找表，数据库搜索引擎可以使用它们来加速数据检索。

要创建一个名为 idx_lastname 的索引，作用于 last_name 列，请运行以下命令：

CREATE INDEX idx_lastname ON employees (last_name);

这个 SQL 语句在 employees 表的 last_name 列上创建了一个名为 idx_lastname 的索引。

要验证索引是否已创建，你可以使用以下命令：

PRAGMA index_list(employees);

此命令将显示 employees 表上的索引列表，包括你刚刚创建的 idx_lastname 索引。你应该看到类似于这样的输出：

0|idx_lastname|0|c|0

此输出确认 idx_lastname 索引存在于 employees 表上。

使用 EXPLAIN QUERY PLAN 分析查询

在这一步中，你将学习如何使用 EXPLAIN QUERY PLAN 命令来分析 SQLite 如何执行查询。这是一个强大的工具，可以帮助你理解查询性能并识别潜在的瓶颈。

要分析一个查询，请在其前面加上 EXPLAIN QUERY PLAN。例如，要分析以下查询：

SELECT * FROM employees WHERE last_name = 'Smith';

运行以下命令：

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';

输出将如下所示：

QUERY PLAN
`--SEARCH employees USING INDEX idx_lastname (last_name=?)

这个输出告诉你，SQLite 正在使用 idx_lastname 索引来查找姓氏为 'Smith' 的员工。 SEARCH 关键字表示 SQLite 正在使用索引来执行搜索。

如果没有使用索引，输出将有所不同。例如，如果你查询名字为 'John' 的员工（并且你没有在 first_name（名）列上创建索引），则输出将是：

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';

输出将如下所示：

QUERY PLAN
`--SCAN employees

SCAN 关键字表示 SQLite 正在执行全表扫描（full table scan），这意味着它必须检查表中的每一行才能找到名字为 'John' 的员工。这不如使用索引有效。

添加更多数据并分析排序

让我们插入更多数据，使查询计划分析更有意义。将以下数据插入到 employees 表中：

INSERT INTO employees (first_name, last_name, email, department) VALUES
('Alice', 'Johnson', '[email protected]', 'HR'),
('Bob', 'Williams', '[email protected]', 'Finance'),
('Charlie', 'Brown', '[email protected]', 'IT'),
('David', 'Miller', '[email protected]', 'Sales'),
('Eve', 'Wilson', '[email protected]', 'Marketing'),
('John', 'Taylor', '[email protected]', 'Engineering'),
('Jane', 'Anderson', '[email protected]', 'HR'),
('Robert', 'Thomas', '[email protected]', 'Finance'),
('Emily', 'Jackson', '[email protected]', 'IT'),
('Michael', 'White', '[email protected]', 'Sales');

现在，让我们分析一个涉及排序的更复杂的查询。假设你要查找 'Sales'（销售）部门中的所有员工，并按姓氏（last name）对他们进行排序。你可以使用以下查询：

SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

分析查询计划：

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

输出可能如下所示：

QUERY PLAN
`--SCAN employees USING INDEX idx_lastname

在这种情况下，SQLite 正在执行全表扫描，然后对结果进行排序。

让我们在 department（部门）列上创建一个索引：

CREATE INDEX idx_department ON employees (department);

现在，再次分析查询计划：

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;

输出可能会更改为：

QUERY PLAN
|--SEARCH employees USING INDEX idx_department (department=?)
`--USE TEMP B-TREE FOR ORDER BY

现在，SQLite 正在使用 idx_department 索引来查找 'Sales' 部门中的员工，但它仍然需要对结果进行排序。

删除冗余索引

在这一步中，你将学习如何在 SQLite 中识别和删除冗余索引。冗余索引会增加写操作的开销，而不会给读操作带来任何好处，从而对数据库性能产生负面影响。

让我们在 department（部门）和 last_name（姓氏）列上都创建一个索引：

CREATE INDEX idx_department_lastname ON employees (department, last_name);

现在，列出 employees 表上的所有索引：

PRAGMA index_list(employees);

你应该看到类似于以下的输出：

0|idx_lastname|0|c|0
1|idx_department|0|c|0
2|idx_department_lastname|0|c|0

现在，让我们分析一个按 department 和 last_name 过滤的查询：

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';

输出可能如下所示：

QUERY PLAN
`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)

此输出表明 SQLite 正在为此查询使用 idx_department_lastname 索引。

现在，让我们分析一个仅按 department 过滤的查询：

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';

输出可能如下所示：

QUERY PLAN
`--SEARCH employees USING INDEX idx_department (department=?)

此输出表明 SQLite 正在为此查询使用 idx_department 索引。

在这种情况下，idx_department_lastname 索引是冗余的，因为 idx_department 索引可以用于仅按 department 过滤的查询。 idx_department_lastname 索引仅对按 department 和 last_name 过滤的查询提供好处。

要删除冗余的 idx_department 索引，你可以使用 DROP INDEX 命令：

DROP INDEX idx_department;

现在，再次列出 employees 表上的所有索引：

PRAGMA index_list(employees);

你应该看到不再列出 idx_department 索引。

总结

在这个实验（lab）中，你已经学习了如何使用索引来优化 SQLite 数据库的性能。你创建了单列索引（single-column index）以提高查询速度，使用 EXPLAIN QUERY PLAN 分析了查询计划，并删除了冗余索引。这些技能将帮助你构建更高效、更快速响应的 SQLite 数据库。