课程 10.5 · 阅读时间：~10 分钟
本课程介绍 SQL 索引及其在查询性能中的作用。您将学习索引是什么，它如何帮助更快地检索数据，以及为什么有时它会减慢数据修改操作。我们将通过使用 Sakila 表创建和验证索引的基本示例进行讲解。到本课程结束时，您将能够更有意识地使用索引来加速实际查询。
介绍 SQL 索引
在上一课中，我们学习了如何使用 EXPLAIN 阅读执行计划并检测瓶颈。下一个逻辑步骤是理解用于加速查找的核心机制：索引。
索引与 DBMS 搜索行的方式直接相关。如果没有索引，服务器通常会扫描整个表。使用合适的索引，它可以更快地跳转到相关数据。
索引是什么
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 是否使用您的索引。
面试问题
什么是 SQL 索引，它有什么用？
索引是一个额外的数据结构，通过列值加速行查找。它有用，因为它减少了读取时间和扫描的数据量。
为什么索引可以加速 SELECT 但减慢 INSERT？
对于读取，索引有助于更快地定位行。对于写入，DBMS 必须同时更新表数据和索引结构，这增加了工作量。
如何验证索引是否实际被使用？
运行 EXPLAIN 以查看查询并检查执行计划：访问类型、选择的索引和估计的行数。
在下一课中，我们将介绍日常工作中使用的错误处理和 SQL 调试技术。