Teknik Peningkatan SQL

Artikel ini memperkenalkan beberapa strategi fundamental untuk meningkatkan kecepatan dan efisiensi kueri SQL.

Pemanfaatan Indeks

Pengindeksan adalah konsep penting dalam SQL karena secara signifikan meningkatkan kinerja kueri basis data.

Peningkatan Kinerja SQL dengan Membuat Indeks

Membuat indeks pada kolom yang digunakan dalam klausa WHERE dapat mengubah pemindaian tabel penuh menjadi pemindaian indeks, sehingga meningkatkan kecepatan kueri. Namun, perlu dicatat bahwa pengindeksan cenderung menguntungkan saat memfilter subset data yang relatif kecil.

-- Sebelum optimalisasi
SELECT * FROM books WHERE author = "Hemingway";

-- Setelah optimalisasi
CREATE INDEX idx_books_author ON books(author);
SELECT * FROM books WHERE author = "Hemingway";

Membuat Indeks Gabungan

Indeks gabungan mencakup beberapa kolom. Ini sangat efektif ketika melakukan filter pada beberapa kolom dalam klausa WHERE. Namun, perlu diingat bahwa urutan kolom dalam indeks dan dalam klausa WHERE penting.

-- Sebelum optimalisasi
SELECT * FROM students WHERE firstName = "John" AND lastName = "Doe";

-- Setelah optimalisasi
CREATE INDEX idx_students_first_last_name ON students(firstName, lastName);
SELECT * FROM students WHERE firstName = "John" AND lastName = "Doe";

Membuat Indeks untuk Setiap Kolom Daripada Indeks Gabungan

Dalam beberapa kasus, Anda mungkin ingin membuat indeks terpisah untuk setiap kolom daripada indeks gabungan tunggal. Ini dapat menguntungkan ketika Anda sering melakukan filter pada kolom individual.

-- Sebelum optimalisasi
SELECT * FROM orders WHERE customerID = 123 OR productID = 456;

-- Setelah optimalisasi
CREATE INDEX idx_orders_customerID ON orders(customerID);
CREATE INDEX idx_orders_productID ON orders(productID);
SELECT * FROM orders WHERE customerID = 123 OR productID = 456;

Membuat Indeks untuk Kolom yang Digunakan dalam ORDER BY dan GROUP BY

Kolom yang digunakan dalam klausa ORDER BY dan GROUP BY dapat mendapatkan manfaat dari indeks, yang dapat mempercepat operasi-operasi ini. Jika melibatkan beberapa kolom, indeks gabungan dapat dibuat.

-- Sebelum optimalisasi
SELECT category, COUNT(*) FROM products GROUP BY category;

-- Setelah optimalisasi
CREATE INDEX idx_products_category ON products(category);
SELECT category, COUNT(*) FROM products GROUP BY category;

-- Sebelum optimalisasi
SELECT * FROM customers ORDER BY lastName, firstName;

-- Setelah optimalisasi
CREATE INDEX idx_customers_last_first_name ON customers(lastName, firstName);
SELECT * FROM customers ORDER BY lastName, firstName;

Menghindari Pengurutan yang Tidak Bermakna

Kinerja kueri basis data dapat terpengaruh secara signifikan oleh penggunaan operasi pengurutan yang tidak perlu. Operasi-operasi ini, termasuk perintah ORDER BY dan GROUP BY, dapat menyebabkan waktu pemrosesan menjadi lebih lama, memengaruhi kinerja secara keseluruhan.

Menghindari Pengurutan Tidak Perlu dengan ORDER BY dan GROUP BY

Operasi ORDER BY dan GROUP BY menggunakan waktu pemrosesan yang signifikan. Oleh karena itu, operasi-operasi ini hanya sebaiknya digunakan jika benar-benar diperlukan. Selain itu, ketika digunakan, sebaiknya diterapkan setelah dataset telah difilter sebanyak mungkin.

-- Sebelum optimalisasi
SELECT * FROM users ORDER BY name;

-- Setelah optimalisasi
SELECT * FROM users WHERE active = 1 ORDER BY name;

Pada versi yang dioptimalkan, klausa ORDER BY diterapkan setelah memfilter pengguna yang tidak aktif, yang mengurangi jumlah catatan yang perlu diurutkan.

Mempercepat Perhitungan MAX dan MIN dengan Indeks

Perhitungan seperti MIN dan MAX dapat dioptimalkan menggunakan indeks. Ketika indeks diterapkan pada kolom yang terlibat dalam operasi-operasi ini, basis data dapat dengan cepat menemukan nilai-nilai yang diperlukan.

-- Sebelum optimalisasi
SELECT MAX(score), MIN(score) FROM students;

-- Setelah optimalisasi
CREATE INDEX idx_students_score ON students(score);
SELECT MAX(score), MIN(score) FROM students;

Menggunakan EXISTS daripada DISTINCT

Klausa DISTINCT, yang digunakan untuk menghilangkan baris duplikat, merupakan operasi yang mahal karena membutuhkan pengurutan data. Dalam skenario di mana hanya nilai unik yang diperlukan tanpa perlu pengurutan, dapat menggunakan EXISTS sebagai alternatif.

-- Sebelum optimalisasi
SELECT DISTINCT c.course_id FROM courses AS c INNER JOIN enrollments AS e ON c.course_id = e.course_id;

-- Setelah optimalisasi
SELECT c.course_id FROM courses AS c WHERE EXISTS (SELECT 1 FROM enrollments AS e WHERE e.course_id = c.course_id);

Menggunakan UNION ALL daripada UNION

Operasi UNION dapat menggunakan resource yang intensif karena melibatkan pengurutan dan penghapusan catatan duplikat. Jika Anda bergabung dengan baris yang Anda tahu unik, dapat menggunakan UNION ALL yang tidak melibatkan pengurutan atau penghapusan duplikat.

-- Before optimization
SELECT name FROM employees UNION SELECT name FROM managers;

-- After optimization
SELECT name FROM employees UNION ALL SELECT name FROM managers;

Menghindari Pemrosesan Berulang

Dalam penyempurnaan SQL, prinsip utama yang harus diterapkan adalah meminimalkan pemrosesan berulang sebanyak mungkin. Hal ini dapat dicapai dengan mengurangi jumlah akses tabel, memilih JOIN daripada subkueri dalam SELECT, dan menghindari filtering yang berulang yang tidak perlu.

Mengurangi Jumlah Akses Tabel

Bagian fundamental dari penyempurnaan SQL melibatkan mengurangi frekuensi akses tabel. Dalam situasi di mana isi tabel dapat diverifikasi dalam satu kali kejadian, optimal untuk menyelesaikan pemeriksaan dalam satu kesempatan tersebut.

Menggunakan JOIN Daripada Subkueri dalam SELECT

Ketika kita menggunakan subkueri dalam kolom target pernyataan SELECT, SQL untuk subkueri dieksekusi untuk setiap baris, yang dapat meningkatkan waktu pemrosesan. Sebagai gantinya, menggunakan JOIN untuk asosiasi tunggal dapat menghasilkan hasil yang sama dengan waktu pemrosesan yang lebih sedikit.

Pertimbangkan contoh kode berikut, di mana kita menjalankan subkueri dalam pernyataan SELECT untuk setiap baris karyawan untuk mendapatkan nama departemen.

SELECT
emp.*, (SELECT dp.name FROM departments AS dp WHERE dp.id = emp.department_id)
FROM employees AS emp;

Dengan menggunakan LEFT JOIN, kita dapat mencapai hasil yang sama tanpa harus menjalankan subkueri beberapa kali, seperti yang ditunjukkan dalam kode yang dimodifikasi di bawah ini.

SELECT
emp.*, dp.name
FROM employees AS emp LEFT JOIN departments AS dp
ON emp.department_id = dp.id;

Menghindari Filtering yang Berulang yang Tidak Perlu

Klausa WHERE juga dapat berkontribusi terhadap waktu pemrosesan. Mengurangi jumlah penggunaannya dapat membantu menghilangkan pemrosesan yang tidak perlu.

Mari kita lihat contoh berikut di mana kita melakukan filter dua kali dengan klausa WHERE dan mengaitkan data tersebut.

SELECT o1.*, o2.daily_summary
FROM orders AS o1
LEFT JOIN(
   SELECT order_date, SUM(order_amount * order_price) AS "daily_summary" FROM orders
   WHERE order_date BETWEEN "2023-01-01" AND "2023-01-31"
   GROUP BY order_date
) AS o2
ON o1.order_date = o2.order_date
WHERE o1.order_date BETWEEN "2023-01-01" AND "2023-01-31";

Dengan menggunakan fungsi window, kita dapat menguranginya menjadi satu filter dan satu agregasi saja, seperti yang ditunjukkan dalam kode yang dimodifikasi di bawah ini.

SELECT
*, SUM(o1.order_amount * o1.order_price) OVER(PARTITION BY o1.order_date) AS daily_summary
FROM orders AS o1
WHERE o1.order_date BETWEEN "2023-06-01" AND "2023-06-30";

Mengurangi Jumlah Eksekusi SQL

Saat mengoptimalkan kinerja SQL, mengurangi jumlah eksekusi SQL dapat memberikan kontribusi signifikan untuk membuat kueri Anda lebih efisien. Hal ini karena setiap eksekusi SQL menyebabkan komunikasi antara server aplikasi dan server DB, yang dapat memakan waktu. Mari kita jelajahi beberapa teknik untuk mengurangi jumlah eksekusi SQL.

Mengurangi Jumlah Perintah INSERT

Saat memasukkan beberapa catatan ke dalam tabel, pertimbangkan menggunakan multi-insert. Ini memungkinkan Anda untuk memasukkan beberapa catatan dalam satu eksekusi SQL.

Misalnya, jika Anda ingin memasukkan dua catatan ke dalam tabel projects, biasanya Anda akan melakukan sesuatu seperti ini:

INSERT INTO projects(name, start_date, end_date) VALUES ('Database','2023-02-01','2023-08-31');
INSERT INTO projects(name, start_date, end_date) VALUES ('WebApp','2023-04-05','2023-12-23');

Namun, dua penyisipan terpisah ini dapat digabungkan menjadi satu operasi seperti berikut:

INSERT INTO
projects(name, start_date, end_date)
VALUES
('Database','2023-02-01','2023-08-31'),  ('WebApp','2023-04-05','2023-12-23');

Menghapus Indeks Sebelum INSERT Masif dan Menambahkannya Kembali Setelahnya

Saat memasukkan catatan ke dalam tabel yang memiliki indeks, sistem juga harus memperbarui indeks, yang dapat menjadi operasi yang memakan waktu. Ketika memasukkan puluhan ribu atau lebih catatan, pertimbangkan untuk menghapus indeks sebelum operasi INSERT, dan kemudian membuatnya kembali setelahnya.

Menggunakan LIMIT untuk Eksekusi Kueri

Jika Anda hanya ingin memeriksa beberapa catatan dalam sebuah tabel, Anda dapat menggunakan klausa LIMIT untuk mengurangi waktu pemrosesan. Namun, perlu dicatat bahwa fungsi agregat seperti COUNT(*) tidak akan dipercepat dengan menambahkan klausa LIMIT.

Menggunakan TRUNCATE Daripada DELETE untuk Penghapusan Total Catatan

Ketika Anda perlu menghapus semua catatan dari sebuah tabel, operasi DELETE dapat lambat karena mencatat penghapusan setiap baris secara individual. Jika Anda tidak perlu menyimpan log ini dan perlu membersihkan tabel sepenuhnya, pertimbangkan menggunakan perintah TRUNCATE, yang biasanya jauh lebih cepat.