Jak dodać indeksy do tabel MySQL?

Mam bardzo dużą tabelę MySQL z około 150 000 wierszy danych. Obecnie, kiedy próbuję uruchomić

SELECT * FROM table WHERE id = '1';

kod działa poprawnie, ponieważ pole ID jest indeksem podstawowym. Jednak w przypadku ostatniego rozwoju projektu muszę przeszukać bazę danych według innego pola. Na przykład:

SELECT * FROM table WHERE product_id = '1';

To pole nie było wcześniej indeksowane; Jednak dodałem jeden, więc mysql teraz indeksuje pole, ale kiedy próbuję uruchomić powyższe zapytanie, działa ono bardzo wolno. Zapytanie EXPLAIN ujawnia, że ​​nie ma indeksu dla pola product_id, gdy już go dodałem, w wyniku czego zapytanie zajmuje od 20 minut do 30 minut, aby zwrócić pojedynczy wiersz.

Moje pełne wyniki WYJAŚNIJ to:

| id | select_type | table | type | possible_keys| key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+
|  1 | SIMPLE      | table | ALL  | NULL         | NULL | NULL    | NULL |157211 | Using where |
+----+-------------+-------+------+--------------+------+---------+------+-------+------------------+

Warto zauważyć, że właśnie spojrzałem, a pole ID jest przechowywane jako INT, natomiast pole PRODUCT_ID jest przechowywane jako VARCHAR. Czy to może być przyczyną problemu?

ALTER TABLE `table` ADD INDEX `product_id_index` (`product_id`)

Nigdy nie porównuj integerz stringsMySQL. Jeśli idtak int, usuń cytaty.

ALTER TABLE TABLE_NAME ADD INDEX (COLUMN_NAME);

Możesz użyć tej składni, aby dodać indeks i kontrolować rodzaj indeksu (HASH lub BTREE).

create index your_index_name on your_table_name(your_column_name) using HASH;
or
create index your_index_name on your_table_name(your_column_name) using BTREE;

Możesz dowiedzieć się o różnicach między indeksami BTREE i HASH tutaj: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html

Warto zauważyć, że wiele indeksów pól może znacznie poprawić wydajność zapytań. Tak więc w powyższym przykładzie zakładamy, że ProductID to jedyne pole do wyszukiwania, ale w zapytaniu było powiedzone ProductID = 1 AND Category = 7, wtedy pomaga indeks wielu kolumn. Osiąga się to dzięki:

ALTER TABLE `table` ADD INDEX `index_name` (`col1`,`col2`)

Dodatkowo indeks powinien być zgodny z kolejnością pól zapytań. W moim rozszerzonym przykładzie indeks powinien wynosić (ProductID, Category), a nie na odwrót.

Można dodać indeksy dwóch typów: kiedy zdefiniujesz klucz podstawowy, MySQL domyślnie przyjmie go jako indeks.

Wyjaśnienie

Klucz podstawowy jako indeks

Rozważ, że masz tbl_studenttabelę i chcesz student_idjako klucz podstawowy:

ALTER TABLE `tbl_student` ADD PRIMARY KEY (`student_id`)

Powyższa instrukcja dodaje klucz podstawowy, co oznacza, że ​​indeksowane wartości muszą być unikalne i nie mogą mieć wartości NULL.

Podaj nazwę indeksu

ALTER TABLE `tbl_student` ADD INDEX student_index (`student_id`)

Powyższa instrukcja utworzy zwykły indeks o student_indexnazwie.

Utwórz unikalny indeks

ALTER TABLE `tbl_student` ADD UNIQUE student_unique_index (`student_id`)

Tutaj student_unique_indexjest nazwa indeksu przypisana do student_id i tworzy indeks, dla którego wartości muszą być unikalne (tutaj można zaakceptować null).

Opcja pełnego tekstu

ALTER TABLE `tbl_student` ADD FULLTEXT student_fulltext_index (`student_id`)

Powyższa instrukcja utworzy nazwę indeksu pełnotekstowego student_fulltext_index, dla której potrzebujesz MyISAM Mysql Engine.

Jak usunąć indeksy?

DROP INDEX `student_index` ON `tbl_student`

Jak sprawdzić dostępne indeksy?

SHOW INDEX FROM `tbl_student`

Mówisz, że masz indeks, wyjaśnienie mówi inaczej. Jeśli jednak tak naprawdę robisz, możesz kontynuować:

Jeśli masz indeks w kolumnie, a MySQL postanawia go nie używać, może to dlatego, że:

1. W zapytaniu MySQL znajduje się inny indeks, który można uznać za bardziej odpowiedni, i może on używać tylko jednego. Rozwiązaniem jest zwykle indeks obejmujący wiele kolumn, jeśli ich normalna metoda wyszukiwania ma wartość większą niż jedna kolumna.
2. MySQL decyduje, że istnieje wiele pasujących wierszy, i uważa, że ​​skanowanie tabel jest prawdopodobnie szybsze. Jeśli tak nie jest, czasem ANALYZE TABLEpomaga.
3. W bardziej złożonych zapytaniach decyduje się nie używać go w oparciu o wyjątkowo inteligentne i przemyślane voodoo w planie zapytań, które z jakiegoś powodu po prostu nie spełnia twoich bieżących wymagań.

W przypadku (2) lub (3) możesz nakłonić MySQL do użycia indeksu według sytax podpowiedzi indeksu , ale jeśli to zrobisz, upewnij się, że wykonałeś kilka testów w celu ustalenia, czy rzeczywiście poprawia ono wydajność korzystania z indeksu podczas jego podpowiedzi .