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.

B3b2139e4f2c0eca4efe2379fcebc1c5?s=128

Anna Filina

May 02, 2013
Tweet

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