mysql update join如何优化update in查询效率

2024-04-02 19:04:59 144人浏览八月长安

摘要

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

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

数据库版本：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 |
+----+-------------+-------+------+-------------------------+-----------------+---------+-----------------------+------+--------------------------+