SQL dilancarkan: 17 cara untuk mempercepat pertanyaan SQL anda

Pembangun SQL di setiap platform bergelut, nampaknya terjebak dalam DO WHILEgelung yang membuat mereka mengulangi kesilapan yang sama berulang kali. Ini kerana bidang pangkalan data masih belum matang. Pasti, vendor membuat beberapa langkah, tetapi mereka terus bergelut dengan masalah yang lebih besar. Kesesuaian, pengurusan sumber daya, pengurusan ruang, dan kelajuan masih mengganggu pemaju SQL sama ada mereka mengkodkan SQL Server, Oracle, DB2, Sybase, MySQL, atau platform hubungan lain.

Sebahagian daripada masalahnya ialah tidak ada peluru ajaib, dan untuk hampir setiap amalan terbaik, saya dapat menunjukkan sekurang-kurangnya satu pengecualian kepada anda. Biasanya, pembangun menemui kaedah kegemarannya sendiri - walaupun biasanya mereka tidak menyertakan konstruk untuk prestasi atau serentak — dan tidak bersusah payah meneroka pilihan lain. Mungkin itu adalah gejala kekurangan pendidikan, atau pembangunnya terlalu dekat dengan proses untuk mengenali ketika mereka melakukan sesuatu yang salah. Mungkin pertanyaan berjalan dengan baik pada sekumpulan data ujian tempatan tetapi gagal pada sistem pengeluaran.

Saya tidak mengharapkan pembangun SQL menjadi pentadbir, tetapi mereka mesti mengambil kira masalah pengeluaran semasa menulis kod mereka. Sekiranya mereka tidak melakukannya semasa pembangunan awal, DBA hanya akan membuat mereka kembali dan melakukannya kemudian - dan pengguna menderita sementara.

Ada sebab mengapa kita mengatakan penyesuaian pangkalan data adalah seni dan sains. Ini kerana sangat sedikit peraturan sukar dan cepat yang berlaku di seluruh bidang. Masalah yang anda selesaikan pada satu sistem bukanlah masalah yang lain, dan sebaliknya. Tidak ada jawaban yang tepat dalam hal menala pertanyaan, tetapi itu tidak berarti Anda harus menyerah.

Terdapat beberapa prinsip baik yang dapat anda ikuti yang harus menghasilkan hasil dalam satu kombinasi atau yang lain. Saya telah merangkumnya dalam senarai SQL dos dan tidak boleh dilakukan yang sering diabaikan atau sukar dilihat. Teknik-teknik ini akan memberi anda sedikit lebih banyak wawasan mengenai pemikiran DBA anda, serta kemampuan untuk mula memikirkan proses dengan cara yang berorientasikan pengeluaran.

1. Jangan gunakan UPDATEsebagai gantiCASE

Masalah ini sangat biasa, dan walaupun tidak sukar untuk dilihat, banyak pembangun sering mengabaikannya kerana penggunaan UPDATEmempunyai aliran semula jadi yang kelihatan logik.

Ambil senario ini, misalnya: Anda memasukkan data ke dalam tabel temp dan memerlukannya untuk menampilkan nilai tertentu jika ada nilai lain. Mungkin anda menarik dari jadual Pelanggan dan anda mahu sesiapa yang mempunyai pesanan lebih dari $ 100,000 untuk dilabel sebagai "Pilihan". Oleh itu, anda memasukkan data ke dalam jadual dan menjalankan UPDATEpernyataan untuk menetapkan lajur CustomerRank menjadi "Preferred" bagi sesiapa yang mempunyai pesanan melebihi $ 100,000. Masalahnya ialah UPDATEpernyataan itu dicatat, yang bermaksud ia mesti menulis dua kali untuk setiap penulisan ke jadual. Cara CASEmengatasi ini, tentu saja, adalah dengan menggunakan penyataan sebaris dalam pertanyaan SQL itu sendiri. Ini menguji setiap baris untuk keadaan jumlah pesanan dan menetapkan label "Pilihan" sebelum ditulis ke jadual. Peningkatan prestasi boleh mengejutkan.

2. Jangan gunakan semula kod secara membuta tuli

Masalah ini juga sangat biasa. Sangat mudah untuk menyalin kod orang lain kerana anda tahu ia menarik data yang anda perlukan. Masalahnya adalah bahawa ia sering mengambil lebih banyak data daripada yang anda perlukan, dan pembangun jarang bersusah payah memangkasnya, jadi mereka berakhir dengan kumpulan data yang besar. Ini biasanya datang dalam bentuk gabungan luar tambahan atau syarat tambahan dalam WHEREklausa. Anda boleh memperoleh keuntungan prestasi yang besar jika anda mengurangkan kod yang digunakan semula mengikut keperluan anda.

3. Jangan tarik jumlah lajur yang anda perlukan

Masalah ini serupa dengan masalah No. 2, tetapi khusus untuk lajur. Terlalu mudah untuk membuat kod semua pertanyaan anda SELECT *daripada menyenaraikan lajur secara berasingan. Masalahnya sekali lagi ialah ia menarik lebih banyak data daripada yang anda perlukan. Saya telah melihat kesilapan ini berpuluh-puluh kali. Pembangun membuat SELECT *pertanyaan terhadap jadual dengan 120 lajur dan berjuta-juta baris, tetapi hanya menggunakan tiga hingga lima daripadanya. Pada ketika itu, anda memproses lebih banyak data daripada yang anda perlukan, hairanlah pertanyaan itu kembali sama sekali. Anda bukan sahaja memproses lebih banyak data daripada yang anda perlukan, tetapi anda juga mengambil sumber dari proses lain.

4. Jangan mencelupkan dua kali

Inilah satu lagi yang saya lihat lebih banyak daripada yang sepatutnya: Prosedur yang tersimpan ditulis untuk menarik data dari meja dengan beratus-ratus juta baris. Pembangun memerlukan pelanggan yang tinggal di California dan mempunyai pendapatan lebih dari $ 40,000. Oleh itu, dia meminta pelanggan yang tinggal di California dan memasukkan hasilnya ke dalam jadual temp; kemudian dia meminta pelanggan dengan pendapatan melebihi $ 40,000 dan memasukkan hasil tersebut ke dalam jadual temp yang lain. Akhirnya, dia bergabung dengan kedua-dua meja untuk mendapatkan produk akhir.

Adakah anda bergurau? Ini mesti dilakukan dalam satu pertanyaan; sebagai gantinya, anda menggunakan dua meja besar. Jangan tolol: Pertanyaan jadual besar hanya sekali jika boleh - anda akan dapat mengetahui prestasi prosedur anda lebih baik.

Senario yang sedikit berbeza adalah apabila subset jadual besar diperlukan oleh beberapa langkah dalam proses, yang menyebabkan jadual besar tersebut akan ditanyakan setiap kali. Elakkan ini dengan meminta subset dan teruskan di tempat lain, kemudian tunjuk langkah seterusnya ke set data anda yang lebih kecil.

6. Lakukan data pra-peringkat

Ini adalah salah satu topik kegemaran saya kerana ini adalah teknik lama yang sering diabaikan. Sekiranya anda mempunyai laporan atau prosedur (atau lebih baik lagi, satu set daripadanya) yang akan bergabung dengan jadual besar, boleh memberi manfaat bagi anda untuk memproses data dengan menggabungkan jadual lebih awal dan meneruskannya ke dalam meja. Kini laporan dapat berlawanan dengan jadual yang telah dipentaskan sebelumnya dan mengelakkan penyertaan yang besar.

Anda tidak selalu dapat menggunakan teknik ini, tetapi apabila anda boleh, anda akan dapati bahawa ini adalah kaedah terbaik untuk menjimatkan sumber pelayan.

Perhatikan bahawa banyak pembangun mengatasi masalah penyertaan ini dengan menumpukan pada pertanyaan itu sendiri dan membuat tontonan hanya di sekitar penyatuan sehingga mereka tidak perlu menaip syarat bergabung berulang kali. Tetapi masalah dengan pendekatan ini adalah bahawa pertanyaan masih berjalan untuk setiap laporan yang memerlukannya. Dengan pra-pementasan data, anda menjalankan penyertaan hanya sekali (katakan, 10 minit sebelum laporan) dan orang lain mengelakkan penyertaan besar. Saya tidak dapat memberitahu anda betapa saya suka teknik ini; di kebanyakan persekitaran, ada jadual popular yang selalu bergabung, jadi tidak ada sebab mengapa mereka tidak dapat dipentaskan sebelumnya.

7. Hapus dan kemas kini secara berkumpulan

Inilah teknik mudah lain yang sering diabaikan. Menghapus atau mengemas kini sejumlah besar data dari jadual besar boleh menjadi mimpi buruk jika anda tidak melakukannya dengan betul. Masalahnya ialah kedua-dua pernyataan ini berjalan sebagai satu transaksi, dan jika anda perlu membunuhnya atau jika sesuatu berlaku pada sistem semasa mereka berfungsi, sistem harus memutar balik keseluruhan transaksi. Ini boleh memakan masa yang sangat lama. Operasi ini juga dapat menyekat urus niaga lain sepanjang tempohnya, pada dasarnya menyekat sistem.

Penyelesaiannya ialah melakukan penghapusan atau kemas kini dalam kumpulan yang lebih kecil. Ini menyelesaikan masalah anda dengan beberapa cara. Pertama, jika urus niaga terbunuh dengan alasan apa pun, ia hanya mempunyai sebilangan kecil baris untuk dikembalikan, jadi pangkalan data kembali dalam talian dengan lebih cepat. Kedua, sementara kumpulan yang lebih kecil melakukan disk, yang lain dapat menyelinap masuk dan melakukan beberapa pekerjaan, jadi konkurensi ditingkatkan.

Sejajar dengan itu, banyak pembangun tidak sedar bahawa operasi penghapusan dan kemas kini ini mesti diselesaikan pada hari yang sama. Itu tidak selalu berlaku, terutamanya jika anda mengarkibkan. Anda boleh melancarkan operasi itu selama yang anda perlukan, dan kumpulan yang lebih kecil membantu menyelesaikannya. Sekiranya anda memerlukan masa lebih lama untuk melakukan operasi intensif ini, luangkan masa tambahan dan jangan jatuhkan sistem anda.

8. Gunakan jadual temp untuk meningkatkan prestasi kursor

Saya harap kita semua tahu sekarang bahawa yang terbaik adalah menjauhkan diri dari kursor jika mungkin. Kursor tidak hanya mengalami masalah kelajuan, yang dengan sendirinya boleh menjadi masalah dengan banyak operasi, tetapi mereka juga dapat menyebabkan operasi anda menyekat operasi lain lebih lama daripada yang diperlukan. Ini mengurangkan kesesuaian sistem anda.

Namun, anda tidak boleh selalu menghindari penggunaan kursor, dan apabila tiba masanya, anda mungkin dapat melepaskan diri dari masalah prestasi yang disebabkan oleh kursor dengan melakukan operasi kursor terhadap jadual temp. Contohnya, ambil kursor yang melalui jadual dan kemas kini beberapa lajur berdasarkan beberapa hasil perbandingan. Daripada melakukan perbandingan dengan siaran langsung, anda mungkin dapat memasukkan data tersebut ke dalam tabel temp dan melakukan perbandingan dengan itu. Kemudian anda mempunyai satu UPDATEpernyataan terhadap siaran langsung yang jauh lebih kecil dan menyimpan kunci hanya untuk waktu yang singkat.

Memotong modifikasi data anda seperti ini dapat meningkatkan kesesuaian. Saya akan menyelesaikannya dengan mengatakan bahawa anda hampir tidak perlu menggunakan kursor. Hampir selalu ada penyelesaian berdasarkan set; anda perlu belajar melihatnya.

9. Jangan bersarang pemandangan

Pandangan boleh menjadi senang, tetapi anda harus berhati-hati ketika menggunakannya. Walaupun pandangan dapat membantu mengaburkan pertanyaan besar dari pengguna dan menyeragamkan akses data, anda dapat dengan mudah berada dalam situasi di mana anda mempunyai pandangan yang memanggil pandangan yang memanggil pandangan yang memanggil pandangan. Ini disebut pandangan bersarang , dan boleh menyebabkan masalah prestasi yang teruk, terutama dalam dua cara:

  • Pertama, kemungkinan besar anda akan mendapat lebih banyak data daripada yang anda perlukan.
  • Kedua, pengoptimum pertanyaan akan menyerah dan mengembalikan rancangan pertanyaan yang tidak baik.

Saya pernah mempunyai pelanggan yang gemar pemandangan bersarang. Pelanggan mempunyai satu pandangan yang digunakan untuk hampir semua perkara kerana mempunyai dua gabungan penting. Masalahnya adalah bahawa paparan mengembalikan lajur dengan dokumen 2MB di dalamnya. Sebilangan dokumen lebih besar. Pelanggan mendorong sekurang-kurangnya 2MB tambahan ke seluruh rangkaian untuk setiap baris dalam hampir setiap pertanyaan yang dijalankan. Secara semula jadi, prestasi pertanyaan tidak menentu.

Dan tiada pertanyaan yang menggunakan lajur itu! Sudah tentu, tiang itu dikuburkan dalam tujuh pandangan, sehingga sukar untuk melihatnya. Semasa saya membuang lajur dokumen dari paparan, masa untuk pertanyaan terbesar berubah dari 2.5 jam hingga 10 minit. Ketika saya akhirnya menyingkap paparan bersarang, yang mempunyai beberapa gabungan dan lajur yang tidak perlu, dan menulis pertanyaan biasa, masa untuk pertanyaan yang sama jatuh kepada beberapa saat.