Indeks SQL Dijelaskan, Pt. 1

Digunakan dengan benar, indeks database SQL bisa sangat efektif sehingga mungkin tampak seperti keajaiban. Tetapi rangkaian latihan berikut akan menunjukkan bahwa di bawahnya, logika sebagian besar indeks SQL—dan menggunakannya dengan benar—cukup mudah.

Dalam seri ini, SQL Indexes Explained , kita akan membahas motivasi penggunaan indeks untuk mengakses data dan untuk merancang indeks seperti yang dilakukan oleh semua RDBMS modern. Kami kemudian akan melihat algoritme yang digunakan untuk mengembalikan data untuk pola kueri tertentu.

Anda tidak perlu tahu banyak tentang indeks untuk bisa mengikuti SQL Indexes Explained . Hanya ada dua prasyarat:

• Pengetahuan SQL dasar
• Pengetahuan dasar tentang bahasa pemrograman apa pun

Topik utama yang akan dijelaskan oleh Indeks SQL adalah:

• Mengapa kita membutuhkan indeks database SQL; memvisualisasikan rencana eksekusi menggunakan indeks
• Desain indeks: indeks mana yang membuat kueri cepat dan efisien
• Bagaimana kami dapat menulis kueri untuk menggunakan indeks secara efektif
• Dampak penggunaan indeks dalam SQL pada efisiensi baca/tulis
• Indeks penutup
• Partisi, dampaknya pada membaca dan menulis, dan kapan menggunakannya

Ini bukan hanya tutorial indeks SQL—ini adalah pemahaman mendalam tentang mekanisme indeks yang mendasarinya.

Kami akan mencari tahu bagaimana RDBMS menggunakan indeks dengan melakukan latihan dan menganalisis metode pemecahan masalah kami. Materi latihan kami terdiri dari Google Spreadsheet yang hanya dapat dibaca. Untuk melakukan latihan, Anda dapat menyalin Google Sheet ( File → Make a copy ) atau menyalin isinya ke Google Sheet Anda sendiri.

Dalam setiap latihan, kami akan menampilkan kueri SQL yang menggunakan sintaks Oracle. Untuk tanggal, kami akan menggunakan format ISO 8601, YYYY-MM-DD .

Latihan 1: Semua Reservasi Klien

Tugas pertama—jangan lakukan dulu—adalah menemukan semua baris dari spreadsheet Reservasi untuk klien tertentu dari sistem reservasi hotel, dan menyalinnya ke spreadsheet Anda sendiri, mensimulasikan eksekusi kueri berikut:

 SELECT * FROM Reservations WHERE ClientID = 12;

Tapi kami ingin mengikuti metode tertentu.

Pendekatan 1: Tanpa Penyortiran, Tanpa Pemfilteran

Untuk percobaan pertama, jangan gunakan fitur pengurutan atau pemfilteran apa pun. Tolong, catat waktu yang dihabiskan. Lembar yang dihasilkan harus berisi 73 baris.

Pseudocode ini mengilustrasikan algoritme untuk menyelesaikan tugas tanpa menyortir:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

Dalam hal ini, kami harus memeriksa semua 841 baris untuk kembali dan menyalin 73 baris yang memenuhi kondisi tersebut.

Pendekatan 2: Hanya Penyortiran

Untuk percobaan kedua, urutkan sheet sesuai dengan nilai kolom ClientID . Jangan gunakan filter. Catat waktu dan bandingkan dengan waktu yang dibutuhkan untuk menyelesaikan tugas tanpa menyortir data.

Setelah disortir, pendekatannya terlihat seperti ini:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

Kali ini, kami harus memeriksa "hanya" 780 baris. Jika kita entah bagaimana bisa melompat ke baris pertama, itu akan memakan waktu lebih sedikit.

Tetapi jika kita harus mengembangkan program untuk tugas tersebut, solusi ini akan lebih lambat daripada yang pertama. Itu karena kita harus mengurutkan semua data terlebih dahulu, yang berarti setiap baris harus diakses setidaknya sekali. Pendekatan ini baik hanya jika lembar sudah diurutkan dalam urutan yang diinginkan.

Latihan 2: Jumlah Reservasi yang Dimulai pada Tanggal tertentu

Sekarang tugas menghitung jumlah check-in pada 16 Agustus 2020:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

Gunakan spreadsheet dari Latihan 1. Ukur dan bandingkan waktu yang dihabiskan untuk menyelesaikan tugas dengan dan tanpa pengurutan. Jumlah yang benar adalah 91.

Untuk pendekatan tanpa pengurutan, algoritma pada dasarnya sama dengan yang ada pada Latihan 1.

Pendekatan penyortiran juga mirip dengan yang dari latihan sebelumnya. Kami hanya akan membagi loop menjadi dua bagian:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

Latihan 3: Investigasi Kriminal

Inspektur polisi meminta untuk melihat daftar tamu yang tiba di hotel pada tanggal 13 dan 14 Agustus 2020.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

Pendekatan 1: Diurutkan berdasarkan Tanggal Saja

Inspektur ingin daftar cepat. Kita sudah tahu bahwa sebaiknya kita mengurutkan tabel/spreadsheet menurut tanggal kedatangan. Jika kita baru saja menyelesaikan Latihan 2, kita beruntung tabelnya sudah diurutkan. Jadi, kami menerapkan pendekatan yang mirip dengan yang ada di Latihan 2.

Tolong, coba dan catat waktu, jumlah baris yang harus Anda baca, dan jumlah item dalam daftar.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

Dengan menggunakan pendekatan ini, kami harus membaca 511 baris untuk menyusun daftar 46 tamu. Jika kami dapat meluncur ke bawah dengan tepat, kami sebenarnya tidak perlu melakukan 324 pembacaan dari siklus berulang hanya untuk menemukan kedatangan pertama pada tanggal 13 Agustus. Namun, kami masih harus membaca lebih dari 100 baris untuk memeriksa apakah tamu tersebut tiba di hotel dengan HotelID 3 .

Inspektur menunggu sepanjang waktu tetapi tidak akan senang: Alih-alih nama tamu dan data relevan lainnya, kami hanya mengirimkan daftar ID yang tidak berarti.

Kita akan kembali ke aspek itu nanti di seri ini. Mari kita cari cara untuk menyiapkan daftar lebih cepat.

Pendekatan 2: Diurutkan berdasarkan Hotel, Lalu Tanggal

Untuk mengurutkan baris menurut HotelID lalu DateFrom , kita bisa memilih semua kolom, lalu gunakan opsi menu Google Sheets Data → Sort range .

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

Kami harus melewati 338 kedatangan pertama sebelum menemukan yang pertama ke hotel kami. Setelah itu, kami pergi ke 103 kedatangan sebelumnya untuk menemukan yang pertama pada tanggal 13 Agustus. Terakhir, kami menyalin 46 nilai ClientID berurutan. Ini membantu kami bahwa pada langkah ketiga, kami dapat menyalin satu blok ID berturut-turut. Sayang sekali kami tidak bisa melompat ke baris pertama dari blok itu.

Pendekatan 3: Diurutkan berdasarkan Hotel Saja

Sekarang coba latihan yang sama menggunakan spreadsheet yang dipesan oleh HotelID saja.

Algoritme yang diterapkan ke tabel yang diurutkan oleh HotelID saja kurang efisien dibandingkan saat kami mengurutkan berdasarkan HotelID dan DateFrom (dalam urutan itu):

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

Dalam hal ini, kita harus membaca semua 166 kedatangan ke hotel dengan HotelID 3 , dan untuk masing-masing, untuk memeriksa apakah DateFrom termasuk dalam interval yang diminta.

Pendekatan 4: Diurutkan berdasarkan Tanggal, Lalu Hotel

Apakah penting apakah kita mengurutkan terlebih dahulu berdasarkan HotelID lalu DateFrom atau sebaliknya? Mari kita cari tahu: Coba urutkan dulu berdasarkan DateFrom , lalu berdasarkan HotelID .

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

Kami menemukan baris pertama dengan tanggal yang relevan, kemudian membaca lebih lanjut sampai kami menemukan kedatangan pertama ke hotel. Setelah itu, untuk beberapa baris, kedua syarat terpenuhi, tanggal yang benar dan hotel yang tepat. Namun, setelah kedatangan di Hotel 3, kami tiba di hotel 4, 5, dan seterusnya, untuk tanggal yang sama. Setelah mereka, kami harus kembali membaca baris untuk hari berikutnya untuk hotel 1 dan 2, sampai kami dapat membaca kedatangan berturut-turut ke hotel yang kami minati.

Seperti yang dapat kita lihat, semua pendekatan memiliki satu blok data berurutan di tengah set baris lengkap, yang mewakili sebagian data yang cocok. Pendekatan 2 dan 4 adalah satu-satunya di mana logika memungkinkan kita untuk menghentikan algoritme sepenuhnya sebelum kita mencapai akhir kecocokan parsial.

Pendekatan 4 telah sepenuhnya mencocokkan data dalam dua blok, tetapi Pendekatan 2 adalah satu-satunya di mana data yang ditargetkan semuanya berada dalam satu blok berturut-turut.

Dari jumlah tersebut, jelas bahwa Pendekatan 2 memiliki keunggulan paling banyak dalam hal ini.

Indeks SQL Dijelaskan: Kesimpulan dan Apa Selanjutnya

Melakukan latihan ini harus membuat poin-poin berikut menjadi jelas:

1. Membaca dari tabel yang diurutkan dengan benar lebih cepat.
2. Jika tabel belum diurutkan, pengurutan membutuhkan waktu lebih lama daripada membaca dari tabel yang tidak diurutkan.
3. Menemukan cara untuk melompat ke baris pertama yang cocok dengan kondisi pencarian dalam tabel yang diurutkan akan menghemat banyak pembacaan.
4. Akan sangat membantu jika tabel diurutkan terlebih dahulu.
5. Mempertahankan salinan tabel yang diurutkan untuk kueri yang paling sering akan sangat membantu.

Sekarang, salinan tabel yang diurutkan terdengar hampir seperti indeks database. Artikel berikutnya di SQL Indexes Explained mencakup implementasi indeks yang belum sempurna. Terima kasih sudah membaca!