www.fromdual.
com
Need for Speed:
Indexierung unter MySQL
CeBIT 2014,
11. Mrz, Hannover
Oli Sennhauser
Senior MySQL Berater bei FromDual GmbH
[email protected]
1 / 30
ber FromDual GmbH
www.fromdual.com
FromDual bietet neutral und unabhngig:
Beratung fr MySQL, Galera Cluster,
MariaDB und Percona Server
Support fr MySQL und Galera Cluster
Remote-DBA Dienstleistungen
MySQL Schulung
OSB Alliance Mitglied
www.fromdual.com
2 / 30
Unsere Kunden
www.fromdual.com
3 / 30
MySQL und Indexierung
www.fromdual.com
MySQL Dokumentation:
The best way to improve the performance of SELECT operations is
to create indexes on one or more of the columns that are tested in
the query.
Grossartig! Aber:
Unnecessary indexes waste space and waste time to determine
which indexes to use. You must find the right balance to achieve
fast queries using the optimal set of indexes.
... hmmm, somit mssen wir wohl ein bisschen denken... :-(
4 / 30
Was ist ein Index?
Adams, Douglas:
The Hitchhiker's
Guide to the
Galaxy?
www.fromdual.com
Sennhauser, Oli,
Uster?
5 / 30
Index technisch gesehen
www.fromdual.com
MyISAM
InnoDB
6 / 30
MySQL nutzt Indizes:
www.fromdual.com
Um Eindeutigkeit zu erzwingen (PRIMARYKEY, UNIQUEKEY)
Um schnell auf Zeilen zuzugreifen und diese zu filtern (WHERE)
Um Joins schnell auszufhren (JOIN)
Um MIN() und MAX() Werte schnell zu finden
Fr Sortier- und Gruppier-Operationen (ORDERBY, GROUPBY)
Um Joins mittels eines Covering Index zu vermeiden
Um FOREIGNKEYConstraints (FOREIGNKEY) zu erzwingen
7 / 30
WHERE Klausel 1
www.fromdual.com
SELECT*
FROMcustomers
SHOWCREATETABLEcustomers\G
WHEREname='FromDual';
CREATETABLE`customers`(
`customer_id`smallint(5)unsigned
,`name`varchar(64)DEFAULTNULL
,PRIMARYKEY(`customer_id`)
)
EXPLAIN
SELECT*
FROMcustomers
WHEREname='FromDual';
+++++++
|table|type|possible_keys|key|rows|Extra|
+++++++
|customers|ALL|NULL|NULL|31978|Usingwhere|
+++++++
8 / 30
Wie legt man Indices an?
www.fromdual.com
ALTERTABLE
ADDPRIMARYKEY(id);
ADDUNIQUEKEY(uuid);
ADDFOREIGNKEY(customer_id)
REFERENCEScustomers(customer_id);
ADDINDEX(last_name,first_name);
ADDINDEXpre_ind(hash(8));
ADDFULLTEXTINDEX(last_name,
first_name);
9 / 30
WHERE Klausel 2
www.fromdual.com
ALTERTABLEcustomers
ADDINDEX(name);
CREATETABLE`customers`(
`customer_id`smallint(5)unsigned
Verbesserung:
20 ms 5 ms
,`name`varchar(64)DEFAULTNULL
,PRIMARYKEY(`customer_id`)
,KEY`name`(`name`)
)
+-----------+------+---------------+------+---------+-------+------+
| table
| type | possible_keys | key | key_len | ref
| rows |
+-----------+------+---------------+------+---------+-------+------+
| customers | ref | name
| name | 67
| const |
1 |
+-----------+------+---------------+------+---------+-------+------+
10 / 30
JOIN Klausel
www.fromdual.com
EXPLAINSELECT*
FROMcustomersASc
JOINordersASoONc.customer_id=o.customer_id
WHEREc.name='FromDual';
++++++++
|table|type|possible_keys|key|key_len|ref|rows|
++++++++
|c|ref|PRIMARY,name|name|67|const|1|
|o|ALL|NULL|NULL|NULL|NULL|1045105|
++++++++
Verbesserung: 450 ms 6 ms
ALTERTABLEorders
ADDINDEX(customer_id);
+-------+------+---------------+-------------+---------+---------------+------+
| table | type | possible_keys | key
| key_len | ref
| rows |
+-------+------+---------------+-------------+---------+---------------+------+
| c
| ref | PRIMARY,name | name
| 67
| const
|
1 |
| o
| ref | customer_id
| customer_id | 3
| c.customer_id |
8 |
+-------+------+---------------+-------------+---------+---------------+------+
11 / 30
Tabellen sortieren oder gruppieren
www.fromdual.com
ORDERBY,GROUPBY
EXPLAINSELECT*
FROMcontactsASc
WHERElast_name='Sennhauser'
ORDERBYlast_name,first_name;
++++++
|table|type|key|rows|Extra|
++++++
|c|ref|last_name|1561|Usingindexcondition;Usingwhere;Usingfilesort|
++++++
Verbesserung : 20 ms 7 ms
ALTERTABLEcontacts
ADDINDEX(last_name,first_name);
++++++
|table|type|key|rows|Extra|
++++++
|c|ref|last_name_2|1561|Usingwhere;Usingindex|
++++++
12 / 30
Covering Indices
www.fromdual.com
EXPLAIN
SELECTcustomer_id,amount
FROMordersASo
WHEREcustomer_id=59349;
++++++
|table|type|key|rows|Extra|
++++++
Und jetzt?
|o|ref|customer_id|15|NULL|
++++++
ALTERTABLEorders
ADDINDEX(customer_id,amount);
++++++
|table|type|key|rows|Extra|
++++++
|o|ref|customer_id_2|15|Usingindex|
++++++
13 / 30
Vorteil von Covering Indices
www.fromdual.com
Warum sind Covering Indices vorteilhaft?
ohne
mit
14 / 30
Finden von fehlenden Indices
www.fromdual.com
ER Diagramm? :-(
Hngt hauptschlich von der Business
Logik ab...
Wie FINDET man sie? --> Slow Query Log
MySQL Variablen:
Seit v5.1
on-line!
+-------------------------------+----------+
| Variable_name
| Value
|
+-------------------------------+----------+
| log_queries_not_using_indexes | ON
|
| long_query_time
| 0.250000 |
| min_examined_row_limit
| 100
|
| slow_query_log
| ON
|
| slow_query_log_file
| slow.log |
+-------------------------------+----------+
15 / 30
Indices sind nicht nur gut!
www.fromdual.com
Indices brauchen Platz (Platte, heisse Daten im RAM!)
Indices brauchen Zeit fr Wartung (CPU, RAM, I/O)
Optimizer braucht Zeit um herauszufinden, welchen
Index er nehmen soll.
Manchmal ist der Optimizer vllig verwirrt und trifft eine
falsche Entscheidung wenn zu viele (hnliche) Indices
vorhanden sind.
Es gilt die richtige Balance
fr schnelle Abfragen und
optimalen Indices zu finden.
16 / 30
Kleiner Index schnelle Abfrage
www.fromdual.com
Passt besser in RAM (weniger I/O)
Hhere Datendichte (Rows/Block)
Weniger CPU Zyklen
Prefixed Index:
ADDINDEXpre_ind(hash(8));
17 / 30
Indices vermeiden
Vermeide redundante (daher unntige) Indices
Wie kann so was passieren?
www.fromdual.com
Entwickler 1: Kreiert einen Foreign Key Constraint erledigt
Entwickler 2: Ouu! Abfrage ist langsam Oli hat gesagt:
Index anlegen erledigt
Entwickler 3: Ouu! Anfrage ist langsam Entwickler 2 ist ein
Idiot! Index anlegen erledigt
Frameworks vs. Entwickler
Upgrade Prozess vs. DevOps
Vermeide Indexes welche nicht benutz/bentigt werden!
18 / 30
Wie findet man solche Indices?
www.fromdual.com
SHOWCREATETABLE...\G
mysqldumpnodata>structure_dump.sql
Seit MySQL 5.6: PERFORMANCE_SCHEMA
Percona Server / MariaDB: Userstats
http://fromdual.com/mysql-performance-schema-hints
SELECT
FROM
WHERE
AND
ORDER
object_schema, object_name, index_name
performance_schema.table_io_waits_summary_by_index_usage
index_name IS NOT NULL
count_star = 0
BY object_schema, object_name;
19 / 30
Vermeide partiell redundante
Indices
www.fromdual.com
INDEX(city,last_name,first_name)
INDEX(city,last_name)
INDEX(city)
INDEX(last_name,city) ???
INDEX(first_name,last_name) !!!
20 / 30
Schlechte Selektivitt
Weg mit Indices mit schlechter Selektivitt (~= geringe
Kardinalitt)
Kandidaten sind:
status
gender
active
Wie findet man ob ein Feld eine
schlechte Selektivitt hat?
Indices (und Joins) sind teuer!!!
www.fromdual.com
SELECT status, COUNT(*)
FROM orders
GROUP BY status;
+--------+--------+
| status | cnt
|
+--------+--------+
|
0 | 393216 |
|
1 | 262144 |
|
2 |
12 |
|
3 |
36 |
|
4 |
24 |
|
5 |
4 |
|
6 |
8 |
+--------+--------+
Break-even zwischen 15% und 66%
Schauen wir mal ob der MySQL
Optimizer davon weiss... :-)
21 / 30
Optimizer liegt falsch!
SELECT * FROM orders WHERE status = 2;
+--------+------+---------------+--------+------+
| table | type | possible_keys | key
| rows |
+--------+------+---------------+--------+------+
| orders | ref | status
| status |
12 |
+--------+------+---------------+--------+------+
SELECT * FROM orders WHERE status = 0;
1.43 s
+--------+------+---------------+--------+--------+
| table | type | possible_keys | key
| rows
|
+--------+------+---------------+--------+--------+
| orders | ref | status
| status | 327469 |
+--------+------+---------------+--------+--------+
5.6.12 (nach ANALYZETABLE)
www.fromdual.com
SELECT status, COUNT(*)
FROM orders
GROUP BY status;
+--------+--------+
| status | cnt
|
+--------+--------+
|
0 | 393216 |
|
1 | 262144 |
|
2 |
12 |
|
3 |
36 |
|
4 |
24 |
|
5 |
4 |
|
6 |
8 |
+--------+--------+
SELECT * FROM orders IGNORE INDEX (status) WHERE status = 0;
0.44 s
+--------+------+---------------+------+--------+
| table | type | possible_keys | key | rows
|
+--------+------+---------------+------+--------+
| orders | ALL | NULL
| NULL | 654939 |
+--------+------+---------------+------+--------+
22 / 30
InnoDB PK und SK
www.fromdual.com
InnoDB kennt
Primary Keys und
Secondary Keys
23 / 30
Geclusterter Index
www.fromdual.com
InnoDB: Daten = Bltter des Primary Keys
Heisst auch Index Clustered Table (IOT)
Daten sind sortiert wie PK (Index ist sortiert)!
PK beeinflusst Lokalitt der Daten (physische
Lage)
AUTO_INCREMENT ~= Sortieren nach Zeit!
Gut in vielen Fllen
Wenn heisse Daten = aktuelle Daten
Schlecht fr Zeitreihen
Wenn heisse Daten = Daten pro Objekt
24 / 30
Beispiel: InnoDB
www.fromdual.com
A_Itsv_idxposypos...
117:30#42x,y,...
217:30#43x,y,...
317:30#44x,y,...
#42
alle 2'
...
200117:32#42x,y,...
200217:32#43x,y,...
200317:32#44x,y,...
Q1: in Zeilen? ~ 2000 Zeilen
A1: 1 Zeile ~ 100 byte
Q2: in bytes? ~ 200 kbyte
Q3: Default InnoDB block size? default: 16 kbyte
Q4: Avg. # Zeilen von LKW #42 in 1 InnoDB block? ~ 1
A2: 3 d und 720 pt/d ~2000 pt ~ 2000 rec ~ 2000 blk
Q5: Wie lange dauert das und warum (32 Mbyte)?
~ 2000 IOPS ~ 10s random read!!!
S: Alles im RAM oder starkes I/O-System oder ?
2000 LKWs
die letzten 3 Tage
25 / 30
Geclusterter PK rettet den Tag!
www.fromdual.com
tsv_idxposypos...
17:30#42x,y,...
17:32#42x,y,...
17:34#42x,y,...
...
#42
alle 2'
17:30#43x,y,...
17:32#43x,y,...
17:34#43x,y,...
...
17:30#44x,y,...
2000 LKWs
Q1: Avg. # Zeilen von LKW #42 in 1 InnoDB block? ~ 120
A1: 3 d und 720 pt/d ~2000 pt ~ 2000 rec ~ 20 blk
Q2: Wie lange dauert das und warum (320 kbyte)?
~ 1-2 IOPS ~ 10-20 ms sequential read!
S: Wow f=50 schneller! Nachteile?
die letzten 3 Tage
26 / 30
Index Hints
www.fromdual.com
MySQL Optimizer liegt manchmal falsch!
Wir mssen ihm nachhelfen...
Index Hints (Hinweise) sind:
USEINDEX(ind1,ind2)
Schau nur diese Indices an...
FORCEINDEX(ind3)
Nimm diesen Index ohne weiter nachzudenken
IGNOREINDEX(ind1,ind3)
Schau Dir alle Indices ausser diese an
Hints nur als allerletzte Rettung verwenden!
27 / 30
MySQL Variablen
www.fromdual.com
MySQL Variablen welche Indices beeinflussen
MyISAM: key_buffer_size
InnoDB: innodb_buffer_pool_size
InnoDB Change Buffer
innodb_change_buffer_max_size
innodb_change_buffering
Adaptive Hash Index (AHI)
MySQL 5.6.3 / 5.5.14 Indexlnge 767 3072 bytes
innodb_large_prefix
28 / 30
Wir suchen noch:
www.fromdual.com
Datenbank Enthusiast/in fr Support /
remote-DBA / Beratung
29 / 30
Q&A
www.fromdual.com
Fragen ?
Diskussion?
Anschliessend ist noch Zeit fr ein persnliches Gesprch...
FromDual bietet neutral und unabhngig:
MySQL Beratung
Remote-DBA
Support fr MySQL, Galera Cluster, MariaDB und Percona Server
MySQL Schulung
www.fromdual.com/presentations
30 / 30
