Apa yang massal di mysql?

Sisipan massal di MySQL adalah saat Anda perlu memasukkan banyak catatan ke dalam tabel sekaligus. Ini dapat digunakan untuk membuat data dengan cepat untuk pengujian. Sangat melelahkan untuk menulis ulang pernyataan penyisipan untuk setiap baris. Inilah cara Anda dapat menyisipkan secara massal di MySQL

Masukkan Massal di MySQL

Sintaksis

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( a1, a2,...aN ),( b1, b2,...bN ),..;

a1, a2, a3,. – nilai untuk field1
b1, b2, b3,. – nilai untuk field2
c1, c2, c3,. – nilai untuk field3
…

Contoh
Untuk tabel nama_tabel dengan kolom a, b, c

mysql> INSERT INTO table_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9),(3,3,3);
+------+------+------+
|   a  |   b  |   c  |
+------+------+------+
|   1  |   2  |   3  |
|   4  |   5  |   6  |
|   7  |   8  |   9  |
|   3  |   3  |   3  |
+------+------+------+

Seperti yang Anda lihat, penyisipan massal di MySQL memungkinkan duplikat baris/kolom. Ini dapat menyebabkan duplikat kunci primer. Inilah cara Anda dapat menghindarinya

Masukkan massal di MySQL tanpa duplikat

Kami menggunakan klausa ON DUPLICATE KEY. Mendeteksi nilai duplikat dari primary key. Kami juga menambahkan klausa UPDATE untuk memperbarui nilai duplikat

Saat Anda memasukkan satu atau beberapa baris ke Mysql, Anda hanya menggunakan kueri INSERT standar dan tidak peduli. Tetapi ketika harus memasukkan banyak data sekaligus (mis. g. memuat data ke dalam tabel) kita bisa kehilangan banyak waktu menunggu data sebenarnya dimuat

Pertama-tama, setel pengaturan agar Mysql dapat memasukkan data dengan cara yang paling efisien di perangkat keras Anda. Dan kemudian mari kita lihat daftar opsi yang kita miliki untuk memasukkan volume data yang besar ke dalam Mysql

Jika kami memuat data sebagai kumpulan INSERT, kami membuat pilihan yang paling buruk dan melecehkan server Mysql kami

Mari kita ukur berapa lama waktu yang diperlukan pada simpul 1-inti virtual kecil untuk menyisipkan 5000 baris dalam pendekatan baris demi baris (menggunakan PHP dan kerangka kerja)

<?php
$start = microtime(1);
for ( $i = 0; $i < 5000; $i++ ) {
  mysqly::insert('bulk', ['col' => 'val', 'col2' => 'val2']);
}
echo microtime(1) - $start;

Ini akan memakan waktu (dalam hitungan detik)

3.8

Jadi kami akan memuat data dengan kecepatan sekitar 1300 baris per detik pada node kami. Memuat jutaan baris bisa memakan waktu berjam-jam. Tidak keren

Pendekatan paling sederhana adalah menggabungkan beberapa INSERT menjadi kueri tunggal dengan menggunakan format massal

INSERT INTO bulk(col1, col2) VALUES('a1', 'b1'), ('a2', 'b2'), ('a3', 'b3'), ...

Mari gunakan pendekatan ini untuk memuat data (menggunakan metode

3.8

<?php
$start = microtime(1);
$insert = [];
for ( $i = 0; $i < 10000; $i++ ) {
  $insert[] = ['val' => md5(mt_rand(1, time()))];
}
mysqly::multi_insert('bulk', $insert);
echo microtime(1) - $start;

Ini akan memberi kami kinerja yang jauh lebih baik

0.25

Yang, seperti yang kita lihat, hampir 30 kali lebih cepat (kami telah memasukkan baris dua kali lebih banyak) dan sekitar

3.8

3.8

Sekarang metodenya, tidak begitu populer, tapi mari kita periksa apakah itu adil. Asumsikan kita telah menyiapkan dan menyimpan data kita di

3.8

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE bulk
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'

Metode ini memungkinkan kami memuat

3.8

3.8

3.8

Untuk memuat volume data besar ke Mysql, gunakan pernyataan

3.8

3.8

Saat Anda perlu menyisipkan banyak juta catatan dalam database MySQL, Anda segera menyadari bahwa mengirimkan pernyataan INSERT satu per satu bukanlah solusi yang layak

Dokumentasi MySQL memiliki beberapa tip pengoptimalan INSERT yang layak dibaca untuk memulai

Saya akan mencoba meringkas di sini dua teknik utama untuk memuat data secara efisien ke dalam database MySQL

Jika Anda mencari kinerja mentah, ini pasti solusi pilihan Anda. LOAD DATA INFILE_ adalah pernyataan khusus MySQL yang sangat dioptimalkan yang secara langsung memasukkan data ke dalam tabel dari file CSV / TSV

Ada dua cara untuk menggunakan LOAD DATA INFILE. Anda dapat menyalin file data ke direktori data server (biasanya /var/lib/mysql-files/) dan menjalankan

LOAD DATA INFILE '/path/to/products.csv' INTO TABLE products;

Ini cukup merepotkan karena mengharuskan Anda memiliki akses ke sistem file server, mengatur izin yang tepat, dll.

Kabar baiknya adalah, Anda juga dapat menyimpan file data di sisi klien, dan menggunakan kata kunci LOCAL

LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;

Dalam hal ini, file dibaca dari sistem file klien, disalin secara transparan ke direktori temp server, dan diimpor dari sana. Secara keseluruhan, hampir secepat memuat dari sistem file server secara langsung. Anda perlu memastikan bahwa ini diaktifkan di server Anda

Ada banyak opsi untuk LOAD DATA INFILE, sebagian besar terkait dengan struktur file data Anda (pembatas bidang, penutup, dll. ). Lihat dokumentasi untuk melihat semuanya

Meskipun LOAD DATA INFILE_ adalah pilihan terbaik Anda dari segi kinerja, Anda harus menyiapkan data Anda sebagai file teks yang dipisahkan pembatas. Jika Anda tidak memiliki file seperti itu, Anda harus mengeluarkan sumber daya tambahan untuk membuatnya, dan kemungkinan akan menambah tingkat kerumitan pada aplikasi Anda. Untungnya, ada alternatif

Sebuah pernyataan khas SQL INSERT terlihat seperti

INSERT INTO user (id, name) VALUES (1, 'Ben');

INSERT yang diperluas mengelompokkan beberapa rekaman ke dalam satu kueri

INSERT INTO user (id, name) VALUES (1, 'Ben'), (2, 'Bob');

Kuncinya di sini adalah menemukan jumlah sisipan optimal per kueri untuk dikirim. Tidak ada satu angka yang cocok untuk semua, jadi Anda perlu membandingkan sampel data Anda untuk mengetahui nilai yang menghasilkan kinerja maksimum, atau pengorbanan terbaik dalam hal penggunaan/kinerja memori

Untuk mendapatkan hasil maksimal dari insert yang diperpanjang, disarankan juga untuk melakukannya

• menggunakan pernyataan yang telah disiapkan
• menjalankan pernyataan dalam transaksi

Saya menyisipkan 1. 2 juta baris, 6 kolom tipe campuran, rata-rata ~26 byte per baris. Saya menguji dua konfigurasi umum

• Klien dan server pada mesin yang sama, berkomunikasi melalui soket UNIX
• Klien dan server pada mesin terpisah, dengan latensi sangat rendah (<0. 1 ms) jaringan Gigabit

Sebagai dasar perbandingan, saya menyalin tabel menggunakan

LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;

MUAT INFILE DATA

Yang mengejutkan saya, LOAD DATA INFILE terbukti lebih cepat daripada salinan tabel

• LOAD DATA INFILE. 377.000 sisipan/detik

• LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;

  _7 melalui jaringan. 322.000 sisipan/detik

Perbedaan antara kedua angka tersebut tampaknya berhubungan langsung dengan waktu yang diperlukan untuk mentransfer data dari klien ke server. file data berukuran 53 MiB, dan perbedaan waktu antara 2 tolok ukur adalah 543 ms, yang akan mewakili kecepatan transfer 780 mbps, mendekati kecepatan Gigabit

Artinya, kemungkinan besar, server MySQL tidak mulai memproses file sampai file tersebut ditransfer sepenuhnya. Oleh karena itu, kecepatan penyisipan Anda terkait langsung dengan bandwidth antara klien dan server, yang penting untuk diperhitungkan jika mereka tidak berada di mesin yang sama

Sisipan yang diperpanjang

Saya mengukur kecepatan penyisipan menggunakan

LOAD DATA LOCAL INFILE '/path/to/products.csv' INTO TABLE products;

Seperti yang bisa kita lihat, kecepatan penyisipan meningkat dengan cepat seiring bertambahnya jumlah penyisipan per kueri. Kami mendapat 6x peningkatan kinerja di localhost dan 17x peningkatan melalui jaringan, dibandingkan dengan kecepatan INSERT berurutan

• 40.000 → 247.000 sisipan/detik di localhost
• 12.000 → 201.000 sisipan / detik melalui jaringan

Dibutuhkan sekitar 1.000 penyisipan per kueri untuk mencapai throughput maksimum dalam kedua kasus, tetapi 40 penyisipan per kueri cukup untuk mencapai 90% dari throughput ini di localhost, yang bisa menjadi tradeoff yang bagus di sini. Penting juga untuk dicatat bahwa setelah puncak, kinerja sebenarnya menurun saat Anda memasukkan lebih banyak sisipan per kueri

Manfaat insert yang diperluas lebih tinggi melalui jaringan, karena kecepatan insert berurutan menjadi fungsi latensi Anda

max sequential inserts per second ~= 1000 / ping in milliseconds

Semakin tinggi latensi antara klien dan server, semakin banyak manfaat yang akan Anda dapatkan dari penggunaan insert yang diperluas

Seperti yang diharapkan,

INSERT INTO user (id, name) VALUES (1, 'Ben');

Sisipan yang diperluas di sisi lain, tidak memerlukan file teks sementara, dan dapat memberi Anda sekitar 65% dari throughput LOAD DATA INFILE, yang merupakan kecepatan sisipan yang sangat wajar. Sangat menarik untuk dicatat bahwa tidak masalah apakah Anda berada di localhost atau melalui jaringan, mengelompokkan beberapa sisipan dalam satu kueri selalu menghasilkan kinerja yang lebih baik

Jika Anda memutuskan untuk menggunakan sisipan yang diperluas, pastikan untuk menguji lingkungan Anda dengan sampel data kehidupan nyata Anda dan beberapa konfigurasi sisipan per kueri yang berbeda sebelum memutuskan nilai mana yang paling cocok untuk Anda

Berhati-hatilah saat menambah jumlah sisipan per kueri, karena Anda mungkin harus melakukannya

• mengalokasikan lebih banyak memori di sisi klien
• meningkatkan pengaturan pada server MySQL

Sebagai catatan terakhir, perlu disebutkan bahwa menurut Percona, Anda dapat mencapai kinerja yang lebih baik menggunakan koneksi bersamaan, partisi, dan beberapa kumpulan buffer. Lihat posting ini di blog mereka untuk informasi lebih lanjut

Benchmark telah dijalankan pada server bare metal yang menjalankan Centos 7 dan MySQL 5. 7, Xeon E3 @ 3. 8 GHz, RAM 32 GB, dan drive NVMe SSD. Tabel benchmark MySQL menggunakan mesin penyimpanan InnoDB

Kode sumber benchmark dapat ditemukan di intisari ini. Grafik hasil benchmark tersedia di plot. ly

Bagaimana cara kerja penyisipan massal di MySQL?

Apa itu muatan massal dalam basis data?

Bagaimana saya bisa mengumpulkan data dalam SQL?

Bagaimana cara memasukkan 10.000 catatan di MySQL?