Analyse du problème selon lequel la suppression dans la sous-requête ne va pas à l'index dans MySQL

Cet article vous apporte des connaissances pertinentes sur mysql Il présente principalement l'analyse du problème de suppression dans la sous-requête sans indexation. J'espère que cela sera utile.

Apprentissage recommandé : Tutoriel vidéo mysql

Avant le début de l'article, laissez-moi vous poser une question : supprimer dans une sous-requête, sera-t-il indexé ? La première impression de nombreux partenaires est qu’ils savent indexer. Récemment, nous avons eu un problème de production lié à cela. Cet article discutera de ce problème avec tout le monde et joindra un plan d'optimisation.

Récurrence du problème

La version de MySQL est 5.7. Supposons qu'il existe actuellement deux tables account et old_account. La structure de la table est la suivante : Le SQL exécuté par

CREATE TABLE `old_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='老的账户表';
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) DEFAULT NULL COMMENT '账户名',
  `balance` int(11) DEFAULT NULL COMMENT '余额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';

5.7，假设当前有两张表account和old_account,表结构如下：

delete from account where name in (select name from old_account);

执行的SQL如下：

explain select * from account where name in (select name from old_account);
show WARNINGS;

我们explain执行计划走一波，

从explain结果可以发现：先全表扫描 account，然后逐行执行子查询判断条件是否满足；显然，这个执行计划和我们预期不符合，因为并没有走索引。

但是如果把delete换成select，就会走索引。如下：

为什么select in子查询会走索引，delete in子查询却不会走索引呢？

原因分析

select in子查询语句跟delete in est le suivant :

explain delete a from account as a where a.name in (select name from old_account)

Expliquons le plan d'exécution,

D'après les résultats de explain, nous pouvons constater que : d'abord analyse complète de la table

compte, puis exécutez la sous-requête ligne par ligne pour déterminer si les conditions sont remplies ; évidemment , ce plan d'exécution ne correspond pas à nos attentes car

ne suit pas l'index

.

Mais si vous remplacez delete par select, il sera indexé. Comme suit :

Pourquoi

sélectionner dans

sous-requête Il peut utiliser l'index, mais la suppression dans la sous-requête ne peut pas utiliser l'index ? Analyse des causesQuelle est la différence entre l'instruction de sous-requête select in et l'instruction de sous-requête delete in ?

Exécutons le SQL suivant pour voir

rrreee

afficher les AVERTISSEMENTS

Vous pouvez afficher le SQL final exécuté après optimisation

Les résultats sont les suivants :

sélectionnez `test2`.`account`.`id` AS `id` ,`test2`.`account`.`name` AS `nom`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS ` create_time`,` test2`.`account`.`update_time` AS `update_time` de `test2`.`account`

semi-jointure (`test2`.`old_account`)

where (`test2`.`account`.` name` = ` test2`.`old_account`.`name`)

On peut constater que lors de l'exécution réelle, MySQL a optimisé le

select dans la sous-requête et a changé la sous-requête en une méthode de jointure, afin que l'index puisse être utilisé. Mais malheureusement, MySQL ne l'a pas optimisé pour la

suppression dans la sous-requête

.

Plan d'optimisation

Alors comment optimiser ce problème ? Grâce à l'analyse ci-dessus, il est évident que la suppression dans la sous-requête peut être modifiée en

join

. Après avoir changé la méthode de jointure, expliquons à nouveau :

Nous pouvons constater que la méthode de jointure est activée par l'indexation, ce qui résout parfaitement ce problème.

En fait, pour mettre à jour ou supprimer des instructions de sous-requête, Le site officiel de MySQL

recommande également l'optimisation de la méthode de jointure

En fait, l'ajout d'alias à la table peut également résoudre ce problème, comme suit :

rrreee

Pourquoi l'index peut-il être indexé en ajoutant un alias ? quoi

? Pourquoi est-il possible d'ajouter un alias, de supprimer une sous-requête et de réutiliser l'index ?

Revenons en arrière et regardons le plan d'exécution d'explication, et nous pouvons constater que dans la colonne Extra, il y a

LooseScan.

🎜🎜🎜🎜Qu'est-ce que LooseScan ? 🎜 En fait, c'est une stratégie, une stratégie d'exécution de 🎜sous-requête semi-join🎜. 🎜🎜Étant donné que la sous-requête est modifiée en jointure, la suppression dans la sous-requête peut être indexée ; 🎜l'ajout d'un alias🎜 utilisera la 🎜stratégie LooseScan🎜, et la stratégie LooseScan est essentiellement une stratégie d'exécution de la 🎜sous-requête semi-jointure🎜. 🎜🎜Par conséquent, l'ajout d'un alias permet d'indexer la suppression dans la sous-requête ! 🎜🎜Apprentissage recommandé : 🎜Tutoriel vidéo mysql🎜🎜

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!