Optimisation de l'index MySQL couvrant l'index

J'ai récemment rencontré un problème lors du traitement de l'ancien code commercial. Cet article partage principalement avec vous l'index couvrant l'optimisation de l'index MySQL. J'espère qu'il pourra vous aider.

J'ai récemment rencontré cet exemple en traitant d'un ancien code commercial :

La structure de la table est la suivante :

CREATE TABLE `group_user` ( 
  `id` int(11) NOT NULL auto_increment, 
  `uid` int(11) NOT NULL, 
  `username` varchar(16) NOT NULL,
  `gid` int(11) NOT NULL, 
  `create_time` int(10) NOT NULL, 
  `update_time` int(10) NOT NULL, 
  PRIMARY KEY  (`id`), 
  KEY `idx_uid` (`uid`), 
  KEY `idx_gid` (`gid`) 
) ENGINE=InnoDB AUTO_INCREMENT=1530312 DEFAULT CHARSET=utf8

150w de données, une telle déclaration :

SELECT SQL_NO_CACHE uid FROM group_user WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

Il existe de nombreux journaux de requêtes lentes qui prennent 2 secondes. Le résultat d'Explain est :

+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                          | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
|  1 | SIMPLE      | group_user | ref  | idx_gid       | idx_gid  | 4       | const | 6535 | Using where; Using filesort    | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+

D'après le résultat d'Explain, nous pouvons voir que la requête a utilisé l'index, mais pourquoi est-ce toujours si lent ?

Analyse : Tout d'abord, l'instruction ORDER BY utilise le tri des fichiers Filesort, et l'efficacité de la requête est faible. Deuxièmement, le champ de requête n'est pas sur l'index et l'index de couverture n'est pas utilisé, donc il doit être interrogé via l'index vers la table ; enfin, la distribution des données, il y a plus de gid qui sont les mêmes, les hachages uid sont relativement égaux et l'effet de l'utilisation uniquement d'index secondaires est moyen (si vous ne savez pas la classification des index, veuillez cliquer sur : Introduction à la classification des index MySQL).

Solution : étant donné que seul le champ uid est interrogé, l'ajout d'un index conjoint peut éviter le retour de table et le tri de fichiers, utiliser l'index de couverture pour améliorer la vitesse de requête et utiliser l'index pour terminer le tri.

Index couvert : MySQL n'a besoin d'utiliser l'index que pour renvoyer les données requises pour la requête, sans avoir à trouver la clé primaire via l'index secondaire puis à interroger les données.

ALTER TABLE group_user ADD INDEX idx_gid_ctime_uid (gid, create_time, uid);

Expliquez à nouveau :

EXPLAIN SELECT SQL_NO_CACHE uid FROM group_user USE INDEX(idx_gid_ctime_uid) WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+ 
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | group_user | ref  | idx_gid_ctime_uid | idx_gid_ctime_uid | 4       | const | 6375 | Using where; Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+

Les informations supplémentaires contiennent déjà « Utilisation de l'index », indiquant qu'un index de couverture a été utilisé (il y a généralement de nombreux collègues qui SÉLECTIONNENT * , ce qui est un écueil).

Pourquoi l'instruction doit-elle spécifier manuellement quel index utiliser ? Parce que l'optimiseur de requêtes MySQL peut utiliser l'index idx_gid à moins qu'il ne soit supprimé.

Après l'optimisation de l'index, les requêtes en ligne ne dépassent généralement pas 0,001 seconde.

Dernière question : Si ce tableau utilise le moteur MyISAM, quelle sera la situation réelle ?

Recommandations associées :

Méthode d'optimisation de l'index MySQL

Comment utiliser l'optimisation de l'index MySQL

Optimisation MySQL : compréhension approfondie des moteurs de stockage et optimisation des index

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!