Pengoptimum pertanyaan pangkalan data berjanji untuk memilih pelan pelaksanaan terpantas secara automatik untuk pertanyaan anda, tetapi ujian terbaru mendedahkan bahawa mereka sering gagal mencapai matlamat ini. Walaupun sistem ini berfungsi dengan baik untuk senario mudah, mereka kerap bergelut dengan corak data dunia sebenar, yang membawa kepada isu prestasi yang boleh mengejutkan pembangun.
Realiti Di Sebalik Pengoptimuman Pertanyaan
Ujian prestasi terkini merentasi taburan data yang berbeza menunjukkan bahawa pengoptimum pertanyaan secara konsisten memilih pelan yang tidak optimum. Ujian tersebut melibatkan pertanyaan SELECT mudah dengan syarat julat, namun operasi asas ini pun mendedahkan jurang ketara antara apa yang dipilih oleh pengoptimum dan apa yang sebenarnya berjalan paling pantas. Masalah ini menjadi lebih ketara dengan pertanyaan kompleks yang melibatkan berbilang jadual dan cantuman.
Isu teras terletak pada cara pengoptimum membuat keputusan. Mereka bergantung pada ringkasan statistik data anda dan model kos yang dipermudahkan untuk menganggar prestasi. Walau bagaimanapun, statistik ini semestinya merupakan perwakilan yang tidak lengkap bagi corak data sebenar. Pangkalan data dunia sebenar mengandungi korelasi, pengelompokan, dan corak taburan yang tidak dapat dirakam sepenuhnya dalam ringkasan statistik yang padat.
Corak Prestasi Pelan Pertanyaan:
- Data seragam: Imbasan indeks dipilih untuk selektiviti 1-5%, tetapi imbasan bitmap sebenarnya menunjukkan prestasi yang lebih baik
- Data kitaran: Perancang berterusan dengan imbasan indeks walaupun pada selektiviti rendah di mana bitmap akan menjadi optimal
- Data linear: Hanya set data linear yang sempurna secara konsisten menghasilkan pemilihan pelan yang optimal
- Kesan cache hangat: Perbezaan prestasi antara kaedah imbasan sebahagian besarnya hilang dengan data yang di-cache
 |
|---|
| Perwakilan visual tentang kekerapan pengoptimum pertanyaan memilih pelan pelaksanaan yang optimum, menonjolkan jurang prestasi dalam pelaksanaan pertanyaan |
Kekecewaan Komuniti Terhadap Kebolehramalan Pelan
Profesional pangkalan data semakin bersuara mengenai ketidakbolehramalan pengoptimum. Ada yang berpendapat bahawa perancang pertanyaan moden telah menjadi terlalu bijak untuk kebaikan mereka sendiri, mewujudkan situasi di mana perubahan kecil dalam data atau struktur pertanyaan boleh mengubah prestasi secara dramatik. Ketidakbolehramalan ini menjadi sangat bermasalah dalam persekitaran pengeluaran di mana prestasi yang konsisten lebih penting daripada pengoptimuman teori.
Komuniti PostgreSQL menghadapi cabaran khusus kerana pangkalan data tersebut tidak membenarkan pengguna memaksa pelan pelaksanaan atau indeks tertentu. Walaupun falsafah reka bentuk ini bertujuan untuk menganggap pelan yang tidak optimum sebagai pepijat yang perlu diperbaiki, ia meninggalkan pembangun dengan pilihan terhad apabila pengoptimum membuat pilihan yang buruk. Sambungan seperti pg_hint_plan memberikan sedikit kelegaan, tetapi ia bukan sebahagian daripada sistem teras.
Ia menjadi terlalu kompleks, terlalu tidak dapat diramal. Setiap pertanyaan dan pembolehubah menjadi kejutan dalam pengeluaran.
Batasan Kawalan Pelan PostgreSQL:
- Tiada keupayaan asli untuk memaksa indeks khusus atau pelan pelaksanaan
- Bendera berskop sesi menyediakan kawalan kasar tetapi mungkin memberi kesan kepada pertanyaan lain
- CTE yang dimaterialkan menawarkan halangan pengoptimuman tetapi boleh menghalang pengoptimuman yang diingini
- Sambungan pihak ketiga pg_hint_plan menyediakan keupayaan pemaksaan pelan
- RDS Aurora PostgreSQL dilaporkan menyokong pelan pertanyaan yang diuruskan
Komplikasi Perkakasan dan Persekitaran
Persekitaran pengkomputeran moden menambah satu lagi lapisan kerumitan kepada pengoptimuman pertanyaan. Model kos yang dibangunkan untuk sistem tradisional di premis mungkin tidak menggambarkan ciri prestasi sistem storan awan, tatasusunan NVMe , atau persekitaran berkontena dengan tepat. Ciri seperti prapengambilan, yang boleh meningkatkan prestasi secara dramatik untuk corak capaian tertentu, tidak selalu diambil kira dengan betul dalam pengiraan kos pengoptimum.
Ujian menunjukkan bahawa imbasan bitmap secara konsisten mengatasi prestasi imbasan indeks disebabkan oleh keupayaan prapengambilan, namun pengoptimum sering memilih pendekatan imbasan indeks yang lebih perlahan. Pengoptimuman peringkat perkakasan ini boleh mengubah sepenuhnya landskap prestasi dengan cara yang tidak dapat dirakam oleh model kos tradisional.
Spesifikasi Persekitaran Ujian:
- Perkakasan: Pemproses AMD Ryzen 9300X dengan storan NVMe RAID
- Keadaan cache: Ujian cache sejuk (tiada data dalam cache halaman atau buffer berkongsi)
- Jenis pertanyaan: Pertanyaan SELECT ringkas dengan syarat WHERE julat tunggal
- Taburan data yang diuji: Seragam, kitaran dengan fuzz, linear dengan pelbagai tahap fuzz
Pendekatan Alternatif dan Penyelesaian Sementara
Sesetengah pembangun menyokong pendekatan pertanyaan yang lebih deterministik, terutamanya untuk pertanyaan yang dijana aplikasi di mana kebolehramalan mengalahkan pengoptimuman teori. Teknik seperti menggunakan Common Table Expressions ( CTEs ) sebagai penghalang pengoptimuman, melaraskan tetapan perancang peringkat sesi, atau menyusun semula pertanyaan boleh memberikan lebih kawalan ke atas pelan pelaksanaan.
Sistem pangkalan data peringkat tinggi menawarkan pemprosesan pertanyaan adaptif, yang membolehkan enjin melaraskan pendekatannya semasa pelaksanaan berdasarkan data prestasi sebenar. Walau bagaimanapun, ciri ini tidak tersedia secara universal dan menambah kerumitan tersendiri kepada pengurusan pangkalan data.
Cabaran asas kekal: pengoptimuman pertanyaan pada dasarnya adalah masalah mampatan lossy. Pengoptimum mesti membuat keputusan pantas berdasarkan maklumat terhad, dan pelan sempurna untuk semua senario yang mungkin mungkin mustahil secara matematik untuk dicapai dalam masa dan kekangan sumber yang munasabah.
Rujukan: How often is the query plan optimal?