FooLab
http://foolab.ca
@foolabca
Accélérez votre base
de données
PHP Québec - 2 mai 2013
Slide 2
Slide 2 text
FooLab
La vitesse importe
• Qu’est-ce qui est lent?
• Indexes.
• Meilleures requêtes.
• Checklist.
2
Slide 3
Slide 3 text
FooLab
Anna Filina
3
Groupe
d’utilisateurs
Conférence
multi-techno
FooLab
Direction et
sauvetage de projet
@afilina
Slide 4
Slide 4 text
FooLab
Qu’est-ce qui est lent?
Slide 5
Slide 5 text
FooLab
Multitude de requêtes
• Boucles:
• Réduisez le nombre de requêtes.
• Vérifiez le lazy loading dans votre ORM.
5
for (id in pictures) {
query_pic_details(id);
}
Slide 6
Slide 6 text
FooLab
Lazy loading
• Données sur demande.
• Utilisez les LEFT JOIN.
6
echo $picture->album->user->name;
FooLab
Compter les requêtes
• Manuel: encapsuler requêtes dans une fonction.
• Auto: ORMs (comme Doctrine) ont des listener.
8
Slide 9
Slide 9 text
FooLab
Slow Query Log
• Ouvrir my.cnf
• Requêtes de 0.5s ou plus seront enregistrées.
• Analyzer requêtes dans le fichier de log.
9
slow_query_log=ON
long_query_time=0.5
slow_query_log_file=/path/to/file
Slide 10
Slide 10 text
FooLab
Example de schéma
10
200
users
100
albums chaque
100
photos chaque
2,000,000 photos
Slide 11
Slide 11 text
FooLab
Requêtes simple
• Toutes les photos de l’utilisateur #1
• 38 secondes.
11
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1;
Slide 12
Slide 12 text
FooLab
Pourquoi est-ce lent?
Slide 13
Slide 13 text
FooLab
Explain
13
• Préfixer avec EXPLAIN:
• Chaque rangée = table scannée:
EXPLAIN SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1;
+----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
| 1 | SIMPLE | album | ALL | NULL | NULL | NULL | NULL | 20000 | Using where |
| 1 | SIMPLE | picture | ALL | NULL | NULL | NULL | NULL | 2000000 | Using where; Using join buffer |
+----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
Slide 14
Slide 14 text
FooLab
Explain
14
• Détails:
• À cause du join,
on scanne tous les albums pour chaque photo.
• Total de 40 milliards de rangées scannées.
table : album
key : NULL
rows : 20,000
table : picture
key : NULL
rows : 2,000,000
Slide 15
Slide 15 text
FooLab
Indexes
Slide 16
Slide 16 text
FooLab
Métaphore
16
Parcourir toutes les pages d’un carnet
d’adresses...
... ou ouvrir à la bonne lettre.
Slide 17
Slide 17 text
FooLab
Ajout d’un index
17
• album.id
• Même requête: 2.38 sec.
• On sauve 36 secondes sur chaque requête!
ALTER TABLE album ADD PRIMARY KEY(id);
Slide 18
Slide 18 text
FooLab
Sous le capot
18
• Détails:
• Avec l’index,
on trouve le bon album tout de suite.
• Total de 2 millions de rangées scannées.
table : album
key : PRIMARY
rows : 1
table : picture
key : NULL
rows : 2,000,000
Slide 19
Slide 19 text
FooLab
Ajout d’un autre index
19
• picture.album_id
• Maintenant 0.12 sec.
• 317 plus vite que l’original.
ALTER TABLE picture ADD INDEX(album_id);
Slide 20
Slide 20 text
FooLab
Sous le capot
20
• Détails:
• Total 200,000 rangées scannées.
table : album
key : NULL
rows : 20,000
table : picture
key : album_id
rows : 100
Slide 21
Slide 21 text
FooLab
Ajour d’un dernier index
21
• album.user_id
• Maintenant 0.10 sec.
• Une réduction de 17%.
ALTER TABLE album ADD INDEX(user_id);
Slide 22
Slide 22 text
FooLab
Sous le capot
22
• Details:
• Avec ces deux indexes, on scanne 100 photos.
• Total 10,000 rangées scannées.
table : album
key : user_id
rows : 100
table : picture
key : album_id
rows : 100
Slide 23
Slide 23 text
FooLab
Indexes utilisés
23
• Clés étrangères dans les JOIN.
• Colonnes dans les WHERE et ORDER BY.
FooLab
Est-ce qu’on peut
encore optimiser?
On est déjà assez rapide.
Slide 26
Slide 26 text
FooLab
Douanes
26
• 5 douaniers, 1 min par voyageur.
• 5 voyageurs / min = fluide.
• 6 voyageurs / min = file d’attente se bâtit.
• 1000 voyageurs / min = 3h d’attente après 1 min.
Slide 27
Slide 27 text
FooLab
Les ms comptent
27
File d’attente Fluidité
Slide 28
Slide 28 text
FooLab
Petits indexes
28
• BIGINT est plus lent qu’un TINYINT.
• UNSIGNED pour doubler la capacité.
Slide 29
Slide 29 text
FooLab
Astuces pour
meilleures requêtes
Slide 30
Slide 30 text
FooLab
Fonctions
30
• Éviter fonctions sur champ indexé.
• L’index sera ignoré.
SELECT id, title FROM picture
WHERE YEAR(create_date) >= 2011;
Slide 31
Slide 31 text
FooLab
Limit
31
• Maintenant 0.0009 sec.
• 100 fois plus rapide.
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1
LIMIT 25;
Slide 32
Slide 32 text
FooLab
Autres contraintes
32
• Gamme:
• Indexez cette colonne:
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1
AND picture.id BETWEEN 26 AND 50; # 0.023 sec
ALTER TABLE picture ADD INDEX(id);
Slide 33
Slide 33 text
FooLab
Autres contraintes
33
• Clause IN:
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1
AND picture.id IN (15,26,29,32,45); # 0.048 sec
Slide 34
Slide 34 text
FooLab
Ségmentez les tables
34
• MySQL peut partitionner les tables.
• Crée plusieurs fichiers.
• Affiche les données dans une seule table.
Slide 35
Slide 35 text
FooLab
Archives
35
• Engin de stockage ARCHIVE:
• Ne supporte pas UPDATE ou DELETE
• Très rapide pour le SELECT
• Archivez les anciennes transactions, articles, etc.
Slide 36
Slide 36 text
FooLab
Ségmentez les BD
36
• Partitionnement horizontal, sharding.
• Users 1-1000 et leurs données dans BD #1
• Users 1001-2000 et leurs données dans BD #2
Slide 37
Slide 37 text
FooLab
Checklist
37
✓Comptez requêtes.
✓Slow Query Log.
✓Analysez rangées
scannées avec EXPLAIN.
✓Essayez divers indexes,
enlevez les superflus.
✓Petits indexes,
numériques de
préférence.
✓Pas de fonctions sur
colonnes indexées.
✓Limitez le nombre de
résultats.
✓Ségmentez tables et BD.