À propos des index redondants et en double dans MySQL

MySQL permet de créer plusieurs index sur la même colonne. Que ce soit intentionnellement ou non, MySQL doit conserver les index en double séparément, et l'optimiseur doit également les considérer un par un lors de l'optimisation des requêtes, ce qui affectera les performances.

Les index en double font référence au même type d'index créés sur les mêmes colonnes dans le même ordre. Les index en double créés de cette manière doivent être évités et doivent être supprimés immédiatement après leur découverte. Cependant, il est possible de créer différents types d'index sur les mêmes colonnes pour répondre à différents besoins de requêtes.

CREATE TABLE test(
  ID INT NOT NULL PRIMARY KEY,
  A INT NOT NULL,
  B INT NOT NULL,  UNIQUE(ID),  INDEX(ID),
) ENGINE=InnoDB;

Ce SQL crée 3 index en double. Il n'y a généralement aucune raison de faire cela.

Il existe quelques différences entre les index redondants et les index en double. Si vous créez un index (a, b), puis créez un index (a), il s'agit d'un index redondant, car il ne s'agit que du préfixe de l'index. index précédent, donc (a ,b) peut également être utilisé comme (a), mais (b,a) n'est pas un index redondant, pas plus que l'index (b), car b n'est pas la colonne de préfixe la plus à gauche de l'index (a, b). De plus, d'autres types d'index créés sur les mêmes colonnes (tels que les index de hachage et les index de texte intégral) ne seront pas des index redondants pour les index B-Tree, quelles que soient les colonnes d'index couvertes.

Les index redondants se produisent généralement lors de l'ajout de nouveaux index à la table. Par exemple, on peut ajouter un nouvel index (A, B) au lieu d'étendre un index ultérieur (A). Une autre situation consiste à étendre un index à (A, ID), où ID est la clé primaire. Pour InnoDB, la clé primaire est déjà incluse dans l'index secondaire, elle est donc également redondante.

Dans la plupart des cas, les index redondants ne sont pas nécessaires. Vous devriez essayer d'étendre les index existants au lieu de créer de nouveaux index. Cependant, des considérations de performances nécessitent parfois des index redondants, car l'expansion des index existants les rendra trop volumineux. affectant les performances des autres requêtes qui utilisent l’index. Par exemple : s'il existe un index sur une colonne entière et que vous devez maintenant ajouter une très longue colonne varchar pour développer l'index, les performances peuvent alors chuter fortement, surtout s'il existe des requêtes qui utilisent cet index comme index de couverture, ou ceci est une table myisam Et quand il y a de nombreuses requêtes de plage (en raison de la compression du préfixe de myisam)

Par exemple, il y a une table userinfo. Cette table contient 1 000 000 d'enregistrements, soit environ 20 000 enregistrements pour chaque valeur state_id. Il y a un index sur state_id, nous appelons donc le SQL suivant Q1

SELECT count(*) FROM userinfo WHERE state_id=5; --Q1

La vitesse d'exécution de la requête modifiée est d'environ 115 fois par seconde (QPS)

Il existe un autre SQL, nous l'appelons Q2

SELECT state_id,city,address FROM userinfo WHERE state_id=5; --Q2

Le QPS de cette requête est de 10. Le moyen le plus simple d'améliorer les performances de cet index est pour combattre l'index est (state_id,city,address), afin que l'index puisse couvrir la requête :

ALERT TABLE userinfo ADD KEY state_id_2(state_id,city,address);

　(Remarque : state_id a déjà un index. Selon au concept précédent, il s'agit d'un index redondant au lieu d'un index dupliqué)

Comment découvrir l'index redondant et l'index dupliqué ?

1. Vous pouvez utiliser certaines tentatives dans common_schema de Shlomi Noach pour localiser common_schema est une série de stockages et de tentatives couramment utilisés qui peuvent être installés sur le serveur.

2. Vous pouvez utiliser pt_duplicate-key-checker dans Percona Toolkit, qui analyse la structure de la table pour trouver les index redondants et en double.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!