Wiele osób zaczynających programowanie wykorzystują w swoich aplikacjach bazy danych. Przy projekcie struktury jak i strojeniu zapytań SQL zawsze stykają się z terminem indeksów i kluczy. Chciałbym w krótki sposób przedstawić i pokazać na przykładach wykorzystanie indeksów i kluczy w bazach danych i wyjaśnić dlaczego mają tak wielki wpływ na wydajność wykonania zapytań.
Klucze w bazie danych - zalążek teorii.
Głównymi zastosowaniami kluczy w bazie danych są:
- poprawienie wydajności działania bazy danych - w operacjach DQL (Data Query Language), takich jak SELECT, gdzie na podstawie kluczy optymalizator bazy danych może dostosować najbardziej optymalny plan wykonania zapytania, co wpływa na szybkość zwracania rekordów jak i obciążenia serwera bazodanowego
- zapewnienie automatycznej kontroli poprawności operacji przetwarzania danych - przy stosowaniu operacji DML (Data Manipulation Language), takich jak INSERT, UPDATE, DELETE, gdzie na poziomie bazy danych dane są weryfikowane czy aby relacja jest poprawna, czy dane się nie powtarzają, czy przedział jest odpowiedni, lub wywoływana jest dowolna metoda weryfikacji danych (trigger)
Indeksy
Indeks jest specjalną strukturą, wprowadzoną w celu przyspieszenia dostępu do danych. Indeks w bazie danych jest odzwierciedleniem spisu treści w książce.
Wykorzystuje się przy zapytaniach DQL (SELECT), które maja na celu wyszukiwanie odpowiednich wartości w bazie danych. Optymalizator najpierw przeszukuje indeks, który jest uporządkowany a następnie na podstawie indeksu odczytuje odpowiednie rekordy.
Definicja indeksu
CREATE INDEX name_index ON user (name);
Lub przez polecenei ALTER
ALTER TABLE user ADD INDEX (name);
Kiedy indeksy są zalecane?
Największy pożytek ze stosowania indeksów zależy w dużej mierze od selektywności kolumny lub kolumn do redukcji zbioru danych, innym przykładem zastosowania indeksów są kolumny które są często wymieniane w warunkach (WHERE) i złączeniach tabel.
Często w bazach danych spotyka się indeksy bazujące na funkcjach, przykładem może być funkcja UPPER(), dzięki której wydajność bazy danych polepsza się.
CREATE INDEX upper_index ON users (UPPER(name));
Indeks w operacjach DML powoduje spadek wydajności, ponieważ po każdej modyfikacji rekordu indeks jest odbudowywany.
Klucz podstawowy (Primary Key)
Zadaniem klucza podstawowego jest zapewnienie unikalności, zapewnienie że kolumny klucza nie zawierają wartości pustych, możliwości identyfikacji rekordu w bazie danych, jak i szybszego wyszukania interesującego nas rekordu.
Definicja klucza podstawowego
Klucz podstawowy można utworzyć podczas tworzenia tabeli lub modyfikacji tabeli.
Tworzenie tabeli (przykład dla MySQLa z AUTO_INCREMENT):
CREATE TABLE `user`
(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(100) NOT NULL
)
Modyfikacja tabeli:
ALTER TABLE `user` ADD PRIMARY KEY (`id`);
Klucz obcy (Foreign Key)
Integracja referencyjna jest jedną z podstawowych cech klucza obcego, w której każda wartość klucza obcego (niepusta) musi odpowiadać jednej z wartości klucza podstawowego (PRIMARY KEY) lub klucza unikalnego (UNIQUE) innej lub tej samej tabeli. Przykładem takim może być użytkownik, który należy do jakiegoś systemu.
Więzy integralności
Klucze obce wymuszają więzy integralności:
- REFERENCES - źródło klucza obcego (tabela i klucz podstawowy)
- ON DELETE, ON UPDATE - określenie akcji jaka należy podjąć w chwili usunięcia lub modyfikacji klucza podstawowego
- SET NULL zastąpienie wartości klucza obcego przez wartość NULL
- SET DEFAULT zastąpienie wartości klucza obcego przez wartość domyślnie ustawioną podczas definiowania struktury tabeli
- CASCADE modyfikacja pola klucza obcego na nową wartość pola klucza podstawowego lub usunięcie wszystkich rekordów zawierających odwołanie klucza obcego
- NO ACTION tylko w przypadku modyfikacji niezmienianie wartości klucza obcego
- RESTRICT nie dopuszczenie do zmiany
Do największych zalet więzów integralności zalicza się:
- łatwość deklaracji i modyfikacji
- jednakowe reguły dla wszystkich użytkowników i aplikacji
- natychmiastowa informacja zwrotna dla użytkownika (w przypadku błędu)
- elastyczność (włączane / wyłączane)
- bardzo dobra wydajność
Przykłady zastosowań więzów integralności.
Dla przykładu użyje 2 tabelek (”user” i “system”) - przykład dla bazy MySQL (tabele musza zastać utworzone jako InnoDB)
CREATE TABLE `user`
(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR( 100 ) NOT NULL
) ENGINE = innodb;
CREATE TABLE `system`
(
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR( 50 ) NOT NULL
) ENGINE = innodb;
Użytkownik musi zostać przypisany do systemu, więc wystarczy dodać dodatkowe pole do tabeli “user” w którym będzie wskazanie na tabele “system”
ALTER TABLE `user` ADD `system_id` INT NOT NULL;
ALTER TABLE `user` ADD INDEX (`system_id`);
Dodajemy kilka rekordów aby zobrazować przykłady wykorzystania relacji.
INSERT INTO `system` ( `id`, `name` ) VALUES ( 1, 'linux' );
INSERT INTO `system` ( `id`, `name` ) VALUES ( 2, 'windows' );
INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (1, 'SongoQ', 1);
INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (2, 'Marcin', 1);
INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (3, 'Kasia', 2);
INSERT INTO `user` (`id`, `name`, `system_id`) VALUES (4, 'Pawel', 2);
Złączenie tabel da rezultat:
SELECT * FROM `user`, `system` WHERE `user`.`system_id` = `system`.`id`;
+----+--------+-----------+----+---------+
| id | name | system_id | id | name |
+----+--------+-----------+----+---------+
| 1 | SongoQ | 1 | 1 | linux |
| 2 | Marcin | 1 | 1 | linux |
| 3 | Monika | 2 | 2 | windows |
| 4 | Pawel | 2 | 2 | windows |
+----+--------+-----------+----+---------+
Następnie przechodzimy do tworzenia relacji między tabelami.
Najczęściej wykorzystywane relacje w bazach danych
RESTRICT
ALTER TABLE `user` ADD CONSTRAINT `user_system_id_fk` FOREIGN KEY (`system_id`)
REFERENCES `system` (`id`);
Usunięcie rekordu z tabeli “system” do której odwołuje się rekord z tabeli “user” spowoduje błąd.
DELETE FROM `system` WHERE `id` = 1;
CASCADE - aktualizacja
ALTER TABLE `user` ADD CONSTRAINT `user_system_id_fk` FOREIGN KEY (`system_id`)
REFERENCES `system` (`id`) ON UPDATE CASCADE;
Zmiana warto??ci klucza podstawowego w tabeli “system” powoduje zmianę klucza obcego w tabeli “user”
UPDATE `system` SET `id` = 3 WHERE `id` = 1;
SELECT * FROM `user`, `system` WHERE `user`.`system_id` = `system`.`id`;
+----+--------+-----------+----+---------+
| id | name | system_id | id | name |
+----+--------+-----------+----+---------+
| 1 | SongoQ | 3 | 3 | linux |
| 2 | Marcin | 3 | 3 | linux |
| 3 | Monika | 2 | 2 | windows |
| 4 | Pawel | 2 | 2 | windows |
+----+--------+-----------+----+---------+
CASCADE - usuwanie
ALTER TABLE `user` ADD CONSTRAINT `user_system_id_fk` FOREIGN KEY (`system_id`)
REFERENCES `system` (`id`) ON DELETE CASCADE;
Usunięcie rekordu z tabeli “system” powoduje usunięcie rekorduw z tabeli “user”w której klucz obcy odpowiada kluczowi podstawowemu.
DELETE FROM `system` WHERE `id` = 3;
SELECT * FROM `user`, `system` WHERE `user`.`system_id` = `system`.`id`;
+----+--------+-----------+----+---------+
| id | name | system_id | id | name |
+----+--------+-----------+----+---------+
| 3 | Monika | 2 | 2 | windows |
| 4 | Pawel | 2 | 2 | windows |
+----+--------+-----------+----+---------+
Oczywiście ON DELETE i ON UPDATE można stosować razem.
Indeks unikalny (UNIQUE)
Indeks unikalny służy do wymuszenia na polu wartości unikalnych, czyli żadna wartość w nim wystąpująca nie może sie powtarzać. Indeks unikalny charakteryzuje się dobrą selektywnośca.
ALTER TABLE `users` ADD UNIQUE (`name`)
Indeks złożony
Indeksy złożone składają sie z wielu kolumn, gdzie kolumny mogą być zdefiniowane w dowolnej kolejności. Indeksy złożone są użyteczne, wtedy kiedy w zapytaniu SELECT w warunku WHERE występuje wiecej niż jedno odwołanie do pol tabeli.
ALTER TABLE `user` ADD UNIQUE `name` (`name`, `surname`)
Mam nadzieje, że ten tekst niektórym przybliżył pojęcia związane z kluczami i indeksami. Odnośnie szczegłów i większej liczby przykładów polecam ksiżeczkę z podstawami relacyjnych baz danych. Wydawnictwo Helion.pl ma kilka dobrych pozycji na ten temat.
