Database Indexing Mistakes That Are Quietly Killing Your App’s Performance

那些正在悄悄拖垮你应用性能的数据库索引错误

Indexing is one of those topics every developer has heard of, most have used, and surprisingly few have actually reasoned through carefully. It’s easy to add an index and move on — it’s much harder to know whether that index is actually helping, or just adding write overhead while your slow query is still slow for a completely different reason. Here are the indexing mistakes that show up again and again in real codebases, and what to do instead.

索引是每个开发者都听说过、大多数人都用过，但令人惊讶的是，很少有人真正深入思考过的话题。添加一个索引并置之不理很容易，但要判断这个索引是否真的有帮助，还是仅仅在增加写入开销，同时你的慢查询却因为完全不同的原因依然缓慢，这要困难得多。以下是在真实代码库中反复出现的索引错误，以及相应的改进建议。

Mistake 1: Indexing Every Column “Just In Case”

错误 1：“以防万一”地为每一列建立索引

It feels safe to add an index to any column that shows up in a WHERE clause somewhere. The problem is that every index has a cost on every write — inserts, updates, and deletes all have to update every index on that table, not just the one relevant to your read query. A table with ten indexes can turn a simple insert into ten additional write operations behind the scenes. The better approach: index based on actual query patterns, not hypothetical ones. Use your database’s query planner (EXPLAIN in Postgres and MySQL) to see what’s actually being scanned, and index those specific access patterns.

为出现在 WHERE 子句中的任何列添加索引看起来很安全。问题在于，每个索引在每次写入时都会产生开销——插入、更新和删除操作不仅要更新与读取查询相关的索引，还必须更新表上的所有索引。一张拥有十个索引的表，可能会让一次简单的插入操作在后台产生十次额外的写入。更好的做法是：基于实际的查询模式而非假设的模式来建立索引。使用数据库的查询计划器（Postgres 和 MySQL 中的 EXPLAIN）来查看实际扫描的内容，并针对这些特定的访问模式建立索引。

Mistake 2: Ignoring Column Order in Composite Indexes

错误 2：忽略复合索引中的列顺序

A composite index on (user_id, created_at) is not the same as one on (created_at, user_id). Order matters because a composite index can only be used efficiently as a left-to-right prefix. If queries always filter by user_id first and sometimes by created_at, the (user_id, created_at) order serves both cases — but a query that only filters by created_at won’t use that index efficiently at all. Before creating a composite index, write out your actual query patterns and check which columns appear together, and in what order they’re typically filtered.

(user_id, created_at) 的复合索引与 (created_at, user_id) 的索引并不相同。顺序很重要，因为复合索引只能作为从左到右的前缀高效使用。如果查询总是先按 user_id 过滤，有时再按 created_at 过滤，那么 (user_id, created_at) 的顺序可以同时满足这两种情况；但如果查询只按 created_at 过滤，则根本无法高效利用该索引。在创建复合索引之前，请写下你的实际查询模式，并检查哪些列是一起出现的，以及它们通常按什么顺序进行过滤。

Mistake 3: Not Indexing Foreign Keys

错误 3：未对外部键（Foreign Keys）建立索引

This one is deceptively common, especially in ORMs that don’t do it automatically. A foreign key relationship without a supporting index means every join, every cascading delete, and every “find all children of this parent” query does a full table scan. This is often the real root cause behind a dashboard that “gets slower over time” as a related table grows.

这种情况非常普遍，尤其是在那些不会自动处理外键索引的 ORM 中。没有配套索引的外键关系意味着每一次连接（JOIN）、每一次级联删除以及每一次“查找该父级下的所有子级”的查询，都会执行全表扫描。这往往是仪表盘随着相关表数据增长而“变得越来越慢”的真正根源。

Mistake 4: Trusting the Index Without Checking If It’s Used

错误 4：盲目信任索引而不检查其是否被使用

Adding an index doesn’t guarantee your database will actually use it. Type mismatches, wrapping an indexed column in a function call in your WHERE clause, or a leading wildcard in a LIKE query can all silently prevent an index from being used, even though it exists on the table. Running EXPLAIN ANALYZE on important queries is the only way to confirm the index you added is actually being used.

添加索引并不能保证数据库一定会使用它。类型不匹配、在 WHERE 子句中将索引列包裹在函数调用中，或者在 LIKE 查询中使用前导通配符，都可能在静默状态下导致索引失效，即使该索引在表中确实存在。对重要查询运行 EXPLAIN ANALYZE 是确认你添加的索引是否被实际使用的唯一方法。

Mistake 5: Over-Indexing for a Query That Should Be Cached Instead

错误 5：为本应缓存的查询过度建立索引

Not every performance problem is an indexing problem. If a query is expensive because it’s aggregating across millions of rows on every dashboard load, indexes will only get you so far — at some point the query should be pre-computed, cached, or served from a materialized view instead. Indexing helps databases find rows faster; it doesn’t make heavy aggregation work disappear.

并非所有性能问题都是索引问题。如果一个查询因为在每次加载仪表盘时都要聚合数百万行数据而变得昂贵，那么索引的作用是有限的——在某些时候，该查询应该被预计算、缓存，或者改用物化视图（Materialized View）来提供服务。索引能帮助数据库更快地找到行，但它无法消除繁重的聚合工作。

A Practical Way To Audit Existing Indexes

审计现有索引的实用方法

If you’ve inherited a codebase with indexes added over years by different people, a useful exercise is:

• Pull a list of all indexes and their sizes from system tables or built-in views.
• Cross-reference against actual query logs to see which indexes are used and which are dead weight.
• Remove indexes that aren’t supporting any real query pattern.
• Re-check composite index column order against your most frequent queries.
• Re-run EXPLAIN on your top 10 slowest queries and confirm indexes are actually being hit.

如果你接手了一个由不同人在多年间添加了各种索引的代码库，以下是一个有用的操作流程：

• 从系统表或内置视图中提取所有索引及其大小的列表。
• 对照实际的查询日志，查看哪些索引被使用了，哪些是冗余的。
• 删除那些没有支持任何实际查询模式的索引。
• 根据最频繁的查询重新检查复合索引的列顺序。
• 对最慢的 10 个查询重新运行 EXPLAIN，确认索引是否确实被命中。

Indexing is a genuinely small, well-understood piece of database design in theory, but it’s one of the areas where “it works” and “it works well” diverge the most in real production systems. A little query-pattern-driven discipline tends to fix performance problems that look, on the surface, like they need a much bigger infrastructure change.

从理论上讲，索引是数据库设计中一个非常小且易于理解的部分，但在实际生产系统中，它是“能用”和“好用”差距最大的领域之一。一点点基于查询模式的规范化管理，往往就能解决那些表面上看起来需要进行大规模基础设施变更的性能问题。