Dlaczego zaleca się przechowywanie obiektów BLOB w osobnych tabelach programu SQL Server?

Ta wysoko oceniana odpowiedź SO zaleca umieszczanie obrazów w osobnych tabelach, nawet jeśli istnieje relacja 1: 1 z inną tabelą:

Jeśli zdecydujesz się umieścić swoje zdjęcia w tabeli programu SQL Server, zdecydowanie zalecam użycie osobnej tabeli do przechowywania tych zdjęć - nie przechowuj zdjęcia pracownika w tabeli pracownika - przechowuj je w osobnej tabeli. W ten sposób stół pracownika może pozostać szczupły, wredny i bardzo wydajny, zakładając, że nie zawsze musisz wybierać zdjęcie pracownika również w ramach twoich zapytań.

Czemu? Miałem wrażenie, że SQL Server przechowuje tylko wskaźnik do jakiejś dedykowanej struktury danych BLOB w tabeli, więc po co zawracać sobie głowy ręcznym tworzeniem kolejnej warstwy pośredniej? Czy to naprawdę znacząco poprawia wydajność? Jeśli tak, dlaczego?

Chociaż nie zgadzam się z tym, że BLOBy powinny znajdować się w innej tabeli - w ogóle nie powinny znajdować się w bazie danych . Zapisz wskaźnik do miejsca, w którym plik znajduje się na dysku, a następnie pobierz go z bazy danych ...

Głównym problemem, który powodują (dla mnie), jest indeksowanie. Używając XML z planami zapytań, ponieważ wszyscy są gotowi, zróbmy tabelę:

SELECT TOP 1000
ID = IDENTITY(INT,1,1),
deq.query_plan
INTO dbo.index_test
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq

ALTER TABLE dbo.index_test ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (ID)

To tylko 1000 wierszy, ale sprawdzanie rozmiaru ...

sp_BlitzIndex @DatabaseName = 'StackOverflow', @SchemaName = 'dbo', @TableName = 'index_test'

To ponad 40 MB na zaledwie 1000 wierszy. Zakładając, że dodajesz 40 MB co 1000 wierszy, może to dość szybko stać się brzydkie. Co się stanie, gdy trafisz 1 milion wierszy? To tylko około 1 TB danych.

Wszelkie zapytania, które wymagają użycia indeksu klastrowego, muszą teraz wczytać wszystkie te dane BLOB do wyjaśnienia pamięci : gdy odwołuje się do kolumny danych BLOB.

Czy możesz wymyślić lepszy sposób wykorzystania pamięci SQL Server niż przechowywanie BLOBów? Ponieważ na pewno mogę.

Rozwijanie go do indeksów nieklastrowanych:

CREATE INDEX ix_noblob ON dbo.index_test (ID)

CREATE INDEX ix_returnoftheblob ON dbo.index_test (ID) INCLUDE (query_plan)

Możesz zaprojektować indeksy nieklastrowane, aby w dużej mierze unikać kolumny BLOB, aby regularne zapytania mogły ominąć indeks klastrowany, ale gdy tylko potrzebujesz tej kolumny BLOB, potrzebujesz indeks klastrowany.

Jeśli dodasz go jako INCLUDEDkolumnę do indeksu nieklastrowanego, aby uniknąć scenariusza wyszukiwania klucza, powstanie gigantyczny indeks nieklastrowany:

Więcej problemów, które powodują:

• Jeśli ktoś uruchomi SELECT *zapytanie, otrzyma wszystkie dane BLOB.
• Zajmują miejsce w kopiach zapasowych i przywracają, spowalniając je
• Zwalniają DBCC CHECKDB, bo wiem, że sprawdzasz, czy nie ma korupcji, prawda?
• A jeśli wykonasz jakąkolwiek konserwację indeksu, one również to spowolnią.

Mam nadzieję że to pomoże!

Jak duże są te obrazy i ile ich oczekujesz? Chociaż w większości zgadzam się z @sp_BlitzErik , myślę, że jest kilka scenariuszy, w których można to zrobić, a więc pomogłoby to uzyskać wyraźniejszy obraz tego, o co właściwie jest tutaj proszony.

Niektóre opcje, które należy rozważyć, które łagodzą większość negatywnych aspektów wskazanych przez Erika, to:

• FILESTREAM (od SQL Server 2008)
• FileTables (od SQL Server 2012)

Obie te opcje zostały zaprojektowane tak, aby stanowić środek pośredni między przechowywaniem obiektów BLOB w całości w SQL Server lub całkowicie na zewnątrz (z wyjątkiem colun ciągów, aby zachować ścieżkę). Pozwalają one, aby obiekty BLOB były częścią modelu danych i uczestniczyły w transakcjach, nie marnując miejsca w puli buforów (tj. Pamięci). Dane BLOB są nadal uwzględniane w kopiach zapasowych, co powoduje, że zajmują więcej miejsca i dłużej zajmują kopie zapasowe iprzywrócić. Mam jednak trudności z postrzeganiem tego jako prawdziwego negatywu, biorąc pod uwagę, że jeśli jest to część aplikacji, należy jakoś wykonać kopię zapasową, a posiadanie tylko kolumny ciągów zawierających ścieżkę jest całkowicie odłączone i pozwala na pobieranie plików BLOB usunięty bez wskazania tego w bazie danych (tj. nieprawidłowe wskaźniki / brakujące pliki). Pozwala również na „usuwanie” plików w bazie danych, ale nadal istnieje w systemie plików, który będzie musiał zostać ostatecznie wyczyszczony (np. Ból głowy). Ale jeśli pliki są OGROMNE, być może najlepiej jest pozostawić program SQL Server całkowicie poza kolumną ścieżki.

Pomaga to w pytaniu „wewnątrz lub na zewnątrz”, ale nie dotyczy pojedynczego stołu w porównaniu do pytania o wielu stołach. Mogę powiedzieć, że poza tym konkretnym pytaniem z pewnością istnieją uzasadnione przypadki podziału tabel na grupy kolumn na podstawie wzorców użytkowania. Często, gdy jedna ma 50 lub więcej kolumn, niektóre z nich są często używane, a niektóre nie. Niektóre kolumny są zapisywane często, a niektóre w większości są czytane. Rozdzielenie często uzyskiwanych i nierzadko uzyskiwanych kolumn na wiele tabel o relacji 1: 1 jest dość często korzystne, ponieważ po co marnować miejsce w puli buforów na dane, których prawdopodobnie nie używasz (podobnie jak w przypadku przechowywania dużych obrazów w regularnychVARBINARY(MAX)kolumny to problem)? Zwiększasz również wydajność często używanych kolumn, zmniejszając rozmiar wiersza, a tym samym dopasowując więcej wierszy do strony danych, dzięki czemu odczyty (fizyczne i logiczne) są bardziej wydajne. Oczywiście wprowadzasz także pewną nieefektywność, powielając PK, a teraz czasami musisz dołączyć do dwóch tabel, co również komplikuje (choćby nieznacznie) niektóre zapytania.

Istnieje więc kilka metod, które można zastosować, a to, co najlepsze, zależy od środowiska i tego, co próbujesz osiągnąć.

Miałem wrażenie, że SQL Server przechowuje tylko wskaźnik do określonej struktury danych BLOB w tabeli

Nie takie proste. Możesz znaleźć dobre informacje tutaj, jaki jest rozmiar wskaźnika LOB dla typów (MAX), takich jak Varchar, Varbinary itp.? , ale podstawy to:

• TEXT, NTEXTi IMAGEtypy danych (domyślnie): 16-bajtowy wskaźnik
• VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)(Domyślnie):
  • Jeśli dane mieszczą się w wierszu, zostaną tam umieszczone
  • Jeśli dane są mniejsze niż około 40 000 bajtów (link do wpisu na blogu pokazuje 40 000 jako górną granicę, ale moje testy wykazały nieco wyższą wartość) ORAZ jeśli w wierszu jest miejsce na tę strukturę, będzie od 1 do 5 bezpośrednich linków do stron LOB, zaczynając od 24 bajty dla pierwszego łącza do pierwszych 8000 bajtów, i wzrost o 12 bajtów na każde dodatkowe łącze dla każdego dodatkowego zestawu 8000 bajtów, maksymalnie 72 bajty.
  • Jeśli dane przekroczą ok. 40 000 bajtów LUB nie ma wystarczającej ilości miejsca do zapisania odpowiedniej liczby bezpośrednich łączy (np. W wierszu pozostało tylko 40 bajtów, a wartość 20 000 bajtów wymaga 3 łączy, co oznacza 24 bajty dla pierwszego plus 12 dla dwóch dodatkowych łączy dla 48 bajtów całkowita wymagana przestrzeń w wierszu), wówczas na stronie drzewa tekstowego zawierającego łącza do stron LOB pojawi się tylko 24-bajtowy wskaźnik).

Jeśli dane muszą być przechowywane w SQL Server z jakiegokolwiek powodu, mogę wymyślić kilka korzyści z przechowywania ich w osobnej tabeli. Niektóre są bardziej przekonujące niż inne.

1. Umieszczenie danych w osobnej tabeli oznacza, że ​​możesz przechowywać je w osobnej bazie danych. Może to mieć zalety w zakresie planowej konserwacji. Na przykład można uruchomić DBCC CHECKDBtylko w bazie danych, która zawiera dane BLOB.

2. Jeśli nie zawsze wstawisz więcej niż 8000 bajtów do BLOBa, możliwe jest, że będzie on przechowywany w wierszu dla niektórych wierszy. Możesz tego nie chcieć, ponieważ spowolni on zapytania, które uzyskują dostęp do danych za pomocą indeksu klastrowanego, nawet jeśli kolumna nie jest potrzebna w zapytaniu. Umieszczenie danych w osobnej tabeli eliminuje to ryzyko.

3. Podczas przechowywania poza wierszem SQL Server używa wskaźnika do 24 bajtów, aby wskazać nową stronę. Zajmuje to miejsce i ogranicza całkowitą liczbę kolumn BLOB, które można dodać do pojedynczej tabeli. Aby uzyskać więcej informacji, zobacz odpowiedź srutzky.

4. Klastrowego indeksu magazynu kolumn nie można zdefiniować w tabeli zawierającej kolumnę BLOB. To ograniczenie zostało usunięte zostanie usunięte w SQL Server 2017.

5. Jeśli ostatecznie zdecydujesz, że dane powinny zostać przeniesione poza SQL Server, łatwiej będzie wprowadzić tę zmianę, jeśli dane są już w osobnej tabeli.