Indeks SQL — Mengapa Kueri Anda Lambat dan Apa yang Sebenarnya Harus Anda Lakukan Terhadap Ini

Pertanyaan Anda membutuhkan 2ms di localhost dengan 500 baris. Di produksi dengan 3 juta baris, waktu eksekusi mencapai 9 detik dan pengguna Anda terus-menerus melihat spinner. Query itu mungkin sudah baik — yang salah adalah skema. Anda membutuhkan indeks, dan Anda harus memahami mengapa jadi Anda berhenti menebak saat kejadian ini terjadi lagi.

Apa yang dilakukan database tanpa indeks

Indeks B-tree adalah salinan terurut dari kolom (atau beberapa kolom), disimpan dalam pohon seimbang yang memungkinkan pencarian biner dalam waktu O(log n). Tanpa indeks, database melakukan pemindaian tabel penuh — ia membaca setiap baris untuk menemukan hasil yang sesuai. Dengan 3 juta baris, itu berarti 3 juta pembacaan baris. Dengan indeks pada kolom yang tepat, hanya 20 hingga 30 perjalanan node pohon untuk menemukan baris yang sesuai.

Bayangkan seperti buku telepon. Menemukan "Smith, John" secara alfabetis membutuhkan beberapa detik. Menemukan semua orang bernama John dengan membaca setiap entri dari halaman pertama membutuhkan waktu yang sangat lama. Indeks adalah pengurutan alfabetis — database menggunakan ini untuk langsung melompat ke baris yang relevan, bukan memindai semua data.

Hal penting: indeks tersimpan di disk bersama dengan tabel Anda. Database mempertahankan indeks secara otomatis saat terjadi penulisan. Ini gratis untuk pembacaan tetapi menghasilkan biaya pada setiap INSERT, UPDATE, dan DELETE — itulah sebabnya Anda tidak bisa hanya mengindeks semua hal.

Mendeteksi masalah dengan EXPLAIN

Sebelum melakukan apa pun, jalankan EXPLAIN pada query lambat. Di MySQL, ini yang Anda cari:

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 |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Dua bidang yang penting:

• type: ALL — pemindaian tabel penuh. Hampir selalu buruk setelah jumlah baris di tabel melebihi sekitar 100k.
• rows: 2847391 — optimizer memperkirakan akan memeriksa 2,8 juta baris untuk menghasilkan hasil Anda.

Sekarang tambahkan indeks dan jalankan lagi:

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 dan rows: 12. Itu adalah solusi. Dari 2,8 juta baris diperiksa menjadi 12.

Di PostgreSQL, gunakan EXPLAIN ANALYZE (yang sebenarnya menjalankan query) untuk mendapatkan angka waktu nyata:

-- 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

8934ms → 0.3ms. Ini bukan contoh yang dipilih secara sembarangan — ini adalah dampak nyata dari satu indeks yang hilang pada tabel dengan beberapa juta baris.

Indeks komposit dan aturan prefix kiri

Setelah Anda memiliki beberapa kolom dalam klausa WHERE, Anda membutuhkan indeks komposit — dan urutan kolom sangat penting.

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

Itu aturan prefix kiri: database menggunakan indeks komposit dimulai dari kolom paling kiri dan bergerak ke kanan, tetapi berhenti berguna saat menemukan celah. Diberikan indeks pada (a, b, c):

• WHERE a = 1 — menggunakan indeks
• WHERE a = 1 AND b = 2 — menggunakan kedua kolom dari indeks
• WHERE a = 1 AND b = 2 AND c = 3 — menggunakan seluruh indeks
• WHERE b = 2 — tidak menggunakan indeks (kolom a dilewatkan)
• WHERE a = 1 AND c = 3 — hanya menggunakan bagian a, mengabaikan c

Aturan urutan praktis: syarat kesamaan dulu, syarat rentang terakhir. Syarat rentang (>, <, BETWEEN, LIKE 'prefix%') menghancurkan prefix yang dapat digunakan untuk kolom berikutnya. Jadi:

-- 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

Juga pikirkan tentang selektivitas: letakkan kolom yang paling selektif (jumlah nilai duplikat paling sedikit) di awal indeks. Jika customer_id memiliki 50.000 nilai unik dan status memiliki 3, customer_id harus datang lebih awal — ini mempercepat pengecualian hasil.

Ketika indeks membuat hal-hal menjadi lebih buruk

Indeks tidak gratis. Setiap indeks mengorbankan kinerja penulisan dan ruang disk. Database memperbarui setiap indeks pada setiap INSERT, UPDATE, dan DELETE pada tabel tersebut. Pada tabel dengan volume penulisan tinggi, terlalu banyak indeks benar-benar merusak throughput. Di bawah ini adalah tempat indeks gagal atau berbalik:

• Kolom dengan kardinalitas rendah. Indeks pada kolom boolean atau kolom dengan 3 nilai unik sering diabaikan oleh optimizer. Jika query akan mengembalikan lebih dari ~10–15% baris melalui indeks, database biasanya memilih pemindaian tabel — biaya pencarian indeks tidak layak. Periksa dengan EXPLAIN apakah indeks benar-benar digunakan. status Tabel dengan volume penulisan tinggi.
• Tabel log, tabel kejadian, tabel audit — berhati-hatilah. Tabel dengan 10 indeks dan jutaan penulisan per jam membayar 10x beban penulisan pada indeks tersebut. Indeks yang tidak digunakan.
• Di PostgreSQL, menampilkan pg_stat_user_indexes — seberapa banyak kali setiap indeks digunakan sejak reset statistik terakhir. Indeks dengan nol penggunaan adalah beban murni. MySQL melacak ini melalui Performance Schema ( idx_scan di MySQL 5.7+).sys.schema_unused_indexes Optimizer salah memperkirakan.
• Pengaturan query tidak selalu benar. Kadang-kadang mereka memilih indeks yang lebih lambat daripada pemindaian penuh. akan menangkap ini — bandingkan jumlah baris yang diprediksi dengan jumlah aktual. Di MySQL Anda bisa mengarahkan dengan EXPLAIN ANALYZE ; di PostgreSQL Anda bisa menguji dengan USE INDEX (idx_name)(jangan biarkan ini di produksi). SET enable_seqscan = off Indeks atau ulang query? Pertukaran nyata

Kadang-kadang indeks adalah solusi. Kadang-kadang query adalah masalah dan indeks hanya menyembunyikan masalah tersebut. Beberapa pola di mana Anda sebaiknya mengubah query daripada (atau selain) menambahkan indeks:

Fungsi pada kolom yang diindeks

Mengelompokkan kolom dalam fungsi mencegah penggunaan indeks standar. Indeks menyimpan nilai dari

-- 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';

, bukan nilai dari created_at— hal ini berbeda. PostgreSQL mendukung indeks ekspresi ( YEAR(created_at) ) sebagai solusi alternatif, tetapi perubahan query lebih bersih dan bekerja di semua tempat.CREATE INDEX ON orders (YEAR(created_at))Indeks penutup untuk jalur bacaan panas

indeks penutup

A mengandung semua kolom yang dibutuhkan oleh query, sehingga database tidak perlu membaca baris tabel secara langsung — semua jawaban berasal dari indeks. Ini bisa jauh lebih cepat daripada indeks biasa untuk tabel dengan volume bacaan tinggi: Polanya LIKE

-- 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

Prefix yang tidak dapat diperbaiki dengan indeks B-tree. Jika Anda membutuhkan pencarian akhir atau substring, Anda membutuhkan indeks penuh teks (

-- 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';

di MySQL,FULLTEXT di PostgreSQL) atau sistem pencarian eksternal seperti Elasticsearch. GIN dengan pg_trgm N+1 query

Indeks tidak memperbaiki N+1 query. Jika ORM Anda melakukan satu query untuk mengambil 200 pesanan, lalu satu query terpisah untuk setiap pesanan untuk mendapatkan nama pelanggan, Anda memiliki 201 query. Setiap satu mungkin cepat. Masalahnya adalah volume. Perbaikinya dengan JOIN atau query berkelompok — tidak ada jumlah indeks yang bisa mengubah jumlah query.

Satu hal lagi: bersihkan query sebelum menganalisisnya

Sebelum menjalankan EXPLAIN pada query lambat, membantu memiliki SQL yang disusun secara jelas. Satu baris dengan 200 karakter tanpa indentasi lebih sulit dipahami daripada query yang terstruktur.

Format SQL di IO Tools mengelola MySQL, PostgreSQL, SQL Server, dan bahasa lainnya — berguna saat Anda menyalin query dari log atau output debug ORM dan perlu melihat strukturnya secara langsung. Daftar praktis

Jalankan

• sebelum menambahkan indeks apa pun. Pastikan masalahnya terlebih dahulu. EXPLAIN Cari
• di MySQL) atau type: ALL di PostgreSQL) — biasanya di mana masalahnya terjadi. Seq Scan Periksa perkiraan di EXPLAIN. Jutaan baris dipindai untuk menghasilkan beberapa hasil = indeks yang hilang.
• Pada indeks komposit: syarat kesamaan dulu, syarat rentang terakhir. rows Kolom dengan selektivitas lebih tinggi harus ditempatkan lebih awal dalam indeks komposit.
• Jangan indeks kolom dengan kardinalitas rendah tanpa memverifikasi bahwa optimizer benar-benar menggunakan indeks tersebut.
• Audit indeks yang tidak digunakan secara teratur:
• di PostgreSQL),
• di MySQL). pg_stat_user_indexes Jika fungsi mengelompokkan kolom Anda dalam klausa WHERE, ubah query daripada mengindeks fungsi tersebut. sys.schema_unused_indexes Untuk jalur bacaan panas dengan set kolom kecil, pertimbangkan indeks penutup.
• N+1 adalah masalah arsitektur query, bukan masalah indeks.
• SQL Indexes — Mengapa Query Anda Lambat dan Apa yang Harus Dilakukan Sebenarnya 2
• SQL Indexes — Mengapa Query Anda Lambat dan Apa yang Harus Dilakukan Sebenarnya 1

Anda mungkin juga menyukai