Bagaimanakah anda mengoptimumkan query SQL yang perlahan?

Question

Accepted Answer

Mengoptimumkan query yang perlahan bermula dengan **menganalisis pelan pelaksanaan** (`EXPLAIN`) untuk mencari bottleneck, kemudian menerapkan pembetulan yang disasarkan — paling biasa **menambah index**, menulis semula query, dan mengelakkan anti-corak yang biasa. Kemenangan terbesar biasanya ialah menukar imbasan jadual penuh kepada pencarian index.

## Langkah 1: analisis dengan EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (atau `EXPLAIN ANALYZE`) menunjukkan bagaimana pangkalan data melaksanakan query — yang paling penting, sama ada ia melakukan **imbasan jadual penuh** (perlahan) atau menggunakan **index** (pantas). Sentiasa lakukan profil sebelum mengoptimumkan.

## Kemenangan terbesar: tambah index

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

Pembetulan yang paling biasa dan berkesan: **tambah index** pada lajur yang digunakan dalam `WHERE`, `JOIN`, dan `ORDER BY`. Ini menukar imbasan jadual penuh kepada pencarian yang pantas.

## Anti-corak biasa untuk dibetulkan

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Teknik pengoptimuman lain

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Mengapa ia penting

Pengoptimuman query ialah pengetahuan kritikal untuk prestasi pangkalan data — query yang perlahan adalah punca utama prestasi aplikasi yang lemah, dan memahami cara mencari dan membetulkannya merupakan kemahiran peringkat senior yang penting bagi sesiapa yang bekerja dengan pangkalan data pada skala besar.

Disiplin asasnya ialah **menganalisis dengan `EXPLAIN`** (memeriksa pelan pelaksanaan untuk mencari bottleneck sebenar — yang paling penting sama ada query melakukan imbasan jadual penuh yang perlahan atau pencarian index yang pantas) sebelum mengoptimumkan, dan bukannya meneka — pendekatan berpandukan pengukuran inilah yang menjadikan pengoptimuman berkesan.

**Kemenangan tunggal terbesar dan paling biasa ialah menambah index** pada lajur yang ditapis, disertakan, dan diisih (menukar imbasan jadual penuh kepada pencarian yang pantas — selalunya meningkatkan masa query dengan ketara), menjadikan pengetahuan index berada di tengah-tengah pengoptimuman.

Memahami **anti-corak biasa** sama pentingnya: fungsi pada lajur dan `LIKE` dengan wildcard di hadapan menghalang penggunaan index, `SELECT *` mengambil data yang tidak perlu, dan terutamanya **query N+1** (query dalam gelung dan bukannya satu JOIN tunggal — pembunuh prestasi yang kerap dan teruk dalam kod aplikasi) — mengenali dan membetulkan ini menangani punca paling biasa query yang perlahan.

Teknik lain (index komposit, mengurangkan data yang diimbas, menulis semula subquery sebagai JOIN, mengemas kini statistik, materialized view untuk query yang mahal) melengkapkan peralatan ini.

Memandangkan prestasi query secara langsung mempengaruhi responsif dan kebolehskalaan aplikasi, dan memandangkan pendekatan sistematik (analisis dengan EXPLAIN, kemudian betulkan — biasanya dengan menambah index dan menghapuskan anti-corak seperti N+1) yang mengekalkan pangkalan data pantas di bawah beban, menguasai pengoptimuman query adalah pengetahuan senior yang penting dan bernilai tinggi yang membezakan pembangun yang boleh membina aplikasi berasaskan pangkalan data yang berprestasi dan boleh skala — kemahiran kritikal yang kerap relevan dan topik temu duga biasa untuk prestasi pangkalan data.