Tworzenie indeksu w MySQL: Samouczek dotyczący indeksu MySQL [2022]
Opublikowany: 2021-01-01Data Scientist lub programista będzie musiał pracować z dużymi ilościami danych i musi być w stanie efektywnie sobie z nimi poradzić, aby przyspieszyć wykonanie. Muszą również wiedzieć, jak dane są zorganizowane i najszybsze metody łatwego dostępu do konkretnych potrzebnych danych.
Ponieważ MySQL to system zarządzania relacyjnymi bazami danych, który ma zastosowanie w różnych sektorach, takich jak internetowe bazy danych, aplikacje e-commerce, media społecznościowe itp., naukowcy zajmujący się danymi muszą znać wszystkie techniki związane z MySQL, z których jednym są indeksy. Artykuł opisuje jak stworzyć indeks w MySQL.
Spis treści
Co to jest indeks?
W MySQL indeksy są używane do sortowania danych w zorganizowany sekwencyjny sposób oraz do wydajnego i szybkiego pobierania danych. Są one tworzone przy użyciu co najmniej jednej kolumny, która będzie używana do filtrowania danych. Indeksy to typ tabeli, w której przechowywane jest pole podstawowe lub pole indeksowe oraz wskaźnik do każdego rekordu w tabeli głównej.
Dla lepszego zrozumienia można posłużyć się jako przykład indeksem książkowym. Zamiast szukać strony, przeglądając wszystkie strony w książce, można sprawdzić indeks książki, aby znaleźć stronę, której szukasz.
W większości systemów bazodanowych wymagana jest wysoka i szybka wydajność. Wiele firm inwestuje ogromne sumy pieniędzy w sprzęt, aby szybciej wyszukiwać i manipulować danymi. Ale poprzez optymalizację bazy danych można obniżyć koszty.
Czas odpowiedzi jest zwykle dłuższy, ponieważ rekordy są przechowywane losowo, a zapytania wyszukiwania muszą znaleźć potrzebne dane, przeglądając całe przechowywane rekordy. Indeksy ułatwiają i przyspieszają to wyszukiwanie.
Przeczytaj: Zmień nazwę kolumny w SQL
Utwórz indeks
Indeks w MySQL można utworzyć w tabeli przy użyciu następującej podstawowej składni.
CREATE INDEX nazwa_indeksu nazwa_tabeli (nazwa_kolumny);
Możesz także utworzyć unikalny indeks w tabeli. Oznacza to, że nazwa jest unikatowa i żadne dwa wiersze nie mogą mieć tej samej nazwy. Można do tego użyć następującej składni.
CREATE UNIQUE INDEX nazwa_indeksu ON nazwa_tabeli (nazwa_kolumny);
Jeśli tworzony jest indeks pomocniczy dla kolumny, MySQL przechowuje wartości w oddzielnej strukturze danych, takiej jak B-Tree i Hash. W MySQL, jeśli kolumny są ciągami, możesz utworzyć indeks dla wiodącej części wartości kolumny ciągu, używając składni:
nazwa_kolumny (długość)
Aby utworzyć część klucza prefiksu kolumny podczas tworzenia tabeli, można użyć następującej składni:
UTWÓRZ TABELĘ nazwa_tabeli(
lista_kolumn,
INDEKS(nazwa_kolumny(długość))
);
Aby dodać indeks do istniejącej tabeli:
CREATE INDEX nazwa_indeksu ON nazwa_tabeli (nazwa_kolumny(długość));
Długość tutaj to liczba znaków, jeśli jest to niebinarny typ łańcucha, taki jak CHAR, VARCHAR i TEXT, oraz liczba bajtów, jeśli jest to binarny typ ciągu, taki jak BINARY, VARBINARY i BLOB. W MySQL można również utworzyć części kluczowe prefiksu kolumny dla kolumn CHAR, VARCHAR, BINARY i VARBINARY. Jeśli utworzono indeksy dla kolumn BLOB i TEXT, konieczne jest określenie części klucza prefiksu kolumny.
W przypadku indeksów FULLTEXT, które są obsługiwane tylko dla tabel InnoDB i MyISAM, mogą one zawierać tylko kolumny CHAR, VARCHAR i TEXT. Nie obsługuje indeksowania prefiksów kolumn, a jeśli zostanie określona jakakolwiek długość prefiksu, zostanie ona zignorowana.
Trzeba przeczytać: Pomysły na projekty SQL
Zmienianie tabel w celu dodania lub usunięcia indeksu
Aby dodać indeksy do tabeli, można użyć czterech typów instrukcji:
- Aby dodać klucz podstawowy:
ALTER TABLE nazwa_tabeli ADD PRIMARY KEY (lista_kolumn);
Zindeksowane wartości w tym miejscu powinny być unikatowe i nie mogą mieć wartości NULL.
- Dla indeksu o unikalnych wartościach:
ALTER TABLE nazwa_tabeli ADD UNIQUE nazwa_indeksu (lista_kolumn);
Wartości powinny być niepowtarzalne, z wyjątkiem NULL, który może pojawiać się wiele razy.
- Aby dodać zwykły indeks:
ALTER TABLE nazwa_tabeli ADD INDEX nazwa_indeksu (lista_kolumn);
Tutaj wartości mogą pojawić się więcej niż raz.
- Aby utworzyć specjalny indeks PEŁNOTEKSTOWY:
ALTER TABLE nazwa_tabeli ADD FULLTEXT nazwa_indeksu (lista_kolumn);
Indeks FULLTEXT może być używany do wyszukiwania tekstu.
W istniejących tabelach
Aby dodać indeks w dowolnej istniejącej tabeli, można użyć następującej składni:
ALTER TABLE nazwa_tabeli ADD INDEX (nazwa_indeksu);
Do usunięcia indeksu z tabeli używa się instrukcji DROP INDEX:
ALTER TABLE nazwa_tabeli DROP INDEX (nazwa_indeksu);
Wyświetl informacje o indeksie
Aby wyświetlić listę wszystkich indeksów powiązanych z dowolną tabelą, używana jest komenda SHOW INDEX.
POKAŻ INDEKS Z nazwa_tabeli\G
'\G' służy do tworzenia listy w formacie pionowym, co pozwala uniknąć zawijania długich linii.
Przeczytaj także: Pytania i odpowiedzi dotyczące wywiadu SQL
Malejące indeksy
InnoDB może przechowywać wpisy w kolejności malejącej, gdy indeks jest indeksem malejącym. Tak więc, gdy w zapytaniu żądana jest kolejność malejąca, optymalizator wybiera ten indeks. Jest to bardziej wydajne w przypadku zapytań z klauzulami ORDER BY. Są one obsługiwane tylko przez silnik pamięci masowej InnoDB.
Składnia tworzenia lub dodawania malejących indeksów jest podobna do składni zmiany lub tworzenia użytych powyżej.
ALTER TABLE nazwa_tabeli ADD nazwa_indeksu (nazwa_kolumny opis, nazwa_kol2 asc);
CREATE INDEX nazwa_indeksu ON nazwa_tabeli (nazwa_col1 opis, nazwa_kol2 asc);
Niewidzialne indeksy
Aby oznaczyć indeksy jako niedostępne dla optymalizatora zapytań, można użyć niewidocznych indeksów. MySQL aktualizuje niewidoczne indeksy, gdy dane w kolumnach powiązanych z indeksem ulegają zmianie. Indeksy są domyślnie widoczne i aby były niewidoczne, musisz jawnie zadeklarować widoczność w momencie tworzenia lub za pomocą polecenia ALTER TABLE.
Aby utworzyć niewidoczny indeks, używana jest następująca składnia:
CREATE INDEX nazwa_indeksu ON nazwa_tabeli (c1, c2…) NIEWIDOCZNY;
Aby zmienić widoczność istniejących indeksów, używana jest następująca składnia:
ALTER TABLE nazwa_tabeli ALTER INDEX nazwa_indeksu [VISIBLE | NIEWIDZIALNY];
Zapisz się na kursy programowania online na najlepszych światowych uniwersytetach. Zdobywaj programy Executive PG, Advanced Certificate Programs lub Masters Programs, aby przyspieszyć swoją karierę.
Wniosek
Wydajność zapytań wyszukiwania MySQL można poprawić za pomocą indeksów. Można je zdefiniować podczas tworzenia tabeli lub dodać później, jeśli tabela została już utworzona.
Ten artykuł zawiera krótki opis tego, czym jest indeks i dlaczego jest używany. W następnej sekcji wyjaśniono, jak utworzyć indeks w MySQL, jak można go dodać do istniejącej tabeli i jak utworzyć unikalny indeks.
Opisano również różne polecenia służące do zmiany tabel i tworzenia lub usuwania indeksu wraz z wyświetlaniem informacji o indeksie. Pokrótce wyjaśniono także indeksy malejące i niewidoczne.
Podsumowując, nauka i praktyczna praktyka we wszystkich bazach danych wymienionych w tym artykule zwiększy Twoje doświadczenie. Zapoznaj się z przeglądem kursu, nauką i możliwościami pracy z nim związanymi — platformy takie jak upGrad oferują bogate kursy zaprojektowane przez renomowane instytuty, takie jak IIIT-B w programie Executive PG Program Full Stack Development.
