Profilowanie aplikacji Opcje

[nospor]

Zgodnie z życzeniem: "Profilowanie aplikacji".
Zachęcam do udziału w dyskusji

[Krolik]

Pytanie było specjalnie takie, żeby nie było jednoznacznej odpowiedzi. Słabsze systemy najprawdopodobniej wybiorą mniejsze zło, tj. tak jak napisał DevV:

LIMIT (1, 10)
|
+ SORT (cena)
|
+ INDEX_SCAN (miasto = 'Krakow' AND cena < 5000) <-- indeks na (miasto, cena)


Albo tak (nieco gorzej):


LIMIT (1, 10)
|
+ SORT (cena)
|
+ FILTER (cena < 5000)
|
+ INDEX_SCAN (miasto = 'Krakow') <--- indeks tylko na (miasto)


Wszystko zależy trochę od rozkładu wartości w bazie, ale można się spodziewać, że ponieważ Kraków jest dużym miastem,
to warunek miasto = Kraków ma słabą selektywność (załóżmy 20%), podobnie warunek na cenę też ma b. słabą selektywność (ok. 50%, jeśli rozkład jest równomierny). W tej sytuacji najlepszy byłby indeks klastrujący na cena, żeby uniknąć sortowania:

LIMIT(1, 10)
|
+ FILTER (miasto = 'Krakow')
|
INDEX SCAN (cena < 5000) <--- indeks tylko na (cena)

Niezależnie od sumarycznej liczby rekordów ten plan wymaga odczytania średnio raptem ok. 50 rekordów tabeli, ze względu na możliwość wykonania iteracyjnego i LIMIT, podczas gdy poprzednie plany czytają odpowiednio 10% * liczba_rekordów (pierwszy) i 20% * liczba_rekordów (drugi) i oba sortują 10% rekordów całej tabeli. Pewnie DB/2 lub Oracle potrafiłoby znaleźć ten plan bez hintów, ale muszę sprawdzić. Jeśli optymalizator nie zauważy, że można wyeliminować sortowanie oraz że można przerwać skanowanie po osiągnięciu 10 rekordów wyniku, ten plan automatycznie staje się prawie najgorszym z możliwych.

To tyle omówienia zadania. Zadanie miało na celu tylko zilustrowanie, że problem wprawdzie prosty nie jest i rozwiązanie mocno zależny od jakości optymalizatora bazy danych, to nie jest wcale żadną tajemnicą, co potrafią robić różne optymalizatory i program może to uwzględniać. Wydaje mi się, że nawet w niektórych przypadkach może to zrobić lepiej niż człowiek - kto z Was zna szczegóły działania optymalizatora MySQL czy PostgreSQL? Raczej stosuje się metodę "prób i błędów" a nie podejście naukowe. Podobnie program bez trudu potrafi oszacować selektywność warunków i zrobić podobną analizę, którą przedstawiłem przy okazji zadania.

BTW. Istnieje taki projekt dla DB/2 i działa nieźle. Dla MySQLa byłoby dużo prosciej, bo on potrafi zdecydowanie mniej (ma regułowy optymalizator -> łatwo się go przewiduje). Ponadto nie zalezy mi na tym, by ten program dawał zawsze najlepszy możliwy wynik, ale żeby raczej stanowił pomoc w "zgrubnym" dobraniu większości indeksów, w bardzo krótkim czasie.