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)

, 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){
            // 得到匹配数据
        }
    }
}

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:

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

join_buffer_size=256kSaiz cache join_buffer_size boleh dilaraskan

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!