mysql update join怎样优化update in查询效率
发布时间：2022-01-12 11:09:33 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要为大家展示了mysql update join如何优化update in查询效率，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下mysql update join如何优化update in查询效率这篇文章吧。 数据库版本：5.6.16 update in
|
这篇文章主要为大家展示了“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查询效率”这篇文章的所有内容，感谢各位的阅读！ （编辑：云计算网_宿迁站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐