当应该 I 添加索引?

添加 indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Composite indexes for multi-column conditions (order matters — put equality conditions first). Avoid indexes on low-cardinality columns (boolean, gender) — they rarely help. Monitor index usage and remove unused indexes (they slow writes).

为什么是 OFFSET 坏为 pagination?

OFFSET skips rows 通过 scanning 和 discarding 它们 — page 1000 具有 LIMIT 10 scans 10,010 rows. Use keyset pagination (WHERE id > last_id ORDER 通过 id LIMIT 10) instead — 它 scans 仅 10 rows. 为复杂排序 orders, use composite cursor (排序列 + 唯一 id).

Optimize 慢 SQL queries

Identify 和 fix 慢 SQL queries using EXPLAIN 分析, 索引优化, 和查询 rewriting techniques.

1

Query 优化步骤: (1) Run EXPLAIN ANALYZE 到 see 执行计划. (2) Look 为 sequential scans 开大 tables — 添加 indexes. (3) Check 连接 types — nested loops 是慢为大 tables. (4) Reduce 工作集合具有 WHERE 之前连接. (5) Avoid SELECT * — 获取仅 needed columns. (6) Use LIMIT 为 pagination 而非 OFFSET.
2

SQL 格式化器 →
格式化 the query for readability before analysis. A well-formatted query makes it easier to spot inefficiencies: missing WHERE clauses, unnecessary subqueries, redundant joins. Format also helps when comparing the query with the EXPLAIN output.
3

sql-format → word-count
查看配方在工具链中打开

Use SQL 格式化 workflow 到格式化, 分析, 和 document 查询. 此 produces clean, readable 查询具有 consistent indentation 该是 easier 到优化.

常见问题

当应该 I 添加索引?: 添加 indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Composite indexes for multi-column conditions (order matters — put equality conditions first). Avoid indexes on low-cardinality columns (boolean, gender) — they rarely help. Monitor index usage and remove unused indexes (they slow writes).
为什么是 OFFSET 坏为 pagination?: OFFSET skips rows 通过 scanning 和 discarding 它们 — page 1000 具有 LIMIT 10 scans 10,010 rows. Use keyset pagination (WHERE id > last_id ORDER 通过 id LIMIT 10) instead — 它 scans 仅 10 rows. 为复杂排序 orders, use composite cursor (排序列 + 唯一 id).