sql-create-index

SQL 索引：加速您的查询

数据库中的索引是一种数据结构，可提高表中数据检索操作的速度。把它想象成书后的索引：您无需扫描每一页来查找某个主题，而是可以在索引中查找并直接跳转到正确的页面。同样，数据库索引允许查询引擎快速找到符合您条件的行，而无需扫描整个表。

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

虽然索引显著加快 SELECT 查询的速度，但它们是有代价的：

更慢的写入操作： 每次您 INSERT、UPDATE 或 DELETE 一行时，数据库不仅要修改表数据，还要更新包含该行的每个索引。这增加了开销。
存储空间： 索引是磁盘上的独立对象，会占用存储空间。

有效数据库性能的关键是战略性索引：在正确的列上创建索引，以优化常见查询，同时避免不必要的写入操作减速。

创建索引

CREATE INDEX 语句用于在表的一个或多个列上创建新索引。

语法

CREATE INDEX index_name ON table_name (column_name1, column_name2, ...);

示例：为外键添加索引

让我们考虑 customers 和 orders 表。在 customer_id 上连接这些表的查询非常常见。在 orders 表的 customer_id 列上创建索引是一种经典的优化。

CREATE TABLE customers (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2),
    customer_id INT REFERENCES customers(id)
);

-- 在外键列上创建索引以加快连接速度
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

专业提示： 大多数数据库系统会自动在 PRIMARY KEY 和 UNIQUE 约束上创建索引。通常手动在外键列上创建索引会很有益处。

索引类型：简要概述

单列索引： 如上所示的基本索引，创建在单个列上。
复合（多列）索引： 在两个或更多列上创建的索引。复合索引中列的顺序至关重要。在 (last_name, first_name) 上的索引可以高效地服务于按 last_name 筛选或按 last_name AND first_name 筛选的查询，但对于仅按 first_name 筛选的查询则帮助不大。
唯一索引： 强制索引列中的所有值必须是唯一的。这既是性能增强，也是数据完整性规则。
其他类型： 现代数据库提供专门的索引，例如全文（用于文本搜索）、GiST/SP-GiST（用于 PostgreSQL 中的几何或复杂数据类型）和哈希索引（用于精确相等检查）。

示例：复合索引

如果您经常根据客户的城市和状态进行搜索，复合索引将是有效的。

CREATE INDEX idx_customers_city_status ON customers (city, status);

查看和管理索引

列出表上索引的命令因数据库系统而异。

MySQL： SHOW INDEX FROM table_name;
PostgreSQL： 在 psql 命令行工具中，使用 \d table_name。
SQL Server： EXEC sp_helpindex 'table_name';

要删除不再需要或损害性能的索引，请使用 DROP INDEX 语句。

-- 语法通常在各系统之间是标准的
DROP INDEX index_name;

最佳实践

为筛选的列创建索引： 在频繁出现在 WHERE 子句和 JOIN 条件中的列上创建索引。
不要过度索引： 避免在每个列上都创建索引。每个索引都会增加写入操作的开销。
使用 EXPLAIN： EXPLAIN（或 EXPLAIN ANALYZE）命令是您最好的朋友。它显示了数据库将使用的查询计划，让您能够查看索引是否被有效使用。
考虑覆盖索引： 如果查询的 SELECT、WHERE 和 JOIN 子句都使用了属于单个索引的列，数据库就可以仅使用索引来回答查询，这会非常快。这被称为覆盖索引。