Artikel ini memperkenalkan strategi MySQL untuk meningkatkan efisiensi operasi pemuatan data. Seringkali Anda khawatir dengan pengoptimalan kueri SELECT karena kueri tersebut adalah kueri yang paling umum digunakan, dan menentukan cara mengoptimalkannya tidak selalu mudah. Memuat data ke dalam database relatif mudah. Seringkali Anda khawatir dengan pengoptimalan kueri SELECT karena kueri tersebut adalah kueri yang paling umum digunakan, dan menentukan cara mengoptimalkannya tidak selalu mudah. Memuat data ke dalam database relatif mudah. Namun demikian, terdapat strategi yang dapat digunakan untuk meningkatkan efisiensi operasi pemuatan data, yang prinsip dasarnya adalah sebagai berikut:
Pemuatan massal lebih cepat daripada pemuatan satu baris karena cache indeks tidak perlu dikosongkan setelah setiap rekaman dimuat, melainkan dapat dikosongkan setelah kumpulan rekaman dimuat.
Memuat tabel tanpa indeks lebih cepat daripada memuat setelah indeks. Jika terdapat indeks, tidak hanya catatan yang harus ditambahkan ke file data, namun setiap indeks harus dimodifikasi untuk mencerminkan penambahan catatan baru.
Pernyataan SQL yang lebih pendek lebih cepat daripada pernyataan SQL yang lebih panjang karena memerlukan lebih sedikit analisis di sisi server dan karena lebih cepat dikirim melalui jaringan dari klien ke server. Beberapa faktor ini mungkin tampak sepele (terutama yang terakhir), namun jika Anda memuat data dalam jumlah besar, faktor kecil sekalipun dapat membuat perbedaan besar pada hasilnya. Kita dapat menggunakan prinsip umum di atas untuk memperoleh beberapa kesimpulan praktis tentang cara memuat data paling cepat:
LOAD DATA (dalam segala bentuknya) lebih efisien daripada INSERT karena memuat baris dalam batch. Penyegaran indeks lebih sedikit, dan server hanya perlu mengurai dan menafsirkan satu pernyataan, bukan beberapa pernyataan.
LOAD DATA lebih efisien dibandingkan LOAD DATA LOCAL. Dengan LOAD DATA, file harus berada di server dan harus memiliki izin FILE, namun server dapat membaca file langsung dari disk. Dengan LOAD DATA LOCAL, klien membaca file dan mengirimkannya melalui jaringan ke server, yang lambat.
Jika Anda harus menggunakan INSERT, Anda harus menggunakan formulir yang memungkinkan beberapa baris ditentukan dalam satu pernyataan, seperti:
Semakin banyak baris yang dapat Anda tentukan dalam sebuah pernyataan, semakin baik. Hal ini mengurangi jumlah pernyataan yang diperlukan dan mengurangi jumlah penyegaran indeks. Jika Anda menggunakan mysqldump untuk membuat file cadangan database, Anda harus menggunakan opsi --exended-insert sehingga file dump berisi pernyataan INSERT multi-baris. Anda juga dapat menggunakan --opt (optimasi), yang mengaktifkan opsi --exended-insert. Sebaliknya, penggunaan opsi --complete-insert pada mysqldump harus dihindari; opsi ini menyebabkan pernyataan INSERT menjadi satu baris, membutuhkan waktu lebih lama untuk dieksekusi, dan memerlukan lebih banyak analisis daripada pernyataan yang dihasilkan tanpa opsi --complete-insert.
Gunakan protokol klien/server terkompresi untuk mengurangi lalu lintas data jaringan. Untuk sebagian besar klien MySQL, hal ini dapat ditentukan dengan opsi baris perintah --compress. Biasanya hanya digunakan pada jaringan yang lebih lambat karena kompresi memerlukan banyak waktu prosesor.
Biarkan MySQL memasukkan nilai default; jangan tentukan kolom dalam pernyataan INSERT yang akan diberi nilai default dengan cara apa pun. Rata-rata, hal ini menghasilkan pernyataan yang lebih pendek dan mengurangi jumlah karakter yang dikirim melalui jaringan ke server. Selain itu, pernyataan yang berisi nilai lebih sedikit memerlukan lebih sedikit analisis dan transformasi oleh server.
Jika tabel diindeks, Anda dapat menggunakan sisipan massal (pernyataan LOAD DATA atau INSERT multi-baris) untuk mengurangi overhead indeks. Hal ini meminimalkan dampak pembaruan indeks karena indeks hanya perlu di-refresh ketika semua baris telah diproses, bukan setelah setiap baris.
Jika Anda perlu memuat data dalam jumlah besar ke dalam tabel baru, Anda harus membuat tabel dan memuatnya saat tidak diindeks, lalu membuat indeks setelah memuat data. Membuat indeks sekali (daripada mengubahnya sekali per baris) lebih cepat.
Jika Anda menghapus atau menonaktifkan indeks sebelum memuat, membuat ulang atau mengaktifkan indeks setelah memuat data dapat mempercepat pemuatan. Jika Anda ingin menggunakan strategi hapus atau nonaktifkan untuk memuat data, pastikan untuk melakukan beberapa eksperimen untuk melihat apakah strategi tersebut layak dilakukan (jika Anda memuat sejumlah kecil data ke dalam tabel besar, pembuatan ulang dan pengindeksan mungkin memerlukan waktu lebih lama daripada memuat datanya) ).
DROP INDEX dan CREATE INDEX dapat digunakan untuk menghapus dan membangun kembali indeks. Alternatifnya adalah menonaktifkan dan mengaktifkan indeks menggunakan myisamchk atau isamchk. Ini memerlukan akun di host server MySQL dengan akses tulis ke file tabel. Untuk menonaktifkan indeks tabel, masukkan direktori database yang sesuai dan jalankan salah satu perintah berikut:
Gunakan myisamchk untuk tabel MyISAM dengan file indeks dengan ekstensi .MYI, dan isamchk untuk tabel ISAM dengan file indeks dengan ekstensi .ISM. Setelah memuat data ke dalam tabel, aktifkan indeks sebagai berikut:
Jika Anda memutuskan untuk menggunakan penonaktifan dan aktivasi indeks, Anda harus menggunakan protokol penguncian perbaikan tabel yang dijelaskan di Bab 13 untuk mencegah server mengubah kunci pada saat yang sama (walaupun tabel tidak diperbaiki saat ini, tabel diubah seperti tabel proses perbaikan, jadi Anda perlu menggunakan protokol penguncian yang sama).
Prinsip pemuatan data yang dijelaskan di atas juga berlaku untuk kueri tetap terkait klien yang perlu melakukan operasi berbeda. Misalnya, Anda biasanya ingin menghindari menjalankan kueri SELECT yang panjang pada tabel yang sering diperbarui. Kueri SELECT yang berjalan lama dapat menimbulkan banyak perselisihan dan mengurangi kinerja penulis. Salah satu solusi yang mungkin adalah dengan terlebih dahulu menyimpan catatan dalam tabel sementara dan kemudian secara berkala menambahkan catatan ke tabel utama jika penulisan utamanya adalah operasi INSERT. Ini bukan pendekatan yang layak jika diperlukan akses segera ke catatan baru. Namun cara ini bisa digunakan asalkan tidak diakses dalam waktu singkat. Ada dua manfaat menggunakan tabel sementara. Pertama, ini mengurangi pertentangan dengan pernyataan kueri SELECT pada tabel utama dan, oleh karena itu, dijalankan lebih cepat. Kedua, total waktu untuk memuat catatan dari tabel sementara ke dalam tabel utama kurang dari total waktu untuk memuat catatan secara terpisah; cache indeks terkait hanya perlu disegarkan pada akhir setiap pemuatan batch, bukan setelah setiap baris memuat. Salah satu penerapan strategi ini adalah mengakses database MySQL dari halaman Web server Web. Dalam skenario ini, mungkin tidak ada tingkat otoritas yang lebih tinggi yang menjamin entri langsung rekaman ke dalam tabel utama.
Jika datanya bukan jenis catatan tunggal yang akan dimasukkan ketika sistem dimatikan, strategi lain untuk mengurangi penyegaran indeks adalah dengan menggunakan opsi pembuatan tabel DELAYED_KEY_WRITE untuk tabel MyISAM (yang mungkin dapat dilakukan jika MySQL digunakan untuk beberapa entri data berfungsi). Ini akan terjadi). Opsi ini menyebabkan cache indeks disegarkan hanya sesekali, bukan setelah setiap penyisipan.
Jika Anda ingin memanfaatkan penundaan penyegaran indeks di seluruh server, jalankan saja mysqld dengan opsi --delayed-key-write. Dalam skenario ini, penulisan blok indeks ditunda hingga blok harus dihapus untuk memberikan ruang bagi nilai indeks lainnya, hingga perintah flush-tables dijalankan, atau hingga tabel indeks ditutup.
-