Haai.. gaaeeesss...
Ada Beberapa Tips untuk mengoptimalkan/mempercepat query di MySQL berikut tipsnya :
Tips 1: Buat Indeks Semua Kolom yang Digunakan di 'where', 'order by', dan 'group by' Clauses
Selain menjamin catatan yang dapat diidentifikasi secara unik, indeks memungkinkan server MySQL untuk mengambil hasil lebih cepat dari database. Indeks juga sangat berguna dalam hal memilah catatan.
Indeks MySQL dapat mengambil lebih banyak ruang dan menurunkan kinerja saat memasukkan, menghapus, dan memperbarui. Namun, jika tabel Anda memiliki lebih dari 10 baris, mereka dapat sangat mengurangi waktu eksekusi permintaan pilih.
Selalu disarankan untuk menguji query MySQL dengan jumlah sampel data "skenario terburuk" untuk mendapatkan gambaran yang lebih jelas tentang bagaimana query akan berperilaku terhadap produksi.
Pertimbangkan kasus di mana Anda menjalankan query SQL berikut dari database dengan 500 baris tanpa indeks:
mysql> select customer_id, customer_name from customers where customer_id='140385';
Query di atas akan memaksa server MySQL untuk melakukan pemindaian tabel penuh (mulai hingga selesai) untuk mengambil catatan yang kita cari.
Untungnya, MySQL memiliki pernyataan ' JELASKAN ' khusus yang dapat Anda gunakan bersama memilih, menghapus, menyisipkan, mengganti, dan memperbarui pernyataan untuk menganalisis pertanyaan Anda.
Setelah Anda menambahkan query sebelum pernyataan SQL, MySQL menampilkan informasi dari optimizer tentang rencana eksekusi yang dimaksud.
Jika kita menjalankan SQL di atas sekali lagi dengan pernyataan jelaskan, kita akan mendapatkan gambaran lengkap tentang apa yang akan dilakukan MySQL untuk mengeksekusi query:
mysql> explain select customer_id, customer_name from customers where customer_id='140385'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 500 | 10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
Seperti yang Anda lihat, pengoptimal telah menampilkan informasi yang sangat penting yang dapat membantu kami menyempurnakan tabel database kami. Pertama, jelas bahwa MySQL akan melakukan pemindaian tabel penuh karena kolom kunci adalah ' NULL '. Kedua, server MySQL telah dengan jelas menunjukkan bahwa ia akan melakukan pemindaian penuh pada 500 baris di basis data kami.
Untuk mengoptimalkan query di atas, kita bisa menambahkan indeks ke bidang ' customer_id ' menggunakan sintaksis di bawah ini:
mysql> Create index customer_id ON customers (customer_Id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
Jika kita menjalankan pernyataan penjelasan sekali lagi, kita akan mendapatkan hasil di bawah ini:
mysql> Explain select customer_id, customer_name from customers where customer_id='140385'; +----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | customers | NULL | ref | customer_id | customer_id | 13 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
Dari penjelasan di atas, jelas bahwa server MySQL akan menggunakan indeks kami (customer_Id) untuk mencari tabel. Anda dapat dengan jelas melihat bahwa jumlah baris yang akan dipindai adalah 1. Meskipun saya menjalankan query di atas dalam sebuah tabel dengan 500 catatan, indeks bisa sangat berguna ketika Anda meminta set data yang besar (misalnya tabel dengan 1 juta baris).
Tips 2: Optimalkan Pernyataan 'Like' dengan 'Union' Clause
Terkadang, Anda mungkin ingin menjalankan query menggunakan operator perbandingan ' or' pada bidang atau kolom yang berbeda di tabel tertentu. Ketika kata kunci ' or' digunakan terlalu banyak di mana klausa, mungkin membuat pengoptimal MySQL salah memilih pemindaian tabel penuh untuk mengambil catatan.
Klausa 'union' dapat membuat query berjalan lebih cepat terutama jika Anda memiliki indeks yang dapat mengoptimalkan satu sisi permintaan dan indeks yang berbeda untuk mengoptimalkan sisi lainnya.
Contoh, pertimbangkan kasus di mana Anda menjalankan kueri di bawah ini dengan indeks ' first_name ' dan ' last_name ':
mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%' ;
Kueri di atas dapat berjalan jauh lebih lambat dibandingkan dengan kueri di bawah ini yang menggunakan operator gabungan menggabungkan hasil dari 2 permintaan cepat terpisah yang mengambil keuntungan dari indeks.
mysql> select from students where first_name like 'Ade%' union all select from students where last_name like 'Ade%' ;
Tips 3: Hindari Ekspresi Like dengan Memimpin Wildcard
MySQL tidak dapat menggunakan indeks ketika ada wildcard terkemuka dalam kueri. Jika kita mengambil contoh di atas pada tabel siswa, pencarian seperti ini akan menyebabkan MySQL melakukan pemindaian tabel penuh walaupun Anda telah mengindeks bidang ' first_name ' pada tabel siswa.
mysql> select * from students where first_name like '%Ade' ;
Kami dapat membuktikan ini menggunakan kata kunci yang menjelaskan:
mysql> explain select * from students where first_name like '%Ade' ; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | students | NULL | ALL | NULL | NULL | NULL | NULL | 500 | 11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
Seperti yang Anda lihat di atas, MySQL akan memindai semua 500 baris di tabel siswa kami dan membuatnya akan membuat permintaan menjadi sangat lambat.
Kiat 4: Manfaatkan MySQL Full-Text Searches
Jika Anda dihadapkan pada situasi di mana Anda perlu mencari data menggunakan wildcard dan Anda tidak ingin database Anda berkinerja buruk, Anda harus mempertimbangkan untuk menggunakan pencarian teks lengkap MySQL (FTS) karena jauh lebih cepat daripada permintaan menggunakan karakter wildcard.
Selain itu, FTS juga dapat memberikan hasil yang lebih baik dan relevan ketika Anda mencari basis data yang sangat besar.
Untuk menambahkan indeks pencarian teks lengkap ke tabel sampel siswa, kita dapat menggunakan perintah MySQL di bawah ini:
mysql>Alter table students ADD FULLTEXT (first_name, last_name); mysql>Select * from students where match(first_name, last_name) AGAINST ('Ade');
Dalam contoh di atas, kami telah menentukan kolom yang ingin kami cocokkan (first_name dan last_name) dengan kata kunci pencarian kami ('Ade').
Jika kami meminta pengoptimal tentang rencana eksekusi dari permintaan di atas, kami akan mendapatkan hasil berikut:
mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade'); +----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+ | 1 | SIMPLE | students | NULL | fulltext | first_name | first_name | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted | +----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
Jelas bahwa hanya satu baris yang akan dipindai meskipun database siswa kami memiliki 500 baris dan ini akan mempercepat database.
Tip 6: Optimalkan Skema Basis Data Anda
Bahkan jika Anda mengoptimalkan kueri MySQL Anda dan gagal menghasilkan struktur basis data yang baik, kinerja basis data Anda masih bisa terhenti ketika data Anda meningkat.
Normalisasikan Tabel
Pertama, normalkan semua tabel database bahkan jika itu akan melibatkan beberapa trade-off. Misalnya, jika Anda membuat dua tabel untuk menyimpan data dan pesanan pelanggan, Anda harus mereferensikan pelanggan pada tabel pesanan menggunakan id pelanggan sebagai ganti mengulangi nama pelanggan pada tabel pesanan. Yang terakhir akan menyebabkan database Anda membengkak.
Gambar di bawah ini mengacu pada skema database yang dirancang untuk kinerja tanpa redundansi data apa pun. Dalam normalisasi database MySQL, Anda harus mewakili fakta hanya sekali di seluruh database. Jangan ulangi nama pelanggan di setiap tabel; alih-alih hanya menggunakan customer_Id untuk referensi di tabel lain.
Selain itu, selalu gunakan tipe data yang sama untuk menyimpan nilai yang sama bahkan jika mereka berada di tabel yang berbeda, misalnya, skema di atas menggunakan tipe data ' INT ' untuk menyimpan ' customer_id ' baik di tabel pelanggan dan pesanan.
Gunakan Jenis Data Optimal
MySQL mendukung berbagai tipe data termasuk integer, float, double, date, date_time, Varchar, dan teks. Saat mendesain meja Anda, Anda harus tahu bahwa "lebih pendek selalu lebih baik."
Sebagai contoh, jika Anda mendesain tabel pengguna sistem yang akan menampung kurang dari 100 pengguna, Anda harus menggunakan tipe data ' TINYINT ' untuk bidang ' user_id ' karena itu akan mengakomodasi semua nilai Anda dari -128 hingga 128.
Juga, jika bidang mengharapkan nilai tanggal (misalnya sales_order_date), menggunakan tipe data date_time akan ideal karena Anda tidak harus menjalankan fungsi rumit untuk mengonversi bidang menjadi tanggal saat mengambil catatan menggunakan SQL.
Gunakan nilai integer jika Anda mengharapkan semua nilai menjadi angka (misalnya dalam bidang student_id atau payment_id). Ingat, ketika datang ke komputasi, MySQL dapat melakukan lebih baik dengan nilai integer dibandingkan dengan tipe data teks seperti Varchar
Hindari Nilai Null
Null adalah tidak adanya nilai dalam kolom. Anda harus menghindari nilai-nilai semacam ini bila memungkinkan karena dapat merusak hasil basis data Anda. Misalnya, jika Anda ingin mendapatkan jumlah dari semua pesanan dalam database tetapi catatan pesanan tertentu memiliki jumlah nol, hasil yang diharapkan mungkin salah tingkah kecuali Anda menggunakan pernyataan MySQL ' ifnull ' untuk mengembalikan nilai alternatif jika catatan nol.
Dalam beberapa kasus, Anda mungkin perlu menetapkan nilai default untuk bidang jika catatan tidak harus menyertakan nilai wajib untuk kolom / bidang tertentu.
Hindari Terlalu Banyak Kolom
Tabel lebar bisa sangat mahal dan membutuhkan lebih banyak waktu CPU untuk diproses. Jika memungkinkan, jangan melebihi seratus kecuali logika bisnis Anda secara khusus menyerukan hal ini.
Alih-alih membuat satu tabel lebar, pertimbangkan untuk membaginya menjadi struktur logis. Misalnya, jika Anda membuat tabel pelanggan tetapi Anda menyadari pelanggan dapat memiliki beberapa alamat, lebih baik membuat tabel terpisah untuk menyimpan alamat pelanggan yang merujuk kembali ke tabel pelanggan menggunakan bidang ' customer_id '.
Optimalkan Joins
Selalu sertakan lebih sedikit tabel dalam pernyataan bergabung Anda. Pernyataan SQL dengan pola yang dirancang buruk yang melibatkan banyak gabungan mungkin tidak berfungsi dengan baik. Aturan praktisnya adalah memiliki selusin bergabung maksimal untuk setiap permintaan.
Tip 7: Caching MySQL Query
Jika situs web atau aplikasi Anda melakukan banyak kueri pemilihan (misalnya WordPress), Anda harus memanfaatkan fitur caching query MySQL. Ini akan mempercepat kinerja ketika operasi baca dilakukan.
Teknologi ini bekerja dengan caching permintaan pilih di samping set data yang dihasilkan. Ini membuat kueri berjalan lebih cepat karena diambil dari memori jika dieksekusi lebih dari satu kali. Namun, jika aplikasi Anda sering memperbarui tabel, ini akan membatalkan kueri dan hasil set yang di-cache.
Anda dapat memeriksa apakah server MySQL Anda memiliki cache permintaan diaktifkan dengan menjalankan perintah di bawah ini:
mysql> show variables like 'have_query_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_query_cache | YES | +------------------+-------+ 1 row in <b>set</b> (0.00 sec)
Mengatur Cache Permintaan Server MySQL
Anda dapat mengatur nilai cache kueri MySQL dengan mengedit file konfigurasi (' /etc/mysql/my.cnf ' atau ' /etc/mysql/mysql.conf.d/mysqld.cnf '). Ini akan tergantung pada instalasi MySQL Anda. Jangan atur nilai ukuran cache permintaan yang sangat besar karena ini akan menurunkan server MySQL karena overhead dan penguncian yang di-cache. Nilai dalam kisaran puluhan megabita disarankan.
Untuk memeriksa nilai saat ini, gunakan perintah di bawah ini:
mysql> show variables like 'query_cache_%' ; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 5 rows in <b>set</b> (0.00 sec)
Kemudian untuk menyesuaikan nilai, sertakan yang berikut pada file konfigurasi MySQL:
query_cache_type=1 query_cache_size = 10M query_cache_limit=256k
Anda dapat menyesuaikan nilai-nilai di atas sesuai dengan kebutuhan server Anda.
Arahan ' query_cache_type = 1 ' mengaktifkan caching MySQL jika dinonaktifkan secara default.
' Query_cache_size ' default adalah 1MB dan seperti yang kami katakan di atas nilai kisaran sekitar 10 MB direkomendasikan. Selain itu, nilainya harus lebih dari 40 KB jika tidak, server MySQL akan memberikan peringatan, " Cache permintaan gagal untuk mengatur ukuran ".
' Query_cache_limit ' default juga 1MB. Nilai ini mengontrol jumlah hasil kueri individual yang bisa di-cache.
No comments:
Post a Comment