{"position":0,"title":"介绍","layout":"doc-fullscreen","text":"## 介绍nn在这个实验中，你将学习如何通过创建索引来优化 SQLite 数据库性能。你会实践创建单列索引来提升查询速度，重点了解索引在实际业务场景中的应用方式与性能分析方法。除此之外，你还将学习如何查看查询计划，以及如何识别并删除冗余索引。n","need_verify":false,"has_solution":false},{"position":1,"title":"创建数据库和表","layout":"doc-workbench-split","text":"## 创建数据库和表nn在这一步中，你将创建一个 SQLite 数据库和一个 `employees`（员工）表，然后向表中插入一些示例数据，用于后续的索引优化与查询性能测试。nn首先，在 LabEx 虚拟机（VM）中打开终端。默认工作路径为 `/home/labex/project`。nn要创建一个名为 `my_database.db` 的 SQLite 数据库，请运行以下命令：nn```bashnsqlite3 my_database.dbn```nn这条命令会在当前项目目录中创建一个名为 `my_database.db` 的 SQLite 数据库文件，并自动进入 SQLite shell。nn接下来，创建结构如下的 `employees` 表：nn```sqlnCREATE TABLE employees (n id INTEGER PRIMARY KEY,n first_name TEXT,n last_name TEXT,n email TEXT,n department TEXTn);n```nn该 SQL 语句会创建一个名为 `employees` 的数据表，其中包含五个字段：`id`、`first_name`（名）、`last_name`（姓）、`email`（电子邮件）和 `department`（部门）。其中，`id` 被设置为主键（primary key），表示这一列中的值必须唯一。nn现在，将一些示例数据插入到 `employees` 表中：nn```sqlnINSERT INTO employees (first_name, last_name, email, department) VALUESn('John', 'Doe', 'john.doe@example.com', 'Sales'),n('Jane', 'Smith', 'jane.smith@example.com', 'Marketing'),n('Robert', 'Jones', 'robert.jones@example.com', 'Engineering'),n('Emily', 'Brown', 'emily.brown@example.com', 'Sales'),n('Michael', 'Da vis', 'michael.da vis@example.com', 'Marketing');n```nn这条语句会向 `employees` 表中插入五条记录。nn要验证数据是否已成功写入，请执行以下命令：nn```sqlnSELECT * FROM employees;n```nn你应该看到如下输出：nn```n1|John|Doe|john.doe@example.com|Salesn2|Jane|Smith|jane.smith@example.com|Marketingn3|Robert|Jones|robert.jones@example.com|Engineeringn4|Emily|Brown|emily.brown@example.com|Salesn5|Michael|Da vis|michael.da vis@example.com|Marketingn```n","need_verify":true,"has_solution":false},{"position":2,"title":"创建索引","layout":"doc-workbench-split","text":"## 创建索引nn在这一步中，你将在 `employees` 表的 `last_name`（姓）列上创建一个索引，以提升按姓氏查询员工信息时的检索效率。nn索引可以理解为数据库内部使用的一种特殊查找结构，SQLite 查询引擎能够借助它更快地定位数据，从而提高 SQL 查询速度。nn要创建一个名为 `idx_lastname`、作用于 `last_name` 列的索引，请运行以下命令：nn```sqlnCREATE INDEX idx_lastname ON employees (last_name);n```nn这条 SQL 语句会在 `employees` 表的 `last_name` 列上创建一个名为 `idx_lastname` 的索引。nn要验证索引是否已经成功创建，可以执行以下命令：nn```sqlnPRAGMA index_list(employees);n```nn该命令会显示 `employees` 表上的索引列表，其中应包含你刚刚创建的 `idx_lastname` 索引。你应该看到类似下面的输出：nn```n0|idx_lastname|0|c|0n```nn这说明 `idx_lastname` 索引已经存在于 `employees` 表中。n","need_verify":true,"has_solution":false},{"position":3,"title":"使用 EXPLAIN QUERY PLAN 分析查询","layout":"doc-workbench-split","text":"## 使用 EXPLAIN QUERY PLAN 分析查询nn在这一步中，你将学习如何使用 `EXPLAIN QUERY PLAN` 命令来分析 SQLite 是如何执行查询语句的。这是一个非常实用的工具，能够帮助你理解 SQL 查询性能，并发现潜在的性能瓶颈。nn要分析某条查询语句，只需要在它前面加上 `EXPLAIN QUERY PLAN`。例如，要分析下面这条查询：nn```sqlnSELECT * FROM employees WHERE last_name = 'Smith';n```nn请运行以下命令：nn```sqlnEXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';n```nn输出结果将类似于：nn```nQUERY PLANn`--SEARCH employees USING INDEX idx_lastname (last_name=?)n```nn这段输出表示 SQLite 正在使用 `idx_lastname` 索引来查找姓氏为 'Smith' 的员工记录。`SEARCH` 关键字说明 SQLite 使用了索引执行查找操作。nn如果查询没有使用索引，输出就会不同。例如，当你查询名字为 'John' 的员工时，由于 `first_name`（名）列上还没有创建索引，执行计划可能如下：nn```sqlnEXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';n```nn输出将如下所示：nn```nQUERY PLANn`--SCAN employeesn```nn`SCAN` 关键字表示 SQLite 正在执行全表扫描（full table scan），也就是需要检查表中的每一行数据，才能找到名字为 'John' 的员工。相比索引查询，这种方式通常效率更低。n","need_verify":true,"has_solution":fal接下来继续往 `employees` 表里补充一些数据，这样再查看查询计划时，分析结果会更具有参考意义。可以先插入下面这批记录：nn```sqlnINSERT INTO employees (first_name, last_name, email, department) VALUESn('Alice', 'Johnson', 'alice.johnson@example.com', 'HR'),n('Bob', 'Williams', 'bob.williams@example.com', 'Finance'),n('Charlie', 'Brown', 'charlie.brown@example.com', 'IT'),n('Da vid', 'Miller', 'da vid.miller@example.com', 'Sales'),n('Eve', 'Wilson', 'eve.wilson@example.com', 'Marketing'),n('John', 'Taylor', 'john.taylor@example.com', 'Engineering'),n('Jane', 'Anderson', 'jane.anderson@example.com', 'HR'),n('Robert', 'Thomas', 'robert.thomas@example.com', 'Finance'),n('Emily', 'Jackson', 'emily.jackson@example.com', 'IT'),n('Michael', 'White', 'michael.white@example.com', 'Sales');n```nn现在，让我们分析一条包含排序操作的更复杂查询。假设你要查找 `Sales`（销售）部门中的所有员工，并按照姓氏（`last_name`）进行排序，可以使用以下 SQL：nn```sqlnSELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;n```nn分析它的查询计划：nn```sqlnEXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;n```nn输出可能如下所示：nn```nQUERY PLANn`--SCAN employees USING INDEX idx_lastnamen```nn在这种情况下，SQLite 可能会扫描相关数据，并借助索引完成排序处理。nn接下来，让我们在 `department`（部门）列上创建一个索引：nn```sqlnCREATE INDEX idx_department ON employees (department);n```nn现在再次分析相同的查询计划：nn```sqlnEXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;n```nn输出可能会变成：nn```nQUERY PLANn|--SEARCH employees USING INDEX idx_department (department=?)n`--USE TEMP B-TREE FOR ORDER BYn```nn这说明 SQLite 现在会使用 `idx_department` 索引先筛选出 `Sales` 部门中的员工，但在结果排序时，仍然需要额外的排序步骤。n","need_verify":true,"has_solution":false},{"position":5,"title":"删除冗余索引","layout":"doc-workbench-split","text":"## 删除冗余索引nn在这一步中，你将学习如何在 SQLite 中识别并删除冗余索引。冗余索引会增加插入、更新和删除操作的维护成本，却不一定为查询带来额外收益，因此可能对数据库整体性能产生负面影响。nn现在，让我们在 `department`（部门）和 `last_name`（姓氏）列上创建一个联合索引：nn```sqlnCREATE INDEX idx_department_lastname ON employees (department, last_name);n```nn现在，列出 `employees` 表上的全部索引：nn```sqlnPRAGMA index_list(employees);n```nn你应该看到类似下面的输出：nn```n0|idx_lastname|0|c|0n1|idx_department|0|c|0n2|idx_department_lastname|0|c|0n```nn接下来，分析一个同时按 `department` 和 `last_name` 过滤的查询：nn```sqlnEXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';n```nn输出可能如下所示：nn```nQUERY PLANn`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)n```nn这段输出表明 SQLite 正在为这条查询使用 `idx_department_lastname` 联合索引。nn接着，再来看一个只按 `department` 条件过滤的查询：nn```sqlnEXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';n```nn可能会得到类似这样的输出：nn```sqlnQUERY PLANn`--SEARCH employees USING INDEX idx_department (department=?)n```nn这说明 SQLite 在执行这条查询时使用的是 `idx_department` 索引。nn放在这个场景下看，`idx_department_lastname` 并不完全等同于 `idx_department`，因为只按 `department` 过滤时，当前查询计划中真正使用的是 `idx_department`；而 `idx_department_lastname` 的主要价值，在于支持同时按 `department` 和 `last_name` 条件过滤的查询。nn如果你决定删除 `idx_department` 这个冗余索引，可以使用 `DROP INDEX` 命令：nn```sqlnDROP INDEX idx_department;n```nn然后再次查看 `employees` 表上的所有索引：nn```sqlnPRAGMA index_list(employees);n```nn这时你应该就看不到 `idx_department` 了。能。你已经创建了单列索引（single-column index）来提升查询速度，使用 `EXPLAIN QUERY PLAN` 分析了 SQL 查询计划，并学习了如何删除冗余索引。这些 SQLite 索引优化技能将帮助你构建性能更高、响应更快的数据库应用。n","need_verify":false,"has_solution":false}