Gambaran
Ada banyak tutorial tentang pengoptimalan SQL, tetapi agak berantakan. Jadi saya meluangkan waktu untuk mengaturnya dan menuliskannya untuk dibagikan dengan Anda. Berikut ini terutama untuk database MySQL, database Oracle juga dapat dirujuk.Ringkasan teknik pengoptimalan
1. Buat indeks
Untuk mengoptimalkan kueri, untuk menghindari pemindaian tabel penuh sebanyak mungkin, pertama-tama pertimbangkan untuk membangun indeks pada kolom yang terlibat di mana dan diurutkan berdasarkan.
2. Hindari nol
Cobalah untuk menghindari penilaian nilai null dari bidang di klausa where
pilih id dari t dimana num adalah nullCara terbaik adalah tidak meninggalkan NULL dalam database, dan menggunakan NOT NULL untuk mengisi database sebanyak mungkin.
Keterangan, deskripsi, komentar, dll. Dapat disetel ke NULL. Untuk yang lain, sebaiknya tidak menggunakan NULL.
Jangan berpikir bahwa NULL tidak membutuhkan spasi. Misalnya: tipe char (100). Saat bidang dibuat, spasi akan tetap. Terlepas dari apakah nilai dimasukkan (NULL juga disertakan), itu akan menempati 100 karakter. Jika berupa varchar Untuk bidang dengan panjang variabel seperti itu, null tidak menempati ruang.
Anda dapat mengatur nilai default 0 on num untuk memastikan bahwa kolom num dalam tabel tidak memiliki nilai null, lalu kueri seperti ini:
pilih id dari t dimana num = 03. Hindari ketidaksetaraan
Cobalah untuk menghindari penggunaan! = Atau di klausa where < > Operator, jika tidak mesin akan menyerah menggunakan indeks dan melakukan pemindaian tabel lengkap.
4. Hindari atau
Anda harus mencoba untuk menghindari penggunaan atau dalam kondisi klausa where untuk bergabung. Jika sebuah field memiliki indeks dan sebuah field tidak memiliki indeks, itu akan menyebabkan mesin berhenti menggunakan indeks dan melakukan scan tabel lengkap, seperti:
pilih id dari t dengan num = 10 atau Name = 'admin'Anda dapat melakukan kueri seperti ini:
pilih id dari t dimana num = 10 serikat semua pilih id dari t dimana Name = 'admin'5. Gunakan di / tidak di dengan hati-hati
Dalam dan tidak dalam harus digunakan dengan hati-hati, jika tidak maka akan menyebabkan pemindaian tabel penuh, seperti:
pilih id dari t dimana num in (1,2,3)Untuk nilai berkelanjutan, jangan gunakan in jika Anda dapat menggunakan antara:
pilih id dari t dengan jumlah antara 1 dan 3Sering kali menggunakan ada, bukan di adalah pilihan yang baik:
pilih num dari a dimana num in (pilih num dari b)Gantikan dengan pernyataan berikut:
pilih num dari a dimana ada (pilih 1 dari b dimana num = a.num)6. Gunakan seperti dengan hati-hati
Kueri berikut juga akan menyebabkan pemindaian tabel lengkap:
pilih id dari t dengan nama seperti '% abc%'Untuk meningkatkan efisiensi, Anda dapat mempertimbangkan pencarian teks lengkap.
7. Hindari menggunakan parameter di klausa where
Jika Anda menggunakan parameter di klausa where, itu juga akan menyebabkan pemindaian tabel lengkap. Karena SQL hanya mem-parsing variabel lokal pada waktu proses, pengoptimal tidak dapat menunda pilihan rencana akses hingga waktu proses; ia harus memilih pada waktu kompilasi. Namun, jika rencana akses dibuat pada waktu kompilasi, nilai variabel masih belum diketahui dan oleh karena itu tidak dapat digunakan sebagai item masukan untuk pemilihan indeks. Seperti pernyataan berikut akan melakukan pemindaian tabel lengkap:
pilih id dari t dimana num = @numAnda dapat memaksa kueri untuk menggunakan indeks sebagai gantinya:
pilih id dari t dengan (indeks (nama indeks)) dimana num = @numCobalah untuk menghindari melakukan operasi ekspresi pada bidang di klausa where, yang akan menyebabkan mesin meninggalkan penggunaan indeks dan melakukan pemindaian tabel lengkap. Seperti:
pilih id dari t dimana num / 2 = 100Harus diubah menjadi:
pilih id dari t dimana num = 100 * 28. Hindari menambahkan fungsi ke bidang di klausa where
Harus mencoba untuk menghindari melakukan operasi fungsional pada bidang di mana klausa, yang akan menyebabkan mesin meninggalkan penggunaan indeks dan melakukan pemindaian tabel lengkap. Seperti:
pilih id dari t dengan substring (name, 1,3) = abc-ID yang namanya dimulai dengan abc pilih id dari t di mana dateiff (day, createate, 2005-11-30) = 0 - 2005-11-30 --- menghasilkan idHarus diubah menjadi:
pilih id dari t dengan nama seperti 'abc%' pilih id dari t tempat dibuat > = '2005-11-30' dan dibuat < '2005-12-1'9. Hindari menambahkan fungsi dan operasi di sebelah kiri "=" di klausa where
Jangan melakukan fungsi, operasi aritmatika, atau operasi ekspresi lainnya di sisi kiri "=" di klausa where, jika tidak sistem mungkin tidak dapat menggunakan indeks dengan benar.
10. Perhatikan urutan indeks
Saat menggunakan bidang indeks sebagai syarat, jika indeks adalah indeks majemuk, maka bidang pertama dalam indeks harus digunakan sebagai syarat untuk memastikan bahwa sistem menggunakan indeks, jika tidak indeks tidak akan digunakan, dan harus digunakan sebanyak mungkin Urutan bidang konsisten dengan urutan indeks.
11. Hindari menulis beberapa pertanyaan yang tidak berarti
Jangan menulis beberapa kueri yang tidak berarti, seperti membuat struktur tabel kosong:
pilih col1, col2 menjadi #t dari t dimana 1 = 0Jenis kode ini tidak akan mengembalikan kumpulan hasil apa pun, tetapi akan menggunakan sumber daya sistem, harus diubah menjadi ini:
buat tabel #t ()
12. Kurangi bidang pembaruan
Dalam pernyataan Perbarui, jika Anda hanya mengubah satu atau dua bidang, jangan perbarui semua bidang, jika tidak, panggilan yang sering akan menyebabkan konsumsi kinerja yang signifikan dan log dalam jumlah besar.
13. Paging dan kemudian bergabung
Untuk beberapa tabel BERGABUNG dengan sejumlah besar data (di sini ratusan di antaranya terlalu besar), Anda harus halaman pertama mereka dan kemudian BERGABUNG, jika tidak pembacaan logis akan sangat tinggi dan kinerjanya akan buruk.
14. Hindari penghitungan tanpa syarat (*)
pilih hitungan (*) dari tabel; hitungan ini tanpa kondisi apa pun akan menyebabkan pemindaian tabel penuh, dan tidak memiliki arti bisnis, itu harus dihilangkan.
15. Jangan membangun terlalu banyak indeks
Indeks tidak sebanyak mungkin. Meskipun pengindeksan dapat meningkatkan efisiensi pemilihan yang sesuai, namun juga mengurangi efisiensi penyisipan dan pembaruan, karena indeks dapat dibangun kembali selama penyisipan atau pembaruan, jadi cara membuat indeks perlu dipertimbangkan dengan cermat, bergantung pada spesifikasinya. Tergantung situasinya. Jumlah indeks tabel tidak boleh melebihi 6, jika terlalu banyak, Anda harus mempertimbangkan apakah perlu membangun indeks pada kolom yang tidak sering digunakan.
16. Hindari memperbarui kolom data indeks berkerumun
Anda harus menghindari memperbarui kolom data indeks berkerumun sebanyak mungkin, karena urutan kolom data indeks berkerumun adalah urutan penyimpanan fisik dari catatan tabel. Setelah nilai kolom ini berubah, urutan seluruh catatan tabel akan disesuaikan, yang akan menghabiskan banyak sumber daya. Jika sistem aplikasi perlu sering memperbarui kolom data indeks berkerumun, maka Anda perlu mempertimbangkan apakah indeks harus dibuat sebagai indeks berkerumun.
17. Coba gunakan kolom numerik
Cobalah untuk tidak mendesain bidang yang hanya berisi informasi numerik sebagai tipe karakter, yang akan mengurangi kinerja kueri dan koneksi serta meningkatkan overhead penyimpanan. Ini karena mesin membandingkan setiap karakter dalam string satu per satu saat memproses kueri dan koneksi, tetapi untuk tipe numerik, hanya perlu membandingkan satu kali.
18. Gunakan varchar / nvarchar sebagai ganti char / nchar sebanyak mungkin
Karena ruang penyimpanan bidang dengan panjang variabel kecil, ruang penyimpanan dapat dihemat, dan kedua, untuk kueri, efisiensi pencarian dalam bidang yang relatif kecil jelas lebih tinggi.
19. Hindari "*"
Jangan gunakan pilih * dari t di mana pun, ganti "*" dengan daftar bidang tertentu, dan jangan kembalikan bidang apa pun yang tidak digunakan.
20. Coba gunakan variabel tabel daripada tabel sementara
Jika variabel tabel berisi banyak data, perlu diketahui bahwa indeksnya sangat terbatas (hanya indeks kunci utama).
21. Hindari sering membuat dan menghapus tabel sementara
Kurangi frekuensi membuat dan menghapus tabel sementara untuk mengurangi konsumsi sumber daya tabel sistem. Tabel sementara tidak dapat digunakan, menggunakannya dengan tepat dapat membuat rutinitas tertentu lebih efektif, misalnya, saat Anda perlu berulang kali merujuk ke tabel besar atau kumpulan data dalam tabel yang umum digunakan. Namun, untuk acara satu kali, lebih baik menggunakan tabel ekspor.
22. Gunakan pilih ke daripada buat tabel
Saat membuat tabel sementara baru, jika Anda memasukkan sejumlah besar data sekaligus, Anda dapat menggunakan pilih ke daripada membuat tabel untuk menghindari sejumlah besar log dan meningkatkan kecepatan; jika jumlah data tidak besar, untuk memudahkan sumber daya tabel sistem, buat tabel terlebih dahulu , Lalu masukkan.
23. potong bukan hapus
Jika tabel sementara digunakan, semua tabel sementara harus dihapus secara eksplisit di akhir prosedur yang disimpan, tabel pemotongan pertama, dan kemudian tabel lepas, untuk menghindari penguncian jangka panjang tabel sistem.
24. Hindari menggunakan kursor
Karena efisiensi kursor buruk, jika data yang dioperasikan oleh kursor melebihi 10.000 baris, maka Anda harus mempertimbangkan untuk menulis ulang.
25. Solusi berbasis set, bukan kursor
Sebelum menggunakan metode berbasis kursor atau metode tabel sementara, Anda harus mencari solusi berbasis himpunan untuk mengatasi masalah tersebut, Metode berbasis himpunan biasanya lebih efektif.
26. Gunakan kursor FAST_FORWARD untuk kumpulan data kecil
Seperti tabel sementara, kursor tidak dapat digunakan. Menggunakan kursor FAST_FORWARD untuk kumpulan data kecil umumnya lebih baik daripada metode pemrosesan baris demi baris lainnya, terutama saat Anda harus mereferensikan beberapa tabel untuk mendapatkan data yang Anda butuhkan. Rutinitas yang menyertakan "total" dalam set hasil biasanya dijalankan lebih cepat daripada menggunakan kursor. Jika waktu pengembangan mengizinkan, Anda dapat mencoba metode berbasis kursor dan metode berbasis set untuk melihat metode mana yang bekerja lebih baik.
27. Setel NOCOUNT ON
Setel SET NOCOUNT ON di awal semua prosedur dan pemicu yang tersimpan, dan setel SET NOCOUNT ON di akhir. Tidak perlu mengirim pesan DONE_IN_PROC ke klien setelah menjalankan setiap pernyataan dari prosedur dan pemicu yang tersimpan.
28. Hindari operasi transaksi besar dan kembalikan data dalam jumlah besar ke klien
Untuk meningkatkan konkurensi, jika jumlah datanya terlalu besar, Anda harus mempertimbangkan apakah permintaan terkait masuk akal.
Editor akan membagikan lebih banyak konten pengoptimalan nanti, dan teman yang tertarik akan mengikuti gelombang perhatian ~
- Lin Zhiling mengirim Liu Ye ke wajah untuk membunuh, tidak takut dengan penampilan aslinya, Huang Xiaoming merasa malu
- Bagaimana cara menggunakan saklar batas, saklar fotolistrik dan saklar proximity? Kontrol otomatis sakelar perjalanan!
- Apakah ini layar penuh yang paling indah? iPhoneX versi ritel hitam dan putih dimulai sebelumnya: menumbangkan kognisi!
- Kontroversi tentang menciptakan nilai baru? Setidaknya 4 poin ini adalah perusahaan mobil tradisional tidak dapat mengikuti kekuatan baru
- Zhang Ting memaafkan Azi Lin Ruiyang karena berfoto dengan wajahnya dan memberikan jam tangan mewah kepada Azi, semua karena Azi telah menjual 400 juta dolar