Jak typy danych i indeksy wpływają na prędkość działania i wydajność bazy danych

To w jakim formacie przechowujesz dane w bazie danych ma znaczenie. Ogromne. Mają też znaczenie indeksy. Właściwe połaczenie typów danych z indeksami jest kluczowe dla wydajności całej bazy danych. W testach poniżej mamy dwie proste tabele wypełnione pseudolosowymi danymi. Testy pokazują jak zmienia się prędkość wykonania zapytań w zależności od typów danych i obecności lub braku indeksów.

Testowe tabele

Utworzymy dwie proste tabele do testow; 'demo_table1' i 'demo_table2'. Obie tabele maja na celowo ustawiony typ danych varchar dla kolumny 'random_number' by zademonstrować różnice czasie wykonania zapytania dla różnych typów danych.

CREATE TABLE demo_table1 (
random_number VARCHAR(8),
random_text VARCHAR(100)
);

CREATE TABLE demo_table2 (
random_number VARCHAR(8),
random_text VARCHAR(100)
);

Tabela 'demo_table2':

Test numer 1

Select z dwóch tabel z pomocą LEFT JOIN połączonych kluczem 'random_number' w obu tabelach. Kolumna 'random_number' przechowująca liczby z typem danych varchar, bez indeksów.

select
dt1.random_number
from demo_table1 dt1
join demo_table2 dt2
on dt1.random_number = dt2.random_number

Czas wykonania: 6.3 sekundy

Dodajemy indeksy do tabel

Dodanie indeksów do tabel prawdopodobnie znacząco przyśpieszy przygotwanie danych przez bazę danych. Dodajmy zatem indeksy dla kolumny 'random_number' w obu tabelach:

CREATE INDEX idx_random_number ON demo_table1 (random_number);
CREATE INDEX idx_random_number ON demo_table2 (random_number);

select
dt1.random_number
from demo_table1 dt1
join demo_table2 dt2
on dt1.random_number = dt2.random_number
where dt1.random_number <= 100 and dt2.random_number < 56798

Zmiana typu danych dla kolumny

Zmieniamy typ danych kolumny random_number na Integer dla obu tabel:

ALTER TABLE demo_table1
MODIFY COLUMN random_number INT;

ALTER TABLE demo_table1
MODIFY COLUMN random_number INT;

Usuwamy też indeksy:

DROP INDEX idx_random_number ON demo_table1 (random_number);
DROP INDEX idx_random_number ON demo_table2 (random_number);

Test numer 4

Zapytanie jak wcześniej - tym razem z typem danych Integer dla kolumny 'random_number' - bez indeksów

select
dt1.random_number
from demo_table1 dt1
join demo_table2 dt2
on dt1.random_number = dt2.random_number

Czas wykonania: 4.6 sekundy 

Dodajemy ponownie indeksy do tabel

Dodajmy zatem indeksy dla kolumny 'random_number' w obu tabelach:

CREATE INDEX idx_random_number ON demo_table1 (random_number);
CREATE INDEX idx_random_number ON demo_table2 (random_number);

Test numer 5

Ponowne wykonanie zapytania:

select
dt1.random_number
from demo_table1 dt1
join demo_table2 dt2
on dt1.random_number = dt2.random_number

Czas wykonania: 0.0 sekundy (milisekundy)

select
dt1.random_number
from demo_table1 dt1
join demo_table2 dt2
on dt1.random_number = dt2.random_number
where dt1.random_number <= 100 and dt2.random_number < 56798

Wnioski

Użycie właściwych typów danych do przechowywania informacji ma znaczenie nawet jeśli nie używasz indeksów dla kolumn w tabelach. Zastosowanie indeksów jest kluczowe dla odpowiedniej wydajności bazy danych. Poniżej podsumowanie rezultatów testów. Weź pod uwagę że testy zostały wykonane na trywialnych ilościach danych i bardzo prostych zapytaniach. W realnym środowisku produkcyjnym różnice byłby ogromne.