mysql engine: myisam vs innodb vs memory vs archive

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: , , , , ,

Komentarze: 7 do “mysql engine: myisam vs innodb vs memory vs archive”

  1. 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. 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. 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. 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. 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 :)

  6. 6 wyniki LIKE i AGAINST     2010-11-11 20:27:24

    „…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….”
    Niezły rozrzut :))

  7. 7 fundusze unijne wrocław     2011-02-15 15:56:48

    It’s really a nice and helpful piece of info. I’m glad that you shared this helpful information with us. Please keep us up to date like this. Thanks for sharing.

Napisz komentarz


Wersja mobilna