집 >데이터 베이스 >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 scanaccount
, 그런 다음 행별로 하위 쿼리를 실행하여 조건이 충족되는지 확인합니다. 분명히 이 실행 계획은
를 따르지 않기 때문에 우리의 기대와 일치하지 않습니다.
하지만select indelete
를select
로 바꾸면 색인이 생성됩니다. 다음과 같습니다:왜
하위 쿼리 인덱스를 사용할 수 있지만 하위 쿼리에서 삭제하면 인덱스를 사용할 수 없습니까? 원인 분석select in
하위 쿼리 문과 delete in
하위 쿼리 문의 차이점은 무엇인가요?
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에 대해 최적화하지 않았습니다.
최적화 계획
그러면 이 문제를 어떻게 최적화할 수 있을까요? 위의 분석을 통해 하위 쿼리의 삭제가
조인 방법이 인덱싱에 의해 활성화되어 이 문제를 완벽하게 해결한 것을 확인할 수 있습니다.
실제로 하위 쿼리 문을 업데이트하거나 삭제하려면 MySQL 공식 웹사이트
에서도 조인 방법 최적화를 권장합니다실제로 테이블에 별칭을 추가하면 다음과 같이 이 문제를 해결할 수도 있습니다.
rrreee별칭을 추가하여 인덱스를 인덱싱할 수 있는 이유는 무엇입니까? 뭐
? 별칭을 추가하고 하위 쿼리에서 삭제하고 인덱스를 다시 사용할 수 있는 이유는 무엇입니까? 다시 돌아와 explain의 실행 계획을 보면 Extra 열에LooseScan이 있는 것을 볼 수 있습니다.
🎜🎜🎜🎜LooseScan이 무엇인가요? 🎜 사실 🎜semi Join subquery🎜의 전략이자 실행 전략입니다. 🎜🎜하위 쿼리가 조인으로 변경되므로 하위 쿼리의 삭제가 인덱싱될 수 있습니다. 🎜별칭 추가🎜는 🎜LooseScan 전략🎜을 사용하며, LooseScan 전략은 본질적으로 🎜semi 조인 하위 쿼리🎜의 실행 전략입니다. 🎜🎜따라서 별칭을 추가하면 하위 쿼리의 삭제를 색인화할 수 있습니다! 🎜🎜추천 학습: 🎜mysql 비디오 튜토리얼🎜🎜위 내용은 서브쿼리에서 삭제가 mysql의 인덱스로 이동하지 않는 문제 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!