mysqlでサブクエリの削除がインデックスに行かない問題の解析

この記事は、mysql に関する関連知識を提供します。主にインデックスなしのサブクエリでの削除の問題の分析を紹介します。困っている友人は参考にしてください。お役に立てれば幸いです。

推奨学習: mysql ビデオ チュートリアル

記事を始める前に質問させてください: delete inサブクエリ、インデックスは作成されますか??多くのパートナーの第一印象は、インデックスの作成方法を知っているということです。最近、それに関連する生産上の問題が発生しました。この記事では、この問題について全員で話し合い、最適化計画を添付します。

問題の再現

現在 2 つのテーブル

account

と # # があると仮定すると、MySQL のバージョンは 5.7 です。 #old_account のテーブル構造は次のとおりです。

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

実行される SQL は次のとおりです。

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

実行計画について説明します。

explain

の結果から、次のことがわかります。まず

テーブル全体をスキャン アカウント、次にサブクエリを行ごとに実行して、次のことを確認します。条件は満たされています。明らかに、この実行プランと がインデックス に移動しなかったため、一致しないことが予想されました。 ただし、delete

を

select に置き換えると、インデックスが使用されます。次のとおりです:

select in

サブクエリはインデックスを経由するのに、サブクエリの delete はインデックスを経由しないのはなぜですか?

原因分析

select in

サブクエリ ステートメントと

delete in サブクエリ ステートメントの違いは何ですか? 次の SQL を実行して、

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

show WARNINGS

最適化後に最終的に実行された SQL を表示できます。

結果は次のとおりです：

select `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`)

実際の実行中に、MySQL がサブクエリの

select

を最適化し、インデックスを使用できるようにサブクエリを結合メソッドに変更したことがわかります。しかし、残念ながら、MySQL はサブクエリの

delete に対して最適化していませんでした。 最適化計画

では、この問題を最適化するにはどうすればよいでしょうか?上記の分析により、サブクエリの削除を

join

に変更できることが明らかです。結合メソッドに変更した後、もう一度説明しましょう。

#結合メソッドへの変更は、インデックスを使用できる であることがわかります。完璧なソリューションがこの問題を解決しました。

実際、更新または削除のサブクエリ ステートメントについては、MySQL 公式 Web サイトでも結合メソッドの最適化を推奨しています

実際には、次のように、テーブルにエイリアスを付けることでもこの問題を解決できます。

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

エイリアスを追加するとインデックス作成が可能になるのはなぜですか? ＃＃＃＃＃＃＃＃＃何＃＃＃？エイリアスを追加し、サブクエリで削除し、インデックスを再度使用できるのはなぜですか?

戻って Explain の実行プランを見てみましょう。「Extra」列に

LooseScan

があることがわかります。

LooseScan とは何ですか? 実際、これは semi join subquery

の戦略、実行戦略です。

サブクエリが結合に変更されるため、サブクエリ内の削除にインデックスを付けることができます。

エイリアスを追加すると、

LooseScan 戦略が使用され、LooseScan戦略は本質的には semi join subquery の実行戦略です。

したがって、エイリアスを追加すると、サブクエリの削除にインデックスを付けることができるようになります。

推奨学習: mysql ビデオ チュートリアル

以上がmysqlでサブクエリの削除がインデックスに行かない問題の解析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。