mysql engine: myisam vs innodb vs memory vs archive
- Mateusz Żeromski | 2008-11-25 | Bazy danych Ciekawostki Inne Optymalizacja
Jakiś czas temu postanowiłem przeprowadzić test, jaki silnik mysql jest najlepszy w jakim przypadku. Kiedyś wszystko tworzyłem w myisam lecz teraz widzę, że to nie było zbyt dobrym posunięciem :). Mysql oferuje kilka silników i każdy z nich ma swoje wady i zalety. W tym artykule porównam te silniki podając konkretne zapytania sql, oraz czasy w jakich zostały one wykonane dla każdego modelu. Tym artykułem również rozpoczynam kategorię Bazy danych na moim blogu.
Założenia
Porównywać będę silniki: myisam, innodb, memory, archive. Otóż ciężko to nazwać porównywaniem ponieważ każda opcja służy do czego innego :). Utworzyłem więc bazy danych w formacie article_ENGINE, user_ENGINE, sql:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE TABLE IF NOT EXISTS `article_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `title` varchar(50) collate utf8_unicode_ci NOT NULL, `description` varchar(250) collate utf8_unicode_ci NOT NULL, `body` text collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=__ENGINE__ DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS `user_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `login` varchar(20) collate utf8_unicode_ci NOT NULL, `name` varchar(50) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `name` (`name`) ) ENGINE=__ENGINE__ DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
Pola user_id w tabeli innodb jest kluczem obcym do tabeli user kolumna id.
Do tabel user_* dodałem 20 tys rekordów i każdemu userowi nadałem 100 artykułów, stąd w każdej tabeli article_* prawie 2 mln rekordów (zrobiłem jakiś błąd i jest tylko 1 999 200 ) :)
Myisam
Jest to podstawowy silnik, i pozwala na wszystko. Główną zaletą jest stosowanie indexów FULL_TEXT. Pozwala to na przeszukiwanie danych o wiele szybciej niż podczas używania zapytań LIKE, np przykładowe zapytanie
1 2 3 | SELECT SQL_NO_CACHE * FROM article_myisam WHERE MATCH(description) AGAINST('xb*' IN BOOLEAN MODE ) |
1 2 3 | SELECT SQL_NO_CACHE * FROM article_myisam WHERE description LIKE 'xb%' |
Tutaj nie wiedzieć czemu te zapytania dawały zupełnie inne wyniki ?. W przypadku LIKE było to 79740 wierszy w 1.50 s, natomiast przy wykorzystaniu FullText 13422 i 0.39s. Jeżeli chcemy robić wyszukiwarkę na danych – to tylko myisam+fulltext :). Dodatkowo FullText ma kilka innych możliwości – w przyszłości postaram się to opisać.
InnoDb
Nie można tworzyć indexu FULLTEXT, natomiast można definiować klucze obce. Dodatkowo tylko w InnoDB można wykonywać transakcję – lecz o tym napiszę kiedy indziej.
memory
Jak sama nazwa wskazuje dane są przechowywane w pamięci komputera i po wyłączenia serwera dane z bazy znikają. Dodatkowo nie można na tym silniku tworzyć pól text/blob itp – dlatego ten rodzaj bazy nie brał udziału we wszystkich testach.
Archive
Jak sama nazwa wskazuje służy do archiwizowania danych – używa się tego do składowania i wyciągania danych, wyjdzie to w testach. Nie można tutaj tworzyć kluczy :(. Po stworzeniu tabeli dane wrzucałem takim kodem:
1 | INSERT INTO article_archive SELECT * FROM article_myisam |
Porównanie – zaczynamy :) – Zajmowanie dysku – objętość danych
article_archive 381,4 MB article_innodb 461,5 MB article_myisam 1,0 GB user_archive 529,4 KB user_innodb 3,0 MB user_memmory 4,7 MB user_myisam 2,3 MB
No cóż – wnioski widać, im większa baza tym innodb zajmuje mniej miejsca, dodatkowo, najmniej miejsca zajmuje article_archive.
Zapytanie: select SQL_NO_CACHE * from TABLE_TYPE
TABLE_TYPE zamieniam odpowiednio na user/article i nazwę silnika. To zapytanie tylko wykonywałem :)
user_archive 0.198708057404
user_myisam 0.137545824051
user_innodb 0.0879549980164
user_memmory 0.0662670135498
article_archive 40.4679379463
article_myisam 46.9114861488
article_innodb 20.1895670891
W prostym zapytaniu wygrywa memmory – co jest oczywiste- RAM jest szybsze niż dyski twarde. Zaskakuje tutaj szybkość innodb, no cóż :)
Zapytanie: select SQL_NO_CACHE user.login, count(*) from user_TYPE user, article_TYPE article where user.id=article.user_id group by user.id
To zapytanie wypluwa user_id, ilosc. Wykonuje tylko na silnikach myisam i innodb – na archive nie udało mi się :), zapewne bym wykorzystał całą pamięć i tak by się nie udało, widać silnik Archive służy tylko do przechowywania danych.
user_myisam 3.07350206375
user_innodb 1.7265329361
Jak widać w zapytaniach łączenia tabel dwa razy lepsze jest innodb.
Koniec :) – Podsumowanie
No jak widać nie ma złotego środka, każdy silnik mysql inaczej działa. Jeżeli robimy dużo relacji – używamy innodb, jeżeli wyszukiwanie – myisam, jeżeli składowanie danych archive. Aby dobrać odpowiedni silnik musimy wiedzieć co chcemy osiągnąć na samym końcu i musimy wybrać odpowiednią technologię.
Lecz zakładając, że chcemy zrobić optymalny system, baza danych nie powinna mieć dużego znaczenia i można by wszystko postawić na MYISAM, ogólnie chodzi mi o to aby baza danych nigdy nie była wąskim gardłem a wyniki zapytań powinny być cachowane do plików abyśmy nie musieli się pytać non stop bazy o jakieś pierdoły. Przykład takiego mechanizmu cachowania: http://blog.zeromski.com.pl/2008/11/07/cachowanie-funkcji-przyspieszenie-generowania-stron/.
Przy przygotowywaniu tego testu sporo się namęczyłem a tak mało napisałem :). Mam nadzieję że zrozumiale :).
Tagi: archive, Bazy danych, Ciekawostki, innodb, memory, myisam
1 eRIZ 2008-11-25 23:41:20
Zapomniałeś o najważniejszym – InnoDB jako jedyny silnik dostępny w MySQL posiada obsługę transakcji. ;)
2 guci0 2008-11-28 12:01:26
Czesc Matusz,
ciekawe, ciekawe – powiedzialbym tak “DOBRA ROBOTA”, swoja droga wide, ze RSS juz nie caly ;)
Pozdrawiam
Paweł
3 Mateusz Żeromski 2008-11-29 17:07:32
eRIZ – dodałem info o transakcjach, w najbliższej przyszłości mam zamiar napisać o tym coś więcej niż że tylko istnieją i jak działają, ale też kiedy ma to praktyczne zastosowanie.
guci0 – niestety w tym wpisie nic nie odkryłem więc jest on troche nudny, jedynie to sie narobiłem. Lecz rozwiewa oczywiste pytania :)
4 Sędziwój 2009-01-21 14:35:51
“można by wszystko postawić na MYISAM”
Pominąłeś klucze obce i szybkość normalnego wyszukiwania. Innodb jest o wiele ciekawsze, bo oferuje zachowanie integralności danych między tabelami, a to bardzo przydatne.
Co do wyszukiwania pełno tekstowego, to musisz pamiętać że szuka wyrazów dłuższych niż 3 literowe, stąd ta różnica w wynikach (chyba że inaczej skonfigurowałeś).
5 Mateusz Żeromski 2009-01-24 12:19:37
Sędziwój – napisałem cały artykuł o kluczach obcych :)
http://blog.zeromski.com.pl/2009/01/mysql-innodb-klucze-obce/
eRIZ – transakcje już wkrótce :)