Cara Mengoptimalkan Tabel MySQL

Pendahuluan

Mengoptimalkan tabel MySQL membantu menyusun ulang informasi di server penyimpanan khusus untuk meningkatkan kecepatan input dan output data. Namun, mengetahui kapan harus menggunakan setiap fungsi pengoptimalan dan bagaimana menerapkannya pada situasi Anda adalah kunci untuk pemeliharaan tabel yang layak.

Artikel ini memberikan tips dan fungsi praktis untuk pengoptimalan tabel MySQL.

Prasyarat

• MySQL versi 8.0 diinstal dan dikonfigurasi
• Akses ke prompt perintah atau baris perintah
• Tabel database yang terfragmentasi

Mengapa Anda Harus Mengoptimalkan Tabel MySQL?

Alasan utama untuk tabel MySQL yang tidak dioptimalkan adalah sering dilakukan pembaruan dan penghapusan kueri. Pada gilirannya, ini menyebabkan fragmentasi dan ada beberapa konsekuensi:

1. Tabel database membutuhkan lebih banyak ruang dari yang dibutuhkan.

2. Mengkueri data membutuhkan lebih banyak waktu dari yang seharusnya.

Teknik optimasi tabel MySQL mengatasi pengaturan data di dalam database. Hasilnya adalah penyimpanan yang bersih tanpa ruang yang tidak terpakai dan berlebihan, yang membantu mempercepat kueri.

Kapan Anda Harus Mengoptimalkan Tabel MySQL?

Tabel di mana informasi dalam database terus diperbarui, seperti database transaksional, adalah kandidat yang paling mungkin untuk dioptimalkan.

Namun, bergantung pada ukuran database, kueri pengoptimalan membutuhkan waktu lama untuk diselesaikan dengan tabel yang lebih besar. Oleh karena itu, mengunci meja selama berjam-jam tidak bermanfaat untuk sistem transaksional.

Daripada langsung mengoptimalkan tabel, pertimbangkan untuk mencoba beberapa trik berikut untuk tabel mesin INNODB:

• Turunkan indeks, optimalkan, dan tambahkan kembali indeks . Jika tabel berfungsi tanpa indeks selama beberapa waktu, penurunan, pengoptimalan, dan penambahan kembali indeks akan bekerja lebih cepat dalam beberapa kasus.
• Analisis nilai mana yang paling diuntungkan dari pemadatan . Terkadang, indeks utama tidak berguna atau bermasalah. Mengoptimalkan nilai yang salah menyebabkan fragmentasi dengan indeks sekunder.

Temukan Tabel untuk Pengoptimalan

Ada beberapa cara untuk menampilkan tabel dan menganalisisnya untuk pengoptimalan. Mulailah dengan menghubungkan ke database MySQL Anda:

use <database name>

Bergantung pada kasus penggunaan Anda, filter informasi yang diperlukan dengan mengikuti salah satu tips di bawah ini.

Kiat 1:Tampilkan Ruang yang Tidak Digunakan dalam Tabel

Periksa status tabel yang diinginkan dengan:

show table status like "<table name>" \G

Output menunjukkan beberapa informasi umum tentang tabel. Dua angka berikut ini penting:

• Panjang_data mewakili jumlah ruang yang digunakan database secara total.
• Bebas_data menunjukkan byte yang tidak digunakan yang dialokasikan dalam tabel database. Informasi ini membantu mengidentifikasi tabel mana yang memerlukan pengoptimalan dan berapa banyak ruang yang akan dibebaskan setelahnya.

Kiat 2:Tampilkan Ruang yang Tidak Digunakan untuk Semua Tabel

Skema informasi menyimpan metadata tentang skema database. Untuk memeriksa alokasi data yang tidak digunakan untuk semua tabel dalam skema yang dipilih, jalankan:

select table_name, data_length, data_free
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Kueri menampilkan nama tabel, total ruang, dan ruang yang dialokasikan yang tidak digunakan. Secara default, memori dicetak dalam byte.

Kiat 3:Tampilkan Data dalam Megabita

Cetak data dalam megabyte dengan:

select table_name, round(data_length/1024/1024), round(data_free/1024/1024)
from information_schema.tables
where table_schema='<schema name>'
order by data_free desc;

Tabel contoh tidak terlalu terfragmentasi. Namun, menggunakan optimize table membantu mengosongkan sebagian ruang dari test_table .

Optimalkan Tabel

Ada beberapa cara untuk mengoptimalkan tabel dengan defragmentasi. Namun, langkah umum yang Anda lakukan serupa.

Pertama, proses membuat salinan sementara dari tabel tempat optimasi terjadi. Kemudian, ketika operasi selesai, fungsi tersebut menggantikan tabel asli dengan yang dioptimalkan, mengganti namanya dengan yang asli.

Kiat 1:Optimalkan Tabel Menggunakan MySQL

MySQL menyediakan fungsi untuk mengoptimalkan tabel. Untuk mengoptimalkan tabel, gunakan:

OPTIMIZE TABLE <table name>;

Keluaran menunjukkan pesan status informatif tentang tindakan dan hasil pengoptimalan dalam format tabel.

Kiat 2:Optimalkan Beberapa Tabel Sekaligus

Untuk mengoptimalkan beberapa tabel sekaligus, gunakan:

OPTIMIZE TABLE <table 1>, <table 2>, <table 3>;

Hasilnya menunjukkan status dari pengoptimalan untuk setiap tabel yang dioptimalkan.

Kiat 3:Optimalkan Tabel Menggunakan Terminal

Lakukan pengoptimalan tabel melalui terminal Linux dengan:

sudo mysqlcheck -o <schema> <table> -u <username> -p <password>

Misalnya, untuk melakukan pemeriksaan pada skema bernama pengujian dan tabel bernama test_table menggunakan akar kredensial pengguna, jalankan:

sudo mysqlcheck -o test test_table -u root

Untuk melakukan optimasi pada beberapa tabel, pisahkan setiap nama tabel dengan spasi:

sudo mysqlcheck -o <schema> <table 1> <table 2> -u <username> -p <password>

Setelah Pengoptimalan

Hasil pengoptimalan mengubah nilai data_length dan bebas_data dari tabel yang dioptimalkan. Kedua nilai diturunkan, menunjukkan:

1. Pengoptimalan membebaskan memori yang dialokasikan yang tidak digunakan.

2. Memori database secara keseluruhan lebih rendah karena ruang yang dibebaskan.