Sql für Dummies

Datenbanken für DummiesKristian Köhntopp

Alter Sack

SysEleven GmbH

Relationenalgebra

Vorsicht, Mathematik!

Rechnen mit Tabellen3

3

Kex

√2

Keks

a

Cookie

1

c

π

b

2

e

{ 1, 2, 3 } vs. (1, 2, 3) Datentypen?

Tabelle: { (1, 2, 3), (π, e, √2), (“Keks”, “Cookie”, “Kex”) }

Rechnen mit Tabellen

• Was können wir mit der Tabelle tun, das wieder eine Tabelle ergibt?

• Algebra:Grundmenge A, Operationen, Ergebnisse wieder in A

• Weiter rechnen!

4

3

Kex

√2

Keks

a

Cookie

1

c

π

b

2

e

Rechnen mit Tabellen

• Selektion

• Projektion

• Kreuzprodukt

• Rename

• Aggregation

5

3

Kex

√2

Keks

a

Cookie

1

c

π

b

2

e

Selektion

• SELECT *FROM tWHERE a=1

• Wähle eine oder mehrere Zeilen

6

3

Kex

√2

Keks

a

Cookie

1

c

π

b

2

e

Selektion

Projektion

Projektion

• SELECT b, concat(“*”, b, “*”) FROM t

• Wähle eine oder mehrere Spalten.

• Generiere neue Spalten durch Anwendung von Funktionen.

7

3

Kex

√2

Keks

a

Cookie

1

c

π

b

2

e

Selektion

Projektion

Kreuzprodukt

• Kombiniere jede Zeile aus a mit jeder Zeile aus b (“full join”)

• Oft mit Joinbedingung: Behalte nur die Paarungen, die Sinn ergeben.

8

3

aid

1

2

a

bid

1

2

b

23

3 1

22

2

aid

1

1

1

bid

1

2

a join b

Kreuzprodukt

• SELECT *FROM kundenJOIN bestellungen ON kunden.kid = bestellungen.kid

• “Verknüpfe alle Kunden mit allen Bestellungen via Kundennummer.”

• “Liste mir alle Kunden mit Kundennamen etc auf, die jemals bestellt haben.”

9

Rename - Self-Join

• create table emp (-> empid integer unsigned not null,-> bossid integer unsigned null-> ) engine = innodb;

• insert into emp-> values ( 1, NULL), (2, 1), (3,1), (4, 2), (5, 2), (6, 2);

10

emp

1

23

4 5 6

Rename - Self-Join

• SELECT * FROM emp JOIN emp ON emp.bossid = emp.empid;

• ERROR 1066 (42000): Not unique table/alias: 'emp'

11

emp

1

23

4 5 6Doppelt!

Rename - Self-Join

• SELECT emp.empid AS mitarbeiter, " hat den Boss ", boss.empid AS boss FROM emp AS boss JOIN emp ON emp.bossid = boss.empid;

12

26

5 2

24

3

empid

1

1

2

bossid

NULL

1

emp

26

5 2

24

3

empid

1

1

2

bossid

NULL

1

emp (as boss)

Self-Join

Tabelle umbenennen

Spalte umbenennen

Rename - Self-Join

• SELECT emp.empid AS mitarbeiter, " hat den Boss ", boss.empid AS boss FROM emp AS boss JOIN emp ON emp.bossid = boss.empid;

13

Wo ist das Paar (1, NULL)?

Aggregation

• select empid, bossid from emp; • +--------+--------+|empid|bossid|+--------+--------+|1|NULL||2|1||3|1||4|2||5|2||6|2|+--------+-------+

14

26

5 2

24

3

empid

1

1

2

bossid

NULL

1

emp

bossidempid

262524

1312

emp (GROUP BY bossid)

Aggregation

• SELECT bossid,count(*),group_concat(empid) AS mitarbeiterFROM empGROUP BY bossid;

15

Relationen-Algebra

• Selektion

• Projektion

• Kreuzprodukt

• Rename

• Aggregation

16

3

Kex

√2

Keks

a

Cookie

1

c

π

b

2

e

Relationen-Algebra

• Algebra:

• Grundmenge A,

• Operationen,

• Ergebnisse wieder in A

• Weiter rechnen!

17

3

Kex

√2

Keks

a

Cookie

1

c

π

b

2

e

Weiter rechnen: Subqueries18


Es gibt 2 Tables in kris

mit 6 Rows

Und in Test dieselbe Situation

nochmal!

Subqueries

• SELECT query FROM query AS t WHERE x = ( query )

• Die Ergebnisse von Queries sind Tabellen, sie können in andere Queries eingesetzt werden.

• Das geht nur deshalb, weil SQL eine Algebra ist.

22

Tupel sind Zeilen, ein Datentyp

• select a,b,c from t where a = 'eins' and b = ‘one';

• select a,b,c from t where (a,b) = ('eins','one');

23


• select * from t where (a,b) in (('eins','one'), ('zwei', ‘two'));

• (a=‘eins’ AND b=‘one’) OR (a=‘zwei’ AND b=‘two’)

24


• select a,b,c from t where 'eins' in (a,b,c);

• (‘eins’=a) OR (‘eins’=b) OR (‘eins’=c)

• ‘eins’ in einer beliebigen Spalte

25


• select a,b,c from t where ('eins','one') in ((a,b),(b,c));

• (‘eins’=a AND ‘one’=b) OR (‘eins’=b AND ‘one’=c)

• (‘eins’, ‘one) in benachbarten Spalten

26

Abfragesprachen, die keine Algebra sind

• LDAP

• Abfragen auf Bäumen.

• Resultate sind keine Bäume, sondern Knotenmengen.

• XPath, XSLT

• Abfragen auf Bäumen.

• Resultat Nodeset (Herstellerspezifische Workarounds).

27

Transaktionen

“Undo”29

id d txn#

1 one 2

2 zwei 1

3 drei 1

id d txn#

1 eins 1

Undo LogTable t

UPDATE t SET d = ‘one’ WHERE id = 1

Transaktionen

• START TRANSACTION READ WRITE;

• UPDATE t SET d = ‘one’ WHERE id = 1;

• COMMIT;



• ROLLBACK;

30

Nix zu tun, Daten stehen schon in der Tabelle

Daten aus dem Undo Log zurück schaufeln

Transaktionen

• Writer:



• Reader:

• SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

• SELECT * FROM t WHERE id = 1;

31

Transaktionen

• Writer:



• Reader:

• SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


32

Transaktionen

• Was ist die Bedeutung von

• START TRANSACTION READ ONLY;



• COMMIT;

• im Vergleich zu “SELECT * FROM t WHERE id = 1” ohne Transaktion?

33

Transaktionen

• SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;


• Die Welt bleibt stehen: Snapshot!

• ‘Gehe im Undo Log mehr als einen Schritt zurück und liefere die neusten Daten, die älter sind als meine Transaktion.’

• COMMIT;

34

Uncommitted, Committed, Repeatable Read35

id d txn#

1 one 9

2 zwei 5

3 drei 7

id d txn#

1 bla 8

1 fasel 6

1 eins 1

Undo LogTable t

SELECT id, d FROM t WHERE id = 1 — txn#4

Undo Log Purge36

id d txn#

1 one 9

2 zwei 5

3 drei 7

id d txn#

1 bla 8

1 fasel 6

1 eins 1

Undo LogTable t

Lösche alle Undo Log Einträge, die älter sind als die älteste noch aktive Transaktion.

Undo Log Purge



• — fährt in den Urlaub

• Was passiert?

37

Ein Zähler

• CREATE TABLE cnt ( name VARCHAR, value INTEGER);

• SELECT value FROM cnt WHERE name = ‘verkäufe’;

• UPDATE cnt SET value = 18 WHERE name = ‘verkäufe’;

38

name value

verkäufe 17

einkäufe 20

Ein Zähler verzählt sich…39

name value

verkäufe 17

einkäufe 20

START TRANSACTION

SELECT valueFROM cntWHERE name = ‘verkäufe’

START TRANSACTION

Thread 1 Thread 2

UPDATE cntSET value = 18WHERE name = ‘verkäufe’

COMMIT

SELECT valueFROM cntWHERE name = ‘verkäufe’


COMMIT

Richtig zählen…40

name value

verkäufe 17

einkäufe 20

START TRANSACTION

SELECT valueFROM cntWHERE name = ‘verkäufe’FOR UPDATE

START TRANSACTION

Thread 1 Thread 2

name = ‘verkäufe’lockedby Thread 1


COMMIT lock dropped

SELECT valueFROM cntWHERE name = ‘verkäufe’FOR UPDATE

name = ‘verkäufe’lockedby Thread 2

MVCC

• “Multi Value Concurrency Control”

• “Multi Value”: in der Tabelle und alte Versionen im Undo-Log

• “Concurrency Control”: Ein Write wird niemals ein Read Blocken.

• SELECT … FOR UPDATE ist ein Read, der wie ein Write lockt und blockt.

41

… TRANSACTION ISOLATION LEVEL …

• Isolation ist etwas, das jeder Client für sich selber wählen kann.

• Das Undo-Log ist sowieso notwendig, damit Rollback geht.

• Der Isolation Level bestimmt, wie weit der Reader ins Undo-Log abtaucht.

• Das erlaubt Snapshots, private eingefrorene Kopien der Datenbank.

42

Daten auf Platten malen

“Platten”

• Sektoren: 512 Byte, 520 Byte, 4096 Byte

• “Ein Sektor wird immer ganz oder gar nicht überschrieben.”

• Was ist mit SSD?

• Das ist kompliziert. Man will was mit PLP (Power Loss Protection).

44

SSD und Power Loss45

https://www.usenix.org/conference/fast13/technical-sessions/presentation/zheng

https://www.usenix.org/conference/fast13/technical-sessions/presentation/zheng

“Pages”

• InnoDB hat 16 KB Pages

• Oracle und Postgres: 8 KB

• Page ist die I/O Unit, d.h. es werden immer 2 oder mehr Sektoren (4K Sektoren) geschrieben.

• “half written pages” sind selbst im Idealfall möglich.

46

Was tun?

• Pages nie überschreiben.

• InnoDB: Doublewrite Buffer

• “Schreibe Page im Doublewrite Buffer, markiere als erledigt, schreibe noch einmal in-place.”

• Postgres: WAL full page writes

• “Hänge die alte Page ans WAL an, schreibe in-place.”

47

Writeback-Caches: Tod der Daten

• Writethrough-Cache:

• Write geht in den Cache und auf die Platte.

• “write ok”: Die Daten stehen wirklich auf der Platte.

• Writeback-Cache:

• Write geht in den Cache, “write ok” melden.

• Daten stehen auf der Platte oder nicht, niemand weiß es.

48

Caches: wo?

• Platte selbst: Track Cache, Onboard-Cache.

• Controller: Not-so-smart-Array.

• Betriebssystem: File System Buffer Cache.

• Alle haben unterschiedliche “Flush” Commands.

49

Replikation und verteilte Systeme

Database State51

State(all tables)

New State(table modified)

ConsistentSnapshot

DataChangingStatement

logged tobinary log

Database State

• "Uhrzeit" hat keinen Sinn:

• viele CPUs, gleichzeitige Operationen

• Binlog = Serialisierung aller Ereignisse

• "Binlog Position" = die "Uhrzeit" dieser Datenbank

52

Backup und Restore

• Vollbackups jede Mitternacht

• Binlog-Position zum Backup bekannt

• Restore:

• Vollbackup + alle Writes seit diesem Backup

• "Point in Time Recovery"

53

Sunday,Midnight

Monday,Midnight

Tuesday,Midnight

binlog.00000108:12

binlog.00000218:31

binlog.00000322:52

binlog.00000404:18

binlog.00000517:49

binlog.00000603:47

binlog.00000716:25

Replikation als laufende Recovery54

Master Slave

Binlog RelayLog

ConnectionThread

IOThread

SQLThread

To Tables

Slavelogs in to Master

Server ID: x Server ID: y

Replikation als laufende Recovery

• Restore eines Slave aus einem Vollbackup

• SLAVE_IO_THREAD lädt die Binlogs vom Master runter

• seit dem Vollbackup-Zeitpunkt

• SLAVE_SQL_THREAD wendet das Binlog laufend an

• Slave = ein durchlaufender Dauer-Restore

55

Statement based vs. Row based

• Wir laden changes vom Master runter:

• SBR: Original-Statement, wie es der Master gesehen hat

• RBR: Die Rows, die das Statement geändert hat

• Was ist besser?

56

Statement Based

• Manche Statements haben unterschiedliche Results, wenn man sie zum 2. Mal ausführt

• NOW(), RAND(), LAST_INSERT_ID()

• LIMIT mit und ohne ORDER BY

• Tausend andere Spezialfälle

• Fazit: SBR ist fragil (in der Praxis stabil genug)

57

Row Based

• Ein Statement = viele Row Changes (braucht Transaktionen!)

• Ein Statement bricht auf dem Master ab (Transaktionen!)

• int auf dem Master, smallint auf dem Slave (WTF?)

• Was passiert beim Replikationsfehler?

58

SBR vs RBR

• In der Praxis:

• RBR ist 1/3 so groß wie SBR

• Kein Index -> Bumm.

• BLOB -> Bumm (fixed in 5.6+) wegen pre/post image

• In Summe: kleiner, robuster, klare Constraints.

59

Best Practices für Replikationen

• “Replikation ist eine laufende Binlog Recovery”

• InnoDB verwenden, MyISAM vernichten, PK verlangen.

• RBR verwenden, BLOB berücksichtigen.

• Neu in MySQL 5.5, Maria 5.5: Group Commit

• Neu in MySQL 5.7, Maria 10.1: Parallel Slave

60

Replikations-Topologien61

Slave Server

Master Server

Client

writes

reads(through LB)

Replikations-Topologien62

Slave Server

Master Server

Slave Server

Local Master Server

Heathrow Amsterdam

Replikations-Topologien Nichts als Probleme63

Slave Server

Master Server DesignatedBackup Master

binlog.000018Offset 743271

binlog.000012Offset 393273?

Replikations-Topologien Dumme Ideen64

Server #1

Server #3

Server #2

"Master-Master" gibt es nicht

• Zugriffe ordnen (aio, aii)

• funktioniert so nicht (UPDATE, DELETE, failover-Fall)

• Wie funktioniert Recovery nach einem ungeordneten Shutdown?

65

Robustes Konzept66

Slave Server

Master Server Backup Master

Disk Mirroror

Shared Disk

VirtualIP Number

Mobile between Hosts

–Johnny Appleseed

Ende der Ausbaustrecke

67

Alternativen zu Master-Master

• HA-Problem lösen:

• Disk/IP Switch, Log Recovery FS, DB

• Galera

• “writes to a single master, most of the time, rollbacks”

• Replikationsumschaltungen

• Relay Log Drain, Binlog-Konvertierung (MHA, GTID)

68

Split Brains und andere Clusterfails

• 2PC, 3PC, Paxos, Raft, Jepsen-Testing

• ZK, etcd, Consul und was die machen

• Write-Serialization

• Attendance

69

Sql für Dummies

Internet

Transcript of Sql für Dummies