Mengganggu penggunaan pengoptimuman MySQL bagi hash join

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql terutamanya cara untuk mengganggu penggunaan gabungan cincang oleh pengoptimum MySQL Artikel ini memperkenalkan anda dengan terperinci ia bersama-sama.

Pembelajaran yang disyorkan: tutorial video mysql

Kandungan asal komuniti GreatSQL tidak boleh digunakan tanpa kebenaran. Sila hubungi editor dan Tandakan sumbernya. GreatSQL ialah versi cawangan domestik MySQL, dan penggunaannya konsisten dengan MySQL.

Kata Pengantar

Pengoptimum pangkalan data adalah setara dengan otak manusia pada kebanyakan masa, ia boleh membuat keputusan yang betul, merumuskan rancangan pelaksanaan yang betul dan mencari jalan yang cekap, tetapi selepas semua, ia. adalah Penghakiman berdasarkan peraturan dan algoritma tetap tertentu kadangkala tidak sefleksibel seperti otak manusia kita. Apakah yang perlu kita lakukan apabila kita menentukan bahawa pengoptimum memilih rancangan pelaksanaan yang salah kaedah pengoptimuman.

Kami tahu bahawa Oracle menyediakan petunjuk yang lebih fleksibel untuk mengarahkan pengoptimum kaedah sambungan jadual yang hendak dipilih semasa menyambungkan berbilang jadual, seperti use_nl, no_use_nl untuk mengawal sama ada menggunakan Nest Loop Join, use_hash ,no_use_hashKawal sama ada hendak menggunakan hash join.

Tetapi MySQL hanya mempunyai satu kaedah sambungan jadual untuk masa yang lama, iaitu Nest Loop Join Hash join tidak muncul sehingga MySQL versi 8.0.18, jadi MySQL tidak memberikan begitu banyak petunjuk yang kaya untuk mengawal. Kaedah sambungan jadual. Petua yang kami gunakan, hash_join dan no_hash_join, hanyalah sepintas lalu kedua-dua jadual Apakah yang perlu saya lakukan jika saya menyertai?

Eksperimen

Mari kita lakukan percubaan dalam persekitaran tersendiri MySQL8.0.25. Buat dua jadual, masukkan 10,000 baris data masing-masing dan gunakan kunci utama untuk melaksanakan pertanyaan berkaitan antara kedua-dua jadual.

create table t1(id int primary key,c1 int,c2 int);
create table t2(id int primary key,c1 int,c2 int);
delimiter //
CREATE PROCEDURE p_test()
BEGIN
declare i int;
set i=1;
while i<10001 do
insert into t1 values(i,i,i);
insert into t2 values(i,i,i);
SET i = i + 1;
end while;
END;
//
delimiter ;

Soal pelan pelaksanaan sebenar apabila kedua-dua jadual menggunakan medan kunci utama untuk mengaitkan pertanyaan, seperti ditunjukkan dalam rajah di bawah:

Soal dua jadual menggunakan bukan- Pelan pelaksanaan sebenar apabila menanyakan medan indeks berkaitan adalah seperti yang ditunjukkan dalam rajah di bawah:

Seperti yang dapat dilihat daripada pelan pelaksanaan, terdapat ialah indeks pada medan berkaitan jadual didorong, dan pengoptimum ialah Apabila memilih kaedah sambungan jadual, Nest Loop Join diutamakan dan gabungan cincang diutamakan apabila tiada indeks tersedia.

Berdasarkan ini, kita boleh menggunakan gesaan no_index untuk melarang pernyataan daripada menggunakan indeks medan yang berkaitan.

Dapat dilihat daripada pelan pelaksanaan di atas bahawa selepas menggunakan gesaan no_index, pengoptimum memilih untuk menggunakan gabungan cincang.

Apabila selektiviti indeks tidak baik, pengoptimum memilih untuk menggunakan indeks untuk melakukan Nest Loop Join, yang sangat tidak cekap.

Kami akan menukar data dalam lajur c1 daripada dua jadual dalam percubaan untuk menjadikannya kurang selektif dan membina indeks biasa pada lajur c1.

update t1 set c1=1 where id<5000;
update t2 set c1=1 where id<5000;
create index idx_t1 on t1(c1);
create index idx_t2 on t2(c1);

Apabila kami melaksanakan sql:

select t1.*,t2.* from t1 join t2 on t1.c1=t2.c1;

Hasil pertanyaan ini akan mengembalikan sejumlah besar data Selektiviti indeks lajur c1 medan yang berkaitan bagi jadual didorong adalah lemah Pada masa ini, memilih gabungan cincang ialah pilihan yang lebih bijak, tetapi pengoptimum akan memilih untuk menggunakan Nest Loop Join. Kami boleh mengesahkan perbezaan prestasi antara gabungan cincang dan Nest Loop Join melalui percubaan.

Dapat dilihat bahawa penggunaan masa penggunaan hash join ialah 1/6 daripada penggunaan Nest Loop Join, tetapi apabila pengoptimum menganggarkan berdasarkan kos, kos penggunaan Nest Loop Join adalah lebih tinggi daripada Kos menggunakan hash join jauh lebih rendah, jadi saya akan memilih Nest Loop Join Pada masa ini, anda perlu menambah pembayang untuk melarang penggunaan indeks pada medan yang berkaitan masa pada jadual didorong adalah sangat tinggi, jadi pengoptimuman ini Selepas anggaran pemproses, ia akan memilih untuk melakukan gabungan cincang.

Dokumentasi rasmi MySQL menyebut menggunakan petua BNL dan NO_BNL untuk menjejaskan pengoptimuman gabungan cincang Walau bagaimanapun, percubaan telah membuktikan bahawa apabila tiada indeks tersedia pada medan sambungan jadual yang berkaitan, pengoptimum menganggarkan kos pada masa hadapan, jadual terdorong tidak akan menggunakan imbasan jadual penuh BNL untuk melakukan cantuman gelung bersarang, tetapi akan memilih untuk menggunakan cantuman cincang, jadi NO_BNL tidak akan digunakan dalam senario ini.

Jadi memandangkan kita tidak memerlukan indeks ini, tidakkah kita boleh mengalih keluarnya sahaja? Mengapa kita perlu menggunakan petunjuk no_index? Kita perlu tahu bahawa terdapat banyak senario penggunaan perniagaan di sini masa, kelebihan pembayang diserlahkan Anda hanya perlu mengawal Hanya gunakan pernyataan ini.

Ringkasan

Nest Loop Join mempunyai kelebihannya Ia merupakan kaedah sambungan terpantas untuk respons dan sesuai untuk senario di mana jumlah data yang dikembalikan adalah kecil. Apabila dua jadual besar disambungkan dan sejumlah besar data dikembalikan, dan indeks medan yang berkaitan agak tidak cekap, ia akan menjadi lebih cekap untuk menggunakan gabungan cincang Kita boleh menggunakan petunjuk no_index untuk melumpuhkan indeks tidak cekap yang berkaitan medan, menggesa pengoptimum untuk memilih gabungan cincang .

Pembelajaran yang disyorkan: tutorial video mysql

Atas ialah kandungan terperinci Mengganggu penggunaan pengoptimuman MySQL bagi hash join. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!