Czy rzeczywiście konieczne jest zaindeksowanie wszystkich wybranych kolumn, aby MySQL mógł użyć indeksu?
To jest załadowane pytanie, ponieważ istnieją czynniki, które określają, czy warto korzystać z indeksu.
CZYNNIK 1
Jaka jest kluczowa populacja dla danego indeksu? Innymi słowy, jaka jest liczność (odrębna liczba) wszystkich krotek zarejestrowanych w indeksie?
CZYNNIK 2
Jakiego silnika pamięci używasz? Czy wszystkie potrzebne kolumny są dostępne z indeksu?
CO DALEJ ???
Weźmy prosty przykład: tabela, która zawiera dwie wartości (mężczyzna i kobieta)
Utwórzmy taką tabelę z testem użycia indeksu
USE test
DROP TABLE IF EXISTS mf;
CREATE TABLE mf
(
id int not null auto_increment,
gender char(1),
primary key (id),
key (gender)
) ENGINE=InnODB;
INSERT INTO mf (gender) VALUES
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
ANALYZE TABLE mf;
EXPLAIN SELECT gender FROM mf WHERE gender='F';
EXPLAIN SELECT gender FROM mf WHERE gender='M';
EXPLAIN SELECT id FROM mf WHERE gender='F';
EXPLAIN SELECT id FROM mf WHERE gender='M';
TEST InnoDB
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE mf
-> (
-> id int not null auto_increment,
-> gender char(1),
-> primary key (id),
-> key (gender)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO mf (gender) VALUES
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.06 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 37 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql>
TEST MyISAM
mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS mf;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE mf
-> (
-> id int not null auto_increment,
-> gender char(1),
-> primary key (id),
-> key (gender)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO mf (gender) VALUES
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('F'),('F'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('M'),('M'),('M'),('M'),('M'),('M'),('M'),('M'),
-> ('F'),('M'),('M'),('M'),('M'),('M'),('M'),('M');
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE mf;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.mf | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT gender FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 36 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='F';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | mf | ref | gender | gender | 2 | const | 3 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT id FROM mf WHERE gender='M';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | mf | ALL | gender | NULL | NULL | NULL | 40 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql>
Analiza dla InnoDB
Gdy dane zostały załadowane jako InnoDB, należy pamiętać, że wszystkie cztery EXPLAINplany korzystały z genderindeksu. Trzeci i czwarty EXPLAINplan wykorzystywały genderindeks, mimo że żądane dane były id. Dlaczego? Ponieważ idznajduje się w nim, PRIMARY KEYa wszystkie indeksy wtórne mają wskaźniki odniesienia z powrotem do PRIMARY KEY(za pośrednictwem ind_klust_genu ).
Analiza dla MyISAM
Gdy dane zostały załadowane jako MyISAM, pamiętaj, że pierwsze trzy EXPLAINplany korzystały z genderindeksu. W czwartym EXPLAINplanie Optymalizator zapytań postanowił w ogóle nie używać indeksu. Zamiast tego wybrał pełny skan tabeli. Dlaczego?
Niezależnie od DBMS, Optymalizatory Zapytań działają na bardzo prostej zasadzie: jeśli indeks jest sprawdzany jako kandydat do użycia w celu przeprowadzenia wyszukiwania, a Optymalizator Zapytań oblicza, że musi on wyszukać więcej niż 5% całkowitej liczby wiersze w tabeli:
- pełne skanowanie indeksu jest wykonywane, jeśli wszystkie potrzebne kolumny do pobrania znajdują się w wybranym indeksie
- w przeciwnym razie pełny skan tabeli
WNIOSEK
Jeśli nie masz odpowiednich indeksów pokrycia lub jeśli kluczowa populacja dla dowolnej krotki wynosi więcej niż 5% tabeli, musi się zdarzyć sześć rzeczy:
- Przekonaj się, że musisz profilować zapytania
- Znajdź wszystko
WHERE, GROUP BYi porządek BY` klauzule z tych zapytań
- Sformułuj indeksy w tej kolejności
WHERE kolumny klauzul o wartościach statycznychGROUP BY kolumnyORDER BY kolumny
- Unikaj pełnego skanowania tabeli (zapytania pozbawione sensownej
WHEREklauzuli)
- Unikaj populacji złych kluczy (lub przynajmniej buforuj te populacje złych kluczy)
- Wybierz najlepszy silnik pamięci MySQL ( InnoDB lub MyISAM ) dla tabel
W przeszłości pisałem o tej ogólnej zasadzie 5%:
AKTUALIZACJA 14.11.2012 13:05 EDT
Spojrzałem na twoje pytanie i na oryginalny post SO . Potem pomyślałem o mojej Analysis for InnoDBwcześniej wspomnianej. To pokrywa się ze personstołem. Dlaczego?
Dla obu tabel mfiperson
- Silnik pamięci to InnoDB
- Klucz podstawowy to
id
- Dostęp do tabeli odbywa się poprzez indeks wtórny
- Gdyby tabelą był MyISAM, zobaczylibyśmy zupełnie inny
EXPLAINplan
Teraz spojrzeć na zapytania z pytaniem SO: select * from person order by age\G. Ponieważ nie ma WHEREklauzuli, wyraźnie zażądałeś pełnego skanowania tabeli . Domyślna kolejność sortowania tabeli byłaby według id(KLUCZ PODSTAWOWY) ze względu na jej auto_increment, a gen_clust_index (aka Clustered Index) jest uporządkowany według wewnętrznego rowid . Po zamówieniu według indeksu należy pamiętać, że indeksy wtórne InnoDB mają identyfikator rowid dołączony do każdej pozycji indeksu. To powoduje wewnętrzną potrzebę pełnego dostępu do wiersza za każdym razem.
Konfigurowanie ORDER BYtabeli InnoDB może być dość zniechęcającym zadaniem, jeśli zignorujesz te fakty dotyczące organizacji indeksów InnoDB.
Wracając do tego zapytania SO, ponieważ wyraźnie zażądałeś pełnego skanowania tabeli , IMHO Optymalizator zapytań MySQL zrobił właściwą czynność (lub przynajmniej wybrał ścieżkę najmniejszego oporu). Jeśli chodzi o InnoDB i kwerendę SO, o wiele łatwiej jest wykonać pełne skanowanie tabeli, a następnie niektóre, filesortzamiast wykonywać pełne skanowanie indeksu i wyszukiwanie wierszy za pośrednictwem gen_clust_index dla każdej pozycji indeksu wtórnego.
Nie jestem zwolennikiem korzystania ze Wskazówek dotyczących indeksu, ponieważ ignoruje on plan WYJAŚNIENIA. Niezależnie od tego, jeśli naprawdę znasz swoje dane lepiej niż InnoDB, będziesz musiał skorzystać ze wskazówek indeksu, szczególnie w przypadku zapytań, które nie zawierają żadnych WHEREklauzul.
AKTUALIZACJA 14.11.2012 14:21 EDT
Zgodnie z książką Understanding MySQL Internals
Page 202 Ustęp 7 mówi, co następuje:
Dane są przechowywane w specjalnej strukturze zwanej indeksem klastrowym , który jest drzewem B z kluczem podstawowym działającym jako wartość klucza oraz rzeczywistym rekordem (a nie wskaźnikiem) w części danych. Dlatego każda tabela InnoDB musi mieć klucz podstawowy. Jeśli nie zostanie dostarczony, zostanie dodana specjalna kolumna identyfikatora wiersza, która zwykle nie jest widoczna dla użytkownika, aby działała jako klucz podstawowy. Klucz pomocniczy przechowuje wartość klucza podstawowego, który identyfikuje rekord. Kod B-drzewa można znaleźć w innobase / btr / btr0btr.c .
Właśnie dlatego powiedziałem wcześniej: o wiele łatwiej jest wykonać pełne skanowanie tabeli, a następnie niektóre sortowanie plików, niż pełne skanowanie indeksu i wyszukiwanie wierszy za pomocą gen_clust_index dla każdej pozycji indeksu wtórnego . InnoDB za każdym razem przeprowadzi podwójne wyszukiwanie indeksu . Brzmi to trochę brutalnie, ale to tylko fakty. Ponownie weź pod uwagę brak WHEREklauzuli. To samo w sobie jest wskazówką dla MySQL Query Optimizer, aby wykonać pełne skanowanie tabeli.
FOR ORDER BY(co jest szczególnym przypadkiem w tym pytaniu). Pytanie zawierało stwierdzenie, że w tym przypadku był to silnik pamięci masowejInnoDB(a oryginalne pytanie SO pokazuje, że wiersze 10 000 są dość równomiernie rozmieszczone na 8 elementach, tutaj również nie powinna być problemem liczność). Niestety nie sądzę, że to odpowiada na pytanie.