Get best of the week

Mengapa SQL Server tidak menjamin hasil pengurutan tanpa ORDER BY

Halo lagi. Pada bulan Juni, OTUS kembali meluncurkan kursus "Pengembang MS SQL Server" , secara tradisional pada awal kursus kami mulai membagikan materi tentang topik tersebut dengan Anda.



Jika kueri Anda tidak mengandung ORDER OLEH, maka Anda tidak dapat yakin bahwa penyortiran hasil tidak akan berubah seiring waktu.

Tentu saja, pada awalnya semuanya akan dapat diprediksi, tetapi ketika perubahan terjadi (dalam indeks, tabel, konfigurasi server, jumlah data Anda), Anda mungkin menemukan beberapa kejutan yang tidak menyenangkan.

Mari kita mulai dengan sesuatu yang sederhana: lakukan SELECT untuk tabel Users dari database Stack Overflow. Tabel ini memiliki indeks cluster untuk kolom Id, yang dimulai dari satu dan meningkat menjadi satu triliun. Untuk kueri ini, data dikembalikan dalam urutan indeks berkerumun:



Tetapi jika Anda membuat indeks pada DisplayName dan Lokasi, maka SQL Server tiba-tiba memutuskan untuk menggunakan indeks baru, bukan yang berkerumun:



Berikut adalah rencana pelaksanaannya:



Mengapa SQL Server memutuskan untuk menggunakan indeks ini, meskipun tidak perlu disortir oleh DisplayName dan Lokasi? Karena indeks ini adalah salinan data terkecil yang perlu diperoleh. Mari kita lihat ukuran indeks dengan sp_BlitzIndex:



Indeks berkerumun (CX / PK) memiliki sekitar 8,9 juta baris dan ukurannya adalah 1,1 GB .

Dalam indeks non-clustered untuk DisplayName, Lokasi juga sekitar 8,9 juta baris, tetapi ukurannya hanya 368 MB . Jika Anda perlu melakukan pemindaian untuk mendapatkan hasil kueri, maka mengapa tidak memilih sumber data terkecil, karena akan lebih cepat. Untuk alasan inilah SQL Server melakukan ini.

"Ya, tapi permintaan saya mengandung WHERE."


Oke, sekarang kami memiliki indeks untuk DisplayName dan Lokasi, mari kita coba menjalankan kueri yang mencari nama tertentu (DisplayName). Hasil diurutkan berdasarkan DisplayName:



Rencana pelaksanaan menunjukkan bahwa indeks digunakan oleh DisplayName dan Lokasi:



Tetapi jika Anda melihat nilai yang berbeda, hasilnya tidak akan lagi diurutkan berdasarkan DisplayName:



SQL Server menemukan bahwa Alex memiliki Indeks Clustered yang jauh lebih masuk akal. Pindai alih-alih Pencarian Indeks + Cari Kunci:



Bahkan dalam kasus yang sangat sederhana ini, Anda tidak dapat menjamin bahwa SQL Server akan selalu menggunakan salinan data yang Anda harapkan.

Baru-baru ini, saya menemukan banyak kasus yang lebih rumit:

  • Menghapus indeks yang digunakan dalam kueri
  • (Forced Parameterization), , SQL Server
  • (Compatibility Level) (Cardinality Estimator), .


.

More articles:


All Articles