SQL 索引入门

Q: 主键会自动创建索引吗？

会。在 MySQL、PostgreSQL 和 SQL Server 中，PRIMARY KEY 和 UNIQUE 约束都会自动创建索引。外键则不同：如果外键列上没有索引，MySQL（InnoDB）会自动创建一个，而 PostgreSQL 不会，需要你自己添加。

Q: 一张表应该有多少个索引？

没有固定的数量。每个索引都能加快与之匹配的查询，但会拖慢 INSERT、UPDATE 和 DELETE，还会占用磁盘空间。请为常用的 WHERE、JOIN 和 ORDER BY 中的列建索引，并删除不用的或重复的索引：已有 (customer_id, payment_date) 索引时，(customer_id) 上的索引就是多余的。

Q: 如何查看一张表的索引？

在 MySQL 中执行 SHOW INDEX FROM customer;。在 PostgreSQL 中，可以在 psql 里使用 \d customer，或者查询系统视图：SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customer';。

第 10.3 课 · 阅读时间：约 10 分钟 · 练习题 ↓

本课介绍 SQL 索引及其在查询性能中的作用。你将了解什么是索引、它如何帮助更快地取出数据，以及为什么它有时会拖慢数据修改操作。我们将通过 Sakila 的表演示创建和验证索引的基本示例。学完本课后，你将能够更有意识地使用索引来加速实际查询。

在上一课中，我们学习了如何编写做更少无用功的查询。合乎逻辑的下一步，是理解加速查找的核心机制：索引。

索引与 DBMS 查找行的方式直接相关。没有索引时，服务器经常要扫描整张表。有了合适的索引，它就能更快地找到相关数据。

SQL 索引入门以及索引对查询性能的影响

什么是索引

SQL 索引是一种额外的数据结构，帮助 DBMS 按列值更快地找到行。

一个简单的类比是书的索引。你不必逐页阅读，而是查看索引，直接翻到需要的章节。

在关系数据库中，最常用的是 B-tree 索引，它适用于：

精确查找（=）；
范围查找（>、<、BETWEEN）；
按带索引的列排序（ORDER BY）。

索引如何影响性能

加快读取（`SELECT`）

当 WHERE 条件使用带索引的列时，DBMS 无需扫描整张表就能定位到行。

可能拖慢写入（`INSERT`、`UPDATE`、`DELETE`）

每个索引都必须保持最新。数据变化时，DBMS 既要更新表，也要更新相关的索引。

占用额外空间

索引单独存储，会占用磁盘空间。在每一列上都建索引通常不是好策略。

基本语法

创建单列索引：

CREATE INDEX idx_customer_last_name
ON customer (last_name);

删除索引（语法因 DBMS 而异）：

DROP INDEX idx_customer_last_name ON customer;

说明：在 PostgreSQL 中，写法是 DROP INDEX index_name;，不带表名。

示例 1：加速单列过滤

假设我们经常按姓氏查找客户：

SELECT
   customer_id,
   first_name,
   last_name
FROM customer
WHERE last_name = 'SMITH';

如果 last_name 上没有索引，DBMS 可能会对 customer 做全表扫描。创建索引后，访问方式通常会变成更高效的类型。

用 EXPLAIN 验证：把它放在查询前面，数据库就会显示执行计划而不是结果，其中包括它使用的索引：

EXPLAIN
SELECT
   customer_id,
   first_name,
   last_name
FROM customer
WHERE last_name = 'SMITH';

结果：在执行计划中，你应该能看到使用了索引（MySQL 中的 key/possible_keys 或 PostgreSQL 中的 Index Scan）。

示例 2：复合索引

如果查询经常同时按两个字段过滤，复合索引会很有用。

CREATE INDEX idx_payment_customer_date
ON payment (customer_id, payment_date);

与这个索引很匹配的查询：

SELECT
   payment_id,
   customer_id,
   amount,
   payment_date
FROM payment
WHERE customer_id = 15
  AND payment_date >= '2005-07-01'
ORDER BY payment_date;

说明：复合索引中列的顺序很重要。很多情况下，应把最常用于过滤的字段放在前面。

什么时候索引可能不会被使用

即使索引存在，优化器也可能忽略它。常见原因：

在 WHERE 中对带索引的列使用了函数（YEAR(payment_date)）；
以 % 开头的模式匹配（LIKE '%abc'）；
列的选择性很低；
复合索引中列的顺序不合适。

一个经常妨碍使用索引的条件示例：

SELECT
   payment_id,
   payment_date
FROM payment
WHERE YEAR(payment_date) = 2005;

对索引更友好的写法：

SELECT
   payment_id,
   payment_date
FROM payment
WHERE payment_date >= '2005-01-01'
  AND payment_date < '2006-01-01';

实用建议

为真实存在的高频查询添加索引，而不是“以防万一”。
从 WHERE、JOIN 和 ORDER BY 中使用的列开始。
添加索引后，用 EXPLAIN 比较执行计划。
保持平衡：索引太多会降低写入性能。

本课要点：

索引是加快行查找的数据结构。
索引通常能提升 SELECT 的性能，但可能拖慢 INSERT、UPDATE 和 DELETE。
单列索引和复合索引适用于不同的过滤模式。
复合索引中列的顺序至关重要。
EXPLAIN 可以帮你验证 DBMS 是否使用了你的索引。

常见问题

主键会自动创建索引吗？

会。在 MySQL、PostgreSQL 和 SQL Server 中，PRIMARY KEY 和 UNIQUE 约束都会自动创建索引。外键则不同：如果外键列上没有索引，MySQL（InnoDB）会自动创建一个，而 PostgreSQL 不会，需要你自己添加。

一张表应该有多少个索引？

没有固定的数量。每个索引都能加快与之匹配的查询，但会拖慢 INSERT、UPDATE 和 DELETE，还会占用磁盘空间。请为常用的 WHERE、JOIN 和 ORDER BY 中的列建索引，并删除不用的或重复的索引：已有 (customer_id, payment_date) 索引时，(customer_id) 上的索引就是多余的。

如何查看一张表的索引？

在 MySQL 中执行 SHOW INDEX FROM customer;。在 PostgreSQL 中，可以在 psql 里使用 \d customer，或者查询系统视图：SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customer';。

面试问题

什么是 SQL 索引，它有什么用？

索引是一种额外的数据结构，可以按列值加快行的查找。它的用处在于减少读取时间和需要扫描的数据量。

为什么索引能加快 `SELECT`，却会拖慢 `INSERT`？

读取时，索引帮助更快地定位行。写入时，DBMS 既要更新表数据，又要更新索引结构，因此增加了工作量。

如何确认索引确实被使用了？

对查询运行 EXPLAIN 并分析执行计划：访问类型、选中的索引以及预计行数。