sql-indexes

SQL - 数据库索引 (Database Indexes)

数据库索引是特殊的数据结构，用于显著加快数据库表上的数据检索操作。虽然它们对用户不可见，但了解如何创建和管理它们是提高数据库性能最重要的技能之一。

经典的类比是书后的索引。与其阅读整本书来查找某个主题（即“全表扫描”），不如在索引中查找该主题，索引会直接指向正确的页码（数据在磁盘上的位置）。

权衡：更快的读取，更慢的写入

索引并非没有代价。它们伴随着成本：

• 更快的 SELECT： 对索引列进行 WHERE、JOIN 和 ORDER BY 子句的查询会显著加快。
• 更慢的写入（INSERT、UPDATE、DELETE）： 当你修改索引列中的数据时，数据库不仅要更新表，还要更新索引结构。这增加了开销。
• 存储空间： 索引会占用磁盘空间。

关键是在经常用于搜索、筛选和排序的列上策略性地创建索引。

创建和删除索引

CREATE INDEX 语句

创建基本索引的语法很简单。

CREATE INDEX index_name ON table_name (column1, column2, ...);

DROP INDEX 语句

删除未使用的或无效的索引对于性能同样重要。

-- PostgreSQL 语法
DROP INDEX index_name;

-- MySQL 语法
DROP INDEX index_name ON table_name;

常见索引类型

1. 单列索引 (Single-Column Index)

最基本的类型，创建在单个列上。非常适合在 WHERE 子句中频繁使用的列。

CREATE INDEX idx_users_email ON Users (Email);

2. 复合（多列）索引 (Composite (Multi-Column) Index)

在两个或更多列上创建的索引。对于按多个列进行筛选的查询非常强大。列的顺序很重要！在 (LastName, FirstName) 上的索引对于根据 LastName 或同时根据 LastName 和 FirstName 进行筛选的查询最为有效。

CREATE INDEX idx_employees_dept_salary ON Employees (Department, Salary);

3. 唯一索引 (Unique Index)

确保索引列中的所有值都是唯一的。它既用于性能，也用于数据完整性。PRIMARY KEY 和 UNIQUE 约束在后台自动创建唯一索引。

CREATE UNIQUE INDEX uq_products_sku ON Products (SKU);

4. 隐式索引 (Implicit Indexes)

如前所述，当你定义 PRIMARY KEY 或 UNIQUE 约束时，这些索引会由数据库自动创建。你无需手动创建它们。

如何验证索引是否正在被使用

创建索引并不能保证数据库会使用它。数据库的查询优化器 (query planner) 会根据成本决定执行查询的最佳方式。要检查它的决定，你必须使用 EXPLAIN（或 EXPLAIN ANALYZE）命令。

EXPLAIN SELECT * FROM Users WHERE Email = 'example@test.com';

输出将显示查询计划 (query plan)。查找 ‘Index Scan’（索引扫描）或 ‘Index Seek’（索引查找）等术语，它们表明你的索引正在被使用。如果你看到 ‘Sequential Scan’（顺序扫描）或 ‘Table Scan’（表扫描），则表示数据库正在读取整个表，你的索引可能对该特定查询无效。

最佳实践：何时使用和避免索引

索引的良好候选者

• 外键列： 始终索引你的外键以加快联接速度。
• WHERE 子句列： 任何频繁用于筛选数据的列。
• ORDER BY 列： 索引 ORDER BY 中使用的列可以防止代价高昂的排序操作。
• 基数高（许多唯一值）的列。

何时避免或谨慎使用

• 小型表： 在非常小的表上，索引的开销不值得；全表扫描已经足够快了。
• 频繁进行大型批处理操作的表： 如果你正在批量加载或更新数百万行，删除索引，执行操作，然后重新创建它们可能会更快。
• 低基数列： 布尔型 IsActive 列上的索引通常没有帮助，因为优化器可能会认为表扫描比使用索引更便宜。
• 频繁修改的列： 频繁更新的列可能会导致显著的索引维护开销。