mysqlupdatejoin如何优化updatein查询效率

这篇文章主要为大家展示了“MySQL update join如何优化update in查询效率”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“mysql update join如何优化update in查询效率”这篇文章吧。

创新互联2013年至今，公司自成立以来始终致力于为企业提供官网建设、移动互联网业务开发（成都小程序开发、手机网站建设、重庆APP软件开发等），并且包含互联网基础服务（域名、主机服务、企业邮箱、网络营销等）应用服务；以先进完善的建站体系及不断开拓创新的精神理念，帮助企业客户实现互联网业务，严格把控项目进度与质量监控加上过硬的技术实力获得客户的一致赞誉。

数据库版本：5.6.16

update in 修改数据，结果执行时间过慢，一直不出结果。

SQL语句及执行计划如下：

UPDATE erp_order_extra SET last_time=123123123123
WHERE order_id IN (SELECT eo.order_id FROM jiuxianweb.erp_order eo WHERE eo.channel_ordersn='18161116045333705180');

+----+--------------------+-----------------+-----------------+-------------------------+---------+---------+------+---------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------+-----------------+-------------------------+---------+---------+------+---------+------------------------------+
| 1 | PRIMARY | erp_order_extra | index | NULL | PRIMARY | 4 | NULL | 7528568 | Using where; Using temporary |
| 2 | DEPENDENT SUBQUERY | eo | unique_subquery | PRIMARY,channel_ordersn | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-----------------+-----------------+-------------------------+---------+---------+------+---------+------------------------------+

两张表的索引结构如下：
mysql> show index from erp_order_extra;
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| erp_order_extra | 0 | PRIMARY | 1 | extra_id | A | 7528568 | NULL | NULL | | BTREE | | |
| erp_order_extra | 1 | order_id | 1 | order_id | A | 7528568 | NULL | NULL | | BTREE | | |
| erp_order_extra | 1 | action_id | 1 | action_id | A | 2 | NULL | NULL | YES | BTREE | | |
| erp_order_extra | 1 | split_type | 1 | split_type | A | 4 | NULL | NULL | | BTREE | | |
+-----------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> show index from erp_order;
+-----------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| erp_order | 0 | PRIMARY | 1 | order_id | A | 8902636 | NULL | NULL | | BTREE | | |
| erp_order | 0 | channel_ordersn | 1 | channel_ordersn | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 0 | channel_ordersn | 2 | channel_id | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | ordersn | 1 | order_sn | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | printBatch | 1 | printBatch | A | 2225659 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | indexes_order_status | 1 | order_status | A | 5324 | NULL | NULL | | BTREE | | |
| erp_order | 1 | indexes_order_status | 2 | depart | A | 14198 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | indexes_order_status | 3 | channel_id | A | 53309 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | orderstatus | 1 | order_status | A | 18 | NULL | NULL | | BTREE | | |
| erp_order | 1 | orderstatus | 2 | shipping_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | addtime | 1 | add_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | consignee | 1 | consignee | A | 2967545 | NULL | NULL | | BTREE | | |
| erp_order | 1 | consignee | 2 | verify_status | A | 2967545 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | consignee | 3 | add_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | channeluser | 1 | channel_id | A | 14131 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | channeluser | 2 | verify_status | A | 34912 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | channeluser | 3 | user_id | A | 4451318 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | mobile | 1 | mobile | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | extension_id | 1 | extension_id | A | 38707 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | self_id | 1 | self_id | A | 864 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | extension_code | 1 | extension_code | A | 3487 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | tel | 1 | tel | A | 2225659 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | order_amount | 1 | order_amount | A | 53955 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | invoice_no | 1 | invoice_no | A | 8902636 | NULL | NULL | | BTREE | | |
| erp_order | 1 | standard_time | 1 | standard_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | shipping_time | 1 | shipping_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | confirm_time | 1 | confirm_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | pay_time | 1 | pay_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | ware_id | 1 | ware_id | A | 110 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | admin_id | 1 | admin_id | A | 1936 | NULL | NULL | | BTREE | | |
| erp_order | 1 | userid | 1 | user_id | A | 2967545 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | shipping_id | 1 | channel_id | A | 18131 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | shipping_id | 2 | shipping_id | A | 74188 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | shipping_id | 3 | user_id | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | shipping_id | 4 | verify_status | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | shipping_id | 5 | add_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | ind_channel_orderid_erp_order | 1 | channel_orderid | A | 4451318 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | index_erp_order_shipping_time | 1 | shipping_time | A | 8902636 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | ind_original_id_erp_order | 1 | original_id | A | 890263 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | idx_ChangeTime | 1 | ChangeTime | A | 45654 | NULL | NULL | | BTREE | | |
| erp_order | 1 | restorestock_status | 1 | restorestock_status | A | 4 | NULL | NULL | | BTREE | | |
| erp_order | 1 | index_temp | 1 | order_status | A | 3697 | NULL | NULL | | BTREE | | |
| erp_order | 1 | index_temp | 2 | is_range | A | 5915 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | index_temp | 3 | islock | A | 7394 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | index_temp | 4 | verify_status | A | 9614 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | index_temp | 5 | invoice_no | A | 8902636 | NULL | NULL | | BTREE | | |
| erp_order | 1 | index_ShopSn | 1 | ShopSn | A | 26 | NULL | NULL | YES | BTREE | | |
| erp_order | 1 | index_SellerSn | 1 | SellerSn | A | 24 | NULL | NULL | YES | BTREE | | |
+-----------+------------+-------------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

通过update join 去优化 update in的查询使用效率：

原SQL:
UPDATE erp_order_extra SET last_time=123123123123
WHERE order_id IN (SELECT eo.order_id FROM jiuxianweb.erp_order eo WHERE eo.channel_ordersn='18161116045333705180');

JOIN SQL：
update erp_order_extra a inner join erp_order b on a.order_id=b.order_id set last_time=123123123123 where b.channel_ordersn='18161116045333705180';

执行计划：
+----+-------------+-------+------+-------------------------+-----------------+---------+-----------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------+-----------------+---------+-----------------------+------+--------------------------+
| 1 | SIMPLE | b | ref | PRIMARY,channel_ordersn | channel_ordersn | 153 | const | 1 | Using where; Using index |
| 1 | SIMPLE | a | ref | order_id | order_id | 4 | jiuxianweb.b.order_id | 1 | NULL |
+----+-------------+-------+------+-------------------------+-----------------+---------+-----------------------+------+--------------------------+

以上是“mysql update join如何优化update in查询效率”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！

当前标题：mysqlupdatejoin如何优化updatein查询效率
分享URL：http://azwzsj.com/article/poeiji.html

mysqlupdatejoin如何优化updatein查询效率

其他资讯