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

这篇文章给大家分享的是有关MySQL如何关联更新删除不走索引优化的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。

成都创新互联公司是一家专注于成都网站建设、成都做网站与策划设计,新丰网站建设哪家好?成都创新互联公司做网站,专注于网站建设十多年,网设计领域的专业建站公司;建站业务涵盖:新丰等地区。新丰做网站价格咨询:13518219792

关于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')

执行计划如下:

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

然后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';

mysql如何关联更新删除不走索引优化执行计划如下:

至此可以看出来,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')

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

下文中有解释执行计划中的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'

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

效率提高了。。。。

关于delete的优化过程:

delete from cfs.acct_trans_payment   where serialno in(

select serialno from jd.jd_flow where repaymentstype='05'

);

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

首先我们来解释一下图中的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')

对应的执行计划:

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

那么当子查询结果集比较大的时候,改怎么优化呢?

一样借助连接的形式

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'

);

如下是两个的执行计划,显然性能提升了不少!

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

又如:

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  from 

cfs.acct_trans_payment)

也就是说会把两个表的符合条件的都删除。。。。。

题外话:关于delete的join形式:

delete from left join

DELETE 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如何关联更新删除不走索引优化
本文链接:http://scyanting.com/article/jppoch.html