Accélerez votre base de données

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