MySQL Samouczek dotyczący indeksowania — twórz, dodawaj i upuszczaj

Przez: Marcusa Allena Marcusa Allena
Hours Zaktualizowano

Co to jest indeks?

Indeksy w MySQL sortować dane w zorganizowany, sekwencyjny sposób. Tworzone są na kolumnach, które będą używane do filtrowania danych. Pomyśl o indeksie jak o liście posortowanej alfabetycznie. Łatwiej jest wyszukiwać nazwy posortowane alfabetycznie niż te, które nie są posortowane.

Używanie indeksu w tabelach, które są często aktualizowane, może skutkować słabą wydajnością. To dlatego, że MySQL tworzy nowy blok indeksu za każdym razem, gdy dane są dodawane lub aktualizowane w tabeli. Ogólnie rzecz biorąc, indeksów należy używać 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 pierwszorzędne znaczenie w prawie wszystkich systemach baz danych. Większość firm dużo inwestuje w sprzęt, dzięki któremu pobieranie i manipulowanie danymi może być szybsze. Istnieją jednak ograniczenia w zakresie inwestycji sprzętowych, jakie może poczynić firma. Optymalizacja bazy danych jest tańszym i lepszym rozwiązaniem.

MySQL wskaźnik

Spowolnienie czasu odpowiedzi wynika zwykle z losowego przechowywania rekordów w tabelach bazy danych. Zapytania wyszukiwania muszą przeglądać całe losowo przechowywane rekordy jeden po drugim, aby zlokalizować żądane dane. Powoduje to słabą wydajność baz danych, jeśli chodzi o pobieranie danych z dużych tabel. Dlatego też indeksy służą do sortowania danych, aby ułatwić ich wyszukiwanie.

Składnia: Utwórz indeks

Indeksy można definiować na 2 sposoby

  1. W momencie tworzenia tabeli
  2. Po utworzeniu tabeli

Przykład:

W przypadku naszego myflixdb spodziewamy się wielu wyszukiwań w bazie danych pod pełnym imieniem i nazwiskiem.

Dodamy kolumnę „full_names” do indeksu w nowej tabeli „members_indexed”.

Skrypt pokazany poniżej 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 MySQL Workbench przeciwko „myflixdb”.

MySQL Utwórz indeks

Odświeżenie myflixdb pokazuje nowo utworzoną tabelę o nazwie Member_indexed.

"Uwaga" tabela Member_indexed zawiera „full_names” w węźle indeksów.

W miarę powiększania się bazy członków i wzrostu liczby rekordów, zapytania wyszukiwania w tabeli Member_indexed, w których używane są klauzule WHERE i ORDER BY, będą znacznie szybsze w porównaniu z zapytaniami wykonywanymi w tabeli członkó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 zapytania w niej wykonywane są bardzo powolne. Zwracanie wyników zajmuje zbyt dużo czasu. Po zbadaniu problemu odkrywamy, że możemy znacznie poprawić wydajność systemu, tworząc INDEX na 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 „filmy” są bardzo powolne i chcemy użyć indeksu na „tytuł filmu”, aby przyspieszyć zapytania. W tym celu możemy użyć następującego skryptu.

CREATE INDEX `title_index` ON `movies`(`title`);

Wykonanie powyższego zapytania tworzy indeks w polu tytułu w tabeli filmów.

Oznacza to, że wszystkie zapytania w tabeli filmów przy użyciu „tytułu” będą szybsze.

Zapytania wyszukiwania w innych polach tabeli filmów będą jednak w dalszym ciągu wolniejsze w porównaniu do zapytań opartych na polu indeksowanym.

Uwaga: Jeśli to konieczne, możesz utworzyć indeksy dla wielu kolumn, w zależności od pól, których zamierzasz używać w swojej wyszukiwarce baz danych.

Jeśli chcesz przejrzeć indeksy zdefiniowane dla konkretnej tabeli, możesz to zrobić za pomocą poniższego skryptu.

SHOW INDEXES FROM table_name;

Przyjrzyjmy się teraz wszystkim indeksom zdefiniowanym w tabeli filmów w pliku myflixdb.

SHOW INDEXES FROM `movies`;

Wykonanie powyższego skryptu w MySQL Workbench przeciwko myflixdb daje nam wyniki dotyczące utworzonego indeksu.

Uwaga: Klucze podstawowe i obce w tabeli zostały już zindeksowane MySQL. Każdy indeks ma swoją unikalną nazwę i pokazana jest również kolumna, w której jest zdefiniowany.

Składnia: Upuść indeks

Polecenie drop służy do usuwania już zdefiniowanych indeksów z tabeli.

Może się zdarzyć, że zdefiniujesz 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 upuszczania 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 Member_indexed.

Podsumowanie

  • Indeksy są bardzo potężne, jeśli chodzi o znaczną poprawę wydajności MySQL Zapytania.
  • Indeksy można definiować podczas tworzenia tabeli lub dodawać później, już po utworzeniu tabeli.
  • Można zdefiniować indeksy dla więcej niż jednej kolumny tabeli.
  • Opcja SHOW INDEX FROM nazwa_tabeli służy do wyświetlania zdefiniowanych indeksów w tabeli.
  • Komenda DROP służy do usunięcia zdefiniowanego indeksu z danej tabeli.

Możesz lubić: