[ Pobierz całość w formacie PDF ]
.film_actor-> GROUP BY actor_id-> ORDER BY cnt DESC-> LIMIT 10;+----------+-----+------+-------+| actor_id | cnt | rank | dummy |+----------+-----+------+-------+| 107 | 42 | 0 | 0 || 102 | 41 | 0 | 0 |.Ups! Zarówno ranking, jak i licznik nigdy nie przekraczają wartości zero.Dlaczego tak się stało?Nie możliwe jest udzielenie jednej odpowiedzi na takie pytanie.Problem może być bardzoprosty i sprowadzać się do błędnie zapisanej nazwy zmiennej (w tym przypadku jednak taknie jest) lub nieco bardziej skomplikowany.W omawianym przykładzie dane wyjściowe po-lecenia EXPLAIN pokazują, że używana jest tabela tymczasowa oraz sortowanie pliku.Dlategoteż zmienne są obliczane w zupełnie innym czasie, niż jest to oczekiwane.Jest to ten rodzaj tajemniczego zachowania, którego można często doświadczyć w MySQLpodczas używania zmiennych zdefiniowanych przez użytkownika.Usuwanie takich błędówZmienne zdefiniowane przez użytkownika | 219 może być trudne, ale naprawdę opłacalne.Utworzenie rankingu w MySQL zwykle wymagaalgorytmu równania kwadratowego, np.zliczania różnych aktorów, którzy występowaliw większej liczbie filmów.Rozwiązanie z użyciem zmiennej zdefiniowanej przez użytkownikamoże być algorytmem liniowym całkiem spore usprawnienie.W omawianym przypadku łatwym rozwiązaniem jest dodanie do zapytania innego poziomutabel tymczasowych za pomocą podzapytania w klauzuli FROM:mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;-> SELECT actor_id,-> @curr_cnt := cnt AS cnt,-> @rank := IF(@prev_cnt @curr_cnt, @rank + 1, @rank) AS rank,-> @prev_cnt := @curr_cnt AS dummy-> FROM (-> SELECT actor_id, COUNT(*) AS cnt-> FROM sakila.film_actor-> GROUP BY actor_id-> ORDER BY cnt DESC-> LIMIT 10-> ) as der;+----------+-----+------+-------+| actor_id | cnt | rank | dummy |+----------+-----+------+-------+| 107 | 42 | 1 | 42 || 102 | 41 | 2 | 41 || 198 | 40 | 3 | 40 || 181 | 39 | 4 | 39 || 23 | 37 | 5 | 37 || 81 | 36 | 6 | 36 || 106 | 35 | 7 | 35 || 60 | 35 | 7 | 35 || 13 | 35 | 7 | 35 || 158 | 35 | 7 | 35 |+----------+-----+------+-------+Większość problemów dotyczących zmiennych zdefiniowanych przez użytkownika wiąże sięz przypisywaniem im wartości i odczytywaniem ich na różnych etapach zapytania.Przykła-dowo przewidywalnie nie będzie działało przypisanie zmiennej w poleceniu SELECT a od-czytanie w klauzuli WHERE.Wydaje się, że przedstawione poniżej zapytanie zwróci po prostujeden rekord, ale to błędne wyobrażenie:mysql> SET @rownum := 0;mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt-> FROM sakila.actor-> WHERE @rownum SET @rownum := 0;mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt-> FROM sakila.actor-> WHERE @rownum ORDER BY first_name;220 | Rozdział 4.Optymalizacja wydajności zapytań Powyższe zapytanie zwróci każdy rekord z tabeli, ponieważ klauzula ORDER BY dodaje ope-rację sortowania pliku, a klauzula WHERE jest obliczana przed operacją sortowania.Rozwiąza-niem problemu jest przypisanie oraz odczytanie wartości na tym samym etapie procesu wy-konywania zapytania:mysql> SET @rownum := 0;mysql> SELECT actor_id, @rownum AS rownum-> FROM sakila.actor-> WHERE (@rownum := @rownum + 1) SET @rownum := 0;mysql> SELECT actor_id, first_name, @rownum AS rownum-> FROM sakila.actor-> WHERE @rownum ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);Odpowiedz na większość nieoczekiwanych zachowań związanych ze zmiennymi definiowa-nymi przez użytkownika można znalezć po wykonaniu polecenia EXPLAIN i wyszukaniu in-formacji Using where , Using temporary lub Using filesort w kolumnie Extra.W ostatnim przykładzie zademonstrowano inną użyteczną sztuczkę umieszczenie przypi-sania w funkcji LEAST().Jej wartość będzie więc efektywnie maskowana i nie wypaczy wy-niku działania klauzuli ORDER BY (jak wcześniej napisano, wartością zwrotną funkcji LEAST()zawsze jest 0).Sztuczka ta jest bardzo użyteczna, gdy programista chce dokonać przypisaniazmiennej jedynie dla jej efektów ubocznych, pozwala bowiem na ukrycie wartości zwrotnejoraz pomaga w uniknięciu dodatkowych kolumn, takich jak dummy, pokazanych we wcze-śniejszym przykładzie.Funkcje GREATEST(), LENGTH(), ISNULL(), NULLIF(), COALESCE()oraz IF() również można wykorzystać w tym celu, samodzielnie oraz w połączeniu, ponieważmają zachowania specjalne.Przykładowo funkcja COALESCE() zatrzymuje obliczanie argu-mentów, gdy tylko jeden z nich będzie miał zdefiniowaną wartość.Przypisanie zmiennej można umieścić we wszystkich rodzajach poleceń, nie tylko w polece-niach SELECT.W rzeczywistości to jeden z najlepszych sposobów użycia dla zmiennych defi-niowanych przez użytkownika.Programista może np.przepisać kosztowne zapytania, takie jakobliczenia rankingu z podzapytaniami, na postać tanich jednoprzebiegowych poleceń UPDATE.Jednak uzyskanie pożądanego zachowania może być trochę trudne.Czasami optymalizatordecyduje się na uznanie w czasie kompilacji zmiennych za stałe i odmawia przeprowadzeniaprzypisania.Umieszczenie operacji przypisania wewnątrz funkcji, takiej jak LEAST(), zwyklepomaga rozwiązać ten problem.Innym sposobem jest sprawdzenie, czy zmienna ma zdefi-niowaną wartość przed wykonaniem zawierającego ją polecenie.Czasami takie zachowaniejest pożądane, czasami nie.Zmienne zdefiniowane przez użytkownika | 221 Przy odrobinie eksperymentów zmienne definiowane przez użytkownika można wykorzy-stać do wykonywania różnych ciekawych operacji.Poniżej przedstawiono kilka pomysłów." Obliczanie wartości całkowitych i przeciętnych." Emulacja funkcji FIRST() oraz LAST() w zgrupowanych zapytaniach
[ Pobierz całość w formacie PDF ]