Accélerez votre base de données

Est-ce que vos requêtes sont lentes? Apprenez comment les accélérer grâce à un meilleur SQL et en choisissant des indexes significatifs. Voyez ce qui marche bien et ce qui marche moins bien. À la fin, je répondrais aux questions pour vos cas spécifiques.

Transcript

1. FooLab http://foolab.ca @foolabca Accélérez votre base de données PHP Québec - 2 mai 2013
2. FooLab La vitesse importe • Qu’est-ce qui est lent? • Indexes. • Meilleures requêtes. • Checklist. 2
3. FooLab Anna Filina 3 Groupe d’utilisateurs Conférence multi-techno FooLab Direction et sauvetage de projet @afilina
4. FooLab Qu’est-ce qui est lent?
5. 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); }
6. FooLab Lazy loading • Données sur demande. • Utilisez les LEFT JOIN. 6 echo $picture->album->user->name;
7. FooLab Multitude de requêtes • Code imbriqué. • Essayez get_first_thumb. 7
8. FooLab Compter les requêtes • Manuel: encapsuler requêtes dans une fonction. • Auto: ORMs (comme Doctrine) ont des listener. 8
9. 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
10. FooLab Example de schéma 10 200 users 100 albums chaque 100 photos chaque 2,000,000 photos
11. 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;
12. FooLab Pourquoi est-ce lent?
13. 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 | +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
14. 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
15. FooLab Indexes
16. FooLab Métaphore 16 Parcourir toutes les pages d’un carnet d’adresses... ... ou ouvrir à la bonne lettre.
17. 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);
18. 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
19. 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);
20. 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
21. 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);
22. 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
23. FooLab Indexes utilisés 23 • Clés étrangères dans les JOIN. • Colonnes dans les WHERE et ORDER BY.
24. FooLab Critères 24 • Fréquence de changement. • Sélectivité: combien de valeurs différentes? • Bons exemples: • Mauvais exemples: user.gender picture.views user.id picture.date
25. FooLab Est-ce qu’on peut encore optimiser? On est déjà assez rapide.
26. 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.
27. FooLab Les ms comptent 27 File d’attente Fluidité
28. FooLab Petits indexes 28 • BIGINT est plus lent qu’un TINYINT. • UNSIGNED pour doubler la capacité.
29. FooLab Astuces pour meilleures requêtes
30. FooLab Fonctions 30 • Éviter fonctions sur champ indexé. • L’index sera ignoré. SELECT id, title FROM picture WHERE YEAR(create_date) >= 2011;
31. 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;
32. 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);
33. 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
34. FooLab Ségmentez les tables 34 • MySQL peut partitionner les tables. • Crée plusieurs fichiers. • Affiche les données dans une seule table.
35. 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.
36. 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
37. 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.
38. FooLab Lectire • Engins de stockage: http://www.linux.org/article/view/ an-introduction-to-mysql-storage-engines • Sharding: http://www.jurriaanpersyn.com/archives/ 2009/02/12/database-sharding-at-netlog-with-mysql- and-php/ 38
39. FooLab Prochaines étapes • Lien des slides sur Twitter: @afilina • Envoyez votre feedback: anna@foolab.ca 39