Bercakap secara ringkas tentang pertanyaan sertai dalam MySQL

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan isu berkaitan tentang pertanyaan sertai. Saya harap ia dapat membantu semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

Kesan indeks pada pertanyaan penyertaan

Data persediaan

Andaikan terdapat dua jadual t1 dan t2 kedua-dua jadual mempunyai id indeks kunci utama dan medan indeks a dan medan b tanpa indeks Kemudian masukkan 100 baris data ke dalam jadual t1 dan t2 Sisipkan 1000 baris data untuk percubaan

CREATE TABLE `t2` (
 `id` int NOT NULL,
 `a` int DEFAULT NULL,
 `b` int DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `t2_a_index` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE PROCEDURE **idata**()
BEGIN
  DECLARE i INT;
  SET i = 1;
  WHILE (i <h3 data-id="heading-3"><strong>Dengan proses pertanyaan indeks</strong></h3><p>Kami menggunakan pertanyaan SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a ); Oleh kerana pengoptimum pertanyaan MYSQL mungkin tidak dapat dilaksanakan mengikut kehendak kami, jadi untuk analisis kami memilih untuk menggunakan STRAIGHT_JOIN sebaliknya untuk memerhati dengan lebih intuitif </p><p><bercakap secara ringkas tentang pertanyaan sertai dalam mysql src="https://Bercakap%20secara%20ringkas%20tentang%20pertanyaan%20sertai%20dalam%20MySQL.php.cn/upload/article/000/000/067/64b9ac3a02f2e5095eba59bdfbcd23d6-2.png" alt="图 1" loading="lazy"></bercakap> Rajah 1</p><p> Ia dapat dilihat bahawa kita menggunakan t1 sebagai jadual pemacu dan t2 sebagai jadual terdorong Penjelasan dalam rajah di atas menunjukkan bahawa pertanyaan ini menggunakan medan indeks jadual t2, jadi proses pelaksanaan pernyataan ini adalah seperti berikut: </p>

• Baca satu baris data r daripada jadual t1

• Dapatkan medan a daripada data r ke jadual t2 untuk dipadankan

• Keluarkan baris yang memenuhi syarat dalam jadual t2, dan bentuk baris dengan r sebagai sebahagian daripada set hasil

• Ulang langkah 1-3 sehingga jadual t1 gelung data

Proses ini dipanggil Index Nested-Loop Join Dalam proses ini, jadual pemacu t1 melakukan imbasan jadual penuh Kerana kami memasukkan 100 baris data ke dalam jadual t1 , kali ini Bilangan baris yang diimbas ialah 100. Apabila melakukan pertanyaan gabungan, setiap baris jadual t1 perlu dicari dalam jadual t2 Carian pepohon indeks digunakan kerana data yang kami bina mempunyai satu ke-. satu surat menyurat, setiap carian Hanya satu baris diimbas, iaitu, sejumlah 100 baris diimbas dalam jadual t2 Jumlah bilangan baris yang diimbas semasa keseluruhan proses pertanyaan ialah 100 100=200 baris.

Tiada proses pertanyaan indeks

SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a = t2.b);

Rajah 2

Ia boleh dilihat kerana tiada indeks pada medan B jadual t2, jadi mengikut Apabila SQL di atas dilaksanakan, imbasan jadual penuh mesti dilakukan setiap kali t1 sepadan dengan t2 Dengan cara ini, t2 mesti diimbas sehingga 100 kali, dan jumlah imbasan ialah 100*1000 =. 100,000 baris.

Sudah tentu, hasil pertanyaan ini masih berdasarkan fakta bahawa dua jadual yang kami bina adalah kecil. Jika ia adalah jadual dengan 100,000 baris, 10 bilion baris perlu diimbas.

2 Fahami Sekat Bersarang-Loop Join

Sekat Nested-Loop Joinproses pertanyaan

Jadi tiada indeks pada jadual yang dipandu, bagaimana semua ini berlaku?

Malah, apabila tiada indeks tersedia pada jadual yang dipacu, aliran algoritma adalah seperti berikut:

• Baca data t1 ke dalam memori benang join_buffer, kerana kami menulis di atas adalah pilih * daripada, jadi ia bersamaan dengan meletakkan keseluruhan jadual t1 ke dalam memori

• Proses mengimbas t2 sebenarnya mengeluarkan setiap baris t2 dan memadankan; ia dengan yang ada dalam join_buffer Data tersebut dibandingkan dan yang memenuhi syarat gabungan dikembalikan sebagai sebahagian daripada set hasil.

Jadi digabungkan dengan bahagian Tambahan dalam Rajah 2, menggunakan penimbal gabungan, kita boleh mencari petunjuk ini semasa keseluruhan proses, kedua-dua jadual t1 dan t2 selesai. Imbasan jadual penuh, jadi bilangan baris yang diimbas ialah 100 1000 = 1100 baris Kerana join_buffer disusun dalam tatasusunan yang tidak tersusun, jadi untuk setiap baris dalam jadual t2, 100 pertimbangan mesti dibuat, dan sejumlah 100. pertimbangan mesti dibuat dalam ingatan Bilangan penghakiman adalah 100*1000=100,000 kali, tetapi kerana 100,000 kali ini berlaku dalam ingatan, kelajuannya lebih pantas dan prestasinya lebih baik.

Join_buffer

Seperti yang anda ketahui dari atas, tanpa indeks, MySQL membaca data ke dalam memori untuk pertimbangan gelung, jadi ingatan ini pastinya tidak terhad kepada Anda gunakan, maka kita perlu menggunakan parameter join_buffer_size, saiz lalai nilai ialah 256k, seperti yang ditunjukkan di bawah:

SHOW VARIABLES LIKE '%join_buffer_size%';

Rajah 4

Jika data pertanyaan terlalu besar untuk dimuatkan sekaligus, dan hanya sebahagian daripada data (80 item) boleh dimuatkan, maka proses pertanyaan menjadi seperti berikut

• 扫描表 t1，顺序读取数据行放入 join_buffer 中，直至加载完第 80 行满了

• 扫描表 t2，把 t2 表中的每一行取出来跟 join_buffer 中的数据做对比，将满足条件的数据作为结果集的一部分返回

• 清空 join_buffer

• 继续扫描表 t1，顺序读取剩余的数据行放入 join_buffer 中，执行步骤 2

这个流程体现了算法名称中 Block 的由来，分块 join，可以看出虽然查询过程中 t1 被分成了两次放入 join_buffer 中，导致 t2 表被扫描了 2次，但是判断等值条件的次数还是不变的，依然是（80+20）*1000=10 万次。

所以这就是有时候 join 查询很慢，有些大佬会让你把 join_buffer_size 调大的原因。

如何正确的写出 join 查询

驱动表的选择

• 有索引的情况下

在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

假设被驱动表的行数是 M，每次在被驱动表查询一行数据，先要走索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2为底的 M的对数，记为 log2M，所以在被驱动表上查询一行数据的时间复杂度是 2*log2M。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上 匹配一次。因此整个执行过程，近似复杂度是 N + N2log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

• 那没有索引的情况

上述我知道了，因为 join_buffer 因为存在限制，所以查询的过程可能存在多次加载 join_buffer，但是判断的次数都是 10 万次，这种情况下应该怎么选择？

假设，驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M。这里的 K不是常数，N 越大 K就越大，因此把 K 表示为λ*N，显然λ的取值范围 是 (0,1)。

扫描的行数就变成了 N+λNM，显然内存的判断次数是不受哪个表作为驱动表而影响的，而考虑到扫描行数，在 M和 N大小确定的情况下，N 小一些，整个算是的结果会更小，所以应该让小表作为驱动表

总结：真相大白了，不管是有索引还是无索引参与 join 查询的情况下都应该是使用小表作为驱动表。

什么是小表

还是以上面表 t1 和表 t2 为例子：

SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.b = t2.b WHERE t2.id <p>上面这两条 SQL 我们加上了条件 t2.id </p><p>再看另一组：</p><pre class="brush:php;toolbar:false">SELECT t1.b,t2.* FROM t1 STRAIGHT_JOIN t2 ON t1.b = t2.b WHERE t2.id <p>这个例子里，表 t1 和 t2 都是只有 100 行参加 join。 但是，这两条语句每次查询放入 join_buffer 中的数据是不一样的: 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，只需要放入字段 b 的值; 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字 段 id、a 和 b。</p><p>这里，我们应该选择表 t1 作为驱动表。也就是说在这个例子里，”只需要一列参与 join 的 表 t1“是那个相对小的表。</p><p>结论：</p><p>在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过 滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”， 应该作为驱动表。</p><p>推荐学习：<a href="https://www.php.cn/course/list/51.html" target="_blank" textvalue="mysql视频教程">mysql视频教程</a></p>

Atas ialah kandungan terperinci Bercakap secara ringkas tentang pertanyaan sertai dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!