mysql update join優化update in查詢效率

haoge0205發表於2016-11-17
MySql優化
資料庫版本：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                     |
+----+-------------+-------+------+-------------------------+-----------------+---------+-----------------------+------+--------------------------+




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-2128599/，如需轉載，請註明出處，否則將追究法律責任。

相關文章