mysql dalam atau kegagalan indeks

Pernyataan IN dalam MySQL ialah pernyataan pertanyaan yang sangat biasa digunakan, yang boleh menanyakan berbilang nilai sekali gus, contohnya:

PILIH * DARI jadual WHERE id IN (1,2,3,4 );

Pernyataan pertanyaan ini akan mengembalikan baris dengan ID 1, 2, 3 dan 4. Manfaat pernyataan IN ialah ia membolehkan kita menanyakan berbilang nilai sekaligus tanpa perlu menggunakan berbilang penyataan ATAU. Walau bagaimanapun, apabila terdapat banyak nilai dalam pernyataan IN, ia boleh menyebabkan kegagalan indeks, yang akan menjejaskan kecekapan pertanyaan secara serius.

Mari kita lihat mengapa kegagalan indeks berlaku dan cara untuk mengelakkannya.

1. Punca kegagalan indeks

Apabila menggunakan pernyataan IN, MySQL akan membandingkan setiap nilai secara individu, yang akan menyebabkan kegagalan indeks.

Andaikan kami mempunyai pesanan meja, yang mengandungi butiran pesanan Salah satu medan ialah customer_id, yang mewakili ID pelanggan pesanan. Kami ingin menanyakan semua pesanan dengan ID pelanggan 1, 2, 3 dan 4. Anda boleh menggunakan pernyataan pertanyaan berikut:

PILIH * DARI pesanan WHERE customer_id IN (1,2,3,4);

Jika terdapat indeks pada medan customer_id, MySQL akan menggunakan indeks ini untuk Membuat siasatan. Walau bagaimanapun, apabila MySQL perlu melakukan perbandingan berasingan untuk setiap nilai dalam pernyataan IN, ia perlu mengimbas keseluruhan indeks, yang akan menyebabkan indeks menjadi tidak sah.

Andaikan kami mempunyai 1,000 pesanan, termasuk 500 dengan ID pelanggan 1, 200 dengan ID pelanggan 2, 100 dengan ID pelanggan 3, dan 50 dengan ID pelanggan 4. Jika anda menggunakan pernyataan pertanyaan di atas, MySQL perlu mengimbas 500+200+100+50=850 baris keseluruhan indeks, yang jelas akan memberi kesan serius terhadap prestasi.

2. Bagaimana untuk mengelakkan kegagalan indeks

Untuk mengelakkan kegagalan indeks, kita boleh menggunakan beberapa teknik untuk menulis semula pernyataan pertanyaan. Berikut adalah beberapa teknik biasa:

2.1 Gunakan berbilang penyataan ATAU

Jika bilangan nilai dalam pernyataan IN adalah kecil, hanya sedikit, maka kita boleh menggunakan penyataan OR berbilang, contohnya:

PILIH * DARI pesanan WHERE customer_id = 1 ATAU customer_id = 2 ATAU customer_id = 3 ATAU customer_id = 4;

Ini akan memaksa MySQL menggunakan indeks untuk pertanyaan tanpa menyebabkan masalah prestasi . Walau bagaimanapun, pendekatan ini tidak boleh dilaksanakan jika terdapat banyak nilai dalam pernyataan IN.

2.2 Gunakan pernyataan EXISTS

Cara lain ialah menggunakan pernyataan EXISTS untuk bertanya:

PILIH *
DARI pesanan o
WHERE EXIST (
PILIH *
DARI (NILAI (1), (2), (3), (4)) SEBAGAI c(id_pelanggan)
DI MANA c.id_pelanggan = o.id_pelanggan
);

Pernyataan pertanyaan ini meletakkan nilai dalam pernyataan IN ke dalam subkueri, dan kemudian menggunakan pernyataan EXISTS untuk membuat pertanyaan. Kaedah ini boleh mengelakkan masalah kegagalan indeks yang disebabkan oleh pernyataan IN.

2.3 Gunakan jadual sementara

Kaedah lain ialah menggunakan jadual sementara untuk menyimpan nilai dalam pernyataan IN, dan kemudian gunakan pernyataan JOIN untuk membuat pertanyaan:

BUAT JADUAL SEMENTARA JIKA TIDAK WUJUD tmp_customer_ids (
customer_id INT PRIMARY KUNCI
);

MASUKKAN ABAIKAN KE DALAM tmp_customer_ids (customer_id)
NILAI (1), (2), (3), 4);

PILIH *
DARI pesanan o
SERTAI tmp_customer_ids tci ON tci.customer_id = o.customer_id;

Kaedah ini akan mencipta jadual sementara untuk menyimpan nilai dalam pernyataan IN, dan kemudian gunakan Kenyataan JOIN menghubungkan jadual sementara dengan jadual pesanan untuk pertanyaan. Kaedah ini boleh mengelakkan masalah kegagalan indeks yang disebabkan oleh kenyataan IN, dan ia juga lebih mudah untuk cache hasil pertanyaan (MySQL akan menanya cache JOIN kenyataan).

3. Ringkasan

Pernyataan IN ialah pernyataan pertanyaan yang sangat mudah, tetapi apabila terdapat banyak nilai dalam pernyataan IN, ia boleh menyebabkan kegagalan indeks dan menjejaskan dengan serius prestasi. Untuk mengelakkan masalah ini, kita boleh menggunakan berbilang penyataan OR, menggunakan penyataan EXISTS atau menggunakan jadual sementara untuk menulis semula penyataan pertanyaan. Kaedah ini boleh mengelakkan kegagalan indeks dan meningkatkan prestasi pertanyaan.

Atas ialah kandungan terperinci mysql dalam atau kegagalan indeks. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!