Analyse des Problems, dass das Löschen in einer Unterabfrage nicht zum Index in MySQL führt

Dieser Artikel vermittelt Ihnen relevantes Wissen über MySQL. Er stellt hauptsächlich die Analyse des Problems des Löschens in Unterabfragen vor. Ich hoffe, dass er hilfreich sein wird.

Empfohlenes Lernen: MySQL-Video-Tutorial

Bevor der Artikel beginnt, möchte ich Ihnen eine Frage stellen: In Unterabfrage löschen, wird es indiziert? Der erste Eindruck vieler Partner ist, dass sie wissen, wie man indexiert. Kürzlich hatten wir ein damit verbundenes Produktionsproblem. In diesem Artikel wird dieses Problem mit allen besprochen und ein Optimierungsplan beigefügt.

Wiederkehr des Problems

Die MySQL-Version ist 5.7. Gehen Sie davon aus, dass es derzeit zwei Tabellen account gibt Die Tabellenstruktur ist wie folgt: Das von

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='账户表';

ausgeführte SQL lautet wie folgt: 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

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

Lassen Sie uns den Ausführungsplan erklären,

Aus den Ergebnissen von explain können wir Folgendes feststellen: erster vollständiger Tabellenscan account, Führen Sie dann die Unterabfrage Zeile für Zeile aus, um festzustellen, ob die Bedingungen erfüllt sind. Dieser Ausführungsplan entspricht offensichtlich nicht unseren Erwartungen, da

nicht dem Index folgt

.

Aber wenn Sie delete durch select ersetzen, wird es indiziert. Wie folgt:

Warum

in

Unterabfrage auswählen Es kann den Index verwenden, aber das Löschen in der Unterabfrage kann den Index nicht verwenden?

UrsachenanalyseWas ist der Unterschied zwischen der Unterabfrageanweisung select in und der Unterabfrageanweisung delete in?

Lassen Sie uns die folgende SQL ausführen, um zu sehen

rrreee

WARNUNGEN anzeigen Sie können die endgültig ausgeführte SQL nach der Optimierung anzeigen

Die Ergebnisse sind wie folgt:

Wählen Sie „test2“. „account“. „id“. AS `id` ,`test2`.`account`.`name` AS `name`,`test2`.`account`.`balance` AS `balance`,`test2`.`account`.`create_time` AS ` create_time`,` test2`.`account`.`update_time` AS `update_time` from `test2`.`account` semi join (`test2`.`old_account`)

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

Es kann festgestellt werden, dass MySQL während der tatsächlichen Ausführung die

Auswahl in Unterabfrage

optimiert und die Unterabfrage in eine Join-Methode geändert hat, sodass der Index sein kann gebraucht. Aber leider hat MySQL es nicht für die Delete-in-Unterabfrage

optimiert.

Optimierungsplan

Wie kann man dieses Problem optimieren? Durch die obige Analyse ist es offensichtlich, dass das Löschen in der Unterabfrage in

Join

geändert werden kann. Nachdem wir zur Join-Methode gewechselt sind, erklären wir es noch einmal:

Wir können feststellen, dass die Änderung der Join-Methode

durch die Indizierung ermöglicht wird, was dieses Problem perfekt löst.

Tatsächlich empfiehlt die

Offizielle MySQL-Website für Aktualisierungs- oder Löschanweisungen für Unterabfragen auch die Optimierung der Join-Methode

Tatsächlich kann das Hinzufügen von Aliasen zur Tabelle dieses Problem auch wie folgt lösen: rrreee

Warum kann der Index durch Hinzufügen eines Alias ​​indiziert werden? was? Warum ist es möglich, einen Alias ​​hinzuzufügen, ihn in einer Unterabfrage zu löschen und den Index erneut zu verwenden?

Gehen wir noch einmal zurück und schauen uns den Ausführungsplan von „explain“ an. Wir können sehen, dass es in der Spalte „Extra“ „LooseScan“ gibt.

🎜Was ist LooseScan? 🎜 Tatsächlich handelt es sich um eine Strategie, eine Ausführungsstrategie der 🎜Semi-Join-Unterabfrage🎜. 🎜🎜Da die Unterabfrage in „Join“ geändert wird, kann das Löschen in der Unterabfrage indiziert werden. Durch „Hinzufügen eines Alias“ wird die „LooseScan-Strategie“ verwendet, und die LooseScan-Strategie ist im Wesentlichen eine Ausführungsstrategie der „Semi-Join-Unterabfrage“. 🎜🎜Daher ermöglicht das Hinzufügen eines Alias ​​die Indizierung des Löschvorgangs in der Unterabfrage! 🎜🎜Empfohlenes Lernen: 🎜MySQL-Video-Tutorial🎜🎜

Das obige ist der detaillierte Inhalt vonAnalyse des Problems, dass das Löschen in einer Unterabfrage nicht zum Index in MySQL führt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!