mysql如何关联更新删除不走索引优化

这篇文章给大家分享的是有关mysql如何关联更新删除不走索引优化的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。关于update in不走索引的：首先select子查询形式是走索引的如下所示：select * from acct_trans_payment where autopayflag=’N’ and objectno in(select serialno from acct_loan where businessstatus=’1′ and accountingorgid=’10080201′)执行计划如下：然后select连接的形式：select * from acct_trans_payment a,acct_loan b where a.objectno=b.serialno and a.autopayflag=’N’ and b.businessstatus=’1′ and b.accountingorgid=’10080201’；执行计划如下：至此可以看出来，select 的in子查询的形式优化器发生了内部转换，转换成了join链接的形式，提高的性能！然而update的却没有自动转换成join链接的形式，如下所示：update acct_trans_payment set autopayflag=’Y’ where autopayflag=’N’ and objectno in(select serialno from acct_loan where businessstatus=’1′ and accountingorgid=’10080201′)下文中有解释执行计划中的select_type中的dependent subquery的检索过程。所以手动改写成join形式：update acct_trans_payment a,acct_loan b set a.autopayflag=’Y’ where a.objectno=b.serialno and a.autopayflag=’N’ and b.businessstatus=’1′ and b.accountingorgid=’10080201’效率提高了。。。。关于delete的优化过程：delete from cfs.acct_trans_payment where serialno in(select serialno from jd.jd_flow where repaymentstype=’05’);首先我们来解释一下图中的dependent subquery是什么意思：手册上的解释是，子查询中的 jd.jd_flow表的select，取决于外面的查询。就这么一句话，其实它表达的意思是：子查询中的查询方式依赖于外部（cfs.acct_payment_log）的查询。换句话说就是jd.jd_flow表的检索方式依赖于cfs.acct_payment_log表的数据，如这里 cfs.acct_payment_log表得到的记录serialno （where serialno in）刚好可以被 jd.jd_flow表作为unique_subquery方式来获得它的相应的记录；换种写法如果此时cfs.acct_payment_log表扫描第一条记录得到的serialno为10001的话，那么后面子查询的语句就类似于这样的语句：select serialno from jd.jd_flow where repaymentstype=’05’ and serialno=’10001’。此时这个语句就会被优化拿来优化，变成了上面的子查询的执行计划，由于jd.jd_flow的主键是serialno，所以会走主键索引。通过这个解释我们可以知道：全表扫描cfs.acct_payment_log表，将cfs.acct_payment_log的每条记录传递给jd.jd_flow表，jd.jd_flow表通过主键索引方式来获得记录判断自身的条件，则找到一个满足此查询的语句。总结：当看到 select_type为dependent subquery的时候，就说明外表走的全表，然后把where value in 中的外表中的每个value值给子查询表，然后遍历结果！当子查询结果比较小的时候可以先把子查询查出来，然后写成如下形式：select * from cfs.acct_trans_payment where serialno in(‘101071256426871193705′,’101184648601257984005′,’101366238550600089605′,’101506423110987776005′,’101699991116782796905′,’101872867624796569705′,’99235027109713920005′)对应的执行计划：那么当子查询结果集比较大的时候，改怎么优化呢？一样借助连接的形式delete

a

from cfs.acct_trans_payment a join jd.jd_flow b where a.serialno =b.serialno and b.repaymentstype=’05’等价于delete from cfs.acct_trans_payment where serialno in(select serialno from jd.jd_flow where repaymentstype=’05’);如下是两个的执行计划，显然性能提升了不少！又如：delete

a ，b

from cfs.acct_trans_payment a join jd.jd_flow b where a.serialno =b.serialno and b.repaymentstype=’05’等价于delete from cfs.acct_trans_payment where serialno in(select serialno from jd.jd_flow where repaymentstype=免费主机域名’05’);同时delete from jd.jd_flow where repaymentstype=’05’ and serialno in 免费主机域名(select serialno fromcfs.acct_trans_payment)也就是说会把两个表的符合条件的都删除。。。。。题外话：关于delete的join形式：delete from left joinDELETE A FROM YSHA A LEFT JOIN YSHB B ON A.code=b.code WHERE b.code is NULL；等同于DELETE FROM YSHA WHERE NOT EXISTS(SELECT 1 FROM YSHB B WHERE YSHA.code=b.code )；注意delete的时候不允许起别名，如下会报错！！！！delete from cfs.acct_trans_payment a where EXISTS ( select serialno from jd.jd_flow b where b.repaymentstype=’05’ and a.serialno=b.serialno );ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘a where EXISTS ( select serialno from jd.jd_flow b where b.repaymentstype=’05’ ‘ at line 1可以需要这样：delete from cfs.acct_trans_payment where EXISTS ( select serialno from jd.jd_flow b where b.repaymentstype=’05’ and cfs.acct_trans_payment.serialno=b.serialno )感谢各位的阅读！关于“mysql如何关联更新删除不走索引优化”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

相关推荐: MySQL中ICP的特性是什么

MySQL中ICP的特性是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。ICP：全称为Index Condition Pushdown，是MySQL 5.6引入的一项优化策略。简单的来说就是将…