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