Pengendali MySQL 'IN' untuk mengendalikan nombor yang besar

Question

Saya memerhati tingkah laku pelik yang cuba saya fahami.

Versi MySQL: 5.7.33 Saya mempunyai pertanyaan berikut:

select * from a_table where time>='2022-05-10' and guid in (102,512,11,35,623,6,21,673);

Indeks pada

a_table 在 time、guid 上有主键，在 guid

Pertanyaan yang saya tulis di atas mempunyai prestasi yang sangat baik dan mengikut pelan terangkan ia adalah using index condition;使用地点；使用MRR

Apabila saya meningkatkan bilangan nilai dalam klausa in, prestasi terjejas dengan ketara.

Selepas beberapa latihan, saya mendapat nombor kasar. Untuk nilai kurang daripada ~14500, skema tafsiran adalah sama seperti di atas. Untuk kuantiti di atas ini, hanya pelan yang dijelaskan 使用 where dan ia mengambil masa selama-lamanya untuk menjalankan pertanyaan saya.

Dalam erti kata lain, jika saya meletakkan 14,000 nilai dalam klausa in 子句中放入 14,000 个值，则解释计划将具有预期的 14,000 行。但是，如果我在 in, contohnya, pelan explain akan mempunyai 14,000 baris yang dijangkakan. Walau bagaimanapun, jika saya meletakkan 15,000 nilai dalam klausa

, penjelasannya mempunyai 221200324 baris. Saya tidak mempunyai banyak baris dalam keseluruhan meja saya.

Saya cuba memahami tingkah laku ini dan mengetahui jika ada cara untuk membetulkannya.

Terima kasih🎜

Answer 1

Fahami menghadkan memori untuk pengoptimuman julat.

Apabila IN() terdapat sejumlah besar nilai dalam predikat, ia menggunakan lebih banyak memori semasa langkah pengoptimuman pertanyaan. Ini telah dianggap sebagai masalah dalam beberapa kes, jadi versi terbaru MySQL menetapkan had memori maksimum (lalai ialah 8MB).

Jika pengoptimum mendapati bahawa ia memerlukan lebih banyak memori daripada had, tiada syarat lain dalam pertanyaan untuk dioptimumkan, ia berhenti mencuba untuk mengoptimumkan dan menggunakan imbasan jadual. Saya menyimpulkan bahawa statistik jadual anda sebenarnya menunjukkan bahawa jadual mempunyai kira-kira 221 juta baris (walaupun statistik jadual adalah anggaran yang tidak tepat).

Saya tidak boleh mengatakan saya tahu formula tepat untuk berapa banyak memori yang diperlukan oleh senarai nilai tertentu, tetapi berdasarkan tingkah laku yang anda perhatikan, kami boleh meneka bahawa mengambil kira 14k item, purata kira-kira 600 bait setiap item berfungsi, tetapi lebih banyak tidak berfungsi.

Anda boleh menetapkan range_optimizer_max_mem_size = 0 untuk melumpuhkan had memori. Ini mewujudkan risiko overcommiting memori, tetapi ia mengelakkan pengoptimum "berputus asa". Kami menetapkan nilai ini pada semua contoh MySQL dalam tugas kami sebelum ini kerana kami tidak dapat mendidik pembangun untuk mengelak daripada membuat senarai besar nilai dalam pertanyaan mereka.