Bagaimana untuk mengoptimumkan pernyataan gabungan dalam MySQL

Simple Nested-Loop Join

Mari kita lihat cara mysql berfungsi semasa melakukan operasi gabungan. Apakah kaedah gabungan biasa?

Seperti yang ditunjukkan dalam rajah, apabila kita melakukan operasi sambungan, jadual di sebelah kiri ialah jadual pemandu, dan jadual di sebelah kanan ialah jadual dipandu

Simple Nested-Loop Join Operasi gabungan ini adalah untuk mengambil rekod daripada jadual pemanduan dan kemudian memadankan rekod jadual dipandu satu demi satu Jika keadaannya sepadan , hasilnya akan dikembalikan. Kemudian, teruskan padankan rekod seterusnya dalam jadual pemacu sehingga semua data dalam jadual pemacu telah dipadankan

Kerana mengambil data dari jadual pemacu setiap kali memakan masa, MySQL tidak gunakan algoritma ini Untuk melaksanakan operasi cantum

Block Nested-Loop Join

Untuk mengelakkan pengambilan data yang memakan masa daripada pemandu jadual setiap kali, kita boleh menambah kumpulan Data diambil dari jadual pemacu sekali gus dan dipadankan dalam ingatan. Selepas kumpulan data ini dipadankan, kumpulan data diambil dari jadual pemacu dan diletakkan dalam memori sehingga semua data dalam jadual pemacu dipadankan

Pendapatan semula data kelompok boleh mengurangkan banyak operasi IO, jadi kecekapan pelaksanaan adalah agak tinggi Operasi sambungan jenis ini juga digunakan oleh MySQL

Dengan cara ini, memori ini mempunyai nama yang betul dalam MySQ, dipanggil join buffer saiz penimbal gabungan

show variables like '%join_buffer%'

Alih keluar jadual jadual_tunggal yang kami gunakan sebelum ini, buat 2 jadual berdasarkan jadual jadual_tunggal, masukkan rekod rawak 1w ke dalam setiap jadual

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

create table t1 like single_table;
create table t2 like single_table;

Jika anda menggunakan pernyataan join secara langsung, pengoptimuman MySQL Pelayan boleh memilih jadual t1 atau t2 sebagai jadual pemacu, yang akan menjejaskan proses kami menganalisis pernyataan sql, jadi kami menggunakan straight_join untuk membenarkan mysql menggunakan kaedah sambungan tetap untuk melaksanakan pertanyaan

select * from t1 straight_join t2 on (t1.common_field = t2.common_field)

Masa berjalan ialah 0.035s

Pelan pelaksanaan adalah seperti berikut

Menggunakan penimbal sertai dilihat dalam lajur Tambahan, menunjukkan bahawa operasi sambungan adalah berdasarkan Sekat Bersarang -Loop Join Algoritma

Index Nested-Loop Join

Selepas memahami algoritma Block Nested-Loop Join, anda dapat melihat bahawa setiap rekod dalam jadual pemacu akan Memadankan semua rekod dalam jadual didorong adalah sangat memakan masa Bolehkah kecekapan pemadanan dalam jadual didorong dapat dipertingkatkan ?

Saya rasa anda juga telah memikirkan algoritma ini, iaitu menambah indeks pada lajur yang disambungkan oleh jadual yang didorong, supaya proses pemadanan sangat pantas, seperti yang ditunjukkan dalam rajah

Mari kita lihat betapa pantasnya melakukan pertanyaan berdasarkan gabungan berdasarkan lajur indeks?

select * from t1 straight_join t2 on (t1.id = t2.id)

Masa pelaksanaan ialah 0.001 saat dapat dilihat bahawa ia lebih daripada satu tahap lebih cepat daripada bergabung berdasarkan lajur biasa

Pelan pelaksanaan. adalah seperti berikut

Bukan semua lajur rekod jadual pemacu akan dimasukkan ke dalam penimbal gabungan Hanya lajur dalam senarai pertanyaan dan lajur dalam penapis syarat akan dimasukkan ke dalam penimbal gabungan, jadi Kami tidak mahu menggunakan * sebagai senarai pertanyaan, kami hanya perlu meletakkan lajur yang kami minati dalam senarai pertanyaan, supaya lebih banyak rekod boleh diletakkan dalam penimbal gabungan

Bagaimana untuk memilih meja pemandu?

Sekarang kita tahu pelaksanaan khusus gabungan, mari kita bincangkan tentang soalan biasa, iaitu, bagaimana untuk memilih jadual pemacu?

Jika ia adalah algoritma Block Nested-Loop Join:

• Apabila penimbal sambung cukup besar, tidak kira siapa yang memandu jadual

• Apabila penimbal gabungan tidak cukup besar, anda harus memilih jadual kecil sebagai jadual pemacu (jadual kecil mempunyai kurang data dan bilangan kali ia dimasukkan ke dalam cantuman penimbal adalah kecil, yang mengurangkan bilangan imbasan jadual)

Jika ia adalah algoritma Index Nested-Loop Join

Andaikan bahawa bilangan baris dalam jadual pemacu ialah M, jadi M baris jadual pemacu perlu diimbas

setiap kali dari Apabila mendapatkan baris data daripada jadual dipacu, anda perlu mencari indeks a dahulu, dan kemudian cari indeks kunci utama. Bilangan baris dalam jadual didorong ialah N. Anggaran kerumitan mencari pokok setiap kali ialah logaritma asas 2 N, jadi kerumitan masa mencari baris pada jadual didorong ialah 2&rendah; l o g 2 N 2*log2^N 2&rendah;log2N

Setiap baris data dalam jadual pemacu mesti dicari sekali dalam jadual didorong Anggaran kerumitan keseluruhan proses pelaksanaan ialah M + M & paling rendah 2 & paling rendah; log2N

Jelas sekali M mempunyai kesan yang lebih besar pada bilangan baris yang diimbas, jadi meja kecil harus digunakan sebagai meja pemanduan. Sudah tentu, premis kesimpulan ini ialah indeks jadual didorong boleh digunakan

Ringkasnya, kita boleh menggunakan jadual kecil sebagai jadual pemanduan

Apabila pernyataan gabungan dilaksanakan dengan perlahan, kita boleh mengoptimumkannya melalui kaedah berikut

• Apabila melakukan operasi sambungan, anda boleh menggunakan indeks jadual dipandu

• meja kecil sebagai meja pemanduan

• Tingkatkan saiz penimbal gabungan

• Jangan gunakan * sebagai senarai pertanyaan, hanya kembalikan lajur yang diperlukan

Atas ialah kandungan terperinci Bagaimana untuk mengoptimumkan pernyataan gabungan dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!