MySQL, sebagai sistem manajemen database relasional database yang paling populer, masih membutuhkan optimasi dari waktu ke waktu. Lebih dari itu, dalam kasus kumpulan data yang besar dan kompleks, tindakan pengoptimalan reguler sangat penting untuk kinerja sistem yang tepat
Optimalisasi kinerja MySQL umumnya melibatkan konfigurasi, pembuatan profil, dan pemantauan kinerja di beberapa tingkatan. Untuk menyempurnakan kinerja MySQL, Anda tidak perlu memiliki keahlian yang luas dan pemahaman yang mendalam tentang SQL
Pada artikel ini, kami akan membawa Anda melalui teknik penyetelan kinerja utama sehingga Anda dapat memastikan stabilitas, keandalan, dan kecepatan aplikasi berbasis database Anda
Periksa perangkat keras dan perangkat lunak yang direkomendasikan
persyaratan MySQL
Hal pertama yang harus dilakukan, terutama jika Anda adalah pemilik PC kelas bawah, adalah memeriksa persyaratan perangkat keras dan perangkat lunak yang optimal untuk MySQL, karena keterbatasan perangkat keras dapat berdampak signifikan pada kinerja.
Persyaratan perangkat keras server database MySQL minimal (untuk versi 5. 7 - 8. 0)
- prosesor 1Ghz
- RAM 512MB
- Ruang hard disk tergantung pada ukuran database
Juga disebutkan bahwa lebih baik menggunakan versi MySQL resmi terbaru jika memungkinkan
Optimalisasi penggunaan memori, disk, dan CPU
Di tingkat perangkat keras, Anda dapat mengambil banyak tindakan untuk meningkatkan sumber daya perangkat keras dan perangkat lunak
ruang disk
Jika Anda menggunakan hard disk drive (HDD) tradisional dan mencari peningkatan kinerja, Anda harus mempertimbangkan untuk memutakhirkan ke SSD. Dokumentasi MySQL resmi tidak secara eksplisit menunjukkan ruang disk atau pengaturan memori yang diperlukan untuk menjalankan server MySQL secara efisien karena mereka terutama bergantung pada ukuran database atau database potensial. Namun, sebaiknya pantau kinerja disk Anda, menggunakan alat kinerja sistem sar dan iostat, misalnya. Jika penggunaan disk jauh lebih tinggi daripada penggunaan sumber daya lain, Anda harus menambahkan lebih banyak penyimpanan atau meningkatkan ke yang lebih cepat
RAM
Kurangnya memori serius dapat mempengaruhi kinerja database juga. Ini mungkin tampak basi, tetapi jika server Anda secara teratur kehabisan memori dan kinerja RAM Disk tidak memuaskan, ada baiknya menambahkan lebih banyak memori. Saat Anda kehabisan RAM, server MySQL menyimpan memori fisik yang memperlambat kinerja. Dengan demikian, pengoptimalan memori MySQL sangat penting
CPU
Pengoptimalan penggunaan CPU MySQL harus dimulai dengan analisis cermat terhadap proses MySQL yang terjadi di mesin Anda dan persentase penggunaan prosesor yang diperlukan. CPU tidak murah untuk ditingkatkan, namun, jika itu adalah hambatan, peningkatan akan diperlukan
koneksi internet
Jaringan adalah bagian penting dari infrastruktur MySQL dan penting untuk melacak dan menganalisis lalu lintas jaringan untuk memastikan Anda memiliki sumber daya yang cukup untuk mengelola beban kerja Anda. Pastikan Anda memiliki koneksi Internet yang baik dan stabil agar server MySQL Anda berjalan dengan baik
Alat untuk penyetelan kinerja perangkat lunak
Seperti yang telah kami sebutkan, Anda dapat mengoptimalkan kinerja MySQL di tingkat perangkat keras dan perangkat lunak. Sekarang mari kita lihat tuning kinerja perangkat lunak MySQL
Penyetelan kinerja MySQL dalam hal perangkat lunak melibatkan konfigurasi opsi server MySQL, peningkatan kinerja kueri MySQL, penyetelan indeks MySQL, beralih ke mesin penyimpanan MySQL InnoDB, dll. Mari pertimbangkan semua ini secara mendetail
Penggunaan indeks MySQL untuk kinerja
Pengindeksan yang tepat untuk peningkatan kinerja tidaklah mudah dan memerlukan tingkat keahlian tertentu, namun ini adalah salah satu peningkatan kinerja terbaik yang dapat Anda lakukan pada database Anda
MySQL menggunakan indeks sebagai indeks buku atau peta jalan untuk menemukan nilai dengan cepat untuk kueri yang diberikan. Tanpa indeks, MySQL akan memindai seluruh tabel baris demi baris untuk menemukan data yang relevan. Dengan demikian, optimasi indeks ditujukan untuk mempercepat pengambilan data. Indeks tidak terlihat oleh pengguna dan berisi informasi tentang di mana data sebenarnya disimpan. Perlu juga dicatat bahwa panjang indeks MySQL untuk tabel InnoDB memiliki batasan tergantung pada format baris
Indeks MySQL sangat berguna untuk kumpulan data besar dan penyetelan indeks adalah hal yang tepat untuk dilakukan jika database Anda tumbuh dengan cepat. Indeks sangat bermanfaat untuk operasi berikut. menemukan baris yang cocok dengan klausa WHERE, mengambil data dengan GABUNG, menyortir dan mengelompokkan data dengan bantuan ORDER BY dan GROUP BY
Jadi mengapa tidak memasukkan indeks sebanyak yang Anda bisa? . Jadi, Anda harus menemukan keseimbangan yang tepat untuk mencapai penggunaan indeks MySQL yang optimal
Tingkatkan kinerja dengan InnoDB
Salah satu tip penyetelan pertama bagi mereka yang memiliki beban berat pada database mereka adalah mencoba beralih ke InnoDB dari mesin penyimpanan MyISAM. Memiliki indeks berkerumun, dengan data dalam halaman dan blok fisik berturut-turut, InnoDB memiliki kinerja yang lebih baik untuk volume data yang besar dibandingkan dengan MyISAM
InnoDB juga menawarkan serangkaian variabel dan pengaturan lanjutan yang dapat dikonfigurasi untuk meningkatkan kinerja MySQL lebih jauh. Pengaturan kinerja InnoDB lebih luas dan dengan demikian ada lebih banyak cara untuk menyetel InnoDB untuk kinerja yang lebih tinggi daripada menyetel MyISAM
Optimasi kueri MySQL
Sekarang mari kita lihat cara mengoptimalkan kueri MySQL untuk kinerja dan kecepatan yang lebih baik. Bagi mereka yang ingin meningkatkan kueri MySQL, ada baiknya mengikuti teknik pengoptimalan berikut
Menambahkan indeks ke kolom yang digunakan dalam klausa WHERE, ORDER BY, dan GROUP BY
Dengan cara ini, Anda akan meningkatkan kinerja kueri MySQL karena server MySQL akan mengambil hasil dari database secara signifikan lebih cepat
Tentukan kolom yang diperlukan dalam pernyataan SELECT
Cobalah untuk menghindari penggunaan SELECT * FROM karena mengambil semua kolom tabel dan dengan demikian menyebabkan beban tambahan pada server dan memperlambat kinerjanya. Buat aturan untuk selalu menentukan kolom dalam pernyataan SELECT
Gunakan DISTINCT dan UNION secukupnya
Tip bagus lainnya untuk penyetelan kueri adalah dengan menggunakan operator DISTINCT dan UNION hanya bila diperlukan karena kueri dengan mereka mengarah ke overhead server dan umumnya meningkatkan waktu respons. Pertimbangkan untuk mengganti UNION dengan UNION ALL dan DISTINCT dengan GROUP BY agar proses lebih efisien
Hindari penggunaan wildcard di awal pola LIKE
Permintaan MySQL dengan operator LIKE sering menyebabkan penurunan kinerja server sehingga harus digunakan dengan hati-hati. MySQL tidak dapat menggunakan indeks ketika pola LIKE dimulai dengan wildcard, misalnya, '%xyz', dan melakukan pemindaian tabel lengkap dalam kasus ini. Anda harus mengingat hal ini saat mengoptimalkan kueri MySQL dan coba gunakan 'xyz%' jika memungkinkan
Gunakan INNER JOIN, bukan OUTER JOIN
Gunakan OUTER JOIN hanya jika diperlukan. MySQL melakukan lebih banyak pekerjaan mengambil hasil untuk OUTER JOIN dibandingkan dengan INNER JOIN. Kami menyarankan Anda untuk memeriksa kinerja kueri JOIN Anda dan jika tidak memuaskan—mulai ubah OUTER JOIN Anda menjadi INNER JOIN jika memungkinkan. Pengoptimalan MySQL JOIN dapat menghasilkan peningkatan kinerja yang dramatis
Sesuaikan opsi server untuk meningkatkan kinerja
Sekarang mari fokus pada cara mengoptimalkan opsi server MySQL dalam hal penyetelan kinerja. Untuk ini, Anda perlu menyesuaikan file konfigurasi (my. cnf/saya. ini)
innodb_buffer_pool_size
Parameter ini menentukan jumlah memori yang dialokasikan oleh MySQL ke kumpulan buffer InnoDB. Nilai yang disarankan untuk parameter ini adalah 70-80% dari memori yang tersedia. Semakin besar kumpulan data Anda, semakin besar nilainya
max_connection
Parameter ini menentukan jumlah maksimum koneksi klien simultan yang diizinkan dan memiliki nilai default 151. Untuk menghindari kesalahan "Terlalu banyak koneksi", nilainya dapat ditingkatkan. Namun, perlu diingat bahwa terlalu banyak koneksi terbuka dapat memengaruhi kinerja
query_cache_size
Parameter ini menetapkan jumlah total memori yang dialokasikan ke cache kueri. Nilai optimal untuk itu tergantung terutama pada kasus kerja Anda dan perlu ditentukan secara tentatif. Idenya adalah untuk memulai dengan sangat kecil—10MB—misalnya, lalu tingkatkan sedikit demi sedikit menjadi 100-200MB. Menyesuaikan query_cache_size, ingat untuk mengaktifkan query cache (query-cache-type ON). Perhatikan bahwa ukuran cache kueri yang besar dapat mengakibatkan penurunan kinerja yang serius
innodb_io_capacity
Parameter ini menentukan jumlah operasi I/O per detik yang diperbolehkan untuk tugas yang dilakukan di latar belakang dan memiliki nilai default 200. Secara umum, nilai sekitar 100 cocok untuk hard drive tingkat rata-rata, sedangkan untuk perangkat penyimpanan yang lebih cepat dan lebih modern, nilai yang lebih tinggi akan menguntungkan.
innodb_log_file_size
Parameter ini menentukan ukuran dalam byte untuk setiap file log redo MySQL dalam grup log dan memiliki nilai default 134.217.728 (sekitar 128 MB). Parameter innodb_log_files_in_group pada gilirannya menentukan jumlah file log dalam grup log dan memiliki nilai default 2. Jika nilai innodb_log_file_size kecil untuk beban kerja Anda dan aplikasi Anda intensif menulis, sebaiknya tingkatkan. Namun, innodb_log_file_size yang terlalu besar akan menambah waktu pemulihan kerusakan. Jadi, Anda harus menemukan ukuran optimalnya
Pembuatan profil MySQL dan pengoptimalan kueri dengan
dbForge Studio untuk MySQL
dbForge Studio untuk MySQL hadir dengan profiler MySQL canggih yang memungkinkan pengumpulan statistik terlengkap tentang kueri yang dieksekusi, menemukan kueri yang lambat, dan memecahkan masalah kinerja dalam bentuk apa pun