Cara menulis pernyataan pertanyaan SQL yang berkualiti tinggi dan berprestasi tinggi

1. Pertama, kita mesti faham apa itu pelan pelaksanaan?

Pelan pelaksanaan ialah pelan pertanyaan yang dibuat oleh pangkalan data berdasarkan maklumat statistik penyata SQL dan jadual berkaitan Pelan ini dianalisis secara automatik oleh pengoptimum pertanyaan Sebagai contoh, jika penyataan SQL digunakan ekstrak 100,000 rekod Untuk mencari 1 rekod dalam jadual, pengoptimum pertanyaan akan memilih kaedah "carian indeks" Jika jadual diarkibkan dan hanya tinggal 5,000 rekod, pengoptimum pertanyaan akan menukar pelan dan menggunakan "imbasan jadual penuh. " "Cara.

Dapat dilihat bahawa pelan pelaksanaan tidak tetap, ia "diperibadikan". Terdapat dua perkara penting dalam menjana "pelan pelaksanaan" yang betul:

(1) Adakah penyataan SQL memberitahu pengoptimum pertanyaan apa yang ingin dilakukan dengan jelas?

(2) Adakah statistik pangkalan data yang diperoleh oleh pengoptimum pertanyaan adalah terkini dan betul?

2. Cara bersatu untuk menulis pernyataan SQL

Untuk dua pernyataan SQL berikut, pengaturcara berpendapat ia adalah sama, tetapi pengoptimum pertanyaan pangkalan data berpendapat ia berbeza.

select*from dual

select*From dual

Malah, kes ini berbeza Penganalisis pertanyaan menganggapnya sebagai dua pernyataan SQL yang berbeza dan mesti dihuraikan dua kali . Hasilkan 2 rancangan pelaksanaan. Oleh itu, sebagai pengaturcara, anda harus memastikan bahawa pernyataan pertanyaan yang sama adalah konsisten di mana-mana, malah satu lagi ruang tidak akan berfungsi!

3 Isu yang perlu diberi perhatian semasa menulis pernyataan SQL

Berikut ialah pengenalan terperinci kepada beberapa isu yang perlu diberi perhatian semasa menulis pernyataan SQL. Dalam klausa ini, walaupun terdapat indeks untuk beberapa lajur, disebabkan penulisan SQL yang tidak berkualiti, sistem tidak boleh menggunakan indeks semasa menjalankan pernyataan SQL, dan juga menggunakan imbasan jadual penuh, yang menghasilkan kelajuan tindak balas yang sangat perlahan pengurangan.

1.IS NULL and IS NOT NULL

Tidak boleh menggunakan null sebagai indeks dan mana-mana lajur yang mengandungi nilai null tidak akan dimasukkan ke dalam indeks. Walaupun indeks mempunyai berbilang lajur, selagi salah satu lajur ini mengandungi nol, lajur akan dikecualikan daripada indeks. Maksudnya, jika terdapat nilai nol dalam lajur, walaupun membina indeks pada lajur tidak akan meningkatkan prestasi.

Sebarang pernyataan yang menggunakan adalah batal atau tidak batal dalam klausa yang tidak dibenarkan menggunakan indeks oleh pengoptimum pernyataan.

2. Elakkan menggunakan jenis data yang tidak serasi.

Jenis data yang tidak serasi mewakili penukaran jenis data perolehan jadual penuh, dan akses akan menjadi imbasan jadual penuh

pilih * daripada pekerja di mana last_name = 100 not last_name ialah jenis varchar

3 Sertai lajur

Untuk lajur dengan cantuman, walaupun nilai gabungan akhir ialah nilai statik, pengoptimum tidak akan menggunakan indeks. Mari kita lihat contoh. Katakan terdapat jadual pekerja (nama pertama dan terakhir pekerja disimpan dalam dua lajur (FIRST_NAME dan LAST_NAME).

Berikut ialah pernyataan SQL menggunakan pertanyaan sertai,

pilih * daripada employss where first_name||''||last_name ='Beill Cliton';

Di atas pernyataan Pernyataan itu boleh menanyakan sepenuhnya sama ada terdapat pekerja bernama Bill Cliton, tetapi perlu diperhatikan di sini bahawa pengoptimum sistem tidak menggunakan indeks yang dibuat berdasarkan nama_akhir.

Apabila pernyataan SQL berikut ditulis, sistem Oracle boleh menggunakan indeks yang dibuat berdasarkan last_name.

*** di mana first_name ='Beill' dan last_name ='Cliton';

4. Pernyataan serupa bermula dengan aksara wildcard (%)

Permintaan semasa ialah seperti ini, meminta untuk menanyakan jadual pekerja untuk orang yang namanya mengandungi cliton. Anda boleh menggunakan pernyataan SQL pertanyaan berikut:

pilih * daripada pekerja di mana last_name seperti '%cliton%' Memandangkan aksara wildcard (%) muncul pada permulaan perkataan carian, sistem Oracle tidak digunakan indeks nama_keluarga. Walau bagaimanapun, apabila kad bebas muncul di tempat lain dalam rentetan, pengoptimum boleh mengambil kesempatan daripada indeks. Indeks digunakan dalam pertanyaan berikut:

pilih * daripada pekerja di mana nama akhir seperti 'c%'

5 Melakukan operasi pada medan indeks akan membatalkan indeks.

Cuba elakkan melaksanakan fungsi atau operasi ekspresi pada medan dalam klausa WHERE, yang akan menyebabkan enjin berhenti menggunakan indeks dan melakukan imbasan jadual penuh.

cth: PILIH * DARI T1 DI MANA F1/2=100 harus ditukar kepada: PILIH * DARI T1 DI MANA F1=100*2

6 ORDER Pernyataan BY menentukan cara Oracle mengisih hasil pertanyaan yang dikembalikan. Penyata Susunan mengikut tidak mempunyai sekatan khas pada lajur yang hendak diisih, dan fungsi juga boleh ditambah pada lajur (seperti cantuman atau tambahan, dsb.). Sebarang item bukan indeks atau ungkapan yang dikira dalam Susunan mengikut pernyataan akan memperlahankan pertanyaan.

Semak pesanan mengikut penyata dengan teliti untuk item atau ungkapan yang tidak diindeks, yang boleh mengurangkan prestasi. Penyelesaian kepada masalah ini adalah dengan menulis semula susunan demi pernyataan untuk menggunakan indeks Anda juga boleh membuat indeks lain untuk lajur yang digunakan Pada masa yang sama, anda harus mengelak daripada menggunakan ungkapan dalam susunan mengikut klausa.

7. BUKAN

Kami sering menggunakan beberapa ungkapan logik dalam klausa where apabila membuat pertanyaan, seperti lebih besar daripada, kurang daripada, sama dengan, tidak sama dengan, dll. Kami juga boleh menggunakan dan (dan), atau (atau) dan bukan (tidak). NOT boleh digunakan untuk menafikan sebarang tanda operasi logik. Berikut ialah contoh klausa NOT:

... di mana tidak (status ='SAH')

Jika anda ingin menggunakan NOT, anda hendaklah meletakkan kurungan di hadapan frasa yang dinafikan dan operator NOT di hadapan frasa. Operator NOT terkandung dalam operator logik yang lain, yang tidak sama dengan operator (<>). Dalam erti kata lain, walaupun perkataan NOT tidak ditambahkan secara eksplisit pada klausa where pertanyaan, NOT masih dalam operator, lihat contoh berikut:

... di mana status <>'INVALID ';

Pertanyaan ini boleh ditulis semula tanpa menggunakan NOT:

pilih * daripada pekerja di mana gaji<3000 atau gaji>3000;

Walaupun keputusan kedua-dua pertanyaan ini adalah sama, Pelan pertanyaan kedua akan lebih pantas daripada pelan pertanyaan pertama. Pertanyaan kedua membenarkan Oracle menggunakan indeks pada lajur gaji, manakala pertanyaan pertama tidak boleh menggunakan indeks.

8. IN dan WUJUD

Kadangkala lajur dibandingkan dengan satu siri nilai. Cara paling mudah ialah menggunakan subquery dalam klausa where. Dua format subkueri boleh digunakan dalam klausa where.

Format pertama ialah menggunakan operator IN:

... di mana lajur masuk (pilih * dari ... di mana ...); Formatnya ialah menggunakan operator EXIST:

... di mana wujud (pilih 'X' dari ...di mana ...); Saya percaya bahawa kebanyakan orang akan menggunakan yang pertama satu format, kerana ia lebih mudah untuk menulis, dan format kedua sebenarnya jauh lebih cekap daripada format pertama. Dalam Oracle, hampir semua subkueri operator IN boleh ditulis semula sebagai subkueri menggunakan EXISTS.

Dalam format kedua, subquery bermula dengan 'pilih 'X'. Menggunakan klausa EXISTS tidak kira apa data yang diekstrak subquery daripada jadual, ia hanya melihat klausa where. Dengan cara ini, pengoptimum tidak perlu melintasi keseluruhan jadual dan boleh menyelesaikan kerja berdasarkan indeks sahaja (ini mengandaikan bahawa lajur yang digunakan dalam pernyataan where mempunyai indeks). Berbanding dengan klausa IN, EXISTS menggunakan subkueri bersambung, yang lebih sukar untuk dibina daripada subkueri IN.

Dengan menggunakan EXIST, sistem Oracle menyemak pertanyaan utama dahulu dan kemudian menjalankan subkueri sehingga ia menemui padanan pertama, yang menjimatkan masa. Apabila sistem Oracle melaksanakan subkueri IN, ia mula-mula melaksanakan subkueri dan menyimpan senarai hasil yang diperoleh dalam jadual sementara yang diindeks. Sebelum melaksanakan subquery, sistem terlebih dahulu menggantung pertanyaan utama Selepas subquery selesai, ia disimpan dalam jadual sementara dan kemudian pertanyaan utama dilaksanakan. Inilah sebabnya mengapa menggunakan EXISTS adalah lebih pantas daripada menggunakan IN untuk pertanyaan umum.

Pada masa yang sama, NOT EXISTS harus digunakan dan bukannya NOT IN sebanyak mungkin Walaupun kedua-duanya menggunakan NOT (indeks tidak boleh digunakan untuk mengurangkan kelajuan), NOT EXISTS adalah lebih cekap daripada pertanyaan NOT IN .

9 Cuba elakkan menggunakan atau dalam klausa di mana untuk menyambung keadaan, jika tidak enjin akan berhenti menggunakan indeks dan melakukan imbasan jadual penuh,

Contohnya: pilih id daripada pekerja. di mana num= 10 atau num=20

Anda boleh membuat pertanyaan seperti ini: pilih id daripada pekerja di mana num=10 kesatuan semua pilih id daripada pekerjadi mana num=20

10 dalam klausa where Menjalankan operasi ungkapan

akan menyebabkan enjin ditinggalkan menggunakan indeks dan melakukan imbasan jadual penuh. Contohnya: pilih id daripada t di mana num/2=100 harus ditukar kepada: pilih id daripada t di mana num=100*2

11 Cuba elakkan melakukan operasi berfungsi pada medan dalam klausa where

Ini akan menyebabkan enjin ditinggalkan menggunakan indeks dan melakukan imbasan jadual penuh. Contohnya: pilih id daripada t di mana subrentetan(nama,1,3)='abc', id yang namanya bermula dengan abc hendaklah ditukar kepada:

pilih id daripada t di mana nama seperti 'abc%'

12. Jangan lakukan fungsi, operasi aritmetik atau operasi ungkapan lain di sebelah kiri "=" dalam klausa where, jika tidak, sistem mungkin tidak dapat menggunakan indeks dengan betul.

13 Apabila menggunakan medan indeks sebagai syarat, jika indeks adalah indeks komposit, medan pertama dalam indeks mesti digunakan sebagai syarat untuk memastikan sistem menggunakan indeks, jika tidak, indeks akan. not akan digunakan, dan susunan medan hendaklah selaras dengan susunan indeks sebanyak mungkin.

14 Lebih banyak indeks, lebih baik

Walaupun indeks boleh meningkatkan kecekapan pilihan yang sepadan, ia juga mengurangkan kecekapan sisipan dan kemas kini, kerana mungkin terdapat ralat semasa memasukkan atau mengemas kini . Bina semula indeks, jadi cara membina indeks perlu dipertimbangkan dengan teliti dan bergantung pada situasi tertentu. Adalah lebih baik untuk tidak mempunyai lebih daripada 6 indeks pada jadual Jika terdapat terlalu banyak, anda harus mempertimbangkan sama ada perlu untuk membina indeks pada beberapa lajur yang tidak biasa digunakan.

15 Cuba gunakan medan angka Jika medan yang mengandungi maklumat berangka sahaja, cuba jangan reka bentuknya sebagai medan aksara Ini akan mengurangkan prestasi pertanyaan dan sambungan, dan meningkatkan overhed storan. Ini kerana enjin akan membandingkan setiap aksara dalam rentetan satu demi satu apabila memproses pertanyaan dan sambungan, dan hanya satu perbandingan yang mencukupi untuk jenis angka.

16. Gunakan varchar/nvarchar dan bukannya char/nchar sebanyak mungkin, kerana pertama sekali, medan panjang boleh ubah mempunyai ruang storan yang kecil dan boleh menjimatkan ruang storan bidang kecil Jelas sekali lebih tinggi.

17 Jangan gunakan pilih * dari mana-mana sahaja, gantikan "*" dengan senarai medan tertentu dan jangan kembalikan sebarang medan yang tidak digunakan.

Atas ialah kandungan terperinci Cara menulis pernyataan pertanyaan SQL yang berkualiti tinggi dan berprestasi tinggi. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!