Partager un exemple de code d'optimisation d'index multi-colonnes MySQL

Alors que les données capturées par les robots continuent d'augmenter, la base de données et les instructions de requête ont été continuellement optimisées au cours des deux derniers jours. L'une des structures de table est la suivante :
<.>

CREATE TABLE `newspaper_article` (
  `id` varchar(50) NOT NULL COMMENT '编号',
  `title` varchar(190) NOT NULL COMMENT '标题',
  `author` varchar(255) DEFAULT NULL COMMENT '作者',
  `date` date NULL DEFAULT NULL COMMENT '发表时间',
  `content` longtext COMMENT '正文',
  `status` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_status_date` (`status`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

Selon les besoins métiers, l'index

a été ajouté, ce qui est particulièrement chronophage lors de l'exécution du SQL suivant : idx_status_date

SELECT id, title, status, date FROM article WHERE status > -2 AND date = '2016-01-07';

D'après l'observation, le le nombre de nouvelles données ajoutées chaque jour est d'environ 2 500. C'est ce que je pensais qu'une date spécifique

avait été spécifiée ici, et la quantité réelle de données à analyser devrait être inférieure à 2 500, mais ce n'est pas le cas : '2016-01-07'
Un total réel de 185 589 éléments de données analysés ont été obtenus, bien plus que les 2 500 éléments estimés, et le temps d'exécution réel était de près de 3 secondes :

Pourquoi ça ?

Solution

Après avoir remplacé

par idx_status_date (status, date), affichez le plan d'exécution MySQL : idx_status (status)

Vous pouvez constater qu'après avoir remplacé l'index multicolonne par un index à colonne unique, la quantité totale de données à analyser par le plan d'exécution ne change pas. Combiné avec le fait que les index multi-colonnes suivent le principe du préfixe le plus à gauche, on suppose que l'instruction de requête ci-dessus utilise uniquement l'index du

le plus à gauche de idx_status_date. status

Après avoir feuilleté "High Performance MySQL", j'ai trouvé le paragraphe suivant, qui a confirmé mon idée :

S'il existe une requête de plage pour une certaine colonne dans la requête, alors la right Toutes les colonnes ne peuvent pas être recherchées à l'aide de l'optimisation d'index. Par exemple, il existe une requête

. Cette requête ne peut utiliser que les deux premières colonnes de l'index, car ici WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23' est une condition de plage (mais le serveur peut utiliser les colonnes restantes à d'autres fins). Si le nombre de valeurs de colonne de requête de plage est limité, vous pouvez remplacer la condition de plage en utilisant plusieurs conditions égales. LIKE

Par conséquent, il existe deux solutions ici :

• Vous pouvez remplacer la condition de plage en utilisant plusieurs conditions égales

• Modifiez

  pour indexer idx_status_date (status, date) et créez un nouvel index idx_date_status (date, status) pour obtenir le même effet. idx_status

Plan d'exécution optimisé :

Résultat d'exécution réel :

Résumé

Quand les gens parlent d'index, s'ils ne précisent pas le type, ils parlent probablement de

index, qui utilisent des B-Tree données structure pour stocker les données. Nous utilisons le terme « B-Tree » car MySQL utilise également ce mot-clé dans B-Tree et d'autres déclarations. Cependant, le moteur de stockage sous-jacent peut également utiliser des structures de stockage différentes. InnoDB utilise B+Tree. CREATE TABLESupposons qu'il existe le tableau de données suivant :

CREATE TABLE People (
  last_name  varchar(50)    not null,
  first_name varchar(50)    not null,
  dob        date           not null,
  gender     enum('m', 'f') not null,
  key(last_name, first_name, dob)
);

L'index B-Tree est valide pour les types de requêtes suivants

• Correspondance de valeur complète

  La correspondance de valeurs complètes fait référence à toutes les colonnes de l'index. Par exemple, l'index du tableau ci-dessus peut être utilisé pour rechercher des personnes nommées Cuba Allen et nées le 01/01/1960.
  

• Correspond au préfixe le plus à gauche

  L'index du tableau ci-dessus peut être utilisé pour trouver toutes les personnes portant le nom de famille Allen, c'est-à-dire que seule la première colonne de l'index est utilisée .
  

• Faire correspondre le préfixe de la colonne

  Correspond uniquement au début de la valeur d'une colonne. Par exemple, l'index du tableau ci-dessus peut être utilisé pour rechercher toutes les personnes dont le nom de famille commence par J. Seule la première colonne de l'index est utilisée ici.
  

• Valeur de plage de correspondance

  Par exemple, l'index du tableau ci-dessus peut être utilisé pour rechercher des personnes dont le nom de famille est compris entre Allen et Barrymore. Seule la première colonne de l'index est utilisée ici.
  

• Correspondre exactement à une certaine colonne et plage correspond à une autre colonne

  L'index du tableau ci-dessus peut également être utilisé pour trouver toutes les personnes dont le nom de famille est Allen et dont le prénom commence par la lettre K (comme Kim, Karl, etc.) les gens. Autrement dit, la première colonne last_name correspond complètement et la deuxième colonne first_name correspond à la plage.
  

• Requête qui accède uniquement à l'index

  B-Tree peut généralement prendre en charge "une requête qui accède uniquement à l'index", c'est-à-dire que la requête n'a besoin que d'accéder à l'index sans accéder au lignes de données.
  

Quelques limitations de l'index B-Tree

• L'index ne peut pas être utilisé si la recherche ne démarre pas à partir de la colonne la plus à gauche de l'index. Par exemple, l'index du tableau ci-dessus ne peut pas être utilisé pour rechercher des personnes nommées Bill, ni pour rechercher des personnes ayant un anniversaire spécifique, car aucune des deux colonnes n'est la colonne de données la plus à gauche. De même, il n’existe aucun moyen de trouver des personnes dont le nom de famille se termine par une certaine lettre.

• Impossible de sauter des colonnes dans l'index. Autrement dit, l’index du tableau ci-dessus ne peut pas être utilisé pour rechercher des personnes portant le nom de famille Smith et nées à une date précise. Si vous ne spécifiez pas de nom (first_name), MySQL ne peut utiliser que la première colonne de l'index.

• S'il existe une requête de plage pour une certaine colonne dans la requête, toutes les colonnes à droite de celle-ci ne peuvent pas être recherchées à l'aide de l'optimisation d'index. Par exemple, il existe une requête WHERE last_name = 'Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23'. Cette requête ne peut utiliser que les deux premières colonnes de l'index, car ici LIKE est une condition de plage (mais le serveur peut utiliser les colonnes restantes à d'autres fins). Si le nombre de valeurs de colonne de requête de plage est limité, vous pouvez remplacer la condition de plage en utilisant plusieurs conditions égales.

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!