Pierwszą rzeczą, którą musisz wiedzieć, jest to, że indeksy są sposobem na uniknięcie skanowania pełnej tabeli w celu uzyskania oczekiwanego wyniku.
Istnieją różne rodzaje indeksów i są one zaimplementowane w warstwie pamięci, więc nie ma między nimi żadnego standardu, a także zależą od używanego silnika pamięci.
InnoDB i indeks drzewa B +
W przypadku InnoDB najczęstszym typem indeksu jest indeks oparty na drzewie B +, który przechowuje elementy w posortowanej kolejności. Ponadto nie musisz uzyskiwać dostępu do prawdziwej tabeli, aby uzyskać zindeksowane wartości, co znacznie przyspiesza zapytanie.
„Problem” związany z tym typem indeksu polega na tym, że musisz użyć wartości skrajnie lewej, aby użyć indeksu. Jeśli więc indeks ma dwie kolumny, np. Nazwisko i imię, kolejność zapytań w tych polach ma duże znaczenie .
Biorąc pod uwagę następującą tabelę:
CREATE TABLE person (
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
INDEX (last_name, first_name)
);
To zapytanie wykorzysta indeks:
SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"
Ale następny nie
SELECT last_name, first_name FROM person WHERE first_name = "Constantine"
Ponieważ najpierw przeszukujesz first_namekolumnę i nie jest to kolumna skrajnie lewa w indeksie.
Ten ostatni przykład jest jeszcze gorszy:
SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"
Ponieważ teraz porównujesz prawą część pola znajdującego się po prawej stronie w indeksie.
Indeks mieszania
Jest to inny typ indeksu, który niestety obsługuje tylko backend pamięci. Jest to szybki jak błyskawica, ale przydatne tylko dla pełnych wyszukiwań, co oznacza, że nie można go używać na podobne operacje >, <albo LIKE.
Ponieważ działa tylko w przypadku backendu pamięci, prawdopodobnie nie będziesz go używać zbyt często. Główną sprawą, o której mogę teraz pomyśleć, jest ta, w której utworzysz tymczasową tabelę w pamięci z zestawem wyników z innego wyboru i wykonasz wiele innych wyborów w tej tabeli tymczasowej za pomocą indeksów skrótów.
Jeśli masz duże VARCHARpole, możesz „naśladować” użycie indeksu skrótu podczas korzystania z B-drzewa, tworząc kolejną kolumnę i zapisując na niej skrót o dużej wartości. Załóżmy, że przechowujesz adres URL w polu, a wartości są dość duże. Możesz także utworzyć pole o nazwie integer url_hashi użyć funkcji skrótu, takiej jak CRC32lub dowolnej innej funkcji skrótu, aby mieszać adres URL podczas wstawiania. A potem, gdy musisz zapytać o tę wartość, możesz zrobić coś takiego:
SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");
Problem z powyższym przykładem polega na tym, że ponieważ CRC32funkcja generuje dość niewielką wartość skrótu, powstanie wiele kolizji w wartościach mieszanych. Jeśli potrzebujesz dokładnych wartości, możesz rozwiązać ten problem, wykonując następujące czynności:
SELECT url FROM url_table
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";
Nadal warto mieszać rzeczy, nawet jeśli liczba kolizji jest wysoka, ponieważ wykonasz tylko drugie porównanie (łańcuchowe) z powtarzającymi się skrótami.
Niestety, używając tej techniki, wciąż musisz trafić w stół, aby porównać urlpole.
Zakończyć
Kilka faktów, które możesz wziąć pod uwagę za każdym razem, gdy chcesz porozmawiać o optymalizacji:
Porównanie liczb całkowitych jest znacznie szybsze niż porównywanie ciągów. Można to zilustrować przykładem emulacji indeksu skrótu w InnoDB.
Być może dodanie dodatkowych kroków w procesie sprawia, że jest to szybsze, a nie wolniejsze. Można to zilustrować faktem, że można zoptymalizować a SELECT, dzieląc go na dwa etapy, dzięki czemu pierwszy z nich przechowuje wartości w nowo utworzonej tabeli w pamięci, a następnie wykonuje trudniejsze zapytania na drugiej tabeli.
MySQL ma również inne indeksy, ale myślę, że drzewko B + jest najczęściej używane w historii, a hash warto wiedzieć, ale inne można znaleźć w dokumentacji MySQL .
Gorąco polecam przeczytanie książki „High Performance MySQL”, powyższa odpowiedź była zdecydowanie oparta na jej rozdziale o indeksach.
