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.

Avatar for Anna Filina

Anna Filina

May 02, 2013
Tweet

More Decks by Anna Filina

Other Decks in Programming

Transcript

  1. FooLab La vitesse importe • Qu’est-ce qui est lent? •

    Indexes. • Meilleures requêtes. • Checklist. 2
  2. 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); }
  3. FooLab Lazy loading • Données sur demande. • Utilisez les

    LEFT JOIN. 6 echo $picture->album->user->name;
  4. FooLab Compter les requêtes • Manuel: encapsuler requêtes dans une

    fonction. • Auto: ORMs (comme Doctrine) ont des listener. 8
  5. 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
  6. 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;
  7. 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 | +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
  8. 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
  9. 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);
  10. 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
  11. 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);
  12. 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
  13. 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);
  14. 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
  15. 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
  16. 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.
  17. FooLab Petits indexes 28 • BIGINT est plus lent qu’un

    TINYINT. • UNSIGNED pour doubler la capacité.
  18. FooLab Fonctions 30 • Éviter fonctions sur champ indexé. •

    L’index sera ignoré. SELECT id, title FROM picture WHERE YEAR(create_date) >= 2011;
  19. 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;
  20. 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);
  21. 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
  22. FooLab Ségmentez les tables 34 • MySQL peut partitionner les

    tables. • Crée plusieurs fichiers. • Affiche les données dans une seule table.
  23. 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.
  24. 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
  25. 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.
  26. 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