Montréal JUG 2024 - MySQL Index et Histogrammes

MySQL, la base de données Open Source la plus populaire, est très facile d'utilisation. Mais vous pouvez améliorer vos requêtes en utilisant des index et des histogrammes. Cette session vous apprendra à créer des index optimaux pour vos requêtes et découvrir la puissance des histogrammes.
Vous découvrirez également comment afficher et comprendre le plan d'exécution de vos requêtes, pour vous aider à trouver celles qui ont besoin d'être optimisées.

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL Montréal JUG - Février 2024 Index et Histogrammes avec MySQL Comment accélèrent-ils vraiment vos requêtes ?
2. Présentation about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2
3. @lefred MySQL Evangelist utilse MySQL depuis la version 3.20 adepte de devops cloud native: né et élevé en h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3
4. Quel est le sujet principal de cette session ? Copyright @ 2024 Oracle and/or its affiliates. 4
5. Vitesse et Performance ! Personne ne se plaint jamais que la base de données est trop rapide ! Copyright @ 2024 Oracle and/or its affiliates. 5
6. Vitesse et Performance ! Personne ne se plaint jamais que la base de données est trop rapide ! C'est souvent l'inverse... la faute à la base de données ! Copyright @ 2024 Oracle and/or its affiliates. 5
7. Vitesse et Performance ! Personne ne se plaint jamais que la base de données est trop rapide ! C'est souvent l'inverse... la faute à la base de données ! Accélérer les requêtes n'est pas un "art obscur"... mais comprendre comment accélérer les requêtes est souvent considéré comme de la magie. Copyright @ 2024 Oracle and/or its affiliates. 5
8. Vitesse et Performance ! Personne ne se plaint jamais que la base de données est trop rapide ! C'est souvent l'inverse... la faute à la base de données ! Accélérer les requêtes n'est pas un "art obscur"... mais comprendre comment accélérer les requêtes est souvent considéré comme de la magie. Nous examinerons donc l'utilisation correcte des index et des histogrammes et nous verrons comment maintenir l'équilibre de la charge (workload). Copyright @ 2024 Oracle and/or its affiliates. 5
9. Aujourd'hui nous n'aborderons pas: La con guration du système OS MySQL Hardware Réseau/Cloud C'est un sujet aride ! N'essayez pas de tout retenir ce soirt, les "slides" seront disponibles (h ps://speakerdeck.com/lefred) Copyright @ 2024 Oracle and/or its affiliates. 6
10. L'Étoile du Nord Le Temps de Réponse des Requêtes (Query Respone Time) Copyright @ 2024 Oracle and/or its affiliates. 7
11. Daniel Nichter, E cient MySQL Performance - Best Practices and Techniques, O'Reilly, 2021 Query Response time Query response time is the only metric anyone truly cares about [...] because query response time is the only metric we experience. When a query takes 7.5 seconds to execute, we experience 7.5 seconds of impatience. That same query might examine a million rows, but we don't experience a million rows examined. Our time is precious.(*) Copyright @ 2024 Oracle and/or its affiliates. 8
12. Notre Objectif est donc... de réduire le "Query Response Time" Copyright @ 2024 Oracle and/or its affiliates. 9
13. Les Mauvaise Requêtes À la recherche du vilain petit canard Copyright @ 2024 Oracle and/or its affiliates. 10
14. Qu'est-ce qu'une mauvaise requête ? Les mauvaises requêtes peuvent être classée dans 2 catégories: Copyright @ 2024 Oracle and/or its affiliates. 11
15. les requêtes appelées bien trop souvent les requêtes bien trop lentes qui e ecutent un scan complet de tables (full table scan) qui utilisent un chier pour trier ( lesort) qui font appel à des tables temporaires Qu'est-ce qu'une mauvaise requête ? Les mauvaises requêtes peuvent être classée dans 2 catégories: Copyright @ 2024 Oracle and/or its affiliates. 11
16. Qu'est-ce qu'une mauvaise requête ? Copyright @ 2024 Oracle and/or its affiliates. 12
17. S'il devait n'y en avoir qu'une seule ? Si vous ne pouviez optimiser qu'une seule requête, la meilleure candidate devrait être la requêtes qui consomme le plus de temps d'exécution ("latency"). Copyright @ 2024 Oracle and/or its affiliates. 13
18. S'il devait n'y en avoir qu'une seule ? Si vous ne pouviez optimiser qu'une seule requête, la meilleure candidate devrait être la requêtes qui consomme le plus de temps d'exécution ("latency"). sys Schema contient toutes les informations nécessaires pour trouver ce vilain petit canard: Copyright @ 2024 Oracle and/or its affiliates. 13
19. S'il devait n'y en avoir qu'une seule ? Si vous ne pouviez optimiser qu'une seule requête, la meilleure candidate devrait être la requêtes qui consomme le plus de temps d'exécution ("latency"). sys Schema contient toutes les informations nécessaires pour trouver ce vilain petit canard: SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G Copyright @ 2024 Oracle and/or its affiliates. 13
20. S'il devait n'y en avoir qu'une seule ? Si vous ne pouviez optimiser qu'une seule requête, la meilleure candidate devrait être la requêtes qui consomme le plus de temps d'exécution ("latency"). sys Schema contient toutes les informations nécessaires pour trouver ce vilain petit canard: SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G Copyright @ 2024 Oracle and/or its affiliates. * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * schema_name: piday schema_name: piday tot_lat: tot_lat: 4.29 4.29 h h exec_count: exec_count: 5 5 latency_per_call: latency_per_call: 51.51 51.51 min min query_sample_text: query_sample_text: select select a a. .device_id device_id, , max max( (a a. .value value) ) as as ` `max temp max temp` `, , min min( (a a. .value value) ) as as ` `min temp min temp` `, , avg avg( (a a. .value value) ) as as ` `avg temp avg temp` `, , max max( (b b. .value value) ) as as ` `max humidity max humidity` `, , min min( (b b. .value value) ) as as ` `min humidity min humidity` `, , avg avg( (b b. .value value) ) as as ` `avg humidity avg humidity` ` from from temperature_history a temperature_history a join join humidity_history b humidity_history b on on b b. .device_id device_id= =a a. .device_id device_id where where date date( (a a. .time_stamp time_stamp) ) = = date date( (now now( () )) ) and and date date( (b b. .time_stamp time_stamp) )= =date date( (now now( () )) ) group group by by device_id device_id _ _ 13
21. Pour plus d'info à propos des requêtes Sys Schema contient toutes les données nécessaires dans ces tables: statements_with_full_table_scans statements_with_runtimes_in_95th_percentile statements_with_sorting statements_with_temp_tables Copyright @ 2024 Oracle and/or its affiliates. 14
22. Pour plus d'info à propos des requêtes Sys Schema contient toutes les données nécessaires dans ces tables: statements_with_full_table_scans statements_with_runtimes_in_95th_percentile statements_with_sorting statements_with_temp_tables Depuis MySQL 8.0, vous avez la possibilité de joindre la table performance_schema.events_statements_summary_by_digest pour récupérer un example de requête qui peut être utilisé. Copyright @ 2024 Oracle and/or its affiliates. 14
23. Pour plus d'info à propos des requêtes Sys Schema contient toutes les données nécessaires dans ces tables: statements_with_full_table_scans statements_with_runtimes_in_95th_percentile statements_with_sorting statements_with_temp_tables Depuis MySQL 8.0, vous avez la possibilité de joindre la table performance_schema.events_statements_summary_by_digest pour récupérer un example de requête qui peut être utilisé. Nous verrons ultérieurement la signi cation de ces tables... un peut de patience ;) Copyright @ 2024 Oracle and/or its affiliates. 14
24. Copyright @ 2024 Oracle and/or its affiliates. 15
25. L'Optimizer un peu de théorie Copyright @ 2024 Oracle and/or its affiliates. 16
26. L'Optimizer de MySQL Il faut considerer l'Optimizer comme le cerveau et le système nerveux de MySQL L'optimization des requêtes fait partie de beaucoup de Système de Base de Données Relationelles L'objectif de l'optimiseur de requêtes est de déterminer la méthode la plus e cace en considérant tous les plans possibles (query plans) (h ps://en.wikipedia.org/wiki/Query_optimization) Copyright @ 2024 Oracle and/or its affiliates. 17
27. L'Optimizer MySQL - estimation L'un des problèmes les plus compliqués dans l'optimization de requêtes est d'estimer de manière précise le coût de chaques plans alternatifs Ces coûts sont le résultat de modèles mathématiques qui sont basés sur des estimations de la cardinalités des données, à travers chaques plans. Copyright @ 2024 Oracle and/or its affiliates. 18
28. L'Optimizer MySQL - évaluation des options L'Optimizer veut extraire vos données de la manière la plus rentable possible. Comme un plani cateur d'itinéraire, le coût est basé sur des statistiques historiques. Et ces statistiques peuvent changer pendant que l'optimiseur travaille. Ainsi, comme dans le cas d'un embouteillage, d'une route délabrée ou d'un autre problème de circulation, l'optimiseur peut prendre de mauvaises décisions pour la situation actuelle... mais c'est très rare ! La décision nale de l'optimiseur est appelée Plan d'exécution de la requête (ou QEP, ou Query Plan). MySQL veut optimiser chaque requête à chaque fois qu'il la voit (il n'y a pas de verrouillage du plan de requête comme avec Oracle). Copyright @ 2024 Oracle and/or its affiliates. 19
29. 120 si votre requête comporte cinq jointures, l'optimiseur peut avoir à évaluer 120 options di érentes 5! (5 * 4 * 3 * 2 * 1) Copyright @ 2024 Oracle and/or its affiliates. 20
30. The Query Execution Plan EXPLAIN: le compagnon des DBAs Copyright @ 2024 Oracle and/or its affiliates. 21
31. EXPLAIN est la commande utilisée pour obtenir le plan d'exécution d'une requête, y compris des informations sur la manière dont les tables sont jointes et dans quel ordre, les index utilisés et l'estimation du nombre de lignes, ... EXPLAIN - La Syntaxe Copyright @ 2024 Oracle and/or its affiliates. 22
32. EXPLAIN - Exemple Copyright @ 2024 Oracle and/or its affiliates. 23
33. EXPLAIN - Exemple il s'agit d'une ESTIMATION de la façon dont MySQL exécuterait la requête car elle n'est pas exécutée ! Copyright @ 2024 Oracle and/or its affiliates. 23
34. system: la table contient exactement 1 enregistrement const: au maximum un enreigstrement dans la table correspond eq_ref: la table est la celle de droite dans une jointure où la condition est sur une clef unique PK ou non nulle. ref: la table est ltrée par un index secondaire non unique. ref_or_null: la même chose que ref mais la colonne ltrée peut aussi être NULL. index_merge: l'optimiseur choisit une combinaison de deux index ou plus pour résoudre un ltre qui inclut un OU ou un ET entre des colonnes dans di érents index. fulltext: utilisation d'un index full text pour ltrer la table. EXPLAIN - Type d'accès Copyright @ 2024 Oracle and/or its affiliates. 24
35. range: utilisation d'un index pour rechercher plusieurs valeurs, soit en séquence, soit en groupes. index: l'Optimizer a choisi d'e ectuer un scan complet de l'index. ALL: scan complet de la table !! EXPLAIN - Type d'accès (2) Vous trouverez beaucoup plus d'informations et d'exemples dans le chapitre 20, Analyzing Queries, du livre de Jesper Wisborg Krogh : MySQL 8 Query Performance Tuning, Apress, 2020. Copyright @ 2024 Oracle and/or its affiliates. 25
36. EXPLAIN - Exemple (avec un JOIN) Copyright @ 2024 Oracle and/or its affiliates. 26
37. Visual EXPLAIN Exemple avec MySQL Workbench Copyright @ 2024 Oracle and/or its affiliates. 27
38. EXPLAIN FORMAT=TREE - Exemple Copyright @ 2024 Oracle and/or its affiliates. 28
39. EXPLAIN FORMAT=TREE - Exemple Copyright @ 2024 Oracle and/or its affiliates. 28
40. EXPLAIN FORMAT=JSON - Exemple Copyright @ 2024 Oracle and/or its affiliates. 29
41. EXPLAIN FORMAT=JSON - Exemple Ceci est l'estimation la plus détaillée! Copyright @ 2024 Oracle and/or its affiliates. 29
42. Plus d'info sur EXPLAIN FORMAT Depuis MySQL 8.2, vous pouvez dé nir le format par défaut d'EXPLAIN en utilisant la variable explain_format: SQL SQL > > select select @ @@explain_format @explain_format; ; + +------------------+ ------------------+ | | @ @@explain_format @explain_format | | + +------------------+ ------------------+ | | TRADITIONAL TRADITIONAL | | + +------------------+ ------------------+ 1 1 row row in in set set ( (0.0002 0.0002 sec sec) ) SQL SQL > > set set explain_format explain_format= =tree tree; ; Maintenant, Il est également possible d'utiliser EXPLAIN FOR SCHEMA Copyright @ 2024 Oracle and/or its affiliates. 30
43. Plus d'info sur EXPLAIN FORMAT=JSON Avec MySQL 8.3, vous disposez d'un second format de rendu JSON. Ceci est con gurable en utilisant explain_json_format_version: SQL SQL > > set set explain_json_format_version explain_json_format_version= =2 2; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0001 0.0001 sec sec) ) SQL SQL > > explain explain format format= =json json select select * * from from city city join join country country on on ( (city city. .population population = = country country. .population population) )\G \G Copyright @ 2024 Oracle and/or its affiliates. 31
44. EXPLAIN FORMAT=JSON version 2 Copyright @ 2024 Oracle and/or its affiliates. 32
45. EXPLAIN vers une variable Avec le format JSON, il est désormais possible de sauvegarder la sortie de l'EXPLAIN dans une variable: SQL SQL > > explain explain format format= =json json into into @myqep @myqep select select * * from from city city join join country country on on ( (city city. .population population = = country country. .population population) ) ; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0003 0.0003 sec sec) ) SQL SQL > > select select json_extract json_extract( (@myqep @myqep, , "$.estimated_total_cost" "$.estimated_total_cost") ) as as query_cost query_cost; ; + +-------------------+ -------------------+ | | query_cost query_cost | | + +-------------------+ -------------------+ | | 96473.87597408294 96473.87597408294 | | + +-------------------+ -------------------+ 1 1 row row in in set set ( (0.0001 0.0001 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 33
46. Peut-on connaître les chiffres réels ? Copyright @ 2024 Oracle and/or its affiliates. 34
47. Coût estimé Statistiques d'exécution réelles Temps de retour du premier enregistrement Temps nécessaire pour retourner tous les enregistrements Nombre d'enregistrements retournés Nombre de boucles EXPLAIN ANALYZE Copyright @ 2024 Oracle and/or its affiliates. 35
48. Coût estimé Statistiques d'exécution réelles Temps de retour du premier enregistrement Temps nécessaire pour retourner tous les enregistrements Nombre d'enregistrements retournés Nombre de boucles instrumente et exécute la requête EXPLAIN ANALYZE Copyright @ 2024 Oracle and/or its affiliates. 35
49. EXPLAIN ANALYZE Exemple Copyright @ 2024 Oracle and/or its affiliates. 36
50. EXPLAIN ANALYZE - Exemple Copyright @ 2024 Oracle and/or its affiliates. 37
51. EXPLAIN ANALYZE - Exemple la durée est exprimée en millisecondes Copyright @ 2024 Oracle and/or its affiliates. 37
52. EXPLAIN ANALYZE - Exemple Copyright @ 2024 Oracle and/or its affiliates. 38
53. EXPLAIN ANALYZE - Exemple Copyright @ 2024 Oracle and/or its affiliates. 39
54. EXPLAIN ANALYZE - Exemple Copyright @ 2024 Oracle and/or its affiliates. 40
55. Plus avec EXPLAIN Il est également possible d'obtenir le QEP d'une connexion en cours en utilisant EXPLAIN FOR CONNECTION: SQL SQL > > show show processlist processlist; ; + +----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ ----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ | | Id Id | | User User | | Host Host | | db db | | Command Command | | Time Time | | State State | | Info Info | | + +----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ ----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ | | 8 8 | | root root | | localh localh | | NULL NULL | | Query Query | | 0 0 | | init init | | show show processlist processlist | | | | 9 9 | | root root | | localh localh | | test test | | Query Query | | 7 7 | | User User sleep sleep | | select select * *, , SLEEP SLEEP( (10 10) ) from from foo foo where where id id> >3 3 | | + +----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ ----+-------+--------+------+---------+-------+--------------+-----------------------------------------+ 3 3 rows rows in in set set, , 0 0 warning warning ( (0.0006 0.0006 sec sec) ) SQL SQL > > explain explain for for connection connection 9 9; ; + +----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ ----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ | | id id | | select_type select_type | | table table | | partit partit. . | | type type | | possible_keys possible_keys | | key key | | key_len key_len | | ref ref | | rows rows | | filtered filtered | | Extra Extra | | + +----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ ----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ | | 1 1 | | SIMPLE SIMPLE | | foo foo | | NULL NULL | | range range | | PRIMARY PRIMARY | | PRIMARY PRIMARY | | 4 4 | | NULL NULL | | 2 2 | | 100 100 | | Using Using where where | | + +----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ ----+-------------+-------+---------+-------+---------------+---------+---------+------+------+----------+-------------+ Copyright @ 2024 Oracle and/or its affiliates. 41
56. Plus avec EXPLAIN h ps://dev.mysql.com/doc/refman/8.3/en/explain.html h ps://dev.mysql.com/doc/refman/8.3/en/explain-output.html h ps://dev.mysql.com/doc/refman/8.3/en/explain-extended.html h ps://dev.mysql.com/doc/workbench/en/wb-performance-explain.html Copyright @ 2024 Oracle and/or its affiliates. 42
57. Index trouver rapidement des enregistrements avec des valeurs de colonnes spéci ques Copyright @ 2024 Oracle and/or its affiliates. 43
58. Index En informatique, dans les bases de données, un index est une structure de données utilisée et entretenue par le système de gestion de base de données (SGBD) pour lui perme re de retrouver rapidement les données. L'utilisation d'un index simpli e et accélère les opérations de recherche, de tri, de jointure ou d'agrégation e ectuées par le SGBD. L’index placé sur une table va perme re au SGBD d'accéder très rapidement aux enregistrements, selon la valeur d'un ou plusieurs champs. (h ps://fr.wikipedia.org/wiki/Index_(base_de_donn%C3%A9es)) Copyright @ 2024 Oracle and/or its affiliates. 44
59. MySQL prend en charge plusieurs types d'index: primary key / clustered index secondary index full-text index spatial index Index dans MySQL Copyright @ 2024 Oracle and/or its affiliates. 45
60. B-TREE HASH R-TREE (spatial data types) Inverted Lists (Fulltext) et les index peuvent avoir certaines propriétés: pre xe d'une colonne mutli-colonnes unique covering functional multi-value descending Index dans MySQL (2) Les principaux modes d'index dans MySQL sont: Copyright @ 2024 Oracle and/or its affiliates. 46
61. Clustered Indexes Chaque table InnoDB possède un index spécial, appelé clustered index, qui stocke les données des enregistrements. En général, le clustered index est synonyme de clef primaire. Copyright @ 2024 Oracle and/or its affiliates. 47
62. Clustered Indexes Chaque table InnoDB possède un index spécial, appelé clustered index, qui stocke les données des enregistrements. En général, le clustered index est synonyme de clef primaire. Je pourrais donner une session complète sur les clefs primaires Voir h ps://speakerdeck.com/lefred/all-you-need-to-know-about-mysql-innodb-primary- keys Copyright @ 2024 Oracle and/or its affiliates. 47
63. InnoDB Secondary Key Les index autres que le clustered index sont appelés index secondaires. Rappelez-vous que dans InnoDB, chaque enregistrement d'un index secondaire contient les colonnes de la clef primaire de l'enregistrement (la plus à droite), ainsi que les colonnes spéci ées pour l'index secondaire. InnoDB utilise ce e valeur de clef primaire pour rechercher l'enregistrement dans le clustered index. Si la clef primaire est longue, les index secondaires utilisent plus d'espace. Il est préférable d'avoir une clef primaire courte. Copyright @ 2024 Oracle and/or its affiliates. 48
64. Indexer sur le pré xe d'une colonne create create index index part_of_name part_of_name on on city city ( (name name( (10 10) )) ); ; Dans cet exemple, seuls les 10 premiers caractères sont indexés, ce qui permet d'économiser de l'espace et de la vitesse. Copyright @ 2024 Oracle and/or its affiliates. 49
65. Indexer sur le pré xe d'une colonne (2) Comparons la taille de cet index pré xe à celle d'un index utilisant la colonne complète: select select database_name database_name, , table_name table_name, , index_name index_name, , stat_value stat_value * * @ @@innodb_page_size @innodb_page_size as as size_in_bytes size_in_bytes from from mysql mysql. .innodb_index_stats innodb_index_stats where where stat_name stat_name = = 'size' 'size' and and database_name database_name= ='world' 'world' and and table_name table_name= ='city' 'city' and and index_name index_name like like '%name%' '%name%' order order by by size_in_bytes size_in_bytes desc desc; ; + +---------------+------------+--------------+---------------+ ---------------+------------+--------------+---------------+ | | database_name database_name | | table_name table_name | | index_name index_name | | size_in_bytes size_in_bytes | | + +---------------+------------+--------------+---------------+ ---------------+------------+--------------+---------------+ | | world world | | city city | | name_idx name_idx | | 212992 212992 | | | | world world | | city city | | part_of_name part_of_name | | 114688 114688 | | + +---------------+------------+--------------+---------------+ ---------------+------------+--------------+---------------+ Copyright @ 2024 Oracle and/or its affiliates. 50
66. Indexer sur le pré xe d'une colonne (3) Copyright @ 2024 Oracle and/or its affiliates. 51
67. Indexer sur le pré xe d'une colonne (4) Copyright @ 2024 Oracle and/or its affiliates. 52
68. Indexer sur le pré xe d'une colonne (4) Nous constatons que les deux index sur name sont candidats et que l'index partiel a la préférence. Copyright @ 2024 Oracle and/or its affiliates. 52
69. Index key_len Que signi e ce 40 ? Copyright @ 2024 Oracle and/or its affiliates. 53
70. Index key_len Que signi e ce 40 ? La colonne key_len ndique la longueur de la clé que MySQL a décidé d'utiliser. Copyright @ 2024 Oracle and/or its affiliates. 53
71. Index key_len (2) < > Copyright @ 2024 Oracle and/or its affiliates. Oh... Okay... mais pourquoi 40 ? Cela n'a pas de sens, n'est-ce pas ? 54
72. Index key_len (2) < > Copyright @ 2024 Oracle and/or its affiliates. Oh... Okay... mais pourquoi 40 ? Cela n'a pas de sens, n'est-ce pas ? En fait, nous avons indexé les 10 premiers caractères de la colonne 'name'... mais cela utilise le charset utf8mb4: 1 caractère est encodé dans un maximum de 4 octets: 10 x 4 bytes = 40 bytes par enregistrement dans l'index 54
73. Multi-column Index Il est également possible d'indexer plusieurs colonnes dans un seul index: create create index index first_last_idx first_last_idx on on employees employees ( (first_name first_name, , last_name last_name) ); ; Copyright @ 2024 Oracle and/or its affiliates. 55
74. Multi-column Index Il est également possible d'indexer plusieurs colonnes dans un seul index: create create index index first_last_idx first_last_idx on on employees employees ( (first_name first_name, , last_name last_name) ); ; Cet index fonctionnera pour ( rst_name, lastname) et ( rst_name) mais pas pour (last_name). Placez le champ avec la cardinalité la plus élevée en premier ! Copyright @ 2024 Oracle and/or its affiliates. 55
75. Multi-column Index Il est également possible d'indexer plusieurs colonnes dans un seul index: create create index index first_last_idx first_last_idx on on employees employees ( (first_name first_name, , last_name last_name) ); ; Cet index fonctionnera pour ( rst_name, lastname) et ( rst_name) mais pas pour (last_name). Placez le champ avec la cardinalité la plus élevée en premier ! Les index sont examinés de gauche à droite Copyright @ 2024 Oracle and/or its affiliates. 55
76. Multi-column Index - Exemple La valeur de key_len vous permet de déterminer le nombre d'éléments d'une clef à plusieurs parties que MySQL utilise réellement. Copyright @ 2024 Oracle and/or its affiliates. 56
77. Multi-column Index - Exemple La valeur de key_len vous permet de déterminer le nombre d'éléments d'une clef à plusieurs parties que MySQL utilise réellement. Copyright @ 2024 Oracle and/or its affiliates. show show create create table table employees\G employees\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: employees : employees Create Create Table Table: : CREATE CREATE TABLE TABLE ` `employees employees` ` ( ( ` `emp_no emp_no` ` int int NOT NOT NULL NULL, , ` `birth_date birth_date` ` date date NOT NOT NULL NULL, , ` `first_name first_name` ` varchar varchar( (14 14) ) NOT NOT NULL NULL, , ` `last_name last_name` ` varchar varchar( (16 16) ) NOT NOT NULL NULL, , ` `gender gender` ` enum enum( ('M' 'M', ,'F' 'F') ) NOT NOT NULL NULL, , ` `hire_date hire_date` ` date date NOT NOT NULL NULL, , PRIMARY PRIMARY KEY KEY ( (` `emp_no emp_no` `) ), , KEY KEY ` `first_last_idx first_last_idx` ` ( (` `first_name first_name` `, ,` `last_name last_name` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =utf8mb4 utf8mb4 COLLATE COLLATE= =utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci select select ( (14 14* *4 4) )+ +2 2+ +( (16 16* *4 4) )+ +2 2; ; + +-------------------+ -------------------+ | | ( (14 14* *4 4) )+ +2 2+ +( (16 16* *4 4) )+ +2 2 | | ( (+ +2 2 --> VARCHAR's length is coded on 2 bytes) --> VARCHAR's length is coded on 2 bytes) + +-------------------+ -------------------+ | | 124 124 | | + +-------------------+ -------------------+ 56
78. Multi-column Index - Exemple (2) Véri ons en utilisant le FORMAT JSONv2: SQL SQL > > set set explain_json_format_version explain_json_format_version= =2 2; ; SQL SQL > > explain explain format format= =json json into into @myqep @myqep select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where first_name first_name like like 'fred%' 'fred%' and and last_name last_name like like 'de%' 'de%' limit limit 10 10; ; SQL SQL > > select select json_extract json_extract( (@myqep @myqep, , "$.inputs[*].ranges[0]" "$.inputs[*].ranges[0]") ) as as index_ranges index_ranges; ; + +-----------------------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------------------+ | | index_ranges index_ranges | | + +-----------------------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------------------+ | | [ ["('fred' <= first_name <= 'fred ' AND 'de' <= last_name <= 'de ')" "('fred' <= first_name <= 'fred ' AND 'de' <= last_name <= 'de ')"] ] | | + +-----------------------------------------------------------------------------------------------------------------------+ -----------------------------------------------------------------------------------------------------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 57
79. Multi-column Index - Exemple (3) Copyright @ 2024 Oracle and/or its affiliates. 58
80. Multi-column Index - Exemple (3) 14 x 4 + 2 = 58 Copyright @ 2024 Oracle and/or its affiliates. 58
81. Multi-column Index Example (4) Copyright @ 2024 Oracle and/or its affiliates. 59
82. Multi-column Index Example (4) la partie la plus à gauche de l'index ne peut pas être utilisée --> l'index n'est pas utilisé Copyright @ 2024 Oracle and/or its affiliates. 59
83. select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' limit limit 10 10; ; select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' order order by by first_name first_name limit limit 10 10; ; Multi-column Index: dé Que pensez-vous de ces deux expressions ? [A] aucune n'utilise l'index [B] l'expression de gauche utilse l'index [C] celle de droite utilise l'index Copyright @ 2024 Oracle and/or its affiliates. 60
84. select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' limit limit 10 10; ; select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where last_name last_name like like 'de%' 'de%' order order by by first_name first_name limit limit 10 10; ; Multi-column Index: dé Que pensez-vous de ces deux expressions ? [A] aucune n'utilise l'index [B] l'expression de gauche utilse l'index [C] celle de droite utilise l'index Copyright @ 2024 Oracle and/or its affiliates. 60
85. Multi-column Index: hashing values Si vous devez e ectuer une recherche sur la valeur exacte (sans utiliser la méthode "like" ou "range") de plusieurs larges colonnes, il est parfois plus e cace d'utiliser une fonction de hachage et d'indexer son résultat: SQL SQL > > alter alter table table employees employees add add column column hash_bin_names hash_bin_names binary binary( (16 16) ) generated always generated always as as ( (unhex unhex( (md5 md5( (concat concat( (first_name first_name, , last_name last_name) )) )) )) ) virtual virtual, , add add key key hash_bin_idx hash_bin_idx( (hash_bin_names hash_bin_names) ); ; Copyright @ 2024 Oracle and/or its affiliates. 61
86. Multi-column Index: hashing values Nous avons donc maintenant ce e information: SQL SQL > > select select first_name first_name, , last_name last_name, , hash_bin_names hash_bin_names from from employees employees limit limit 5 5; ; + +------------+-----------+------------------------------------+ ------------+-----------+------------------------------------+ | | first_name first_name | | last_name last_name | | hash_bin_names hash_bin_names | | + +------------+-----------+------------------------------------+ ------------+-----------+------------------------------------+ | | Georgi Georgi | | Facello Facello | | 0x87F253B6EDC462C31CE4C0B94A7759C6 0x87F253B6EDC462C31CE4C0B94A7759C6 | | | | Bezalel Bezalel | | Simmel Simmel | | 0x7E22DC40403977D056144607BB9F5FF1 0x7E22DC40403977D056144607BB9F5FF1 | | | | Parto Parto | | Bamford Bamford | | 0x5099CEEC19A0CBF02D90352570E296C6 0x5099CEEC19A0CBF02D90352570E296C6 | | | | Chirstian Chirstian | | Koblick Koblick | | 0xDC13ABBDC898223009F5E5403A2AC879 0xDC13ABBDC898223009F5E5403A2AC879 | | | | Kyoichi Kyoichi | | Maliniak Maliniak | | 0x1C1684B875701A01512340BCE6B319EF 0x1C1684B875701A01512340BCE6B319EF | | + +------------+-----------+------------------------------------+ ------------+-----------+------------------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 62
87. Multi-column Index: hashing values Réécrivons maintenant la requête et véri ons le QEP : SQL SQL > > explain explain select select emp_no emp_no, , first_name first_name, , last_name last_name, , hire_date hire_date from from employees employees where where hash_bin_names hash_bin_names= =unhex unhex( (md5 md5( ('AamodDeville' 'AamodDeville') )) ) and and first_name first_name= ='Aamod' 'Aamod' and and last_name last_name like like 'Deville' 'Deville' order order by by first_name first_name limit limit 10 10\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: ref : ref possible_keys: first_last_idx possible_keys: first_last_idx, ,hash_bin_idx hash_bin_idx key key: hash_bin_idx : hash_bin_idx key_len: key_len: 17 17 ref: const ref: const rows rows: : 1 1 filtered: filtered: 5 5 Extra: Extra: Using Using where where Copyright @ 2024 Oracle and/or its affiliates. 63
88. Index Fonctionnels MySQL supporte des clef fonctionnelles qui indexent des valeurs d'expression plutôt que des valeurs de colonnes ou de pré xes de colonnes. L'utilisation de clefs fonctionnelles permet d'indexer des valeurs qui ne sont pas stockées directement dans la table. Copyright @ 2024 Oracle and/or its affiliates. 64
89. Index Fonctionnels MySQL supporte des clef fonctionnelles qui indexent des valeurs d'expression plutôt que des valeurs de colonnes ou de pré xes de colonnes. L'utilisation de clefs fonctionnelles permet d'indexer des valeurs qui ne sont pas stockées directement dans la table. Supposons que nous voulions retrouver tous les employés qui ont été embauchés en mars: SQL SQL > > select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3; ; Copyright @ 2024 Oracle and/or its affiliates. 64
90. Index Fonctionnels (2) Obtenons le plan d'exécution de la requête: SQL SQL > > explain explain select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: : ALL ALL possible_keys: possible_keys: NULL NULL key key: : NULL NULL key_len: key_len: NULL NULL ref: ref: NULL NULL rows rows: : 299379 299379 filtered: filtered: 100 100 Extra: Extra: Using Using where where Copyright @ 2024 Oracle and/or its affiliates. 65
91. Index Fonctionnels (2) Obtenons le plan d'exécution de la requête: SQL SQL > > explain explain select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: : ALL ALL possible_keys: possible_keys: NULL NULL key key: : NULL NULL key_len: key_len: NULL NULL ref: ref: NULL NULL rows rows: : 299379 299379 filtered: filtered: 100 100 Extra: Extra: Using Using where where FULL TABLE SCAN ! Copyright @ 2024 Oracle and/or its affiliates. 65
92. Index Fonctionnels (3) SQL SQL > > create create index index month_hire_idx month_hire_idx on on employees employees ( (( (month month( (hire_date hire_date) )) )) ); ; please mind the please mind the ( (( (. .. .. .) )) ) notation notation Copyright @ 2024 Oracle and/or its affiliates. 66
93. Index Fonctionnels (3) SQL SQL > > create create index index month_hire_idx month_hire_idx on on employees employees ( (( (month month( (hire_date hire_date) )) )) ); ; please mind the please mind the ( (( (. .. .. .) )) ) notation notation SQL SQL > > explain explain select select first_name first_name, , hire_date hire_date from from employees employees where where month month( (hire_date hire_date) )= =3 3\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: ref : ref possible_keys: month_hire_idx possible_keys: month_hire_idx key key: month_hire_idx : month_hire_idx key_len: key_len: 5 5 ref: const ref: const rows rows: : 51684 51684 filtered: filtered: 100 100 Extra: Extra: NULL NULL Copyright @ 2024 Oracle and/or its affiliates. 66
94. Index Décroissants MySQL prend en charge les index descendants: DESC dans une dé nition d'index n'est plus ignoré mais entraîne le stockage des valeurs des clés dans l'ordre décroissant. Auparavant, les index pouvaient être parcourus dans l'ordre inverse, mais avec une pénalité en termes de performances. Copyright @ 2024 Oracle and/or its affiliates. 67
95. Index Décroissants MySQL prend en charge les index descendants: DESC dans une dé nition d'index n'est plus ignoré mais entraîne le stockage des valeurs des clés dans l'ordre décroissant. Auparavant, les index pouvaient être parcourus dans l'ordre inverse, mais avec une pénalité en termes de performances. Considérons ce e requête: SQL SQL> > select select last_name last_name, , birth_date birth_date from from employees employees where where birth_date birth_date between between "1961-01-10" "1961-01-10" and and "1965-02-01" "1965-02-01" order order by by birth_date birth_date desc desc limit limit 10 10; ; . .. .. . 10 10 rows rows in in set set ( (0.0006 0.0006 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 67
96. Index Décroissants (2) * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: range : range possible_keys: birth_date_idx possible_keys: birth_date_idx key key: birth_date_idx : birth_date_idx key_len: key_len: 3 3 ref: ref: NULL NULL rows rows: : 149689 149689 filtered: filtered: 100 100 Extra: Extra: Using Using index index condition condition; ; Backward Backward index index scan scan Copyright @ 2024 Oracle and/or its affiliates. 68
97. Index Décroissants (3) Créons maintenant un index décroissant: SQL SQL> > alter alter table table employees employees add add index index birth_date_desc_idx birth_date_desc_idx( (birth_date birth_date desc desc) ); ; Copyright @ 2024 Oracle and/or its affiliates. 69
98. Index Décroissants (3) Créons maintenant un index décroissant: SQL SQL> > alter alter table table employees employees add add index index birth_date_desc_idx birth_date_desc_idx( (birth_date birth_date desc desc) ); ; Et exécutons la même requête: SQL SQL> > select select last_name last_name, , birth_date birth_date from from employees employees where where birth_date birth_date between between "1961-01-10" "1961-01-10" and and "1965-02-01" "1965-02-01" order order by by birth_date birth_date desc desc limit limit 10 10; ; . .. .. . 10 10 rows rows in in set set ( (0.0004 0.0004 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 69
99. Index Décroissants (4) Véri ons le plan d'éxécution de la requête: * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 1 1 select_type: select_type: SIMPLE SIMPLE table table: employees : employees partitions: partitions: NULL NULL type type: range : range possible_keys: birth_date_idx possible_keys: birth_date_idx, ,birth_date_desc_idx birth_date_desc_idx key key: birth_date_desc_idx : birth_date_desc_idx key_len: key_len: 3 3 ref: ref: NULL NULL rows rows: : 149689 149689 filtered: filtered: 100 100 Extra: Extra: Using Using index index condition condition Copyright @ 2024 Oracle and/or its affiliates. 70
100. N'oubliez pas... S'il existe un choix entre plusieurs index, MySQL utilise normalement l'index qui trouve le plus petit nombre d'enregistrements (l'index le plus sélectif). MySQL peut utiliser les index sur les colonnes plus e cacement s'ils sont déclarés comme étant du même type et de la même taille. Copyright @ 2024 Oracle and/or its affiliates. 71
101. NULL est utilisé pour désigner un MANQUE de données: False 0 True 1 Don't know NULL NULL Indexer les valeurs NULL réduit réellement les performances des index. Copyright @ 2024 Oracle and/or its affiliates. 72
102. Avant les index invisibles . douter de l'utilité de l'index . supprimer cet index . recevoir un appel/texto/cri de la part d'un utilisateur expérimenté à propos de la lenteur des performances . le reste de la planète semble avoir besoin de cet index ! . recréer l'index... et cela peut prendre beaucoup de teeeeeemps Après les index invisibles . douter de l'utilité de l'index . rendre l'index invisible - l'optimiseur ne peut pas le voir ! . recevoir un appel/texto/cri de la part d'un utilisateur expérimenté à propos de la lenteur des performances . rendre l'index à nouveau visible . blâmer un problème sur { le réseau | le matériel | le nuage | un collègue} Index Invisibles MySQL o re la possibilité de cacher les index à l'optimiseur. Ce e fonctionnalité est très utile pour tester la pertinence des index avant de les supprimer. Et très patique pour l'équipe d'exploitation (ops). Copyright @ 2024 Oracle and/or its affiliates. 73
103. Comment utiliser les INDEX INVISIBLES alter alter table table employees employees alter alter index index first_last_idx invisible first_last_idx invisible; ; alter alter table table employees employees alter alter index index first_last_idx visible first_last_idx visible; ; Copyright @ 2024 Oracle and/or its affiliates. 74
104. Comment utiliser les INDEX INVISIBLES alter alter table table employees employees alter alter index index first_last_idx invisible first_last_idx invisible; ; alter alter table table employees employees alter alter index index first_last_idx visible first_last_idx visible; ; Lister tous les index invisibles: select select table_name table_name, , index_name index_name, , is_visible is_visible from from information_schema information_schema. .statistics statistics where where is_visible is_visible= ='no' 'no' group group by by table_name table_name, , index_name index_name; ; + +------------+----------------+------------+ ------------+----------------+------------+ | | TABLE_NAME TABLE_NAME | | INDEX_NAME INDEX_NAME | | IS_VISIBLE IS_VISIBLE | | + +------------+----------------+------------+ ------------+----------------+------------+ | | employees employees | | first_last_idx first_last_idx | | NO NO | | + +------------+----------------+------------+ ------------+----------------+------------+ Copyright @ 2024 Oracle and/or its affiliates. 74
105. Index Inutiles (ou inutilisés) Maintenir des index qui ne sont pas utilisés a un coût car cela augmente le nombre de iops et d'utilisation mémoire. De plus, cela oblige l'optimiseur à travailler davantage. En utilisant sys Schema et innodb_index_stats, ile est possible d'indenti er ces index inutilisés: SQL SQL > > select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc; ; Copyright @ 2024 Oracle and/or its affiliates. 75
106. Index Inutiles (ou inutilisés) Maintenir des index qui ne sont pas utilisés a un coût car cela augmente le nombre de iops et d'utilisation mémoire. De plus, cela oblige l'optimiseur à travailler davantage. En utilisant sys Schema et innodb_index_stats, ile est possible d'indenti er ces index inutilisés: SQL SQL > > select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc; ; Copyright @ 2024 Oracle and/or its affiliates. SQL SQL > > select select database_name database_name, , table_name table_name, , t1 t1. .index_name index_name, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_unused_indexes t2 schema_unused_indexes t2 on on object_schema object_schema= =database_name database_name and and object_name object_name= =table_name table_name and and t2 t2. .index_name index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' and and database_name database_name= ="employees" "employees" order order by by stat_value stat_value desc desc; ; + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ | | database_name database_name | | table_name table_name | | index_name index_name | | size size | | + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ | | employees employees | | employees employees | | hash_bin_names2 hash_bin_names2 | | 9.52 9.52 MiB MiB | | | | employees employees | | employees employees | | month_year_hire_idx month_year_hire_idx | | 6.52 6.52 MiB MiB | | | | employees employees | | dept_emp dept_emp | | dept_no dept_no | | 5.52 5.52 MiB MiB | | | | employees employees | | dept_manager dept_manager | | dept_no dept_no | | 16.00 16.00 KiB KiB | | + +---------------+--------------+---------------------+-----------+ ---------------+--------------+---------------------+-----------+ 4 4 rows rows in in set set ( (0.0252 0.0252 sec sec) ) 75
107. Index dupliqués Il en va de même pour les index dupliqués. Il n'y a aucune raison de les maintenir : SQL SQL > > select select t2 t2. .* *, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_redundant_indexes t2 schema_redundant_indexes t2 on on table_schema table_schema= =database_name database_name and and t2 t2. .table_name table_name= =t1 t1. .table_name table_name and and t2 t2. .redundant_index_name redundant_index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc\G \G Copyright @ 2024 Oracle and/or its affiliates. 76
108. Index dupliqués Il en va de même pour les index dupliqués. Il n'y a aucune raison de les maintenir : SQL SQL > > select select t2 t2. .* *, , format_bytes format_bytes( (stat_value stat_value * * @ @@innodb_page_size @innodb_page_size) ) size size from from mysql mysql. .innodb_index_stats t1 innodb_index_stats t1 join join sys sys. .schema_redundant_indexes t2 schema_redundant_indexes t2 on on table_schema table_schema= =database_name database_name and and t2 t2. .table_name table_name= =t1 t1. .table_name table_name and and t2 t2. .redundant_index_name redundant_index_name= =t1 t1. .index_name index_name where where stat_name stat_name= ='size' 'size' order order by by stat_value stat_value desc desc\G \G Copyright @ 2024 Oracle and/or its affiliates. * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: world table_schema: world table_name: city table_name: city redundant_index_name: part_of_name redundant_index_name: part_of_name redundant_index_columns: Name redundant_index_columns: Name redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: name_idx dominant_index_name: name_idx dominant_index_columns: Name dominant_index_columns: Name dominant_index_non_unique: dominant_index_non_unique: 1 1 subpart_exists: subpart_exists: 1 1 sql_drop_index: sql_drop_index: ALTER ALTER TABLE TABLE ` `world world` `. .` `city city` ` DROP DROP INDEX INDEX ` `part_of_name part_of_name` ` size: size: 112.00 112.00 KiB KiB * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 2. 2. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * table_schema: world table_schema: world table_name: countrylanguage table_name: countrylanguage redundant_index_name: CountryCode redundant_index_name: CountryCode redundant_index_columns: CountryCode redundant_index_columns: CountryCode redundant_index_non_unique: redundant_index_non_unique: 1 1 dominant_index_name: dominant_index_name: PRIMARY PRIMARY dominant_index_columns: CountryCode dominant_index_columns: CountryCode, ,Language Language dominant_index_non_unique: dominant_index_non_unique: 0 0 subpart_exists: subpart_exists: 0 0 sql_drop_index: sql_drop_index: ALTER ALTER TABLE TABLE ` `world world` `. .` `countrylanguage countrylanguage` ` DROP DROP INDEX INDEX ` `CountryCode CountryCode` ` size: size: 64.00 64.00 KiB KiB 76
109. N'oubliez pas! Ne prenez pas les recommandations au pied de la le re, véri ez avant de supprimer un index. Ne supprimez pas un index immédiatement, mais dé nissez-le d'abord comme INVISIBLE pendant un certain temps. De temps en temps, cet index peut être utilisé, par exemple pour un rapport mensuel. Copyright @ 2024 Oracle and/or its affiliates. 77
110. N'oubliez pas! Ne prenez pas les recommandations au pied de la le re, véri ez avant de supprimer un index. Ne supprimez pas un index immédiatement, mais dé nissez-le d'abord comme INVISIBLE pendant un certain temps. De temps en temps, cet index peut être utilisé, par exemple pour un rapport mensuel. Copyright @ 2024 Oracle and/or its affiliates. Mais lorsque 'ajoute ou supprime un index, puis-je en estimer le temps restant ? 77
111. Progression d'un ALTER SQL SQL > > select select stmt stmt. .thread_id thread_id, , stmt stmt. .sql_text sql_text, , stage stage. .event_name event_name as as state state, , stage stage. .work_completed work_completed, , stage stage. .work_estimated work_estimated, , lpad lpad( (concat concat( (round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, , 2 2) ), ,"%" "%") ), ,10 10, ," " " ") ) as as completed_at completed_at, , lpad lpad( (format_pico_time format_pico_time( (stmt stmt. .timer_wait timer_wait) ), , 10 10, , " " " ") ) as as started_ago started_ago, , lpad lpad( (format_pico_time format_pico_time( (stmt stmt. .timer_wait timer_wait/ /round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, ,2 2) )* *100 100) ), , 10 10, , " " " ") ) as as estimated_full_time estimated_full_time, , lpad lpad( (format_pico_time format_pico_time( (( (stmt stmt. .timer_wait timer_wait/ /round round( (100 100* *stage stage. .work_completed work_completed/ /stage stage. .work_estimated work_estimated, ,2 2) )* *100 100) ) - -stmt stmt. .timer_wait timer_wait) ), , 10 10, , " " " ") ) as as estimated_remaining_time estimated_remaining_time, , current_allocated memory current_allocated memory from from performance_schema performance_schema. .events_statements_current stmt events_statements_current stmt inner inner join join sys sys. .memory_by_thread_by_current_bytes mt memory_by_thread_by_current_bytes mt on on mt mt. .thread_id thread_id = = stmt stmt. .thread_id thread_id inner inner join join performance_schema performance_schema. .events_stages_current stage events_stages_current stage on on stage stage. .thread_id thread_id = = stmt stmt. .thread_id\G thread_id\G Copyright @ 2024 Oracle and/or its affiliates. 78
112. Progression d'un ALTER - exemple Copyright @ 2024 Oracle and/or its affiliates. 79
113. La création d'index est lente... < > Copyright @ 2024 Oracle and/or its affiliates. La création d'index est une opération très lente même sur mon puissant serveur avec plusieurs cœurs ! Que puis-je faire ? 80
114. La création d'index est lente... < > Copyright @ 2024 Oracle and/or its affiliates. La création d'index est une opération très lente même sur mon puissant serveur avec plusieurs cœurs ! Que puis-je faire ? Depuis MySQL 8.0.27, vous avez la possibilité de contrôler le nombre maximum de threads parallèles qu'InnoDB peut utiliser pour créer des index secondaires ! 80
115. Création d'index en parallèle Le nombre de threads parallèles utilisés par InnoDB est contrôlé par innodb_ddl_threads. Ce e nouvelle variable est couplée à une autre nouvelle variable: innodb_ddl_buffer_size. Si vous disposez d'un stockage rapide et de processeur à plusieurs cœurs, le réglage de ces variables peut accélérer la création d'index secondaires. Copyright @ 2024 Oracle and/or its affiliates. 81
116. Création d'index en parallèle - exemple SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 82
117. Création d'index en parallèle - exemple SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) Les paramètres par défaut sont les suivants: innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 Copyright @ 2024 Oracle and/or its affiliates. 82
118. Création d'index en parallèle - exemple SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (9 9 min min 0.6838 0.6838 sec sec) ) Les paramètres par défaut sont les suivants: innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 La taille du tampon mémoire innodb_ddl_buffer_size est patagée entre tous les innodb_ddl_threads dé nis. Si vous augmentez le nombre de threads, je vous recommande d'augmenter également la taille du tampon mémoire. Copyright @ 2024 Oracle and/or its affiliates. 82
119. Création d'index en parallèle - exemple (2) Pour trouver les meilleures valeurs pour ces variables, examinons le nombre de cœurs à disposition: SQL SQL> > select select count count from from information_schema information_schema. .INNODB_METRICS INNODB_METRICS where where name name = = 'cpu_n' 'cpu_n'; ; + +-------+ -------+ | | count count | | + +-------+ -------+ | | 16 16 | | + +-------+ -------+ Nous avons donc 16 cœurs à partager. Comme ma machine a su samment de mémoire, je vais allouer 1GB pour le tampon DDL d'InnoDB. Copyright @ 2024 Oracle and/or its affiliates. 83
120. Création d'index en parallèle - exemple (3) SQL SQL> > SET SET innodb_ddl_threads innodb_ddl_threads = = 8 8; ; SQL SQL> > SET SET innodb_parallel_read_threads innodb_parallel_read_threads = = 8 8; ; SQL SQL> > SET SET innodb_ddl_buffer_size innodb_ddl_buffer_size = = 1048576000 1048576000; ; Copyright @ 2024 Oracle and/or its affiliates. 84
121. Création d'index en parallèle - exemple (3) SQL SQL> > SET SET innodb_ddl_threads innodb_ddl_threads = = 8 8; ; SQL SQL> > SET SET innodb_parallel_read_threads innodb_parallel_read_threads = = 8 8; ; SQL SQL> > SET SET innodb_ddl_buffer_size innodb_ddl_buffer_size = = 1048576000 1048576000; ; Nous pouvons maintenant réessayer la même création d'index que précédemment : SQL SQL> > alter alter table table booking booking add add index index idx_2 idx_2( (flight_id flight_id, , seat seat, , passenger_id passenger_id) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (3 3 min min 9.1862 9.1862 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 84
122. Création d'index en parallèle - exemple (4) Je recommande de faire des tests pour dé nir les paramètres optimaux pour votre base de données, votre matériel et vos données. Par exemple, j'ai obtenu le meilleur résultat en xant la taille de la mémoire tampon à 2 Go et les threads ddl et les threads de lecture parallèle à 4. Cela m'a pris 2 minutes 43 secondes, bien mieux que les 9 minutes initiales ! Pour plus d'informations, rendez-vous sur h ps://lefred.be/content/mysql-8-0-innodb- parallel-threads-for-online-ddl-operations/ Copyright @ 2024 Oracle and/or its affiliates. 85
123. Histogrammes ou comment aider l'Optimizer à prendre la bonne décision Copyright @ 2024 Oracle and/or its affiliates. 86
124. Histogrammes Qu'est-ce qu'un histogramme? Selon Wikipédia, un histogramme est une représentation précise de la distribution de données numériques. Pour les SGBDR, un histogramme est une approximation de la distribution des données dans une colonne spéci que. Ainsi, dans MySQL, les histogrammes aident l'optimiseur à trouver le plan de requête le plus e cace pour récupérer ces données. Copyright @ 2024 Oracle and/or its affiliates. 87
125. Histogrammes dans MySQL MySQL fournit: les histogrammes des requêtes (distribution du temps de réponse) les histogrammes pour l'optimiseur Copyright @ 2024 Oracle and/or its affiliates. 88
126. Histogrammes dans MySQL MySQL fournit: les histogrammes des requêtes (distribution du temps de réponse) les histogrammes pour l'optimiseur aujourd'hui, nous nous concentrons sur la deuxième catégorie ! Copyright @ 2024 Oracle and/or its affiliates. 88
127. Histogrammes des Requêtes Voici un exemple de distribution du temps de réponse d'une requête pour une instruction: Copyright @ 2024 Oracle and/or its affiliates. 89
128. Histogrammes Globaux des Requêtes Si vous souhaitez obtenir une vue d'ensemble de toutes les requêtes: SELECT SELECT CONCAT CONCAT( ('<' '<', ,ROUND ROUND( (BUCKET_TIMER_HIGH BUCKET_TIMER_HIGH/ /1000000 1000000, ,2 2) ), , ' microsec (<' ' microsec (<', ,ROUND ROUND( (BUCKET_TIMER_HIGH BUCKET_TIMER_HIGH/ /1000000000 1000000000, ,2 2) ) , ,'ms)' 'ms)') ) QRT QRT, , CONCAT CONCAT( (RPAD RPAD( ('' '', ,ROUND ROUND( (BUCKET_QUANTILE BUCKET_QUANTILE* *100 100) ), ,'*' '*') ), , ROUND ROUND( (BUCKET_QUANTILE BUCKET_QUANTILE* *100 100, ,2 2) ), ,"%" "%") ) bar bar FROM FROM events_statements_histogram_global events_statements_histogram_global WHERE WHERE count_bucket count_bucket> >0 0; ; Copyright @ 2024 Oracle and/or its affiliates. 90
129. Les Histogrammes de l'Optimizer de MySQL Un histogramme est une distribution de données en tranches logiques. Il existe deux types d'histogrammes : singleton equi-height Les nombre maximal de "tranches" (buckets) est de 1024. Copyright @ 2024 Oracle and/or its affiliates. 91
130. Optimizer Histograms dans MySQL - Pourquoi? Examinons la requête n°90 de la TPC-DS Benchmark Suite : “Quel est le rapport entre le nombre d'articles vendus sur internet le matin et le nombre d'articles vendus le soir par des clients ayant un nombre déterminé de bénéficiaires. Ne considérer que les sites web à fort contenu” Copyright @ 2024 Oracle and/or its affiliates. 92
131. SQL SQL> > SELECT SELECT CAST CAST( (amc amc AS AS DECIMAL DECIMAL( (15 15, , 4 4) )) ) / / CAST CAST( (pmc pmc AS AS DECIMAL DECIMAL( (15 15, , 4 4) )) ) am_pm_ratio am_pm_ratio FROM FROM ( (SELECT SELECT COUNT COUNT( (* *) ) amc amc FROM FROM web_sales web_sales, , household_demographics household_demographics, , time_dim time_dim, , web_page web_page WHERE WHERE ws_sold_time_sk ws_sold_time_sk = = time_dim time_dim. .t_time_sk t_time_sk AND AND ws_ship_hdemo_sk ws_ship_hdemo_sk = = household_demographics household_demographics. .hd_demo_sk hd_demo_sk AND AND ws_web_page_sk ws_web_page_sk = = web_page web_page. .wp_web_page_sk wp_web_page_sk AND AND time_dim time_dim. .t_hour t_hour BETWEEN BETWEEN 9 9 AND AND 9 9 + + 1 1 AND AND household_demographics household_demographics. .hd_dep_count hd_dep_count = = 2 2 AND AND web_page web_page. .wp_char_count wp_char_count BETWEEN BETWEEN 5000 5000 AND AND 5200 5200) ) at at, , ( (SELECT SELECT COUNT COUNT( (* *) ) pmc pmc FROM FROM web_sales web_sales, , household_demographics household_demographics, , time_dim time_dim, , web_page web_page WHERE WHERE ws_sold_time_sk ws_sold_time_sk = = time_dim time_dim. .t_time_sk t_time_sk AND AND ws_ship_hdemo_sk ws_ship_hdemo_sk = = household_demographics household_demographics. .hd_demo_sk hd_demo_sk AND AND ws_web_page_sk ws_web_page_sk = = web_page web_page. .wp_web_page_sk wp_web_page_sk AND AND time_dim time_dim. .t_hour t_hour BETWEEN BETWEEN 15 15 AND AND 15 15 + + 1 1 AND AND household_demographics household_demographics. .hd_dep_count hd_dep_count = = 2 2 AND AND web_page web_page. .wp_char_count wp_char_count BETWEEN BETWEEN 5000 5000 AND AND 5200 5200) ) pt pt ORDER ORDER BY BY am_pm_ratio am_pm_ratio LIMIT LIMIT 100 100; ; Copyright @ 2024 Oracle and/or its affiliates. 93
132. Optimizer Histograms dans MySQL - Pourquoi? Résulat et temps de réponse: + +-------------+ -------------+ | | am_pm_ratio am_pm_ratio | | + +-------------+ -------------+ | | 1.13274336 1.13274336 | | + +-------------+ -------------+ 1 1 row row in in set set ( (2.3150 2.3150 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 94
133. Optimizer Histograms dans MySQL - Pourquoi? Créons maintenant un HISTOGRAMME qui devrait nous aider: SQL SQL> > ANALYZE ANALYZE TABLE TABLE web_page web_page UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON wp_char_count wp_char_count WITH WITH 8 8 BUCKETS BUCKETS; ; + +----------------+-----------+----------+----------------------------------------------------------+ ----------------+-----------+----------+----------------------------------------------------------+ | | Table Table | | Op Op | | Msg_type Msg_type | | Msg_text Msg_text | | + +----------------+-----------+----------+----------------------------------------------------------+ ----------------+-----------+----------+----------------------------------------------------------+ | | tpcds tpcds. .web_page web_page | | histogram histogram | | status status | | Histogram Histogram statistics statistics created created for for column column 'wp_char_count' 'wp_char_count'. . | | + +----------------+-----------+----------+----------------------------------------------------------+ ----------------+-----------+----------+----------------------------------------------------------+ 1 1 row row in in set set ( (0.0124 0.0124 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 95
134. Optimizer Histograms dans MySQL - Pourquoi? Résulat et temps de réponse pour la même requête: + +-------------+ -------------+ | | am_pm_ratio am_pm_ratio | | + +-------------+ -------------+ | | 1.13274336 1.13274336 | | + +-------------+ -------------+ 1 1 row row in in set set ( (0.2549 0.2549 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 96
135. Optimizer Histograms dans MySQL - Pourquoi? Résulat et temps de réponse pour la même requête: + +-------------+ -------------+ | | am_pm_ratio am_pm_ratio | | + +-------------+ -------------+ | | 1.13274336 1.13274336 | | + +-------------+ -------------+ 1 1 row row in in set set ( (0.2549 0.2549 sec sec) ) Presque 10x plus rapide ! Copyright @ 2024 Oracle and/or its affiliates. 96
136. Histogrammes dans MySQL (2) Pour aider l'optimiseur à trouver le plan de requête le plus e cace, des histogrammes peuvent être créés. Comme nous le savons, un histogramme est une approximation de la distribution des données dans une colonne spéci que. Les histogrammes sont utiles pour les colonnes qui ne sont PAS candidates à l'indexation. Un histogramme n'est créé ou mis à jour qu'à la demande, il n'ajoute donc pas de surcharge lorsque les données de la table sont modi ées. En revanche, les statistiques deviennent progressivement obsolètes lorsque des modi cations sont apportées à la table, jusqu'à la prochaine mise à jour. Copyright @ 2024 Oracle and/or its affiliates. 97
137. Optimizer Histograms dans MySQL - Pourquoi? Revenons à notre requête précédente, pourquoi avons-nous créé un histogramme sur la colonne wp_char_count de la table web_page ? Copyright @ 2024 Oracle and/or its affiliates. 98
138. Optimizer Histograms dans MySQL - Pourquoi? Sans aucune statistique disponible, l'Optimiseur MySQL suppose que 11.11% des lignes de la table web_page correspondent à "wp_char_count BETWEEN 5000 AND 5200" La réalité est toute autre: SQL SQL> > SELECT SELECT ( (SELECT SELECT COUNT COUNT( (* *) ) FROM FROM web_page web_page WHERE WHERE web_page web_page. .wp_char_count wp_char_count BETWEEN BETWEEN 5000 5000 AND AND 5200 5200) ) / / ( (SELECT SELECT COUNT COUNT( (* *) ) FROM FROM web_page web_page) ) AS AS ratio ratio; ; + +--------+ --------+ | | ratio ratio | | + +--------+ --------+ | | 0.0167 0.0167 | | + +--------+ --------+ Copyright @ 2024 Oracle and/or its affiliates. 99
139. Optimizer Histograms dans MySQL - Pourquoi? Copyright @ 2024 Oracle and/or its affiliates. 100
140. Optimizer Histograms dans MySQL - Pourquoi? Copyright @ 2024 Oracle and/or its affiliates. 101
141. Optimizer Histograms dans MySQL - Pourquoi? Avec les statistiques d'histogramme disponibles, l'optimiseur sait maintenant que seul 1,6% (1 enregistrement sur 60) et pousse la table plus tôt dans l'ordre de jointure, produisant ainsi un plan d'exécution qui s'exécute près de dix fois plus vite. SQL SQL> > select select histogram histogram from from information_schema information_schema. .column_statistics column_statistics where where table_name table_name= ='web_page' 'web_page'\G \G . .. .. . HISTOGRAM: { HISTOGRAM: {"buckets" "buckets": : [ [[ [0 0, , 1339 1339, , 0.11666666666666667 0.11666666666666667, , 5 5] ], , [ [1530 1530, , 1842 1842, , 0.25 0.25, , 6 6] ], , [ [2077 2077, , 2343 2343, , 0.38333333333333336 0.38333333333333336, , 6 6] ], , [ [2472 2472, , 2964 2964, , 0.48333333333333334 0.48333333333333334, , 6 6] ], , [ [3117 3117, , 3467 3467, , 0.6166666666666667 0.6166666666666667, , 5 5] ], , [ [3544 3544, , 4233 4233, , 0.75 0.75, , 7 7] ], , [ [4295 4295, , 5062 5062, , 0.8833333333333333 0.8833333333333333, , 6 6] ], , [ [5310 5310, , 6372 6372, , 1.0 1.0, , 6 6] ]] ], , Copyright @ 2024 Oracle and/or its affiliates. 102
142. Optimizer Histograms dans MySQL - Pourquoi? Avec les statistiques d'histogramme disponibles, l'optimiseur sait maintenant que seul 1,6% (1 enregistrement sur 60) et pousse la table plus tôt dans l'ordre de jointure, produisant ainsi un plan d'exécution qui s'exécute près de dix fois plus vite. SQL SQL> > select select histogram histogram from from information_schema information_schema. .column_statistics column_statistics where where table_name table_name= ='web_page' 'web_page'\G \G . .. .. . HISTOGRAM: { HISTOGRAM: {"buckets" "buckets": : [ [[ [0 0, , 1339 1339, , 0.11666666666666667 0.11666666666666667, , 5 5] ], , [ [1530 1530, , 1842 1842, , 0.25 0.25, , 6 6] ], , [ [2077 2077, , 2343 2343, , 0.38333333333333336 0.38333333333333336, , 6 6] ], , [ [2472 2472, , 2964 2964, , 0.48333333333333334 0.48333333333333334, , 6 6] ], , [ [3117 3117, , 3467 3467, , 0.6166666666666667 0.6166666666666667, , 5 5] ], , [ [3544 3544, , 4233 4233, , 0.75 0.75, , 7 7] ], , [ [4295 4295, , 5062 5062, , 0.8833333333333333 0.8833333333333333, , 6 6] ], , [ [5310 5310, , 6372 6372, , 1.0 1.0, , 6 6] ]] ], , (0.8833333333333333-0.75)*(5062-5000)/(5062-4295) = 0.010777923 Copyright @ 2024 Oracle and/or its affiliates. 102
143. Optimizer Histograms - Obtenir l'estimation Vous pouvez utiliser Optimizer Trace pour connaître plus en détail l'estimation e ectuée par l'optimiseur MySQL: SQL SQL> > SET SET OPTIMIZER_TRACE OPTIMIZER_TRACE = = "enabled=on" "enabled=on"; ; SQL SQL> > SET SET OPTIMIZER_TRACE_MAX_MEM_SIZE OPTIMIZER_TRACE_MAX_MEM_SIZE = = 1000000 1000000; ; SQL SQL> > EXPLAIN EXPLAIN SELECT SELECT . .. .. .. . SQL SQL> > SELECT SELECT JSON_EXTRACT JSON_EXTRACT( (TRACE TRACE- -> >"$**.filtering_effect[*]" "$**.filtering_effect[*]", , "$[1]" "$[1]") ) FROM FROM INFORMATION_SCHEMA INFORMATION_SCHEMA. .OPTIMIZER_TRACE OPTIMIZER_TRACE; ; + +---------------------------------------------------------------------------------------------------------+ ---------------------------------------------------------------------------------------------------------+ | | JSON_EXTRACT JSON_EXTRACT( (TRACE TRACE- -> >"$**.filtering_effect[*]" "$**.filtering_effect[*]", , "$[1]" "$[1]") ) | | + +---------------------------------------------------------------------------------------------------------+ ---------------------------------------------------------------------------------------------------------+ | | { {"condition" "condition": : "(`web_page`.`wp_char_count` between 5000 and 5200)" "(`web_page`.`wp_char_count` between 5000 and 5200)", , "histogram_selectivity" "histogram_selectivity": : 0.0109375 0.0109375} } | | + +---------------------------------------------------------------------------------------------------------+ ---------------------------------------------------------------------------------------------------------+ 1 1 row row in in set set ( (0.0008 0.0008 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 103
144. Les deux types d'histogrammes Equi-height: Un bucket représente une plage de valeurs. Ce type d'histogramme est créé lorsque les valeurs distinctes de la colonne sont supérieures au nombre de buckets spéci é dans la syntaxe ANALYZE TABLE. Pensz à A-G H-L M-T U-Z. Singleton: Ce type d'histogramme est le plus précis et sera créé lorsque le nombre de valeurs distinctes dans la colonne est inférieur ou égal au nombre de buckets spéci é dans ANALYZE TABLE. Copyright @ 2024 Oracle and/or its affiliates. 104
145. Syntaxe des Histogrammes de l'Optimizer ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c2 c2, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t DROP DROP HISTOGRAM HISTOGRAM ON ON c2 c2; ; Notez que la première instruction crée trois histogrammes di érents sur c1, c2 et c3 car un histogramme est créé par colonne. Copyright @ 2024 Oracle and/or its affiliates. 105
146. Syntaxe des Histogrammes de l'Optimizer ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c2 c2, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t UPDATE UPDATE HISTOGRAM HISTOGRAM ON ON c1 c1, , c3 c3 WITH WITH 10 10 BUCKETS BUCKETS; ; ANALYZE ANALYZE TABLE TABLE t t DROP DROP HISTOGRAM HISTOGRAM ON ON c2 c2; ; Notez que la première instruction crée trois histogrammes di érents sur c1, c2 et c3 car un histogramme est créé par colonne. Les histogrammes peuvent être créés pour presque tous les types de données. Si un type n'est pas supporté, vous obtiendrez: The The column column 'doc' 'doc' has an unsupported has an unsupported data data type type. . Copyright @ 2024 Oracle and/or its affiliates. 105
147. Syntaxe des Histogrammes de l'Optimizer (2) Information_Schema peut être utilisé pour récupérer les informations relatives aux histogrammes créés: SQL SQL> > select select table_name table_name, , column_name column_name, , histogram histogram- ->> >>'$."data-type"' '$."data-type"' AS AS 'data-type' 'data-type', , histogram histogram- ->> >>'$."number-of-buckets-specified"' '$."number-of-buckets-specified"' AS AS 'specified-buckets' 'specified-buckets', , json_length json_length( (histogram histogram- ->> >>'$."buckets"' '$."buckets"') ) AS AS 'created-buckets' 'created-buckets', , histogram histogram- ->> >>'$."last-updated"' '$."last-updated"' AS AS 'last-updated' 'last-updated' from from information_schema information_schema. .column_statistics column_statistics; ; + +------------+---------------+-----------+-------------------+-----------------+----------------------------+ ------------+---------------+-----------+-------------------+-----------------+----------------------------+ | | TABLE_NAME TABLE_NAME | | COLUMN_NAME COLUMN_NAME | | data data- -type type | | specified specified- -buckets buckets | | created created- -buckets buckets | | last last- -updated updated | | + +------------+---------------+-----------+-------------------+-----------------+----------------------------+ ------------+---------------+-----------+-------------------+-----------------+----------------------------+ | | web_page web_page | | wp_char_count wp_char_count | | int int | | 8 8 | | 8 8 | | 2024 2024- -01 01- -19 19 08 08: :04 04: :46.573993 46.573993 | | | | store store | | s_gmt_offset s_gmt_offset | | decimal decimal | | 8 8 | | 1 1 | | 2024 2024- -01 01- -19 19 11 11: :20 20: :57.963225 57.963225 | | + +------------+---------------+-----------+-------------------+-----------------+----------------------------+ ------------+---------------+-----------+-------------------+-----------------+----------------------------+ 2 2 rows rows in in set set ( (0.0004 0.0004 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 106
148. Histogramme ou Index ? Pourquoi envisager un histogramme plutôt qu'un index? Le maintien d'un index a un coût. Si vous avez un index, chaque INSERT/UPDATE/DELETE entraîne la mise à jour de l'index. Ce n'est pas gratuit et cela aura un impact sur vos performances. En revanche, un histogramme est créé une seule fois et n'est jamais mis à jour, sauf si vous le demandez explicitement. Il n'a ectera donc pas vos performances en écriture. Si vous disposez d'un index, l'optimiseur devra l'utiliser pour e ectuer ce que nous appelons des "index dives" a n d'estimer le nombre d'enregistrements dans un intervalle donné. Ce e opération peut s'avérer trop coûteuse si votre requête comporte, par exemple, de très longues listes IN. Les statistiques d'histogramme sont beaucoup moins coûteuses dans ce cas et peuvent donc être plus appropriées. Copyright @ 2024 Oracle and/or its affiliates. 107
149. Histogramme ou Index ? Quelques conseils Quelles sont les colonnes qui se prêtent le mieux à l'utilisation d'histogrammes ? Les histogrammes sont utiles pour les colonnes qui ne sont pas la première colonne d'un index, et utilisées dans les conditions WHERE des requêtes JOIN requêtes avec des IN-subqueries requêtes ORDER BY ... LIMIT C'est la meilleure solution pour Les colonnes à faible cardinalité (sexe, état de la commande, jour de la semaine, ENUM) Les colonnes avec une distribution inégale (skew) Une Distribution stable (ne change pas beaucoup au l du temps) Copyright @ 2024 Oracle and/or its affiliates. 108
150. Histogramme ou Index ? Quelques conseils (2) Quand ne pas créer d'histogrammes: Première colonne d'un index Jamais utilisé dans la clause WHERE Colonnes avec valeur augmentant de façon cyclique (par exemple, colonnes de dates) Si l'histogramme doit être fréquemment mis à jour pour être précis Dans ces cas, envisagez de créer un index. Copyright @ 2024 Oracle and/or its affiliates. 109
151. MySQL HeatWave J'ai besoin de plus de performance, beaucoup plus! Copyright @ 2024 Oracle and/or its affiliates. 110
152. MySQL HeatWave MySQL est également disponible dans Oracle Cloud Infrastructure (OCI) en tant que service géré (DBaaS). Il s'agit de MySQL HeatWave. HeatWave Cluster est un accélérateur de requêtes haute performance pour OCI MySQL Database Service qui accélère les performance de MySQL pour les requêtes analytiques et les charges de travail mixtes (OLAP + OLTP). HeatWave Cluster peut être activé à la demande. Copyright @ 2024 Oracle and/or its affiliates. 111
153. Lorsque les requêtes sont encore trop lentes Parfois, les données peuvent être si grandes ou complexes qu'il est di cile de gérer les index ou des les maintenir en mémoire... dans ce cas il est très compliqué d'optimiser les requêtes. Ceci est d'autant plus valable pour les requêtes analytiques. Copyright @ 2024 Oracle and/or its affiliates. 112
154. HeatWave Exemple - données de PiDay SQL SQL> > select select * * from from ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max hum max hum` `, , min min( (value value) ) as as ` `min hum min hum` `, , avg avg( (value value) ) as as ` `avg hum avg hum` ` from from humidity_history humidity_history group group by by device_id device_id, , day day) ) a a natural natural join join ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max temp max temp` `, , min min( (value value) ) as as ` `min temp min temp` `, , avg avg( (value value) ) as as ` `avg temp avg temp` ` from from temperature_history temperature_history group group by by device_id device_id, , day day) ) b b order order by by day day, , device_id device_id; ; + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | day day | | device_id device_id | | tot tot | | max hum max hum | | min hum min hum | | avg hum avg hum | | max max temp temp | | min min temp temp | | avg avg temp temp | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 14534 14534 | | 65.00 65.00 | | 55.00 55.00 | | 60.009273 60.009273 | | 29.99 29.99 | | 20.00 20.00 | | 22.597118 22.597118 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 31605 31605 | | 800.21 800.21 | | 1.00 1.00 | | 8.570861 8.570861 | | 814.36 814.36 | | 0.00 0.00 | | 5.079733 5.079733 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 31284 31284 | | 279.32 279.32 | | 30.00 30.00 | | 35.294440 35.294440 | | 288.44 288.44 | | 10.00 10.00 | | 12.797445 12.797445 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000001 c0000000000000001 | | 114906 114906 | | 50.00 50.00 | | 40.00 40.00 | | 45.001613 45.001613 | | 14.00 14.00 | | 9.00 9.00 | | 11.499796 11.499796 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 100913 100913 | | 65.00 65.00 | | 55.00 55.00 | | 59.999105 59.999105 | | 25.00 25.00 | | 20.00 20.00 | | 22.501319 22.501319 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 101465 101465 | | 11.00 11.00 | | 1.00 1.00 | | 5.998472 5.998472 | | 5.00 5.00 | | 0.00 0.00 | | 2.501763 2.501763 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 101044 101044 | | 40.00 40.00 | | 30.00 30.00 | | 34.991012 34.991012 | | 15.00 15.00 | | 10.00 10.00 | | 12.496505 12.496505 | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ 7 7 rows rows in in set set ( (1.2717 1.2717 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 113
155. HeatWave Exemple - données de PiDay La même Requête après avoir activé et chargé les données dans le Cluster HeatWave SQL SQL> > select select * * from from ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max hum max hum` `, , min min( (value value) ) as as ` `min hum min hum` `, , avg avg( (value value) ) as as ` `avg hum avg hum` ` from from humidity_history humidity_history group group by by device_id device_id, , day day) ) a a natural natural join join ( ( select select date date( (time_stamp time_stamp) ) as as ` `day day` `, , device_id device_id, , count count( (* *) ) as as ` `tot tot` `, , max max( (value value) ) as as ` `max temp max temp` `, , min min( (value value) ) as as ` `min temp min temp` `, , avg avg( (value value) ) as as ` `avg temp avg temp` ` from from temperature_history temperature_history group group by by device_id device_id, , day day) ) b b order order by by day day, , device_id device_id; ; + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | day day | | device_id device_id | | tot tot | | max hum max hum | | min hum min hum | | avg hum avg hum | | max max temp temp | | min min temp temp | | avg avg temp temp | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 14534 14534 | | 65.00 65.00 | | 55.00 55.00 | | 60.009272 60.009272 | | 29.99 29.99 | | 20.00 20.00 | | 22.597117 22.597117 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 31605 31605 | | 800.21 800.21 | | 1.00 1.00 | | 8.570860 8.570860 | | 814.36 814.36 | | 0.00 0.00 | | 5.079732 5.079732 | | | | 2022 2022- -03 03- -09 09 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 31284 31284 | | 279.32 279.32 | | 30.00 30.00 | | 35.294440 35.294440 | | 288.44 288.44 | | 10.00 10.00 | | 12.797445 12.797445 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000001 c0000000000000001 | | 115609 115609 | | 50.00 50.00 | | 40.00 40.00 | | 45.001736 45.001736 | | 14.00 14.00 | | 9.00 9.00 | | 11.499157 11.499157 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000002 c0000000000000002 | | 100913 100913 | | 65.00 65.00 | | 55.00 55.00 | | 59.999104 59.999104 | | 25.00 25.00 | | 20.00 20.00 | | 22.501318 22.501318 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000003 c0000000000000003 | | 101465 101465 | | 11.00 11.00 | | 1.00 1.00 | | 5.998472 5.998472 | | 5.00 5.00 | | 0.00 0.00 | | 2.501762 2.501762 | | | | 2022 2022- -03 03- -10 10 | | 00006227543 00006227543c0000000000000004 c0000000000000004 | | 101044 101044 | | 40.00 40.00 | | 30.00 30.00 | | 34.991011 34.991011 | | 15.00 15.00 | | 10.00 10.00 | | 12.496504 12.496504 | | + +------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ ------------+------------------------------+--------+---------+---------+-----------+----------+----------+-----------+ 7 7 rows rows in in set set ( (0.1267 0.1267 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 114
156. HeatWave Exemple - données de PiDay 0.1267 sec VS 1.2717 sec 10x plus rapide... mais ceci n'est qu'un seul jour de données... Augmentons maintenant les données en ajoutant 11G (14 jours): + +----------+----------+------------+ ----------+----------+------------+ | | DATA DATA | | INDEXES INDEXES | | TOTAL SIZE TOTAL SIZE | | + +----------+----------+------------+ ----------+----------+------------+ | | 8.66 8.66 GiB GiB | | 2.93 2.93 GiB GiB | | 11.59 11.59 GiB GiB | | + +----------+----------+------------+ ----------+----------+------------+ Copyright @ 2024 Oracle and/or its affiliates. 115
157. Sans HeatWave Cluster: 45 45 rows rows in in set set ( (10 10 min min 14.1022 14.1022 sec sec) ) Avec HeatWave Cluster: 45 45 rows rows in in set set ( (1.6051 1.6051 sec sec) ) HeatWave Example - data from PiDay 14 jours de données (11GB) 69M d'enregistrements 383x plus rapide ! Copyright @ 2024 Oracle and/or its affiliates. 116
158. HeatWave - chargement des données alter alter table table temperature_history secondary_engine temperature_history secondary_engine= =rapid rapid; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0257 0.0257 sec sec) ) alter alter table table temperature_history secondary_load temperature_history secondary_load; ; Query OK Query OK, , 0 0 rows rows affected affected ( (17.3070 17.3070 sec sec) ) 4.6GB de données dans ce e table (sans index) chargés sur HeatWave Copyright @ 2024 Oracle and/or its affiliates. 117
159. HeatWave Copyright @ 2024 Oracle and/or its affiliates. 118
160. Lectures Recommandées livres Copyright @ 2024 Oracle and/or its affiliates. 119
161. Copyright @ 2024 Oracle and/or its affiliates. 120
162. Partagez votre ❤ pour MySQL #MySQL #MySQLCommunity Rejoingnez nous sur slack! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 121
163. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 122