Rozwój oprogramowania Ruby on Rails. Indeksy v2

Podczas pracy z frameworkiem Ruby on Rails zazwyczaj mamy do czynienia z relacyjnymi bazami danych, takimi jak MySQL czy PostgreSQL. Podczas definiowania migracji za pomocą Active Record Migrations natrafiamy na tak zwane indeksy, ale początkujący często nie do końca rozumieją indeksy i jakie korzyści przynoszą.

W tym poście chciałbym wyjaśnić, czym są indeksy, do czego służą i przedstawić kilka dobrych praktyk dotyczących ich stosowania.

Baza danych

Istnieje wiele silników baz danych, a jednymi z najpopularniejszych są wspomniane wcześniej MySQL, PostgreSQL, Oracle czy Microsoft SQL Server. Wszystkie one są relacyjnymi bazami danych, co oznacza, że wszystkie dane są ze sobą powiązane i przechowywane w tabelach. Każdy wiersz tabeli nazywany jest rekordem, a każdy z nich ma swój unikalny identyfikator (id). Ranking najpopularniejszych silników baz danych można sprawdzić na stronie https://db-engines.com/en/ranking. Znajdziesz tam również nierelacyjne bazy danych, takie jak MongoDB.

Tworzenie indeksu

Tabele w naszych bazach danych mogą mieć od kilku do kilkudziesięciu - w skrajnych przypadkach nawet do kilkuset - kolumn. Należy pamiętać, że każda tabela może mieć nieograniczoną liczbę wierszy. Liczba ta nie wynika bezpośrednio ze struktury bazy danych i zawsze powinniśmy zakładać, że liczba rekordów będzie sukcesywnie wzrastać, a co za tym idzie, nasza baza danych będzie się rozrastać. Początkowe założenia i zapytania pisane w istniejących aplikacjach mogą być świetne dla małej lub średniej liczby rekordów, ale z czasem, gdy danych przybywa, komunikacja aplikacji z bazą danych przestaje być wydajna.

Rolą programisty jest pisanie zapytań w celu pobrania pewnych danych z tabeli lub tabel, ale optymalny sposób przetwarzania zapytania zależy od silnika bazy danych. Należy pamiętać, że silniki baz danych ładują dane z dysku do pamięci, a następnie je skanują. Oznacza to, że jeśli wielu użytkowników wykonuje złożone operacje w tym samym czasie, kilku z nich będzie musiało czekać na swoją kolej z powodu braku zasobów do realizacji swoich wyszukiwań. Dlatego tak ważne są odpowiednie indeksy.

Wiki: Indeks - struktura danych, która zwiększa szybkość wykonywania operacji wyszukiwania w tabeli.

Dla każdego indeksu musimy zdefiniować klucze (dla jednej lub wielu kolumn), które będą używane do wyszukiwania rekordów w tabeli. Dane w indeksie będą sortowane według wcześniej zdefiniowanego klucza, co znacznie przyspieszy wyszukiwanie danych w tabeli. Najprostszym przykładem z życia codziennego jest książka telefoniczna, w której osoby posortowane są według imienia i nazwiska. Można powiedzieć, że naszym indeksem w tym przypadku będzie imię i nazwisko.

Jak wybrać najlepszy klucz indeksowy? Nie jest to trudne - wystarczy pamiętać o kilku zasadach. Utwórz indeks na podstawie kolumn, które:

- będzie często używany w naszych zapytaniach (WHERE),

- w połączeniu ze sobą dają unikalną wartość (tj. taką, która wskaże dokładnie jeden wiersz),

- będą używane jako tak zwane kolumny łączące (JOIN),

- podają najbardziej selektywne klucze, tj. te, które zwracają najmniejszą liczbę wierszy podczas pisania zapytania.

Jeśli wiemy już, które klucze będą optymalne dla naszej tabeli, możemy również zadać sobie pytanie, ile indeksów potrzebujemy. W tym przypadku najlepiej już na etapie projektowania znać zapytania, które będą odwoływać się do naszej tabeli.

Twórzmy indeksy dla konkretnych zapytań, które będą się pojawiać, ale nie piszmy ich dla każdej kolumny. Indeksy, podobnie jak tabele, muszą być gdzieś przechowywane, więc tworząc tabele z indeksem dla każdej kolumny, musimy liczyć się z tym, że ilość wykorzystywanego miejsca może znacząco wzrosnąć.

Tworzenie unikalnego indeksu

Kolejną kwestią, o której musimy pomyśleć, jest unikalność. Warto poświęcić dodatkowe pięć minut na zastanowienie się, czy nasz indeks jest naprawdę unikalny. W ten sposób mówimy optymalizatorowi zapytań, że nie musi oczekiwać duplikatów w zapytaniu. Na przykład adresy e-mail:

frozenstringliteral: true

class CreateUsers < ActiveRecord::Migration[6.0]
def change
createtable :users do |t|
t.string :email, null: false
end
addindex :users, :email, unique: true
end
end

Na przykładzie silnika PostgreSQL pokażę różnicę w szybkości zapytań dla kolumny email z indeksem unikalnym i bez indeksu.

1. Można użyć próbki kod na własnej bazie danych, aby móc przetestować poniższy przykład. Najpierw utwórzmy pustą tabelę z jedną kolumną:

 CREATE TABLE users (
   email varchar
 );

2. Wygenerujmy 10 000 rekordów dla testu:

 DO $
   BEGIN FOR i IN 1..10000 LOOP
     INSERT INTO users values((select 'user' || i || '@example.com'));
   END LOOP; END;
 $;

Użyjemy EXPLAIN ANALYZE, aby sprawdzić, jak szybko nasze zapytanie zostanie przetworzone, gdy chcemy znaleźć konkretnego użytkownika w bazie danych.

EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Nasze zapytanie wymusiło iterację po całej tabeli w poszukiwaniu interesującego nas rekordu.

Proces ten nazywany jest skanowaniem sekwencyjnym. W tym przypadku najlepszym sposobem jest odczytanie całej tabeli i odfiltrowanie poszczególnych wierszy.

PostgreSQL odfiltruje niepotrzebne wiersze i po prostu zwróci te, które nas interesują. Jest to naprawdę najlepsza rzecz do zrobienia w tym przypadku. Skanowanie sekwencyjne nie zawsze jest złe, istnieją przypadki, w których skanowanie sekwencyjne jest idealne.

4. Teraz nadszedł czas, aby sprawdzić zapytanie już wykonane na tabeli, która ma INDEX UNIQUE. Ustawmy indeks i wykonajmy zapytanie.

 EATE UNIQUE INDEX index_email on users(email);
 EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Tym razem PostgreSQL skorzystał ze skanowania indeksu, ponieważ wszystkie potrzebne kolumny znajdują się już w indeksie.

Wybranie tylko kilku wierszy będzie bardzo wydajne podczas korzystania z indeksu. Jeśli jednak wybranych zostanie więcej danych, skanowanie indeksu i tabeli będzie zbyt czasochłonne.

Podsumowanie

Jak widać, czas wykonania zapytania na kolumnie z indeksem jest znacznie krótszy (w pokazanym przykładzie jest to spadek z 1,267 ms do 0,111 ms, a więc aż o 91,24%!). Najważniejszą różnicą jest sposób, w jaki PostgreSQL wyszukuje interesujący nas rekord. W pierwszym przypadku silnik bazy danych musiał przeszukać całą tabelę w poszukiwaniu interesującego nas rekordu. W drugim natomiast struktura indeksu jest posortowana i unikalna, stąd silnik wiedział gdzie znajduje się rekord, co znacznie przyspieszyło czas przetwarzania zapytania.

W przypadku dużych baz danych i bardzo złożonych zapytań, poprawnie ustawione indeksy mogą znacząco przyspieszyć pracę aplikacji bez konieczności zwiększania szybkości maszyny, na której przeszukujemy bazę danych.

Warto pamiętać, że tworzenie indeksów na każdej kolumnie nie jest dobrą praktyką. Utworzone indeksy przyspieszą pracę optymalizatora przy wyszukiwaniu interesujących nas danych, ale jednocześnie spowolnią wstawianie nowych i aktualizowanie istniejących.

Czytaj więcej:

– Potrzebujesz użyć popularnych frameworków JS w swojej aplikacji Rails? Stimulus.js może być alternatywą

– Czas na nową rzeczywistość. Miesiąc temu rozpoczęła się era pracy zdalnej

– Tworzenie aplikacji internetowych: Dlaczego warto wybrać technologię Ruby on Rails?