Indeksy: liczba całkowita vs wydajność łańcucha, jeśli liczba węzłów jest taka sama

Tworzę aplikację w Ruby on Rails z bazą danych PostgreSQL (9.4). W moim przypadku użycia kolumny w tabelach będą bardzo często wyszukiwane, ponieważ cały punkt aplikacji szuka bardzo specyficznych atrybutów w modelu.

Obecnie jestem podejmowaniu decyzji, czy użyć integertypu lub po prostu użyć typowy typu string (np character varying(255), który jest domyślny w Rails ) do kolumn, jak nie jestem pewien, co różnica wydajności będzie na indeksie.

Te kolumny są wyliczeniami . Mają stały rozmiar dla liczby możliwych wartości, jakie mogą mieć. Większość długości wyliczeń nie przekracza 5, co oznacza, że indeks byłby mniej więcej ustalony przez cały okres użytkowania aplikacji ; dlatego indeksy liczb całkowitych i łańcuchów byłyby identyczne pod względem liczby węzłów.

Jednak ciąg, który byłby indeksowany, może mieć długość około 20 znaków, co w pamięci jest około 5 razy dłuższe niż liczba całkowita (jeśli liczba całkowita wynosi 4 bajty, a ciągi są czystymi ASCII o 1 bajcie na znak, to jest to zachowane). Nie wiem, w jaki sposób silniki baz danych wykonują wyszukiwania indeksów, ale jeśli trzeba „zeskanować” ciąg znaków, dopóki nie będzie dokładnie pasował , to w gruncie rzeczy oznacza to, że wyszukiwanie ciągu będzie 5 razy wolniejsze niż wyszukiwanie liczb całkowitych; „skanowanie” do momentu dopasowania liczby całkowitej będzie wynosić 4 bajty zamiast 20. Oto, co sobie wyobrażam:

Wartość wyszukiwania to (liczba całkowita) 4:

skanowanie ............................ ZNALEZIONO | uzyskiwanie rekordów ... | BYTE_1 | BYTE_2 | BYTE_3 | BYTE_4 | BYTE_5 | BYTE_6 | BYTE_7 | BYTE_8 | ... |

Wartość wyszukiwania to (ciąg) „some_val” (8 bajtów):

łów................................................. .................................... ZNALEZIONO | uzyskiwanie rekordów ... | BYTE_1 | BYTE_2 | BYTE_3 | BYTE_4 | BYTE_5 | BYTE_6 | BYTE_7 | BYTE_8 | ... |

Mam nadzieję, że to ma sens. Zasadniczo, ponieważ liczba całkowita zajmuje mniej miejsca, można ją „dopasować” szybciej niż jej ciąg znaków. Być może jest to całkowicie błędne przypuszczenie, ale nie jestem ekspertem, dlatego właśnie was pytam! Przypuszczam, że ta odpowiedź, którą właśnie znalazłem, wydaje się potwierdzać moją hipotezę, ale chcę mieć pewność.

Liczba możliwych wartości w kolumnie nie zmieniłaby się przy użyciu żadnej z nich, więc sam indeks nie zmieniłby się (chyba że dodałem nową wartość do wyliczenia). Czy w takim przypadku wystąpiłaby różnica w wydajności przy użyciu integerlub varchar(255), czy też użycie typu liczby całkowitej ma większy sens?

Pytam dlatego, że enumtyp Railsów odwzorowuje liczby całkowite na klucze łańcuchowe, ale nie mają one być kolumnami skierowanymi do użytkownika. Zasadniczo nie można zweryfikować, czy wartość wyliczenia jest poprawna, ponieważ niepoprawna wartość spowoduje, że ArgumentErrorprzed uruchomieniem jakichkolwiek sprawdzeń poprawności. Użycie stringtypu pozwoliłoby na sprawdzanie poprawności, ale jeśli istnieje koszt wydajności, wolałbym po prostu rozwikłać problem sprawdzania poprawności.

Krótka odpowiedź: integerjest szybszy niż varcharlub textw każdym aspekcie. Nie będzie to miało większego znaczenia dla małych stolików i / lub krótkich klawiszy. Różnica rośnie wraz z długością kluczy i liczbą rzędów.

string ... długość 20 znaków, która w pamięci jest około 5 razy większa od liczby całkowitej (jeśli liczba całkowita ma 4 bajty, a ciągi są czystym ASCII z 1 bajtem na znak, to jest to)

Mówiąc ściślej, typy znaków ( textlub varchar) zajmują dokładnie 21 bajtów na 20 znaków ASCII na dysku i 23 bajty w pamięci RAM. Szczegółowa ocena:

• Co to jest narzut dla varchar (n)?

Ważne: COLLATIONreguły mogą spowodować, że sortowanie danych znakowych będzie droższe - w przeciwieństwie do liczbowych typów danych:

• Gdyby wszystkie wartości miały 36 znaków, wyszukiwanie indeksu byłoby zauważalnie szybsze przy użyciu char vs varchar

Rozmiar indeksu jest prawdopodobnie odpowiedzialny za udział lwa w różnicach wydajności w większości przypadków. Rozważmy narzut na krotkę indeksu (w zasadzie taki sam jak dla tabeli): 4 bajty dla wskaźnika pozycji i 24 bajty dla nagłówka krotki. Tak więc krotka indeksu dla integermiałaby 36 bajtów (w tym 4 bajty wyrównania dopełniania ), a dla varchar(20)20 znaków ASCII będzie to 52 bajty (w tym również dopełnianie). Detale:

• Konfigurowanie PostgreSQL pod kątem wydajności odczytu

Cała teoria na bok: najlepiej po prostu przetestować:

• Zmierz rozmiar wiersza tabeli PostgreSQL

Postgres 9.5 wprowadził optymalizację do sortowania długich ciągów danych znakowych (słowo kluczowe „skróty klawiaturowe” ). Jednak błąd w niektórych funkcjach biblioteki C w Linuksie zmusił projekt do wyłączenia tej funkcji w przypadku zestawień innych niż C w Postgres 9.5.2. Szczegóły w informacjach o wydaniu.

Jeśli jednak faktycznie używasz enumtypów Postgres , większość z tych rozważań jest nieistotna, ponieważ i tak są one implementowane z integerwartościami wewnętrznie. Instrukcja:

enumWartość zajmuje cztery bajty na dysku.

Poza tym: ma varchar(255)zastosowanie w przypadku wczesnych wersji SQL Server, które mogą wykorzystywać bardziej wydajny typ danych wewnętrznie do limitu 255 znaków. Ale ograniczenie długości nieparzystej do 255 znaków nie ma żadnego specjalnego wpływu na wydajność w Postgres.