SQL 索引 — 为什么你的查询会变慢以及你应该真正采取的措施

你的查询在本地主机上耗时2毫秒，处理500行数据。而在生产环境中处理300万行数据时，耗时9秒，用户只能看到加载动画。查询本身可能没问题，问题出在表结构上。你需要添加索引，并且需要理解这一点。 为什么 因此，当你遇到类似情况时，不要再猜测下一步该怎么做。

数据库在没有索引的情况下在做什么

B树索引是列（或列组合）的有序副本，存储在平衡树中，数据库可以在O(log n)时间内进行二分查找。没有索引的情况下，数据库会进行全表扫描。 全表扫描 —— 它需要读取每一行来查找匹配项。对于300万行数据，这意味着300万次读取。如果在正确的列上添加索引，可能只需要20到30次树节点遍历就能找到匹配行。

你可以把它想象成电话簿。按字母顺序查找“Smith, John”需要几秒钟。而要查找所有名为“John”的人，必须从第一页开始逐条阅读，这将耗时无穷。索引就是这种字母排序——数据库利用它直接跳转到相关行，而不是扫描所有内容。

有一点非常重要：索引与表一样，存储在磁盘上。数据库会自动在写入操作时维护这些索引。读取操作是免费的，但在每次INSERT、UPDATE和DELETE操作时都会产生开销——这就是为什么你不能对所有列都添加索引。

使用EXPLAIN定位问题

在做任何修改之前，请运行 EXPLAIN 在慢查询上。在MySQL中，你需要关注以下两个字段：

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 2847391 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

两个关键字段：

• type: ALL —— 全表扫描。一旦表行数超过约10万行，这几乎总是个坏主意。
• rows: 2847391 —— 优化器估计需要检查284万行才能返回结果。

现在添加索引并再次运行查询：

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

type: ref 且 rows: 12。这就是解决方案。从检查284万行减少到仅12行。

在PostgreSQL中，使用 EXPLAIN ANALYZE （实际上执行查询）来获取真实的执行时间：

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

8934毫秒 → 0.3毫秒。这不是一个刻意挑选的例子——这是在拥有几百万行数据的表上，一个缺失索引的真实影响。

复合索引和最左前缀规则

当你在WHERE子句中使用多个列时，你需要复合索引，并且列的顺序很重要。

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

这 最左前缀规则：数据库从最左边的列开始使用复合索引，并向右逐列进行，但一旦遇到空隙就不再有用。给定一个在 (a, b, c):

• WHERE a = 1 上的索引：
• WHERE a = 1 AND b = 2 —— 使用索引
• WHERE a = 1 AND b = 2 AND c = 3 —— 使用索引中的两个列
• WHERE b = 2 —— 使用完整的索引
• WHERE a = 1 AND c = 3 —— 不使用索引（跳过列a）

—— 仅使用a部分，忽略c 实际排序规则：等值条件优先，范围条件最后。>, <, BETWEEN, LIKE 'prefix%'范围条件（

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

）会破坏后续列的可用前缀。因此： customer_id 同时还要考虑选择性：将最具选择性的列（重复值最少）放在索引的前面。如果 status 有5万个不同值，而 customer_id 只有3个，那么

应该放在前面——它能更快地缩小结果集。

索引导致性能变差的情况

• 索引并非免费。每个索引都会带来写入性能和磁盘空间的开销。数据库会在每次INSERT、UPDATE和DELETE操作时更新所有相关索引。对于写入密集型的表，过多的索引会真正影响吞吐量。索引会失败或适得其反的情况如下： 低基数列。 status 在布尔值或仅有3个不同值的列上创建索引，通常会被优化器忽略。如果查询通过索引返回的行数超过约10到15%，数据库通常会更倾向于全表扫描——索引查找的开销不值得。
• 写入密集型表。 日志表、事件表、审计表——要谨慎。一个每小时有数百万次写入的表，其10个索引会带来10倍的写入开销。
• 未使用的索引。 在PostgreSQL中， pg_stat_user_indexes 显示 idx_scan —— 自上次统计重置以来每个索引被使用的次数。一个零扫描索引是纯粹的开销。MySQL通过性能模式（sys.schema_unused_indexes 在MySQL 5.7+中）跟踪这一信息。
• 优化器预测错误。 查询规划器并不总是正确。有时它们会选择比全表扫描更慢的索引。 EXPLAIN ANALYZE 将捕获这种情况——比较估算与实际行数。在MySQL中，你可以使用 USE INDEX (idx_name)提示；在PostgreSQL中，你可以使用 SET enable_seqscan = off （不要在生产环境中留下这个）。

是索引还是重写查询？真正的权衡

有时索引是解决方案。有时查询本身存在问题，而索引只是掩盖了问题。以下是一些应该重写查询而不是（或除了）添加索引的情况：

在索引列上使用函数

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

将列包装在任何函数中都会阻止标准索引的使用。索引存储的是 created_at的值，而不是 YEAR(created_at) 的值——这两者是不同的。PostgreSQL支持表达式索引（CREATE INDEX ON orders (YEAR(created_at))）作为替代方案，但查询重写更简洁，且适用于所有数据库。

为热点读路径创建覆盖索引

A 覆盖索引 包含查询所需的所有列，因此数据库无需再读取实际表行——它完全从索引中获取答案。对于高读取量的表，这可能比普通索引快得多：

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

LIKE模式

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

前缀通配符无法通过B树索引解决。如果你需要后缀或子串搜索，你需要全文索引（FULLTEXT 在MySQL中， GIN 是快速路径，但它对属性处理不一致，且在边缘情况中可能丢失数据。在处理SOAP响应的生产环境中，建议使用 pg_trgm 在PostgreSQL中）或外部搜索系统如Elasticsearch。

N+1查询

索引无法解决N+1查询。如果你的ORM一次查询获取200个订单，然后为每个订单单独查询客户姓名，你将有201个查询。每个查询可能很快，但问题在于查询数量。解决方法是使用JOIN或批量查询——无论索引多好，都无法改变查询数量。

还有一点：在分析查询前先清理查询

在对慢查询运行EXPLAIN之前，清晰格式化的SQL更易于理解。一个200字符的单行查询没有缩进，比结构清晰的查询更难理解。工具 SQL Formatter at IO Tools 支持MySQL、PostgreSQL、SQL Server等方言——当你从日志或ORM调试输出中复制查询时，这个工具能让你快速看到查询结构。

实用检查清单

• 运行 EXPLAIN 在添加任何索引之前。首先确认问题所在。
• 查找 type: ALL (MySQL) 或 Seq Scan (PostgreSQL) —— 通常就是问题所在。
• 检查EXPLAIN中的估算值。扫描数百万行以返回几十行 = 缺失索引。 rows 在复合索引中：等值条件优先，范围条件最后。
• 选择性更高的列应放在复合索引的前面。
• 不要在未验证优化器实际使用索引的情况下为低基数列添加索引。
• 定期审计未使用的索引：
• (PostgreSQL)， pg_stat_user_indexes (MySQL)。 sys.schema_unused_indexes 如果WHERE子句中使用了函数包装列，应重写查询而不是索引该函数。
• 对于读取密集型的热点路径且列数量较少的情况，考虑使用覆盖索引。
• N+1是查询架构问题，而不是索引问题。
• SQL索引——为什么你的查询变慢以及你应该真正做什么 2

你可能也会喜欢