SQL Subselect - parę słów o wydajności

Napisano maj 8, 2008

Trafiłem ostatnio na ciekawy wpis na blogu Krisa, traktujący o problemach związanych z wydajnością SQL subselect w dużych bazach (a przynajmniej dużych jak na aplikacje internetową). Temat dla mnie również ciekawy, ponieważ nasza aplikacja ma poważne problemy z wydajnością pojawiające się przy rozroście bazy danych, i część tych problemów jest związana z bazą danych.

Podejście Krisa - polegające na złamaniu postaci normalnej poprzez utworzenie kolumn pomocniczych z pewnością - poprawiło wydajność, ale budzi we mnie niepokój. Spreparowałem więc sobie zestaw danych wejściowych w celu przeprowadzenia własnego eksperymentu…

Jako podstawy użyłem prostego zestawu tabel:Schemat tabel

Nie mam dostępu do PosgreSQL-a, ale wszystkie testy wykonałem na MySQL (5.0, InnoDB) i MSSQL 2000.

Indeksy:

users.id (primary)
users.age
user_language.userId
user_language.languageId
user_language.languageId, user_language.userId

Ilość rekordów:

users - 900 000, languages - 12, user_language - 2 700 000 (3 języki na użytkownika)

WAŻNE! Obie bazy były uruchamiane na różnych maszynach! MySQL pracował na lokalnym komputerze (Pentium 4, 3.2Ghz, 1GB RAM + kupa dodatkowych programów, np. WinAmp w tle), SQL Server na maszynie zdalnej (Xeon 3.2Ghz, 2GB RAMu i w zasadzie tylko MSSQL). Nie można więc porównywać wyników uzyskanych na MySQL i MSSQL.

Druga sprawa: SQL Server był za szybki, żeby sensownie mierzyć czas ;) Ale korzystając z opcji “Execution Plan” SQL Query Analyzera mogę mierzyć szybkość względną zapytania, gdzie 100% = szybkość wykonania wszystkich przedstawionych zapytań.

1. Prosty, podstawowy test: wyszukiwanie ilości osób o określonym wieku:

  1. SELECT COUNT(*) FROM users u WHERE age > 56;

czasy wykonania: MySQL: 0.28, 0.03, 0.03 sekund, SQL Server: 0.35%

  1. SELECT count(*) FROM users u WHERE age < 42;

czasy wykonania: MySQL: 2.31, 0.30, 0.30 sekundy, SQL Server: 3.35%

2. Teraz pora na trochę bardziej złożone zapytanie, wzorowane dokładnie na zapytaniu Krisa:

  1. SELECT COUNT(*)
  2. FROM users u
  3. WHERE
  4.     age < 50
  5.     AND (
  6.         SELECT count(*)
  7.         FROM user_language l
  8.         WHERE l.languageId=4 AND l.userId=u.id
  9.     ) = 1;

czasy wykonania: MySQL: 200.66, 186.50, 186.42 sekund, SQL Server: 18.04%

Szczerze mówiąc, trochę to zapytanie dla mnie było dziwne. Po pierwsze, użycie funkcji COUNT() dla sprawdzenia, czy istnieje rekord jest w pewnym sensie nadużycie. Po drugie, złączenie w podzapytaniu z tabelą nadrzędną też nie jest najlepszym pomysłem - wymusza wykonanie zapytania wewnętrznego dla każdego potencjalnie pasującego rekordu.

Moim następnym pomysłem było pozbycie się ww. wad:

  1. SELECT COUNT(*) FROM users u WHERE age < 50 AND id IN (SELECT userId FROM user_language l WHERE l.languageId=4);

czasy wykonania: MySQL: 172.88, 190.06, 176.76 sekund, SQL Server: 15.42%

Szczerze mówiąc - zdębiałem. Powinienem dostać spory przyrost wydajności, a tym czasem MySQL totalnie olał moje modyfikacje (6% szybciej, a w SQL Serverze uzyskałem około 15%)… zacząłem szukać przyczyny, i wykonałem coś takiego:

  1. SELECT count(*) FROM users u WHERE id IN (SELECT userId FROM user_language l WHERE l.languageId=4);

czasy wykonania: MySQL: 25.81, 25.72, 25.27 sekund, SQL Server: test nie został przeprowadzony

Usunięcie warunku dla wieku dało tak olbrzymi przyrost wydajności! Zupełnie nie rozumiem, co się wydarzyło, ale zacząłem się zastanawiać, jak w takim razie umieścić warunek wieku, i wymyśliłem coś takiego:

  1. SELECT count(*) FROM users u WHERE id IN (SELECT id FROM users WHERE age < 50) AND id IN (SELECT userId FROM user_language l WHERE l.languageId=4);

czasy wykonania: MySQL: 29.97, 30.88, 30.41 sekund, SQL Server: 31.38%

W MySQL użycie dwóch podzapytań działa znacznie lepiej, niż użycie jednego i warunku dla WHERE. Przyczyny są dla mnie na razie tajemnicą ;). SQL Server zadziałał zgodnie z oczekiwaniami, tzn: wydajność poważnie (dwukrotnie!) spadła.

3. Trochę zirytowany podzapytaniami, a w szczególności zachowaniem MySQ, na sam koniec zostawiłem sobie testy z wykorzystaniem zwykłych złączeń

  1. SELECT count(*) FROM users u, user_languages l WHERE l.userId = u.id AND l.languageId = 4 AND u.age < 50;

Czas wykonania: MySQL: 4.08, 4.11, 4.02 sekund. SQL Server: 15.72%

MySQL znacznie szybciej obsługuje zwykłe złączenie niż podzapytania! Przyrost wydajności był imponujący. Natomiast dla SQL Servera użycie złączenia wymagało mniej-więcej tyle samo wysiłku co użycie podzapytania (w najszybciej wersji). Spróbowałem jeszcze odrobinę przyspieszyć zapytanie:

  1. SELECT count(*) FROM users u LEFT JOIN user_language l ON l.userId = u.id WHERE l.languageId = 4 AND u.age < 50;

Czas wykonania: MySQL: 3.86, 3.75, 3.88. SQL Server: 15.72%

Zamiana zwykłego złączenia na lewe złączenie przyspieszyła odrobinę zapytanie - o ok. 3% dla MySQL. SQL Server w ogóle nie zarejestrował zmian: plany wykonania obu zapytań nie różnią się.

Wnioski:

po pierwsze, obsługa podzapytań w MySQL jest bardzo wolna. Jeżeli można - należy jej unikać, korzystając ze złączeń.

po drugie - zapomnij o “przenaszalnych” aplikacjach między różnymi silnikami baz danych. SQL może i jest taki sam (lub podobny), ale sposób jego interpretacji i optymalizacji - zupełnie różny. Zupełnie inaczej optymalizuje się zapytanie dla MySQL, a inaczej dla SQL Servera.

2 Komentarzy

  1. gosc on październik 13th, 2008

    czy moglbys dzielic zapytania na wiersze?

    ciezko sie porownuje zapytania w przewijanym divie

    dzieki :F

  2. Ania on listopad 4th, 2009

    hm, a kto używa count(*), karydnalny błąd :)

Twój komentarz