SQL-Optimierungen

Tag 7
Inhaltsverzeichnis
• Das DB-Testdaten Problem
• Verschiedene Werkzeuge
• Überblick über "benerator"
• Testdaten für DB GMCD2 generieren
• Installation und Übungen mit "benerator"
• SQL-Optimierungen
• Was macht der Optimizer?
• Problematik der Datenzugriff mit SQL
• Anwendungsfall GMCD2 und MySQL
• Übungen
• Ein Blick in die Zukunft
• RDB vs. NoSQL
RDB 7 - 1
Version 3.5
DB-Testdaten
Das Problem
Daten
frisch erstellen
produktive Daten
anonymisieren
Beispiele:
- gmcd
- EqualS
- ...
RDB 7 - 2
Version 3.5
DB-Testdaten
Werkzeuge
• SQL-Scripte von Hand entwickeln
• Perl-Script / Java-Program selber entwickeln
• Datengenerator verwenden
Zum Beispiel:
•
•
•
•
benerator
TurboData
Jailer
...
Quelle: http://databene.org/databene-benerator/similar-products.html
RDB 7 - 3
Version 3.5
DB-Testdaten
Das benerator Werkzeug
Quelle: http://databene.org/databene-benerator.html
RDB 7 - 4
Version 3.5
DB-Testdaten
Wie funktioniert benerator?
Quelle: http://databene.org/databene-benerator.html
RDB 7 - 5
Version 3.5
DB-Testdaten
benerator Komponenten
Quelle: http://databene.org/download/databene-benerator-manual-0.8.1.pdf
RDB 7 - 6
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (1)
Inhalt der Datei "gmcd2.benerator.xml"
<?xml version="1.0" encoding="iso-8859-1"?>
<setup
xmlns="http://databene.org/benerator/0.7.8"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://databene.org/benerator/0.7.8
http://databene.org/benerator-0.7.8.xsd">
<comment>
Subject: MySQL Benerator Populator for DB GMCD2
Author : Gilles Maitre, 2012
Links :
</comment>
...
RDB 7 - 7
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (2)
Inhalt der Datei "gmcd2.benerator.xml"
...
<import defaults="true"
domains="person,net,product,address,organization"
platforms="db" />
<comment>setting default values</comment>
<setting name="dbCatalog" value="" />
<setting name="dbSchema" value="" />
<setting name="dbBatch" value="false" />
...
RDB 7 - 8
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (3)
Inhalt der Datei "gmcd2.benerator.xml"
...
...
<comment>import database properties' file</comment>
<include uri="gmcd2.benerator.properties" />
Inhalt der Datei "gmcd2.benerator.properties"
dbType=MySQL
dbUrl=jdbc:mysql://localhost/GMCD2
dbDriver=com.mysql.jdbc.Driver
dbSchema=GMCD2
dbCatalog=GMCD2
dbUser=gmaitre
dbPassword=Gilles
nbrOfMusikerPerLandAndSex=25
nbrOfStueck=20
nbrOfCD=10
RDB 7 - 9
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (4)
Inhalt der Datei "gmcd2.benerator.xml"
<comment>define a database, later referred by the id 'db'</comment>
<database id="db" url="{dbUrl}" driver="{dbDriver}"
catalog="{dbCatalog}" user="{dbUser}"
password="{dbPassword}" batch="{dbBatch}" />
<comment>drop current tables if they exist and recreate them</comment>
<execute uri="{ftl:drop${dbType}Tables.sql}" target="db" onError="ignore" />
<execute uri="{ftl:create${dbType}Tables.sql}" target="db" />
Inhalt der Datei "dropMySQLTables.sql"
drop table
drop table
drop table
drop table
drop table
drop table
drop table
commit;
tbl_MusikerInstrument;
tbl_StueckMusiker;
tbl_CDStueck;
tbl_CD;
tbl_Instrument;
tbl_Musiker;
tbl_Stueck;
RDB 7 - 10
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (5)
Inhalt der Datei "gmcd2.benerator.xml"
<comment>Importing Instruments from CSV file called "Instrument.csv"</comment>
<iterate source="Instrument.csv" type="tbl_Instrument"
encoding="utf-8" consumer="db" />
Inhalt der Datei "Instrument.csv"
ID,Typ,Name
1,Saiteninstrument,Gitarre
2,Saiteninstrument,Bass
...
Inhalt der DB
mysql> select * from tbl_Instrument;
+----+-------------------+----------------+
| ID | Typ
| Name
|
+----+-------------------+----------------+
| 1 | Saiteninstrument | Gitarre
|
| 2 | Saiteninstrument | Bass
|
...
RDB 7 - 11
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (6)
Inhalt der Datei "gmcd2.benerator.xml"
<comment>Generate Musiker of different countries / MALE and FEMALE</comment>
<comment>First, define a primary key generator for Musiker
starting at 1000</comment>
<bean id="idGenMusiker" spec="new IncrementGenerator(1000)" />
<comment>Generate Brasilian MALE Musiker</comment>
<generate type="tbl_Musiker" count="{nbrOfMusikerPerLandAndSex}" consumer="db">
<id name="ID" generator="idGenMusiker" />
<attribute name="Vorname"
generator="new GivenNameGenerator('BR', Gender.MALE)" />
<attribute name="Name" generator="new FamilyNameGenerator('BR')" />
</generate>
Inhalt der DB
mysql> select * from tbl_Musiker;
+------+--------------+------------+
| ID
| Vorname
| Name
|
+------+--------------+------------+
| 1000 | Danilo
| Ribeiro
|
| 1001 | Bruno
| Oliveira
|
| 1002 | Mateus
| Pinto
|
RDB 7 - 12
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (7)
Inhalt der Datei "gmcd2.benerator.xml"
<iterate type="tbl_Musiker" name="musiker" source="db">
<generate type="tbl_MusikerInstrument"
minCount="1" maxCount="3" consumer="db">
<!-- An Hand der Liste Musikers, vergebe jedem 1 bis 3 Instrumente -->
<attribute name="MusikerID" type="long" script="{musiker.ID}"/>
<reference name="InstrumentID" source="db"
distribution="random" unique="true"/>
</generate>
</iterate>
Inhalt der DB, Instrumenteverteilung:
mysql> select Name, count(*) from tbl_MusikerInstrument mi,
tbl_Instrument i where mi.InstrumentID=i.ID group by
InstrumentID;
+----------------+----------+
| Name
| count(*) |
+----------------+----------+
| Gitarre
|
9 |
| Bass
|
22 |
| Gesang
|
14 |
| Schlagzeug
|
14 |
RDB 7 - 13
...
Version 3.5
DB-Testdaten
benerator Beispiel für GMCD2 (8)
Inhalt der Datei "gmcd2.benerator.xml"
<comment>Generate Stueck</comment>
<generate type="tbl_Stueck" count="{nbrOfStueck}" consumer="db">
<attribute name="AufnahmeDatum"
generator="new DateGenerator('1958-01-01', '2011-02-15', 1)" />
<attribute name="Dauer" min="10" max="1000"
distribution="new GaussianFunction(240, 120)"/>
</generate>
Inhalt der DB, "Dauer" Verteilung:
mysql> select min(Dauer), max(Dauer), avg(Dauer) from tbl_Stueck;
+------------+------------+------------+
| min(Dauer) | max(Dauer) | avg(Dauer) |
+------------+------------+------------+
|
81 |
418 |
252.6000 |
+------------+------------+------------+
RDB 7 - 14
Version 3.5
DB-Testdaten
benerator Installation und Test mit GMCD2
•
•
•
•
Software herunterladen ab http://bergmann-it.de/download/download_benerator
und ins <Ihr benerator Installationsverzeichnis> dekomprimieren.
Datei http://www.sws.bfh.ch/maitre/SD-RDB/gmcd2.zip
in ein Arbeitsverzeichnis dekomprimieren.
In der Datei "gmcd2.benerator.properties", Benutzername und Passwort anpassen.
Mit einem mysql-Interpreter oder MySQL Workbench, als "root":
• create database GMCD2;
grant all privileges on GMCD2.* to <yourUser>@localhost identified by "<your
password>";
In einem DOS-Fenster (Achtung: auf Linux sieht es anders aus):
•
•
•
cd <Ihr Arbeitsverzeichnis>
•
set BENERATOR_HOME=<benerator Installationsverzeichnis>
•
set Path=%BENERATOR_HOME%\bin;%Path%
•
set BENERATOR_OPTS=-Dstage=development -Ddatabase=mysql
•
Alles auf einer Zeile folgenden Befehl in einem DOS-Fenster ausführen:
java -classpath .;%BENERATOR_HOME%/bin/;%BENERATOR_HOME%/lib;
%BENERATOR_HOME%/lib/* org.databene.benerator.main.Benerator
./gmcd2.benerator.xml
•
Schauen Sie den Inhalt der GMCD2 DB an und testen Sie die SQL-Befehle aus
der Datei "gmcd2.benerator.xml" (in Bezug auf Datenverteilung).
RDB 7 - 15
Version 3.5
SQL-Optimierungen
Problematik
• RDB Tag 6: Performance auf Betriebssystem-,
DB-Index- und Applikationsebene
• Jetzt: Fokus auf SQL
• Problem 1: SQL sagt "was" aber nicht "wie"...
• Problem 2: RDB-spezifisch
=> MySQL != Oracle
RDB 7 - 16
Version 3.5
SQL-Optimierungen
Datentypen
•
Wenn möglich, NOT NULL-Felder definieren
(NULL sind schwerer zu optimieren, indexieren)
•
MySQL-Breite für Integer-Typen (INT(1) gleich performant wie
INT(20))
Für interaktive Werkzeuge (z.B. MySQL Command-Interpreter)
•
DECIMAL: mehr Speicher und langsamer als FLOAT und DOUBLE
•
CHAR: gut für kleine Strings
VARCHAR: "richtig dimensionieren", wegen Sortieren
•
DATETIME vs. TIMESTAMP... nicht gleichen Anwendungszweck
(DATETIME braucht mehr Speicher, weniger begrenzt, siehe RDB Tag
3)
•
Achtung: Automatisch-generierte Schemata (z.B. ORM) überprüfen
Quelle: High Performance MySQL, Kapitel 3
RDB 7 - 17
Version 3.5
SQL-Optimierungen
Index- und Datenfragmentierung
• Probleme
• Tabellen können beschädigt sein (wegen Crash)
• B-Bäume Indexe können fragmentiert sein
• InnoDB Index-Benutzung basiert auf Statistiken
(diese sind vielleicht nicht mehr aktuell)
• ==> Tabellen und Indexes (Cardinality) prüfen
• Befehle:
• analyse table <table>
• show index from <table>
• optimize table <table> oder
• alter table <table> engine=innodb
Quelle: High Performance MySQL, Seite 148
RDB 7 - 18
Version 3.5
SQL-Optimierungen
MySQL Abfragebearbeitung
Quelle: High Performance MySQL, Seite 172
RDB 7 - 19
Version 3.5
SQL-Optimierungen
MySQL Client/Server Protokoll
•
Halb-Duplex Protokoll
==
Entweder Client sendet dem Server etwas, oder das Gegenteil
•
<=> Keine Kommunikation "gleichzeitig" zwischen Client und Server
•
==> Keine Möglichkeit, eine Abfrage abzubrechen...
Man muss warten bis alle Daten fertig übertragen werden!
Quelle: High Performance MySQL, Seite 173
RDB 7 - 20
Version 3.5
SQL-Optimierungen
MySQL Abfrage Cache
•
Hash-Tabelle (Abfrage / Resultatsdaten) (Case-sensitive)
Abfrage
Resultatsdaten
select * from ...
...
select ID, XY from ...
...
•
Nicht determinische Abfragen werden nicht gespeichert (Abfragen mit now() oder
current_user())
•
Problem mit Transaktionen: Cache-Lookup erst möglich wenn die Transaktion
abgeschlossen ist
•
SET SESSION query_cache_type = OFF; => Disables CACHE... (Default: ON)
•
Parametrisierung: mit Variablen in my.cnf (my.conf/ini):
query_cache_limit
= 1M
// Max. Grösse des Resultatspuffers
query_cache_size
= 16M // Gesamte Cache-Grösse
•
select ohne Cache-Anwendung ausführen (Demo):
select SQL_NO_CACHE count(*) from tbl_Musiker;
show status like "last_query_cost"; // Effekt... Einheit: x*4kb-Seiten lesen
Quelle: High Performance MySQL, Kapitel 5
RDB 7 - 21
Version 3.5
SQL-Optimierungen
Oracle / MySQL Abfrage Optimierer
• Oracle 10g zwei Optimierer (alt und neu):
• Rule Based Optimizer (RBO): Regel-basiert (alt)
<=> Basiert auf fixen Eigenschaften von Tabellen
(Index, Datentypen)
unabhängig von der Entwicklung der Daten
• Cost Based Optimizer (CBO): Kosten-basiert (neu)
<=> Basiert auf Tabellen-Statistiken (periodisch aktualisiert)
abhängig von der Entwicklung der Daten
• MySQL Optimierer ist "Kosten-basiert"
=> Statistik müssen stimmen (siehe Slide Fragmentierung)
• MySQL Optimierer unabhängig vom Engine (siehe Slide
Abfragebearbeitung)
Quelle: SQL Tuning, Seite 86
RDB 7 - 22
Version 3.5
SQL-Optimierungen
MySQL Nested-Loop-Join's
•
Alle JOIN's im MySQL als Nested-Loop-Join's betrachtet
select ... from A a join B b using(id);
foreach a in A {
foreach b in B {
if a.id == b.id
}
}
•
=> getroffen...
Es gibt aber andere Möglichkeiten zu "joinen"
Quelle: http://de.wikipedia.org/wiki/Nested_Loop_Join
RDB 7 - 23
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (1)
•
Beispiel: Wie viele Musiker in GMCD2 spielen Gitarre?
•
SELECT count(*)
FROM tbl_Musiker m,
tbl_MusikerInstrument mi,
tbl_Instrument i
WHERE i.Name = 'Gitarre'
AND
i.ID = mi.InstrumentID AND
m.ID = mi.MusikerID;
•
Antwort: 19
•
Last_query_cost: 31.799000
RDB 7 - 24
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (2)
•
Gleiches Beispiel wie vorher:
•
SELECT count(*)
FROM tbl_Musiker m,
tbl_MusikerInstrument mi,
tbl_Instrument i
WHERE i.Name = 'Gitarre'
AND
i.ID = mi.InstrumentID AND
m.ID = mi.MusikerID;
•
Antwort: 19
•
Last_query_cost: 0.000000... Wieso?
RDB 7 - 25
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (3)
•
Wir probieren noch einmal, aber ohne Cache diesmal:
•
SELECT SQL_NO_CACHE count(*)
FROM tbl_Musiker m,
tbl_MusikerInstrument mi,
tbl_Instrument i
WHERE i.Name = 'Gitarre'
AND
i.ID = mi.InstrumentID AND
m.ID = mi.MusikerID;
•
Antwort: 19
•
Last_query_cost: 31.799000
RDB 7 - 26
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (4)
•
Wie wird überhaupt die Abfrage ausgeführt?
•
EXPLAIN EXTENDED SELECT count(*)
FROM tbl_Musiker m,
tbl_MusikerInstrument mi,
tbl_Instrument i
WHERE i.Name = 'Gitarre'
AND
i.ID = mi.InstrumentID AND
m.ID = mi.MusikerID;
+-------+--------+-----------------+--------------------+------+-------------+
| table | type
| key
| ref
| rows | Extra
|
+-------+--------+-----------------+--------------------+------+-------------+
| i
| ALL
| NULL
| NULL
|
14 | Using where |
| mi
| ref
| FK_InstrumentID | GMCD2.i.ID
|
7 | Using index |
| m
| eq_ref | PRIMARY
| GMCD2.mi.MusikerID |
1 | Using index |
+-------+--------+-----------------+--------------------+------+-------------+
(Abgekürzte Darstellung)
RDB 7 - 27
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (5)
•
Wie wird überhaupt die Abfrage ausgeführt? Darstellung
mit mk-visual-explain (von unten an lesen)
JOIN
+- Unique index lookup
| key
m->PRIMARY
| possible_keys PRIMARY
| key_len
4
| ref
GMCD2.mi.MusikerID
| rows
1
+- JOIN
+- Index lookup
| key
mi->FK_InstrumentID
| possible_keys PRIMARY,FK_InstrumentID,FK_MusikerID
| key_len
4
| ref
GMCD2.i.ID
| rows
7
+- Filter with WHERE
+- Table scan
rows
14
+- Table
table
i
possible_keys PRIMARY
Quelle: http://www.maatkit.org/doc/mk-visual-explain.html
RDB 7 - 28
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (6)
•
Wir ändern die Reihenfolge der Elemente in der Abfrage
•
EXPLAIN EXTENDED SELECT count(*)
FROM tbl_Musiker m,
tbl_MusikerInstrument mi,
tbl_Instrument i
WHERE m.ID = mi.MusikerID
AND
i.ID = mi.InstrumentID AND
i.Name = 'Gitarre';
+-------+--------+-----------------+--------------------+------+-------------+
| table | type
| key
| ref
| rows | Extra
|
+-------+--------+-----------------+--------------------+------+-------------+
| i
| ALL
| NULL
| NULL
|
14 | Using where |
| mi
| ref
| FK_InstrumentID | GMCD2.i.ID
|
7 | Using index |
| m
| eq_ref | PRIMARY
| GMCD2.mi.MusikerID |
1 | Using index |
+-------+--------+-----------------+--------------------+------+-------------+
(Abgekürzte Darstellung)
Überhaupt keine Änderung im Plan... Wieso?
RDB 7 - 29
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (7)
•
Wir ändern die Reihenfolge der Elemente in der Abfrage
und schalten den JOIN-Optimierer aus...
•
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN count(*)
FROM tbl_Musiker m,
tbl_MusikerInstrument mi,
tbl_Instrument i
WHERE m.ID = mi.MusikerID
AND
i.ID = mi.InstrumentID AND
i.Name = 'Gitarre';
ERROR 1064 (42000): You have an error in your SQL syntax
Wieso?
RDB 7 - 30
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (8)
•
•
Wir ändern die Reihenfolge der Elemente in der Abfrage,
schalten den Optimierer aus und schreiben einen richtigen JOIN
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN count(*)
Neue
FROM tbl_Musiker m
Abfrage
INNER JOIN tbl_MusikerInstrument mi ON m.ID = mi.MusikerID
INNER JOIN tbl_Instrument i ON i.ID = mi.InstrumentID
WHERE i.Name = 'Gitarre';
+-------+--------+-----------------+--------------------+------+-------------+
| table | type
| key
| ref
| rows | Extra
|
+-------+--------+-----------------+--------------------+------+-------------+
| i
| ALL
| NULL
| NULL
|
14 | Using where |
| mi
| ref
| FK_InstrumentID | GMCD2.i.ID
|
7 | Using index |
| m
| eq_ref | PRIMARY
| GMCD2.mi.MusikerID |
1 | Using index |
+-------+--------+-----------------+--------------------+------+-------------+
+-------+--------+---------+-----------------------+------+-------------+
| table | type
| key
| ref
| rows | Extra
|
+-------+--------+---------+-----------------------+------+-------------+
| m
| index | PRIMARY | NULL
| 100 | Using index |
| mi
| ref
| PRIMARY | GMCD2.m.ID
|
1 | Using index |
| i
| eq_ref | PRIMARY | GMCD2.mi.InstrumentID |
1 | Using where |
+-------+--------+---------+-----------------------+------+-------------+
RDB 7 - 31
Version 3.5
SQL-Optimierungen
MySQL Ausführungspläne vergleichen (9)
•
Wir ändern die Reihenfolge der Elemente in der Abfrage,
schalten den Optimierer aus, schreiben einen richtigen JOIN und schalten den
Cache aus und führen die Abfrage aus:
•
SELECT STRAIGHT_JOIN SQL_NO_CACHE count(*)
FROM tbl_Musiker m
INNER JOIN tbl_MusikerInstrument mi ON m.ID = mi.MusikerID
INNER JOIN tbl_Instrument i ON i.ID = mi.InstrumentID
WHERE i.Name = 'Gitarre';
show status like "last_query_cost";
Faktor 7...
+-----------------+-----------+
| Variable_name
| Value
|
+-----------------+-----------+
| Last_query_cost | 31.799000 |
+-----------------+-----------+
+-----------------+-------+
| Variable_name
| Value |
+-----------------+-------+
| Last_query_cost | 221
|
+-----------------+-------+
alte Abfrage
neue (absichtlich)
nicht optimierte
Abfrage
RDB 7 - 32
Version 3.5
Ein Blick in die Zukunft
RDB vs. NoSQL (1)
The big 3
und ihr Problem
mit RDM in 2005
Apache Hadoop
MongoDB
CouchBase
URL-Indexing
Amazon
Facebook
MapReduce
HBase
Google
Document-DB
Neue DB-Typen
Speichern von
Verkaufsdaten
Social Network
Datenstruktur
BigData
The Players /
Problems /
New Comers
The Cloud
IoT (Internet of Things)
DynamoDB
Apache Cassandra
Consistency
KeyValue Store
CAP
Oracle NoSQL
Neo4J
Availability
Partition Tolerance
CAP vs. ACID
Graph-DB
Atomacity
ACID
Weitere
Störfaktoren
Consistency
Isolation
Durability
MongoDB
MEAN
Express (WebApp Framework)
AngularJS (JavaScript Framework)
Node.js (Computational Engine)
MEAN vs. LAMP
Linux
LAMP
Apache
MySQL
PHP
Quelle: Next Generation Databases, S. 15-73
RDB 7 - 33
Version 3.5
Ein Blick in die Zukunft
RDB vs. NoSQL (2)
Multi-record ACID
Single Object strict
Tunable/Eventual
Tables
Consistency
Schema
Eventual
Documents
Wide column
Graph
B-tree
Log-structured Merge Trees
Storage
Index free graph
Kriterien für die
Wahl einer DB
SQL
Processing
Columnar
Row bases
Format
Quelle: Next Generation Databases, S. 194
REST
Graph
MapReduce
RDB 7 - 34
Version 3.5
Übungen
1) Bringen Sie das Benerator-Beispiel auf Seite 15 zum Laufen
2) Probieren Sie diese SQL-Optimierungsbeispiele zu reproduzieren und
vor allem sie zu verstehen.
Parameter, die eine Rolle spielen:
1) Cache ein/aus
2) SQL JOIN oder nicht
3) JOIN-Reihenfolge forciert oder nicht
"EXPLAIN EXTENDED SELECT" und "last_query_cost" ausprobieren
3) Probieren Sie den Ausführungsplan eine Abfrage aus Ihrer eigenen
Fantasie/Erfahrung abzubilden, zu verstehen und am Schluss
von Hand zu optimieren.
RDB 7 - 35
Version 3.5