Rozkład danych w kolumnach SQL
Normalizacja bazy danych?
Zapewne każdy słyszał o czymś takim jak postać normalna bazy danych. Generalnie chodzi o to, aby baza danych nie powielała informacji i aby zmiana jednego atrybutu nie pociągała za sobą konieczności zmiany tego atrybutu lub atrybutu zależnego w innym miejscu. Ta druga część zdania dotyczy równeż zależności funkcyjnych, które często są źle rozumiane bądź ignorowane. Można się spotkać ze stwierdzeniem, że wszystkie atrybuty niekluczowe są w zależności funkcyjnej od klucza. Nie obejmuje to jednak kilku innych przypadków.
Przykładowo, co nam daje kolumna, która zawsze przyjmuje tę samą wartość? Czy to nie jest zależność funkcyjna? Nie jest to zależność funkcyjna od klucza, ale jet to zależność funkcyjna od całej tabeli! Niejednokrotnie widziałem takie rozwiązania. Przykład drugi, tabela łącząca, linkująca, czy jak ją sobie nazwiemy. Tworzona po to, by realizować funkcję pośrednika w relacji wiele do wielu. Taka tabela składa się najczęściej z dwóch kolumn, które wskazują na kolumny w dwóch innych tabelach. Powiedzmy, że jeden dokument może mieć wiele załączników, a ten sam załącznik może występować w wielu dokumentach. Żeby nie powielać danych dokumentu lub załącznika tworzy się tabelę, która to łączy, spina. Wystarczają do tego dwie wartości - klucz dokumentu i klucz załącznika. O ile do dokumentu nie można wstawić dwa razy tego samego załącznika, te dwie kolumny już są kluczem! Znów muszę przyznać, że zjawisko jest częste, a do takiej tabeli, niejako automatycznie, wstawia się sztuczny klucz główny z opcją IDENTITY. Po co on tam jest? W zasadzie po nic, jest redundantny. Wiele błędnych konstrukcji relacyjnych sprawia, że tabele niepotrzebnie się rozrastają, dane mnożone są bez potrzeby. Może się też pojawiać zjawisko odwrotne, duża ilość wartości NULL znaczących mniej więcej tyle co "Nie dotyczy".
Współczynnik braku danych
Co oznacza duża liczba wartości NULL? Odpowiedź nie jest jednoznaczna, ale może to być objaw złego projektu bazy. Przypuśćmy, że mamy do czynienie z klasycznym problemem, który w obiektowych językach programowania rozwiązuje się przy pomocy dziedziczenia. W tabeli przechowujemy informacje o naprawach samochodów i korzystamy z pewnych standardowych atrybutów - numer rejestracyjny (może to być klucz unikatowy), pojemność silnika, model. Aplikacja działa znakomicie, dopóki do naszego zakładu nie przyjedzie ciężarówka. Ciężarówka też jest samochodem, ale trochę się od zwykłych samochodów różni. Możemy zechcieć zanotować maksymalną ładowność i kilka innych parametrów opisujących samochód ciężarowy (takie wartości, które są bez znaczenia dla samochodów osobowych). Bardzo częstym rozwiązaniem jest dodanie do tabeli samochodów kilku atrybutów, które uzupełniane są tylko w przypadku ciężarówek. Takie rozwiązanie sprawia, że część kolumn w większości przypadków będzie miała NULL. Czy to jest dobre? Niekoniecznie.
Jak zatem zbadać liczbę wartości NULL, a raczej proporcje NULL do NOT NULL kolumn w tabeli, którą podejrzewamy? Przyjrzyjmy się poniższemu zapytaniu:
DECLARE @columns nvarchar(MAX);
SELECT @columns = COALESCE(@columns+',', '')+
'1.0*SUM(CASE WHEN '+C.name+' IS NULL THEN 0 ELSE 1 END)/COUNT(*) ['+C.name+']'
FROM sys.tables T
JOIN sys.columns C ON T.[object_id]=C.[object_id]
WHERE T.name=@table
SET @columns = 'SELECT '+@columns+' FROM '+@table
EXEC(@columns)
Otrzymamy wynik, którego struktura będzie podobna do poniższej:
| Kolumna1 | Kolumna2 | Kolumna3 | Kolumna4 | Kolumna5 |
|---|---|---|---|---|
| 1.000000 | 1.000000 | 0.783452 | 0.034523 | 0.034523 |
Wartość w kolumnie określa współczynnik wypełnienia (lub braku danych). Wartość 1 oznacza pełne wypełnienie, wartość 0 to same wartości NULL. Niskie wartości, bliskie 0, oznaczają niewielką liczbę danych (lub ogromną liczbę wartości NULL). To objaw opisanego w tym podpunkcie problemu. Co więcej, wartość w czwartej i piątej kolumnie jest taka sama, co może sugerować zależność funkcyjną (albo obie wartości są uzupełnione, albo obie są równe NULL).
Jak można temu zaradzić? Stworzyć relację jeden do jednego. Zostawić tabelę Samochód, a dodać tabelę Ciężarówka, która będzie wskazywała na samochód. Jeżeli samochód nie jest ciężarówką, uzupełniamy tylko dane w tabeli Samochód. Jeżeli samochód jest ciężarówką, uzupełniamy obie tabele. Dla samochodu nie mamy pustych kolumn, a jedynym efektem ubocznym w tabeli Ciężarówka jest powielony klucz z tabeli Samochód. Może nie jedynym, bo teraz trzeba te tabele ze sobą złączyć. Nie jest to jakiś szczególny koszt dla SQL Server, bo w relacji jeden do jeden kluczem głównym w tabeli Ciężarówka jest klucz z tabeli Samochód. Klucz główny automatycznie tworzy indeks, a w zasadzie to nawet indeks unikalny, który dla klucza zwróci maksymalnie jedną wartość. Wystarczy nam zwykłe złączenie zewnętrzne.
Warto wpomnieć, że powód dużej liczby wartości NULL może leżeć zupełnie gdzie indziej. Być może użytkownik aplikacji nie wpisuje jakiejś wartości, bo nie jest mu do niczego potrzebna. Być może istnieje pole email, ale właściciel zakładu i tak maili nie wysyła. Po co ma się fatygować i tracić swój, z pewnością cenny, czas.
Różnorodność danych w kolumnie
Wspomniałem na wstępie, że zdarzają się tabele, które mają w pewnej kolumnie (kolumnach) zawsze tę samą wartość. Oznacza to funkcyjną zależność kolumny od tabeli. Dwie wartości w kolumnie mogą oznaczać atrybuty będące flagą bitową, kilka wartości może oznaczać definicje słownikowe lub pewne z góry zdefiniowane stałe atrybuty. Brak różnorodności to najczęściej błąd - zależność funkcyjną (chyba, że dane nie są reprezentatywne). Niskie wartości, nazwijmy to sobie, współczynnika różnorodności mogą posłużyć do opracowania odpowiedniego mechanizmu partycjonowania (np. flaga oznaczająca rekordy archiwalne). Te same wartości współczynnika w dwóch kolumnach mogą być (ale nie muszą) sygnałem zależności funkcyjnych. I w końcu, niskie współczynniki mogą być objawem dużej liczby wartości NULL. Mała różnorodność to też przeciwwskazanie do zakładania indeksu na tej kolumnie. Indeks lepiej się spisuje, gdy jest wysoko selektywny. Dla dużej grupy tych samych wartości musi przeprowadzać poziome skanowanie, w ramach tej grupy, a także, bardzo często, odwoływać się do pozostałych danych jeżeli indeks nie jest pokrywający (operacje Key lookup lub RID lookup).
Jak zatem zbadać współczynnik różnorodności? Popatrzmy na poniższy listing:
DECLARE @columns nvarchar(MAX);
SELECT @columns = COALESCE(@columns+','+CHAR(13)+CHAR(10), '')+
'1.0*(COUNT(DISTINCT '+C.name+')-1)/(COUNT(*)-1) ['+C.name+']'
FROM sys.tables T
JOIN sys.columns C ON T.[object_id]=C.[object_id]
WHERE T.name=@table
SET @columns = 'SELECT '+@columns+CHAR(13)+CHAR(10)+'FROM '+@table+' HAVING COUNT(*)>1'
EXEC(@columns)
Po wykonaniu skryptu otrzymamy wynik, którego struktura będzie podobna do poniższej:
| Kolumna1 | Kolumna2 | Kolumna3 | Kolumna4 | Kolumna5 |
|---|---|---|---|---|
| 1.000000 | 0.956332 | 0.743223 | 0.014534 | 0.000345 |
Współczynnik wypełnienia to tylko pewna wskazówka. Pozwala wysnuć pewną hipotezę, którą trzeba zweryfikować.
Wnioski
Badanie struktury i rozmieszczenia danych pozwala wykryć wiele nieprawidłowości w projekcie bazy danych. Nie zawsze można taką bazę łatwo poprawić. Wszystko i tak w którymś momencie musi być przeliczone na roboczogodziny, a ostatecznie na koszty. Wykrycie nieprawidłowości ma jednak jedną zaletę - możliwość uczenia się na błędach. Być może w przyszłości uda się takich błędów uniknąć.
Kategoria:Optymalizacja SQLSQL Server
Brak komentarzy - bądź pierwszy