Index de requête avec la clause WHERE et ORDER BY non utilisés

Question

J'ai créé un tableau comme indiqué ci-dessous

CREATE TABLE IF NOT EXISTS
`table`
  (
     id          VARCHAR(100) NOT NULL,
     seq_id      BIGINT UNSIGNED NOT NULL,
     in_use      BOOLEAN NOT NULL DEFAULT false,
     scheduled   BOOLEAN NOT NULL DEFAULT false,
     dialed      BOOLEAN NOT NULL DEFAULT false,
     priority    INT UNSIGNED NOT NULL DEFAULT 0,
     data_0      VARCHAR(100) NULL,
     data_1      VARCHAR(100) NULL,
     data_2      VARCHAR(40) NULL,
     data_3      VARCHAR(200) NULL,
     data_4      VARCHAR(10) NULL,
     data_5      DECIMAL(65, 20) NULL,
     data_6      DECIMAL(65, 20) NULL,
     PRIMARY KEY (`id`)
  )

Ayez une requête volumineuse qui sélectionne les lignes en fonction de la clause Where, puis trie les résultats.

SELECT id
FROM
`table`
WHERE  ( dialed = false
         AND in_use = false
         AND scheduled = false )
ORDER  BY priority DESC,
          data_6 ASC,
          data_5 DESC,
          data_4 ASC,
          data_3 DESC,
          seq_id
LIMIT  100

J'essaie de trouver le meilleur index pour cette requête en exécutant EXPLAIN. J'ai créé quelques index différents ;

1. (composé, en cours d'utilisation, programmé, prioritaire, data_6, data_5, data_4, data_3, seq_id)
2. (priorité, data_6, data_5, data_4, data_3, seq_id)
3. (programmé, en cours d'utilisation, composé, prioritaire, data_6)
4. (réservé, en cours d'utilisation)

La requête EXPLAIN affiche ce qui suit :

possible_keys: [index1],[index3],[index4]         
key: [index4]
key_len: 2
ref: const, const
rows: 448
filtered: 100.0
Extra: Using index condition; Using where; Using filesort

Je suis curieux de savoir pourquoi il n'utilise pas d'index contenant les colonnes ORDER BY (index1 et index3) et pourquoi il sélectionne un index qui ne contient qu'un sous-ensemble des colonnes de la clause WHERE ? Je pense que l'index 1 couvre toutes les colonnes de la requête et est idéal.

L'index couvrant uniquement la colonne ORDER BY (index2) n'est pas du tout affiché dans possible_keys. Est-ce que je définis les index dans le mauvais ordre ici ?

Est-il possible pour une requête d'utiliser un index pour filtrer, puis d'utiliser un autre index pour trier les résultats ?

Comme vous pouvez le voir, j'exécute 448 lignes de tests. Cette requête peut être exécutée sur des tables plus grandes ; jusqu'à un million. Pour les tables plus volumineuses, d’autres index finiront-ils par être plus performants que l’index 4 ?

Enfin, un index comme l'index 1 avec de nombreuses colonnes dégradera-t-il les performances simplement à cause du nombre de colonnes ?

Answer 1

3 possibilités :

Cette combinaison est utile si elle est sélectivement « assez » : INDEX(dialed, in_use, Schedule). L’ordre de ces 3 n’a pas d’importance.

Si vous utilisez MySQL 8.0, ceci peut être utile (dans l'ordre indiqué) :

INDEX(priority DESC,
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)

Les anciennes versions sont ignorées DESC，使它们不使用 INDEX

Même (encore sur 8.0) :

INDEX(dialed, in_use, schedule,   -- in any order
      priority DESC,    -- the rest, as specified
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)