Podstawy
4 godz. 8 min · Bazy Danych · Full-stack i Programowanie
Pierwszy rozdział wprowadza w historię powstania języka. Wspomnimy o standardach, wykorzystywanych wersjach i podzbiorach SQL’a. Zaraz potem wskoczymy w pierwsze poważne operacje, które pozwolą na pełny cykl tworzenia, zasilania bazy danych i wyciągnięcia z niej wybranych informacji. Rozwikłamy też tajemniczy akronim - CRUD.
Skoro potrafimy już tworzyć bazę i tabele, w które możemy wstawić nowe wiersze, dowiemy się więcej o bardzo ważnej funkcji bazy danych: weryfikacji danych. Zostaną zaprezentowane mechanizmy i techniki, które pomogą utrzymać spójność danych.
W kolejnych lekcjach znajduje się opis serca SQL’a, czyli polecenia wybierającego dane i możliwości i opcji z nim związanych. Od przekształcenia bazy danych w prosty kalkulator, aż do warunków filtrowania wybieranych danych (klauzulą WHERE), sortowania ich (klauzulą ORDER BY), czy ograniczania dużych zestawów wynikowych - w tym wsparcia do paginacji.
SQL jest językiem obsługi relacyjnych baz danych. Dlatego najwyższy czas, aby wspomnieć o relacjach i tym w jaki sposób rozszerzają one możliwości wyszukiwania danych oraz pozwalają zobrazować powiązania między danymi w bazie. Dowiesz się o różnych rodzajach połączeń między tabelami i jak te połączenia (zwane JOIN’ami) pozwalają wybrać to, czego potrzebujesz.
Kolejny rozdział dotyczy grupowania danych (wykorzystując klauzulę GROUP BY) i związanych z tym nowymi możliwościami. Dowiesz się tutaj też w jaki sposób agregować pogrupowane dane i jak takie zagregowane dane filtrować.
Przekonasz się, w jaki sposób można zapamiętywać trudne lub często wykonywane zapytania, wykorzystując do tego widoki (VIEWS) i jak można poprawić wydajność pobierania z nich danych. Przykładowo zastosujemy widoki zmaterializowane (MATERIALISED VIEWS). W tym rozdziale przećwiczysz też możliwość zapisywania danych przez widoki.
Z baz danych korzysta zwykle wielu użytkowników. Aby operacje, które wykonują na wspólnej bazie nie spowodowały nieoczekiwanych konsekwencji, wykorzystuje się mechanizm transakcji. W tym rozdziale wytłumaczymy Ci w jaki sposób użytkownicy mogą modyfikować równolegle te same dane. Tutaj też dowiesz się o poziomach izolacji transakcji i jak używać ich w różnych sytuacjach bazodanowych.
Przy pracy z większą ilością danych, bardzo szybko doświadczysz spowolnienia wykonywania zapytań. O tym jak je przyspieszyć dowiesz się właśnie w tym rozdziale. Zwiększanie wydajności zapytań jest tematem rozległym i zaawansowanym. Tutaj będziesz miał szansę poznać podstawy, w tym - po co są i jak używać indeksów, w jaki sposób analizować jak zapytania są wykonywane i jakie jeszcze są metody wydajniejszego odczytywania danych.
Kurs jest dla osób początkujących, rozpoczynających przygodę z bazami danych. Nie wymaga się znajomości SQL'a ani zaawansowanej wiedzy dotyczącej baz danych. Warto wiedzieć co to są relacyjne bazy danych. Dobrze mieć dostęp do ulubionego silnika bazy danych, ale nie jest to wymagane.
W lekcji wprowadzającej stworzyłeś dwóch użytkowników dzięki
temu teraz mogę ci przedstawić w jaki sposób można wykorzystać transakcję
pokażę ci jak to zrobić na naszej bazie żeby
zademonstrować działanie transakcji otworzę sobie dwa okienka do
wpisywania zapytań jedno powiązane z użytkownikiem
postgres drugie powiązane z użytkownikiem
other w ten sposób będą mogli niezależnie wykonywać
zapytania i działać w niezależnych
transakcjach na poprzedniej lekcji wspomniałem
o poziomach izolacji zobaczmy jak działają przy
operacjach transakcyjnych zaczniemy od domyślnego trybu
read commited tryb read uncommitted pierwszy
o którym mówiłem jest na tyle niepraktyczny i niebezpieczny
że go pomijam nie jest generalnie stosowany w operacjach bazodanowych
jeszcze tylko żeby użytkownik other mógł modyfikować
coś w tabeli equipment musimy mu dać uprawnienia może
nie są mu potrzebne wszystkie ale dla uproszczenia damy wszystkie
już będzie mógł na nie działać
każda transakcja tak jak wspominałem zaczyna
się od słówka begin a teraz będziemy ustawiali
różne poziomy izolacji robi się to
poleceniem set transaction isolation level i
teraz poziom jaki chcemy a
następnie napiszemy zapytanie które
będzie wyrównywało wartość obrony dla tych
elementów ekwipunku które mają ją za niską powiedzmy
że chcemy do każdego elementu ekwipunku który ma w ogóle ma jakąś
wartość obrony dorzucić też wartość średnią ze wszystkich
takich elementów powiedzmy że podzieloną przez 2 tak znormalizowaną
żeby nie była to zbyt duża wartość polecam ci zapauzować
i pomyśleć jakby takie zapytanie wyglądało a jeżeli
masz pomysł albo nie masz pomysłu to odpauzuj i
zobacz jak takie zapytanie wygląda na
początek uruchommy tą transakcję i zobaczymy w ogóle jak wyglądają
te elementy ekwipunków które mają wartość obrony
a następnie tak będzie to zapytanie wyglądało
zauważ że ustawiamy wartość kolumny armor
na bieżącą wartość plus średnia podzielona na 2
tam gdzie ma jakąś wartość w ten sposób można
aktualizować wartość kolumny uwzględniając wartości
w innych wierszach tabeli tutaj ta transakcja
jest jeszcze nie zamknięta a w międzyczasie pójdziemy do
użytkownika other o i też zaczniemy nową transakcję
też ustawimy sobie tryb izolacji
odpowiadniej tutaj troszkę inny będzie kwerend zaktualizujemy
te elementy które mają wysoki współczynnik obrony i po prostu go
troszkę zwiększymy zanim to jeszcze uruchomimy to też
zobaczymy jak wygląda ta tabela od strony tego użytkownika tak
tutaj nic się na razie nie zmieniło natomiast dla
użytkownika postgres na połączeniu lokalnym odpalamy ten
ten update i teraz możemy zobaczyć o są nowe wartości tak
wszystko jest trochę wyższa wartość natomiast tutaj nic się
nie zmieniło tak ta transakcja widzi stan bazy jaki był w
momencie kiedy została rozpoczęta zmiany które następują
w innej transakcji nie są widziane w pozostałych
transakcjach równoległych no dobrze teraz będziemy w tej transakcji spróbowali
zaktualizować wartość obrony dla wszystkich elementów
ekwipunku które mają wartość mniejszą niż 20
czyli to będą także te elementy które
zostały tutaj zaktualizowane tak czyli te
dwie te dwa zapytania będą chciały działać na tych samych wierszach
na tych na tych samych rekordach co się stanie jak spróbuję
uruchomić ten update on zostanie wstrzymany do
momentu kiedy poprzednia transakcja nie zostanie zatwierdzona
albo odrzucona jeżeli ją zatwierdzimy
to
wtedy nastąpi ta aktualizacja i możemy
zobaczyć że nasze trzy punkty
zostały dodane do wartości tak
tutaj są wartości po dodaniu tej średniej a w
tej drugiej transakcji widać od razu zmodyfikowane
o 3 wartości całego tego zestawu
uzbrojenia teraz możemy zatwierdzić już
te nasze zmiany albo je cofnąć jeżeli chcemy więc może cofniemy
je poleceniem rollback co będzie oznaczało
że nie chcemy ich jednak zachowywać stan
teraz bazy jak sobie go podejrzymy kopiuje
te polecenia żeby było widać w jakiej kolejności je
wykonywałem jest dokładnie taki jak na koniec
pierwszej transakcji czyli po prostu są te wartości lekko
zwiększone i tyle nasze nasze plus 3 zostało odrzucone
i nie jest tutaj uznane tak
jak widziałeś w trybie izolacji read commited
transakcja aktualizująca te same dane po prostu
czekają na siebie nawzajem i na już zaktualizowanych danych
nakładają swoje swoje zmiany zobaczmy
teraz jak sobie poradzi baza z wyższym trybem
czyli repeatable read tym razem nasz
użytkownik postgres będzie chciał zmniejszyć wartość obrony o 3 uruchommy
tą transakcje proszę bardzo wszystko
jest zmniejszone o 3 natomiast przejdźmy do drugiej
do drugiego połączenia do drugiego uzytkownika on
też chciałby coś takiego zrobić możemy nawet skopiować
sobie update ale on chciałby o
5 zł zmniejszyć nie o 3 no i teraz co się stanie jeżeli ja wykonam
to zapytanie też będzie
czekać na zakończenie tamtej transakcji powiedzmy że stwierdzimy że
okej commitujemy chcemy takie
wartości zmniejszone utrzymać i teraz będziemy chcieli
zacommitować te zmiany czyli to zmniejszenia o trzy tutaj
zmniejszenia o 5 czeka aż transakcja się zakończy zatwierdzamy
pięknie jest commit a co tam po drugiej stronie błąd
nie można serializować dostępu z powodu równoczesnej aktualizacji w
tym trybie w momencie kiedy druga transakcja modyfikuje
te same wiersze po prostu zgłaszany jest błąd i
trzeba wtedy ponowić najwyżej ten tą aktualizację
najlepiej sprawdzając te wartości jeszcze raz tak
bo jeżeli chcieliśmy zmniejszyć zmodyfikować
wartości na podstawie jakichś obliczeń to
w przypadku pierwszego poziomu read commited te obliczenia
po prostu były błędne dlatego że tak jak pamiętasz transakcja
widzi stan bazy w momencie uruchomienia transakcji w momencie
kiedy pierwsza transakcja coś zmieni druga transakcja tego
nie widzi i może mogą być jej obliczenia
błędne tak bo bazuje na starym starych danych
w tym wypadku nie ma takiego zagrożenia dlatego że po prostu baza
nie pozwoli na zaktualizowanie tych samych wierszy
z drugiej transakcji ostatni
tryb serializable ostatni poziom izolacji
serializable jest prawie identyczny do repeatable read
nie pozwala dwóm transakcjom
które zmieniają te same dane zostać
uruchomionymi zatwierdzonymi natomiast jest troszkę
bardziej restrykcyjne od repeatable read pozwól że
troszkę inny przykład tutaj zademonstruje ponieważ
tamten którym wcześniej używaliśmy nie
pokazałby pełnych możliwości trybu poziomu serializable w
tej chwili nasz główny użytkownik postgres chce wstawić
wartość zależną od innych wartości w tabeli w
dodatku ta wartość będzie
wpływała na wynik zapytania drugiej
transakcji drugiego użytkownika tym razem też
zaczniemy od poziomu serializacji a później pokażę
jak to wygląda na poziomie repeatable read tutaj
mamy już początek transakcji ustawiony odpowiedni poziom
a zapytanie wpisuje nowy rekord który
ma wartość zależną od średniej wartości innych
rekordów w ten sposób jak widzisz w tym zapytaniu po
pierwsze jest to inna forma insert into tu
podaję jakie kolumny będą aktualizowane
do jakich kolumn będą wartości przypisane drugą częścią
nie jest klauzula values tylko po prostu kwerenda która
będzie zwracała dokładnie tyle wartości ile spodziewa
się insert czyli 5 i tutaj
są po prostu częściowo zahardkodowane to pod broń natomiast
wartość obrażeń jest brana jako
średnia pozostałych wartości obrażeń jeden topór
i od razu przypisany jest dla bohatera o
identyfikatorze 2 co jest ważne dlatego że średnia
liczona jest dla bohaterów
których id jest większe niż 4 to jest
o tyle istotne że w drugim zapytaniu będziemy właśnie
dodawali broń bohaterowi o
id większą niż 4 tak czyli to zapytanie
bazuje na danych które
będą zmieniane przez drugą
transakcję to wpiszmy
uruchommy teraz tego inserta ciach możemy
sprawdzić co tutaj się pojawiło i proszę
na końcu pojawił się topór z wartością 10
i przypisany do bohatera numer 2 teraz przejdźmy do drugiej
do drugiej transakcji uruchommy
ją nie możemy jej uruchomić dlatego że nie
zakończyliśmy transakcji w której nastąpił błąd musimy
ją zakończyć najlepiej ją cofnąć
tak rollback ciach i
teraz możemy zacząć nową transakcję do
tej transakcji też dodamy nowy element ekwipunku ale
dla odmiany będzie to łuk i tak jak wspomniałem jest przypisany do
innego bohatera natomiast
średnia zadawanych obrażeń jest liczona dla bohaterów
i id mniejszym niż 4 mniejszym równym 4 dlatego tu jest łuk dodany
wartość jest 8 i
przypisany jest do użytkownika nr 5 wracamy
w takim razie do naszej pierwszej transakcji
chcemy ją zapisać chcemy dodać topór topór
się dodał możemy to sprawdzić że na pewno to
zostało już zatwierdzone jest
topór a teraz co się stanie tutaj w
tej transakcji spróbujemy ją
zacommitować a to błąd dlatego
że nie można serializować dostępu ze względu na zależności odczytu
zapisu między transakcjami to brzmi bardzo enigmatycznie
chodzi o to że gdybyśmy chcieli jedną z tych transakcji
uruchomić najpierw a później drugą to kolejność będzie
grała rolę jeżeli chodzi o ich wyniki tak jeżeli pierwszą
uruchomimy najpierw a później drugą to
wartość średnich obrażeń będzie
inna niż jak najpierw uruchomimy pierwszą a później drugą ponieważ
ta kolejność ma znaczenie to na poziomie
serializable jest to niedopuszczalne na
taką niejednoznaczność ten tryb nie może pozwolić więc zgłasza
błąd i ta transakcja nie dojdzie do do skutku trzeba ją cofnąć
transakcje na poziomie serializable są tylko dozwolone jeżeli
każda z nich może być uruchomiana niezależnie i
jedna nie będzie miała wpływu na drugą jeżeli jedna
by aktualizowała nawet gdyby dodawała wartość
która by opierała się na średnich obrażeniach ekwipunku dla
bohaterów powiedzmy 1 2 a druga transakcja
dodawałaby rekord który bazowałby na średniej
wartości zadawanych obrażeń dla ekwipunku
bohaterów nie wiem 4 i 5 to
wszystko jedno w jakiej kolejności byłyby one uruchamiane zawsze
wynik byłby ten sam czyli te transakcje byłyby
serializowane tak stąd właśnie poziom serializable
że można jedną po drugiej uruchomić w dowolnej kolejności w
ramach zadań do przepracowania zadanie teoretyczne
załóżmy że masz aplikację w której użytkownicy mogą zapisywać swoje
profile to tylko imię nazwisko email płać bardzo
proste dane pomyśl jaki poziom izolacji
jest wystarczający dla operacji zapisu tych danych
do bazy taki poziom który zapewni że te dane
będą spójne a z drugiej strony nie
będzie bardzo blokował równoległych operacji
zapisu jeżeli chcesz się upewnić że dobrze odpowiedziałeś to na
początku następnej lekcji będę omawiał rozwiązanie do
tego zadania do usłyszenia
Interakcje transakcji - tryb read committed - użytkownik postgres - część 1 · 3 min
-- umożliwiamy modyfikowanie tabeli equipment drugiemu użytkownikowi
GRANT ALL ON TABLE equipment TO other;
-- rozpoczynamy transakcję
BEGIN;
-- ustawiamy bazowy poziom izolacji (read commited)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- odpalamy aktualizację rekordów, które mają niedużą wartość obrony.
-- Wartość zmiany obrony zależna jest od WSZYSTKICH rekordów z niepustą obroną
UPDATE equipment SET armor = armor + (SELECT AVG(armor)/2 FROM equipment WHERE armor IS NOT NULL)
WHERE armor < 20;Interakcje transakcji - tryb read committed - użytkownik other - część 1 · 4 min
-- rozpoczynamy transakcję
BEGIN;
-- ustawiamy bazowy poziom izolacji (read commited)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- odpalamy aktualizację rekordów, które mają dużą wartość obrony
-- w tym momencie baza będzie czekała na zakończenie pierwszej transakcji (użytkownika postgres),
-- która modyfikuje te same rekordy co poniższe zapytanie
UPDATE equipment SET armor = armor + 3 WHERE armor > 20;Interakcje transakcji - tryb read committed - użytkownik postgres - część 2 · 5 min
-- umożliwiamy modyfikowanie tabeli equipment drugiemu użytkownikowi
-- GRANT ALL ON TABLE equipment TO other;
-- rozpoczynamy transakcję
-- BEGIN;
-- ustawiamy bazowy poziom izolacji (read commited)
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
-- SELECT * FROM equipment WHERE armor IS NOT NULL;
-- odpalamy aktualizację rekordów, które mają niedużą wartość obrony.
-- Wartość zmiany obrony zależna jest od WSZYSTKICH rekordów z niepustą obroną
-- UPDATE equipment SET armor = armor + (SELECT AVG(armor)/2 FROM equipment WHERE armor IS NOT NULL)
-- WHERE armor < 20;
-- w tej samej transakcji (z części pierwszej) sprawdzamy zmiany wprowadzone przez UPDATE
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- i zatwierdzamy transakcję (odblokowywując tym samym transakcję użytkownika other)
-- transakcja użytkownika other widzi już zmiany wprowadzone przez UPDATE z obecnej transakcji
COMMIT;Interakcje transakcji - tryb read committed - użytkownik other - część 2 · 6 min
-- rozpoczynamy transakcję
-- BEGIN;
-- ustawiamy bazowy poziom izolacji (read commited)
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
-- SELECT * FROM equipment WHERE armor IS NOT NULL;
-- odpalamy aktualizację rekordów, które mają dużą wartość obrony
-- w tym momencie baza będzie czekała na zakończenie pierwszej transakcji (użytkownika postgres),
-- która modyfikuje te same rekordy co poniższe zapytanie
-- UPDATE equipment SET armor = armor + 3 WHERE armor > 20;
-- w tej samej transakcji (z części pierwszej) weryfikujemy zmiany wprowadzone przez UPDATE
-- (zmiany z transakcji użytkownika postgres są też tutaj widoczne)
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- cofamy zmiany wprowadzone przez tą transakcję
ROLLBACK;
-- teraz widoczne są tylko zmiany z transakcji użytkownika postgres
SELECT * FROM equipment WHERE armor IS NOT NULL;Interakcje transakcji - tryb repeatable read - użytkownik postgres - część 1 · 7 min
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- wprowadzamy małą poprawkę do wartości obrony
UPDATE equipment SET armor = armor - 3 WHERE armor IS NOT NULL;
-- sprawdzamy jak się ta zmiana zaaplikowała
SELECT * FROM equipment WHERE armor IS NOT NULL;Interakcje transakcji - tryb repeatable read - użytkownik other · 7 min
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- wprowadzamy małą poprawkę do wartości obrony
-- ale tutaj transakcja zatrzyma się, czekając na zakończenie się transakcji użytkownika postgres
UPDATE equipment SET armor = armor - 5 WHERE armor IS NOT NULL;
--po zakończeniu się transakcji użytkownika postgres tutaj nastąpi błąd (serializacji)Interakcje transakcji - tryb serializable - użytkownik postgres - część 1 · 12 min
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- wstawiamy nowy rekord z wartością obrony zależną od modyfikacji w równoległej transakcji użytkownika other
-- średnia z wartości obrony dla bohaterów z hero_id > 4
-- (a w równoległej transakcji wstawiamy rekord powiązany z hero_id = 5)
INSERT INTO equipment (equipment_name, equipment_type, damage, amount, hero_id)
SELECT 'topór', 'broń', CAST(AVG(damage) AS integer), 1, 2 FROM equipment WHERE hero_id > 4;
-- sprawdzamy jaka wartość została wyliczona
SELECT * FROM equipment WHERE damage IS NOT NULL;Interakcje transakcji - tryb serializable - użytkownik other - część 1 · 13 min
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- wstawiamy nowy rekord z wartością obrony zależną od modyfikacji w równoległej transakcji użytkownika postgres
-- średnia z wartości obrony dla bohaterów z hero_id <= 4
-- (a w równoległej transakcji wstawiamy rekord powiązany z hero_id = 2)
INSERT INTO equipment (equipment_name, equipment_type, damage, amount, hero_id)
SELECT 'łuk', 'broń', CAST(AVG(damage) AS integer), 1, 5 FROM equipment WHERE hero_id <= 4;
-- sprawdzamy jaka wartość została wyliczona
SELECT * FROM equipment WHERE damage IS NOT NULL;