Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Accélerez votre base de données

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.

Anna Filina
PRO

May 02, 2013
Tweet

More Decks by Anna Filina

Other Decks in Programming

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:
    [email protected]
    39

    View Slide