Каква е точно разликата между първичен и вторичен индекс? И какво е предимството на един пред друг?


Отговор 1:

Нека първо да кажа приликите:

прилики

  • И двете структури на индекса са реализирани като отделни първокласни обекти в базата данни. Това означава, че таблицата и съответстващият й индекс (първичен или вторичен) съществуват като две отделни структури. Добре прилагайте ниво на индирекция, при което заявките първо търсят индекса, и използвайте резултата от това търсене, за да извлечете директно записа, към който сочи. Индексните блокове и в двата типа индекси поддържат подредените записи; т.е. какъвто и да е действителният запис на индекса → обикновено a , записите в индексния блок винаги са сортирани по индекса / ключа за търсене.

Различията

Основен индекс

  • Основен индекс влияе върху съхранението и организацията на редовете в блоковете с данни. Блоковете с данни са дискови блокове, които съхраняват действителните данни за редовете (множество колони). Приоритетният индекс изисква редовете в блоковете с данни да бъдат подредени на индексния ключ. Така че освен самите записи на индекса, които са сортирани в индексния блок, първичният индекс също налага подреждане на редове в блоковете с данни. По-долу диаграмата, заимствана от „Внедряване на система от бази данни от Garcia Molina et al“, показва как индексните записи в индексните блокове (отляво) съдържа указатели (това са локатори на редове в терминологията на базата данни) към съответните редове в блокове с данни (дясна страна). Всеки блок данни има редове в подреден ред според индексния ключ.
  • Основният индекс може да бъде създаден както в ключови, така и в не-ключови колони. Няма такова нещо, тъй като първичният индекс е предназначен само за първичен ключ. Но, да, обикновено той се създава на първичния ключ на таблицата. Тъй като първичният индекс променя начина, по който (трябва да поддържа подредени редове) данните са организирани в таблица, може да има най-много 1 първичен индекс на дадена таблица.

Вторичен индекс

  • Вторичният индекс не оказва влияние върху това как реално са организирани редовете в блокове с данни. Те могат да бъдат в произволен ред. Единственото подреждане е wrt индексния ключ в индексните блокове. По-долу диаграмата, заимствана от „Внедряване на система от бази данни от Garcia Molina et al“, показва как записите на индекса в индексните блокове (отляво) съдържа указатели (това са локатори на редове в терминологията на базата данни ) към съответните редове в блокове с данни (дясна страна). Блоковете с данни нямат сортирани редове в индексния ключ

сравнение

  • Първо, потребителят може да дефинира множество вторични индекси, тъй като те не оказват влияние върху организацията на редовете в таблицата. Но може да има само 1 първичен индекс. Тъй като първичният индекс не е задължително да е на първичен ключ, първичният индекс може да има и дублиращи се индексни ключове. Всъщност примерът, показан по-горе за вторичен индекс, е за дублиращи се ключове. Това е общо и за двете. Очевидно, ако първичният индекс е създаден на първичен ключ, тогава не може да има дублиращи се индексни ключове, тъй като първичният ключ налага UNIQUE CONSTRAINT.Новите първични и вторични индекси могат да се използват за търсене на точки и заявки за обхват. Но очакванията за обхвата се очакват да бъдат по-бързи за основния индекс и в двата случая - уникален индексен ключ и дублиращи се индексни ключове. Търсенето на точки в случай на NON UNIQUE индекс се очаква да бъде по-бързо с първичен индекс. Но ако индексът е УНИКАЛЕН, търсенето на точка с първичен и вторичен индекс в идеалния случай трябва да отнеме еднакъв период от време - най-малко същото количество I / O Причината е, че първичният индекс принуждава редовете в блоковете с данни да бъдат подредени. Така че, ако потребителят се интересува от намирането на редове за WHERE KEY> = 20 AND KEY <= 40, има голям шанс да прочете по-малко дискови блокове и следователно по-малко I / O. Възможно е редовете, съответстващи на тези ключове, да са в един и същи блок данни (дори ако индексът не е уникален). От друга страна, вторичният индекс няма контрол върху организацията на редовете. Така че за същия пример за намиране на всички записи между 20 и 40 е възможно записът (ите), съответстващ на всеки клавиш, да се намира в свой собствен блок данни. Това очевидно предполага, че ще има повече I / O и по този начин заявките могат да бъдат по-малко ефективни при вторичен индекс. Резултатът от търсенето ще бъде локатор на редове и базата данни трябва да го следва, за да стигне до реалния запис. Така един I / O ще бъде там. Тъй като първичният индекс принуждава редовете да бъдат подредени в блокове с данни, DML ще бъдат по-малко ефективни. Тъй като DML трябва да поддържат редове в подреден ред в блока с данни, INSERT / UPDATE ще предизвика често движение на реда, освен ако потребителят не се погрижи вмъкванията да се извършват в последователен ред. Всеки DML, който води до движение на редовете на ред в данните блокът също ще изисква актуализация на съответната основна структура на индекса след въвеждането на индекса сега трябва да бъде актуализиран с нов локатор на редове (защото редът беше преместен). За движението на ROW също ще е необходима актуализация на структурата на вторичния индекс, но вероятността INSERT, причиняваща движение на редица, е сравнително ниска за вторичен индекс, тъй като INSERT прави не е необходимо да поддържате редове в подреден ред в рамките на блок данни. Случаи като редица с увеличаване на размера и по този начин трябва да бъдат преместени към всички заедно, различен блок са един от малкото случаи на движение на реда във вторичен индекс.

Отговор 2:

Основен индекс е структура за съхранение, съдържаща действителните основни редове на таблицата в B-Tree оформление за съхранение. Обикновено индексът се организира около PRIMARY KEY, ако има такъв.

Първичните индекси понякога се наричат ​​„клъстерирани индекси“, въпреки че не всички неща, наречени клъстерирани индекси, са „истински“ реализации на първични индекси (т.е. този в PostgreSQL не е).

По-голямата част от двигателите за съхранение съхраняват редове на базата на таблицата без конкретен ред, извлечен от данните на колоните, и не прилагат „истински“ първични индекси.

Само няколко двигателя за съхранение на DB всъщност реализират първични индекси. Oracle „Index Organilized Tables“ и MySQL InnoDB са най-широко използваните двигатели за съхранение, които внедряват първични индекси.

Най-голямото предимство на структурата за съхранение, организирано от индекс, е търсенето на PRIMARY KEY изискване на едно ходене на основното B-дърво. В повечето други аранжировки за съхранение, PRIMARY KEY търсенето изисква две разходки: едната, за да потърси показалеца на реда на основната таблица във вторична структура на индекса, изградена около PK, и самия ред на реда.

В тези договорености, търсенето на PK не е по-евтино от търсенето с помощта на който и да е друг индекс.

Тъй като основен индекс се използва за организацията на основна таблица, получавате само един на таблица.

Най-големият недостатък на първичния индекс за съхранение в сравнение с други видове съхранение е, че те могат да се зареждат по-бавно, тъй като всеки INSERT на основната таблица трябва да ходи по B-Tree, за да разбере къде да постави реда. Освен това, тъй като повечето двигатели за съхранение, които поддържат първични индекси, използват основния индексен ключ като идентификатор за търсене на вторични индекси, можете да имате вторични индекси, които заемат много място, ако имате дълъг PRIMARY KEY (т.е. ако използвате нещо като Глобално уникален идентификатор като ПК и имат други индекси).


Отговор 3:

Основен индекс е структура за съхранение, съдържаща действителните основни редове на таблицата в B-Tree оформление за съхранение. Обикновено индексът се организира около PRIMARY KEY, ако има такъв.

Първичните индекси понякога се наричат ​​„клъстерирани индекси“, въпреки че не всички неща, наречени клъстерирани индекси, са „истински“ реализации на първични индекси (т.е. този в PostgreSQL не е).

По-голямата част от двигателите за съхранение съхраняват редове на базата на таблицата без конкретен ред, извлечен от данните на колоните, и не прилагат „истински“ първични индекси.

Само няколко двигателя за съхранение на DB всъщност реализират първични индекси. Oracle „Index Organilized Tables“ и MySQL InnoDB са най-широко използваните двигатели за съхранение, които внедряват първични индекси.

Най-голямото предимство на структурата за съхранение, организирано от индекс, е търсенето на PRIMARY KEY изискване на едно ходене на основното B-дърво. В повечето други аранжировки за съхранение, PRIMARY KEY търсенето изисква две разходки: едната, за да потърси показалеца на реда на основната таблица във вторична структура на индекса, изградена около PK, и самия ред на реда.

В тези договорености, търсенето на PK не е по-евтино от търсенето с помощта на който и да е друг индекс.

Тъй като основен индекс се използва за организацията на основна таблица, получавате само един на таблица.

Най-големият недостатък на първичния индекс за съхранение в сравнение с други видове съхранение е, че те могат да се зареждат по-бавно, тъй като всеки INSERT на основната таблица трябва да ходи по B-Tree, за да разбере къде да постави реда. Освен това, тъй като повечето двигатели за съхранение, които поддържат първични индекси, използват основния индексен ключ като идентификатор за търсене на вторични индекси, можете да имате вторични индекси, които заемат много място, ако имате дълъг PRIMARY KEY (т.е. ако използвате нещо като Глобално уникален идентификатор като ПК и имат други индекси).


Отговор 4:

Основен индекс е структура за съхранение, съдържаща действителните основни редове на таблицата в B-Tree оформление за съхранение. Обикновено индексът се организира около PRIMARY KEY, ако има такъв.

Първичните индекси понякога се наричат ​​„клъстерирани индекси“, въпреки че не всички неща, наречени клъстерирани индекси, са „истински“ реализации на първични индекси (т.е. този в PostgreSQL не е).

По-голямата част от двигателите за съхранение съхраняват редове на базата на таблицата без конкретен ред, извлечен от данните на колоните, и не прилагат „истински“ първични индекси.

Само няколко двигателя за съхранение на DB всъщност реализират първични индекси. Oracle „Index Organilized Tables“ и MySQL InnoDB са най-широко използваните двигатели за съхранение, които внедряват първични индекси.

Най-голямото предимство на структурата за съхранение, организирано от индекс, е търсенето на PRIMARY KEY изискване на едно ходене на основното B-дърво. В повечето други аранжировки за съхранение, PRIMARY KEY търсенето изисква две разходки: едната, за да потърси показалеца на реда на основната таблица във вторична структура на индекса, изградена около PK, и самия ред на реда.

В тези договорености, търсенето на PK не е по-евтино от търсенето с помощта на който и да е друг индекс.

Тъй като основен индекс се използва за организацията на основна таблица, получавате само един на таблица.

Най-големият недостатък на първичния индекс за съхранение в сравнение с други видове съхранение е, че те могат да се зареждат по-бавно, тъй като всеки INSERT на основната таблица трябва да ходи по B-Tree, за да разбере къде да постави реда. Освен това, тъй като повечето двигатели за съхранение, които поддържат първични индекси, използват основния индексен ключ като идентификатор за търсене на вторични индекси, можете да имате вторични индекси, които заемат много място, ако имате дълъг PRIMARY KEY (т.е. ако използвате нещо като Глобално уникален идентификатор като ПК и имат други индекси).