Pertanyaan Apa artinya Clustered dan Non Clustered Index?


Saya memiliki eksposur terbatas ke DB dan hanya menggunakan DB sebagai programmer aplikasi. Saya ingin tahu tentang Clustered dan Non clustered indexes. Saya googled dan apa yang saya temukan adalah:

Indeks berkerumun adalah jenis indeks khusus yang mengatur ulang jalannya   catatan dalam tabel bersifat fisik   disimpan. Oleh karena itu tabel hanya bisa   satu indeks berkerumun. Simpul daun   dari indeks berkerumun berisi data   halaman. Indeks tidak tercakup adalah a   tipe khusus dari indeks di mana   urutan logis dari indeks tidak   cocok dengan urutan tersimpan fisik   baris pada disk. Simpul daun a   indeks tidak tercakup tidak terdiri dari   halaman data. Sebaliknya, daun   node mengandung baris indeks.

Apa yang saya temukan di SO Apa perbedaan antara indeks yang terkluster dan yang tidak berkelompok?.

Bisakah seseorang menjelaskan ini dalam bahasa Inggris biasa?


805
2017-08-09 15:59


asal


Jawaban:


Dengan indeks berkerumun, baris-baris disimpan secara fisik pada disk dalam urutan yang sama dengan indeks. Oleh karena itu, hanya ada satu indeks yang terkelompok.

Dengan indeks yang tidak bergerombol ada daftar kedua yang memiliki pointer ke baris fisik. Anda dapat memiliki banyak indeks non-cluster, meskipun setiap indeks baru akan meningkatkan waktu yang diperlukan untuk menulis catatan baru.

Biasanya lebih cepat untuk membaca dari indeks berkerumun jika Anda ingin mendapatkan kembali semua kolom. Anda tidak harus pergi dulu ke indeks dan kemudian ke meja.

Menulis ke tabel dengan indeks berkerumun bisa lebih lambat, jika ada kebutuhan untuk mengatur ulang data.


804
2017-08-09 16:05



Indeks berkerumun berarti Anda memberi tahu basis data untuk menyimpan nilai dekat yang benar-benar dekat satu sama lain pada disk. Ini memiliki manfaat dari pemindaian cepat / pengambilan kembali catatan yang jatuh ke dalam beberapa kisaran nilai indeks berkerumun.

Misalnya, Anda memiliki dua tabel, Pelanggan dan Pemesanan:

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

Jika Anda ingin cepat mengambil semua pesanan dari satu pelanggan tertentu, Anda mungkin ingin membuat indeks berkerumun di kolom "CustomerID" pada tabel Pesanan. Dengan cara ini, catatan dengan CustomerID yang sama akan disimpan secara fisik dekat satu sama lain pada disket (terkelompok) yang mempercepat pengambilan mereka.

P.S. Indeks pada CustomerID jelas tidak akan unik, jadi Anda perlu menambahkan kolom kedua untuk "meng-uniquify" indeks atau membiarkan database menangani itu untuk Anda, tetapi itu adalah cerita lain.

Mengenai beberapa indeks. Anda hanya dapat memiliki satu indeks berkerumun per tabel karena ini mendefinisikan bagaimana data diatur secara fisik. Jika Anda menginginkan sebuah analogi, bayangkan sebuah ruangan besar dengan banyak tabel di dalamnya. Anda dapat menempatkan tabel ini untuk membentuk beberapa baris atau menarik mereka semua bersama-sama untuk membentuk sebuah meja konferensi besar, tetapi tidak keduanya pada saat yang bersamaan. Sebuah tabel dapat memiliki indeks lain, mereka kemudian akan menunjuk ke entri dalam indeks berkerumun yang pada gilirannya akhirnya akan mengatakan di mana menemukan data yang sebenarnya.


528
2017-08-09 16:01



Dalam penyimpanan berorientasi baris SQL Server, baik indeks berkerumun maupun tidak tersusun diatur sebagai pohon B.

enter image description here

(Sumber Gambar)

Perbedaan utama antara indeks berkerumun dan indeks tidak bergerombol adalah bahwa tingkat daun dari indeks berkerumun aku s meja. Ini memiliki dua implikasi.

  1. Baris pada halaman daun indeks berkerumun selalu berisi sesuatu untuk masing-masing kolom (tidak jarang) di dalam tabel (baik nilai, atau penunjuk ke nilai yang sebenarnya).
  2. Indeks berkerumun adalah salinan utama dari sebuah tabel.

Indeks yang tidak bergerombol juga dapat melakukan poin 1 dengan menggunakan INCLUDE klausa (Sejak SQL Server 2005) untuk secara eksplisit menyertakan semua kolom non kunci tetapi mereka adalah representasi sekunder dan selalu ada salinan lain dari data di sekitar (tabel itu sendiri).

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)

Dua indeks di atas hampir identik. Dengan halaman indeks tingkat atas yang mengandung nilai untuk kolom kunci A,B dan halaman tingkat daun mengandung A,B,C,D

Hanya ada satu indeks berkerumun per tabel, karena baris data   sendiri dapat diurutkan hanya dalam satu pesanan.

Kutipan di atas dari buku-buku SQL Server online menyebabkan banyak kebingungan

Menurut pendapat saya itu akan lebih baik diutarakan sebagai.

Hanya ada satu indeks berkerumun per tabel, karena baris tingkat daun dari indeks berkerumun adalah baris tabel.

Buku-buku kutipan online tidak salah tetapi Anda harus jelas bahwa "pemilahan" dari kedua indeks yang tidak bergerombol dan bergerombol adalah logis bukan fisik. Jika Anda membaca halaman pada tingkat daun dengan mengikuti daftar tertaut dan membaca baris pada halaman dalam urutan susunan slot maka Anda akan membaca baris indeks dalam urutan terurut tetapi secara fisik halaman mungkin tidak disortir. Keyakinan yang umum dipegang bahwa dengan indeks berkerumun baris selalu disimpan secara fisik pada disk dalam urutan yang sama dengan indeks kunci salah.

Ini akan menjadi implementasi yang absurd. Sebagai contoh jika baris dimasukkan ke tengah meja 4GB SQL Server tidak tidak harus menyalin 2GB data di dalam file untuk memberi ruang bagi baris yang baru disisipkan.

Sebagai gantinya, pemisah halaman terjadi. Setiap halaman pada tingkat daun dari indeks yang berkerumun dan tidak bergerombol memiliki alamat (File:Page) dari halaman berikutnya dan sebelumnya dalam urutan kunci logis. Halaman-halaman ini tidak perlu bersebelahan atau dalam urutan kunci.

misalnya rantai halaman terkait mungkin 1:2000 <-> 1:157 <-> 1:7053

Ketika sebuah halaman terpecah terjadi halaman baru dialokasikan dari mana saja di filegroup (baik dari tingkat campuran, untuk meja kecil, atau tingkat seragam yang tidak kosong milik objek itu atau tingkat seragam baru dialokasikan). Ini mungkin tidak berada di file yang sama jika grup file berisi lebih dari satu.

Sejauh mana urutan logis dan kedekatan berbeda dari versi fisik ideal adalah tingkat fragmentasi logis.

Dalam database yang baru dibuat dengan satu file, saya menjalankan yang berikut ini.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

Lalu periksa tata letak halaman dengan

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

Hasilnya ada di mana-mana. Baris pertama dalam urutan kunci (dengan nilai 1 - disorot dengan panah di bawah) berada di hampir halaman fisik terakhir.

enter image description here

Fragmentasi dapat dikurangi atau dihilangkan dengan membangun kembali atau mereorganisasi suatu indeks untuk meningkatkan korelasi antara tatanan logis dan tatanan fisik.

Setelah berlari

ALTER INDEX ix ON T REBUILD;

Saya mendapat yang berikut ini

enter image description here

Jika tabel tidak memiliki indeks berkerumun, ini disebut heap.

Indeks yang tidak bergerombol dapat dibangun di atas tumpukan atau indeks berkerumun. Mereka selalu berisi pencari baris kembali ke tabel dasar. Dalam kasus tumpukan ini adalah pengidentifikasi baris fisik (rid) dan terdiri dari tiga komponen (File: Page: Slot). Dalam kasus indeks Clustered, locator baris logis (kunci indeks berkerumun).

Untuk kasus terakhir jika indeks non cluster sudah secara alami menyertakan kolom kunci CI (s) baik sebagai kolom kunci NCI atau INCLUDE-d kolom maka tidak ada yang ditambahkan. Jika tidak, kolom kunci CI yang hilang (s) diam-diam ditambahkan ke dalam NCI.

SQL Server selalu memastikan bahwa kolom kunci unik untuk kedua jenis indeks. Mekanisme di mana ini diberlakukan untuk indeks tidak dinyatakan sebagai unik berbeda antara kedua jenis indeks.

Indeks terkelompok mendapatkan uniquifier ditambahkan untuk setiap baris dengan nilai kunci yang menduplikasi baris yang ada. Ini hanya bilangan bulat naik.

Untuk indeks non-kelompok tidak dinyatakan sebagai SQL Server yang unik secara diam-diam menambahkan pencari baris ke dalam kunci indeks yang tidak bergerombol. Ini berlaku untuk semua baris, bukan hanya baris yang benar-benar duplikat.

Nomenklatur pengelompokan vs non-berkelompok juga digunakan untuk indeks kolom toko. Kertas Peningkatan ke Toko Kolom SQL Server menyatakan

Meskipun data penyimpanan kolom tidak benar-benar "terkelompok" pada kunci apa pun, kami   memutuskan untuk mempertahankan konvensi penghitungan SQL Server tradisional   ke indeks utama sebagai indeks berkerumun.


206
2018-06-28 19:16



Saya menyadari ini adalah pertanyaan yang sangat lama, tetapi saya pikir saya akan menawarkan analogi untuk membantu mengilustrasikan jawaban yang bagus di atas.

INDEKS CLUSTERED

Jika Anda masuk ke perpustakaan umum, Anda akan menemukan bahwa semua buku disusun dalam urutan tertentu (kemungkinan besar Sistem Desimal Dewey, atau DDS). Ini sesuai dengan "indeks berkerumun" dari buku-buku. Jika DDS # untuk buku yang Anda inginkan ada 005.7565 F736s, Anda akan mulai dengan menempatkan deretan rak buku yang berlabel 001-099 atau semacam itu. (Tanda endcap ini di ujung tumpukan sesuai dengan "simpul antara" dalam indeks.) Akhirnya Anda akan menelusuri ke rak tertentu berlabel 005.7450 - 005.7600, maka Anda akan memindai sampai Anda menemukan buku dengan DDS # yang ditentukan, dan pada titik itu Anda telah menemukan buku Anda.

INDEKS NON-CLUSTERED

Tetapi jika Anda tidak datang ke perpustakaan dengan DDS # dari buku Anda dihafal, maka Anda akan membutuhkan indeks kedua untuk membantu Anda. Di masa lalu Anda akan menemukan di depan perpustakaan sebuah laci yang indah yang dikenal sebagai "Katalog Kartu". Di dalamnya ada ribuan 3x5 kartu - satu untuk setiap buku, diurutkan berdasarkan abjad (berdasarkan judul, mungkin). Ini sesuai dengan "indeks tidak terkelompok". Katalog kartu ini disusun dalam struktur hirarkis, sehingga setiap laci akan diberi label dengan kisaran kartu yang ada di dalamnya.Ka - Kl, sebagai contoh; yaitu, "simpul antara"). Sekali lagi, Anda akan mengebor sampai Anda menemukan buku Anda, tetapi di ini kasus, setelah Anda menemukannya (yaitu, "simpul daun"), Anda tidak memiliki buku itu sendiri, tetapi hanya sebuah kartu dengan indeks number (DDS #) yang dapat Anda temukan buku aktual dalam indeks cluster.

Tentu saja, tidak ada yang akan menghentikan pustakawan dari memfotokopi semua kartu dan menyortirnya dalam urutan yang berbeda dalam katalog kartu yang terpisah. (Biasanya ada setidaknya dua katalog seperti itu: satu diurutkan berdasarkan nama penulis, dan satu per judul.) Pada prinsipnya, Anda dapat memiliki banyak dari indeks "tidak berkelompok" seperti yang Anda inginkan.


67
2017-10-26 21:06



Temukan di bawah ini beberapa karakteristik indeks berkerumun dan tidak bergerombol:

Indeks Terkelompok

  1. Clustered indexes adalah indeks yang secara unik mengidentifikasi baris dalam tabel SQL.
  2. Setiap tabel dapat memiliki tepat satu indeks berkerumun.
  3. Anda dapat membuat indeks berkerumun yang mencakup lebih dari satu kolom. Sebagai contoh: create Index index_name(col1, col2, col.....).
  4. Secara default, kolom dengan kunci utama sudah memiliki indeks berkerumun.

Indeks Non-cluster

  1. Indeks non-cluster seperti indeks sederhana. Mereka hanya digunakan untuk pengambilan data yang cepat. Tidak yakin memiliki data unik.

60
2018-01-21 14:21



Aturan main-jempol non-teknis yang sangat sederhana adalah bahwa indeks-indeks yang berkelompok biasanya digunakan untuk kunci utama Anda (atau, setidaknya, kolom unik) dan yang tidak terkumpul digunakan untuk situasi lain (mungkin kunci asing) . Memang, SQL Server akan secara default membuat indeks berkerumun di kolom kunci primer Anda (s). Seperti yang telah Anda pelajari, indeks berkerumun berhubungan dengan cara data secara fisik diurutkan pada disk, yang berarti itu adalah pilihan serba yang baik untuk sebagian besar situasi.


41
2017-08-09 16:17



Indeks Terkelompok

Indeks berkerumun menentukan urutan fisik DATA dalam tabel. Untuk alasan ini, tabel hanya memiliki 1 indeks berkerumun.

seperti "kamus" Tidak membutuhkan Indeks lain, indeksnya sudah sesuai dengan kata-kata

Indeks Noncluster

Indeks yang tidak bergerombol adalah analog dengan indeks dalam Buku. Data disimpan di satu tempat. itu indeks disimpan di tempat lain dan indeks memiliki pointer ke lokasi penyimpanan data. Untuk alasan ini tabel memiliki lebih dari 1 indeks Noncluster.

seperti "buku Kimia" saat menatap ada indeks terpisah untuk menunjuk lokasi Bab dan Pada "AKHIR" ada Indeks lain yang menunjuk lokasi KATA umum


4
2018-01-21 18:47



Indeks Terkelompok

Indeks digolongkan dan menyimpan baris data dalam tabel atau tampilan berdasarkan nilai kunci mereka. Ini adalah kolom yang termasuk dalam definisi indeks. Hanya ada satu indeks berkerumun per tabel, karena data rows sendiri dapat diurutkan hanya dalam satu urutan.

Satu-satunya waktu baris data dalam tabel disimpan dalam urutan yang terurut adalah ketika tabel berisi indeks berkerumun. Ketika tabel memiliki indeks berkerumun, tabel disebut tabel berkerumun. Jika tabel tidak memiliki indeks berkerumun, baris datanya disimpan dalam struktur tak teratur yang disebut heap.

Nonclustered

Indeks yang tidak berserakan memiliki struktur terpisah dari baris data. Sebuah indeks tidak bersisi berisi nilai kunci indeks nonkluster dan setiap entri nilai kunci memiliki penunjuk ke baris data yang berisi nilai kunci. Penunjuk dari baris indeks dalam indeks nonkluster ke baris data disebut pencari baris. Struktur pelacak baris tergantung pada apakah halaman data disimpan dalam tumpukan atau tabel berkerumun. Untuk heap, pencari baris adalah penunjuk ke baris. Untuk tabel berkerumun, pencari baris adalah kunci indeks yang dikelompokkan.

Anda dapat menambahkan kolom nonkunci ke tingkat daun dari indeks tidak berskala untuk melewati batas kunci indeks yang ada, dan mengeksekusi sepenuhnya tertutup, diindeks, kueri. Untuk informasi lebih lanjut, lihat Buat Indeks dengan Kolom yang Disertakan. Untuk detail tentang batas kunci indeks lihat Spesifikasi Kapasitas Maksimum untuk SQL Server.

Referensi: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-description 


3
2017-08-28 00:10



Jika file yang berisi catatan secara berurutan dipesan, indeks pengelompokan adalah indeks yang kunci pencariannya juga menentukan urutan berurutan dari file tersebut. Indeks penggumpalan juga disebut indeks utama; istilah indeks utama dapat muncul untuk menunjukkan indeks pada kunci primer, tetapi indeks tersebut pada kenyataannya dapat dibangun pada kunci pencarian apa pun. Kunci pencarian dari indeks pengelompokan sering menjadi kunci utama, meskipun itu belum tentu demikian. Indeks yang kunci pencariannya menentukan urutan yang berbeda dari urutan sekuensial dari file disebut indeks nonkluster, atau indeks sekunder. Istilah “terkelompok"Dan"tidak tercakup"Sering digunakan di tempat"kekelompokan"Dan"tidak mengkilap. "


0
2017-07-12 16:24