Wydajność Mysql na tabeli 6 milionów wierszy
Pewnego dnia podejrzewam, że będę musiał nauczyć się hadoop i przenieść wszystkie te dane do niestrukturalnej bazy danych, ale jestem zaskoczony, że wydajność spada tak znacząco w tak krótkim czasie.
Mam tabelę mysql z prawie 6 milionami wierszy. Robię bardzo proste zapytanie na tej tabeli i wierzę, że mam wszystkie poprawne indeksy na miejscu.
Zapytanie to
SELECT date, time FROM events WHERE venid='47975' AND date>='2009-07-11' ORDER BY date
The explain zwraca
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE updateshows range date_idx date_idx 7 NULL 648997 Using where
Więc używam poprawnego indeksu o ile można powiedzieć, ale to zapytanie trwa 11 sekund, aby uruchomić.
Baza danych to MyISAM, a phpMyAdmin mówi, że tabela To 1.0 GiB.
Jakieś pomysły?Edytowane: Date_idx indeksuje zarówno kolumny date, jak i venid. Czy powinny to być dwa oddzielne indeksy?
4 answers
Chcesz się upewnić, że zapytanie będzie używać tylko indeksu, więc upewnij się, że indeks obejmuje wszystkie zaznaczone pola. Ponadto, ponieważ jest to zapytanie zakresu, musisz mieć venid jako pierwszy w indeksie, ponieważ jest pytany jako stała. Chciałbym zatem utworzyć i indeksować w ten sposób:
ALTER TABLE events ADD INDEX indexNameHere (venid, date, time);
W tym indeksie wszystkie informacje potrzebne do wypełnienia zapytania znajdują się w indeksie. Oznacza to, że, miejmy nadzieję, silnik magazynowy jest w stanie pobrać informacji bez szukania wewnątrz samej tabeli. Jednak MyISAM może nie być w stanie tego zrobić, ponieważ nie przechowuje danych w liściach indeksów, więc możesz nie uzyskać pożądanego wzrostu prędkości. W takim przypadku spróbuj utworzyć kopię tabeli i użyj silnika InnoDB na kopii. Powtórz tam te same kroki i sprawdź, czy masz znaczny wzrost prędkości. InnoDB przechowuje wartości pól w liściach indeksu i pozwala na pokrycie indeksy.
Teraz, mam nadzieję, że zobaczysz, co następuje, gdy wyjaśnisz zapytanie:
mysql> EXPLAIN SELECT date, time FROM events WHERE venid='47975' AND date>='2009-07-11' ORDER BY date;
id select_type table type possible_keys key [..] Extra
1 SIMPLE events range date_idx, indexNameHere indexNameHere Using index, Using where
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-07-11 21:39:41
Spróbuj dodać klucz, który obejmuje venid i date (lub odwrotnie, lub oba...)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-07-11 20:55:28
Wyobrażam sobie, że stół z 6-metrowym wierszem powinien być zoptymalizowany całkiem normalnymi technikami.
Zakładam, że masz dedykowany serwer bazy danych i ma on rozsądną ilość pamięci ram (powiedzmy minimum 8G).
Będziesz chciał upewnić się, że dostrojony mysql do efektywnego wykorzystania pamięci ram. Jeśli używasz 32-bitowego systemu operacyjnego, nie rób tego. jeśli używasz MyISAM, dostosuj bufor klawiszy, aby używał znaczącej proporcji, ale nie zbyt dużej, pamięci ram.
In any case you want to run powtarzające się testy wydajności na sprzęcie produkcyjnym.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-07-11 21:53:40
Spróbuj umieścić indeks na kolumnie venid.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-07-11 20:55:37