Bagaimana menangani jutaan catatan di mysql

Jika Anda telah membaca cukup banyak forum terkait database, milis, atau blog, Anda mungkin pernah mendengar keluhan tentang MySQL yang tidak dapat menangani lebih dari 1.000.000 (atau pilih nomor lain) baris oleh beberapa pengguna. Di sisi lain, terkenal dengan pelanggan seperti Google, Yahoo, LiveJournal, dan Technorati, MySQL memiliki instalasi dengan miliaran baris dan memberikan kinerja yang luar biasa. Apa alasannya?

Alasannya biasanya desain tabel dan memahami pekerjaan bagian dalam MySQL. Jika Anda mendesain data dengan bijak, mempertimbangkan apa yang dapat dilakukan MySQL dan apa yang tidak, Anda akan mendapatkan kinerja yang hebat. Dan jika tidak, Anda mungkin akan kesal dan menjadi salah satu blogger tersebut. Catatan – setiap sistem manajemen database berbeda dalam beberapa hal dan apa yang bekerja dengan baik untuk Oracle, MS SQL, atau PostgreSQL mungkin tidak bekerja dengan baik untuk MySQL dan sebaliknya. Bahkan mesin penyimpanan memiliki perbedaan yang sangat penting yang dapat mempengaruhi kinerja secara dramatis

Tiga masalah utama yang harus Anda perhatikan jika Anda berurusan dengan kumpulan data yang sangat besar adalah Penyangga, Indeks, dan Gabungan

Buffer

Hal pertama yang perlu Anda perhitungkan adalah fakta; . Jika Anda memulai dari ukuran data dalam memori dan mengharapkan penurunan kinerja secara bertahap seiring bertambahnya ukuran database, Anda mungkin akan terkejut dengan penurunan kinerja yang parah. Ini terutama berlaku untuk pencarian indeks dan gabungan yang akan kita bahas nanti. Karena semuanya biasanya sangat melambat setelah tidak muat di memori, solusi yang baik adalah memastikan data Anda muat di memori sebaik mungkin. Ini dapat dilakukan dengan mempartisi data (mis. e. data lama dan jarang diakses disimpan di server yang berbeda), partisi multi-server untuk menggunakan memori gabungan, dan banyak teknik lain yang harus saya bahas di lain waktu

Jadi Anda mengerti berapa banyak data dalam memori mengubah banyak hal, berikut adalah contoh kecil dengan angka. Jika Anda memiliki data sepenuhnya dalam memori, Anda dapat melakukan lebih dari 300.000 pencarian acak per detik dari satu utas, bergantung pada sistem dan struktur tabel. Sekarang jika data Anda sepenuhnya ada di disk (baik data maupun indeks), Anda memerlukan 2+ IO untuk mengambil baris – yang berarti Anda mendapatkan sekitar 100 baris/detik. Catatan. banyak drive tidak banyak membantu karena kita berbicara tentang utas/kueri tunggal di sini. Jadi selisihnya 3.000x. Ini mungkin terlalu banyak karena hanya ada sedikit beban kerja yang benar-benar tidak di-cache, tetapi perbedaan 100+ kali cukup sering terjadi

Indeks

Apa yang diketahui semua orang tentang indeks adalah fakta bahwa indeks bagus untuk mempercepat akses ke database. Beberapa orang juga akan mengingat apakah indeks berguna atau tidak bergantung pada selektivitas indeks – seberapa besar proporsi baris yang cocok dengan nilai atau rentang indeks tertentu. Yang sering dilupakan adalah, bergantung pada apakah beban kerja di-cache atau tidak, selektivitas yang berbeda mungkin menunjukkan manfaat dari penggunaan indeks. Faktanya, bahkan pengoptimal MySQL saat ini tidak memperhitungkannya. Untuk indeks beban kerja dalam memori, akses mungkin lebih cepat meskipun 50% baris diakses, sementara untuk akses terikat IO disk, kami mungkin lebih baik melakukan pemindaian tabel lengkap meskipun hanya beberapa persen atau baris yang diakses

Mari kita lakukan beberapa perhitungan lagi. Pertimbangkan tabel yang memiliki baris 100-byte. Dengan drive SCSI yang layak, kita bisa mendapatkan kecepatan baca 100MB/detik yang memberi kita sekitar 1.000.000 baris per detik untuk akses yang sepenuhnya berurutan, dengan baris yang penuh sesak – sangat mungkin skenario untuk tabel MyISAM. Sekarang jika kita mengambil hard drive yang sama untuk beban kerja yang sepenuhnya terikat IO, itu akan dapat memberikan hanya 100 pencarian baris berdasarkan indeks per detik. Perbedaannya adalah 10.000 kali untuk skenario terburuk kami. Mungkin tidak terlalu buruk dalam praktiknya, tetapi sekali lagi, tidak sulit untuk mencapai perbedaan 100 kali lipat

Ini adalah ilustrasi kecil yang saya buat dari tabel dengan lebih dari 30 juta baris. kolom “val” dalam tabel ini memiliki 10.000 nilai berbeda, jadi rentangnya 1. 100 memilih sekitar 1% dari tabel. Waktu untuk pemindaian tabel lengkap vs pemindaian rentang berdasarkan indeks

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql> pilih hitung(pad) from large;

+-----------+

. hitung(pad) .

+-----------+

.    31457280 .

+-----------+

1 baris di set (4 min 58.63 dtk)

mysql> pilih hitung(pad) from large where val between  1 and 100;

+-----------+

. hitung(pad) .

+-----------+

.      314008 .

+-----------+

1 baris di set (29 min 53.01 dtk)

Juga, ingat – tidak semua indeks dibuat sama. Beberapa indeks dapat ditempatkan dengan cara diurutkan atau halaman ditempatkan di tempat acak – ini dapat mempengaruhi kecepatan pemindaian indeks/jangkauan pemindaian secara dramatis. Baris yang direferensikan oleh indeks juga dapat ditempatkan secara berurutan atau memerlukan IO acak jika rentang indeks dipindai. Ada juga kunci berkerumun di Innodb yang menggabungkan akses indeks dengan akses data, menghemat IO Anda untuk beban kerja yang sepenuhnya terikat disk

Ada pengoptimalan tertentu yang sedang dikerjakan yang akan meningkatkan kinerja akses indeks/pemindaian indeks. Misalnya, mengambil nilai indeks terlebih dahulu dan kemudian mengakses baris dalam urutan terurut bisa sangat membantu untuk pemindaian besar. Ini akan mengurangi kesenjangan, tapi saya ragu itu akan ditutup

Bergabung

Gabungan digunakan untuk menyusun objek kompleks yang sebelumnya dinormalisasi ke beberapa tabel, atau melakukan kueri kompleks untuk menemukan hubungan antar objek. Struktur yang dinormalisasi dan banyak gabungan adalah cara yang tepat untuk mendesain database Anda seperti yang diajarkan buku pelajaran, namun jika berurusan dengan kumpulan data besar, ini bisa menjadi resep bencana. Masalahnya bukan pada ukuran data; . Masalah ini ada untuk semua jenis aplikasi, namun, untuk aplikasi OLTP dengan kueri yang hanya memeriksa beberapa baris, masalahnya lebih kecil. Pengambilan data, pencarian, DSS, aplikasi intelijen bisnis yang perlu menganalisis banyak baris menjalankan agregat, dll. , adalah saat masalah ini paling dramatis

Beberapa gabungan juga lebih baik dari yang lain. Misalnya, jika Anda memiliki gabungan bintang dengan tabel dimensi yang kecil, itu tidak akan terlalu memperlambat segalanya. Di sisi lain, gabungan dari beberapa tabel besar, yang sepenuhnya terikat dengan disk, bisa sangat lambat

Salah satu alasan mengangkat masalah ini di MySQL adalah kurangnya metode gabungan lanjutan pada saat ini (pekerjaan sedang dalam perjalanan) - MySQL tidak dapat melakukan hash join atau sort-merge join - hanya dapat melakukan metode loop bersarang, yang mana

Ini contoh yang bagus. Seperti yang kita lihat, tabel 30mil baris (12GB) saya dipindai dalam waktu kurang dari 5 menit. Sekarang jika kita akan melakukan eq join dari tabel ke tabel baris 30mil lainnya, itu akan benar-benar acak. Kita harus melakukan 30 juta pembacaan baris acak, yang memberi kita 300.000 detik dengan laju 100 baris/detik. Jadi kami akan beralih dari 5 menit menjadi hampir 4 hari jika kami perlu bergabung. Beberapa orang menganggap bergabung akan mendekati dua pemindaian tabel penuh (karena 60 juta baris perlu dibaca) – tetapi ini salah

Jangan menganggap saya menentang normalisasi atau bergabung. Ini adalah prinsip yang bagus dan harus digunakan jika memungkinkan. Hanya saja, jangan lupa tentang implikasi kinerja yang dirancang ke dalam sistem dan jangan berharap bergabung menjadi gratis

Akhirnya saya harus menyebutkan satu lagi batasan MySQL yang mengharuskan Anda ekstra hati-hati bekerja dengan kumpulan data besar. Di MySQL, kueri tunggal berjalan sebagai utas tunggal (dengan pengecualian MySQL Cluster) dan MySQL mengeluarkan permintaan IO satu per satu untuk eksekusi kueri, yang berarti jika waktu eksekusi kueri tunggal menjadi perhatian Anda, banyak hard drive dan sejumlah besar CPU . Kadang-kadang merupakan ide bagus untuk membagi kueri secara manual menjadi beberapa proses secara paralel dan menggabungkan kumpulan hasil

Jadi, jika Anda berurusan dengan kumpulan data yang besar dan kueri yang rumit, berikut adalah beberapa tip

Cobalah untuk menyesuaikan kumpulan data yang sedang Anda kerjakan di memori – Pemrosesan di memori jauh lebih cepat dan Anda memiliki banyak masalah yang diselesaikan hanya dengan melakukannya. Gunakan beberapa server untuk menghosting bagian dari kumpulan data. Simpan sebagian data yang akan Anda kerjakan di tabel sementara, dll

Lebih suka pemindaian tabel lengkap daripada akses indeks – Untuk kumpulan data besar, pemindaian tabel lengkap seringkali lebih cepat daripada pemindaian rentang dan jenis pencarian indeks lainnya. Bahkan jika Anda melihat 1% dari baris atau kurang, pemindaian tabel lengkap mungkin lebih cepat

Hindari bergabung ke tabel besar Menggabungkan kumpulan data besar menggunakan loop bersarang sangat mahal. Cobalah untuk menghindarinya. Bergabung ke tabel yang lebih kecil tidak apa-apa, tetapi Anda mungkin ingin memuatnya terlebih dahulu ke memori sebelum bergabung sehingga tidak diperlukan IO acak untuk mengisi cache

Dengan arsitektur aplikasi dan desain tabel yang tepat, Anda dapat membangun aplikasi yang beroperasi dengan kumpulan data yang sangat besar berdasarkan MySQL

Bisakah MySQL menangani 1 juta catatan?

Bagaimana cara MySQL menangani data dalam jumlah besar?

Bagaimana Anda menangani jutaan catatan dalam database?

Bisakah MySQL menyimpan jutaan catatan?