7.2.5.2 Méthode d'accès par intervalle pour les index multi-colonnes

Les conditions d'intervalle sur un index à plusieurs parties est une extension de la version pour index mono-colonne. Une condition d'intervalle pour un index multi-colonnes restreint les lignes à un ou plusieurs intervalles dans l'index. Les intervalles sont définis comme un jeu d'index, en utilisant l'ordre de l'index existant.

Par exemple, considérez l'index multi-colonnes suivant, défini par 'key1(key_part1, key_part2, key_part3)' et leur ordre :

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

(1, -inf, -inf) <= (key_part1, key_part2, key_part3) < (1, +inf, +inf)

La description suivante montre comment les conditions d'intervalles fonctionnent avec un index multi-colonnes.

• Pour les index HASH , chaque intervalle contiennent des valeurs identiques qui peuvent être utilisées. Cela signifie que l'intervalle peut produire des conditions d'intervalles uniquement pour les conditions suivantes :

  key_part1 cmp const1 AND key_part2 cmp const2 AND ... AND key_partN cmp constN;

  Ici, const1 , const2 , ... sont constantes, cmp est un des opérateurs de comparaison = , <=> ou IS NULL et les conditions couvrent toutes les parties de l'index. C'est à dire qu'il y a N conditions, une pour chaque partie de l'index.Voyez Méthode d'accès par intervalle pour les index mono-colonne pour avoir la définition d'une constante dans ce contexte.

  Par exemple, la condition suivante est une condition d'intervalle pour un index HASH :

  key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

• Pour in index BTREE , un intervalle peut être utilisable pour des conditions AND combinées, où chaque condition compare une partie de la clée avec une valeur constante et un opérateur de comparaison = , <=> , IS NULL , > , < , >= , <= , != , <> , BETWEEN ou 'LIKE 'pattern'' (où ''pattern'' ne commence pas par un joker). Un intervalle peut être utilisé tant qu'il est possible de déterminer une ligne qui vérifie la condition, ou deux intervalles si <> ou != est utilisé. Par exemple :

  key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

  L'intervalle sera :

  ('foo', 10, 10)    < (key_part1, key_part2, key_part3)       < ('foo', +inf, +inf)

  Il est possible que l'intervalle créée contienne plus de lignes que la condition initialle. Par exemple, l'intervalle précédent inclut la valeur ('foo', 11, 0) ,, qui ne satisfait pas les conditions initiales.
• Si les conditions qui génèrent les conditiosn d'intervalle sont combinées avec OR , elles forment une condition qui couvre un jeu de ligne contenu dans l'union des intervalles. Si les conditions sont combinées avec AND , elles forment une condition qui couvre un jeu de lignes contenu dans l'intersection des intervalles. Par exemple, pour cette condition bâtie sur un index à 2 colonnes :

  (key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)

  Les intervalles seront :

  (1, -inf) < (key_part1, key_part2) < (1, 2) (5, -inf) < (key_part1, key_part2)

  Dans cet exemple, l'intervalle de la première ligne utilise une partie de la clé pour l'opérande de gauche, et deux parties de clé pour l'opérande de droite. La colonne key_len dans le résultat de EXPLAIN indique la taille maximale du préfixe de clé utilisé.Dans certains cas, key_len peut indiquer qu'une clé a été utilisée mais ce n'est pas ce que vous attendiez. Par exemple, supposez que key_part1 et key_part2 soient NULL . Alors, la colonne key_len va afficher deux clés de taille différentes pour les conditions suivantes :

  key_part1 >= 1 AND key_part2 < 2

  Mais en fait, les conditions seront converties en :

  key_part1 >= 1 AND key_part2 IS NOT NULL