這篇文章主要為大家展示了“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 |
+----+-------------+-------+------+-------------------------+-----------------+---------+-----------------------+------+--------------------------+
以上是“mysql update join如何優化update in查詢效率”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
