Co to jest indeks?
Indeksy w MySQL sortują dane w uporządkowany sposób. Są tworzone w kolumnach, które będą używane do filtrowania danych. Pomyśl o indeksie jako o liście posortowanej alfabetycznie. Łatwiej jest wyszukać nazwy, które zostały posortowane w kolejności alfabetycznej, niż te, które nie zostały posortowane.
Używanie indeksu w tabelach, które są często aktualizowane, może spowodować niską wydajność. Dzieje się tak, ponieważ MySQL tworzy nowy blok indeksu za każdym razem, gdy dane są dodawane lub aktualizowane w tabeli. Zasadniczo indeksy powinny być używane w tabelach, których dane nie zmieniają się często, ale są często używane w wybranych zapytaniach wyszukiwania.
Do czego służy indeks?
Nikt nie lubi wolnych systemów. Wysoka wydajność systemu ma ogromne znaczenie w prawie wszystkich systemach baz danych. Większość firm dużo inwestuje w sprzęt, aby pobieranie i przetwarzanie danych było szybsze. Jednak inwestycje w sprzęt mogą być ograniczone przez firmę. Optymalizacja bazy danych to tańsze i lepsze rozwiązanie.
Spowolnienie czasu odpowiedzi jest zwykle spowodowane losowym przechowywaniem rekordów w tabelach bazy danych. Zapytania wyszukiwania muszą przechodzić przez wszystkie losowo przechowywane rekordy jeden po drugim, aby zlokalizować żądane dane. Skutkuje to niską wydajnością baz danych, jeśli chodzi o pobieranie danych z dużych tabel. W związku z tym używane są indeksy, które sortują dane, aby ułatwić wyszukiwanie.
Składnia: Utwórz indeks
Indeksy można zdefiniować na 2 sposoby
- W momencie tworzenia stołu
- Po utworzeniu tabeli
W przypadku naszego myflixdb spodziewamy się wielu wyszukiwań w bazie danych po pełnym nazwisku.
Dodamy kolumnę „full_names” do indeksu w nowej tabeli „Members_indexed”.
Poniższy skrypt pomaga nam to osiągnąć.
CREATE TABLE `members_indexed` (`membership_number` int(11) NOT NULL AUTO_INCREMENT,`full_names` varchar(150) DEFAULT NULL,`gender` varchar(6) DEFAULT NULL,`date_of_birth` date DEFAULT NULL,`physical_address` varchar(255) DEFAULT NULL,`postal_address` varchar(255) DEFAULT NULL,`contact_number` varchar(75) DEFAULT NULL,`email` varchar(255) DEFAULT NULL,PRIMARY KEY (`membership_number`),INDEX(full_names)) ENGINE=InnoDB;
Wykonaj powyższy skrypt SQL w środowisku roboczym MySQL na „myflixdb”.
Odświeżenie myflixdb pokazuje nowo utworzoną tabelę o nazwie Members_indexed.
Tabela „Uwaga” Members_indexed ma „full_names” w węźle indeksów.
W miarę rozszerzania się bazy członków i zwiększania liczby rekordów zapytania wyszukiwania w tabeli Members_indexed, które używają klauzul WHERE i ORDER BY, będą znacznie szybsze w porównaniu z zapytaniami wykonywanymi w tabeli elementów bez zdefiniowanego indeksu.
Dodaj podstawową składnię indeksu
Powyższy przykład utworzył indeks podczas definiowania tabeli bazy danych. Załóżmy, że mamy już zdefiniowaną tabelę i wyszukiwane hasła są bardzo wolne. Zwracanie wyników trwa zbyt długo. Po zbadaniu problemu odkrywamy, że możemy znacznie poprawić wydajność systemu, tworząc INDEKS w najczęściej używanej kolumnie w klauzuli WHERE.
Możemy użyć następującego zapytania, aby dodać indeks
CREATE INDEX id_index ON table_name(column_name);
Załóżmy, że zapytania wyszukiwania w tabeli filmów są bardzo wolne i chcemy użyć indeksu „tytułu filmu”, aby przyspieszyć zapytania. Aby to osiągnąć, możemy użyć następującego skryptu.
CREATE INDEX `title_index` ON `movies`(`title`);
Wykonanie powyższego zapytania powoduje utworzenie indeksu w polu tytułu w tabeli filmów.
Oznacza to, że wszystkie zapytania wyszukiwania w tabeli filmów przy użyciu „tytułu” będą szybsze.
Zapytania wyszukiwania w innych polach w tabeli filmów będą jednak nadal wolniejsze w porównaniu do zapytań opartych na indeksowanym polu.
Uwaga: W razie potrzeby możesz utworzyć indeksy na wielu kolumnach, w zależności od pól, których zamierzasz używać w wyszukiwarce bazy danych.
Jeśli chcesz wyświetlić indeksy zdefiniowane w określonej tabeli, możesz to zrobić za pomocą następującego skryptu.
SHOW INDEXES FROM table_name;
Przyjrzyjmy się teraz wszystkim indeksom zdefiniowanym w tabeli filmów w myflixdb.
SHOW INDEXES FROM `movies`;
Wykonanie powyższego skryptu w środowisku MySQL na myflixdb daje nam wyniki dotyczące utworzonego indeksu.
Uwaga: Klucz podstawowy i obcy w tabeli zostały już zindeksowane przez MySQL. Każdy indeks ma własną unikalną nazwę, a także wyświetlana jest kolumna, w której jest zdefiniowany.
Składnia: Drop index
Polecenie drop służy do usuwania już zdefiniowanych indeksów w tabeli.
Może się zdarzyć, że zdefiniowałeś już indeks w tabeli, która jest często aktualizowana. Możesz chcieć usunąć indeksy z takiej tabeli, aby poprawić wydajność zapytań UPDATE i INSERT. Podstawowa składnia używana do usuwania indeksu w tabeli jest następująca.
DROP INDEX `index_id` ON `table_name`;
Spójrzmy teraz na praktyczny przykład.
DROP INDEX ` full_names` ON `members_indexed`;
Wykonanie powyższego polecenia powoduje usunięcie indeksu o identyfikatorze `full_names 'z tabeli Members_indexed.
Podsumowanie
- Indeksy mają bardzo duże możliwości, jeśli chodzi o znaczną poprawę wydajności zapytań wyszukiwania MySQL.
- Indeksy można zdefiniować podczas tworzenia tabeli lub dodać później, po utworzeniu tabeli.
- Możesz zdefiniować indeksy w więcej niż jednej kolumnie w tabeli.
- SHOW INDEX FROM nazwa_tabeli służy do wyświetlania zdefiniowanych indeksów w tabeli.
- Polecenie DROP służy do usuwania zdefiniowanego indeksu w danej tabeli.