Pemilihan pengoptimasi dan indeks berasaskan kos mysql

Asas teras untuk pengoptimuman pertanyaan MySQL untuk memilih indeks ialah model kos berasaskan kos (CBO), yang menentukan penyelesaian optimum dengan menilai kos laluan pelaksanaan yang berlainan. 1. Pengoptimal akan mempertimbangkan faktor -faktor seperti baris pengimbasan, halaman membaca, sama ada untuk kembali ke jadual, sama ada menggunakan sorting atau jadual sementara. 2. Sebab -sebab umum untuk indeks yang tidak dipilih termasuk: pengagihan data yang tidak sekata atau maklumat statistik yang tidak tepat, mengakibatkan anggaran kardinaliti yang salah; Kos jadual sandaran terlalu tinggi, dan pengoptimum percaya bahawa pengimbasan meja penuh lebih cekap; Penulisan pertanyaan menjadikan indeks tidak sah, seperti menggunakan fungsi, pemadanan kabur terkemuka, atau beberapa indeks NO dalam keadaan atau. 3. Adalah disyorkan untuk menjalankan jadual menganalisis dengan kerap, mengelakkan pengindeksan dalam bidang pembahagian rendah, menubuhkan indeks liputan untuk mengurangkan kembali ke jadual, menulis SQL dengan munasabah, dan gunakan Jelaskan untuk menganalisis pelan pelaksanaan. Proses keputusan pengoptimuman dapat dilihat melalui menjelaskan analisa atau mengoptimumkan jejak, dan jika perlu, indeks daya dapat digunakan untuk memaksa indeks, tetapi reka bentuk dan statistik harus dioptimumkan terlebih dahulu.

Apabila memilih indeks, pengoptimuman pertanyaan MySQL terutamanya bergantung pada model kos berasaskan kos (CBO) . Ia menilai "kos" pelan pelaksanaan yang berbeza dan kemudian memilih penyelesaian yang dianggapnya optimum. Tetapi kadang -kadang anda akan mendapati bahawa walaupun terdapat indeks, MySQL tidak memerlukannya, dan juga memilih pengimbasan meja penuh. Alasan di sebalik ini sebenarnya berkaitan dengan bagaimana kos anggaran CBO.

Apakah pengoptimuman berasaskan kos?

Pengoptimuman MySQL tidak hanya melihat sama ada terdapat indeks, tetapi melihat indeks mana yang "lebih kos efektif". Ia mengira kos setiap laluan pelaksanaan yang mungkin melalui beberapa data yang dianggarkan, seperti:

• Berapa banyak baris data yang diimbas?
• Berapa banyak halaman yang perlu saya baca?
• Adakah saya perlu mengembalikan meja?
• Adakah jadual penyortiran atau sementara digunakan?

Ini akan menjejaskan pelan pelaksanaan akhir. Dalam erti kata lain, walaupun anda menambah indeks, pengoptimal mungkin tidak merasakan ia berguna , kerana dari pengiraannya, lebih perlahan untuk melalui indeks.

Mengapa indeks tidak dipilih? Alasan umum

1. Pengagihan data yang tidak sekata atau maklumat statistik yang tidak tepat

MySQL menggunakan statistik yang disediakan oleh enjin penyimpanan untuk menganggarkan kos pertanyaan. Sebagai contoh, InnoDB mengekalkan kardinaliti indeks. Jika nilai ini tidak tepat, pengoptimum boleh membuat pertimbangan yang salah.

Contohnya:
Anda mempunyai indeks pada medan jantina (hanya nilai lelaki/wanita). Apabila pengoptimum melihat bahawa selektiviti indeks ini terlalu miskin, lebih baik untuk mengimbas seluruh jadual secara langsung.

Cadangan:

• Jalankan ANALYZE TABLE secara berkala untuk mengemas kini statistik.
• Jangan indeks medan rendah.

2. Pengoptimuman menganggarkan bahawa harga pulangan ke meja terlalu tinggi

Walaupun beberapa pertanyaan memukul indeks, mereka memerlukan sejumlah besar pengembalian jadual untuk mendapatkan data sebenar. Pada masa ini, pengoptimal boleh menyerah menggunakan indeks ini dan mengimbas secara langsung melalui indeks kluster (indeks utama utama).

Contohnya:

 Pilih * dari pengguna di mana umur> 30;

Jika anda telah mengindeks age tetapi mempunyai banyak data yang memenuhi syarat -syarat, pengoptimal akan mendapati bahawa anda perlu kembali ke meja berkali -kali, jadi lebih baik untuk mengimbas kunci utama secara langsung dan datang dengan cepat.

Cadangan:

• Jika anda sering hanya mencari medan tertentu, anda boleh mempertimbangkan untuk mewujudkan indeks overlay untuk mengelakkan sokongan meja.
• Untuk pertanyaan pelbagai, perhatikan sama ada jumlah data terlalu besar.

3. Indeks itu sendiri tidak sesuai untuk pertanyaan semasa

Kadang -kadang anda membuat indeks, tetapi pertanyaan ditulis supaya ia tidak dapat digunakan. Contohnya:

• Gunakan fungsi atau ungkapan: WHERE YEAR(create_time) = 2023
• Padanan Fuzzy Sebelum Wildcard: WHERE name LIKE '%Tom'
• Syarat pertanyaan digunakan atau, salah satunya tidak mempunyai indeks

Keadaan semacam ini akan membatalkan indeks dan secara semulajadi tidak memasukkan senarai calon pengoptimuman.

Cadangan:

• Cuba elakkan beroperasi di medan semasa menulis kenyataan pertanyaan.
• Gunakan EXPLAIN lebih kerap untuk memeriksa pelan pelaksanaan dan sahkan sama ada indeks itu benar -benar digunakan.

Bagaimana untuk melihat proses keputusan pengoptimasi?

Anda boleh mengetahui bagaimana MySQL memilih rancangan pelaksanaan dengan cara berikut:

• Gunakan EXPLAIN atau EXPLAIN ANALYZE (MySQL 8.0) untuk melihat pelan pelaksanaan.
• Dayakan fungsi Trace Optimizer dan lihat proses keputusan dalaman pengoptimuman:

 Tetapkan optimizer_trace = "enabled = on";
Pilih ...; - pertanyaan anda pilih * dari maklumat_schema.optimizer_trace;

Dengan cara ini, kita dapat melihat butir -butir tentang berapa banyak laluan pelaksanaan yang dinilai pengoptimum, anggaran kos setiap laluan, dan butiran lain.

Petua: Dipaksa menggunakan indeks?

Jika anda yakin bahawa indeks lebih baik tetapi pengoptimum tidak dipilih, anda boleh memaksanya menggunakan FORCE INDEX :

 Pilih * dari Pengguna Force Index (IDX_AGE) di mana umur> 30;

Tetapi sedar bahawa ini hanya memintas penghakiman pengoptimuman dan tidak semestinya lebih baik. Adalah disyorkan untuk menyelesaikan masalah dari reka bentuk dan maklumat statistik dalam jangka masa panjang .

Pada dasarnya itu sahaja. CBO adalah pintar, tetapi ia juga bergantung pada ketepatan data dan sama ada penulisan SQL anda mesra. Apabila indeks tidak berkuatkuasa, jangan tergesa -gesa meragui MySQL. Pertama, lihat jika anda telah meninggalkannya.

Atas ialah kandungan terperinci Pemilihan pengoptimasi dan indeks berasaskan kos mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!