Mempercepat ORDER BY / GROUP BY dengan Indeks

Singkirkan temp sort dengan indeks

Ketika kamu menata data dengan ORDER BY dan kolom target tidak punya indeks, database harus menarik setiap baris keluar lalu mengurutkannya.

Plan menampilkan temporary sort ini sebagai USE TEMP B-TREE FOR ORDER BY (ia membangun struktur kerja sementara hanya untuk menata ulang urutannya).

Keadaan berubah begitu kamu membuat indeks pada kolom yang kamu pakai untuk menata.

Indeks sudah berada dalam urutan kolom tersebut sejak ia dibangun.

Telusuri urutan indeks dan hasilnya langsung keluar.

Temp sort tidak lagi dibutuhkan, dan USE TEMP B-TREE hilang dari plan.

Temp sort menyusun ulang seluruh 50.000 baris ke dalam struktur kerja, jadi biayanya tumbuh seiring jumlah baris.

Meski LIMIT 10 hanya mengembalikan 10 baris teratas, memilih 10 teratas itu tetap berarti menata ulang setiap baris dulu — bebannya tidak menyusut.

Di artikel ini, kamu akan mencoba "menyingkirkan sort dengan indeks" secara praktik.

-- Contoh: ambil 10 baris teratas yang ditata berdasarkan amount
-- Dengan indeks, database tinggal menelusuri urutannya
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

EXPLAIN QUERY PLAN
SELECT sale_id, amount
FROM perf_sales
ORDER BY amount
LIMIT 10;

Bayangkan kebutuhannya: "Kami ingin penjualan ditata berdasarkan tanggal supaya kami bisa melihat yang paling awal." Ambil kueri yang menata berdasarkan sale_date dan bandingkan plan-nya dengan dan tanpa indeks, perhatikan apakah USE TEMP B-TREE FOR ORDER BY muncul. Buang dan bangun ulang indeks di dalam satu eksekusi konsol ini supaya plan sepenuhnya mandiri. (Jalankan dengan benar untuk memunculkan penjelasan.)

① Pertama, pakai DROP INDEX IF EXISTS untuk membuang indeks pada sale_date, lalu tampilkan EXPLAIN QUERY PLAN untuk 10 baris pertama yang ditata berdasarkan sale_date (kondisi tanpa indeks).

② Lalu buat indeks satu-kolom pada sale_date.

③ Tampilkan plan untuk kueri sort yang sama lagi, dan bandingkan dengan ① untuk melihat apakah temp sort hilang.

GROUP BY juga bisa mengandalkan urutan indeks

GROUP BY juga perlu baris dengan nilai yang sama duduk berdekatan supaya bisa mengelompokkannya.

Tanpa indeks, itu berarti temp sort dan USE TEMP B-TREE FOR GROUP BY di plan.

Buat indeks pada kolom grouping dan baris dengan nilai yang sama sudah berdekatan dalam urutan indeks, jadi database bisa memotong grup hanya dengan menelusurinya.

Misalnya, GROUP BY emp_id dipasangkan dengan indeks pada emp_id berarti baris setiap emp_id sudah berurutan dalam urutan indeks, jadi agregasi berjalan tanpa temp sort.

Sertakan kolom yang diagregasi di indeks juga dan kamu juga menghindari kembali ke tabel dasar (Index-Only Scan dari artikel sebelumnya).

-- Contoh: hitung per region
-- Dengan indeks pada region, GROUP BY tidak perlu temp sort
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

Bayangkan kebutuhannya: "Kami ingin jumlah penjualan per rep." Ambil kueri yang mengelompokkan berdasarkan emp_id dan bandingkan plan-nya dengan dan tanpa indeks, perhatikan apakah USE TEMP B-TREE FOR GROUP BY muncul.

① Pertama, pakai DROP INDEX IF EXISTS untuk membuang indeks, lalu tampilkan EXPLAIN QUERY PLAN untuk kueri yang menghitung baris per emp_id (kondisi tanpa indeks).

② Lalu buat indeks satu-kolom pada emp_id.

③ Tampilkan plan untuk kueri agregat yang sama lagi, dan bandingkan dengan ① untuk melihat apakah temp sort hilang.

DISTINCT menyingkirkan temp sort-nya dengan cara yang sama

DISTINCT (yang membuang duplikat dan mengembalikan hanya nilai unik) juga perlu nilai berjajar untuk mendeteksi duplikat, jadi tanpa indeks ia memicu temp sort dengan USE TEMP B-TREE FOR DISTINCT.

Mekanikanya sama dengan GROUP BY: dengan indeks pada kolom target, nilai identik sudah berdekatan dalam urutan, jadi membuang duplikat hanya soal membandingkan setiap tetangga.

Untuk SELECT DISTINCT product FROM perf_sales, di mana kamu ingin nilai unik dari satu kolom, indeks pada product menghilangkan kebutuhan akan temp sort.

Sorting, grouping, dan deduplikasi semuanya berbagi sifat sebagai "operasi yang butuh nilai berjajar" — dan indeks pada kolom target memungkinkan kamu menghindari temp sort untuk salah satunya.

-- Contoh: ambil nilai unik dari region
-- Indeks pada region menghindari temp sort DISTINCT
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT DISTINCT region
FROM perf_sales;

Bayangkan kebutuhannya: "Kami ingin daftar kode produk yang pernah kami tangani, tanpa duplikat." Ambil kueri yang mengembalikan nilai unik dari product dan bandingkan plan-nya dengan dan tanpa indeks, perhatikan apakah USE TEMP B-TREE FOR DISTINCT muncul.

① Pertama, pakai DROP INDEX IF EXISTS untuk membuang indeks, lalu tampilkan EXPLAIN QUERY PLAN untuk kueri yang mengembalikan nilai unik dari product (kondisi tanpa indeks).

② Lalu buat indeks satu-kolom pada product.

③ Tampilkan plan untuk kueri dedup yang sama lagi, dan bandingkan dengan ① untuk melihat apakah temp sort hilang.