Lonjakan latensi pada endpoint listing data berukuran jutaan baris sering kali berakar pada ketiadaan indeks yang tepat untuk kombinasi filter dan pengurutan. Saat database mengeksekusi operasi sortir di memori atau disk (Using filesort) bersamaan dengan full table scan, throughput aplikasi Go Fiber anjlok drastis. Akibatnya, koneksi pada database/sql pool tertahan hingga memicu connection starvation.

Root Cause: Diagnosis via EXPLAIN ANALYZE

Skenario umum: endpoint riwayat transaksi menampilkan daftar pesanan dengan filter status pembayaran, rentang nominal, dan diurutkan berdasarkan waktu pembuatan terbaru.

SELECT id, user_id, status, amount, created_at
FROM orders
WHERE status = 'PAID' AND amount >= 50000.00
ORDER BY created_at DESC
LIMIT 20;

Jalankan EXPLAIN ANALYZE pada instance database (MySQL 8.0+) untuk memeriksa rencana eksekusi awal tanpa indeks komposit:

-> Limit: 20 row(s)  (cost=105432.10 rows=20) (actual time=842.152..842.158 rows=20 loops=1)
    -> Sort: orders.created_at DESC, limit input to 20 row(s)  (cost=105432.10 rows=104520) (actual time=842.150..842.155 rows=20 loops=1)
        -> Filter: ((orders.`status` = 'PAID') and (orders.amount >= 50000.00))  (cost=52112.50 rows=104520) (actual time=0.082..710.420 rows=125000 loops=1)
            -> Table scan on orders  (cost=52112.50 rows=1000000) (actual time=0.075..520.110 rows=1000000 loops=1)

Dua masalah utama teridentifikasi:

  • Table scan masif: Database membaca 1.000.000 baris untuk mencari baris yang memenuhi predikat status dan amount.
  • Sort cost tinggi (Filesort): Karena data yang difilter tidak tersusun menurut urutan created_at di disk/indeks, database mengalokasikan sort buffer untuk mengurutkan 125.000 baris sebelum mengambil 20 baris teratas.

Strategi Indexing: Kaidah ESR (Equality, Sort, Range)

Penyusunan indeks majemuk (composite index) harus mengikuti urutan spesifik agar B-Tree dapat memfilter sekaligus mengeliminasi kebutuhan sorting:

  1. Equality: Kolom dengan perbandingan eksak (=) diletakkan di urutan paling kiri.
  2. Sort: Kolom yang digunakan pada klausa ORDER BY diletakkan tepat setelah kolom equality. B-Tree yang sudah terurut secara fisik memungkinkan database membaca urutan secara langsung tanpa komputasi tambahan.
  3. Range: Kolom dengan filter rentang (>, <, BETWEEN, LIKE) diletakkan di posisi akhir. Begitu mesin traversal B-Tree menemukan rentang non-spesifik, struktur indeks setelah kolom rentang tersebut tidak dapat lagi digunakan untuk ordering.

Terapkan DDL berikut untuk menerapkan kaidah ESR pada tabel orders:

-- Skema DDL
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    created_at DATETIME NOT NULL,
    KEY idx_user_id (user_id)
) ENGINE=InnoDB;

-- Composite Index berbasis ESR: Equality (status) -> Sort (created_at) -> Range (amount)
CREATE INDEX idx_orders_status_created_amount 
ON orders (status, created_at DESC, amount);

Implementasi Go Fiber dan Mitigasi Connection Pool Starvation

Saat kueri lambat terjadi, request HTTP yang datang bersamaan akan menyerap koneksi database yang tersedia sampai mencapai batas SetMaxOpenConns. Gunakan c.UserContext() yang dipadukan dengan context.WithTimeout agar kueri otomatis dibatalkan di sisi database jika melampaui SLA teknis.

package main

import (
	"context"
	"database/sql"
	"log"
	"time"

	"github.com/gofiber/fiber/v2"
	_ "github.com/go-sql-driver/mysql"
)

type OrderResponse struct {
	ID        int64     `json:"id"`
	UserID    int64     `json:"user_id"`
	Status    string    `json:"status"`
	Amount    float64   `json:"amount"`
	CreatedAt time.Time `json:"created_at"`
}

func SetupOrderHandler(app *fiber.App, db *sql.DB) {
	app.Get("/api/v1/orders", func(c *fiber.Ctx) error {
		// Terapkan batas toleransi latensi ketat (SLA 500ms)
		ctx, cancel := context.WithTimeout(c.UserContext(), 500*time.Millisecond)
		defer cancel()

		status := c.Query("status", "PAID")
		minAmount := c.QueryFloat("min_amount", 50000.00)
		limit := c.QueryInt("limit", 20)
		if limit > 100 {
			limit = 100
		}

		query := `
			SELECT id, user_id, status, amount, created_at
			FROM orders
			WHERE status = ? AND amount >= ?
			ORDER BY created_at DESC
			LIMIT ?;
		`

		rows, err := db.QueryContext(ctx, query, status, minAmount, limit)
		if err != nil {
			if ctx.Err() == context.DeadlineExceeded {
				return c.Status(fiber.StatusGatewayTimeout).JSON(fiber.Map{
					"error": "Database query timed out",
				})
			}
			return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{
				"error": "Internal server error",
			})
		}
		defer rows.Close()

		orders := make([]OrderResponse, 0, limit)
		for rows.Next() {
			var o OrderResponse
			if err := rows.Scan(&o.ID, &o.UserID, &o.Status, &o.Amount, &o.CreatedAt); err != nil {
				return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": err.Error()})
			}
			orders = append(orders, o)
		}

		if err := rows.Err(); err != nil {
			return c.Status(fiber.StatusInternalServerError).JSON(fiber.Map{"error": err.Error()})
		}

		return c.JSON(orders)
	})
}

func main() {
	db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/app_db?parseTime=true")
	if err != nil {
		log.Fatalf("Database connection error: %v", err)
	}
	defer db.Close()

	// Konfigurasi pool connection untuk mencegah leak
	db.SetMaxOpenConns(50)
	db.SetMaxIdleConns(25)
	db.SetConnMaxLifetime(15 * time.Minute)

	app := fiber.New()
	SetupOrderHandler(app, db)
	log.Fatal(app.Listen(":3000"))
}

Verifikasi: Sebelum vs Sesudah Optimasi

Jalankan ulang EXPLAIN ANALYZE setelah indeks komposit dibuat:

-> Limit: 20 row(s)  (cost=28.45 rows=20) (actual time=0.045..0.082 rows=20 loops=1)
    -> Filter: (orders.amount >= 50000.00)  (cost=28.45 rows=20) (actual time=0.044..0.079 rows=20 loops=1)
        -> Index range scan on orders using idx_orders_status_created_amount over (status = 'PAID')  (cost=28.45 rows=35) (actual time=0.038..0.068 rows=35 loops=1)

Dampak Perubahan

  • Using filesort lenyap: Node Sort dihilangkan dari query tree. Urutan data dibaca langsung dari daun indeks B-Tree.
  • Row processed menurun: Mesin basis data hanya perlu membaca puluhan baris untuk mendapatkan 20 baris yang valid alih-alih memproses jutaan baris.
  • Latensi P99: Latensi eksekusi kueri menurun dari kisaran ~850ms ke <2ms pada beban data 1.000.000 baris.
  • Kestabilan Connection Pool: Karena durasi sewa koneksi berkurang drastis, pool starvation berhasil dieliminasi sepenuhnya pada beban concurrent request tinggi.

Trade-off dan Batasan Indeks Majemuk

  • Overhead Operasi Tulis (DML): Setiap operasi INSERT, UPDATE, dan DELETE pada kolom indeks memerlukan re-balancing node B-Tree, yang sedikit menurunkan write throughput.
  • Aturan Leftmost Prefix: Indeks (status, created_at, amount) tidak akan digunakan jika kueri hanya menyaring berdasarkan created_at tanpa menyertakan predikat status.
  • Penggunaan Disk dan RAM: Indeks komposit membutuhkan memori buffer pool (seperti InnoDB Buffer Pool) lebih besar untuk memastikan node indeks tetap tersimpan dalam RAM.