Setelah pengujian, kueri bersyarat dilakukan pada tabel yang berisi lebih dari 4 juta rekaman, dan waktu kueri setinggi 40 detik. Oleh karena itu, cara meningkatkan efisiensi kueri pernyataan SQL sangat penting. Berikut ini adalah beberapa metode pengoptimalan pernyataan kueri yang beredar luas di Internet:
Pertama-tama, ketika volume data besar, Anda harus mencoba menghindari pemindaian tabel lengkap, dan mempertimbangkan untuk membangun indeks pada kolom yang terlibat dalam di mana dan urutkan, yang dapat sangat mempercepat pengambilan data. Namun, ada beberapa situasi di mana pengindeksan tidak berfungsi:
1. Cobalah untuk menghindari penggunaan operator != atau <> dalam klausa where, jika tidak, mesin akan meninggalkan penggunaan indeks dan melakukan pemindaian tabel penuh.
2. Cobalah untuk menghindari penilaian nilai nol pada bidang dalam klausa where, jika tidak, mesin akan meninggalkan penggunaan indeks dan melakukan pemindaian tabel penuh, seperti:
Pilih id dari t di mana num adalah null
Anda dapat mengatur nilai default 0 pada num, pastikan tidak ada nilai null di kolom num dalam tabel, lalu kueri seperti ini:
Pilih ID dari t di mana num=0
3. Cobalah untuk menghindari penggunaan OR dalam klausa where untuk menggabungkan kondisi, jika tidak, mesin akan meninggalkan penggunaan indeks dan melakukan pemindaian tabel penuh, seperti:
Pilih ID dari t di mana num=10 atau num=20
Anda dapat melakukan kueri seperti ini:
Pilih ID dari t di mana num=10
serikat semua
Pilih ID dari t di mana num=20
4. Kueri berikut juga akan menghasilkan pemindaian tabel penuh:
Pilih ID dari t di mana nama seperti '%abc%'
Untuk meningkatkan efisiensi, pertimbangkan pencarian teks lengkap.
5. Di dalam dan tidak di juga harus digunakan dengan hati-hati, jika tidak maka akan menyebabkan pemindaian meja penuh, seperti:
Pilih ID dari t di mana num in(1,2,3)
Untuk nilai berkelanjutan, jika Anda dapat menggunakan antara, jangan gunakan di:
Pilih ID dari t di mana num antara 1 dan 3
6. Jika Anda menggunakan parameter dalam klausa where, itu juga akan menyebabkan tabel lengkap dipindai. Karena SQL hanya menyelesaikan variabel lokal saat runtime, tetapi pengoptimal tidak dapat menunda pemilihan paket akses ke runtime; Itu harus dipilih 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 input untuk pemilihan indeks. Pernyataan berikut akan dipindai secara lengkap:
Pilih ID dari t di mana num=@num
Anda dapat memaksa kueri untuk menggunakan indeks sebagai gantinya:
Pilih ID dari t dengan(indeks(indeks(nama indeks)) di mana num=@num
7. Cobalah untuk menghindari mengekspresikan bidang dalam klausa where, yang akan menyebabkan mesin meninggalkan penggunaan indeks dan melakukan pemindaian tabel penuh. Sebagai contoh:
Pilih ID dari t di mana num/2=100
harus diubah menjadi:
Pilih ID dari t di mana num=100*2
8. Cobalah untuk menghindari melakukan operasi fungsi pada bidang dalam klausa where, yang akan menyebabkan mesin meninggalkan penggunaan indeks dan melakukan pemindaian tabel penuh. Sebagai contoh:
Pilih id dari t di mana substring(nama,1,3)='abc' – id nama yang dimulai dengan ABC
Pilih ID dari t di mana datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ ID yang dihasilkan
harus diubah menjadi:
Pilih ID dari t di mana nama seperti 'abc%'
Pilih id dari t Where createdate>='2005-11-30′ dan createdate<'2005-12-1′
9. Jangan melakukan fungsi, operasi aritmatika, atau operasi ekspresi lainnya di sebelah kiri "=" dalam klausa where, jika tidak, sistem mungkin tidak dapat menggunakan indeks dengan benar.
10. Saat menggunakan bidang indeks sebagai kondisi, jika indeks adalah indeks komposit, maka bidang pertama dalam indeks harus digunakan sebagai kondisi untuk memastikan bahwa sistem menggunakan indeks, jika tidak, indeks tidak akan digunakan, dan urutan bidang harus konsisten dengan urutan indeks sebanyak mungkin.
11. Jangan menulis beberapa kueri yang tidak berarti, seperti membuat struktur tabel kosong:
Pilih col1,col2 ke dalam #t dari t di mana 1=0
Jenis kode ini tidak mengembalikan kumpulan hasil apa pun, tetapi menggunakan sumber daya sistem, jadi harus diubah menjadi seperti ini:
membuat tabel #t(...)
12. Sering kali itu adalah pilihan yang baik untuk menggunakan ada alih-alih di:
Pilih Num dari A Tempat Num In(Pilih Num dari B)
Ganti dengan pernyataan berikut:
pilih num dari a where exists(pilih 1 dari b di mana num=a.num)
Hal-hal yang perlu diperhatikan saat membuat indeks:
1. Tidak semua indeks valid untuk kueri, SQL didasarkan pada data dalam tabel untuk mengoptimalkan kueri, ketika kolom indeks memiliki duplikasi data dalam jumlah besar, kueri SQL mungkin tidak menggunakan indeks, seperti tabel memiliki bidang jenis kelamin, laki-laki, perempuan hampir setengahnya masing-masing, maka meskipun indeks dibangun berdasarkan jenis kelamin, itu tidak akan berperan dalam efisiensi kueri.
2. Semakin banyak indeks tidak semakin baik, indeks tentu dapat meningkatkan efisiensi pilihan yang sesuai, tetapi juga mengurangi efisiensi sisipan dan pembaruan, karena indeks dapat dibangun kembali saat menyisipkan atau memperbarui, jadi cara membangun indeks perlu dipertimbangkan dengan cermat, tergantung pada situasi tertentu. Yang terbaik adalah tidak memiliki lebih dari 6 indeks dalam tabel, dan jika ada terlalu banyak, pertimbangkan apakah perlu membangun indeks pada beberapa kolom yang jarang digunakan.
3. Hindari memperbarui kolom data indeks berkluster sebanyak mungkin, karena urutan kolom data terindeks berkluster adalah urutan penyimpanan fisik rekaman tabel, dan setelah nilai kolom berubah, itu akan mengarah pada penyesuaian urutan seluruh rekaman tabel, yang akan menghabiskan sumber daya yang cukup besar. Jika sistem aplikasi perlu sering memperbarui kolom indeks berkluster, sistem aplikasi perlu mempertimbangkan apakah indeks harus dibangun sebagai indeks berkluster.
Poin lain yang perlu diperhatikan:
1. Cobalah untuk menggunakan bidang numerik, dan cobalah untuk tidak mendesain bidang yang hanya berisi informasi numerik sebagai karakter, yang akan mengurangi performa kueri dan koneksi, serta meningkatkan overhead penyimpanan. Ini karena mesin membandingkan setiap karakter dalam string satu per satu saat memproses kueri dan gabungan, sedangkan untuk jenis numerik, hanya perlu dibandingkan sekali.
2. Jangan gunakan pilih * dari t di mana saja, ganti "*" dengan daftar bidang tertentu, dan jangan mengembalikan bidang apa pun yang tidak digunakan.
3. Cobalah untuk menggunakan variabel tabel alih-alih tabel sementara. Jika variabel tabel berisi data dalam jumlah besar, perhatikan bahwa indeksnya sangat terbatas (hanya indeks kunci primer).
4. Hindari sering membuat dan menghapus tabel sementara untuk mengurangi konsumsi sumber daya tabel sistem.
5. Tabel sementara tidak dapat digunakan, dan menggunakannya dengan tepat dapat membuat rutinitas tertentu lebih efektif, misalnya, ketika Anda perlu berulang kali mereferensikan tabel besar atau himpunan data dalam tabel yang umum digunakan. Namun, untuk peristiwa satu kali, sebaiknya gunakan tabel ekspor.
6. Saat membuat tabel sementara, jika jumlah data yang disisipkan pada satu waktu besar, maka Anda dapat menggunakan pilih ke alih-alih membuat tabel untuk menghindari menyebabkan sejumlah besar log meningkatkan kecepatan; Jika jumlah data tidak besar, untuk memudahkan sumber daya tabel sistem, Anda harus membuat tabel terlebih dahulu dan kemudian menyisipkan.
7. Jika tabel sementara digunakan, pastikan untuk secara eksplisit menghapus semua tabel sementara di akhir prosedur yang disimpan, potong tabel terlebih dahulu, lalu jatuhkan tabel, untuk menghindari kunci panjang tabel sistem.
8. Cobalah untuk menghindari penggunaan kursor, karena efisiensi kursor buruk, jika data yang dioperasikan oleh kursor melebihi 10.000 baris, maka Anda harus mempertimbangkan untuk menulis ulang.
9. Sebelum menggunakan metode berbasis kursor atau metode tabel sementara, Anda harus terlebih dahulu mencari solusi berbasis set untuk memecahkan masalah, dan metode berbasis set biasanya lebih efektif.
10. Seperti tabel sementara, kursor tidak dapat digunakan. Menggunakan kursor FAST_FORWARD untuk himpunan data kecil seringkali lebih baik daripada metode pemrosesan baris demi baris lainnya, terutama jika Anda harus mereferensikan beberapa tabel untuk mendapatkan data yang Anda butuhkan. Rutinitas yang menyertakan "total" dalam kumpulan hasil biasanya lebih cepat daripada yang dijalankan dengan kursor. Jika waktu pengembangan memungkinkan, metode berbasis kursor dan berbasis set dapat dicoba untuk melihat mana yang bekerja lebih baik.
11. Atur SET NOCOUNT ON di awal semua prosedur dan pemicu yang disimpan, dan atur SET NOCOUNT OFF di akhir. Tidak perlu mengirim pesan DONE_IN_PROC ke klien setelah menjalankan setiap pernyataan prosedur dan pemicu yang disimpan.
12. Cobalah untuk menghindari pengembalian data besar ke klien, jika volume data terlalu besar, Anda harus mempertimbangkan apakah permintaan yang sesuai masuk akal.
13. Cobalah untuk menghindari operasi transaksi besar dan meningkatkan kemampuan konkurensi sistem.
|
Diposting pada 11/05/2018 13.57.06
|
|
|
Diposting pada 17/05/2018 10.12.27