MySQL est un système de gestion de base de données relationnelle (SGBDR) qui utilise une architecture client-serveur. Son architecture peut être globalement divisée en plusieurs couches principales :

• Couche de Connexion : Gère les connexions client-serveur et l'authentification.
• Couche de Service : Contient le parseur SQL, l'optimiseur et le gestionnaire de requêtes.
• Couche Moteur : Interagit avec les moteurs de stockage sous-jacents.
• Couche de Stockage : Responsable du stockage physique des données.

Moteurs de Stockage

MySQL prend en charge plusieurs moteurs de stockage, chacun avec ses propres caractéristiques. Les plus courants incluent InnoDB et MyISAM. Il est possible de vérifier les moteurs pris en charge, le moteur par défaut et le moteur utilisé par une table spécifique.

Vérification des Moteurs de Stockage

-- Afficher tous les moteurs de stockage supportés
SHOW ENGINES;

-- Afficher le moteur de stockage par défaut
SHOW VARIABLES LIKE 'default_storage_engine';

-- Afficher le moteur de stockage d'une table spécifique (dans la base de données actuelle)
SHOW TABLE STATUS LIKE 'nom_table';

-- Afficher le moteur de stockage d'une table spécifique dans une base de données donnée
SHOW TABLE STATUS FROM nom_base_de_donnees WHERE Name = 'nom_table';

Configuration des Moteurs de Stockage

-- Créer une table en spécifiant le moteur de stockage
CREATE TABLE ma_table (id INT) ENGINE = InnoDB;
CREATE TABLE table_csv (id INT) ENGINE = CSV;
CREATE TABLE table_memoire (id INT) ENGINE = MEMORY;

-- Modifier le moteur de stockage d'une table existante
ALTER TABLE ma_table ENGINE = InnoDB;

-- Modifier le moteur de stockage par défaut (peut aussi être configuré dans my.cnf)
SET default_storage_engine = MyISAM;

Types de Données

MySQL propose une large gamme de types de données pour stocker différentes sortes d'informations, tels que les entiers, les nombres décimaux, les chaînes de caractères, les dates et heures, les types binaires, etc.

Index

Les index améliorent la vitesse de récupération des données en créant des structures de données qui permettent de trouver rapidement les lignes sans avoir à parcourir toute la table. Cependant, ils peuvent ralentir les opérations d'écriture (INSERT, UPDATE, DELETE).

Création d'Index

-- Créer un index sur une colonne
CREATE INDEX idx_nom_col ON ma_table (colonne);

-- Créer un index unique sur une colonne
CREATE UNIQUE INDEX idx_unique_nom_col ON ma_table (colonne_unique);

-- Ajouter un index à une table existante
ALTER TABLE ma_table ADD INDEX idx_nom_col (colonne);

-- Ajouter un index FULLTEXT (pour la recherche de texte)
ALTER TABLE ma_table ADD FULLTEXT(colonne_texte);

Suppression d'Index

-- Supprimer un index
DROP INDEX idx_nom_col ON ma_table;

Affichage des Index

-- Afficher les index d'une table (utilisez \G pour un affichage formaté)
SHOW INDEX FROM ma_table\G;

Modification/Ajout de Types d'Index

-- Ajouter une clé primaire (index unique, ne peut pas être NULL)
ALTER TABLE ma_table ADD PRIMARY KEY (colonne_id);

-- Ajouter un index unique (peut contenir des NULL)
ALTER TABLE ma_table ADD UNIQUE INDEX idx_unique (colonne_unique);

-- Ajouter un index standard (permet les doublons)
ALTER TABLE ma_table ADD INDEX idx_normal (colonne);

-- Ajouter un index FULLTEXT
ALTER TABLE ma_table ADD FULLTEXT INDEX idx_fulltext (colonne_texte);

Requêtes

Comparaison COUNT(\*), COUNT(1), COUNT(colonne)

• COUNT(*) : Compte toutes les lignes, y compris celles où toutes les colonnes sont NULL.
• COUNT(1) : Compte toutes les lignes, similaire à COUNT(*).
• COUNT(colonne) : Compte les lignes où la colonne spécifiée n'est pas NULL.

En général :

• Si la colonne est une clé primaire, COUNT(colonne_primaire) peut être très rapide.
• Si vous avez une colonne non nulle et sans index, COUNT(1) ou COUNT(*) sont souvent plus rapides que COUNT(colonne).
• Si la colonne est NULLable, COUNT(colonne) ne comptera que les lignes non NULL.
• COUNT(1) et COUNT(*) ont généralement des performances très similaires, l'optimisation de MySQL les rendant souvent équivalents.

Différence entre IN et EXISTS

Ces deux clauses sont utilisées pour les sous-requêtes, mais leur fonctionnement diffère :

• IN : Vérifie si la valeur d'une colonne est présente dans un ensemble de valeurs (résultat d'une sous-requête). L'exécution implique souvent de matérialiser la sous-requête.
• EXISTS : Vérifie l'existence d'au moins une ligne dans la sous-requête qui satisfait une condition de jointure avec la requête externe. C'est une approche basée sur un curseur qui peut être plus efficace lorsque la sous-requête retourne beaucoup de lignes, mais que la requête externe n'a besoin que de savoir si une correspondance existe.

-- Exemple avec IN
SELECT * FROM TableA WHERE TableA.id IN (SELECT id FROM TableB);

-- Exemple avec EXISTS
SELECT * FROM TableA WHERE EXISTS (SELECT 1 FROM TableB WHERE TableB.id = TableA.id);

Si les tables sont de taille comparable et qu'un index est présent sur la colonne de jointure, la différence de performance entre IN et EXISTS peut être minime. Sinon, EXISTS est souvent préféré pour les grandes tables car il peut arrêter la recherche dès la première correspondance.

Ordre d'Exécution des Requêtes SQL

Comprendre l'ordre d'exécution des clauses SQL est crucial pour écrire des requêtes performantes.

Ordre Logique (pour le développeur)

SELECT DISTINCT
    <select_list>
FROM
    <left_table>
<join_type> JOIN
    <right_table> ON <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT
    <limit_number>;
</limit_number></order_by_condition></having_condition></group_by_list></where_condition></join_condition></right_table></join_type></left_table></select_list>

Ordre d'Exécution Physique (par l'optimiseur MySQL)

FROM
<join_type> JOIN <right_table> ON <join_condition>
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT;
</join_condition></right_table></join_type>

L'optimiseur réorganise les étapes pour obtenir le plan d'exécution le plus efficace.

Schéma de Jointures

Les schémas de jointures décrivent comment les différentes tables sont liées entre elles dans une base de données, ce qui est essentiel pour comprendre la structure et écrire des requêtes correctes.