Meningkatkan kinerja mysql database besar

Daftar isi

Menampilkan lebih banyak

MySQL adalah basis data sumber terbuka yang paling banyak digunakan di dunia, dan menempati urutan kedua dalam popularitas di antara basis data secara keseluruhan. Ini adalah sistem manajemen basis data relasional yang efektif yang telah menjadi inti dari aplikasi populer selama bertahun-tahun. Namun, ini bisa menjadi tantangan untuk digunakan dan ada banyak peluang untuk meningkatkan kinerja

Ada beberapa perkembangan baru yang penting dalam beberapa tahun terakhir untuk MySQL juga. Artikel ini memperbarui rangkaian tip penyempurnaan kinerja MySQL sebelumnya yang disediakan oleh Baron Schwartz. Meskipun artikel sebelumnya masih relevan, ada beberapa langkah tambahan yang dapat Anda ambil untuk mencapai performa terbaik untuk penerapan MySQL Anda. Berikut adalah 10 tips penyetelan kinerja MySQL lainnya untuk ditambahkan ke daftar Anda

Tip kinerja MySQL No. 1. Desain skema sama pentingnya dengan pengaturan MySQL lainnya

Desain skema adalah salah satu hal terpenting yang akan Anda lakukan di database Anda. Ini adalah prinsip teknologi basis data lintas relasional, karena bentuk normal diperkenalkan kembali pada tahun 1970-an. Sejak MySQL pindah ke InnoDB sebagai mesin penyimpanan default di versi 5. 6, desain skema menjadi lebih penting

Kenapa ini? . Ini berkaitan dengan cara InnoDB mengatur data. Di InnoDB, kunci utama dikelompokkan dan setiap kunci sekunder menambahkan penunjuk entri ke kunci utama. Jika Anda tidak mempertimbangkan hal ini dalam desain skema Anda, maka kinerja Anda akan terpengaruh secara negatif

Data juga disimpan menggunakan indeks B-tree, jadi memasukkan data dengan cara yang teratur (mis. e. menggunakan nilai kuasi-sekuensial) mencegah fragmentasi kunci utama dan dengan demikian mengurangi operasi I/O yang diperlukan untuk menemukan simpul daun

Ada beberapa kasus penggunaan di mana kunci utama sekuensial bukanlah pilihan yang tepat — contoh yang baik di sini adalah Pengidentifikasi Unik Universal atau UUID. Anda dapat menemukan pembahasan lebih dalam tentang masalah seputar UUID dan kunci utama di sini. Namun, secara umum, sebaiknya gunakan kunci primer berurutan untuk sebagian besar kasus penggunaan

Tip kinerja MySQL No. 2. Kunci sekunder bukanlah musuh Anda

Kunci sekunder diperbarui oleh proses latar belakang. Akibatnya, dampak kinerja tidak seserius yang Anda harapkan. Sebaliknya, masalahnya ada di sekitar jejak disk karena menambahkan kunci sekunder akan meningkatkan persyaratan penyimpanan

Memfilter bidang yang tidak memiliki indeks dapat menghasilkan pemindaian tabel penuh setiap kali kueri berjalan. Ini dapat, tentu saja, menghasilkan dampak kinerja yang sangat besar. Oleh karena itu, lebih baik memiliki kunci sekunder daripada melewatkannya

Karena itu, Anda tidak boleh mengindeks berlebihan basis data Anda, karena menjalankan banyak indeks mungkin tidak memberikan peningkatan kinerja yang ingin Anda capai. Pada saat yang sama, indeks tambahan ini dapat meningkatkan biaya penyimpanan Anda, dan InnoDB harus melakukan banyak operasi latar belakang agar tetap terbarui

Tip kinerja MySQL No. 3. Baris dapat disajikan dari indeks

InnoDB dapat menemukan dan benar-benar melayani baris langsung dari indeks, sedangkan kunci sekunder menunjuk ke kunci utama dan kunci utama berisi baris itu sendiri. Jika InnoDB Buffer Pool cukup besar, ia juga dapat menyimpan sebagian besar data di memori. Anda bahkan dapat menggunakan kunci gabungan, yang biasanya lebih efektif untuk kueri daripada kunci per kolom individual. MySQL dapat menggunakan satu indeks per akses tabel, jadi jika Anda menjalankan kueri dengan klausa seperti WHERE x=1 and y=2 maka memiliki indeks di atas x,y lebih baik daripada memiliki indeks individual di setiap kolom

Selain itu, indeks gabungan atas x,y juga dapat meningkatkan kinerja kueri berikut

SELECT y FROM table WHERE x=1

MySQL akan menggunakan indeks penutup dan melayani y dari indeks, yang ada di memori

Dalam praktiknya, Anda dapat meningkatkan performa dengan menggunakan indeks komposit saat Anda memiliki kesempatan untuk melakukannya. Setiap kali Anda merancang indeks, Anda perlu memikirkannya dengan cara alami saat dibaca. Artinya, indeks selalu dibaca dari kiri ke kanan, jadi berikan kueri seperti ini

SELECT a,b,c FROM table WHERE a=1 and b=2

Kemudian indeks atas a,b akan membantu dengan kueri. Tetapi jika kueri dalam format ini

SELECT a,b,c FROM table WHERE b=2

Kemudian indeks tidak akan berguna dan akan menyebabkan pemindaian tabel penuh. Ide untuk selalu membaca indeks dari kiri juga berlaku untuk beberapa kasus lainnya. Misalnya diberikan query berikut

SELECT a,b,c FROM table WHERE a=1 and c=2

Kemudian indeks di atas a,b,c hanya akan membaca kolom pertama karena tidak ada

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

Elemen lain yang terkait dengan desain kueri adalah pendekatan indeks paling kiri, karena ini adalah pengoptimalan umum yang digunakan di MySQL. Misalnya, indeks pada a,b,c tidak akan mencakup kueri seperti

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

Tip kinerja MySQL No. 4. Ulasan kueri, ulasan kueri, ulasan kueri

Hanya memiliki mobil Formula Satu tidak memenangkan perlombaan. Tidak jika Anda menempatkan pengemudi yang tidak berpengalaman di belakang kemudi, dan mereka menabraknya di tikungan pertama. Demikian pula, Anda mungkin memiliki server MySQL yang paling baik di dunia, tetapi jika Anda memiliki kueri yang buruk, database Anda akan lebih lambat dari yang seharusnya.

Anda harus secara teratur meninjau desain kueri Anda dari waktu ke waktu karena aplikasi Anda berubah dengan fitur baru dan perbaikan bug. Kumpulan data dan pola penggunaan aplikasi juga cenderung berubah dari waktu ke waktu, yang semuanya dapat memengaruhi kinerja kueri

Menyisihkan waktu untuk meninjau kueri dan memantau waktu eksekusi kueri sangatlah penting. Anda dapat menggunakan log kueri yang lambat atau Skema Kinerja untuk ini, tetapi menerapkan alat pemantauan akan membantu Anda mendapatkan data yang lebih baik

Perlu diingat bahwa tidak selalu kueri paling lambat yang paling penting untuk diperbaiki. Misalnya, Anda mungkin memiliki kueri yang memerlukan waktu 30 detik tetapi berjalan dua kali sehari bersama kueri yang membutuhkan waktu satu detik dan berjalan 100 kali dalam satu menit. Untuk kemenangan besar, Anda harus mulai mengoptimalkan kueri kedua, karena meningkatkannya dapat menghemat banyak waktu dan sumber daya dalam jangka panjang

Tip kinerja MySQL No. 5. Visibilitas penting

Pemantauan adalah salah satu elemen kunci dari penyetelan kinerja. Tanpa mengetahui beban kerja dan pola saat ini, sulit untuk memberikan rekomendasi khusus. Dalam beberapa tahun terakhir, MySQL telah meningkatkan paparan metrik MySQL/InnoDB tingkat rendah, yang dapat membantu dalam memahami beban kerja

Misalnya, di versi sebelumnya, Skema Performa merupakan hambatan dan berdampak besar, terutama jika Anda memiliki banyak tabel. Di MySQL versi terbaru, banyak perubahan seperti Kamus Data baru telah meningkatkan kinerja, dan sekarang Anda dapat memiliki banyak tabel tanpa dampak yang signifikan

Sebagian besar alat pemantauan modern menggunakan Skema Kinerja dalam beberapa cara, jadi rekomendasi yang bagus adalah memeriksa alat ini dan memilih salah satu yang paling sesuai dengan kebutuhan Anda. Visibilitas data kinerja ini dapat menjadi aset besar dalam penyelidikan Anda

Tip kinerja MySQL No. 6. Hati-hati dengan alat penyetelan

Beberapa rekomendasi umum yang diberikan oleh alat penyetelan akan berfungsi di sebagian besar kasus penggunaan. Namun, setiap beban kerja dan setiap skema berbeda. Dalam beberapa kasus, rekomendasi umum alat penyetelan tidak berfungsi, dan sebaiknya berhati-hati saat memercayai rekomendasi ini. Bahkan , yang merupakan alat Oracle sendiri dan tersedia di MySQL, dapat membuat perubahan konfigurasi yang meragukan

Misalnya, menyetel

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE a=1 and c=2

Rekomendasi yang baik adalah menyetel

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

Mengenai

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and c=2

Tip kinerja MySQL No. 7. Operasi I/O masih mahal

MySQL dan InnoDB mencoba meminimalkan jumlah operasi I/O yang mereka lakukan karena mengakses lapisan penyimpanan mahal dalam hal kinerja aplikasi. Ada beberapa pengaturan yang dapat memengaruhi berapa banyak operasi I/O yang dilakukan InnoDB. Dua dari pengaturan ini sering disalahpahami, dan mengubahnya sering kali menyebabkan masalah kinerja

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and c=2

Masalah pertama adalah penurunan kinerja dengan membuat InnoDB menyiram halaman kotor terlalu cepat, sehingga mengurangi kesempatan untuk memodifikasi halaman lebih dari sekali sebelum dibilas. Menyimpan halaman kotor di memori dapat secara signifikan mengurangi operasi I/O yang diperlukan untuk menulis data ke penyimpanan

Kedua, SSD memiliki jumlah penulisan yang diharapkan sebelum mereka melihat penurunan kinerja. Oleh karena itu, meningkatkan jumlah operasi tulis dapat memengaruhi masa pakai SSD Anda, bahkan jika Anda menggunakan drive kelas atas

Cloud hosting sangat populer akhir-akhir ini, dan menjalankan instance layanan MySQL Anda di cloud bisa sangat berguna. Namun, server di cloud sering kali memiliki batas I/O atau akan mengenakan biaya lebih banyak untuk menggunakan lebih banyak I/O. Dengan mengetahui batasan ini, Anda dapat mengonfigurasi parameter ini dengan hati-hati untuk memastikan bahwa batasan ini tidak tercapai dan operasi I/O diminimalkan

Penting untuk menyebutkan

SELECT a,b,c FROM table WHERE a=1 and c=2

Rekomendasi yang baik untuk diikuti adalah tetap menggunakan default kecuali Anda tahu Anda perlu mengubahnya

Perlu juga disebutkan bahwa SSD terbaru secara khusus dioptimalkan untuk database transaksional. Salah satu contohnya adalah Western Digital, yang mencari bantuan ahli untuk membantu mereka memenuhi persyaratan gelombang baru aplikasi yang sedang dibuat

Tip kinerja MySQL No. 8. Manfaatkan ekspresi tabel umum

MySQL8. 0 melihat pengenalan ekspresi tabel umum (CTE), yang membantu menyingkirkan kueri bersarang yang akan membuat tabel turunan. Fungsionalitas baru ini memungkinkan Anda membuat kueri kustom dan mereferensikan hasilnya seolah-olah itu adalah tabel atau tampilan sementara. Perbedaannya adalah bahwa CTE dapat direferensikan beberapa kali dalam transaksi tanpa perlu membuat dan menghapusnya secara eksplisit

Mengingat bahwa CTE terwujud hanya sekali, CTE cenderung lebih cepat dalam transaksi kompleks yang menjalankan banyak kueri. Plus, rekursi CTE didukung, sehingga Anda dapat dengan mudah membuat struktur kompleks dalam bahasa SQL seperti model dan rangkaian hierarki. Jika Anda menginginkan detail lebih lanjut tentang CTE, Anda akan menemukan pengantar di sini

Tip kinerja MySQL No. 9. Waspadai awan

Ada banyak opsi cloud berbeda yang layak dipertimbangkan untuk penerapan MySQL, mulai dari mengimplementasikan instance server MySQL di VM yang Anda kelola, hingga menggunakan solusi database sebagai layanan (DBaaS). Kisaran pilihan sangat luas

Banyak dari layanan ini berjanji untuk memberikan peningkatan kinerja yang signifikan dan menghilangkan semua masalah Anda. Dalam beberapa kasus penggunaan sederhana itu mungkin benar. Namun, bahkan di cloud, Anda harus mengetahui dan memahami prinsip dasar database, atau biaya Anda akan meningkat secara signifikan. Peningkatan biaya ini sering terjadi karena Anda pada dasarnya memecahkan masalah dengan membuang lebih banyak perangkat keras pada masalah tersebut daripada memperbaiki desainnya

Bagaimana cara mempercepat database MySQL yang besar?

Apakah MySQL bagus untuk database besar?

Bagaimana cara mengoptimalkan database yang besar?

Bisakah MySQL menangani 1 juta catatan?