Bagaimanakah MySQL mengoptimumkan dalam subquery?

Dalam pembangunan sebenar, kami sering menggunakan subkueri dalam, yang bersamaan dengan set nilai untuk memadankan medan yang ditentukan, membolehkan kami menapis dan membuat pertanyaan dengan lebih mudah. Walau bagaimanapun, kaedah subkueri ini akan menyebabkan masalah prestasi yang hebat apabila jumlah data adalah besar. Artikel ini akan memperkenalkan cara MySQL mengoptimumkan dalam subquery.

1 Elakkan menggunakan dalam subkueri

Dalam projek sebenar, kita sering melihat cara penulisan ini:

SELECT *
FROM table
WHERE col1 IN (SELECT col1 FROM table2 WHERE condition);

Pernyataan ini adalah yang paling mudah dalam Subkueri, berdasarkan keadaan jadual2, mendapatkan semula nilai col1 dalam berbilang baris, memadankan nilai dalam jadual dan mengembalikan baris yang sepadan. Walau bagaimanapun, menulis dengan cara ini akan menyebabkan kesesakan prestasi, kerana cara MySQL secara dalaman melaksanakan dalam subquery akan cache set hasil subquery dalam memori (atau cakera Selepas itu, setiap kali dalam penghakiman dilaksanakan, memori (atau cakera) akan dicache ), ini akan menyebabkan sejumlah besar operasi I/O, dan apabila set hasil subquery adalah besar, ia juga akan menduduki sejumlah besar memori.

Oleh itu, cuba elakkan menggunakan dalam subquery dalam pembangunan sebenar, anda boleh menggunakan join sebagai gantinya.

2. Gunakan join untuk menggantikan dalam subquery

Gunakan join untuk menggantikan dalam subquery dan penulisan subquery asal dalam subquery menjadi gabungan untuk mengoptimumkan sintaks SQL Itu sahaja, dan kecekapan pelaksanaan adalah lebih tinggi daripada dalam subquery. Cari col1, dan kemudian sambungkannya dengan col1 dalam jadual1, seperti yang ditunjukkan di bawah:

SELECT table.*
FROM table
JOIN table2 ON table.col1 = table2.col1
WHERE table2.condition;

Berbanding dengan subkueri dalam, menggunakan join boleh menyambungkan set hasil subkueri dengan jadual, yang mengurangkan banyak memori (cakera) operasi baca.

3. Penggunaan wujud bukannya dalam subkueri

Penggunaan wujud bukannya dalam subkueri sebenarnya menggunakan gabungan. Tidak seperti subquery in, subquery wujud hanya perlu melakukan pertimbangan mudah, tanpa mengira saiz set hasil. Berikut ialah contoh sintaks subkueri wujud:

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition);

Penggunaan wujud dan bukannya dalam subkueri telah meningkatkan kecekapan dan boleh menjimatkan banyak penggunaan I/O dan memori.

4. Gunakan indeks untuk mengoptimumkan penyataan dalam

Jika anda boleh menggunakan indeks untuk mempercepatkan dalam subkueri semasa pertanyaan, kecekapan pertanyaan juga akan bertambah baik. Indeks MySQL dibahagikan kepada tiga jenis: indeks kunci utama, indeks unik dan indeks biasa Jika anda boleh mencipta indeks yang sesuai, anda boleh mengelakkan MySQL daripada melakukan imbasan jadual penuh dan meningkatkan kecekapan pertanyaan.

CREATE INDEX idx_col1 ON table (col1);

Apabila nilai col1 besar, menggunakan indeks akan mengoptimumkan kecekapan pertanyaan dan mengurangkan masalah kecekapan yang disebabkan oleh penggunaan dalam subkueri.

5 Gunakan had dan wujud untuk mengoptimumkan dalam subkueri

Jika set hasil dalam subkueri adalah sangat besar, kami boleh menggunakan had dan wujud untuk melaksanakan pertanyaan paging padanya sambil mengelakkan imbasan jadual penuh , untuk mencapai tujuan mengoptimumkan kecekapan pertanyaan.

SELECT *
FROM table
WHERE EXISTS (SELECT 1 FROM table2 WHERE table.col1 = table2.col1 AND table2.condition LIMIT 1000, 20);

Fungsi pernyataan SQL ini adalah untuk mencari set hasil jadual2, dan kemudian perform wujud dengan jadual melalui col1, hadkan set hasil pertanyaan kepada 20 dan pertanyaan daripada baris ke-1000.

6. Penggunaan pengoptimuman memori yang sesuai dalam pernyataan

Jika hasil subkueri yang digunakan dalam pertanyaan tidak mempunyai banyak baris, kita boleh menggunakan set dan bukannya in. set menyimpan set hasil subquery dalam memori untuk padanan pertanyaan berikutnya Menggunakan memori untuk mengoptimumkan pernyataan dalam juga boleh meningkatkan prestasi.

SET @col1 = (SELECT GROUP_CONCAT(DISTINCT col1) FROM table2 WHERE condition);
SELECT *
FROM table
WHERE FIND_IN_SET(table.col1, @col1);

Pernyataan ini mula-mula menggunakan pilih untuk pemadanan data, dan kemudian menggunakan GROUP_CONCAT untuk menggabungkan senarai nilai col1 ke dalam rentetan, yang disimpan dalam @col1. Dalam pertanyaan seterusnya, FIND_IN_SET digunakan untuk pemadanan dan cache memori digunakan untuk mengoptimumkan kecekapan pertanyaan.

7. Ringkasan

Apabila menggunakan dalam subkueri, pastikan anda mengelakkan imbasan jadual penuh, terutamanya apabila jumlah data adalah besar, jika tidak, ia akan menyebabkan masalah prestasi yang serius. Dengan menyertai, wujud, mengoptimumkan indeks, menggunakan had dengan sewajarnya, menggunakan memori dan kaedah lain, anda boleh meningkatkan kecekapan pertanyaan dan mengoptimumkan prestasi dalam subkueri. Dalam projek sebenar, kita harus memilih penyelesaian terbaik mengikut situasi khusus untuk mencapai kesan pengoptimuman prestasi terbaik.

Atas ialah kandungan terperinci Bagaimanakah MySQL mengoptimumkan dalam subquery?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!