Pembelajaran lanjutan MySQL: pemahaman mendalam tentang tiga algoritma gabungan

Artikel ini adalah kajian lanjutan tentang MySQL Ia akan memperkenalkan prinsip sambungan gabungan dan tiga algoritma gabungan secara terperinci.

Kami sering menggunakan gabungan untuk menyambungkan berbilang jadual apabila menanyakan berbilang jadual jadual Untuk padanan gelung, MySQL hanya menyokong satu algoritma sambung, Nested-Loop Join (sambungan bersarang gelung), tetapi ia mempunyai pelbagai variasi algoritma, yang sebenarnya meningkatkan kecekapan pelaksanaan gabungan. [Cadangan berkaitan: tutorial video mysql]

1. Simple Nested-Loop Join (gabungan gelung bersarang ringkas)

Algoritma Simple Nested-Loop join (NLJ) membaca satu baris pada satu masa daripada jadual pertama dalam gelung, menghantar setiap baris ke gelung bersarang yang sepadan sama ada data itu konsisten. Sebagai contoh, sql pengguna jadual pemanduan dan UserInfo jadual dipandu ialah

, yang sebenarnya adalah logik bagi kod pseudo itu ialah select * from User u left join User_info info on u.id = info.user_id

for(User u:Users){
    for(UserInfo info:UserInfos){
        if(u.id == info.userId){
            // 得到匹配数据
        }
    }
}

algoritma kasar, setiap kali daripada jadual Pengguna Keluarkan sekeping data, kemudian imbas semua rekod dalam User_info untuk pemadanan, dan akhirnya gabungkan data dan kembalikannya.

Jika Pengguna jadual pemanduan mempunyai 10 keping data, dan UserInfo jadual dipandu juga mempunyai 10 keping data, maka Pengguna jadual pemanduan sebenarnya akan diimbas 10 kali, dan jadual dipandu akan diimbas 10* 10=100 kali (Setiap kali jadual pemacu diimbas, semua jadual dipandu akan diimbas).

Setiap imbasan sebenarnya membaca data dari cakera keras dan memuatkannya ke dalam memori, yang merupakan IO pada masa ini adalah kesesakan terbesar

<.>

2. Index Nested-Loop Join (gabungan gelung bersarang indeks)Gelung bersarang indeks menggunakan indeks untuk mengurangkan bilangan imbasan untuk meningkatkan kecekapan, jadi ia tidak memerlukan -pemandu Mesti ada indeks di atas meja.

Apabila membuat pertanyaan, jadual pemacu (Pengguna) akan membuat pertanyaan berdasarkan indeks medan yang berkaitan Apabila nilai yang sepadan ditemui pada indeks, pertanyaan jadual akan dilakukan. Jika medan yang berkaitan (user_id) bagi jadual bukan didorong (User_info) ialah kunci utama, kecekapan pertanyaan akan menjadi sangat tinggi (nod daun struktur indeks kunci utama mengandungi data baris yang lengkap (InnoDB) Jika ya bukan kunci utama, indeks akan dipadankan setiap kali Akhirnya, pertanyaan pemulangan jadual diperlukan (pertanyaan pemulangan jadual dilakukan berdasarkan ID kunci utama indeks sekunder (indeks bukan kunci utama)), dan prestasinya adalah pasti lebih lemah daripada pertanyaan kunci utama.

Pertanyaan indeks dalam gambar di atas mungkin tidak semestinya mengembalikan jadual dalam keadaan apa Ini bergantung pada sama ada medan yang ditanya oleh indeks boleh bertemu medan yang diperlukan oleh pertanyaan untuk butiran, sila rujuk artikel sebelumnya:

Beberapa asas indeks dan pengetahuan indeks B-tree yang anda perlu tahu

3. Block Nested-Loop Join (cache block Nested loop connection)Jika terdapat indeks, kaedah indeks akan digunakan untuk bergabung Jika lajur gabungan tidak mempunyai indeks, jadual didorong perlu diimbas terlalu banyak kali Setiap kali Apabila mengakses jadual didorong, rekod dalam jadual akan dimuatkan ke dalam memori, dan kemudian rekod diambil dari jadual pemandu untuk memadankannya , memori dikosongkan, dan kemudian rekod dimuatkan dari jadual pemacu dan rekod jadual didorong ialah Padanan dimuatkan ke dalam ingatan, dan ini berlaku berulang kali, meningkatkan bilangan IO dengan banyak. Untuk mengurangkan bilangan IO pada jadual terdorong, kaedah Block Nested-Loop Join telah muncul.

Ia tidak lagi memperoleh data jadual pemacu satu demi satu, tetapi memperolehnya sekeping demi sekeping Penampan gabungan diperkenalkan untuk cache beberapa lajur data yang berkaitan dengan cantuman jadual pemacu (saiznya ialah had penimbal cantum) kepada penimbal, dan kemudian imbas jadual terdorong secara keseluruhannya Setiap rekod dalam jadual terdorong dipadankan dengan semua rekod jadual pemacu dalam penimbal sambung sekali (operasi dalam memori). berbilang perbandingan dalam gelung bersarang mudah digabungkan menjadi satu , mengurangkan kekerapan capaian jadual bukan didorong.

Sama ada jadual pemacu boleh dimuatkan sekali gus bergantung pada sama ada penimbal gabungan boleh menyimpan semua data secara lalai

, Penimbal Sertaan akan cache semua peserta apabila menanyakan lajur pertanyaan dan bukannya hanya menyertai lajur, penimbal gabungan N-1 akan diperuntukkan dalam SQL dengan persatuan gabungan N. Oleh itu, semasa membuat pertanyaan, cuba kurangkan medan yang tidak diperlukan supaya lebih banyak lajur boleh disimpan dalam penimbal gabungan.

join_buffer_size=256kSaiz cache join_buffer_size boleh dilaraskan

Nilai ini boleh ditukar mengikut situasi sebenar.

show variables like '%join_buffer%'

Untuk menggunakan algoritma Block Nested-Loop Join, anda perlu menghidupkan tetapan optimizer_switch bagi konfigurasi pengurusan pengoptimum block_nested_loop kepada hidup, yang didayakan secara lalai. Anda boleh menyemak status show variables like '%optimizer_switch%' melalui block_nested_loop.

Memahami ketiga-tiga algoritma di atas sebenarnya, dalam kerja sebenar, selagi kita boleh menggunakan indeks dengan baik, ia akan menjadi baik menyertai sambungan, kita mesti memberi perhatian kepada sama ada medan yang berkaitan ditetapkan masih perlu mahir menggunakan indeks untuk meningkatkan kecekapan pertanyaan.

Alamat asal: https://juejin.cn/post/7014105037517357093

Pengarang: En. Ji

Untuk lebih banyak pengetahuan berkaitan pengaturcaraan, sila layari :Pengenalan kepada Pengaturcaraan! !

Atas ialah kandungan terperinci Pembelajaran lanjutan MySQL: pemahaman mendalam tentang tiga algoritma gabungan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!