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
   

   View Slide

2. FooLab
   La vitesse importe
   • Qu’est-ce qui est lent?
   • Indexes.
   • Meilleures requêtes.
   • Checklist.
   2
   

   View Slide

3. FooLab
   Anna Filina
   3
   Groupe
   d’utilisateurs
   Conférence
   multi-techno
   FooLab
   Direction et
   sauvetage de projet
   @afilina
   

   View Slide

4. FooLab
   Qu’est-ce qui est lent?
   

   View Slide

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);
   }
   

   View Slide

6. FooLab
   Lazy loading
   • Données sur demande.
   • Utilisez les LEFT JOIN.
   6
   echo $picture->album->user->name;
   

   View Slide

7. FooLab
   Multitude de requêtes
   • Code imbriqué.
   • Essayez get_first_thumb.
   7
   

   View Slide

8. FooLab
   Compter les requêtes
   • Manuel: encapsuler requêtes dans une fonction.
   • Auto: ORMs (comme Doctrine) ont des listener.
   8
   

   View Slide

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
   

   View Slide

10. FooLab
    Example de schéma
    10
    200
    users
    100
    albums chaque
    100
    photos chaque
    2,000,000 photos
    

    View Slide

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;
    

    View Slide

12. FooLab
    Pourquoi est-ce lent?
    

    View Slide

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 |
    +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
    

    View Slide

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
    

    View Slide

15. FooLab
    Indexes
    

    View Slide

16. FooLab
    Métaphore
    16
    Parcourir toutes les pages d’un carnet
    d’adresses...
    ... ou ouvrir à la bonne lettre.
    

    View Slide

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);
    

    View Slide

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
    

    View Slide

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);
    

    View Slide

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
    

    View Slide

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);
    

    View Slide

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
    

    View Slide

23. FooLab
    Indexes utilisés
    23
    • Clés étrangères dans les JOIN.
    • Colonnes dans les WHERE et ORDER BY.
    

    View Slide

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
    

    View Slide

25. FooLab
    Est-ce qu’on peut
    encore optimiser?
    On est déjà assez rapide.
    

    View Slide

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.
    

    View Slide

27. FooLab
    Les ms comptent
    27
    File d’attente Fluidité
    

    View Slide

28. FooLab
    Petits indexes
    28
    • BIGINT est plus lent qu’un TINYINT.
    • UNSIGNED pour doubler la capacité.
    

    View Slide

29. FooLab
    Astuces pour
    meilleures requêtes
    

    View Slide

30. FooLab
    Fonctions
    30
    • Éviter fonctions sur champ indexé.
    • L’index sera ignoré.
    SELECT id, title FROM picture
    WHERE YEAR(create_date) >= 2011;
    

    View Slide

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;
    

    View Slide

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);
    

    View Slide

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
    

    View Slide

34. FooLab
    Ségmentez les tables
    34
    • MySQL peut partitionner les tables.
    • Crée plusieurs fichiers.
    • Affiche les données dans une seule table.
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

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.
    

    View Slide

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
    

    View Slide

39. FooLab
    Prochaines étapes
    • Lien des slides sur Twitter:
    @afilina
    • Envoyez votre feedback:
    anna@foolab.ca
    39
    

    View Slide