Z tego co mi wiadomo mój poprzedni wpis o viewsach spotkał się ze sporym zainteresowaniem. Szkoda mi porzucić tematu, ponieważ jest on bardzo wdzięczny. Chciałabym więc poruszyć kwestie optymalizacji widoków. Bardzo prosto jest wbrew pozorom wygenerować viewsa, który wykonuje się dłużej niż 10 sekund. Nawet przy włączonym cache'owaniu jest to na tyle uciążliwe, że warto zadbać o poprawną optymalizację. Aby jednak tego dokonać trzeba mieć nieco elementarnej wiedzy na temat zapytań do baz danych. W to również postaram się zagłębić.

Teoria

Widoki najczęściej są wolne przez dwa elementy:

  • LEFT JOIN (RIGHT JOIN)
  • DISTINCT

Weźmy pod uwagę dwie tabele:

a
a_id a_value
1 Kowalska
2 Nowak
3 Kwiatkowski
b
b_id a_id b_value
1 2 Anna
2 3 Jan
3 4 Tomasz

LEFT JOIN

Jest bardzo częstym sposobem łączenia danych w tabelach. I nader często jest gigantycznym nadużyciem.
Najszybciej i najogólniej: mamy dwie tabele A i B, gdy zastosujemy LEFT JOINA dostaniemy wszystkie wyniki z bazy A połączone z rekordami z bazy B (gdy istnieje odpowiednie powiązanie) lub NULL gdy nie istnieje powiązanie. RIGHT JOIN robi to samo w drugą stronę - połączy elementy bazy B z rekordami A (gdy istnieje powiązanie) lub NULL gdy ono nie istnieje. Na przykładzie:

select a_value, b_value from a left join b on a.a_id = b.a_id;

a_value b_value
Kowalska NULL
Nowak Anna
Kwiatkowski Jan

select a_value, b_value from a right join b on a.a_id = b.a_id;

a_value b_value
Nowak Anna
Kwiatkowski Jan
NULL Tomasz
Rozwiązanie - INNER JOIN

INNER JOIN połączy jedynie pasujące elementy.

select a_value, b_value from a inner join b on a.a_id = b.a_id;

a_value b_value
Nowak Anna
Kwiatkowski Jan

Nie musi sprawdzać dodatkowych warunków, więc jest o wiele bardziej wydajne niż LEFT czy RIGHT JOIN.

DISTINCT

Nie istnieje coś takiego jak DISTINCT na jednym polu! Jeśli w zapytaniu użyjemy frazy DISTINCT zostanie ona zastosowana do wszystkich pól wybranych w select.
Weźmy pod uwagę tabelę

a
a_id a_value
1 Kowalska
2 Nowak
3 Kwiatkowski
4 Kwiatkowski

select distinct(a_value), a_id from a;

Zadziwiająco (sic!) zostaną zwrócone wszystkie rekordy, mimo że Kwiatkowski powtarza się dwukrotnie. Dzieje się tak, bo bez względu na pozorne przypisanie do jednego pola DISTINCT działa na wszystkich polach w klauzuli select. To znaczy, że szuka niepowtarzalnych par a_value i a_id. Nie znajduje, bo nasz Kowalski ma dwa różne a_id.
Często DISTINCT jest używane w takim kontekście. Nie dość więc, że nie dostajemy tego, o co nam chodziło to jeszcze niepotrzebnie zajeżdżamy bazę!

Viewsy a optymalizacja

Aby w ogóle wiedzieć jakie zapytanie generują nam nasze widoki koniecznie należy przejść do strony admin/strucure/views/settings i włączyć opcje:

  • Pokazuj zapytanie SQL
  • Pokazuj statystyki wydajności

Na początku nie polecam Podczas generowania widoku w trybie podglądu wyświetlaj pozostałe zapytania, ponieważ może to zaciemnić ogólny widok.



Podgląd widoku razem z zapytaniem.

Pozbywanie się LEFT/RIGHT JOINÓW

W widokach jest kilka miejsc, dla których mogą wystąpić LEFT/RIGHT JOINY. Wiadomo mi o dwóch, nad którymi mamy bezpośrednią kontrolę.

Powiązania
Domyślnie element Powiązanie obowiązkowe jest odznaczone, co generuje LEFT JOINA, jak na powyższym przykładzie. Po włączeniu opcji pojawi się INNER JOIN. Jeśli więc mamy np. powiązanie z autorem (niejako niezbędne w Drupalu, nawet node'y czy komentarze niezarejestowanych userów mają swój odpowiednik w tabeli {users}) zupełnie nie warto zostawiać opcji odhaczonej.

Filtry kontekstowe
Drupalowe widoki dostarczają jednego bardzo postępnego filtra - Zawartość: Posiada ID terminu kategorii (z głębokością). Spróbujcie ustawić go na głębokość 10 i dostarczyć domyślną wartość ID terminu taksonomii z adresu URL. Dostaniemy mniej więcej taki wynik:
SELECT node.nid AS nid, node.title AS node_title, node.created AS node_created, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, 'node' AS field_data_field_blog_tags_node_entity_type, 'node' AS field_data_body_node_entity_type
FROM
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
INNER JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE (( (node.nid IN (SELECT tn.nid AS nid
FROM
{taxonomy_index} tn
LEFT OUTER JOIN {taxonomy_term_hierarchy} th ON th.tid = tn.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th1 ON th.parent = th1.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th2 ON th1.parent = th2.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th3 ON th2.parent = th3.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th4 ON th3.parent = th4.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th5 ON th4.parent = th5.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th6 ON th5.parent = th6.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th7 ON th6.parent = th7.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th8 ON th7.parent = th8.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th9 ON th8.parent = th9.tid
LEFT OUTER JOIN {taxonomy_term_hierarchy} th10 ON th9.parent = th10.tid
WHERE ( (tn.tid = '1') OR (th1.tid = '1') OR (th2.tid = '1') OR (th3.tid = '1') OR (th4.tid = '1') OR (th5.tid = '1') OR (th6.tid = '1') OR (th7.tid = '1') OR (th8.tid = '1') OR (th9.tid = '1') OR (th10.tid = '1') ))) )AND(( (node.status = '1') AND (node.type IN ('blog_post')) )OR( (node.type IN ('book')) AND (node.status = '1') AND (node.promote '0') )))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

Wygląda dostatecznie koszmarnie. Jeśli więc wiemy, że w serwisie mamy tylko jeden poziom podkategorii bezwzględnie należy ustawić głębokość na 1, a jeśli w ogóle mamy płaską kategoryzację wybrać Zawartość: Posiada ID terminu kategorii.

DISTICT

Ogólnie należy unikać jego używania. Chyba że faktycznie nie da się inaczej ;)

Sortowanie

Dodatkowy TIP: jeśli używacie sortowania upewnijcie się, że na takie pole założony jest indeks.

W przypadku, gdy chcemy wyświetlić wpisy od najstarszych do najnowszych do najstarszych dobrym pomysłem jest na przykład sortowanie nie po czasie dodania (jeśli nie zmieniamy tego czasu z ekranu dodawania node'a, a node'y nie są zaimportowane z zewnętrznego źródła odpowiednio) a po nid (ID Zawartości). Jest to również klucz główny tabeli {node} więc widok będzie dużo bardziej efektywny.

Filtracja

Najpierw ustawiajmy najmniej zasobożerne elementy. Dla przykładu jeśli chcemy ograniczyć wpisy tylko do kategorii Drupal (identyfikator 1) i przedstawić tylko opublikowane wpisy fragment kwerendy może wyglądać tak:
WHERE (term_node.tid = 1) AND (node.status 0)
albo
WHERE (node.status 0) AND (term_node.tid = 1)

Pierwsza wersja jest bardziej efektywna, z tego względu, że widok najpierw filtruje w "bazowej" tabeli (zakładając, że views jest typu "Zawartość").

Pomocne moduły

W razie dalszych problemów można spróbować wykorzystać moduł Shadow - SQL queries / views optimization

Podsumowanie

Niestety nie mam benchmarków, nie jestem więc w stanie udowodnić konkretnych przyspieszeń w konkretnych sytuacjach. Z doświadczenia (przy pracy nad zaledwie dziesiątkami tysięcy node'ów i userów) mogę jednak podpowiedzieć, że po optymalizacji widoki przyspieszyły od 2 do w niektórych przypadkach ponad 100 razy.
Pojawia się pytanie jak określić, które z viewsów warto optymalizować.

Mając zainstalowane moduły Admin i Devel jest to proste. Wystarczy wejść na stronę admin/config/development/devel, zaznaczyć opcję Wyświetl dziennik zapytań i określić poniżej, które zapytania są zapytaniami wolnymi. W rozwijanym menu modułu admin w zakładce Devel pokaże się opcja do wyświetlania wolnych kwerend. Minus jest taki, że nie znalazłam genialnego sposobu na stwierdzenie, który views generuje daną kwerendę. Osobiście patrzę na kwerendę i określam, skąd może pochodzić. Plusem jest, że po jakimś czasie nawet przy bardziej zaawansowanych widokach przestało mi to sprawiać problem.

Komentarze

Dodaj komentarz