서브쿼리에서 삭제가 mysql의 인덱스로 이동하지 않는 문제 분석

이 글은 mysql 관련 지식을 제공합니다. 주로 인덱싱이 없는 하위 쿼리의 삭제 문제에 대한 분석을 소개합니다. 도움이 필요한 친구들에게 도움이 되길 바랍니다.

추천 학습: mysql 비디오 튜토리얼

기사가 시작되기 전에 질문 하나 하겠습니다: 하위 쿼리에서 삭제하면 색인이 생성되나요? 많은 파트너의 첫인상은 색인 생성 방법을 알고 있다는 것입니다. 최근에 이와 관련된 생산 문제가 발생했습니다. 이 기사에서는 이 문제를 모든 사람과 논의하고 최적화 계획을 첨부할 것입니다.

문제 재발

MySQL 버전은 5.7입니다. 현재 account와 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='账户表';

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가 실행한 SQL은 다음과 같습니다.

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

실행 계획을 설명하겠습니다.

explain 결과에서 다음을 확인할 수 있습니다. 먼저 full table scan

account, 그런 다음 행별로 하위 쿼리를 실행하여 조건이 충족되는지 확인합니다. 분명히 이 실행 계획은

인덱스

를 따르지 않기 때문에 우리의 기대와 일치하지 않습니다.

하지만 delete를 select로 바꾸면 색인이 생성됩니다. 다음과 같습니다:

왜

select in

하위 쿼리 인덱스를 사용할 수 있지만 하위 쿼리에서 삭제하면 인덱스를 사용할 수 없습니까? 원인 분석select in 하위 쿼리 문과 delete in 하위 쿼리 문의 차이점은 무엇인가요?

다음 SQL을 실행하여

rrreee

show WARNINGS

최적화 후 최종 실행된 SQL을 볼 수 있습니다

결과는 다음과 같습니다.

`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 in subquery를 최적화하고 서브 쿼리를 조인 방식으로 변경하여 인덱스를 사용된. 그러나 불행하게도 MySQL은

delete in subquery

에 대해 최적화하지 않았습니다.

최적화 계획

그러면 이 문제를 어떻게 최적화할 수 있을까요? 위의 분석을 통해 하위 쿼리의 삭제가

join

으로 변경될 수 있음이 분명합니다. 조인 방법으로 변경한 후 다시 설명하겠습니다.

조인 방법이 인덱싱에 의해 활성화되어 이 문제를 완벽하게 해결한 것을 확인할 수 있습니다.

실제로 하위 쿼리 문을 업데이트하거나 삭제하려면 MySQL 공식 웹사이트

에서도 조인 방법 최적화를 권장합니다

실제로 테이블에 별칭을 추가하면 다음과 같이 이 문제를 해결할 수도 있습니다.

rrreee

별칭을 추가하여 인덱스를 인덱싱할 수 있는 이유는 무엇입니까? 뭐

? 별칭을 추가하고 하위 쿼리에서 삭제하고 인덱스를 다시 사용할 수 있는 이유는 무엇입니까?

다시 돌아와 explain의 실행 계획을 보면 Extra 열에

LooseScan이 있는 것을 볼 수 있습니다.

🎜🎜🎜🎜LooseScan이 무엇인가요? 🎜 사실 🎜semi Join subquery🎜의 전략이자 실행 전략입니다. 🎜🎜하위 쿼리가 조인으로 변경되므로 하위 쿼리의 삭제가 인덱싱될 수 있습니다. 🎜별칭 추가🎜는 🎜LooseScan 전략🎜을 사용하며, LooseScan 전략은 본질적으로 🎜semi 조인 하위 쿼리🎜의 실행 전략입니다. 🎜🎜따라서 별칭을 추가하면 하위 쿼리의 삭제를 색인화할 수 있습니다! 🎜🎜추천 학습: 🎜mysql 비디오 튜토리얼🎜🎜

위 내용은 서브쿼리에서 삭제가 mysql의 인덱스로 이동하지 않는 문제 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!