Sistem basis data adalah inti dari sistem informasi manajemen. Pemrosesan transaksi online berbasis basis data (OLTP) dan pemrosesan analitis online (OLAP) adalah salah satu aplikasi komputer terpenting di bank, perusahaan, pemerintah, dan departemen lainnya. Berdasarkan contoh penerapan dan dikombinasikan dengan teori database, artikel ini memperkenalkan penerapan teknologi optimasi kueri dalam sistem nyata. Dilihat dari contoh aplikasi di sebagian besar sistem, operasi kueri merupakan bagian terbesar dari berbagai operasi database, dan pernyataan SELECT yang menjadi dasar operasi kueri adalah pernyataan yang paling mahal di antara pernyataan SQL. Misalnya, jika jumlah data terakumulasi hingga tingkat tertentu, seperti informasi tabel database rekening bank yang terakumulasi hingga jutaan atau bahkan puluhan juta catatan, pemindaian tabel lengkap sering kali memerlukan waktu puluhan menit atau bahkan berjam-jam. Jika Anda mengadopsi strategi kueri yang lebih baik daripada pemindaian tabel lengkap, Anda sering kali dapat mengurangi waktu kueri menjadi beberapa menit, yang menunjukkan pentingnya teknologi pengoptimalan kueri.
Selama implementasi proyek aplikasi, penulis menemukan bahwa ketika mengembangkan aplikasi database menggunakan beberapa alat pengembangan database front-end (seperti PowerBuilder, Delphi, dll.), banyak programmer hanya fokus pada keindahan antarmuka pengguna dan tidak membayar perhatian pada efisiensi pernyataan kueri, mengakibatkan semua masalah. Sistem aplikasi yang dikembangkan tidak efisien dan menyebabkan pemborosan sumber daya yang serius. Oleh karena itu, bagaimana merancang pernyataan kueri yang efisien dan masuk akal sangatlah penting. Berdasarkan contoh penerapan dan dikombinasikan dengan teori database, artikel ini memperkenalkan penerapan teknologi optimasi kueri dalam sistem nyata.
Analisis masalahnya
Banyak pemrogram percaya bahwa optimasi kueri adalah tugas DBMS (sistem manajemen basis data) dan tidak ada hubungannya dengan pernyataan SQL yang ditulis oleh pemrogram. Ini salah. Rencana kueri yang baik sering kali dapat meningkatkan kinerja program hingga puluhan kali lipat. Rencana kueri adalah kumpulan pernyataan SQL yang dikirimkan oleh pengguna, dan rencana kueri adalah kumpulan pernyataan yang dihasilkan setelah optimasi. Proses rencana kueri pemrosesan DBMS adalah sebagai berikut: setelah menyelesaikan pemeriksaan leksikal dan sintaksis dari pernyataan kueri, pernyataan tersebut diserahkan ke pengoptimal kueri DBMS. Setelah pengoptimal menyelesaikan pengoptimalan aljabar dan pengoptimalan jalur akses, modul yang telah dikompilasi akan Memprosesnya pernyataan dan menghasilkan rencana kueri, kemudian mengirimkannya ke sistem untuk diproses dan dieksekusi pada waktu yang tepat, dan akhirnya mengembalikan hasil eksekusi kepada pengguna. Dalam produk database aktual versi tinggi (seperti Oracle, Sybase, dll.), metode optimasi berbasis biaya digunakan. Optimasi ini dapat memperkirakan biaya rencana kueri yang berbeda berdasarkan informasi yang diperoleh dari tabel kamus sistem, dan kemudian memilih perencanaan yang lebih baik. Meskipun produk database saat ini menjadi lebih baik dan lebih baik dalam optimasi query, pernyataan SQL yang dikirimkan oleh pengguna adalah dasar untuk optimasi sistem. Sulit untuk membayangkan bahwa rencana query yang awalnya buruk akan menjadi efisien setelah optimasi sistem pernyataan yang ditulis pengguna sangatlah penting. Kami tidak akan membahas optimasi kueri yang dilakukan oleh sistem untuk saat ini. Berikut ini berfokus pada solusi untuk meningkatkan rencana kueri pengguna.
memecahkan masalah
Berikut ini mengambil sistem database relasional Informix sebagai contoh untuk memperkenalkan metode guna meningkatkan rencana kueri pengguna.
1. Penggunaan indeks yang wajar
Indeks adalah struktur data penting dalam database, dan tujuan mendasarnya adalah untuk meningkatkan efisiensi kueri. Sebagian besar produk database sekarang menggunakan struktur indeks ISAM yang pertama kali diusulkan oleh IBM. Penggunaan indeks harus tepat, dan prinsip penggunaannya adalah sebagai berikut:
●Membuat indeks pada kolom yang sering terhubung tetapi tidak ditetapkan sebagai kunci asing, sementara pengoptimal secara otomatis membuat indeks untuk kolom yang jarang terhubung.
● Membuat indeks pada kolom yang sering diurutkan atau dikelompokkan (yaitu, mengelompokkan berdasarkan atau mengurutkan berdasarkan operasi).
●Buat pencarian pada kolom dengan banyak nilai berbeda yang sering digunakan dalam ekspresi kondisional. Jangan membuat indeks pada kolom dengan sedikit nilai berbeda. Misalnya, hanya ada dua nilai berbeda di kolom "Gender" pada tabel karyawan, "Pria" dan "Wanita", sehingga tidak perlu membuat indeks. Jika Anda membuat indeks, tidak hanya tidak akan meningkatkan efisiensi kueri, namun juga akan sangat mengurangi kecepatan pembaruan.
●Jika ada beberapa kolom yang akan diurutkan, Anda dapat membuat indeks gabungan pada kolom tersebut.
●Gunakan alat sistem. Misalnya, database Informix memiliki alat tbcheck yang dapat memeriksa indeks mencurigakan. Pada beberapa server database, indeks mungkin tidak valid atau efisiensi pembacaan mungkin berkurang karena seringnya operasi. Jika kueri yang menggunakan indeks melambat tanpa alasan yang jelas, Anda dapat mencoba menggunakan alat tbcheck untuk memeriksa integritas indeks. dan memperbaikinya jika perlu. Selain itu, ketika tabel database memperbarui data dalam jumlah besar, menghapus dan membangun kembali indeks dapat meningkatkan kecepatan kueri.
2. Hindari atau sederhanakan penyortiran
Penyortiran berulang pada tabel besar harus disederhanakan atau dihindari. Pengoptimal menghindari langkah pengurutan ketika dapat menggunakan indeks untuk secara otomatis menghasilkan keluaran dalam urutan yang benar. Berikut beberapa faktor yang mempengaruhinya:
●Indeks tidak mencakup satu atau beberapa kolom yang akan diurutkan;
●Urutan kolom dalam klausa group by atau order by berbeda dengan urutan indeks;
●Kolom yang diurutkan berasal dari tabel yang berbeda.
Untuk menghindari penyortiran yang tidak perlu, perlu menambahkan indeks dengan benar dan menggabungkan tabel database secara wajar (walaupun terkadang hal ini dapat memengaruhi normalisasi tabel, peningkatan efisiensi tidak sia-sia). Jika pengurutan tidak dapat dihindari, Anda harus mencoba menyederhanakannya, seperti mempersempit rentang kolom untuk pengurutan, dll.
3. Hilangkan akses berurutan ke data baris tabel besar
Dalam kueri bertumpuk, akses berurutan ke tabel mungkin berdampak fatal pada efisiensi kueri. Misalnya, dengan menggunakan strategi akses sekuensial, jika kueri dengan tiga tingkat bersarang menanyakan 1.000 baris di setiap tingkat, maka kueri ini akan menanyakan 1 miliar baris data. Cara utama untuk menghindari hal ini adalah dengan mengindeks kolom yang digabungkan. Misalnya ada dua tabel: tabel siswa (nomor siswa, nama, umur...) dan tabel pemilihan mata kuliah (nomor siswa, nomor mata kuliah, nilai). Jika dua tabel ingin dihubungkan, indeks harus dibuat pada bidang koneksi "nomor siswa".
Anda juga dapat menggunakan serikat pekerja untuk menghindari akses berurutan. Meskipun terdapat indeks di semua kolom centang, beberapa bentuk klausa memaksa pengoptimal untuk menggunakan akses berurutan. Kueri berikut akan memaksa operasi berurutan pada tabel pesanan: SELECT * FROM pesanan WHERE (nomor_pelanggan=104 DAN nomor_pesanan>1001) OR nomor_pesanan=1008
Meskipun terdapat indeks pada customer_num dan order_num, pengoptimal masih menggunakan jalur akses berurutan untuk memindai seluruh tabel pada pernyataan di atas. Karena pernyataan ini mengambil kumpulan baris terpisah, maka pernyataan ini harus diubah menjadi pernyataan berikut:
PILIH * DARI pesanan DI MANA jumlah_pelanggan=104 DAN jumlah_pesanan>1001
SERIKAT
PILIH * DARI pesanan DI MANA jumlah_pesanan=1008
Hal ini memungkinkan jalur indeks digunakan untuk memproses kueri.
4. Hindari subkueri yang berkorelasi
Jika label kolom muncul di kueri utama dan kueri di klausa Where, kemungkinan besar subkueri tersebut harus dikueri ulang ketika nilai kolom di kueri utama berubah. Semakin banyak level kueri yang disarangkan, semakin rendah efisiensinya, sehingga subkueri harus dihindari sebisa mungkin. Jika subkueri tidak dapat dihindari, saring sebanyak mungkin baris dalam subkueri tersebut.
5. Hindari ekspresi reguler yang sulit
Kata kunci MATCHES dan LIKE mendukung pencocokan wildcard, yang secara teknis disebut ekspresi reguler. Namun pencocokan seperti ini sangat memakan waktu. Misalnya: PILIH * DARI pelanggan DIMANA kode pos SEPERTI “98_ _ _”
Meskipun indeks dibuat pada bidang kode pos, pemindaian berurutan masih digunakan dalam kasus ini. Jika Anda mengubah pernyataan menjadi SELECT * FROM customer WHERE zipcode > "98000", indeks akan digunakan untuk melakukan kueri saat menjalankan kueri, yang jelas akan sangat meningkatkan kecepatan.
Selain itu, hindari substring yang tidak memulai. Misalnya, pernyataan: SELECT * FROM customer WHERE zipcode[2, 3]>"80" menggunakan substring yang tidak dimulai pada klausa Where, sehingga pernyataan ini tidak menggunakan indeks.
6. Gunakan tabel sementara untuk mempercepat kueri
Mengurutkan subset tabel dan membuat tabel sementara terkadang dapat mempercepat kueri. Ini membantu menghindari operasi pengurutan ganda dan menyederhanakan pekerjaan pengoptimal. Misalnya: PILIH cust.name, rcVBles.balance,...kolom lainnya
PILIH cust.name,rcVBles.balance,...kolom lainnya
DARI pelanggan, rcvbles
DIMANA cust.customer_id = rcvlbes.customer_id
DAN rcvblls.saldo>0
DAN kode pos khusus>"98000"
ORDER BERDASARKAN nama khusus
Jika kueri ini akan dijalankan beberapa kali, bukan hanya sekali, Anda dapat menemukan semua pelanggan yang belum dibayar dalam file sementara dan mengurutkannya berdasarkan nama pelanggan: SELECT cust.name, rcvbles.balance,...other kolom
PILIH cust.name,rcvbles.balance,...kolom lainnya
DARI pelanggan, rcvbles
DIMANA cust.customer_id = rcvlbes.customer_id
DAN rcvblls.saldo>0
ORDER BERDASARKAN nama khusus
KE TEMP cust_with_balance
Kemudian query pada tabel sementara dengan cara sebagai berikut: SELECT * FROM cust_with_balance
DIMANA kode pos>"98000"
Ada lebih sedikit baris di tabel sementara dibandingkan di tabel utama, dan urutan fisik adalah urutan yang diperlukan, yang mengurangi I/O disk, sehingga beban kerja kueri bisa sangat berkurang.
Catatan: Setelah tabel sementara dibuat, tabel tersebut tidak akan mencerminkan modifikasi tabel utama. Jika data dalam tabel utama sering diubah, berhati-hatilah agar data tidak hilang.
7. Gunakan pengurutan untuk menggantikan akses non-sekuensial
Akses disk non-sekuensial adalah operasi paling lambat dan diwakili oleh gerakan bolak-balik dari lengan akses disk. Pernyataan SQL menyembunyikan situasi ini, sehingga memudahkan kita untuk menulis kueri yang memerlukan akses ke sejumlah besar halaman non-berurutan saat menulis aplikasi. Terkadang, menggunakan kemampuan pengurutan database alih-alih akses non-sekuensial dapat meningkatkan kueri.
-