SQL/NoSQL query optimization pattern, execution plan analysis, index strategy, N+1 resolution etc. database performance optimization guide. 'query optimization', 'execution plan', 'EXPLAIN', 'index ', 'N+1 ', 'slow query', 'slow query', 'DB performance' etc. database query performance improvement this for. bottleneck-analystand optimization-engineerof DB performance analysis -ize. , before system profilingthis benchmark execution this of scope .
database query performance systematicas analysisand optimizationlower methodology.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2024-01-01'
ORDER BY o.total_amount DESC
LIMIT 10;
| metric | of | risk |
|---|---|---|
| Seq Scan | before table | large tablefrom |
| Nested Loop | join | external tablethis |
| Hash Join | hash join | work_mem exceeding disk for |
| Sort | sort | memory exceeding external sort |
| Bitmap Heap Scan | index → table | lossy map performance lower |
| actual time | actual between | vs before this |
| rows | estimated vs actual this | 10 or more this → statistics renewal |
❌ Seq Scan on large_table (rows=10000000)
→ index addition necessary
❌ Sort Method: external merge (Disk: 256MB)
→ work_mem or index sort
❌ Nested Loop (actual rows=1000000)
→ Hash Join or Merge Joinas transition
❌ estimated=100 actual=100000
→ ANALYZE executionto statistics renewal
| index type | suitable | unsuitable |
|---|---|---|
| B-Tree (default) | etc., scope, sort | , JSON, before search |
| Hash | etc. only | scope query |
| GIN | , JSONB, before search | simple etc./scope |
| GiST | between, scope type | simple |
| BRIN | -basedas sortthe data | distribution |
-- rule (Leftmost Prefix)
CREATE INDEX idx_orders ON orders(status, created_at, customer_id);
-- this index lower query:
✅ WHERE status = 'PAID'
✅ WHERE status = 'PAID' AND created_at > '2024-01-01'
✅ WHERE status = 'PAID' AND created_at > '2024-01-01' AND customer_id = 123
❌ WHERE created_at > '2024-01-01' (status )
❌ WHERE customer_id = 123 (status, created_at )
-- column decision criteria:
-- 1. etc. cases column (optionalalso high )
-- 2. scope cases column
-- 3. ORDER BY column
-- table this indexonlyas query completed
CREATE INDEX idx_covering ON orders(status, created_at) INCLUDE (total_amount);
SELECT total_amount FROM orders
WHERE status = 'PAID' AND created_at > '2024-01-01';
-- Index Only Scan → heap necessary
# N+1 pattern (!)
orders = Order.objects.filter(status="PAID") # query 1
for order in orders:
print(order.customer.name) # query N (order countonly)
# query: 1 + N
# Eager Loadingas resolution
orders = Order.objects.filter(status="PAID").select_related("customer") # query 1 (JOIN)
# or
orders = Order.objects.filter(status="PAID").prefetch_related("items") # query 2 (IN)
| ORM | N+1 resolution | |
|---|---|---|
| Django | select_related / prefetch_related | FK JOIN / Reverse IN |
| SQLAlchemy | joinedload / subqueryload | JOIN / query |
| TypeORM | relations / @JoinColumn | eager/lazy configuration |
| Prisma | include | automatic |
| JPA | @EntityGraph / JOIN FETCH | JPQL/Criteria |
| SQL | performance | suitable | |
|---|---|---|---|
| OFFSET | LIMIT 20 OFFSET 10000 | O(N) — | , seconds this |
| Keyset | WHERE id > 1000 LIMIT 20 | O(1) — | , |
| Cursor | encryptionthe keyset | O(1) | API, clientfor |
-- OFFSET (10000from → 10000 after )
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
-- Keyset (immediate corresponding locationas)
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;
| pattern | resolution | |
|---|---|---|
SELECT * | necessary column before | necessary columnonly people |
WHERE func(column) | index for impossible | transformation constant as this |
LIKE '%keyword%' | before search index(GIN) | |
| query IN () | slow execution | JOINas transition |
| -based type transformation | index invalid-ize | type day |
Spring Boot中的JPA/Hibernate模式，用于实体设计、关系处理、查询优化、事务管理、审计、索引、分页和连接池。