par Guillaume Lelarge.

Distinguer les prédicats d'accès et de filtre


La base de données MySQL utilise trois méthodes différentes pour appliquer les clauses where (prédicats) :

Prédicat d'accès (via la colonne « key_len »)

Les prédicats d'accès expriment les conditions de début et de fin des parcours de nœuds feuilles.

Prédicat de filtre d'index (« Using index condition », depuis MySQL 5.6)

Les prédicats de filtres d'index sont appliqués seulement lors du parcours de nœuds feuilles. Ils ne contribuent pas aux conditions de début et d'arrêt, et ne diminuent pas l'intervalle parcouru.

Prédicat de filtre au niveau table (« Using where » dans la colonne « Extra »)

Les prédicats sur les colonnes qui ne font pas partie de l'index sont évalués au niveau de la table. Pour cela, la base de données doit tout d'abord charger la ligne à partir de la table.

Les plans d'exécution MySQL ne montrent pas le type de prédicat utilisé pour chaque condition, ils ne font que lister les types de prédicats utilisés.

Dans l'exemple suivant, la totalité de la clause where est utilisée comme un prédicat d'accès :

CREATE TABLE demo (
   id1 NUMERIC
 , id2 NUMERIC
 , id3 NUMERIC
 , val NUMERIC);

INSERT INTO demo VALUES (1,1,1,1);
INSERT INTO demo VALUES (2,2,2,2);

CREATE INDEX demo_idx
          ON demo
             (id1, id2, id3);

EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id2=1;

Il n'y a pas de « Using where » ou « Using index condition » indiqué dans la colonne « Extra ». L'index est cependant utilisé (type=ref, key=demo_idx) et vous pouvez donc supposer que l'intégralité de la clause where qualifie un prédicat d'accès.

Notez aussi que la colonne ref indique que les deux colonnes sont utilisées à partir de l'index (les deux sont des constantes de la requête dans cet exemple). Vous pouvez aussi utiliser la valeur de la colonne key_len pour le vérifier. Cela montre que la requête utilise les 12 premiers octets de la définition de l'index. Pour relier cela à des noms de colonnes, il « suffit » de connaître la place occupée par chacun des champs (voir « Data Type Storage Requirements » dans la documentation MySQL). En l'absence de contrainte NOT NULL, MySQL a besoin d'un octet supplémentaire pour chaque colonne. Au final, chaque colonne de type NUMERIC nécessite 6 octets dans cet exemple. Par conséquent, la longueur de clé de 12 confirme que les deux premières colonnes de l'index sont utilisées en tant que prédicat d'accès.

Lorsque le filtre se fait sur la colonne ID3 (à la place de la colonne ID2), MySQL 5.6 (et les versions suivantes) utilise un prédicat de filtre d'index (« Using index condition ») :

EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id3=1;

Dans ce cas, la longueur de clé de six -key_len=6 et seulement une constante (const) dans la colonne ref signifie qu'une seule colonne est utilisée comme prédicat d'accès.

Les précédentes versions de MySQL utilisaient un prédicat de filtre au niveau table pour cette requête, identifié par « Using where » dans la colonne « Extra » :

+------+----------+---------+------+-------------+
| type | key      | key_len | rows | Extra       |
+------+----------+---------+------+-------------+
| ref  | demo_idx | 6       |    1 | Using where | 
+------+----------+---------+------+-------------+

Astuce

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

Livre de Markus

Couverture du livre « SQL : Au cœur des performances »

L'essence de SQL tuning dans 200 pages.

Acheter de Markus
(Livre de poche et PDF)

Achetez chez Amazon
(Seulement en poche)

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license