Kueri adalah operasi yang paling umum digunakan dalam teknologi basis data. Proses operasi query relatif sederhana: Pertama, pernyataan SQL query dikeluarkan dari klien. Setelah menerima pernyataan SQL yang dikirim oleh klien, server database mengeksekusi pernyataan SQL dan kemudian mengembalikan hasil query ke klien. Meskipun prosesnya sangat sederhana, metode kueri dan pengaturan database yang berbeda akan berdampak besar pada kinerja kueri.
Oleh karena itu, artikel ini membahas teknik optimasi query yang umum digunakan di MySQL. Diskusi meliputi: meningkatkan kecepatan kueri melalui buffering kueri; optimalisasi kueri otomatis MySQL; penyortiran berbasis indeks terhadap kueri yang tidak dapat dijangkau dan menggunakan berbagai opsi kueri untuk meningkatkan kinerja.
1. Tingkatkan kecepatan kueri melalui buffering kueri
Umumnya, ketika kita menggunakan pernyataan SQL untuk melakukan kueri, server database akan mengeksekusi pernyataan SQL ini setiap kali menerima SQL dari klien. Namun ketika pernyataan SQL yang sama persis diterima dalam interval tertentu (misalnya dalam 1 menit), maka akan dieksekusi dengan cara yang sama. Meskipun hal ini dapat memastikan sifat data yang real-time, seringkali data tidak memerlukan waktu nyata yang lengkap, yang berarti mungkin ada penundaan tertentu. Jika ini kasusnya, menjalankan SQL yang sama persis dalam waktu singkat tidak sebanding dengan keuntungannya.
Untungnya, MySQL memberi kita fungsi buffering kueri (buffering kueri hanya dapat digunakan di MySQL 4.0.1 dan yang lebih baru). Kami dapat meningkatkan kinerja kueri sampai batas tertentu melalui cache kueri.
Kita dapat mengatur buffer query melalui file my.ini di direktori instalasi MySQL. Settingnya juga sangat sederhana, cukup set query_cache_type menjadi 1. Setelah mengatur atribut ini, sebelum mengeksekusi pernyataan SELECT apa pun, MySQL akan memeriksa di buffernya apakah pernyataan SELECT yang sama telah dieksekusi. Jika demikian, dan hasil eksekusi belum kedaluwarsa, maka hasil kueri akan langsung dikembalikan ke klien. Namun saat menulis pernyataan SQL, harap dicatat bahwa buffer kueri MySQL peka huruf besar-kecil. Dua pernyataan SELECT berikut adalah sebagai berikut: SELECT * dari TABLE1
PILIH * DARI TABEL1
Kedua pernyataan SQL di atas adalah SELECT yang sangat berbeda untuk buffering kueri. Selain itu, buffer kueri tidak secara otomatis menangani spasi. Oleh karena itu, saat menulis pernyataan SQL, Anda harus mencoba mengurangi penggunaan spasi, terutama spasi di awal dan akhir SQL (karena cache kueri tidak secara otomatis mencegat spasi di awal dan akhir).
Meskipun tidak menyiapkan buffer kueri terkadang dapat menyebabkan penurunan performa, ada beberapa pernyataan SQL yang memerlukan kueri data secara real-time, atau tidak sering digunakan (mungkin dijalankan sekali atau dua kali sehari). Ini memerlukan mematikan buffering. Tentu saja, Anda dapat menonaktifkan cache kueri dengan menetapkan nilai query_cache_type, namun ini akan menonaktifkan cache kueri secara permanen. MySQL 5.0 menyediakan metode untuk mematikan sementara cache kueri: SELECT SQL_NO_CACHE field1, field2 FROM TABLE1
Karena pernyataan SQL di atas menggunakan SQL_NO_CACHE, terlepas dari apakah pernyataan SQL ini telah dijalankan sebelumnya, server tidak akan mencari di buffer dan akan mengeksekusinya setiap saat.
Kita juga dapat mengatur query_cache_type di my.ini menjadi 2, sehingga cache query hanya akan digunakan setelah SQL_CACHE digunakan. PILIH SQL_CALHE * DARI TABEL1
2. Optimalisasi kueri MySQL secara otomatis
Indeks sangat penting untuk database. Indeks dapat digunakan untuk meningkatkan kinerja selama kueri. Namun terkadang penggunaan indeks dapat menurunkan kinerja. Kita bisa melihat tabel PENJUALAN berikut ini: BUAT TABEL PENJUALAN
(
ID INT(10) UNSIGNED BUKAN NULL AUTO_INCREMENT,
NAMA VARCHAR(100) BUKAN NULL,
HARGA FLOAT BUKAN NULL,
SALE_COUNT INT BUKAN NULL,
TANGGAL PENJUALAN TANGGAL BUKAN NULL,
KUNCI UTAMA (ID),
INDEKS (NAMA),
INDEKS (TANGGAL_JUAL)
)
Misalkan ada jutaan data yang disimpan dalam tabel ini, dan kita ingin menanyakan harga rata-rata produk nomor 1000 pada tahun 2004 dan 2005. Kita dapat menulis pernyataan SQL berikut: SELECT AVG(PRICE) FROM SALES
DIMANA ID = 1000 DAN TANGGAL PENJUALAN ANTARA '01-01-2004' DAN '31-12-2005';
Jika kuantitas produk ini sangat besar, maka jumlahnya mencapai hampir 50% atau lebih dari catatan dalam tabel PENJUALAN. Kemudian menggunakan indeks pada bidang SALE_DATE untuk menghitung rata-rata agak lambat. Karena jika menggunakan indeks, Anda harus mengurutkan indeksnya. Ketika ada sangat banyak catatan yang memenuhi ketentuan (seperti mencakup 50% atau lebih catatan di seluruh tabel), kecepatannya akan melambat, jadi lebih baik memindai seluruh tabel. Oleh karena itu, MySQL akan secara otomatis memutuskan apakah akan menggunakan indeks untuk kueri berdasarkan proporsi data yang memenuhi ketentuan di seluruh tabel.
Untuk MySQL, indeks tidak digunakan ketika proporsi hasil kueri di atas terhadap catatan di seluruh tabel adalah sekitar 30%. Proporsi ini diperoleh oleh pengembang MySQL berdasarkan pengalaman mereka. Namun, nilai skala sebenarnya akan bervariasi tergantung pada mesin database yang digunakan.
3. Penyortiran berdasarkan indeks
Salah satu kelemahan MySQL adalah penyortirannya. Meskipun MySQL dapat menanyakan sekitar 15.000 catatan dalam 1 detik, MySQL hanya dapat menggunakan paling banyak satu indeks saat melakukan kueri. Oleh karena itu, jika kondisi WHERE sudah menempati indeks, indeks tidak akan digunakan dalam pengurutan, yang akan sangat mengurangi kecepatan kueri. Kita dapat melihat pernyataan SQL berikut: SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
Indeks pada kolom NAME telah digunakan pada klausa WHERE pada SQL di atas, sehingga indeks tidak lagi digunakan saat mengurutkan SALE_DATE. Untuk mengatasi masalah ini, kita dapat membuat indeks gabungan pada tabel SALES: ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME, SALE_DATE)
Dengan cara ini, kecepatan akan meningkat pesat saat menggunakan pernyataan SELECT di atas untuk melakukan kueri. Namun hati-hati, saat menggunakan metode ini, pastikan tidak ada bidang pengurutan di klausa WHERE. Dalam contoh di atas, Anda tidak dapat menggunakan SALE_DATE untuk melakukan kueri. Jika tidak, meskipun pengurutan lebih cepat, tidak ada indeks terpisah di bidang SALE_DATE , sehingga kueri akan melambat.
4. Deteksi pertanyaan yang tidak dapat dijangkau
Saat menjalankan pernyataan SQL, Anda pasti akan menghadapi beberapa kondisi yang pasti salah. Yang disebut kondisi must-false adalah tidak peduli bagaimana data dalam tabel berubah, kondisi ini salah. Seperti nilai WHERE < 100 DAN nilai > 200. Kita tidak akan pernah menemukan bilangan yang kurang dari 100 dan lebih besar dari 200.
Jika Anda menghadapi kondisi kueri seperti itu, pernyataan SQL tersebut tidak perlu dijalankan. Untungnya, MySQL dapat mendeteksi situasi ini secara otomatis. Misalnya, kita dapat melihat pernyataan SQL berikut: SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
Pernyataan kueri di atas mencari rekaman yang NAMAnya sama dengan nama1 dan nama2. Jelas sekali, ini adalah kueri yang tidak dapat dijangkau, dan kondisi WHERE harus salah. Sebelum MySQL mengeksekusi pernyataan SQL, terlebih dahulu akan dianalisis apakah kondisi WHERE merupakan query yang tidak dapat dijangkau. Jika demikian, pernyataan SQL tidak akan lagi dieksekusi. Untuk memverifikasi ini. Kami pertama kali menggunakan EXPLAIN untuk menguji SQL berikut: EXPLAIN SELECT * FROM SALES WHERE NAME = "name1"
Kueri di atas adalah kueri biasa. Kita dapat melihat bahwa item tabel dalam data informasi eksekusi yang dikembalikan oleh EXPLAIN adalah PENJUALAN. Ini menunjukkan bahwa MySQL mengoperasikan PENJUALAN. Perhatikan kembali pernyataan berikut ini: JELASKAN SELECT * FROM SALES WHERE NAME = “name1” AND NAME = “name2”
Kita dapat melihat bahwa item tabel tersebut kosong, artinya MySQL belum beroperasi pada tabel SALES.
5. Gunakan berbagai pilihan kueri untuk meningkatkan kinerja
Selain penggunaan normal pernyataan SELECT, MySQL juga memberi kita banyak opsi yang dapat meningkatkan kinerja kueri. Seperti disebutkan di atas, SQL_NO_CACHE dan SQL_CACHE, yang digunakan untuk mengontrol buffering kueri, adalah dua opsi. Di bagian ini, saya akan memperkenalkan beberapa opsi kueri yang umum digunakan.
1. STRAIGHT_JOIN: Paksa perintah koneksi
Ketika kita menghubungkan dua atau lebih tabel untuk query, kita tidak perlu peduli tentang tabel mana yang pertama kali dihubungkan oleh MySQL dan tabel mana yang terakhir dihubungkan. Semua ini ditentukan oleh urutan koneksi yang ditentukan oleh MySQL melalui serangkaian perhitungan dan evaluasi internal. Dalam pernyataan SQL berikut, TABLE1 dan TABLE2 belum tentu terhubung satu sama lain: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1, TABLE2 WHERE …
Jika pengembang perlu melakukan intervensi secara manual dalam urutan koneksi, mereka harus menggunakan kata kunci STRAIGHT_JOIN, seperti pernyataan SQL berikut: SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
Seperti dapat dilihat dari pernyataan SQL di atas, STRAIGHT_JOIN digunakan untuk memaksa MySQL menggabungkan tabel dalam urutan TABLE1 dan TABLE2. Jika menurut Anda lebih efisien untuk bergabung dalam pesanan Anda sendiri daripada pesanan yang direkomendasikan oleh MySQL, Anda dapat menggunakan STRAIGHT_JOIN untuk menentukan urutan koneksi.
2. Melakukan intervensi dalam penggunaan indeks dan meningkatkan kinerja
Penggunaan indeks telah disebutkan di atas. Dalam keadaan normal, MySQL akan memutuskan apakah akan menggunakan indeks dan indeks mana yang akan digunakan saat melakukan kueri. Namun dalam beberapa kasus khusus, kami ingin MySQL hanya menggunakan satu atau beberapa indeks, atau kami tidak ingin menggunakan indeks tertentu. Ini memerlukan penggunaan beberapa opsi kueri MySQL untuk mengontrol indeks.
Batasi cakupan penggunaan indeks
Terkadang kita membuat banyak indeks di tabel data. Saat MySQL memilih indeks, semua indeks ini dipertimbangkan. Namun terkadang kita ingin MySQL hanya mempertimbangkan beberapa indeks, bukan semua indeks. Hal ini memerlukan penggunaan USE INDEX untuk menyetel pernyataan kueri. PILIH * DARI TABEL1 GUNAKAN INDEKS (FIELD1, FIELD2) …
Seperti dapat dilihat dari pernyataan SQL di atas, tidak peduli berapa banyak indeks yang telah dibuat di TABLE1, MySQL hanya mempertimbangkan indeks yang dibuat di FIELD1 dan FIELD2 saat memilih indeks.
Batasi rentang indeks yang tidak digunakan
Jika kita memiliki banyak indeks untuk dipertimbangkan dan sedikit indeks yang tidak digunakan, kita dapat menggunakan IGNORE INDEX untuk pemilihan terbalik. Pada contoh di atas, indeks yang dianggap dipilih, sedangkan penggunaan IGNORE INDEX adalah memilih indeks yang tidak dipertimbangkan. PILIH * DARI TABEL1 ABAIKAN INDEKS (FIELD1, FIELD2) …
Dalam pernyataan SQL di atas, hanya indeks pada FIELD1 dan FIELD2 pada tabel TABLE1 yang tidak digunakan.
Paksa penggunaan indeks
Kedua contoh di atas memberikan pilihan kepada MySQL, yang berarti MySQL tidak harus menggunakan indeks ini. Terkadang kita berharap MySQL harus menggunakan indeks tertentu (karena MySQL hanya dapat menggunakan satu indeks saat melakukan query, maka MySQL hanya dapat menggunakan satu indeks). Ini memerlukan penggunaan FORCE INDEX untuk menyelesaikan fungsi ini. PILIH * DARI TABLE1 FORCE INDEKS (FIELD1) …
Pernyataan SQL di atas hanya menggunakan indeks yang dibangun di FIELD1, bukan indeks di bidang lain.
3. Gunakan tabel sementara untuk meningkatkan kinerja kueri
Ketika ada banyak data dalam kumpulan hasil kueri kami, kami dapat memaksa kumpulan hasil tersebut ke dalam tabel sementara melalui opsi SQL_BUFFER_RESULT, sehingga kunci tabel MySQL dapat segera dilepaskan (sehingga pernyataan SQL lainnya dapat menanyakannya catatan) ) dan dapat menyajikan kumpulan catatan besar kepada klien untuk jangka waktu yang lama. PILIH SQL_BUFFER_RESULT * DARI TABEL 1 DIMANA …
Mirip dengan opsi SQL_BUFFER_RESULT, ada SQL_BIG_RESULT. Opsi ini umumnya digunakan untuk mengelompokkan atau kata kunci DISTINCT. Opsi ini memberi tahu MySQL bahwa, jika perlu, hasil kueri akan ditempatkan di tabel sementara, atau bahkan diurutkan dalam tabel sementara. PILIH SQL_BUFFER_RESULT FIELD1, COUNT(*) DARI TABEL1 KELOMPOK BERDASARKAN FIELD1
6. Kesimpulan
Ada juga "prinsip 20/80" dalam pemrograman, yaitu 20% kode membutuhkan 80% waktu. Hal yang sama berlaku untuk pengembangan aplikasi database. Optimalisasi aplikasi database berfokus pada efisiensi eksekusi SQL. Fokus optimasi kueri data adalah membuat server database membaca lebih sedikit data dari disk dan membaca halaman secara berurutan, bukan tidak berurutan.