Różnice między INDEX, PRIMARY, UNIQUE, FULLTEXT w MySQL?

Jakie są różnice między PRIMARY, UNIQUE, INDEX i FULLTEXT podczas tworzenia tabel MySQL?

Jak mam z nich korzystać?

Różnice

• KEY lub INDEX odnosi się do normalnego nieunikalnego indeksu. Niedopróżnione wartości indeksu są dozwolone, więc indeks może zawierać wiersze o identycznych wartościach we wszystkich kolumnach indeksu. Indeksy te nie wymuszają żadnych ograniczeń danych, dlatego są używane tylko w celu uzyskania dostępu - do szybkiego osiągnięcia określonych zakresów rekordów bez skanowania wszystkich rekordów.

• UNIQUE odnosi się do indeksu, w którym wszystkie wiersze indeksu muszą być unikalne. Oznacza to, że ten sam wiersz może nie mieć identycznych wartości innych niż NULL dla wszystkich kolumn w tym indeksie, co inny wiersz. Oprócz szybkiego osiągania określonych zakresów rekordów indeksy UNIQUE mogą służyć do wymuszania ograniczeń danych, ponieważ system bazy danych nie pozwala na złamanie reguły odrębnych wartości podczas wstawiania lub aktualizowania danych.

  Twój system bazy danych może zezwalać na stosowanie indeksu UNIQUE do kolumn, które zezwalają na wartości NULL, w którym to przypadku dwa wiersze mogą być identyczne, jeśli oba zawierają wartość NULL (uzasadnienie jest takie, że wartość NULL nie jest równa sobie). Jednak w zależności od zastosowania może być to niepożądane: jeśli chcesz temu zapobiec, nie należy dopuszczać NULL wartości w odpowiednich kolumnach.

• PODSTAWOWY działa dokładnie tak, jak indeks UNIKALNY, z tym wyjątkiem, że zawsze nazywa się „PODSTAWOWY”, a na stole może znajdować się tylko jeden (i zawsze taki powinien być; chociaż niektóre systemy baz danych tego nie wymuszają). Indeks PODSTAWOWY ma służyć jako podstawowy sposób jednoznacznej identyfikacji dowolnego wiersza w tabeli, dlatego w przeciwieństwie do UNIQUE nie należy go stosować w żadnych kolumnach, które dopuszczają wartości NULL. Twój indeks PODSTAWOWY powinien znajdować się na najmniejszej liczbie kolumn wystarczających do jednoznacznej identyfikacji wiersza. Często jest to tylko jedna kolumna zawierająca unikalny automatycznie zwiększany numer, ale jeśli istnieje coś innego, co może jednoznacznie zidentyfikować wiersz, na przykład „Countrycode” na liście krajów, możesz użyć tego zamiast tego.

  Niektóre systemy baz danych (takie jak InnoDB MySQL) przechowują rekordy tabeli na dysku w kolejności, w jakiej występują w indeksie PIERWOTNYM.

• Indeksy FULLTEXT różnią się od wszystkich powyższych, a ich zachowanie różni się znacznie między systemami baz danych. Indeksy FULLTEXT są użyteczne tylko w przypadku wyszukiwania pełnotekstowego za pomocą klauzuli MATCH () / AGAINST (), w przeciwieństwie do powyższych trzech - które zazwyczaj są implementowane wewnętrznie za pomocą b-drzew (umożliwiając wybieranie, sortowanie lub zakresy zaczynające się od lewej kolumny) lub tabele skrótów (pozwalające na wybór zaczynając od lewej kolumny).

  Tam, gdzie inne typy indeksów są ogólnego przeznaczenia, indeks FULLTEXT specjalizuje się w tym, że służy wąskiemu celowi: służy tylko do wyszukiwania pełnotekstowego.

Podobieństwa

• Wszystkie te indeksy mogą zawierać więcej niż jedną kolumnę.

• Z wyjątkiem FULLTEXT, kolejność kolumn jest znacząca: aby indeks był użyteczny w zapytaniu, zapytanie musi wykorzystywać kolumny z indeksu zaczynające się od lewej - nie może używać tylko drugiej, trzeciej lub czwartej części indeks, chyba że używa także poprzednich kolumn indeksu w celu dopasowania wartości statycznych. (Aby indeks FULLTEXT był przydatny w zapytaniu, zapytanie musi używać wszystkich kolumn indeksu).

Wszystkie te rodzaje wskaźników.

podstawowy: musi być unikalny, jest indeksem, jest (prawdopodobnie) indeksem fizycznym, może być tylko jeden na tabelę.

wyjątkowy: jak mówi. Nie możesz mieć więcej niż jednego wiersza z krotką tej wartości. Zauważ, że ponieważ unikalny klucz może znajdować się w więcej niż jednej kolumnie, niekoniecznie oznacza to, że każda pojedyncza kolumna w indeksie jest unikalna, ale każda kombinacja wartości w tych kolumnach jest unikalna.

indeks: jeśli nie jest pierwotny ani unikalny, nie ogranicza wartości wstawianych do tabeli, ale pozwala na bardziej efektywne wyszukiwanie.

pełny tekst: bardziej wyspecjalizowana forma indeksowania, która umożliwia wyszukiwanie pełnotekstowe. Pomyśl o tym jako o (zasadniczo) tworzeniu „indeksu” dla każdego „słowa” w określonej kolumnie.

Wydaje mi się, że zostało to dobrze omówione, może poza następującymi:

• Proste KEY/ INDEX(lub inaczej nazywane SECONDARY INDEX) zwiększają wydajność, jeśli selektywność jest wystarczająca. W tej kwestii zwykłą rekomendacją jest to, że jeśli liczba rekordów w zestawie wyników, w których zastosowano indeks, przekroczy 20% całkowitej liczby rekordów tabeli nadrzędnej, wówczas indeks będzie nieskuteczny. W praktyce każda architektura będzie się różnić, ale pomysł jest nadal poprawny.

• Indeksy wtórne (i to jest bardzo specyficzne dla mysql) nie powinny być postrzegane jako całkowicie oddzielne i różne obiekty od klucza podstawowego. W rzeczywistości oba powinny być używane łącznie i, gdy znane są te informacje, stanowią dodatkowe narzędzie do mysql DBA: w Mysql indeksy osadzają klucz podstawowy. Prowadzi to do znacznej poprawy wydajności, szczególnie przy sprytnym budowaniu niejawnych indeksów pokrycia, takich jak tam opisane

• Jeśli uważasz, że Twoje dane powinny być UNIQUE, użyj unikalnego indeksu. Możesz pomyśleć, że jest opcjonalny (na przykład wypracowanie go na poziomie aplikacji) i że zrobi to normalny indeks, ale w rzeczywistości stanowi dla Mysql gwarancję, że każdy wiersz jest unikalny, co nawiasem mówiąc, zapewnia korzyści w zakresie wydajności.

• Możesz używać FULLTEXT(lub inaczej nazywane SEARCH INDEX) tylko z Innodb (w MySQL 5.6.4 i nowszych) i Myisam Engines

• Można używać tylko FULLTEXTna CHAR, VARCHARa TEXTtypy kolumn
• FULLTEXTIndeks obejmuje dużo więcej niż tylko tworzenie indeksu. Utworzono kilka tabel systemowych, całkowicie oddzielny system buforowania oraz zastosowano określone reguły i optymalizacje. Zobacz http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html i http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html