Co zyskują relacyjne bazy danych, ustawiając predefiniowany typ danych dla każdej kolumny?

Obecnie pracuję z bazą danych SQL i zawsze mnie to ciekawiło, ale wyszukiwania Google nie pojawiają się zbyt często: dlaczego ścisłe typy danych?

Rozumiem, dlaczego miałbyś mieć kilka różnych typów danych, na przykład, jak ważne jest rozróżnienie między danymi binarnymi i zwykłymi danymi tekstowymi . Rozumiem, że zamiast przechowywać jedynki i zera danych binarnych w postaci zwykłego tekstu, przechowywanie danych binarnych jako własnego formatu jest bardziej wydajne.

Ale nie rozumiem, jaka jest korzyść z posiadania tak wielu różnych typów danych:

• Dlaczego mediumtext, longtexti text?
• Dlaczego decimal, floati int?
• itp.

Jaka jest korzyść z powiedzenia bazie danych „We wpisach do tej kolumny będzie tylko 256 bajtów danych tekstowych”. lub „Ta kolumna może zawierać wpisy do 16 777 215 bajtów”?

Czy to poprawa wydajności? Jeśli tak, to dlaczego znajomość wielkości wpisu przed rozdaniem pomaga w wydajności? A może jest to coś zupełnie innego?

SQL jest językiem o typie statycznym . Oznacza to, że musisz wiedzieć, jaki typ zmiennej (lub pola, w tym przypadku) jest, zanim będziesz mógł z niej korzystać. Jest to przeciwieństwo języków dynamicznie typowanych, w przypadku których niekoniecznie tak jest.

Zasadniczo SQL został zaprojektowany do definiowania danych ( DDL ) i dostępu do danych ( DML ) w silniku relacyjnej bazy danych . Pisanie statyczne ma kilka zalet w porównaniu z typowaniem dynamicznym tego typu systemu.

• Indeksy używane do szybkiego dostępu do określonych rekordów działają naprawdę dobrze, gdy rozmiar jest ustalony. Rozważ zapytanie, które wykorzystuje indeks, prawdopodobnie z wieloma polami: jeśli typy danych i rozmiary są znane z wyprzedzeniem, mogę bardzo szybko porównać mój predykat (klauzula WHERE lub kryteria JOIN) z wartościami w indeksie i szybciej znaleźć żądane rekordy .

• Rozważ dwie wartości całkowite . W systemie typu dynamicznego mogą mieć zmienną wielkość (pomyśl Java BigIntegerlub wbudowane w Pythona liczby całkowite o dowolnej dokładności). Jeśli chcę porównać liczby całkowite, najpierw muszę poznać ich długość. Jest to aspekt porównania liczb całkowitych, który jest w dużej mierze ukryty przez współczesne języki, ale jest bardzo realny na poziomie procesora. Jeśli rozmiary są ustalone i znane z wyprzedzeniem, cały etap jest usuwany z procesu. Ponownie, bazy danych powinny być w stanie przetwarzać zilliony transakcji tak szybko, jak to możliwe. Prędkość jest królem.

• SQL został zaprojektowany w latach 70. We wcześniejszych czasach mikrokomputera pamięć była na wagę złota. Ograniczenie danych pomogło utrzymać w ryzach wymagania dotyczące pamięci. Jeśli liczba całkowita nigdy nie przekroczy jednego bajtu, po co przydzielać dla niej więcej pamięci? To marnowana przestrzeń w erze ograniczonej pamięci. Nawet w dzisiejszych czasach te dodatkowe zmarnowane bajty mogą zsumować i zabić wydajność pamięci podręcznej procesora. Pamiętaj, że są to silniki baz danych, które mogą obsługiwać setki transakcji na sekundę, a nie tylko małe środowisko programistyczne.

• Wzdłuż linii ograniczonej przestrzeni dyskowej pomocne może być zmieszczenie jednego rekordu na jednej stronie w pamięci. Po przejściu przez jedną stronę pojawia się więcej braków stron i wolniejszy dostęp do pamięci. Nowsze silniki mają optymalizacje, które sprawiają, że jest to mniejszy problem, ale nadal istnieje. Odpowiednio dobierając dane, możesz zmniejszyć to ryzyko.

• Moreso w czasach współczesnych, SQL jest używany do podłączenia do innych językach za pośrednictwem ORM lub ODBC lub innej warstwie. Niektóre z tych języków mają reguły wymagające silnych, statycznych typów. Najlepiej jest dostosować się do bardziej rygorystycznych wymagań, ponieważ dynamicznie pisane języki radzą sobie z typami statycznymi łatwiej niż na odwrót.

• SQL obsługuje typowanie statyczne, ponieważ silniki bazy danych potrzebują go do wydajności, jak pokazano powyżej.

Warto zauważyć, że istnieją implementacje SQL, które nie są silnie typowane. SQLite jest prawdopodobnie najpopularniejszym przykładem takiego relacyjnego silnika bazy danych. Z drugiej strony jest przeznaczony do użytku jednowątkowego w jednym systemie, więc problemy z wydajnością mogą nie być tak wyraźne, jak np. W korporacyjnej bazie danych Oracle obsługującej miliony żądań na minutę.

Po pierwsze: zwykły tekst jest binarny (nie są to nawet znaki UTF8 ani ASCII „0” i „1”, ale rzeczywiste bity włączania / wyłączania)

To powiedziawszy, niektóre z powodów to:

• Ograniczenia biznesowe / projektowe: dopuszczenie liczby 7626355112 w kolumnie HEIGHT tabeli PERSON byłoby błędne. Dopuszczenie „Howya” w kolumnie DATA FAKTURY byłoby błędne.
• Mniej podatny na błędy kod: nie musisz pisać kodu, aby upewnić się, że dane pobrane z kolumny daty są naprawdę datą. Jeśli typy kolumn byłyby dynamiczne, musiałbyś wykonać wiele kontroli typu podczas ich czytania.
• Wydajność obliczeniowa: jeśli kolumna jest typu INTEGER, a jeśli SUM (), RDBMS nie musi stosować arytmetyki zmiennoprzecinkowej.
• Wydajność pamięci: stwierdzenie, że kolumna to VARCHAR (10), umożliwia RDBMS dokładniejsze przydzielanie miejsca.
• Integralność referencyjna i jednoznaczność: PK (lub FK) tabeli nie powinny dopuszczać liczb zmiennoprzecinkowych, ponieważ równość zmiennoprzecinkowa jest trudna, dlatego należy zadeklarować je w typie zmiennoprzecinkowym, takim jak znaki lub liczby całkowite.
• Istnieją RDBMS z dynamicznymi (nie ścisłymi) typami kolumn (SQLite) . Wykorzystuje koncepcję „powinowactwa typu”, jednocześnie pozwalając ci wstawić praktycznie wszystko w dowolną kolumnę bez narzekania. Istnieją kompromisy, których nie będziemy tutaj omawiać. Zobacz to pytanie .

Jest tak, że bazowy kod, w którym zapisana jest baza danych, może przydzielić i używać rekordów o stałym rozmiarze, jeśli wie, że określone pole może zawierać od 0 do 256 znaków tekstu, może przydzielić blok 256 bajtów do przechowywania.

To sprawia, że ​​rzeczy są znacznie szybsze, np. Nie musisz przydzielać dodatkowej pamięci, ponieważ typy użytkowników, ponieważ dane pole zawsze rozpoczyna x bajtów w rekordzie wyszukiwania lub wybierz w tym polu wie, że zawsze sprawdza x bajtów w każdym rekordzie itp.

Gdy kolumny bazy danych mają zdefiniowane typy, typy są zwykle definiowane same w celu uzyskania określonego rozmiaru w bitach. W rezultacie:

1) gdy silnik bazy danych przegląda wiersze w tabeli, nie musi wykonywać żadnych fantazyjnych analiz, aby ustalić, gdzie kończy się każdy rekord, może po prostu wiedzieć, że każdy wiersz składa się, powiedzmy, z 32 bajtów, i tak następny rekord wystarczy dodać 32 bajty do bieżącej lokalizacji rekordów.

2) podczas wyszukiwania pola w wierszu można ponownie poznać dokładne przesunięcie dla tego pola bez parsowania, więc wyszukiwanie kolumn jest prostą operacją arytmetyczną, a nie potencjalnie kosztownym przetwarzaniem danych.

Zapytałeś, dlaczego DBMS mają statyczne typy danych.

1. Szybkość wyszukiwania. Istotą DBMS jest przechowywanie znacznie większej ilości danych, niż można by załadować do programu. Pomyśl „wszystkie odcinki kart kredytowych wygenerowane na świecie w ciągu ostatnich dziesięciu lat”. W celu skutecznego wyszukiwania takich danych pomocne są typy danych o stałej długości. Jest to szczególnie prawdziwe w przypadku danych strukturalnych, takich jak datowniki i numery kont. Jeśli wiesz, z czym masz do czynienia z wyprzedzeniem, łatwiej jest załadować do wydajnych indeksów.

2. Uczciwość i ograniczenia. Łatwiej utrzymać dane w czystości, jeśli mają ustalone typy danych.

3. Historia. RDBMS zaczęły się, gdy komputery miały zaledwie kilka megabajtów pamięci RAM, a pamięć masowa w terabajtach była niezwykle droga. Zapisanie tuzina bajtów w każdym rzędzie tabeli mogłoby w tych okolicznościach zaoszczędzić tysiące dolarów i godziny czasu.

4. Klątwa bazy klientów. RDBMS są dziś bardzo złożonymi, wysoce zoptymalizowanymi pakietami oprogramowania i są używane od dziesięcioleci gromadząc dane. Są dojrzałe. Oni pracują. Awaria RDBMS powodująca utratę danych na dużą skalę jest obecnie niezwykle rzadka. Przejście na coś z bardziej elastycznym systemem typowania danych nie jest warte kosztów ani ryzyka dla większości organizacji.

Analogia: może być oślepiające, że miejskie systemy metra działałyby lepiej (ciszej, szybciej, bardziej energooszczędnie) na węższym rozstawie szyn. Ale jak zamierzasz zmienić wszystkie szyny w systemie metra w Nowym Jorku, aby zrealizować te ulepszenia? Nie jesteś, więc optymalizujesz to, co masz.

Ogólnie rzecz biorąc, im bardziej szczegółowe informacje o bazie danych przechowujesz, tym bardziej może ona próbować zoptymalizować różne wskaźniki wydajności związane z tymi danymi, takie jak ilość miejsca do przydzielenia na dysku lub ilość pamięci do przydzielenia podczas pobierania .

Dlaczego średni tekst, długi tekst i tekst?

Nie jestem pewien, której bazy danych używasz, więc będę musiał zgadywać: zgaduję, że dwa z tych typów danych mają górne limity, jeden nie. Używanie typów danych dla tekstu, który ma górne limity, informuje bazę danych, ile miejsca będzie potrzebne na każdy rekord. Możliwe jest również, że niektóre bazy danych mogą mieć różne sposoby przechowywania dużego (być może nieograniczonego) tekstu w porównaniu z małym tekstem o stałej długości (może się to różnić w zależności od bazy danych, sprawdź w instrukcji, aby dowiedzieć się o twojej).

Dlaczego dziesiętny, zmiennoprzecinkowy i int?

Różne poziomy dokładności wymagają różnych ilości miejsca do przechowywania i nie każde użycie wymaga najwyższego stopnia precyzji. Na przykład zobacz tutaj: https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF50950

Oracle ma wiele różnych typów liczbowych o różnych wymaganiach dotyczących pamięci i różnych możliwościach pod względem poziomu precyzji i wielkości, które można przedstawić.

Do pewnego stopnia jest to historyczne.

Dawno, dawno temu dane tabelaryczne były przechowywane w plikach składających się z rekordów o stałej długości, z kolei złożonych ze wstępnie zdefiniowanych pól, tak że dane pole zawsze było tego samego typu i znajdowało się w tym samym miejscu w każdym rekordzie. Uczyniło to przetwarzanie wydajnym i ograniczyło złożoność kodowania.

Dodaj kilka indeksów do takiego pliku i masz początki relacyjnej bazy danych.

W miarę ewolucji relacyjnych baz danych zaczęto wprowadzać kolejne typy danych i opcje przechowywania, w tym pola tekstowe o zmiennej długości lub pola binarne. Ale wprowadziło to rekordy o zmiennej długości i złamało możliwość konsekwentnego lokalizowania rekordów za pomocą obliczeń lub pól za pomocą stałego przesunięcia. Bez względu na to, maszyny są dziś znacznie potężniejsze niż wtedy.

Czasami przydatne jest ustawienie określonego rozmiaru pola, aby pomóc w egzekwowaniu pewnej logiki biznesowej - powiedzmy 10 cyfr dla numeru telefonu w Ameryce Północnej. W większości przypadków jest to tylko odrobina dziedzictwa komputerowego.

Jeśli baza danych używa rekordów o stałej wielkości, dowolny rekord w bazie danych będzie nadal pasował w tej samej lokalizacji, nawet jeśli jego zawartość zostanie zmieniona. Natomiast jeśli baza danych próbuje przechowywać rekordy przy użyciu dokładnie takiej ilości pamięci, jakiej potrzebują ich pola, zmiana nazwy Emmy Smith na Emma Johnson może spowodować, że jej rekord będzie zbyt duży, aby zmieścił się w obecnej lokalizacji. Jeśli rekord zostanie przeniesiony w miejsce z wystarczającą ilością miejsca, każdy indeks, który śledzi jego lokalizację, musiałby zostać zaktualizowany, aby odzwierciedlić nową lokalizację.

Istnieje wiele sposobów zmniejszenia kosztów związanych z takimi aktualizacjami. Na przykład, jeśli system utrzymuje listę numerów rekordów i lokalizacji danych, lista ta będzie jedyną rzeczą, którą należy zaktualizować, jeśli rekord się przeniesie. Niestety, takie podejścia nadal wiążą się ze znacznymi kosztami (np. Utrzymywanie mapowania między numerami rekordów a lokalizacjami wymagałoby, że wyszukiwanie rekordów wymagałoby dodatkowego kroku w celu odzyskania danych związanych z danym numerem rekordu). Używanie rekordów o stałych rozmiarach może wydawać się nieefektywne, ale znacznie prostsze.

W przypadku wielu rzeczy, które robisz jako programista, nie musisz rozumieć, co się dzieje „pod maską”. Są jednak chwile, kiedy to pomaga.

Jaka jest korzyść z powiedzenia bazie danych „We wpisach do tej kolumny będzie tylko 256 bajtów danych tekstowych”. lub „Ta kolumna może zawierać wpisy do 16 777 215 bajtów”?

Jak podejrzewasz, powodem jest wydajność. Abstrakcje przeciekają . Takie zapytanie SELECT author FROM booksmoże być uruchamiane dość szybko, gdy znany jest rozmiar wszystkich pól w tabeli.

Jak mówi Joel,

Jak implementuje się relacyjna baza danych SELECT author FROM books? W relacyjnej bazie danych każdy wiersz w tabeli (np. Tabela książek) ma dokładnie tę samą długość w bajtach, a każde pole ma zawsze ustalone przesunięcie od początku wiersza. Na przykład, jeśli każdy rekord w tabeli książek ma długość 100 bajtów, a pole autora ma przesunięcie 23, to są autorzy zapisani w bajcie 23, 123, 223, 323 itd. Do jakiego kodu należy przejść następny rekord w wyniku tego zapytania? Zasadniczo jest to:

pointer += 100;

Jedna instrukcja CPU. Faaaaaaaaaast.

Często pracujesz na tyle daleko, że nie musisz się tym przejmować. Czy jako webowy programista PHP interesuje Cię, ile instrukcji procesora używa Twój kod? Przez większość czasu nie, nie bardzo. Ale czasem warto wiedzieć, z dwóch powodów: może wyjaśniać decyzje podejmowane przez biblioteki; a czasem trzeba dbać o szybkość we własnym kodzie.