mysql innodb – klucze obce

Poprzedni artykuł na temat silników baz danych wywołał krytyke artykułu z powodu pominięcia kluczy obcych w innodb – i słusznie :)W tym artykule napiszę krótki wstęp co to są te klucze obce po ci i podam jakiś przykład.

Klucz obcy

Klucz obcy jest to identyfikator relacji w podrzędnej do klucza podstawowego w tabeli nadrzędnej np załóżny strukturę tabeli:

1
2
3
4
5
6
7
8
users
  id - primary key
  name

sites
  id - primary key
  user_id  - foreign key
  url

Jak widać na przykładzie kluczem obcym jest sites.user_id i odwołuje się on do users.id. Taką relację można utworzyć tylko używając silnika InnoDB.

Tworzenie, integralność?

Klucze obce pozwalają zachować integralność danych, tzn tworząc klucz obcy możemy zaprogramować, aby podczas usuwania rekordu z tabeli nadrzędnej w wszystkie dzieci w innych tabelach zostały usunięte. W przypadku bardzo złożonych relacyjnych baz danych ma to duuże znaczenie.

Kod sql do wykonania takiej operacji (na powyzszym przykladzie bazy):

1
2
3
4
5
alter table sites
    ADD CONSTRAINT `key`
    FOREIGN KEY (`user_id`)
    REFERENCES `users`(`id`)
    ON DELETE CASCADE

Powyższe sql sugeruje: jeżeli usuniesz usera to automatycznie usuń wszsytko co do niego należy w tabelach podrzędnych. Oczywście taką akcję można usunąć, co pozwala na tworzenie smietnika, albo dodać inne:

1
RESTRICT | CASCADE | SET NULL | NO ACTION

Również zamias on delete możemy użyć on update – działa tak samo

Po dokładniejszy opis zapraszam do dokumentacji: 13.2.4.4. FOREIGN KEY Constraints

Szybkość i porządek

Tabele innoDb z kluczami obcymi są troszkę trudniejsze w obsłudze, tzn nie pozwalają na banalne usuwanie tabel jeżeli do nich są relacje – wymuszają integralność – jest to bardzo bezpieczne, aczkolwiek na etapie developerskim potrafi wkurzyć – jednak bazę tworzy się zazwyczaj raz, także nie ma to znaczenia :) Oczywiście jeżeli mamy relację zapisaną poprzez klucze obce (w myisam są to po prostu indeksy w dwóch tabelach) zapytania odwołujące się do zależności są szybsze.

Pewnego razu tworzyłem system z ok 10 tabelami w innoDB i musiałem wykonywać zapytania na 9 tabelach, oto takie zapytanie:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  select SQL_NO_CACHE
        us.id,
        concat(d.url , u.url) as url,
        e.name as engine,
        l.value  as lang,
        k.value as keyword
    from
        domains d, urls u, urls_keywords uk, keywords k, langs l, engines e, users users, urls_stats us
    where
        d.id=u.domain_id
        and uk.url_id=u.id
      and uk.keyword_id=k.id
        and uk.lang_id=l.id
        and uk.engine_id=e.id
        and users.id=d.user_id
        and us.url_keyword_id=uk.id

Po stworzeniu bazy projekt porzuciłem :) Jednakże tabele zostały, na tym przykładzie chcę pokazać jak szybsze jest InnoDB od MyISAM w przypadku relacji. Test polegał na wykonaniu zapytania na bazach po 10 zapytań, kod:

1
2
3
4
5
6
7
8
9
10
$times = array();
for($i=0; $i<10; $i++){
  $start_time = microtime();

  mysql_query($sql);
  $end_time = microtime();
  $times[] = $end_time-$start_time;

}
print_r(array_sum($times)/count($times));

Aha, ilość danych w obu bazach była taka sama (bardzo mała), jednakże po czasie wykonywania zapytań można dużo wywnioskować. Wszystko odbywało się na poziomie od 0.001 do 0.009 – w zależności od obciążenia localhosta. InnoDB zawsze było szybsze czasem nawet o 50%. Zakładając znikomą ilość danych testowane były same relacje, co tu więcej pisać. Przykładowe wyniki (pierwszy wynik to innodb):

0.0017036
0.0024086
------------
0.0025321
0.0031381
------------
0.0032978
0.0042627
------------
0.0016936
0.0020416

Podsumowanie

Podczas tworzenia relacyjnych baz danych polecam używanie InnoDB – jest szybsze, zachowuje integralność i nie pozwala robić śmietnika z bazy :)

Transakcje

O tym napiszę w kolejnym artykule „Ciekawostki mysql….” :)

 

Tagi: , , ,

Komentarze: 2 do “mysql innodb – klucze obce”

  1. 1 Marcin Baszczewski     2010-02-27 15:42:00

    Witaj. Znalazłem daną notkę w google. Jestem zdziwiony tym co zaobserwowałeś. Trzeba wiedzieć że klucze główne jak i obce zawsze posiadają na sobie indeks. Czy w przypadku testu z MyISAM takowy został dodany?

  2. 2 Mariusz Cypliński     2010-07-19 04:06:44

    Witam,
    Zgadzam się, z tym co napisałeś o kluczach obcych, dodałbym że ani MyISAM ani InnoDB nie obsługują CONSTRAINT CHECKów
    Jednak z tą wydajnością przesadziłeś. Możliwe, że akurat na tym przykładzie Inno działa lepiej, ale np ma prostych Insertach MyISAM jest szybszy i to kilkadziesiąt razy (właśnie nad tym pracuje :) )
    Podobna opinia jest w tym artykule:
    http://webhosting.pl/MySQL.kiedy.warto.przejsc.z.MyISAM.na.InnoDB

    Pozdrawiam,
    MC

Napisz komentarz


Wersja mobilna