前言大家好，我是捡蜗牛的小男孩。（请在最上面打个星号）在文章开始之前，我想问大家一个问题：子查询中的delete会用到索引吗？很多小伙伴的第一感觉就是：会不会用到索引。最近我们有一个与之相关的生产问题。本文将和大家一起探讨这个问题，并附上优化方案。问题复现MySQL版本为5.7，假设有account和old_account两张表，表结构如下：CREATETABLE`old_account`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'primarykeyId',`name`varchar(255)DEFAULTNULLCOMMENT'账户名',`balance`int(11)DEFAULTNULLCOMMENT'余额',`create_time`datetimeNOTNULLCOMMENT'创建时间',`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`id`),KEY`idx_name`(`name`)USINGBTREE)ENGINE=InnoDBAUTO_INCREMENT=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='旧账户表';CREATETABLE`account`(`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'主键Id',`name`varchar(255)DEFAULTNULLCOMMENT'账户名',`balance`int(11)DEFAULTNULLCOMMENT'balance',`create_time`datetimeNOTNULLCOMMENT'创建时间',`update_time`datetimeNOTNULLONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(`id`),KEY`idx_name`(`name`)USINGBTREE)ENGINE=InnoDBAUTO_INCR=1570068DEFAULTCHARSET=utf8ROW_FORMAT=REDUNDANTCOMMENT='账户表';执行的SQL如下：deletefromaccountwherenamein(selectnamefromold_account);我们解释执行计划并经历一波。从explain结果我们可以发现：先扫描全表的account，然后逐行扫描执行子查询判断是否满足条件；很明显，这个执行计划不符合我们的预期，因为没有索引，但是如果把delete换成select，索引就会跟着执行。如下：为什么selectinsubquery使用索引，而deleteinsubquery不使用索引？原因分析子查询语句中的select和子查询语句中的delete有什么区别？下面执行SQL看看explainselect*fromaccountwherenamein(selectnamefromold_account);showWARNINGS;showWARNINGS可以查看优化后最终执行的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`semijoin(`test2`.`old_account`)where(`test2`.`account`.`name`=`test2`.`old_account`.`name`)可以发现，在实际执行过程中，MySQL优化了子查询中的select，将子查询改为join方式，这样索引就可以使用了。但遗憾的是，对于子查询中的delete，MySQL并没有对其进行优化。优化方案如何优化这个问题？通过上面的分析，很明显子查询中的delete可以改成join。改成join方法后，再看explain：可以发现可以用index代替join方法，完美解决了这个问题。其实对于update或者delete子查询语句，mysql官网也推荐join优化。其实给表加一个别名也可以解决这个问题，如下：explaindeleteafromaccountasawherea.namein(selectnamefromold_account)Indexed?什么？为什么添加别名，在子查询中删除再次工作，并再次建立索引？我们回过头来看explain的执行计划，可以发现在Extra一栏有一个LooseScan。什么是松动扫描？其实就是一个策略，一个semijoin子查询的执行策略。因为子查询改成了join，子查询中的delete可以被索引；添加别名会遵循LooseScan策略，而LooseScan策略本质上是一种针对半连接子查询的执行策略。所以，加个别名就可以让子查询中的delete去索引！综上所述，这篇博文分析了子查询中的delete不去索引的原因，并附上解决方案。deletein在日常开发中很常见，在工作中需要注意。同时我建议大家在工作或者写SQL的时候，尽量养成一个好习惯，先用explain来分析SQL。这篇文章的整体思路参考了我同事的博文，得到了他的认同。也建议大家遇到问题多思考，多写总结，避免重蹈覆辙。我是一个捡蜗牛的小男孩。编码并不容易。看完文章如果有收获，可以把我公众号推给身边的程序员。男孩》，可通过以下二维码关注，转载请联系捡蜗牛的小男孩公众号。
