Wyższa Szkoła Technologii Teleinformatycznych w Świdnicy

Internetowe bazy danych wykład 3 dr inż. Jacek Mazurkiewicz e-mail: [email protected]

Typy tabel MySQL   

domyślny – MyISAM inne możliwe: Merge, Heap, InnoDB, BDB jedna baza – różne typy tabel możliwe

Kryteria wyboru  

tabela transakcyjna – transaction-safe table – TST: InnoDB, BDB tabela nietransakcyna – non-transaction-safe table – NTST: MyISAM, Merge, Heap

Czy transakcje potrzebne?  

w aplikacjach WWW tabele transakcyjne nie są potrzebne bezstanowe środowisko HTTP nie wymaga zatwierdzania i odwoływania – operacje nie są skomplikowane i nie jest konieczna niezależność, transakcja jako logika PHP

Tabele transakcyjne – zalety: 











dane bardziej bezpieczne, można je odtworzyć gdy serwer MySQL lub system się załamie odtworzenie danych automatyczne lub ręczne na podstawie kopii zapasowej i dziennika tabeli łączenie kilku instrukcji SQL w jeden zbiór – transakcję – zbiór taki to jedna, atomowa operacja albo zrealizują się wszystkie operacje ze zbioru albo żadna z nich można odwołać dotychczas wykonane operacje gdy akcja zostanie przez użytkownika anulowana lub któryś z kroków się nie powiedzie można także odwołać operacje gdy współpraca interakcja użytkownika z aplikacją bazodanową WWW będzie podejrzana

Tabele nietransakcyjne – zalety: 

 

są znacznie szybsze – zapewnienie bezpieczeństwa danych i zarządzanie nimi jest czasochłonne zużywają mniej zasobów – tak dysku, jak i pamięci prostsza idea konstrukcyjna

CREATE TABLE winery ( winery_id int(4) NOT NULL, winery_name varchar(10) NOT NULL, region_id int(4) NOT NULL, PRIMARY KEY (winery_id), Przykład tworzenia KEY name (winery_id), InnoDB KEY region (region_id) ) type = InnoDB;  



MyISAM, Merge, Heap, InnoDB – dostępne od MySQL 4 BDB – trzeba wkompilować w serwer jak zakładamy tabelę, która nie jest realizowana – MyISAM

tabeli

MyISAM: 



 











bardzo szybkie wykonywanie zapytań, czasochłonność modyfikacji danych niewielka trzy wbudowane mechanizmy przechowywania danych – łatwość tworzenia tabel o różnych konstrukcjach najlepiej dopasowana do aplikacji WWW blokowanie tabel – jedna lub wiele tabel jest niedostępnych wtedy dla innych użytkowników blokady potrzebne na krótko – DELETE i UPDATE na grupach wierszy – szybko, dostęp – index, klucz główny blokady rzadko potrzebne – odczytów dużo więcej niż zapisów, współbieżność nie jest nagminna jedynie blokowanie – grupowanie GROUP BY – generalnie dostęp do większości wierszy w tabeli zarządzanie współbieżnymi uaktualnieniami – automatyczna kopia nowych danych, reszta (SELECT) czyta niezmienione – wersjonowanie danych atrybuty stałej długości – na dysku wiersze stałej długości, szybki dostęp, łatwość rekonstrukcji, zmienna długość - dynamiczna

InnoDB (1):  

 









ogólnego przeznaczenia, alternatywa do MyISAM tabele transakcyjne – obecny FOREIGN KEY, zatwierdzanie, odwoływanie operacji, odzyskiwanie danych, blokowanie wierszy zalety rzadko przewyższają wady w świecie WWW – lepiej MyISAM COMMIT I ROLLBACK – zbiór instrukcji SQL to jeden blok, albo zrobi się wszystko, albo nic elastyczne, szybkie blokowanie na poziomie wierszy – blokowane są wiersze, gdzie realizuje się zapytania i uaktualnienia, a nie całe tabele jak w MyISAM – sprawniejsze niż w MyISAM przy dużej liczbie użytkowników ograniczenia kluczy obcy – dbałość o integralność danych – nie można dodać żadnych wierszy, które nie mają odpowiedników w innej tabeli ograniczenia kluczy obcych nie są konieczne – wymagają dużo zasobów, z powodzeniem może je zastąpić logika PHP punkty kontrolne odzyskiwania danych – wpis do pliku dziennika – w przypadku załamania bazy lub systemu można odzyskać dane z tabeli InnoDB

InnoDB (2): 



 



 



elastyczna izolacja transakcji – model można rozluźnić by przyspieszyć wykonywanie zapytań część właściwości transakcji znika elastyczne indeksowanie – automatyczna decyzja czy w tabeli potrzebny jest szybki indeks mieszający zajmują dużo więcej przestrzeni niż MyISAM jeśli są klucze obce to trzeba na zarządzanie tabelami dużo więcej zasobów wersjonowanie danych i użycie transakcji – zarządzanie tabelami – dużo więcej zasobów większa ilość pamięci by panować nad blokadami wierszy blokowanie może obniżyć wydajność – skoro jest na poziomie wierszy –więcej trzeba ustanawiać i zdejmować blokad indeksy zakładać gdy ładujemy dane do tabel, potem jest to bardzo czasochłonne

Heap:   









 

do specjalnych celów, mają ograniczenia przechowywane w pamięci, nie na dysku tabele tymczasowe, tabele z częstymi odwołaniami ograniczenia – jak serwer zatrzyma się, ponowne uruchomienie, nie ładują się indeksowanie mieszające – najszybsze wyszukiwanie – dokładne dopasowanie jako = i inne porównania nie działają, nie działa sortowanie ORDER BY – do znalezienia wiersza związanego z wartością klucza nie obsługują wszystkich typów – brak TEXT, BLOB, brak mechanizmu AUTO_INCREMENT trzymanie w pamięci – zasobochłonne – używać z umiarem, małe „gabaryty” blokady na poziomie całych tabel jak padnie serwer to tabela przepada – jest tylko w pamięci!

Transakcje – COMMIT i ROLLBACK (1) 

 



seria instrukcji SQL jako jedna niepodzielna grupa, działa tylko w tabelach transakcyjnych (InnoDB) transakcje = powtarzalne odczyty domyślnie MySQL na automatyczne zatwierdzanie – aktualizacja danych zaraz po wykonaniu modyfikacji wyłączenie – poprzez ustawienie parametru bądź włączenie transakcji:

SET AUTOCOMMIT = 0; START TRANSACTION; SELECT * FROM tabela1 WHERE typ LIKE ”tutu”; UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”; COMMIT;

SET AUTOCOMMIT = 1; START TRANSACTION; SELECT * FROM tabela1 WHERE typ LIKE ”tutu”; UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”; ROLLBACK;

Transakcje – COMMIT i ROLLBACK (2) BEGIN WORK; SELECT * FROM tabela1 WHERE typ LIKE ”tutu”; UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”; COMMIT; BEGIN; SELECT * FROM tabela1 WHERE typ LIKE ”tutu”; UPDATE tabela2 SET suma = 1 WHERE typ LIKE ”tutu”; ROLLBACK; 



nie można cofnąć instrukcji, które tworzą i usuwają bazy danych, tworzą, usuwają tabele – projektować tak transakcje by ich nie było w nich, inaczej nie można cofnąć całej transakcji niejawne zatwierdzenie robią:

ALTER TABLE DROP DATABASE LOAD MASTER TABLE SET AUTOCOMMIT=1 UNLOCK TABLES

BEGIN DROP INDEX LOCK TABLES START TRANSACTION CREATE TABLE

CREATE INDEX DROP TABLE RENAME TABLE TRUNCATE TABLE

Transakcje – COMMIT i ROLLBACK (3) 

wycofanie transakcji do punku kontrolnego:

SAVEPOINT identyfikator ROLLBACK TO SAVEPOINT identyfikator 











blokowanie tabel - LOCK TABLE – blokowanie tabeli dla potrzeb bieżącego wątku, UNLOCK TABLES – zwalnia wszystkie blokady nałożone w danym wątku, niejawne odblokowanie przy wykonaniu innej LOCK TABLES lub przy zamknięciu połączenia z serwerem LOCK TABLES niebezpieczna dla transakcji – zatwierdza aktywną transakcję przed nałożeniem blokad Użycie LOCK TABLES wymaga uprawnień do LOCK TABLES i SELECT, wcześniejsze wersje MySQL: SELECT, INSERT, DELETE, UPDATE blokada READ – tylko odczyt z tabeli, WRITE – wątek, który założył może pisać, czytać, reszta wątków zablokowana READ LOCAL – możliwe są niekonfliktowe INSERT-y jeśli blokada z synonimem trzeba odwołać się z synonimem:

Transakcje – COMMIT i ROLLBACK (4) LOCK TABLE t READ; SELECT * FROM t AS synonim; LOCK TABLE t AS synonim READ; SELECT * FROM t; SELECT * FROM t AS synonim; 









WRITE ma wyższy priorytet od READ – jeśli nałożono READ i czeka WRITE do nałożenia, to stawiane kolejne READ muszą czekać aż WRITE będzie nałożona i zwolniona LOW_PRIORITY WRITE – pozwala nałożyć READ chociaż czekamy na rozstrzygnięcie z WRITE LOCK TABLES: blokada do odczytu i zapisu, najpierw zapis, KILL pozwala znieść oczekiwanie na założenie blokady w MyISAM blokowanie przyspiesza wykonywanie wstawiania, aktualizacji, usuwania, klucze są pamiętane w pamięci podręcznej, normalnie jest ona czyszczona po każdym zapytaniu SQL-owym zasadniczo nie ma potrzeby robienia blokad!

Transakcje – COMMIT i ROLLBACK (5)

LOCK TABLES trans READ, klienci WRITE; SELECT SUM(value) FROM trans WHERE id_klienta == pewien_id; UPDATE klienci SET razem = suma_z_poprzednich_instrukcji WHERE id_klienta == pewnie_id; UNLOCK TABLES; 



bez LOCK TABLES między SELECT a UPDATE inny wątek może spowodować wstawienie nowego wiersza do tabeli trans alternatywą do blokowania są operacje SET postaci:

UPDATE klienci SET wartosc = wartosc + nowa_wartosc