предисловие
Привет всем, я маленький мальчик, который собирает улиток.
Прежде чем начать эту статью, позвольте задать вам вопрос:Удалить в подзапросе, через индекс пройдет?? много партнеровпервое впечатлениеТо есть: пойдет на index. Недавно у нас была производственная проблема, связанная с этим. В этой статье мы обсудим с вами эту проблему и приложим план оптимизации.
- Нет публики:маленький мальчик собирает улиток
Повторение проблемы
Версия MySQL5.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='账户表';
Выполненный SQL выглядит следующим образом:
delete from account where name in (select name from old_account);
Мы объясняем план выполнения, чтобы идти волной,
отexplainВ результате можно обнаружить, что:полное сканирование таблицы account, а затем выполнить подзапрос построчно, чтобы определить, выполняются ли условия; очевидно, что этот план выполнения не соответствует нашим ожиданиям, потому чтоне пошел в индекс.
Но если заменить его наdeleteзаменитьselect, он попадет в index. следующим образом:
Зачемselect inПодзапрос пойдет в индекс, а удаление в подзапросе не пойдет в индекс?
Анализ причин
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выбрать в подзапросеПосле оптимизации подзапрос изменяется на соединение, поэтому можно использовать индекс. Но, к сожалению, дляудалить в подзапросе, MySQL не делает для него эту оптимизацию.
Оптимизация
Так как же оптимизировать эту проблему? Из вышеприведенного анализа очевидно, чтоdelete in子查询изменить наjoinПуть. После того, как мы перейдем к методу соединения, давайте объясним это снова:
Можно обнаружить, что вместо этого можно использовать соединениеиндексируемый, который отлично решает эту проблему.
Фактически, для оператора обновления или удаления подзапросаофициальный сайт MySQLТакже рекомендуется оптимизировать способ соединения
Фактически, добавление псевдонима к таблице также может решить эту проблему следующим образом:
explain delete a from account as a where a.name in (select name from old_account)
Зачем добавлять еще одно имя в индекс?
what? Зачем добавлять псевдоним, снова удалять в подзапросе и снова переходить к индексу?
Вернемся назад и посмотрим на план выполнения объяснения, и мы обнаружим, что в столбце «Дополнительно» естьLooseScan.
Что такое LooseScan?на самом деле это стратегияподзапрос полуобъединениястратегия исполнения.
Поскольку подзапрос изменен на соединение, можно разрешить удаление в подзапросе через индекс; ипсевдоним, ПойдуLooseScanСтратегия, и стратегия LooseScan, по сутиподзапрос полуобъединениястратегия исполнения.
Следовательно, добавление псевдонима позволяет удалить в подзапросе индекс!
Суммировать
- Этот пост в блоге анализируетудалить в подзапросе не идет индексПричина и решение прилагается.
delete inЭто очень распространено в повседневном развитии, и нам нужно обращать на это внимание в нашей повседневной работе. В то же время рекомендуется, когда вы работаете и пишете SQL, стараться выработать хорошую привычку и сначала анализировать SQL с объяснением. - Общая идея этой статьи отсылает к посту в блоге моего коллеги, который был им одобрен. Также рекомендуется, когда вы сталкиваетесь с проблемами, больше думать и писать больше резюме, чтобы не повторять одних и тех же ошибок.
- Я маленький мальчик, который собирает улиток. Кодировать слова непросто. Если вы что-то получили после прочтения статьи, пожалуйста, лайкните ее, а паблик-аккаунт (мальчик, который собирает улиток) просит внимания, спасибо и сердце~