Vous êtes ici : Formations en Ligne Gratuites » Catalogue » workbooks » SER400 - Administration des serveurs MySQL-MariaDB » SER407 - Optimisation

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

--- elearning:workbooks:mysql:my08 [2020/01/30 03:29] – modification externe 127.0.0.1
+++ elearning:workbooks:mysql:my08 [2023/11/20 12:51] (Version actuelle) – created admin
@@ Ligne 1: / Ligne 1: @@
-~~PDF:LANDSCAPE~~
 **Version**: ~~LASTMOD~~
-====== DBA201 - Présentation, Installation et Configuration======
+======SER407 - Optimisation======
-=====Présentation de MySQL=====
+=====Matériel=====
-%%MySQL%% comprend les outils suivants :
+====Processeur====
-  * **Un Serveur SQL**
+La vitesse d'un processeur est la caractéristique la plus importante. Il n'est pas possible de paralléliser une requête sur plusieurs processeurs. Pour cette raison, une requête occupe un processeur à la fois. Prenons le cas d'un serveur esclave dans le contexte d'une réplication. Toutes les requêtes de réplication passeront par un seul processeur, rendant les autres processeurs éventuels inutiles.
-    * Un moteur qui permet d'accéder aux bases de données.
-  * **Les programmes clients pour accéder aux serveurs**
-    * Un programme interactif permet de saisir directement les requêtes et d'afficher les résultats
-    * Plusieurs utilitaires et outils d'administration vous permettent de gérer votre site.
-  * **Une bibliothèque client pour écrire vos propres programmes**
-    * Les programmes clients peuvent être écrits en C, car la bibliothèque est elle-même écrite en C.
-    * Elle intègre également toutes les bases nécessaires pour supporter d'autres langages.
-====Développement====
+====Mémoire====
-Le développement de MySQL depuis son rachat par la société Oracle en 2009 produit des versions différentes selon le cycle suivant :
+La mémoire est un facteur critique pour le serveur MariaDB. La donnée la plus importante est la quantité de données consultées. Prenons le cas d'une base de données de 20Go où seules les données de la dernière semaine sont consultées. Si ces données ne représentent que 1Go, un serveur muni de 2Go suffira largement.
-  * Création de une ou plusieurs versions successives dites **DMR** (//Development Milestone Release//) destinée(s) à être testée(s) par des personnes intéressées par des nouvelles fonctionnalités,
+====Disque Dur====
-  * La sortie d'une ou de plusieurs versions successives dites **RC** (//Release Candidate//) qui sont des versions qui deviendront stables,
-  * La sortie d'une version dite **GA** ((//Generally Available//) qui correspond à la version stable actuelle.
-<WRAP center round important 60%>
+Les performances d'un disque dur se mesurent avec deux paramètres, le temps d'accès et le débit. Avec une application transactionnelle de boutique Internet, le temps d'accès est le facteur le plus important. Si par contre, l'application est faite pour stocker l'ensemble des livres dans une bibliothèque, le débit devient très important lors des requêtes de recherche pour trouver un livre spécifique.
-**Important** - A noter que MySQL existe en version 32 bits et 64 bits.
-</WRAP>
-MySQL existe en deux versions, la version communautaire qui est disponible sous la licence **[[http://fr.wikipedia.org/wiki/Licence_publique_g%C3%A9n%C3%A9rale_GNU|GNU GPL v2]]** et la version **Enterprise** qui :
+=====Système d'Exploitation=====
-  * bénéficie du support d'Oracle,
+MariaDB est disponible pour Windows(tm), Solaris(tm) et Linux. Le choix du Système d'Exploitation est une question de coût, de compétences internes et de politique de l'infrastructure informatique.
-  * contient des outils tels Enterprise Monitor, Query Analyzer et Enterprise Backup.
-<WRAP center round important 60%>
+=====Cache de Requêtes=====
-**Important** - A noter qu'à un instant t, la version communautaire et la version Entreprise sont indentique.
-</WRAP>
-Ce cours se concentre sur la version communautaire.
+Le cache des requêtes est utilisé par MariaDB lors des requêtes de type SELECT. Le cache est utile dans le cas où la majorité des requêtes sont de type lecture. Lors d'un SELECT, MariaDB vérifie si la requête est déjà dans le cache. Cette vérification est faite pour **toutes** les requêtes, mêmes celles qui sont **impropres**. Il ne faut donc **pas** activer le cache quand :
-====Protocoles de Communication====
+  * les écritures sont nombreuses,
+  * les lectures ne sont pas répétées,
+  * le requêtes sont en grande partie **impropre**.
-MySQL propose 4 protocoles de communication :
+L'activation du cache se fait en modifiant la valeur de l'option **query_cache_type** :
-^ Protocole ^ Connexion ^ OS ^ Commentaire ^
+<code>
-| TCP | Local et distant | Unix, Windows | Seul protocole de connexion à distance |
+MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
-| Socket Unix | Local | Unix | Protocole par défaut pour les connexions locales |
++------------------+-------+
-| Shared Memory | Local | Windows | Zone de mémoire partagée entre le serveur et le client |
+| Variable_name    | Value |
-| Named Pipes | Local | Windows | Fichier permettant deux processus sans lien de parenté de communiquer |
++------------------+-------+
+| query_cache_type | ON    |
++------------------+-------+
+row in set (0.00 sec)
-<WRAP center round important 60%>
+MariaDB [(none)]>
-**Important** - Le port TCP par défaut de MySQL est le **3306**. Il est possible de changer le port de communication de MySQL en éditant le fichier my.cnf. Pour se connecter sur un serveur distant il convient d'utiliser la commande suivante **mysql -u root -p -h nom_serveur - -protocol=tcp**.
+</code>
-</WRAP>
-====Architecture====
+Cette option peut prendre trois valeurs :
-L'architecture est la suivante :
+  * **ON** : le serveur essaie de mettre en cache toutes les requêtes SELECT sauf celles comportant la phrase SQL_NO_CACHE,
+  * **OFF** : aucune requête n'est mise en cache,
+  * **DEMAND** : le serveur essaie de mettre en cache toutes les requêtes SELECT comportant la phrase SQL_CACHE.
-  * Le serveur reçoit une requête du client,
+====Exclusions====
-  * Le serveur regarde dans le cache des requêtes, si activé,
-  * Si la requête s'y trouve, le serveur renvoie le résultat du requête stocké dans le cache,
-  * Si la requête ne s'y trouve pas, le serveur analyse et optimise la requête avant de l'exécuter en utilisant le **moteur d’exécution des requêtes**, le met dans le cache et retourne le résultat au client.
-=====MariaDB=====
+Seules les requêtes dites déterministes peuvent être mises en cache. Par exemple, une requête de type SELECT * FROM table donnera toujours le même résultat tant que la table n'est pas modifiée. Cette requête peut être mise dans le cache. Par contre la requête SELECT CURRENT_TIMESTAMP() est non-déterministe. Par conséquent, la requête est dite **impropre**.
-MariaDB est un système de gestion de base de données édité sous licence GPL. Il s'agit d'un fork communautaire de MySQL. La gouvernance du projet est assurée par la fondation MariaDB, et sa maintenance par la société Monty Program AB, créateur du projet.
+====Requêtes====
-En 2009, à la suite du rachat de MySQL par Sun Microsystems et des annonces du rachat de Sun Microsystems par Oracle Corporation, Michael Widenius, fondateur de MySQL, quitte la société afin de lancer le projet MariaDB.
+MariaDB recherche dans le cache chaque fois qu'il y a une requête SELECT. MariaDB est sensible à la case. Pour cette raison, MariaDB considère que les trois requêtes suivantes sont différentes :
-Wikipédia annonce, fin 2012, la migration de MySQL à MariaDB8. En septembre 2013 Google annonce l'adoption de MariaDB et affecte un de ses ingénieurs à la Fondation MariaDB.
+  * SELECT prenom,nom FROM familles,
+  * select prenom,nom FROM familles,
+  * SELECT nom,prenom FROM familles.
-Un consortium baptisé **Open Database Alliance** a été créé pour assurer le développement du logiciel.
+====Invalidations====
-====Utilisation du Disque====
+Le serveur MariaDB met à jour automatiquement le contenu du cache. Lors d'une modification d'une table au niveau de sa structure ou bien au niveau de ses données, le serveur MariaDB cherche l'efficacité et supprime du cache **toutes** les requêtes impliquant la table concernée.
-Les bases de données de MariaDB, aussi appelées **Schémas** sont représentées sur disque par un répertoire du même nom que la base de données.
+====Effacements====
-Ce répertoire existe dans un répertoire de données appelé le **datadir**.
+Dans le cas où le cache n'est pas suffisant pour stocker une requête, MariaDB procède a un effacement de l'entrée la plus ancienne. Ce processus s’appelle un **effacement**.
-Pour connaître l'emplacement du datadir, il convient d'utiliser la commande suivante :
+====Fragmentation====
+MariaDB divise le cache en blocs mémoire d'une taille égale. Quand une requête est mise en cache, le serveur MariaDB ne connaît pas à l'avance le nombre d'enregistrements qui vont être retournées. De ce fait, il ne connaît pas non plus l'occupation de la mémoire. Si la requête n'occupe pas un nombre entier de blocs, la mémoire concernée est perdue. Dans ce cas on parle de **fragmentation**.
+====Paramètres====
+Les options associées avec le cache sont :
+^ Option ^ Descripton ^
+| query_cache_size | La taille de la mémoire dédiée au cache. La mémoire est affectée par multiples de 1 024 octets. Dans la pratique il ne faut pas dépasser les 256 Mo. |
+| query_cache_limit | La taille maximale d'une requête qui peut être mise en cache |
+| query_cache_type| Indique le type de cache |
+| query_cache_min_res_unit | La taille des blocs de mémoire du cache |
+| query_cache_wlock_invalidate | Quand cette option est **OFF**, le serveur peut renvoyer le contenu du cache même si la table est verrouillée par un autre utilisateur |
+====Verification du Cache====
+Pour consulter la configuration du cache, utilisez la requête suivante :
 <code>
-MariaDB [(none)]> SHOW VARIABLES LIKE 'datadir';
+MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Qcache%';
-+---------------+-----------------+
++-------------------------+-------+
-| Variable_name | Value           |
+| Variable_name           | Value |
-+---------------+-----------------+
++-------------------------+-------+
-| datadir       | /var/lib/mysql/ |
+| Qcache_free_blocks      | 0     |
-+---------------+-----------------+
+| Qcache_free_memory      | 0     |
+| Qcache_hits             | 0     |
+| Qcache_inserts          | 0     |
+| Qcache_lowmem_prunes    | 0     |
+| Qcache_not_cached       | 0     |
+| Qcache_queries_in_cache | 0     |
+| Qcache_total_blocks     | 0     |
++-------------------------+-------+
+rows in set (0.01 sec)
+</code>
+Cette configuration s'explique de la façon suivante :
+^ Option ^ Descripton ^
+| Qcache_free_memory  | La taille de la mémoire cache libre |
+| Qcache_lowmem_prunes | Le nombre d'entrées supprimées dans le cache par défaut d'assez d'espace disque |
+| Qcache_hits  | Le nombre de fois que le serveur a pu servir une requête du cache |
+Les valeurs ci-dessus doivent être comparées avec le nombre total de requêtes de type SELECT :
+<code>
+MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Com_select';
++---------------+-------+
+| Variable_name | Value |
++---------------+-------+
+| Com_select    | 14    |
++---------------+-------+
 row in set (0.00 sec)
+</code>
+L'efficacité du cache peut être calculé en utilisant le formule suivant :
+<file>
+* Qcache_hits / Com_select
+</file>
+Le taux d'insertion dans le cache est le résultat du formule suivant :
+<file>
+* Qcache_inserts / Com_select
+</file>
+La fragmentation peut être evaluée par le formule :
+<file>
+Qcache_free_blocks / Qcache_total_blocks
+</file>
+<WRAP center round important 60%>
+**Important** :
+Pour défragmenter le cache, il convient d'utiliser la commande **FLUSH QUERY CACHE**. Il est à noter que cette commande ne vide pas le contenu du cache. Pour vider le contenu du cache, il convient d'utiliser la commande **RESET QUERY CACHE**.
+</WRAP>
+=====Optimisation du Schéma=====
+L'optimisation du schéma est un aspect primordial de la bonne gestion de MariaDB. Les règles de base sont les suivantes :
+  * Plus le type de données est petit et compact, plus il sera léger et performant,
+  * Évitez des colonnes NULL car cela demande plus de travail au serveur que des colonnes NOT NULL,
+  * Utilisez des champs adéquats aux besoins réels de la donnée à stocker,
+  * Ne surdimensionnez pas les champs de texte car MariaDB utilise la taille maximale pour effectuer des tris. Privilégiez donc des champs VARCHAR par rapport CHAR car ce dernier a une longueur fixe.
+====PROCEDURE ANALYSE====
+Afin de vous aider, MariaDB fournit une commande capable d'examiner les données réellement présentes dans une table et de vous conseiller sur le type de champ à adopter :
+<code>
+MariaDB [(none)]> SHOW DATABASES;
++--------------------+
+| Database           |
++--------------------+
+| information_schema |
+| CarnetAdresses     |
+| ligue1             |
+| mysql              |
+| performance_schema |
+| test               |
++--------------------+
+rows in set (0.01 sec)
+MariaDB [(none)]> USE ligue1;
+Database changed
+MariaDB [(none)]> SHOW TABLES;
++------------------+
+| Tables_in_ligue1 |
++------------------+
+| equipe           |
+| rencontre        |
++------------------+
+rows in set (0.00 sec)
+MariaDB [(none)]> SHOW CREATE TABLE equipe\G
+*************************** 1. row ***************************
+       Table: equipe
+Create Table: CREATE TABLE `equipe` (
+  `id_equipe` int(11) NOT NULL AUTO_INCREMENT,
+  `nom` varchar(50) NOT NULL,
+  `stade` varchar(50) NOT NULL,
+  `ville` varchar(30) NOT NULL,
+  `points` int(11) NOT NULL DEFAULT '0',
+  `buts` int(11) NOT NULL DEFAULT '0',
+  PRIMARY KEY (`id_equipe`)
+) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+row in set (0.00 sec)
+MariaDB [(none)]> SELECT * FROM equipe PROCEDURE ANALYSE()\G
+*************************** 1. row ***************************
+             Field_name: ligue1.equipe.id_equipe
+              Min_value: 1
+              Max_value: 3
+             Min_length: 1
+             Max_length: 1
+       Empties_or_zeros: 0
+                  Nulls: 0
+Avg_value_or_avg_length: 2.0000
+                    Std: 0.8165
+      Optimal_fieldtype: ENUM('1','2','3') NOT NULL
+*************************** 2. row ***************************
+             Field_name: ligue1.equipe.nom
+              Min_value: Debian AC
+              Max_value: Vista FC
+             Min_length: 8
+             Max_length: 11
+       Empties_or_zeros: 0
+                  Nulls: 0
+Avg_value_or_avg_length: 9.3333
+                    Std: NULL
+      Optimal_fieldtype: ENUM('Debian AC','FC Mandriva','Vista FC') NOT NULL
+*************************** 3. row ***************************
+             Field_name: ligue1.equipe.stade
+              Min_value: Parc des Princes
+              Max_value: Yankee Stadium
+             Min_length: 11
+             Max_length: 16
+       Empties_or_zeros: 0
+                  Nulls: 0
+Avg_value_or_avg_length: 13.6667
+                    Std: NULL
+      Optimal_fieldtype: ENUM('Parc des Princes','Qwest Field','Yankee Stadium') NOT NULL
+*************************** 4. row ***************************
+             Field_name: ligue1.equipe.ville
+              Min_value: New York
+              Max_value: Redmond
+             Min_length: 5
+             Max_length: 8
+       Empties_or_zeros: 0
+                  Nulls: 0
+Avg_value_or_avg_length: 6.6667
+                    Std: NULL
+      Optimal_fieldtype: ENUM('New York','Paris','Redmond') NOT NULL
+*************************** 5. row ***************************
+             Field_name: ligue1.equipe.points
+              Min_value: 0
+              Max_value: 0
+             Min_length: 1
+             Max_length: 1
+       Empties_or_zeros: 3
+                  Nulls: 0
+Avg_value_or_avg_length: 0.0000
+                    Std: 0.0000
+      Optimal_fieldtype: ENUM('0') NOT NULL
+*************************** 6. row ***************************
+             Field_name: ligue1.equipe.buts
+              Min_value: 0
+              Max_value: 0
+             Min_length: 1
+             Max_length: 1
+       Empties_or_zeros: 3
+                  Nulls: 0
+Avg_value_or_avg_length: 0.0000
+                    Std: 0.0000
+      Optimal_fieldtype: ENUM('0') NOT NULL
+rows in set (0.00 sec)
 MariaDB [(none)]>
 </code>
-L’examen de ce répertoire montre la présence des répertoires des schémas :
+=====Normalisation=====
+La normalisation d'une base de données consiste en la structuration des objets de façon à obtenir un modèle de données performant et sur.
+Les trois règles les plus importantes sont :
+  * Eviter des colonnes qui se répètent,
+  * Eviter l’incohérence des données,
+  * Tous les champs doivent dépendre uniquement de la clef primaire.
+====LAB #1 - Normalisation====
+Créez la base de données **connaissances** :
 <code>
-[root@centos7 ~]# ls -l /var/lib/mysql
+MariaDB [(none)]> CREATE DATABASE connaissances;
-total 28704
+Query OK, 1 row affected (0.00 sec)
--rw-rw----. 1 mysql mysql 18874368 Oct 30 15:59 ibdata1
+MariaDB [(none)]> USE connaissances;
--rw-rw----. 1 mysql mysql  5242880 Oct 30 15:59 ib_logfile0
+Database changed
--rw-rw----. 1 mysql mysql  5242880 Oct 30 10:09 ib_logfile1
-drwx------. 2 mysql mysql     4096 Oct 30 11:39 mysql
-srwxrwxrwx. 1 mysql mysql        0 Oct 30 10:09 mysql.sock
-drwx------. 2 mysql mysql     4096 Oct 30 10:09 performance_schema
-drwx------. 2 mysql mysql        6 Oct 30 11:37 test
 </code>
-<WRAP center round important 60%>
+Utilisez la requête suivante pour créer la table **employe** :
-**Important** - Il est possible de configurer MariaDB pour utiliser plusieurs supports différents pour stocker les journaux, fichiers temporaires, binaires etc.
-</WRAP>
-====Utilisation de la Mémoire====
+<file>
+CREATE TABLE employe (
+id int(11) NOT NULL,
+nom varchar(30) NOT NULL DEFAULT '',
+ville varchar(30) NOT NULL DEFAULT '',
+savoir1 varchar(30) NOT NULL DEFAULT '',
+niv1 tinyint(4) NOT NULL,
+savoir2 varchar(30) NOT NULL DEFAULT '',
+niv2 tinyint(4) NOT NULL,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+</file>
-L'allocation de la mémoire est de deux types différentes :
+<code>
+MariaDB [(none)]> CREATE TABLE employe (
+    -> id int(11) NOT NULL,
+    -> nom varchar(30) NOT NULL DEFAULT '',
+    -> ville varchar(30) NOT NULL DEFAULT '',
+    -> savoir1 varchar(30) NOT NULL DEFAULT '',
+    -> niv1 tinyint(4) NOT NULL,
+    -> savoir2 varchar(30) NOT NULL DEFAULT '',
+    -> niv2 tinyint(4) NOT NULL,
+    -> PRIMARY KEY (id)
+    -> ) ENGINE=InnoDB;
+Query OK, 0 rows affected (0.06 sec)
-===Allocation par Instance===
+MariaDB [(none)]>
+</code>
-C'est la mémoire allouée par le serveur au démarrage de celui-ci. Elle est partagée par le serveur **mariadb** et les **threads** (connexions). Elle comprend, entre autre :
+Utilisez la requête suivante pour injecter des données :
-  * le //key_buffer_size// : le cache d'index des tables MyISAM,
+<file>
-  * le //innodb_buffer_pool_size// : le cache de données InnoDB,
+INSERT INTO employe (id , nom , ville , savoir1 , niv1 , savoir2 , niv2) VALUES ('1' , 'Alex', 'Londres', 'Linux', '10','Solaris', '8');INSERT INTO employe (id , nom , ville , savoir1 , niv1 , savoir2 , niv2) VALUES ('2' , 'Mathieu', 'Paris', 'Apache', '5','MariaDB', '7');INSERT INTO employe (id, nom , ville , savoir1 , niv1 , savoir2 , niv2) VALUES ('3' , 'Thomas', 'Paris', 'Linux', '5','MariaDB', '7');
-  * le //table_definition_cache// et le //table_open_cache// : le cache des tables,
+</file>
-  * le //query_cache// : le cache des requêtes.
-===Allocation par Threads===
+<code>
+MariaDB [(none)]> INSERT INTO employe (id , nom , ville , savoir1 , niv1 , savoir2 , niv2) VALUES ('1' , 'Alex', 'Londres', 'Linux', '10','Solaris', '8');INSERT INTO employe (id , nom , ville , savoir1 , niv1 , savoir2 , niv2) VALUES ('2' , 'Mathieu', 'Paris', 'Apache', '5','MariaDB', '7');INSERT INTO employe (id, nom , ville , savoir1 , niv1 , savoir2 , niv2) VALUES ('3' , 'Thomas', 'Paris', 'Linux', '5','MariaDB', '7');
+Query OK, 1 row affected (0.00 sec)
-C'est la mémoire allouée d'une manière dynamique en fonction des besoins de chaque client. Elle comprend, entre autre :
+Query OK, 1 row affected (0.01 sec)
-  * le //sort_buffer_size// : le buffer de tri,
+Query OK, 1 row affected (0.00 sec)
-  * le //read_buffer_size//  : le buffer de lecture,
+</code>
-  * le //tmp_table_size//  : l'espace mémoire des tables temporaires,
-  * le //max_heap_table_size// : l'espace mémoire des tables Memory.
-===== Installation=====
+Vous devez obtenir le résultat suivant :
-====MariaDB====
+<code>
+MariaDB [(none)]> SELECT * FROM employe;
++----+---------+---------+---------+------+---------+------+
+| id | nom     | ville   | savoir1 | niv1 | savoir2 | niv2 |
++----+---------+---------+---------+------+---------+------+
+|  1 | Alex    | Londres | Linux   |   10 | Solaris |    8 |
+|  2 | Mathieu | Paris   | Apache  |    5 | MariaDB   |    7 |
+|  3 | Thomas  | Paris   | Linux   |    5 | MariaDB   |    7 |
++----+---------+---------+---------+------+---------+------+
+rows in set (0.01 sec)
-Pour installer MariaDB, utilisez yum :
+MariaDB [(none)]>
+</code>
+=====Indexes=====
+Un index est une structure de données liée à une table dont le rôle est de permettre le serveur de trouver une référence au plus vite.
+Un index peut être créé à la création de la table avec la clause KEY ou INDEX ou bien après sa création avec une des commandes suivantes :
+  CREATE INDEX nom_index ON table (colonne);
+  ALTER TABLE table ADD INDEX (colonne);
+Ces deux commandes on des différences :
+  * Avec CREATE INDEX le nom de l'index est obligatoire,
+  * Avec ALTER TABLE si le nom de l'index n'est pas spécifié, MariaDB en crée un,
+  * Avec ALTER TABLE il est possible de créer plusieurs index en même temps en séparant les clauses ADD INDEX par des virgules.
+Pour supprimer un index on utilise une des deux commandes suivantes :
+  DROP INDEX nom_index
+  ALTER TABLE nom_index
+====Types d'Index====
+===Index Uniques===
+Un index unique comporte une contrainte. Toutes les valeurs non NULL doivent être unique. Par contre il est possible d'avoir plusieurs valeurs NULL :
 <code>
-[root@centos7 ~]# yum install mariadb mariadb-server
+MariaDB [(none)]> use indexes;
+Reading table information for completion of table and column names
+You can turn off this feature to get a quicker startup with -A
+Database changed
+MariaDB [(none)]> INSERT INTO t (id) VALUES (1);
+Query OK, 1 row affected (0.01 sec)
+MariaDB [(none)]> INSERT INTO t (id) VALUES (1);
+ERROR 1062 (23000): Duplicate entry '1' for key 'id'
+MariaDB [(none)]> INSERT INTO t (id) VALUES (NULL);
+Query OK, 1 row affected (0.00 sec)
+MariaDB [(none)]> INSERT INTO t (id) VALUES (NULL);
+Query OK, 1 row affected (0.02 sec)
+MariaDB [(none)]> SELECT * FROM t;
++------+
+| id   |
++------+
+| NULL |
+| NULL |
+|    1 |
++------+
+rows in set (0.00 sec)
+MariaDB [(none)]>
+</code>
+===Clef Primaires===
+Une Clef Primaire ressemble à un index unique mais possède deux différences :
+  * Il ne peut y avoir qu'une Clef Primaire par table,
+  * Une Clef Primaire ne peut pas contenir des valeurs NULL.
+<code>
+MariaDB [(none)]> CREATE TABLE t1 (id INT);
+Query OK, 0 rows affected (0.03 sec)
+MariaDB [(none)]> CREATE INDEX `PRIMARY` ON t1;
+ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+MariaDB [(none)]> ALTER TABLE t1 ADD PRIMARY KEY (ID);
+Query OK, 0 rows affected (0.06 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+</code>
+===Index sur Plusieurs Colonnes===
+<code>
+MariaDB [(none)]> CREATE TABLE t2 (id INT, col1 VARCHAR(30), col2 VARCHAR(30));
+Query OK, 0 rows affected (0.33 sec)
+MariaDB [(none)]> CREATE INDEX index_col12 ON t2 (col1,col2);
+Query OK, 0 rows affected (0.11 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+MariaDB [(none)]>
 </code>
 <WRAP center round important 60%>
-**Important** Vous pouvez également installer MariaDB à partir des sources disponibles sur le site **[[https://downloads.mariadb.org/]]**.
+**Important** :
+MariaDB est capable d'exploiter un préfixe à gauche de l'index multicolonne. Cela implique que si MariaDB juge que seul **col1** est nécessaire pour faire le travail, il est capable de l'isoler de l'index multicolonne.
+</WRAP>
+===Index sur un Préfixe de Colonne===
+<code>
+MariaDB [(none)]> CREATE TABLE t3 (col TEXT);
+Query OK, 0 rows affected (0.94 sec)
+MariaDB [(none)]> CREATE INDEX index_prefixe ON t3 (col);
+ERROR 1170 (42000): BLOB/TEXT column 'col' used in key specification without a key length
+MariaDB [(none)]> CREATE INDEX index_prefixe ON t3 (col(50));
+Query OK, 0 rows affected (0.10 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+MariaDB [(none)]>
+</code>
+<WRAP center round important 60%>
+**Important** :
+Pour les index stockant du texte, il est possible de ne créer l'index que sur les N premiers caractères du champ.
 </WRAP>
-====MySQL====
+===Clefs Etrangères===
-Téléchargez le rpm du dépôt yum :
+Une clef étrangère indique à MariaDB de rejeter tout ajout ou modification dans la table enfant si la valeur de la clef n'a pas de correspondance dans la table parent.
 <code>
-[root@centos7 ~]# wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
+MariaDB [(none)]> CREATE TABLE t4 (
+    -> id INT(11) NOT NULL AUTO_INCREMENT,
+    -> nom VARCHAR(20) NOT NULL DEFAULT'',
+    -> PRIMARY KEY (id),
+    -> KEY index_nom (nom)
+    -> ) ENGINE = InnoDB;
+Query OK, 0 rows affected (0.32 sec)
+MariaDB [(none)]> CREATE TABLE t5 ( id INT(11) NOT NULL AUTO_INCREMENT, ref_nom VARCHAR(20) NOT NULL DEFAULT'', PRIMARY KEY (id), CONSTRAINT foreign_index FOREIGN KEY foreign_ref_nom (ref_nom) REFERENCES t4(nom)) ENGINE = InnoDB;
+Query OK, 0 rows affected (0.31 sec)
+MariaDB [(none)]>
 </code>
-Installez le rpm :
+<WRAP center round important 60%>
+**Important** :
+La clause CONSTRAINT est optionnelle. Le nom de la contrainte doit être unique dans toute la base. Le nom de l'index est optionnel. La clef étrangère peut référencer plusieurs champs en même temps mais tous les champs doivent être dans la même table.
+</WRAP>
+Lors d'une suppression ou d'une mise à jour, le comportement est dicté par l'ajout d'une clause supplémentaire dans la clause FOREIGN KEY :
+  * **RESTRICT** ou **NO ACTION** - C'est l'action par défaut. Toute tentative de mise à jour ou de suppression dans la table parente provoque une erreur si une ou plusieurs lignes correspondantes se trouvent dans la table enfant,
+  * **CASCADE** - Quand une ligne est mise à jour ou supprimée de la table parente, la ou les lignes corrspondantes dans la table enfant sont mises à jour ou supprimées,
+  * **SET NULL** - Quand une ligne est mise à jour ou supprimée de la table parente, la ou les lignes corrspondantes dans la table enfant sont mises à NULL.
+===Index Cluster avec InnoDB===
+Le moteur InnoDB stocke les données **avec** la clef primaire. Par conséquent toute requête qui utilise la clef primaire est très efficace. Par contre toute requête qui utilise une clef secondaire est moins efficace car MariaDB recherche d'abord dans l'index secondaire la clef primaire associée puis recherche les données à partir de la clef primaire.
+<WRAP center round important 60%>
+**Important** :
+Dans le cas où une clef primaire n'existe pas, MariaDB essaie de trouver une clef unique ne contentant aucune valeur NULL. S'il n'en trouve pas, il crée une clef primaire cachée.
+</WRAP>
+===Index Couvrant===
+Dans le cas où toutes les données nécessaires à la résolution d'une requête se trouvent dans un index, on parle d'un index **couvrant**. Dans ce cas, l'optimiseur de MariaDB n'a pas besoin de chercher les données.
+<WRAP center round important 60%>
+**Important** :
+Un index peut être créé avec un de deux algorithmes principaux : B-Tree ou Hash. Un index couvrant est forcément de type B-Tree.
+</WRAP>
+===Index FULLTEXT===
+Dans le cas où il est nécessaire de rechercher une chaîne de caractères dans un champs, on doit utiliser un index FULLTEXT :
 <code>
-[root@centos7 ~]# rpm -ivh mysql57-community-release-el7-9.noarch.rpm
+MariaDB [(none)]> CREATE TABLE t6(
+    -> col1 VARCHAR(50),
+    -> col2 VARCHAR(50),
+    -> col3 VARCHAR(50),
+    -> FULLTEXT index_col1_col2 (col1,col2)
+    -> );
+ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
 </code>
-Maintenant installez mysql-server :
+<WRAP center round important 60%>
+**Important** :
+Le moteur InnoDB supporte les index FULLTEXT à partir de la version 5.6 de MariaDB.
+</WRAP>
 <code>
-[root@centos7 ~]# yum install mysql-server
+MariaDB [(none)]> CREATE TABLE t6( col1 VARCHAR(50), col2 VARCHAR(50), col3 VARCHAR(50), FULLTEXT index_col1_col2 (col1,col2) ) ENGINE=MyISAM;
+Query OK, 0 rows affected (0.04 sec)
+MariaDB [(none)]> CREATE FULLTEXT INDEX index_col2 ON t6 (col2);
+Query OK, 0 rows affected (0.07 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+MariaDB [(none)]> ALTER TABLE t6 ADD FULLTEXT index_col3 (col3);
+Query OK, 0 rows affected (0.02 sec)
+Records: 0  Duplicates: 0  Warnings: 0
+MariaDB [(none)]>
 </code>
-=====Démarrage du Serveur=====
+Téléchargez la base de donnée exemple sakila :
-Le serveur MariaDB ou MySQL peut être démarré par l'utilisation d'une de deux méthodes différentes.
+<code>
+[root@centos ~]# wget http://downloads.mysql.com/docs/sakila-db.tar.gz
+--2014-02-01 11:46:10--  http://downloads.mysql.com/docs/sakila-db.tar.gz
+Résolution de downloads.mysql.com... 137.254.60.14
+Connexion vers downloads.mysql.com|137.254.60.14|:80...connecté.
+requête HTTP transmise, en attente de la réponse...200 OK
+Longueur: 722633 (706K) [application/x-gzip]
+Sauvegarde en : «sakila-db.tar.gz»
-====Le Service====
+%[======================================>] 722 633     20,2K/s   ds 14s
-===Le Service MariaDB===
+-02-01 11:46:30 (50,6 KB/s) - «sakila-db.tar.gz» sauvegardé [722633/722633]
+</code>
-Pour activer le service **mariadb** il convient d'utiliser la commande **systemctl** :
+Editez le fichier ~/sakila-db/sakila-schema.sql avec VI en exécutant la commande **%%:g/ENGINE=InnoDB/s//ENGINE=MyISAM/g%%** :
 <code>
-[root@centos7 ~]# systemctl enable mariadb
+[root@centos7 ~]# vi ~/sakila-db/sakila-schema.sql
-Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
 </code>
-Le démarrage se fait également avec systemctl :
+Installez sakila :
 <code>
-[root@centos7 ~]# systemctl start mariadb
+[root@centos ~]# tar xvf sakila-db.tar.gz
-[root@centos7 ~]# systemctl status mariadb
+sakila-db/
-● mariadb.service - MariaDB database server
+sakila-db/sakila-schema.sql
-   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
+sakila-db/sakila.mwb
-   Active: active (running) since Tue 2017-10-31 12:34:38 CET; 5s ago
+sakila-db/sakila-data.sql
-  Process: 9769 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
+[root@centos ~]# mysql -uroot -p < sakila-db/sakila-schema.sql
-  Process: 9737 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
+Enter password:
- Main PID: 9768 (mysqld_safe)
+[root@centos ~]# mysql -uroot -p < sakila-db/sakila-data.sql
-   CGroup: /system.slice/mariadb.service
+Enter password:
-           ├─9768 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
+</code>
-           └─9930 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql...
-Oct 31 12:34:35 centos7.fenestros.loc systemd[1]: Starting MariaDB database s...
+==Mode Langage Naturel==
-Oct 31 12:34:35 centos7.fenestros.loc mariadb-prepare-db-dir[9737]: Database ...
-Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
+L'appel à une recherche FULLTEXT nécessite l'utilisation de clauses spécifiques, à savoir **MATCH()** et **AGAINST()** :
-Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
-Oct 31 12:34:38 centos7.fenestros.loc systemd[1]: Started MariaDB database se...
+<code>
-Hint: Some lines were ellipsized, use -l to show in full.
+MariaDB [(none)]> USE sakila;
+Reading table information for completion of table and column names
+You can turn off this feature to get a quicker startup with -A
+Database changed
+MariaDB [(none)]> SELECT film_id,
+    -> LEFT(description,30),
+    -> MATCH(title,description)
+    -> AGAINST('Database Administrator') AS pertinence
+    -> FROM film_text
+    -> WHERE MATCH(title,description)
+    -> AGAINST('Database Administrator')
+    -> LIMIT 5;
++---------+--------------------------------+-------------------+
+| film_id | LEFT(description,30)           | pertinence        |
++---------+--------------------------------+-------------------+
+|     113 | A Thrilling Yarn of a Database | 6.662790775299072 |
+|     962 | A Fanciful Story of a Database | 6.662790775299072 |
+|      27 | A Amazing Reflection of a Data | 6.662790775299072 |
+|     363 | A Fast-Paced Display of a Car  | 4.527374267578125 |
+|     372 | A Taut Display of a Cat And a  | 4.527374267578125 |
++---------+--------------------------------+-------------------+
+rows in set (0.00 sec)
+MariaDB [(none)]>
 </code>
-Le service mariadb appel un script appelé **mysqld_safe** qui lance le serveur et crée un journal d'erreur. Il relance le serveur en cas d'arrêt intempestif.
+<WRAP center round important 60%>
+**Important** :
+Par défaut une requête FULLTEXT est une recherches en langage naturel. Ceci implique que chaque mot de la clause AGAINST est recherché dans l'index et que la pertinence est calculé en fonction de la fréquences d'apparition. Notez que si un mot est peu fréquent dans l'ensemble des lignes indexées et ce mot fait partie de ceux recherchés, la pertinence de la ligne trouvée sera plus haute que la pertinence de la ligne contenant un mot peu qui est fréquent dans les lignes. Dernièrement et par défaut les mots faisant partie de au moins 50% des enregistrements sont automatiquement ignorés. Il n'est pas possible de modifier ce pourcentage.
+</WRAP>
-===Le Service MySQL===
+==Mode Booléen==
-Le démarrage se fait également avec systemctl :
+En utilisant le mode booléen, il vous est possible d'ajouter des **modificateurs** pour préciser quels mots ont une pertinence plus élevée que d'autres :
+^ Modificateur ^ Description ^
+| + | Le résultat doit contenir ce mot |
+| - | Le résultat ne doit pas contenir ce mot |
+| ~ | La pertinence de ce mot est plus faible que les autres mots |
+|   | La pertinence de ce mot est plus haute que les autres mots |
+| * | Placé à la fin d'une chaine, ce modificateur est un joker |
+Par exemple :
 <code>
-[root@centos7 ~]# systemctl start mysqld
+MariaDB [(none)]> SELECT film_id,title,description FROM film_text WHERE MATCH(title,description) AGAINST('Data* Administrator +Anaconda' IN BOOLEAN MODE)\G*************************** 1. row ***************************
+    film_id: 23
+      title: ANACONDA CONFESSIONS
+description: A Lacklusture Display of a Dentist And a Dentist who must Fight a Girl in Australia
+*************************** 2. row ***************************
+    film_id: 315
+      title: FINDING ANACONDA
+description: A Fateful Tale of a Database Administrator And a Girl who must Battle a Squirrel in New Orleans
+rows in set (0.01 sec)
+MariaDB [(none)]>
 </code>
-Le service mysqld appel un script appelé **mysqld_safe** qui lance le serveur et crée un journal d'erreur. Il relance le serveur en cas d'arrêt intempestif.
+<WRAP center round important 60%>
+**Important** :
+Notez que seulement deux résultats sont retournés. En effet, il n'y a que deux enregistrements dans la base qui contiennent le mot Anaconda. Notez que les mots faisant partie de au moins 50% des enregistrements **ne** sont **pas** automatiquement ignorés.
+</WRAP>
-====Invocation Directe====
+==Mode Expansion de Requête==
-Il est aussi possible d'invoquer directement le binaire **mysqld** en spécifiant manuellement le fichier de configuration de MariaDB, le fichier d'erreurs ainsi que le nom de l'utilisateur. Par exemple :
+Dans ce mode, la recherche est lancée deux fois. La première fois avec les mots de la clause AGAINST, la deuxième avec les mots ainsi que les résultats les plus pertinents.
-<file>
+<code>
-# /usr/libexec/mysqld --defaults-file=/chemin/my.cnf --log-error=/chemin/nom_log --user=mysql &
+MariaDB [(none)]> SELECT film_id,title,description FROM film_text WHERE MATCH(title,description) AGAINST('MariaDB');
-</file>
++---------+------------------------+----------------------------------------------------------------------------------------------------------------------+
+| film_id | title                  | description                                                                                                          |
++---------+------------------------+----------------------------------------------------------------------------------------------------------------------+
+|     428 | HOMICIDE PEACH         | A Astounding Documentary of a Hunter And a Boy who must Confront a Boy in A MariaDB Convention                         |
+|     577 | MILE MULAN             | A Lacklusture Epistle of a Cat And a Husband who must Confront a Boy in A MariaDB Convention                           |
+|     812 | SMOKING BARBARELLA     | A Lacklusture Saga of a Mad Cow And a Mad Scientist who must Sink a Cat in A MariaDB Convention                        |
+|     936 | VANISHING ROCKY        | A Brilliant Reflection of a Man And a Woman who must Conquer a Pioneer in A MariaDB Convention                         |
+|     494 | KARATE MOON            | A Astounding Yarn of a Womanizer And a Dog who must Reach a Waitress in A MariaDB Convention                           |
+|     608 | MURDER ANTITRUST       | A Brilliant Yarn of a Car And a Database Administrator who must Escape a Boy in A MariaDB Convention                   |
+|     341 | FROST HEAD             | A Amazing Reflection of a Lumberjack And a Cat who must Discover a Husband in A MariaDB Convention                     |
+|     773 | SEABISCUIT PUNK        | A Insightful Saga of a Man And a Forensic Psychologist who must Discover a Mad Cow in A MariaDB Convention             |
+|     242 | DOOM DANCING           | A Astounding Panorama of a Car And a Mad Scientist who must Battle a Lumberjack in A MariaDB Convention                |
+|     819 | SONG HEDWIG            | A Amazing Documentary of a Man And a Husband who must Confront a Squirrel in A MariaDB Convention                      |
+|      15 | ALIEN CENTER           | A Brilliant Drama of a Cat And a Mad Scientist who must Battle a Feminist in A MariaDB Convention                      |
+|     137 | CHARADE DUFFEL         | A Action-Packed Display of a Man And a Waitress who must Build a Dog in A MariaDB Convention                           |
+|     844 | STEERS ARMAGEDDON      | A Stunning Character Study of a Car And a Girl who must Succumb a Car in A MariaDB Convention                          |
+|     119 | CAPER MOTIONS          | A Fateful Saga of a Moose And a Car who must Pursue a Woman in A MariaDB Convention                                    |
+|     907 | TRANSLATION SUMMER     | A Touching Reflection of a Man And a Monkey who must Pursue a Womanizer in A MariaDB Convention                        |
+|     899 | TOWERS HURRICANE       | A Fateful Display of a Monkey And a Car who must Sink a Husband in A MariaDB Convention                                |
+|     918 | TWISTED PIRATES        | A Touching Display of a Frisbee And a Boat who must Kill a Girl in A MariaDB Convention                                |
+|     933 | VAMPIRE WHALE          | A Epic Story of a Lumberjack And a Monkey who must Confront a Pioneer in A MariaDB Convention                          |
+|     716 | REAP UNFAITHFUL        | A Thrilling Epistle of a Composer And a Sumo Wrestler who must Challenge a Mad Cow in A MariaDB Convention             |
+|     727 | RESURRECTION SILVERADO | A Epic Yarn of a Robot And a Explorer who must Challenge a Girl in A MariaDB Convention                                |
+|     809 | SLIPPER FIDELITY       | A Taut Reflection of a Secret Agent And a Man who must Redeem a Explorer in A MariaDB Convention                       |
+|     567 | MEET CHOCOLATE         | A Boring Documentary of a Dentist And a Butler who must Confront a Monkey in A MariaDB Convention                      |
+|     129 | CAUSE DATE             | A Taut Tale of a Explorer And a Pastry Chef who must Conquer a Hunter in A MariaDB Convention                          |
+|     261 | DUFFEL APOCALYPSE      | A Emotional Display of a Boat And a Explorer who must Challenge a Madman in A MariaDB Convention                       |
+|     213 | DATE SPEED             | A Touching Saga of a Composer And a Moose who must Discover a Dentist in A MariaDB Convention                          |
+|      11 | ALAMO VIDEOTAPE        | A Boring Epistle of a Butler And a Cat who must Fight a Pastry Chef in A MariaDB Convention                            |
+|     201 | CYCLONE FAMILY         | A Lacklusture Drama of a Student And a Monkey who must Sink a Womanizer in A MariaDB Convention                        |
+|     352 | GATHERING CALENDAR     | A Intrepid Tale of a Pioneer And a Moose who must Conquer a Frisbee in A MariaDB Convention                            |
+|     398 | HANOVER GALAXY         | A Stunning Reflection of a Girl And a Secret Agent who must Succumb a Boy in A MariaDB Convention                      |
+|     426 | HOME PITY              | A Touching Panorama of a Man And a Secret Agent who must Challenge a Teacher in A MariaDB Convention                   |
+|      72 | BILL OTHERS            | A Stunning Saga of a Mad Scientist And a Forensic Psychologist who must Challenge a Squirrel in A MariaDB Convention   |
+|     845 | STEPMOM DREAM          | A Touching Epistle of a Crocodile And a Teacher who must Build a Forensic Psychologist in A MariaDB Convention         |
+|     870 | SWARM GOLD             | A Insightful Panorama of a Crocodile And a Boat who must Conquer a Sumo Wrestler in A MariaDB Convention               |
+|     466 | INTOLERABLE INTENTIONS | A Awe-Inspiring Story of a Monkey And a Pastry Chef who must Succumb a Womanizer in A MariaDB Convention               |
+|     974 | WILD APOLLO            | A Beautiful Story of a Monkey And a Sumo Wrestler who must Conquer a A Shark in A MariaDB Convention                   |
+|     980 | WIZARD COLDBLOODED     | A Lacklusture Display of a Robot And a Girl who must Defeat a Sumo Wrestler in A MariaDB Convention                    |
+|     551 | MAIDEN HOME            | A Lacklusture Saga of a Moose And a Teacher who must Kill a Forensic Psychologist in A MariaDB Convention              |
+|     987 | WORDS HUNTER           | A Action-Packed Reflection of a Composer And a Mad Scientist who must Face a Pioneer in A MariaDB Convention           |
+|     183 | CONVERSATION DOWNHILL  | A Taut Character Study of a Husband And a Waitress who must Sink a Squirrel in A MariaDB Convention                    |
+|     804 | SLEEPING SUSPECTS      | A Stunning Reflection of a Sumo Wrestler And a Explorer who must Sink a Frisbee in A MariaDB Convention                |
+|     576 | MIGHTY LUCK            | A Astounding Epistle of a Mad Scientist And a Pioneer who must Escape a Database Administrator in A MariaDB Convention |
+|     733 | RIVER OUTLAW           | A Thrilling Character Study of a Squirrel And a Lumberjack who must Face a Hunter in A MariaDB Convention              |
+|     303 | FANTASY TROOPERS       | A Touching Saga of a Teacher And a Monkey who must Overcome a Secret Agent in A MariaDB Convention                     |
+|     937 | VARSITY TRIP           | A Action-Packed Character Study of a Astronaut And a Explorer who must Reach a Monkey in A MariaDB Convention          |
+|     782 | SHAKESPEARE SADDLE     | A Fast-Paced Panorama of a Lumberjack And a Database Administrator who must Defeat a Madman in A MariaDB Convention    |
+|     114 | CAMELOT VACATION       | A Touching Character Study of a Woman And a Waitress who must Battle a Pastry Chef in A MariaDB Convention             |
+|     971 | WHALE BIKINI           | A Intrepid Story of a Pastry Chef And a Database Administrator who must Kill a Feminist in A MariaDB Convention        |
+|     822 | SOUP WISDOM            | A Fast-Paced Display of a Robot And a Butler who must Defeat a Butler in A MariaDB Convention                          |
+|     107 | BUNCH MINDS            | A Emotional Story of a Feminist And a Feminist who must Escape a Pastry Chef in A MariaDB Convention                   |
+|     750 | RUN PACIFIC            | A Touching Tale of a Cat And a Pastry Chef who must Conquer a Pastry Chef in A MariaDB Convention                      |
++---------+------------------------+----------------------------------------------------------------------------------------------------------------------+
+rows in set (0.01 sec)
-=====Arrêt du Serveur=====
+MariaDB [(none)]>
+</code>
-====Le Service MariaDB====
+<code>
+MariaDB [(none)]> SELECT film_id,title,description FROM film_text WHERE MATCH(title,description) AGAINST('MariaDB' WITH QUERY EXPANSION);
+...
+|     113 | CALIFORNIA BIRDS            | A Thrilling Yarn of a Database Administrator And a Robot who must Battle a Database Administrator in Ancient India                 |
+|     617 | NATURAL STOCK               | A Fast-Paced Story of a Sumo Wrestler And a Girl who must Defeat a Car in A Baloon Factory                                         |
+|     424 | HOLOCAUST HIGHBALL          | A Awe-Inspiring Yarn of a Composer And a Man who must Find a Robot in Soviet Georgia                                               |
+|     589 | MODERN DORADO               | A Awe-Inspiring Story of a Butler And a Sumo Wrestler who must Redeem a Boy in New Orleans                                         |
+|     432 | HOPE TOOTSIE                | A Amazing Documentary of a Student And a Sumo Wrestler who must Outgun a A Shark in A Shark Tank                                   |
+|     145 | CHISUM BEHAVIOR             | A Epic Documentary of a Sumo Wrestler And a Butler who must Kill a Car in Ancient India                                            |
+|      41 | ARSENIC INDEPENDENCE        | A Fanciful Documentary of a Mad Cow And a Womanizer who must Find a Dentist in Berlin                                              |
+|     751 | RUNAWAY TENENBAUMS          | A Thoughtful Documentary of a Boat And a Man who must Meet a Boat in An Abandoned Fun House                                        |
+|     962 | WASTELAND DIVINE            | A Fanciful Story of a Database Administrator And a Womanizer who must Fight a Database Administrator in Ancient China              |
+|     177 | CONNECTICUT TRAMP           | A Unbelieveable Drama of a Crocodile And a Mad Cow who must Reach a Dentist in A Shark Tank                                        |
+|     627 | NORTH TEQUILA               | A Beautiful Character Study of a Mad Cow And a Robot who must Reach a Womanizer in New Orleans                                     |
+|     702 | PULP BEVERLY                | A Unbelieveable Display of a Dog And a Crocodile who must Outrace a Man in Nigeria                                                 |
+|     511 | LAWRENCE LOVE               | A Fanciful Yarn of a Database Administrator And a Mad Cow who must Pursue a Womanizer in Berlin                                    |
+|     376 | GRAPES FURY                 | A Boring Yarn of a Mad Cow And a Sumo Wrestler who must Meet a Robot in Australia                                                  |
+|     430 | HOOK CHARIOTS               | A Insightful Story of a Boy And a Dog who must Redeem a Boy in Australia                                                           |
+|     267 | EAGLES PANKY                | A Thoughtful Story of a Car And a Boy who must Find a A Shark in The Sahara Desert                                                 |
+|     968 | WEREWOLF LOLA               | A Fanciful Story of a Man And a Sumo Wrestler who must Outrace a Student in A Monastery                                            |
+|     692 | POTLUCK MIXED               | A Beautiful Story of a Dog And a Technical Writer who must Outgun a Student in A Baloon                                            |
+|     192 | CROSSING DIVORCE            | A Beautiful Documentary of a Dog And a Robot who must Redeem a Womanizer in Berlin                                                 |
+|     752 | RUNNER MADIGAN              | A Thoughtful Documentary of a Crocodile And a Robot who must Outrace a Womanizer in The Outback                                    |
+|     448 | IDAHO LOVE                  | A Fast-Paced Drama of a Student And a Crocodile who must Meet a Database Administrator in The Outback                              |
+|     996 | YOUNG LANGUAGE              | A Unbelieveable Yarn of a Boat And a Database Administrator who must Meet a Boy in The First Manned Space Station                  |
++---------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------+
+rows in set (0.02 sec)
-Il est possible d'arrêter le service mariadb avec la commande stop :
+MariaDB [(none)]>
+</code>
+==Configuration==
+Une recherche FULLTEXT peut être configurée en modifiant les trois variables suivants :
 <code>
-[root@centos7 ~]# systemctl stop mariadb
+MariaDB [(none)]> SHOW VARIABLES LIKE 'ft_min_word_len';
-[root@centos7 ~]# systemctl status mariadb
++-----------------+-------+
-● mariadb.service - MariaDB database server
+| Variable_name   | Value |
-   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
++-----------------+-------+
-   Active: inactive (dead) since Tue 2017-10-31 12:38:12 CET; 3s ago
+| ft_min_word_len | 4     |
-  Process: 9769 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
++-----------------+-------+
-  Process: 9768 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
+row in set (0.00 sec)
-  Process: 9737 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
- Main PID: 9768 (code=exited, status=0/SUCCESS)
-Oct 31 12:34:35 centos7.fenestros.loc systemd[1]: Starting MariaDB database s...
+MariaDB [(none)]> SHOW VARIABLES LIKE 'ft_max_word_len';
-Oct 31 12:34:35 centos7.fenestros.loc mariadb-prepare-db-dir[9737]: Database ...
++-----------------+-------+
-Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
+| Variable_name   | Value |
-Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
++-----------------+-------+
-Oct 31 12:34:38 centos7.fenestros.loc systemd[1]: Started MariaDB database se...
+| ft_max_word_len | 84    |
-Oct 31 12:38:09 centos7.fenestros.loc systemd[1]: Stopping MariaDB database s...
++-----------------+-------+
-Oct 31 12:38:12 centos7.fenestros.loc systemd[1]: Stopped MariaDB database se...
+row in set (0.00 sec)
-Hint: Some lines were ellipsized, use -l to show in full.
-[root@centos7 ~]# systemctl start mariadb
-[root@centos7 ~]# systemctl status mariadb
-● mariadb.service - MariaDB database server
-   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
-   Active: active (running) since Tue 2017-10-31 12:38:25 CET; 1s ago
-  Process: 11254 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
-  Process: 11222 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
- Main PID: 11253 (mysqld_safe)
-   CGroup: /system.slice/mariadb.service
-           ├─11253 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
-           └─11420 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysq...
-Oct 31 12:38:22 centos7.fenestros.loc systemd[1]: Starting MariaDB database s...
+MariaDB [(none)]> SHOW VARIABLES LIKE 'ft_stopword_list';
-Oct 31 12:38:23 centos7.fenestros.loc mariadb-prepare-db-dir[11222]: Database...
+Empty set (0.00 sec)
-Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys...
-Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys...
+MariaDB [(none)]>
-Oct 31 12:38:25 centos7.fenestros.loc systemd[1]: Started MariaDB database se...
-Hint: Some lines were ellipsized, use -l to show in full.
 </code>
-====La Commande mysqladmin====
+<WRAP center round important 60%>
+**Important** :
+La dernière variable peut contenir une liste de mots qui ne seront pas indexés. Notez que chaque fois que vous modifiez une des ces variables, il est nécessaire de reconstruire l'index FULLTEXT avec la commande REPAIR TABLE table QUICK;
+</WRAP>
+==Limitations==
+Veuillez garder en tête les points suivants :
+  * Pour effectuer une recherche FULLTEXT, il **faut** utiliser MATCH AGAINST,
+  * Dès que vous utilisez MATCH AGAINST, le serveur utilisera une recherche FULLTEXT même si l'optimiseur trouve un autre index qui aurait été meilleur,
+  * Les résultats en mode langage naturel sont triés par la pertinence. Ceci n'est **pas** le cas pour les deux autres modes,
+  * Un index FULLTEXT ne peut **pas** être utilisé comme index couvrant.
+=====La Commande EXPLAIN=====
-La comande **mysqladmin** peut aussi être utilisée pour arrêter le serveur à condition que l'utilisateur qui l'invoque possède le privilège **shutdown**. Par exemple :
+La commande EXPLAIN est utilisée pour voir le plan d'exécution de la requête choisi par l'optimiseur :
 <code>
-[root@centos7 ~]# /bin/mysqladmin -uroot -p shutdown
+MariaDB [(none)]> EXPLAIN SELECT film_id,title,description FROM film_text WHERE MATCH(title,description) AGAINST('MariaDB')\G
-Enter password:
+*************************** 1. row ***************************
-[root@centos7 ~]# systemctl status mariadb
+           id: 1
-● mariadb.service - MariaDB database server
+  select_type: SIMPLE
-   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
+        table: film_text
-   Active: inactive (dead) since Tue 2017-10-31 12:53:09 CET; 4s ago
+         type: fulltext
-  Process: 11254 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
+possible_keys: idx_title_description
-  Process: 11253 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
+          key: idx_title_description
-  Process: 11222 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
+      key_len: 0
- Main PID: 11253 (code=exited, status=0/SUCCESS)
+          ref:
+         rows: 1
+        Extra: Using where
+row in set (0.00 sec)
-Oct 31 12:38:22 centos7.fenestros.loc systemd[1]: Starting MariaDB database s...
+MariaDB [(none)]>
-Oct 31 12:38:23 centos7.fenestros.loc mariadb-prepare-db-dir[11222]: Database...
-Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys...
-Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys...
-Oct 31 12:38:25 centos7.fenestros.loc systemd[1]: Started MariaDB database se...
-Hint: Some lines were ellipsized, use -l to show in full.
 </code>
+La sortie de la commande démontre 10 colonnes dont les plus importantes sont **type**, **possible_keys**, **keys**, **key_len**, **rows** et **extra** :
 <WRAP center round important 60%>
-**Important** - Le nom d'utilisateur peut être accoler ou non à l'option **-u**. Par exemple **-uroot** et **-u root** sont tous les deux correctes. Par contre, si vous souhaitez spécifier le mot de passe de l'utilisateur dans la ligne de commande, celui-ci **doit** être accoler à l'option **-p**.
+**Important** :
+Pour l'explication des autres colonnes, voir **[[http://dev.mysql.com/doc/refman/5.0/fr/explain.html|ce lien]]**.
 </WRAP>
-=====Configuration=====
+====La Colonne type====
-Votre première prise en mains de %%MariaDB%% doit débuter par la commande **mysql** :
+Cette colonne montre comment l'optimiseur a prévu d'accéder aux données. Les cas suivants peuvent être trouvés en sachant qu'ils sont dans l'ordre du moins performant vers le plus performant :
-====Le Client MySQL====
+===ALL===
-===Utilisation===
+Un parcours complet de la table est nécessaire car il n'y a pas d'index disponible ou satisfaisant :
-MariaDB dispose d’un outil client permettant de se connecter et d’envoyer des commandes SQL au serveur.
+<code>
+MariaDB [(none)]> EXPLAIN SELECT * FROM film_text WHERE title LIKE 'The%'\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: film_text
+         type: ALL
+possible_keys: idx_title_description
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 1000
+        Extra: Using where
+row in set (0.00 sec)
-Pour démarrer une connexion au serveur MariaDB nous pouvons utiliser un C.L.I. sous Linux.
+MariaDB [(none)]>
+</code>
-Les paramètres les plus courants sont :
+<code>
+MariaDB [(none)]> SELECT * FROM film_text WHERE title LIKE 'The%'\G
+*************************** 1. row ***************************
+    film_id: 886
+      title: THEORY MERMAID
+description: A Fateful Yarn of a Composer And a Monkey who must Vanquish a Womanizer in The First Manned Space Station
+row in set (0.00 sec)
-  $ mysql –u root –p Databasename [Entrée]
+MariaDB [(none)]>
+</code>
-Où :
+===index===
-  * **-u**
+Un parcours complet d'un index est necéssaire :
-    * donne le nom de l’utilisateur. Si vous renoncez à l’option –u , le nom de login sera utilisé sous Unix/Linux et le nom ODBC sous Windows.
-  * **-p**
+<code>
-    * permet de saisir le mot de passe de MariaDB, cette option est obligatoire lorsque les utilisateurs MariaDB sont sécurisés, c’est-à-dire qu’ils ont un mot de passe.
+MariaDB [(none)]> EXPLAIN SELECT category_id FROM category\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: category
+         type: index
+possible_keys: NULL
+          key: PRIMARY
+      key_len: 1
+          ref: NULL
+         rows: 16
+        Extra: Using index
+row in set (0.01 sec)
-  * **-h**
+MariaDB [(none)]>
-    * permet de préciser le nom de l’hôte qui héberge MariaDB. Par défaut le port de communication est 3306 il est possible de changer ce port soit en configurant l’instance MariaDB soit en configurant le fichier **My.ini** sous Windows(tm) ou **my.cnf** sous Unix/Linux. (Attention: vérifiez qu’un par feu ne bloque pas ce port). Enfin vous devez vous assurer que le serveur MariaDB est configuré de sorte à autoriser les accès depuis votre ordinateur local.
+</code>
-  * **- -protocol = name**
+<code>
-    * cette option permet de spécifier le protocole de communication à utiliser. Il est rarement nécessaire d’indiquer cette option, puisque MariaDB choisit le bon protocole par défaut. Lorsque le client mysql et le serveur MariaDB ne tournent pas sur le même ordinateur, le seul protocole réseau possible est le **TCP**, dans ce cas n’oubliez pas de préciser le paramètre –h.
+MariaDB [(none)]> SELECT category_id FROM category\G
+*************************** 1. row ***************************
+category_id: 1
+*************************** 2. row ***************************
+category_id: 2
+*************************** 3. row ***************************
+category_id: 3
+*************************** 4. row ***************************
+category_id: 4
+*************************** 5. row ***************************
+category_id: 5
+*************************** 6. row ***************************
+category_id: 6
+*************************** 7. row ***************************
+category_id: 7
+*************************** 8. row ***************************
+category_id: 8
+*************************** 9. row ***************************
+category_id: 9
+*************************** 10. row ***************************
+category_id: 10
+*************************** 11. row ***************************
+category_id: 11
+*************************** 12. row ***************************
+category_id: 12
+*************************** 13. row ***************************
+category_id: 13
+*************************** 14. row ***************************
+category_id: 14
+*************************** 15. row ***************************
+category_id: 15
+*************************** 16. row ***************************
+category_id: 16
+rows in set (0.00 sec)
-  * **-P n**
+MariaDB [(none)]>
-    * permet de préciser le port de communication utilisée pour se connecter à MariaDB. Cette option est effective que lorsque la communication passe par TCP/IP.
+</code>
-  * **- -default-character-set = nom**
+===range===
-    * cette option indique le jeu de caractères utilisés dans le cadre de communication entre mysql et le serveur MariaDB. En théorie, il s’agit du même jeu de caractères que celui utilisé par défaut dans la vie de commandes sous Windows ou la console sous Linux. Les jeux de caractères pris en charge par MariaDB sont notamment Latin 1 (ISO-8559-1), latin 2 (ISO-8559-2), UTF-8 (unicode) et cp850 (le jeu de caractères DOS pour l’Europe occidentale).
-  * Databasename
+Un parcours d'une partie d'un index est nécessaire :
-    * ce dernier paramètre indique à MariaDB le nom d’une base de données, ce qui permet de l’utiliser directement dès l’ouverture de MariaDB. Si vous désirez changer ici de base de données après sa connexion vous pouvez utiliser la commande SQL **USE <NOM_DB>.
-Exemple :
+<code>
+MariaDB [(none)]> EXPLAIN SELECT * FROM category WHERE category_id > 50\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: category
+         type: range
+possible_keys: PRIMARY
+          key: PRIMARY
+      key_len: 1
+          ref: NULL
+         rows: 1
+        Extra: Using where
+row in set (0.01 sec)
-  $ mysql -u root -p -h server --protocol=tcp [Entrée]
+MariaDB [(none)]>
-  $ Password : ********** [Entrée]
+</code>
-===Options===
+<code>
+MariaDB [(none)]> SELECT * FROM category WHERE category_id > 50\G
+Empty set (0.01 sec)
-Dans la console MariaDB nous avons diverses options.
+MariaDB [(none)]>
+</code>
-^ Abréviation ^ Commande ^ Signification ^
+===index_merge===
-| \c | clear | Annule une commande en cours de saisie |
-| \h | help | Affiche la liste des commandes |
-| \q | exit ou quit | Ferme la connexion à MariaDB. Sous Unix/Linux, il est possible utilisé le raccourci ctrl+D |
-| \s | status | Affiche les informations de statut du serveur MariaDB |
-| \T[f] | tee[filename] | Enregistre tous ce qui apparaît dans la fenêtre de commandes dans le fichier indiqué |
-| L | notee | Ferme tee. Le protocole doit être repris à tout instant avec tee ou \T. Il n’est pas nécessaire de saisir de nouveau le nom de fichier. |
-| \u db | use database | La base de données saisie devient la base de données par défaut |
-| \. Fn | source file name | exécute les commandes SQL contenues dans le fichier spécifié. Les commandes doivent à séparer par des pointsvirgules. |
-NB : la fonction \c n’a aucun effet dans les chaînes de caractères( "" ou ' ').
+Plusieurs index sont utilisés simultanément :
-Notez aussi que MariaDB se souvient des dernières commandes grâce aux touches **flèche vers le haut** et **flèche vers le bas**.
+<code>
+MariaDB [(none)]> EXPLAIN SELECT * FROM rental WHERE rental_id > 10 OR inventory_id < 100\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: rental
+         type: index_merge
+possible_keys: PRIMARY,idx_fk_inventory_id
+          key: idx_fk_inventory_id,PRIMARY
+      key_len: 3,4
+          ref: NULL
+         rows: 8557
+        Extra: Using sort_union(idx_fk_inventory_id,PRIMARY); Using where
+row in set (0.01 sec)
-====LAB #1 - Configuration de Base====
+MariaDB [(none)]>
+</code>
-Saisissez donc la commande **mysql** :
+<code>
+MariaDB [(none)]> SELECT * FROM rental WHERE rental_id > 10 OR inventory_id < 100\G
+...
+*************************** 13776. row ***************************
+   rental_id: 13790
+ rental_date: 2005-08-20 12:17:27
+inventory_id: 1385
+ customer_id: 11
+ return_date: 2005-08-25 12:20:27
+    staff_id: 1
+ last_update: 2006-02-15 21:30:53
+*************************** 13777. row ***************************
+   rental_id: 13791
+ rental_date: 2005-08-20 12:21:05
+inventory_id: 1890
+ customer_id: 67
+ return_date: 2005-08-22 17:58:05
+    staff_id: 1
+ last_update: 2006-02-15 21:30:53
+*************************** 13778. row ***************************
+   rental_id: 13792
+ rental_date: 2005-08-20 12:21:37
+inventory_id: 4157
+ customer_id: 78
+ return_date: 2005-08-27 14:28:37
+    staff_id: 1
+ last_update: 2006-02-15 21:30:53
+*************************** 13779. row ***************************
+   rental_id: 13793
+ rental_date: 2005-08-20 12:22:04
+inventory_id: 2598
+ customer_id: 424
+ return_date: 2005-08-27 09:51:04
+    staff_id: 2
+ last_update: 2006-02-15 21:30:53
+...
+</code>
+===ref===
+Un index non unique est examiné. Plusieurs lignes peuvent être retournées :
 <code>
-[root@centos7 ~]# systemctl start mariadb
+MariaDB [(none)]> EXPLAIN SELECT rental_date FROM rental INNER JOIN customer USING (customer_id) WHERE customer_id=1\G
-[root@centos7 ~]# mysql
+*************************** 1. row ***************************
-Welcome to the MariaDB monitor.  Commands end with ; or \g.
+           id: 1
-Your MariaDB connection id is 2
+  select_type: SIMPLE
-Server version: 5.5.56-MariaDB MariaDB Server
+        table: customer
+         type: const
+possible_keys: PRIMARY
+          key: PRIMARY
+      key_len: 2
+          ref: const
+         rows: 1
+        Extra: Using index
+*************************** 2. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: rental
+         type: ref
+possible_keys: idx_fk_customer_id
+          key: idx_fk_customer_id
+      key_len: 2
+          ref: const
+         rows: 32
+        Extra:
+rows in set (0.01 sec)
-Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
+MariaDB [(none)]>
+</code>
-Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
+<code>
+MariaDB [(none)]> SELECT rental_date FROM rental INNER JOIN customer USING (customer_id) WHERE customer_id=1\G
+*************************** 1. row ***************************
+rental_date: 2005-05-25 11:30:37
+*************************** 2. row ***************************
+rental_date: 2005-05-28 10:35:23
+*************************** 3. row ***************************
+rental_date: 2005-06-15 00:54:12
+*************************** 4. row ***************************
+rental_date: 2005-06-15 18:02:53
+*************************** 5. row ***************************
+rental_date: 2005-06-15 21:08:46
+*************************** 6. row ***************************
+rental_date: 2005-06-16 15:18:57
+*************************** 7. row ***************************
+rental_date: 2005-06-18 08:41:48
+*************************** 8. row ***************************
+rental_date: 2005-06-18 13:33:59
+*************************** 9. row ***************************
+rental_date: 2005-06-21 06:24:45
+*************************** 10. row ***************************
+rental_date: 2005-07-08 03:17:05
+*************************** 11. row ***************************
+rental_date: 2005-07-08 07:33:56
+*************************** 12. row ***************************
+rental_date: 2005-07-09 13:24:07
+*************************** 13. row ***************************
+rental_date: 2005-07-09 16:38:01
+*************************** 14. row ***************************
+rental_date: 2005-07-11 10:13:46
+*************************** 15. row ***************************
+rental_date: 2005-07-27 11:31:22
+*************************** 16. row ***************************
+rental_date: 2005-07-28 09:04:45
+*************************** 17. row ***************************
+rental_date: 2005-07-28 16:18:23
+*************************** 18. row ***************************
+rental_date: 2005-07-28 17:33:39
+*************************** 19. row ***************************
+rental_date: 2005-07-28 19:20:07
+*************************** 20. row ***************************
+rental_date: 2005-07-29 03:58:49
+*************************** 21. row ***************************
+rental_date: 2005-07-31 02:42:18
+*************************** 22. row ***************************
+rental_date: 2005-08-01 08:51:04
+*************************** 23. row ***************************
+rental_date: 2005-08-02 15:36:52
+*************************** 24. row ***************************
+rental_date: 2005-08-02 18:01:38
+*************************** 25. row ***************************
+rental_date: 2005-08-17 12:37:54
+*************************** 26. row ***************************
+rental_date: 2005-08-18 03:57:29
+*************************** 27. row ***************************
+rental_date: 2005-08-19 09:55:16
+*************************** 28. row ***************************
+rental_date: 2005-08-19 13:56:54
+*************************** 29. row ***************************
+rental_date: 2005-08-21 23:33:57
+*************************** 30. row ***************************
+rental_date: 2005-08-22 01:27:57
+*************************** 31. row ***************************
+rental_date: 2005-08-22 19:41:37
+*************************** 32. row ***************************
+rental_date: 2005-08-22 20:03:46
+rows in set (0.00 sec)
 MariaDB [(none)]>
 </code>
-pour visualiser la liste des bases de données par défaut :
+<WRAP center round important 60%>
+**Important** :
+Il existe une variante de ref, appelé ref_or_null. Dans ce cas, il y a une deuxième passe pour retrouver les valeurs NULL.
+</WRAP>
+===eq_ref===
+Un index unique ou une clé primaire est examiné :
 <code>
-MariaDB [(none)]> show databases;
+MariaDB [(none)]> EXPLAIN SELECT email FROM customer INNER JOIN rental USING (customer_id) WHERE rental_date > '2006-04-10 00:00:00' AND RENTAL_DATE < '2006-05-24 00:00:00'\G
-+--------------------+
+*************************** 1. row ***************************
-| Database           |
+           id: 1
-+--------------------+
+  select_type: SIMPLE
-| information_schema |
+        table: rental
-| mysql              |
+         type: range
-| performance_schema |
+possible_keys: rental_date,idx_fk_customer_id
-| test               |
+          key: rental_date
-+--------------------+
+      key_len: 8
- rows in set (0.01 sec)
+          ref: NULL
+         rows: 1
+        Extra: Using where; Using index
+*************************** 2. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: customer
+         type: eq_ref
+possible_keys: PRIMARY
+          key: PRIMARY
+      key_len: 2
+          ref: sakila.rental.customer_id
+         rows: 1
+        Extra:
+ rows in set (0.00 sec)
 MariaDB [(none)]>
 </code>
-Ensuite changez de base de données avec la commande :
+<WRAP center round important 60%>
+**Important** :
+Ce type est le meilleur t(ype d'accès possible.
+</WRAP>
+===Cas Spécifiques===
+==const==
+Une seule ligne a besoin d'être lue dans la table concernée :
+<code>
+MariaDB [(none)]> EXPLAIN SELECT * FROM category WHERE category_id=1\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: category
+         type: const
+possible_keys: PRIMARY
+          key: PRIMARY
+      key_len: 1
+          ref: const
+         rows: 1
+        Extra:
+row in set (0.00 sec)
+MariaDB [(none)]> SELECT * FROM category WHERE category_id=1\G
+*************************** 1. row ***************************
+category_id: 1
+       name: Action
+last_update: 2006-02-15 04:46:27
+row in set (0.00 sec)
+MariaDB [(none)]>
+</code>
+==System==
+Identique au type précédent dans le cas où la table est une table à une seule ligne.
+==NULL==
+Aucun parcours de table ou d'index n'est nécessaire :
+<code>
+MariaDB [(none)]> EXPLAIN SELECT COUNT(*) FROM film_text\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: NULL
+         type: NULL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: NULL
+        Extra: Select tables optimized away
+row in set (0.00 sec)
+MariaDB [(none)]> SELECT COUNT(*) FROM film_text\G
+*************************** 1. row ***************************
+COUNT(*): 1000
+row in set (0.01 sec)
+MariaDB [(none)]>
+</code>
+====Les Colonnes possible_keys, keys et key_len====
+La colonne **possible_keys** contient l'ensemble des index que l'optimiseur va considérer pour la requête concernée tandis que la colonne **key** indique l'index qui serait utiliser.
+Une valeur NULL dans la colonne **possible_keys** indique qu'il n'existe pas d'index utilisable tandis que la valeur NULL dans la colonne **key** indique que l'optimiseur n'a pas trouvé d'index pertinent.
+La colonne **key_len** indique la taille en octets de l'index choisi.
+Téléchargez la base de données exemple **world** :
+<code>
+[root@centos ~]# wget https://downloads.mysql.com/docs/world-db.tar.gz
+</code>
+Installez la base de données exemple **world** :
+<code>
+[root@centos ~]# mysql -uroot -p
+Enter password:
+Welcome to the MariaDB monitor.  Commands end with ; or \g.
+Your MariaDB connection id is 8
+Server version: 5.5.33-cll-lve MariaDB Community Server (GPL) by Atomicorp
+Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
+Oracle is a registered trademark of Oracle Corporation and/or its
+affiliates. Other names may be trademarks of their respective
+owners.
+Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
+MariaDB [(none)]> CREATE DATABASE world;
+Query OK, 1 row affected (0.00 sec)
+MariaDB [(none)]> exit
+Bye
+[root@centos ~]# gunzip world-db.tar.gz
+[root@centos ~]# mysql -uroot -p world < world-db/world.sql
+Enter password:
+[root@centos ~]#
+</code>
+Voyons un exemple maintenant avec cette base de données :
 <code>
-MariaDB [(none)]> USE mysql;
+MariaDB [(none)]> USE world;
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 Database changed
-MariaDB [mysql]>
+MariaDB [(none)]> ALTER TABLE City ADD INDEX idx_name_district(Name,District);
+Query OK, 4079 rows affected (0.11 sec)
+Records: 4079  Duplicates: 0  Warnings: 0
+MariaDB [(none)]> EXPLAIN SELECT * FROM City WHERE Name LIKE 'San%'\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: City
+         type: range
+possible_keys: idx_name_district
+          key: idx_name_district
+      key_len: 35
+          ref: NULL
+         rows: 141
+        Extra: Using where
+row in set (0.01 sec)
+MariaDB [(none)]>
 </code>
-Afin de consulter les tables présentes dans la base, utilisez la commande :
+<WRAP center round important 60%>
+**Important** :
+Notez que la taille de l'index utilisé est de 35 octets.
+</WRAP>
+====La Colonne rows====
+La colonne **rows** contient une estimation du nombre de lignes que le moteur doit parcourir avant de pouvoir retourner un résultat de la requête :
 <code>
-MariaDB [mysql]> SHOW TABLES;
+MariaDB [(none)]> EXPLAIN SELECT * FROM City CROSS JOIN Country\G
-+---------------------------+
+*************************** 1. row ***************************
-| Tables_in_mysql           |
+           id: 1
-+---------------------------+
+  select_type: SIMPLE
-| columns_priv              |
+        table: Country
-| db                        |
+         type: ALL
-| event                     |
+possible_keys: NULL
-| func                      |
+          key: NULL
-| general_log               |
+      key_len: NULL
-| help_category             |
+          ref: NULL
-| help_keyword              |
+         rows: 239
-| help_relation             |
+        Extra:
-| help_topic                |
+*************************** 2. row ***************************
-| host                      |
+           id: 1
-| ndb_binlog_index          |
+  select_type: SIMPLE
-| plugin                    |
+        table: City
-| proc                      |
+         type: ALL
-| procs_priv                |
+possible_keys: NULL
-| proxies_priv              |
+          key: NULL
-| servers                   |
+      key_len: NULL
-| slow_log                  |
+          ref: NULL
-| tables_priv               |
+         rows: 4079
-| time_zone                 |
+        Extra: Using join buffer
-| time_zone_leap_second     |
+ rows in set (0.00 sec)
-| time_zone_name            |
-| time_zone_transition      |
-| time_zone_transition_type |
-| user                      |
-+---------------------------+
- rows in set (0.00 sec)
-MariaDB [mysql]>
+MariaDB [(none)]>
 </code>
-Pour consulter une table spécifique, utilisez la commande **DESCRIBE** :
+<WRAP center round important 60%>
+**Important** :
+Dans l'exemple ci-dessus, MariaDB estime avoir besoin de parcourir 239*4079 lignes soit 974 881 enregistrements !!
+</WRAP>
+Pour prouver qu'il s'agit bien d'une estimation, saisissez la requête suivante :
 <code>
-MariaDB [mysql]> DESCRIBE user;
+MariaDB [(none)]> EXPLAIN SELECT * FROM City WHERE Name LIKE 'San%'\G
-+------------------------+-----------------------------------+------+-----+---------+-------+
+*************************** 1. row ***************************
-| Field                  | Type                              | Null | Key | Default | Extra |
+           id: 1
-+------------------------+-----------------------------------+------+-----+---------+-------+
+  select_type: SIMPLE
-| Host                   | char(60)                          | NO   | PRI |         |       |
+        table: City
-| User                   | char(16)                          | NO   | PRI |         |       |
+         type: range
-| Password               | char(41)                          | NO   |     |         |       |
+possible_keys: idx_name_district
-| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
+          key: idx_name_district
-| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
+      key_len: 35
-| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
+          ref: NULL
-| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
+         rows: 141
-| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
+        Extra: Using where
-| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
+row in set (0.05 sec)
-| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
-| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
-| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
-| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
-| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
-| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
-| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
-| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
-| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
-| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
-| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
-| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
-| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
-| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
-| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
-| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
-| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
-| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
-| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
-| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
-| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
-| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
-| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
-| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
-| ssl_cipher             | blob                              | NO   |     | NULL    |       |
-| x509_issuer            | blob                              | NO   |     | NULL    |       |
-| x509_subject           | blob                              | NO   |     | NULL    |       |
-| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
-| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
-| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
-| max_user_connections   | int(11)                           | NO   |     | 0       |       |
-| plugin                 | char(64)                          | NO   |     |         |       |
-| authentication_string  | text                              | NO   |     | NULL    |       |
-+------------------------+-----------------------------------+------+-----+---------+-------+
-rows in set (0.00 sec)
-MariaDB [mysql]>
+MariaDB [(none)]> SELECT COUNT(*) FROM City WHERE Name LIKE 'San%'\G
+*************************** 1. row ***************************
+COUNT(*): 110
+row in set (0.02 sec)
+MariaDB [(none)]>
 </code>
-Pour visualiser la liste des utilisateurs autorisés pour %%MariaDB%%, utilisez la commande suivante :
+<WRAP center round important 60%>
+**Important** :
+Si vous vous rendez compte que les estimations sont largement erronées vous pouvez utilisez la commande ANALYZE TABLE pour mettre à jour les statistiques sur la table. D'une manière générale, plus élevée est la valeur de **rows**, plus lente sera la requête.
+</WRAP>
-<code>
+====La Colonne extra====
-MariaDB [mysql]> SELECT host, user, password FROM user;
-+-----------------------+-------+-----------+
-| host                  | user  | password  |
-+-----------------------+-------+-----------+
-| localhost             | root  |           |
-| centos7.fenestros.loc | root  |           |
-| 127.0.0.1             | root  |           |
-| ::1                   | root  |           |
-+-----------------------+-------+-----------+
-rows in set (0.00 sec)
-MariaDB [mysql]>
+La colonne extra donne des informations supplémentaires concernant la requête :
-</code>
-Vous noterez que l'utilisateur root, l'administrateur de %%MariaDB%%, n'a pas de mot de passe !
+  * **Using where** - visible quand le serveur n'a pas pu résoudre une clause WHERE en utilisant un index,
+  * **Using index** - le serveur va utiliser un index couvrant,
+  * **Using temporary** - le serveur va utiliser une table temporaire pour effectuer un tri,
+  * **Using filesort** - le serveur doit trier les résultats de la requête.
-Il faut par conséquence en définir un en urgence !
+=====La Commande EXPLAIN EXTENDED=====
-Pour sortir de l'administration de %%MariaDB%%, utilisez la commande **exit** :
+La commande **EXPLAIN EXTENDED** est disponible dans MariaDB depuis la version 5.1. EXPLAIN EXTENDED donne une approximation, en pourcentage par rapport à la valeur de **rows**, du nombre de lignes qui seront retournées lors de la requête :
 <code>
-MariaDB [mysql]> exit
+MariaDB [(none)]> EXPLAIN EXTENDED SELECT ID FROM City WHERE ID < 50\G
-Bye
+*************************** 1. row ***************************
-[root@centos7 ~]#
+           id: 1
+  select_type: SIMPLE
+        table: City
+         type: range
+possible_keys: PRIMARY
+          key: PRIMARY
+      key_len: 4
+          ref: NULL
+         rows: 50
+     filtered: 100.00
+        Extra: Using where; Using index
+row in set, 1 warning (0.00 sec)
+MariaDB [(none)]> SELECT COUNT(ID) FROM City WHERE ID < 50\G
+*************************** 1. row ***************************
+COUNT(ID): 49
+row in set (0.01 sec)
+MariaDB [(none)]>
 </code>
-Pour définir le mot de passe **fenestros** pour root, il convient de saisir la commande suivante :
+Par contre cette valeur n'est qu'une estimation :
 <code>
-[root@centos7 ~]# mysqladmin -u root password fenestros
+MariaDB [(none)]> EXPLAIN EXTENDED SELECT Name FROM City WHERE CountryCode LIKE 'F%' AND district LIKE 'F%' AND population >100000\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: City
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 4079
+     filtered: 100.00
+        Extra: Using where
+row in set, 1 warning (0.00 sec)
+MariaDB [(none)]> SELECT Name FROM City WHERE CountryCode LIKE 'F%' AND district LIKE 'F%' AND population >100000\G
+*************************** 1. row ***************************
+Name: Besançon
+row in set (0.07 sec)
+MariaDB [(none)]>
 </code>
-Lors de la prochaine tentative de connexion en tant que root, vous obtiendrez un message d'erreur car le mot de passe est maintenant non-null :
+Malgré le peu de fiabilité dans le résultat de la colonne **filtered**, EXPLAIN EXTENDED est une clause bien utile car elle permet de voir la requête réécrite à partir du plan d'exécution :
 <code>
-[root@centos7 ~]# mysql -u root
+MariaDB [(none)]> EXPLAIN EXTENDED SELECT COUNT(*) FROM Country\G
-ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: NULL
+         type: NULL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: NULL
+     filtered: NULL
+        Extra: Select tables optimized away
+row in set, 1 warning (0.00 sec)
+MariaDB [(none)]> SHOW WARNINGS\G
+*************************** 1. row ***************************
+  Level: Note
+   Code: 1003
+Message: select count(0) AS `COUNT(*)` from `world`.`Country`
+row in set (0.00 sec)
+MariaDB [(none)]>
 </code>
-Pour vous connecter en tant que l'administrateur de la base il faut maintenant utiliser la commande suivante :
+=====Optimisation des Requêtes=====
+====Isolation des Colonnes====
+L'encapsulation d'une colonne dans une fonction interdit l'utilisation d'un index :
 <code>
-[root@centos7 ~]# mysql -u root -p mysql
+MariaDB [(none)]> USE sakila;
-Enter password: fenestros
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
-Welcome to the MariaDB monitor.  Commands end with ; or \g.
+Database changed
-Your MariaDB connection id is 5
+MariaDB [(none)]> EXPLAIN SELECT * FROM rental WHERE TO_DAYS(CURRENT_DATE()) - TO_DAYS(rental_date) < 10\G
-Server version: 5.5.56-MariaDB MariaDB Server
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: rental
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 15629
+        Extra: Using where
+row in set (0.00 sec)
-Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
+MariaDB [(none)]> EXPLAIN SELECT * FROM rental WHERE rental_date > CURRENT_DATE() + INTERVAL 10 DAY\G
+*************************** 1. row ***************************
-Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
+           id: 1
+  select_type: SIMPLE
+        table: rental
+         type: range
+possible_keys: rental_date
+          key: rental_date
+      key_len: 8
+          ref: NULL
+         rows: 1
+        Extra: Using where
+row in set (0.00 sec)
-MariaDB [mysql]>
+MariaDB [(none)]>
 </code>
 <WRAP center round important 60%>
-**Important** - Notez l'utilisation de l'option **-p** qui indique à **MariaDB** que vous souhaitez saisir un mot de passe. Le mot de passe n'est **pas** mysql mais **fenestros**, comme démontre l'exemple ci-dessus. Bien entendu le mot de passe **fenestros** n'apparaît pas réellement en clair. L'utilisation de l'option **mysql** dans la ligne de commande indique simplement que nous souhaitons se connecter à la base **mysql** dès la connexion.
+**Important** :
+La réécriture de la requête sans encapsuler une colonne dans une fonction produit une requête plus performante ( range > ALL ).
 </WRAP>
-Saisissez la commande suivante pour vérifier la table des utilisateurs :
+====Jointures====
+Quand MariaDB effectue des jointures il le fait une à une et successivement. Par exemple, dans le cas de 4 tables, table1, table2, table3, table4, la table1 est jointe à la table2 produisant une table table12. La table12 est ensite jointe à la table3 produisant la table123. La table123 est ensuite jointe à la table4 pour produire la table1234.
+Dans le cas d'un INNER JOIN, MariaDB peut décider de ne pas respecter cet ordre :
 <code>
-MariaDB [mysql]> SELECT host, user, password FROM user;
+MariaDB [(none)]> EXPLAIN SELECT email FROM customer INNER JOIN rental ON customer.customer_id = rental.customer_id WHERE rental_date > '2006-04-10 00:00:00' AND rental_date < '2006-05-24 00:00:00'\G
-+-----------------------+-------+-------------------------------------------+
+*************************** 1. row ***************************
-| host                  | user  | password                                  |
+           id: 1
-+-----------------------+-------+-------------------------------------------+
+  select_type: SIMPLE
-| localhost             | root  | *00269BA49BEC800F9CCF34C20C1FD83E0236B89A |
+        table: rental
-| centos7.fenestros.loc | root  |                                           |
+         type: range
-| 127.0.0.1             | root  |                                           |
+possible_keys: rental_date,idx_fk_customer_id
-| ::1                   | root  |                                           |
+          key: rental_date
-+-----------------------+-------+-------------------------------------------+
+      key_len: 8
- rows in set (0.00 sec)
+          ref: NULL
+         rows: 1
+        Extra: Using where; Using index
+*************************** 2. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: customer
+         type: eq_ref
+possible_keys: PRIMARY
+          key: PRIMARY
+      key_len: 2
+          ref: sakila.rental.customer_id
+         rows: 1
+        Extra:
+ rows in set (0.01 sec)
-MariaDB [mysql]>
+MariaDB [(none)]>
 </code>
 <WRAP center round important 60%>
-**Important** - Notez que non seulement le mot de passe de root est présent mais qu'il a été crypté. Vous avez aussi la possibilité de sécuriser votre installation de MariaDB en utilisant le script **/usr/bin/mysql_secure_installation**.
+**Important** :
+L'ordre de jointure ici est rental vers customer et non pas customer vers rental.
 </WRAP>
-====LAB #2 - Configuration Avancée====
+Pourquoi l'optimiseur a choisi d'inverser l'ordre de jointure ? Dans l'exemple précédent il faut parcourir 1 ligne. Forcez maintenant la jointure dans l'autre direction :
-La configuration avancée du serveur MariaDB se fait soit :
+<code>
+MariaDB [(none)]> EXPLAIN SELECT email FROM customer STRAIGHT_JOIN rental ON customer.customer_id = rental.customer_id WHERE rental_date > '2006-04-10 00:00:00' AND rental_date < '2006-05-24 00:00:00'\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: customer
+         type: ALL
+possible_keys: PRIMARY
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 577
+        Extra:
+*************************** 2. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: rental
+         type: range
+possible_keys: rental_date,idx_fk_customer_id
+          key: rental_date
+      key_len: 8
+          ref: NULL
+         rows: 1
+        Extra: Using where; Using index; Using join buffer
+rows in set (0.00 sec)
+MariaDB [(none)]>
+</code>
+Dans le cas ci-dessus, l'optimiseur a vu qu'il faut parcourir 577*1 lignes !!
+<WRAP center round important 60%>
+**Important** :
+Pour les jointure externes, LEFT JOIN et RIGHT JOIN, l'ordre est important.
+</WRAP>
+====Indexes====
-  * en éditant le fichier **/etc/my.cnf**,
+===USE INDEX===
-  * en passant des paramètres à l'exécutable mysqld,
-  * en paramétrant le serveur dynamiquement.
-===Le fichier my.cnf===
+Il est possible d'indiquer à MariaDB d'utiliser un index spécifique ou d'ignorer un index :
 <code>
-[root@centos7 ~]# cat /etc/my.cnf
+MariaDB [(none)]> EXPLAIN SELECT email FROM customer INNER JOIN rental USE INDEX (idx_fk_customer_id) ON customer.customer_id = rental.customer_id WHERE rental_date > '2006-04-10 00:00:00' AND rental_date < '2006-05-24 00:00:00'\G
-[mysqld]
+*************************** 1. row ***************************
-datadir=/var/lib/mysql
+           id: 1
-socket=/var/lib/mysql/mysql.sock
+  select_type: SIMPLE
-# Disabling symbolic-links is recommended to prevent assorted security risks
+        table: customer
-symbolic-links=0
+         type: ALL
-# Settings user and group are ignored when systemd is used.
+possible_keys: PRIMARY
-# If you need to run mysqld under a different user or group,
+          key: NULL
-# customize your systemd unit file for mariadb according to the
+      key_len: NULL
-# instructions in http://fedoraproject.org/wiki/Systemd
+          ref: NULL
+         rows: 577
+        Extra:
+*************************** 2. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: rental
+         type: ref
+possible_keys: idx_fk_customer_id
+          key: idx_fk_customer_id
+      key_len: 2
+          ref: sakila.customer.customer_id
+         rows: 13
+        Extra: Using where
+rows in set (0.00 sec)
+</code>
-[mysqld_safe]
+<WRAP center round important 60%>
-log-error=/var/log/mariadb/mariadb.log
+**Important** :
-pid-file=/var/run/mariadb/mariadb.pid
+Dans le cas d'utilisation de la clause USE INDEX, l'auteur de la requête indique à l'optimiseur d'utiliser l'index passé en paramètre. Cependant l'optimiseur peut décider d'utiliser un autre index si celui-si s'avère plus approprié. Dans le cas ci-dessus l'optimiseur a été d'accord avec l'auteur.
+</WRAP>
-#
+===FORCE INDEX===
-# include all files from the config directory
-#
+<code>
-!includedir /etc/my.cnf.d
+MariaDB [(none)]> EXPLAIN SELECT email FROM customer INNER JOIN rental FORCE INDEX (idx_fk_customer_id) ON customer.customer_id = rental.customer_id WHERE rental_date > '2006-04-10 00:00:00' AND rental_date < '2006-05-24 00:00:00'\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: customer
+         type: ALL
+possible_keys: PRIMARY
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 577
+        Extra:
+*************************** 2. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: rental
+         type: ref
+possible_keys: idx_fk_customer_id
+          key: idx_fk_customer_id
+      key_len: 2
+          ref: sakila.customer.customer_id
+         rows: 13
+        Extra: Using where
+rows in set (0.01 sec)
 </code>
-Ce fichier n'est pas cependant le seul endroit où est configuré le serveur. En effet, le serveur lit des directives des fichiers **/etc/my.cnf** /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf dans l'ordre, comme démontre la sortie de la commande **/usr/libexec/mysqld - -help - -verbose** :
+<WRAP center round important 60%>
+**Important** :
+Dans le cas d'utilisation de la clause FORCE INDEX, l'auteur de la requête indique à l'optimiseur d'utiliser l'index passé en paramètre. L'index sera utilisé sauf dans le cas où il n'est pas utilisable.
+</WRAP>
+===IGNORE INDEX===
 <code>
-[root@centos7 ~]# /usr/libexec/mysqld --help --verbose | grep my.cnf
+MariaDB [(none)]> EXPLAIN SELECT email FROM customer INNER JOIN rental IGNORE INDEX (idx_fk_customer_id,rental_date) ON customer.customer_id = rental.customer_id WHERE rental_date > '2006-04-10 00:00:00' AND rental_date < '2006-05-24 00:00:00'\G
-13:10:57 [Note] Plugin 'FEEDBACK' is disabled.
+*************************** 1. row ***************************
-/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
+           id: 1
-                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
+  select_type: SIMPLE
-                      my.cnf files
+        table: rental
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 15629
+        Extra: Using where
+*************************** 2. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: customer
+         type: eq_ref
+possible_keys: PRIMARY
+          key: PRIMARY
+      key_len: 2
+          ref: sakila.rental.customer_id
+         rows: 1
+        Extra:
+rows in set (0.01 sec)
+MariaDB [(none)]>
 </code>
 <WRAP center round important 60%>
-**Important** : En cas de duplicité de directives, c'est la dernière lue qui l'emporte. Si vous avez un autre fichier de configuration qui n'est pas un de ceux mentionné, vous pouvez l'utiliser ainsi : /usr/libexec/mysqld --defaults-file=/chemin/votre_fichier.
+**Important** :
+Dans le cas d'utilisation de la clause IGNORE INDEX, l'auteur de la requête indique à l'optimiseur de ne **pas** utiliser le ou les index passés en paramètre. De ce fait notez l'inversion de l'ordre de jointure.
 </WRAP>
-Le fichier my.cnf est organisé en sections, aussi appelées des groupes. Ces sections font référence au programme concerné par la configuration. Par exemple la section **[mysqld]** fait référence au serveur, tandis qu'une section [mysql] ferait référence au client en mode texte. Il est aussi possible de trouver les sections suivantes :
+====CLAUSES LENTES====
-  * [mysqldump],
+Les clauses d’agrégation telles SUM(), MAX() et MIN() provoquent en générale des requêtes lentes car il faut lire l'ensemble de la table pour obtenir un résultat. C'est le même cas pour la clause COUNT() avec le moteur InnoDB, mais pas avec le moteur MyISAM. Le moteur MyISAM stocke dans ces métadonnées le nombre d’enregistrements de la table :
-  * [mysqladmin],
-  * [mysqlhotcopy],
+<code>
-  * [myisamchk],
+MariaDB [(none)]> SHOW TABLE STATUS WHERE Name = 'film_text'\G
-  * [client],
+*************************** 1. row ***************************
-  * etc.
+           Name: film_text
+         Engine: MyISAM
+        Version: 10
+     Row_format: Dynamic
+           Rows: 1000
+ Avg_row_length: 119
+    Data_length: 119616
+Max_data_length: 281474976710655
+   Index_length: 205824
+      Data_free: 0
+ Auto_increment: NULL
+    Create_time: 2014-02-01 11:48:00
+    Update_time: 2014-02-01 11:48:16
+     Check_time: NULL
+      Collation: utf8_general_ci
+       Checksum: NULL
+ Create_options:
+        Comment:
+row in set (0.00 sec)
+MariaDB [(none)]> EXPLAIN SELECT COUNT(*) FROM film_text\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: NULL
+         type: NULL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: NULL
+        Extra: Select tables optimized away
+row in set (0.00 sec)
+MariaDB [(none)]>
+</code>
 <WRAP center round important 60%>
-**Important** - [client] est une section qui configure **tous** les clients. [mysqld] peut être remplacé par [server]. Toute ligne commençant par le caractère **#** ou **;** est un **commentaire**.
+**Important** :
+Dans le cas ci-dessus la valeur de la colonne **type** est **NULL** indiquant qu'aucun accès à la table n'est nécessaire.
 </WRAP>
-Le fichier de configuration peut aussi contenir des lignes **!include** qui référencent des fichiers tiers de configuration ainsi que des lignes **!includedir** qui référencent des répertoires contenant plusieurs fichiers de configuration. Attention, dans ce dernier cas, l'administrateur n'a aucun contrôle sur l'ordre de lecture des fichiers par le serveur :
+====Sous-requêtes====
+MariaDB exécute parfois très lentement des sous-requêtes. Privilégiez donc les jointures plutôt que des sous-requêtes :
 <code>
-[root@centos7 ~]# ls -l /etc/my.cnf.d
+MariaDB [(none)]> USE world;
-total 12
+Reading table information for completion of table and column names
--rw-r--r--. 1 root root 295 Apr 30  2017 client.cnf
+You can turn off this feature to get a quicker startup with -A
--rw-r--r--. 1 root root 232 Apr 30  2017 mysql-clients.cnf
--rw-r--r--. 1 root root 744 Apr 30  2017 server.cnf
+Database changed
+MariaDB [(none)]> SELECT Language FROM CountryLanguage WHERE CountryCode = (SELECT Code FROM Country WHERE Name='France');
++------------+
+| Language   |
++------------+
+| Arabic     |
+| French     |
+| Italian    |
+| Portuguese |
+| Spanish    |
+| Turkish    |
++------------+
+rows in set (0.03 sec)
+MariaDB [(none)]> SELECT Language FROM CountryLanguage INNER JOIN Country ON Code=CountryCode WHERE Name='France';
++------------+
+| Language   |
++------------+
+| Arabic     |
+| French     |
+| Italian    |
+| Portuguese |
+| Spanish    |
+| Turkish    |
++------------+
+rows in set (0.01 sec)
+MariaDB [(none)]>
 </code>
+====Moteurs====
+===MyISAM===
+Le paramètre le plus important pour le moteur MyISAM est le key_buffer_size :
 <code>
-[root@centos7 ~]# ls -l /etc/my.cnf.d
+MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'key_buffer_size';
-total 12
++-----------------+---------+
--rw-r--r--. 1 root root 295 Apr 30  2017 client.cnf
+| Variable_name   | Value   |
--rw-r--r--. 1 root root 232 Apr 30  2017 mysql-clients.cnf
++-----------------+---------+
--rw-r--r--. 1 root root 744 Apr 30  2017 server.cnf
+| key_buffer_size | 8388608 |
-[root@centos7 ~]# cat /etc/my.cnf.d/client.cnf
++-----------------+---------+
-#
+row in set (0.00 sec)
-# These two groups are read by the client library
-# Use it for options that affect all clients, but not the server
-#
+MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Key_%';
++------------------------+-------+
+| Variable_name          | Value |
++------------------------+-------+
+| Key_blocks_not_flushed | 0     |
+| Key_blocks_unused      | 7239  |
+| Key_blocks_used        | 9     |
+| Key_read_requests      | 39    |
+| Key_reads              | 9     |
+| Key_write_requests     | 0     |
+| Key_writes             | 0     |
++------------------------+-------+
+rows in set (0.01 sec)
-[client]
+MariaDB [(none)]>
+</code>
-# This group is not read by mysql client library,
+<WRAP center round important 60%>
-# If you use the same .cnf file for MySQL and MariaDB,
+**Important** :
-# use it for MariaDB-only client options
+La variable **Key_reads** indique le nombre de requêtes de lectures qui n'ont pas pu être satisfaites par le cache. La variable **Key_read_requests** indique le nombre de lectures totale. La formule (1 - Key_reads / Key_read_requests ) * 100 donne une indication de l'efficacité du cache. Dans notre cas la valeur est de 76,92%. Une valeur basse indique qu'il faut augmenter la taille de key_buffer_size.
-[client-mariadb]
+</WRAP>
-[root@centos7 ~]# cat /etc/my.cnf.d/mysql-clients.cnf
+===InnoDB==
-#
-# These groups are read by MariaDB command-line tools
-# Use it for options that affect only one utility
-#
-[mysql]
+Les paramètres les plus importants pour le moteur **InnoDB** sont résumés dans la table suivante :
-[mysql_upgrade]
+^ Paramètre ^ Valeur Suggérée ^
+| innodb_buffer_pool_size | 80% de la mémoire physique de la machine |
+| innodb_flush_log_at_trx_commit | 1 si la protection des données est primordiale, 2 si la performance est optimale |
-[mysqladmin]
-[mysqlbinlog]
+=====Partitionnement=====
-[mysqlcheck]
+Le partitionnement est utilisé pour diviser les données d'une table en parties logiques selon une règle pré-établie :
-[mysqldump]
+  * Par les données contenues dans la table. Dans ce cas on parle de partitionnement **horizontal**. Il existe 4 types possibles :
+    * **Par plages**,
+    * **Par listes**,
+    * **Par hachage**,
+    * **Par clef**,
+  * Par les colonnes de la table.  Dans ce cas on parle de partitionnement **vertical**.
-[mysqlimport]
-[mysqlshow]
+<WRAP center round important 60%>
+**Important** :
+Il est possible de faire un partitionnement vertical et un partitionnement horizontal sur la même table. Il est aussi possible de faire des partitions de partitions.
+</WRAP>
-[mysqlslap]
+Le partitionnement apporte trois avantages :
-[root@centos7 ~]# cat /etc/my.cnf.d/server.cnf
+  * Créer des tables plus grandes que la taille autorisée par un système de fichiers. Il est aussi possible de stocker des partitions à des endroits différents,
-#
-# These groups are read by MariaDB server.
-# Use it for options that only the server (but not clients) should see
-#
-# See the examples of server my.cnf files in /usr/share/mysql/
-#
-# this is read by the standalone daemon and embedded servers
+  * Supprimer très rapidement des données en détruisant la partition qui les contient. Ceci s'appelle le **Scaling Back**,
-[server]
-# this is only for the mysqld standalone daemon
+  * Optimiser certaines requêtes car les données étant organisées dans différentes partitions. Ceci s'appelle le **Partition Pruning**.
-[mysqld]
-# this is only for embedded server
+====Partitionnement Horizontal====
-[embedded]
-# This group is only read by MariaDB-5.5 servers.
+===LAB #2 - Partitionnement par Plages===
-# If you use the same .cnf file for MariaDB of different versions,
-# use this group for options that older servers don't understand
-[mysqld-5.5]
-# These two groups are only read by MariaDB servers, not by MySQL.
+Créez une base de données **transactions** ainsi que la table **transac** :
-# If you use the same .cnf file for MySQL and MariaDB,
-# you can put MariaDB-only options here
-[mariadb]
-[mariadb-5.5]
+<file>
+CREATE DATABASE transactions;
+USE transactions;
+CREATE TABLE transac
+(
+id INT UNSIGNED PRIMARY KEY,
+montant INT UNSIGNED NOT NULL,
+jour DATE NOT NULL,
+codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
+);
+</file>
+<code>
+MariaDB [(none)]> CREATE DATABASE transactions;
+Query OK, 1 row affected (0.00 sec)
+MariaDB [(none)]> USE transactions;
+Database changed
+MariaDB [(none)]>
+MariaDB [(none)]> CREATE TABLE transac
+    -> (
+    ->    id INT UNSIGNED PRIMARY KEY,
+    ->    montant INT UNSIGNED NOT NULL,
+    ->    jour DATE NOT NULL,
+    ->    codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
+    -> );
+Query OK, 0 rows affected (0.04 sec)
+MariaDB [(none)]>
 </code>
-Chaque section contient des directives au format **option=valeur** ou au format binaire, par exemple **enable-federated**.
+Imaginons qu'en étudiant les requêtes des utilisateurs sur cette table sur une période probante, nous découvrons que la majorité utilise des plages de date. Dans ce cas il serait utile de partitionner la table par la valeur de la colonne **jour**. Créer donc la table partitionnée appelée **transac_part** :
-Avec presque **300** options possibles et avec autant d'emplacements de fichiers de configuration, deux problèmes se posent.
+<file>
+CREATE TABLE transac_part
+(
+id INT UNSIGNED NOT NULL,
+montant INT UNSIGNED NOT NULL,
+jour DATE NOT NULL,
+codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
+) PARTITION BY RANGE(YEAR(jour))
+(
+PARTITION p1 VALUES LESS THAN(1997),
+PARTITION p2 VALUES LESS THAN(1998),
+PARTITION p3 VALUES LESS THAN(1999),
+PARTITION p4 VALUES LESS THAN(2000),
+PARTITION p5 VALUES LESS THAN(2001),
+PARTITION p6 VALUES LESS THAN(2002),
+PARTITION p7 VALUES LESS THAN(2003),
+PARTITION p8 VALUES LESS THAN(2004),
+PARTITION p9 VALUES LESS THAN(2005),
+PARTITION p10 VALUES LESS THAN(2006),
+PARTITION p11 VALUES LESS THAN MAXVALUE
+);
+</file>
-  * Comment savoir bien configurer le serveur,
+<code>
-  * Comment connaître avec exactitude la configuration actuelle.
+MariaDB [(none)]> CREATE TABLE transac_part
+    -> (
+    ->    id INT UNSIGNED NOT NULL,
+    ->    montant INT UNSIGNED NOT NULL,
+    ->    jour DATE NOT NULL,
+    ->    codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
+    -> ) PARTITION BY RANGE(YEAR(jour))
+    -> (
+    ->    PARTITION p1 VALUES LESS THAN(1997),
+    ->    PARTITION p2 VALUES LESS THAN(1998),
+    ->    PARTITION p3 VALUES LESS THAN(1999),
+    ->    PARTITION p4 VALUES LESS THAN(2000),
+    ->    PARTITION p5 VALUES LESS THAN(2001),
+    ->    PARTITION p6 VALUES LESS THAN(2002),
+    ->    PARTITION p7 VALUES LESS THAN(2003),
+    ->    PARTITION p8 VALUES LESS THAN(2004),
+    ->    PARTITION p9 VALUES LESS THAN(2005),
+    ->    PARTITION p10 VALUES LESS THAN(2006),
+    ->    PARTITION p11 VALUES LESS THAN MAXVALUE
+    -> );
+Query OK, 0 rows affected (0.13 sec)
-==Comment Savoir Bien Configurer le Serveur==
+MariaDB [(none)]>
+</code>
-La réponse au premier problème se trouve dans les fichiers exemples fournis par Oracle. Ces fichiers se trouvent dans le répertoire **support-files**. Oracle propose des fichiers de configuration en fonction de la mémoire du serveur :
+<WRAP center round important 60%>
+L'ordre de définition des partitions est important. Les informations liées aux partitions sont stockées dans la table **information_schema.partitions**.
+</WRAP>
+Créez ensuite une procédure pour injecter des données dans la table **transac** :
+<file>
+DELIMITER //
+CREATE PROCEDURE remplir_transaction(nbTransacs INT)
+BEGIN
+DECLARE i INT DEFAULT 1;
+DECLARE nbAlea DOUBLE;
+DECLARE _jour DATE;
+DECLARE _montant INT UNSIGNED;
+DECLARE _codePays TINYINT UNSIGNED;
+WHILE i <= nbTransacs DO
+SET nbAlea = RAND();
+SET _jour = ADDDATE('1996-01-01', FLOOR(nbAlea * 4015));
+SET _montant = FLOOR(1 + (nbAlea * 9999));
+SET nbAlea = RAND();
+SET _codePays = FLOOR(1 + (nbAlea * 6));
+INSERT INTO transac (id, montant, jour, codePays) VALUES (i, _montant, _jour, _codePays);
+SET i = i + 1;
+END WHILE;
+END //
+DELIMITER ;
+</file>
 <code>
-[root@centos7 ~]# ls -l /usr/share/mysql | grep cnf
+MariaDB [(none)]> DELIMITER //
--rw-r--r--. 1 root root   4920 Aug  4 20:28 my-huge.cnf
+MariaDB [(none)]> CREATE PROCEDURE remplir_transaction(nbTransacs INT)
--rw-r--r--. 1 root root  20438 Aug  4 20:28 my-innodb-heavy-4G.cnf
+    -> BEGIN
--rw-r--r--. 1 root root   4907 Aug  4 20:28 my-large.cnf
+    ->    DECLARE i INT DEFAULT 1;
--rw-r--r--. 1 root root   4920 Aug  4 20:28 my-medium.cnf
+    ->    DECLARE nbAlea DOUBLE;
--rw-r--r--. 1 root root   2846 Aug  4 20:28 my-small.cnf
+    ->    DECLARE _jour DATE;
--rw-r--r--. 1 root root    656 Jun  8 16:25 README.mysql-cnf
+    ->    DECLARE _montant INT UNSIGNED;
+    ->    DECLARE _codePays TINYINT UNSIGNED;
+    ->
+    ->    WHILE i <= nbTransacs DO
+    ->       SET nbAlea = RAND();
+    ->       SET _jour = ADDDATE('1996-01-01', FLOOR(nbAlea * 4015));
+    ->       SET _montant = FLOOR(1 + (nbAlea * 9999));
+    ->       SET nbAlea = RAND();
+    ->       SET _codePays = FLOOR(1 + (nbAlea * 6));
+    ->       INSERT INTO transac (id, montant, jour, codePays) VALUES (i, _montant, _jour, _codePays);
+    ->       SET i = i + 1;
+    ->    END WHILE;
+    -> END //
+Query OK, 0 rows affected (0.05 sec)
+MariaDB [(none)]> DELIMITER ;
+MariaDB [(none)]>
 </code>
-Par exemple :
+Appelez ensuite la procédure et patientez 10 minutes. A l'issue de la période, contrôlez le nombre d'enregistrements créés en sachant qu'il faut au mois 150 000 pour pouvoir voir l'impact de partitionnement :
 <code>
-[root@centos7 ~]# cat /usr/share/mysql/my-huge.cnf
+MariaDB [(none)]> CALL remplir_transaction(200000);
-# Example MariaDB config file for very large systems.
+^CCtrl-C -- sending "KILL QUERY 2" to server ...
-#
+Ctrl-C -- query aborted.
-# This is for a large system with memory of 1G-2G where the system runs mainly
+MariaDB [(none)]> SELECT COUNT(*) FROM transac;
-# MariaDB.
++----------+
-#
+| COUNT(*) |
-# MariaDB programs look for option files in a set of
++----------+
-# locations which depend on the deployment platform.
+|   191939 |
-# You can copy this option file to one of those
++----------+
-# locations. For information about these locations, do:
+row in set (0.09 sec)
-# 'my_print_defaults --help' and see what is printed under
-# Default options are read from the following files in the given order:
-# More information at: http://dev.mysql.com/doc/mysql/en/option-files.html
-#
-# In this file, you can use all long options that a program supports.
-# If you want to know which options a program supports, run the program
-# with the "--help" option.
-# The following options will be passed to all MySQL clients
+MariaDB [(none)]>
-[client]
-#password	= your_password
-port		= 3306
-socket		= /var/lib/mysql/mysql.sock
-# Here follows entries for some specific programs
+Insérez maintenant les mêmes données dans la table **transac_part** :
-# The MySQL server
+<code>
-[mysqld]
+MariaDB [(none)]> INSERT INTO transac_part SELECT * FROM transac;
-port		= 3306
+Query OK, 191939 rows affected (3.63 sec)
-socket		= /var/lib/mysql/mysql.sock
+Records: 191939  Duplicates: 0  Warnings: 0
-skip-external-locking
-key_buffer_size = 384M
-max_allowed_packet = 1M
-table_open_cache = 512
-sort_buffer_size = 2M
-read_buffer_size = 2M
-read_rnd_buffer_size = 8M
-myisam_sort_buffer_size = 64M
-thread_cache_size = 8
-query_cache_size = 32M
-# Try number of CPU's*2 for thread_concurrency
-thread_concurrency = 8
-# Point the following paths to a dedicated disk
+MariaDB [(none)]> SELECT COUNT(*) FROM transac_part;
-#tmpdir		= /tmp/
++----------+
+| COUNT(*) |
++----------+
+|   191939 |
++----------+
+row in set (0.14 sec)
-# Don't listen on a TCP/IP port at all. This can be a security enhancement,
+MariaDB [(none)]>
-# if all processes that need to connect to mysqld run on the same host.
+</code>
-# All interaction with mysqld must be made via Unix sockets or named pipes.
-# Note that using this option without enabling named pipes on Windows
-# (via the "enable-named-pipe" option) will render mysqld useless!
-#
-#skip-networking
-# Replication Master Server (default)
+Pour voir l'impact du partitionnement sur la performance, saisissez les requêtes suivantes :
-# binary logging is required for replication
-log-bin=mysql-bin
-# required unique id between 1 and 2^32 - 1
+<code>
-# defaults to 1 if master-host is not set
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac WHERE jour BETWEEN '1998-01-01' AND '1998-12-31';
-# but will not function as a master if omitted
++--------------+--------------+
-server-id	= 1
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|     39970960 |    2276.8989 |
++--------------+--------------+
+ row in set (0.17 sec)
-# Replication Slave (comment out master section to use this)
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part WHERE jour BETWEEN '1998-01-01' AND '1998-12-31';
-#
++--------------+--------------+
-# To configure this host as a replication slave, you can choose between
+| SUM(montant) | AVG(montant) |
-# two methods :
++--------------+--------------+
-#
+|     39970960 |    2276.8989 |
-# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
++--------------+--------------+
-#    the syntax is:
+row in set (0.03 sec)
-#
+</code>
-#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
-#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
-#
-#    where you replace <host>, <user>, <password> by quoted strings and
-#    <port> by the master's port number (3306 by default).
-#
-#    Example:
-#
-#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
-#    MASTER_USER='joe', MASTER_PASSWORD='secret';
-#
-# OR
-#
-# 2) Set the variables below. However, in case you choose this method, then
-#    start replication for the first time (even unsuccessfully, for example
-#    if you mistyped the password in master-password and the slave fails to
-#    connect), the slave will create a master.info file, and any later
-#    change in this file to the variables' values below will be ignored and
-#    overridden by the content of the master.info file, unless you shutdown
-#    the slave server, delete master.info and restart the slaver server.
-#    For that reason, you may want to leave the lines below untouched
-#    (commented) and instead use CHANGE MASTER TO (see above)
-#
-# required unique id between 2 and 2^32 - 1
-# (and different from the master)
-# defaults to 2 if master-host is set
-# but will not function as a slave if omitted
-#server-id       = 2
-#
-# The replication master for this slave - required
-#master-host     =   <hostname>
-#
-# The username the slave will use for authentication when connecting
-# to the master - required
-#master-user     =   <username>
-#
-# The password the slave will authenticate with when connecting to
-# the master - required
-#master-password =   <password>
-#
-# The port the master is listening on.
-# optional - defaults to 3306
-#master-port     =  <port>
-#
-# binary logging - not required for slaves, but recommended
-#log-bin=mysql-bin
-#
-# binary logging format - mixed recommended
-#binlog_format=mixed
-# Uncomment the following if you are using InnoDB tables
+Notez que la requête sur la table partitionnée retourne un résultat **5,67** fois plus vite que sur la table d'origine. Ceci est du au fait que le serveur exclue les partitions qui ne contiennent pas les données recherchées. Ceci peut être constaté en utilisant la clause EXPLAIN :
-#innodb_data_home_dir = /var/lib/mysql
-#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
-#innodb_log_group_home_dir = /var/lib/mysql
-# You can set .._buffer_pool_size up to 50 - 80 %
-# of RAM but beware of setting memory usage too high
-#innodb_buffer_pool_size = 384M
-#innodb_additional_mem_pool_size = 20M
-# Set .._log_file_size to 25 % of buffer pool size
-#innodb_log_file_size = 100M
-#innodb_log_buffer_size = 8M
-#innodb_flush_log_at_trx_commit = 1
-#innodb_lock_wait_timeout = 50
-[mysqldump]
+<code>
-quick
+MariaDB [(none)]> EXPLAIN PARTITIONS SELECT SUM(montant), AVG(montant) FROM transac_part WHERE jour BETWEEN '1998-01-01' AND '1998-12-31'\G
-max_allowed_packet = 16M
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: transac_part
+   partitions: p3
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 18035
+        Extra: Using where
+row in set (0.01 sec)
-[mysql]
+MariaDB [(none)]>
-no-auto-rehash
+</code>
-# Remove the next comment character if you are not familiar with SQL
-#safe-updates
-[myisamchk]
+<WRAP center round important 60%>
-key_buffer_size = 256M
+**Important** :
-sort_buffer_size = 256M
+Notez que la valeur de la colonne **partitions** est **p3**.
-read_buffer = 2M
+</WRAP>
-write_buffer = 2M
-[mysqlhotcopy]
+Evidemment, plus qu'il y a de partitions à consulter, moins important est le gains en performance :
-interactive-timeout
+<code>
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac WHERE jour BETWEEN '1996-01-01' AND '2000-12-31';
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|    198031481 |    2268.9216 |
++--------------+--------------+
+row in set (0.18 sec)
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part WHERE jour BETWEEN '1996-01-01' AND '2000-12-31';
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|    198031481 |    2268.9216 |
++--------------+--------------+
+row in set (0.16 sec)
+MariaDB [(none)]>
 </code>
-Pour connaître toutes les options valides pour le serveur, il convient de saisir la commande suivante :
+Le système de partitionnement peut aussi s'avérer un handicap au niveau de la performance si les requêtes portent sur l'ensemble des données :
 <code>
-[root@centos7 ~]# /usr/libexec/mysqld --help --verbose | more
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac;
-13:17:19 [Note] Plugin 'FEEDBACK' is disabled.
++--------------+--------------+
-/usr/libexec/mysqld  Ver 5.5.56-MariaDB for Linux on x86_64 (MariaDB Server)
+| SUM(montant) | AVG(montant) |
-Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
++--------------+--------------+
+|    959746672 |    5000.2692 |
++--------------+--------------+
+row in set (0.16 sec)
-Starts the MariaDB database server.
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part;
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|    959746672 |    5000.2692 |
++--------------+--------------+
+row in set (0.20 sec)
-Usage: /usr/libexec/mysqld [OPTIONS]
+MariaDB [(none)]>
+</code>
-Default options are read from the following files in the given order:
+Dernièrement, l'effacement de données en grande quantité est plus rapide quand on drop une partition :
-/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
-The following groups are read: mysqld server mysqld-5.5 mariadb mariadb-5.5 clie
-nt-server
-The following options may be given as the first argument:
---print-defaults        Print the program argument list and exit.
---no-defaults           Don't read default options from any option file.
---defaults-file=#       Only read default options from the given file #.
---defaults-extra-file=# Read this file after the global files are read.
-  --allow-suspicious-udfs
+<code>
-                      Allows use of UDFs consisting of only one symbol xxx()
+MariaDB [(none)]> DELETE FROM transac_part WHERE jour BETWEEN '1996-01-01' AND '1996-12-31';
-                      without corresponding xxx_init() or xxx_deinit(). That
+Query OK, 17680 rows affected (0.18 sec)
-                      also means that one can load any function from any
-                      library, for example exit() from libc.so
+MariaDB [(none)]> ALTER TABLE transac_part DROP PARTITION p1;
-  -a, --ansi          Use ANSI SQL syntax instead of MySQL syntax. This mode
+Query OK, 0 rows affected (0.10 sec)
---More--
+Records: 0  Duplicates: 0  Warnings: 0
+MariaDB [(none)]>
 </code>
+===LAB #3 - Partitionnement par Listes===
-Pour connaître les options valides pour le client mysql, l'option de la ligne de commande **--verbose** n'est pas requise :
+Imaginons maintenant que nous avons découvert que les requêtes des utilisateurs portent souvent sur continent géographique. Dans ce cas, il serait utile de faire un partitionnement par liste. Le champs crée par l'instruction suivante :
+<file>
+codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
+</file>
+associe une valeur entière à chaque valeur du champ de type ENUM. Nous allons utiliser ce fait pour créer la table partitionnée.
+Commencez par nettoyer ce que vous avez déjà fait :
 <code>
-[root@centos7 ~]# mysql --help | more
+MariaDB [(none)]> TRUNCATE TABLE transac;
-mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
+Query OK, 0 rows affected (0.17 sec)
-Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
-Usage: mysql [OPTIONS] [database]
+MariaDB [(none)]> DROP TABLE transac_part;
+Query OK, 0 rows affected (0.09 sec)
-Default options are read from the following files in the given order:
+MariaDB [(none)]>
-/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
+</code>
-The following groups are read: mysql client client-server client-mariadb
-The following options may be given as the first argument:
---print-defaults        Print the program argument list and exit.
---no-defaults           Don't read default options from any option file.
---defaults-file=#       Only read default options from the given file #.
---defaults-extra-file=# Read this file after the global files are read.
-  -?, --help          Display this help and exit.
+Créez maintenant la table transac_part :
-  -I, --help          Synonym for -?
-  --abort-source-on-error
+<code>
-                      Abort 'source filename' operations in case of errors
+MariaDB [(none)]> CREATE TABLE transac_part
-  --auto-rehash       Enable automatic rehashing. One doesn't need to use
+    -> (
-                      'rehash' to get table and field completion, but startup
+    ->    id INT UNSIGNED NOT NULL,
-                      and reconnecting may take a longer time. Disable with
+    ->    montant INT UNSIGNED NOT NULL,
-                      --disable-auto-rehash.
+    ->    jour DATE NOT NULL,
-                      (Defaults to on; use --skip-auto-rehash to disable.)
+    ->    codePays TINYINT UNSIGNED NOT NULL
---More--
+    -> ) PARTITION BY LIST(codePays)
+    -> (
+    ->    PARTITION pEurope VALUES IN (1, 2, 3),
+    ->    PARTITION pAmeriqueNord VALUES IN (4, 5),
+    ->    PARTITION pAsie VALUES IN (6)
+    -> );
+Query OK, 0 rows affected (0.06 sec)
 </code>
-==Comment Connaître avec Exactitude la Configuration Actuelle==
+Appelez la procédure remplir_transaction et copiez la valeurs dans transac_part :
-La réponse à la deuxième question est obtenue en utilisant la commande **mysqladmin** :
+<code>
+MariaDB [(none)]> CALL remplir_transaction(10000000);
+^CCtrl-C -- sending "KILL QUERY 2" to server ...
+Ctrl-C -- query aborted.
+MariaDB [(none)]> INSERT INTO transac_part SELECT * FROM transac;
+Query OK, 52193 rows affected (0.57 sec)
+Records: 52193  Duplicates: 0  Warnings: 0
+MariaDB [(none)]>
+</code>
+Comparez le gains de performance en utilisant la table partitionnée :
 <code>
-[root@centos7 ~]# mysqladmin -uroot -p variables | more
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac WHERE codePays IN ('FR','BE','UK');
-Enter password:
++--------------+--------------+
-+---------------------------------------------------+---------------------------------------------------------------------------------
+| SUM(montant) | AVG(montant) |
---------------------------------------------------------------------------------------------------------------------------------------
++--------------+--------------+
---------------------------------------------------------------------------------------------------------------------------------------
+|    130838834 |    5018.9433 |
---------------------------------------------------------------------------------------------------------------------------------------
++--------------+--------------+
-------------------------------------------------------------------------------------------------------------------+
+row in set (0.07 sec)
-| Variable_name                                     | Value
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part WHERE codePays IN (1, 2, 3);
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
-                                                                                                                  |
++--------------+--------------+
-+---------------------------------------------------+---------------------------------------------------------------------------------
+|    130838834 |    5018.9433 |
---------------------------------------------------------------------------------------------------------------------------------------
++--------------+--------------+
---------------------------------------------------------------------------------------------------------------------------------------
+row in set (0.04 sec)
---------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------+
+MariaDB [(none)]>
-| aria_block_size                                   | 8192
-                                                                                                                  |
-| aria_checkpoint_interval                          | 30
-                                                                                                                  |
-| aria_checkpoint_log_activity                      | 1048576
-                                                                                                                  |
-| aria_force_start_after_recovery_failures          | 0
-                                                                                                                  |
---More--
 </code>
-===Passer des Paramètres à l'Exécutable mysqld===
+Notez que la requête sur la table partitionnée retourne un résultat **1,75** fois plus vite que sur la table d'origine.
-Le serveur mysqld peut être paramétrer en passant des options à l'exécutable lors de son lancement. Dans ce cas, les options sont les mêmes que celles dans le fichier my.cnf, précédées par deux tirés.
+Comparez le gains de performance en utilisant uniquement la partition numéro 6 :
-===Paramétrer le Serveur Dynamiquement===
+<code>
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac WHERE codePays = 'JP';
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|     43098698 |    4973.3093 |
++--------------+--------------+
+row in set (0.05 sec)
-Pour paramétrer le serveur à chaud, il convient d'utiliser la commande **SET**. Pour utiliser la commande **SET**, il faut posséder le privilège **SUPER**.
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part WHERE codePays = 6;
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|     43098698 |    4973.3093 |
++--------------+--------------+
+row in set (0.01 sec)
+MariaDB [(none)]>
+</code>
-La portée des options peut être SESSION, c'est-à-dire pour la session en cours, ou GLOBAL pour toutes le sessions.
+Notez que la requête sur la table partitionnée retourne un résultat **5** fois plus vite que sur la table d'origine. Ceci est du au fait qu'il y a moins d'enregistrements à parcourir.
+===LAB #4 - Partitionnement par Hash===
-==SESSION==
+Dans ce cas la partition à laquelle appartient un enregistrement est déterminée à partir de la valeur de retour d'une fonction définie par l'utilisateur.
-Prenant l'example de l'option **tmp_table_size** :
+Re-créez la table transac_part :
 <code>
-[root@centos7 ~]# mysql -u root -p
+MariaDB [(none)]> DROP TABLE transac_part;
-Enter password:
+Query OK, 0 rows affected (0.03 sec)
-Welcome to the MariaDB monitor.  Commands end with ; or \g.
-Your MariaDB connection id is 8
-Server version: 5.5.56-MariaDB MariaDB Server
-Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
+MariaDB [(none)]>
+MariaDB [(none)]> CREATE TABLE transac_part
+    -> (
+    ->    id INT UNSIGNED NOT NULL,
+    ->    montant INT UNSIGNED NOT NULL,
+    ->    jour DATE NOT NULL,
+    ->    codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
+    -> ) PARTITION BY HASH(YEAR(jour)) PARTITIONS 11;
+Query OK, 0 rows affected (0.07 sec)
-Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
+MariaDB [(none)]>
+MariaDB [(none)]>
+MariaDB [(none)]> INSERT INTO transac_part SELECT * FROM transac;
+Query OK, 52193 rows affected (0.85 sec)
+Records: 52193  Duplicates: 0  Warnings: 0
-MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+MariaDB [(none)]>
-+----------------+----------+
+</code>
-| Variable_name  | Value    |
-+----------------+----------+
-| tmp_table_size | 16777216 |
-+----------------+----------+
-row in set (0.00 sec)
-MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size';
+<WRAP center round important 60%>
-+----------------+----------+
+**Important** :
-| Variable_name  | Value    |
+Notez qu'ici on indique le champs de référence **jour** et le nombre de partitions désirées.
-+----------------+----------+
+</WRAP>
-| tmp_table_size | 16777216 |
-+----------------+----------+
-row in set (0.01 sec)
-MariaDB [(none)]> SET SESSION tmp_table_size=8388608;
+Testez maintenant le gains de performance en utilisant les partitions :
-Query OK, 0 rows affected (0.00 sec)
-MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+<code>
-+----------------+----------+
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac WHERE jour BETWEEN '1998-01-01' AND '1998-12-31';
-| Variable_name  | Value    |
++--------------+--------------+
-+----------------+----------+
+| SUM(montant) | AVG(montant) |
-| tmp_table_size | 16777216 |
++--------------+--------------+
-+----------------+----------+
+|     10867590 |    2271.6534 |
-row in set (0.00 sec)
++--------------+--------------+
+row in set (0.09 sec)
-MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size';
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part WHERE jour BETWEEN '1998-01-01' AND '1998-12-31';
-+----------------+---------+
++--------------+--------------+
-| Variable_name  | Value   |
+| SUM(montant) | AVG(montant) |
-+----------------+---------+
++--------------+--------------+
-| tmp_table_size | 8388608 |
+|     10867590 |    2271.6534 |
-+----------------+---------+
++--------------+--------------+
-row in set (0.00 sec)
+row in set (0.08 sec)
 MariaDB [(none)]>
@@ Ligne 1078: / Ligne 2290: @@
 <WRAP center round important 60%>
-**Important** - Notez que la modification est immédiate.
+**Important** :
+Notez qu'ici le gains de performance est minime.
 </WRAP>
-==GLOBAL==
+Le gains de performance est minime parce que toutes les partitions ont été scannées :
-En utilisant la même option :
 <code>
-MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+MariaDB [(none)]> EXPLAIN PARTITIONS SELECT SUM(montant), AVG(montant) FROM transac_part WHERE jour BETWEEN '1998-01-01' AND '1998-12-31'\G
-+----------------+----------+
+*************************** 1. row ***************************
-| Variable_name  | Value    |
+           id: 1
-+----------------+----------+
+  select_type: SIMPLE
-| tmp_table_size | 16777216 |
+        table: transac_part
-+----------------+----------+
+   partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 52982
+        Extra: Using where
 row in set (0.00 sec)
-MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size';
+MariaDB [(none)]>
-+----------------+---------+
+</code>
-| Variable_name  | Value   |
-+----------------+---------+
-| tmp_table_size | 8388608 |
-+----------------+---------+
-row in set (0.01 sec)
-MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size';
+<WRAP center round important 60%>
-+----------------+---------+
+**Important** :
-| Variable_name  | Value   |
+Le partitionnement par hachage n'est efficace que dans le cas ou le hash est généré à partir d'une colonne de type entier.
-+----------------+---------+
+</WRAP>
-| tmp_table_size | 8388608 |
-+----------------+---------+
-row in set (0.01 sec)
-MariaDB [(none)]>
+Pour prouver ce point, recréez la table transac_part avec des partitions basées sur la colonne **codePays** :
+<code>
+MariaDB [(none)]> DROP TABLE transac_part;
+Query OK, 0 rows affected (0.22 sec)
+MariaDB [(none)]>
+MariaDB [(none)]> CREATE TABLE transac_part
+    -> (
+    ->    id INT UNSIGNED NOT NULL,
+    ->    montant INT UNSIGNED NOT NULL,
+    ->    jour DATE NOT NULL,
+    ->    codePays TINYINT UNSIGNED NOT NULL
+    -> ) PARTITION BY HASH(codePays) PARTITIONS 6;
+Query OK, 0 rows affected (0.23 sec)
+MariaDB [(none)]>
+MariaDB [(none)]> INSERT INTO transac_part SELECT * FROM transac;
+Query OK, 52193 rows affected (0.65 sec)
+Records: 52193  Duplicates: 0  Warnings: 0
+MariaDB [(none)]>
+</code>
+Testez de nouveau le gain de performance en utilisant les partitions :
+<code>
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac WHERE codePays = 1;
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|     43320506 |    5023.2498 |
++--------------+--------------+
+row in set (0.04 sec)
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part WHERE codePays = 1;
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|     43320506 |    5023.2498 |
++--------------+--------------+
+row in set (0.02 sec)
+MariaDB [(none)]>
 </code>
 <WRAP center round important 60%>
-**Important** - Notez que pour une option ayant à la fois une portée globale et une portée session, la modification de la valeur globale n'est pas prise en compte dans la session active.
+**Important** :
+Cette fois-ci le gain est plus important.
 </WRAP>
+===Partitionnement par Key===
-Prenons maintenant le cas d'une option qui n'a **qu'une** portée globale :
+Dans ce cas la partition à laquelle appartient un enregistrement est déterminée à partir de la valeur de retour d'une fonction définie par le serveur MariaDB.
+Par exemple :
+<file>
+CREATE TABLE transac_part
+(
+   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
+   montant INT UNSIGNED NOT NULL,
+   jour DATE NOT NULL,
+   codePays ENUM('FR', 'BE', 'UK', 'US', 'CA', 'JP') NOT NULL
+) PARTITION BY KEY() PARTITIONS 5;
+</file>
+<WRAP center round important 60%>
+**Important** :
+Dans ce cas, la clef n'est pas spécifiée. Le hachage est généré à partir de tous les champs de la clef primaire. Dans notre cas c'est le champ **id**. Puisqu'il y a 5 partitions, si le nombre de données est de 100 000, le serveur mettra exactement 20 000 enregistrements dans chaque partition.
+</WRAP>
+===LAB #5 - Sous-partitionnement===
+Avec MariaDB il est possible de créer des sous-partitions. Cependant il existe deux limitations :
+  * La partition de premier niveau doit être de type RANGE ou LIST,
+  * La partition de deuxième niveau doit être de type HASH ou KEY,
+  * Le nombre de partitions total ne peut pas dépasser **8192**.
+Prenons le cas où les requêtes fréquentes portent sur les dates **et** sur la zone géographique.
+Créez la table transac-part :
 <code>
-MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+MariaDB [(none)]> DROP TABLE transac_part;
-+------------------+-------+
+Query OK, 0 rows affected (0.18 sec)
-| Variable_name    | Value |
-+------------------+-------+
+MariaDB [(none)]>
-| query_cache_size | 0     |
+MariaDB [(none)]> CREATE TABLE transac_part
-+------------------+-------+
+    -> (
+    ->    id INT UNSIGNED NOT NULL,
+    ->    montant INT UNSIGNED NOT NULL,
+    ->    jour DATE NOT NULL,
+    ->    codePays INT UNSIGNED NOT NULL
+    -> )
+    -> PARTITION BY RANGE(YEAR(jour))
+    -> SUBPARTITION BY HASH(codePays) SUBPARTITIONS 6
+    -> (
+    ->    PARTITION p1 VALUES LESS THAN(1997),
+    ->    PARTITION p2 VALUES LESS THAN(1998),
+    ->    PARTITION p3 VALUES LESS THAN(1999),
+    ->    PARTITION p4 VALUES LESS THAN(2000),
+    ->    PARTITION p5 VALUES LESS THAN(2001),
+    ->    PARTITION p6 VALUES LESS THAN(2002),
+    ->    PARTITION p7 VALUES LESS THAN(2003),
+    ->    PARTITION p8 VALUES LESS THAN(2004),
+    ->    PARTITION p9 VALUES LESS THAN(2005),
+    ->    PARTITION p10 VALUES LESS THAN(2006),
+    ->    PARTITION p11 VALUES LESS THAN MAXVALUE
+    -> );
+Query OK, 0 rows affected (1.37 sec)
+MariaDB [(none)]>
+MariaDB [(none)]> INSERT INTO transac_part SELECT * FROM transac;
+</code>
+Testez maintenant le gain de performance en utilisant les partitions :
+<code>
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac WHERE codePays=2 AND jour BETWEEN '2000-01-01' AND '2000-12-01';
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|      2928528 |    4073.0570 |
++--------------+--------------+
+row in set (0.05 sec)
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part WHERE codePays=2 AND jour BETWEEN '2000-01-01' AND '2000-12-01';
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|      2928528 |    4073.0570 |
++--------------+--------------+
 row in set (0.01 sec)
-MariaDB [(none)]> SET SESSION query_cache_size=122880;
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac;
-ERROR 1229 (HY000): Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL
++--------------+--------------+
-MariaDB [(none)]> SET GLOBAL query_cache_size=122880;
+| SUM(montant) | AVG(montant) |
-Query OK, 0 rows affected (0.01 sec)
++--------------+--------------+
+|    261035779 |    5001.3561 |
++--------------+--------------+
+row in set (0.07 sec)
+MariaDB [(none)]> SELECT SUM(montant), AVG(montant) FROM transac_part;
++--------------+--------------+
+| SUM(montant) | AVG(montant) |
++--------------+--------------+
+|    261035779 |    5001.3561 |
++--------------+--------------+
+row in set (0.09 sec)
+MariaDB [(none)]>
+</code>
+Notez la dégradation des performance avec les partitions dans le cas d'une requête sans clause WHERE :
+<code>
+MariaDB [(none)]> EXPLAIN PARTITIONS SELECT SUM(montant), AVG(montant) FROM transac_part WHERE codePays=2 AND jour BETWEEN '2000-01-01' AND '2000-12-01'\G
+*************************** 1. row ***************************
+           id: 1
+  select_type: SIMPLE
+        table: transac_part
+   partitions: p5_p5sp2
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 893
+        Extra: Using where
+row in set (0.01 sec)
-MariaDB [(none)]>  SHOW VARIABLES LIKE 'query_cache_size';
+MariaDB [(none)]> EXPLAIN PARTITIONS SELECT SUM(montant), AVG(montant) FROM transac_part\G
-+------------------+--------+
+*************************** 1. row ***************************
-| Variable_name    | Value  |
+           id: 1
-+------------------+--------+
+  select_type: SIMPLE
-| query_cache_size | 122880 |
+        table: transac_part
-+------------------+--------+
+   partitions: p1_p1sp0,p1_p1sp1,p1_p1sp2,p1_p1sp3,p1_p1sp4,p1_p1sp5,p2_p2sp0,p2_p2sp1,p2_p2sp2,p2_p2sp3,p2_p2sp4,p2_p2sp5,p3_p3sp0,p3_p3sp1,p3_p3sp2,p3_p3sp3,p3_p3sp4,p3_p3sp5,p4_p4sp0,p4_p4sp1,p4_p4sp2,p4_p4sp3,p4_p4sp4,p4_p4sp5,p5_p5sp0,p5_p5sp1,p5_p5sp2,p5_p5sp3,p5_p5sp4,p5_p5sp5,p6_p6sp0,p6_p6sp1,p6_p6sp2,p6_p6sp3,p6_p6sp4,p6_p6sp5,p7_p7sp0,p7_p7sp1,p7_p7sp2,p7_p7sp3,p7_p7sp4,p7_p7sp5,p8_p8sp0,p8_p8sp1,p8_p8sp2,p8_p8sp3,p8_p8sp4,p8_p8sp5,p9_p9sp0,p9_p9sp1,p9_p9sp2,p9_p9sp3,p9_p9sp4,p9_p9sp5,p10_p10sp0,p10_p10sp1,p10_p10sp2,p10_p10sp3,p10_p10sp4,p10_p10sp5,p11_p11sp0,p11_p11sp1,p11_p11sp2,p11_p11sp3,p11_p11sp4,p11_p11sp5
+         type: ALL
+possible_keys: NULL
+          key: NULL
+      key_len: NULL
+          ref: NULL
+         rows: 54365
+        Extra:
 row in set (0.01 sec)
 MariaDB [(none)]>
 </code>
+====LAB #6 - Partitionnement Vertical====
-<WRAP center round important 60%>
+Dans ce cas les partitions sont des tables différentes. Prenons l'exemple d'une base de données contenant des photos :
-**Important** - Notez que pour une option ayant uniquement une portée globale, la modification de la valeur globale est prise en compte dans la session active. Notez aussi que les modifications à chaud ne sont pas persistants.
-</WRAP>
-====LAB #3 - Le Mode SQL====
+<code>
+MariaDB [(none)]> CREATE TABLE produit
+    -> (
+    ->    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
+    ->    libelle VARCHAR(50) NOT NULL,
+    ->    prix DECIMAL(10,2) NOT NULL,
+    ->    photo BLOB NOT NULL
+    -> );
+Query OK, 0 rows affected (0.04 sec)
+</code>
-Le mode SQL est utilisé principalement pour empêcher l'insertion de données invalides. La valeur par défaut du mode SQL peut être visualisée avec la commande suivante :
+Séparez maintenant les photos des autres données :
 <code>
-MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'sql_mode';
+MariaDB [(none)]> CREATE TABLE produit2
-+---------------+-------+
+    -> (
-| Variable_name | Value |
+    ->    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-+---------------+-------+
+    ->    libelle VARCHAR(50) NOT NULL,
-| sql_mode      |       |
+    ->    prix DECIMAL(10,2) NOT NULL
-+---------------+-------+
+    -> );
-row in set (0.01 sec)
+Query OK, 0 rows affected (0.04 sec)
+MariaDB [(none)]>
+MariaDB [(none)]> CREATE TABLE photo_produit
+    -> (
+    ->    idProd INT UNIQUE NOT NULL,
+    ->    photo BLOB NOT NULL
+    -> );
+Query OK, 0 rows affected (0.03 sec)
 MariaDB [(none)]>
 </code>
-Les valeurs de l'option sql_mode peuvent être combinées. Pour aider l'administrateur, MariaDB propose un mode sql appelé **TRADITIONAL** :
+Utilisez la procédure suivante pour injecter les données :
 <code>
-MariaDB [(none)]> SET SESSION sql_mode='TRADITIONAL';
+MariaDB [(none)]> DELIMITER //
-Query OK, 0 rows affected (0.00 sec)
+MariaDB [(none)]>
+MariaDB [(none)]> CREATE PROCEDURE remplir_produit(nbProds INT)
+    -> BEGIN
+    ->    DECLARE i INT DEFAULT 1;
+    ->    DECLARE _prix DECIMAL(10,2);
+    ->    DECLARE _photo BLOB DEFAULT REPEAT('A', 20480);
+    ->
+    ->    WHILE i <= nbProds DO
+    ->       SET _prix = CAST((RAND() * 1000) AS DECIMAL(10,2));
+    ->       INSERT INTO produit VALUES (i, CONCAT('Produit_', i), _prix, _photo);
+    ->       INSERT INTO produit2 VALUES (i, CONCAT('Produit_', i), _prix);
+    ->       INSERT INTO photo_produit VALUES (i, _photo);
+    ->       SET i = i + 1;
+    ->    END WHILE;
+    -> END //
+Query OK, 0 rows affected (0.10 sec)
-MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'sql_mode';
+MariaDB [(none)]>
-+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
+MariaDB [(none)]> DELIMITER ;
-| Variable_name | Value                                                                                                                                                |
+MariaDB [(none)]> CALL remplir_produit(10000);
-+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
+^CCtrl-C -- sending "KILL QUERY 3" to server ...
-| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+Ctrl-C -- query aborted.
-+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
+ERROR 1317 (70100): Query execution was interrupted
-row in set (0.00 sec)
+MariaDB [(none)]> SELECT COUNT(*) FROM produit;
++----------+
+| COUNT(*) |
++----------+
+|     1664 |
++----------+
+row in set (0.02 sec)
-MariaDB [(none)]>
+MariaDB [(none)]> SELECT COUNT(*) FROM produit2;
++----------+
+| COUNT(*) |
++----------+
+|     1664 |
++----------+
+row in set (0.01 sec)
+MariaDB [(none)]> SELECT COUNT(*) FROM photo_produit;
++----------+
+| COUNT(*) |
++----------+
+|     1663 |
++----------+
+row in set (0.01 sec)
+MariaDB [(none)]>
 </code>
+Testez maintenant le gain de performance en utilisant les partitions :
+<code>
+MariaDB [(none)]> SELECT id, libelle, prix FROM produit;
+MariaDB [(none)]> SELECT id, libelle, prix FROM produit2;
+</code>
 -----
 <html>
 <center>
-Copyright © 2011-2018 I2TCH LIMITED
+Copyright © 2020 Hugh Norris.
 </center>
 </html>

Piste : • Cockpit • LCF605 - Installation de Machines Virtuelles KVM • SER400 - Administration des serveurs MySQL-MariaDB • LCF606 - Gestion de la Sécurité • Infrastructure - Team IT Training • SER303 - Configuration du serveur Tomcat • Utilisation de l'Infrastructure - Team IT Training • Instructions pour la Suite 2 • SER301 - Présentation des Technologies • BD100 - ElasticSearch ELK

Différences

Recherche

Imprimer/exporter

Menu