警惕 MySql 更新 sql 的 WHERE 从句中的 IN() 子查询时出现的陷阱

最新推荐文章于 2024-04-13 20:54:15 发布
最新推荐文章于 2024-04-13 20:54:15 发布
阅读量4.3k 收藏 2
点赞数
分类专栏： db_mysql

转自：http://blog.csdn.net/defonds/article/details/46745143

mer_stage 表有 216423 条记录，DDL：

[sql]  view plain  copy
 print ?
  1. CREATE TABLE `mer_stage` (  
  2.   `STAGE_ID` int(11) NOT NULL AUTO_INCREMENT,  
  3.   `MER_ID` int(11) NOT NULL,  
  4.   `MER_CODE` varchar(16) DEFAULT NULL,  
  5.   `MER_NAME` varchar(80) NOT NULL,  
  6.   `INS_CODE` varchar(16) NOT NULL,  
  7.   `INS_NAME` varchar(64) DEFAULT NULL,  
  8.   `AGENT_CODE` varchar(16) DEFAULT NULL,  
  9.   `AGENT_NAME` varchar(64) DEFAULT NULL,  
  10.   `BIG_CATEGORY_NAME` varchar(32) DEFAULT NULL,  
  11.   `SUB_CATEGORY_CODE` char(4) DEFAULT NULL,  
  12.   `SUB_CATEGORY_NAME` varchar(64) DEFAULT NULL,  
  13.   `LICENSE_CODE` varchar(64) DEFAULT NULL,  
  14.   `LICENSE_NAME` varchar(64) DEFAULT NULL,  
  15.   `SHORT_NAME` varchar(25) DEFAULT NULL,  
  16.   `MER_STATUS` tinyint(4) DEFAULT NULL,  
  17.   `PROVINCE_NAME` varchar(16) DEFAULT NULL,  
  18.   `CITY_CODE` char(4) DEFAULT NULL,  
  19.   `CITY_NAME` varchar(12) DEFAULT NULL,  
  20.   `REGISTER_ADDRESS` varchar(128) DEFAULT NULL,  
  21.   `BIZ_ADDRESS` varchar(128) DEFAULT NULL,  
  22.   `TAX_REGISTRATION` varchar(32) DEFAULT NULL,  
  23.   `INSTITUTION` varchar(16) DEFAULT NULL,  
  24.   `LEGAL_NAME` varchar(40) DEFAULT NULL,  
  25.   `LEGAL_CARD` varchar(32) DEFAULT NULL,  
  26.   `LEGAL_PHONE` varchar(16) DEFAULT NULL,  
  27.   `BIZ_SCOPE` varchar(128) DEFAULT NULL,  
  28.   `BIZ_CONTENT` varchar(64) DEFAULT NULL,  
  29.   `BIZ_TIME` varchar(32) DEFAULT NULL,  
  30.   `LICENSE_EXPIRED` varchar(16) DEFAULT NULL,  
  31.   `AVG_SINGLE_TRADE` int(11) DEFAULT NULL,  
  32.   `AVG_MONTH_TRADE` int(11) DEFAULT NULL,  
  33.   `BIZ_PLACE_OWNER` varchar(64) DEFAULT NULL,  
  34.   `REGISTERED_CAPITAL` decimal(11,0) DEFAULT NULL,  
  35.   `PAID_IN_CAPITAL` int(11) DEFAULT NULL,  
  36.   `BIZ_PERIOD` tinyint(4) DEFAULT NULL,  
  37.   `BIZ_AREA` int(11) DEFAULT NULL,  
  38.   `SETTLE_PERIOD` tinyint(4) DEFAULT NULL,  
  39.   `DELAY_TIME` varchar(50) DEFAULT NULL,  
  40.   `DELAY_TYPE` tinyint(4) DEFAULT '0',  
  41.   `BANK_CODE` varchar(40) DEFAULT NULL,  
  42.   `BRANCH_CODE` varchar(25) DEFAULT NULL,  
  43.   `BRANCH_CODE_ONE` varchar(25) DEFAULT NULL,  
  44.   `BRANCH_CODE_TWO` varchar(25) DEFAULT NULL,  
  45.   `BRANCH_NAME` varchar(128) DEFAULT NULL,  
  46.   `ACCOUNT_CODE` varchar(32) DEFAULT NULL,  
  47.   `ACCOUNT_NAME` varchar(80) DEFAULT NULL,  
  48.   `BRANCH_PROVINCE` varchar(32) DEFAULT NULL,  
  49.   `BRANCH_CITY_CODE` varchar(10) DEFAULT NULL,  
  50.   `BRANCH_CITY_NAME` varchar(50) DEFAULT NULL,  
  51.   `SETTLE_CURRENCY` varchar(16) DEFAULT NULL,  
  52.   `SETTLE_PARAM` char(1) DEFAULT NULL,  
  53.   `CUP_TYPE` tinyint(4) NOT NULL DEFAULT '1',  
  54.   `CUP_CD` varchar(6) DEFAULT NULL,  
  55.   `CUP_NM` varchar(80) DEFAULT NULL,  
  56.   `UPI_TYPE` tinyint(4) NOT NULL DEFAULT '1',  
  57.   `UPI_CD` varchar(6) DEFAULT NULL,  
  58.   `UPI_NM` varchar(80) DEFAULT NULL,  
  59.   `VISA_EDC_FEE` double DEFAULT NULL,  
  60.   `VISA_DCC_FEE` double DEFAULT NULL,  
  61.   `MASTERCARD_EDC_FEE` double DEFAULT NULL,  
  62.   `MASTERCARD_DCC_FEE` double DEFAULT NULL,  
  63.   `JCB_EDC_FEE` double DEFAULT NULL,  
  64.   `AE_EDC_FEE` double DEFAULT NULL,  
  65.   `DC_EDC_FEE` double DEFAULT NULL,  
  66.   `CONTACT_NAME` varchar(40) DEFAULT NULL,  
  67.   `CONTACT_FIXED` varchar(32) DEFAULT NULL,  
  68.   `CONTACT_MOBILE` varchar(32) DEFAULT NULL,  
  69.   `CONTACT_FAX` varchar(32) DEFAULT NULL,  
  70.   `CONTACT_EMAIL` varchar(80) DEFAULT NULL,  
  71.   `CONTACT_ADDRESS` varchar(128) DEFAULT NULL,  
  72.   `CONTACT_ZIP` varchar(8) DEFAULT NULL,  
  73.   `biz_license` text COMMENT '营业执照',  
  74.   `tax_register_cert` text COMMENT '税务登记证',  
  75.   `ins_cert` text COMMENT '组织机构代码证',  
  76.   `legal_id_card` text COMMENT '法人身份证',  
  77.   `open_license` text COMMENT '开户许可证',  
  78.   `auth_letter` text COMMENT '授权书',  
  79.   `portal_photo` text COMMENT '门头照片',  
  80.   `cashier_photo` text COMMENT '收银台照片',  
  81.   `scene_photo` text COMMENT '经营场景照片',  
  82.   `mer_agreement` text COMMENT '商户协议',  
  83.   `other_qualification` text COMMENT '其他特殊资质',  
  84.   `EXPECT_OPEN_TIME` datetime DEFAULT NULL,  
  85.   `IN_OUT_FLAG` varchar(32) DEFAULT NULL,  
  86.   `DCC_MODE` int(2) DEFAULT '0',  
  87.   `SPECIAL_FLAG` tinyint(4) DEFAULT NULL,  
  88.   `TRADING_CURRENCY` varchar(3) DEFAULT NULL,  
  89.   `STATUS` int(11) DEFAULT '0',  
  90.   `EDITABLE` tinyint(4) DEFAULT NULL,  
  91.   `MER_SINGLE_LIMIT` decimal(30,5) DEFAULT NULL,  
  92.   `MER_DAY_LIMIT` decimal(30,5) DEFAULT NULL,  
  93.   `MER_NATION` varchar(3) DEFAULT NULL,  
  94.   `ROUTE_SCHEME` varchar(13) DEFAULT NULL,  
  95.   `CREATOR_ID` int(11) DEFAULT NULL,  
  96.   `CREATOR_NAME` varchar(32) DEFAULT NULL,  
  97.   `create_time` datetime NOT NULL COMMENT '记录创建时间',  
  98.   `modify_time` datetime NOT NULL COMMENT '最好修改时间',  
  99.   `TERM_CNT` int(11) DEFAULT NULL,  
  100.   `DATA_SRC` tinyint(4) NOT NULL DEFAULT '1',  
  101.   `CUP_CARD_PLAN` bit(1) DEFAULT NULL,  
  102.   `UPI_CARD_PLAN` bit(1) DEFAULT NULL,  
  103.   `RISK_DESC` varchar(50) DEFAULT NULL,  
  104.   `IS_FLAG` char(1) DEFAULT NULL,  
  105.   `ALP` decimal(22,3) DEFAULT NULL,  
  106.   `WXP` decimal(22,3) DEFAULT NULL,  
  107.   `dfs_edc_fee` decimal(22,3) DEFAULT NULL,  
  108.   `prp_edc_fee` decimal(22,3) DEFAULT NULL,  
  109.   `in_account_id_card` text COMMENT '入账人身份证',  
  110.   `in_account_bank_card` text COMMENT '入账银行卡信息',  
  111.   `ins_credit_card` text COMMENT '机构信用代码证',  
  112.   `ins_store_photo` text COMMENT '仓库照片',  
  113.   `lease_agreement` text COMMENT '租赁协议',  
  114.   `sct` decimal(22,3) DEFAULT NULL COMMENT '扫码支付（支付宝、微信整合）',  
  115.   `card_type` char(1) DEFAULT '1' COMMENT '法人证件类型(1:身份证,2:护照)',  
  116.   PRIMARY KEY (`STAGE_ID`),  
  117.   KEY `mer_stage_s_e_ms` (`STATUS`,`EDITABLE`,`MER_STATUS`) USING BTREE  
  118. ) ENGINE=InnoDB AUTO_INCREMENT=216826 DEFAULT CHARSET=utf8;  

proc 表有 6450 条记录，DDL：
[sql]  view plain  copy
 print ?
  1. CREATE TABLE `proc` (  
  2.   `proc_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流程id',  
  3.   `proc_name` varchar(32) NOT NULL COMMENT '流程名称，如 新增商户全聚德审批流程',  
  4.   `proc_type` tinyint(4) NOT NULL COMMENT '流程类型：1－新增商户，2-变更商户，3-新增终端',  
  5.   `associated_id` int(11) NOT NULL COMMENT '流程关联的商户id或其他',  
  6.   `node_id` tinyint(4) NOT NULL COMMENT '流程进行到哪个节点',  
  7.   `associated_name` varchar(64) DEFAULT NULL COMMENT '流程关联的商户名称',  
  8.   `proc_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '流程状态：1-启动流程，2-进行中，3-已完成',  
  9.   `starter_id` int(11) NOT NULL COMMENT '流程发起者用户id',  
  10.   `starter_name` varchar(32) NOT NULL COMMENT '流程发起者用户名',  
  11.   `node_name` varchar(64) NOT NULL COMMENT '节点名称',  
  12.   `next_id` tinyint(4) NOT NULL COMMENT '下一节点id',  
  13.   `next_name` varchar(64) NOT NULL COMMENT '下一节点名称',  
  14.   `create_time` datetime NOT NULL COMMENT '记录创建时间',  
  15.   `ass_version` datetime NOT NULL COMMENT '关联版本号',  
  16.   `node_remark` varchar(255) DEFAULT NULL COMMENT '备注',  
  17.   `modify_time` datetime DEFAULT NULL COMMENT '上一节点完成时间',  
  18.   `mer_id` int(11) NOT NULL,  
  19.   PRIMARY KEY (`proc_id`),  
  20.   KEY `proc_mer_id_index` (`mer_id`) USING BTREE  
  21. ) ENGINE=InnoDB AUTO_INCREMENT=6451 DEFAULT CHARSET=utf8 COMMENT='流程';  

关于这两张表的一个慢查询日志如下：
# Time: 150703 15:13:33
# User@Host: test[test] @ localhost [127.0.0.1]  Id:     1
# Query_time: 2.101248  Lock_time: 0.046034 Rows_sent: 0  Rows_examined: 865689
SET timestamp=1435907613;
update mer_stage set editable = 1 where stage_id in(
select associated_id from proc where proc_id in(6446 , 6447 , 6450));
日志中可以看出该 sql 的执行时间是 2.101 s。
我们来查看一下该 sql 的执行计划：
我们来查看一下该 sql 的执行计划
注意：select_type 里出现了 DEPENDENT SUBQUERY。
这意味着什么？——子查询取决于外面的查询，MySql 先执行外查询，内查询根据这个查询结果(如执行计划里所述，190102 rows)的每一条记录组成新的查询语句：
[sql]  view plain  copy
 print ?
  1. select associated_id from proc where proc_id in(6446 , 6447 , 6450) and associated_id = '外查询结果.stage_id';  

这就是个坑。我相信，每个写出上面这种 sql 的程序员都不会想到 MySql 会对其这样执行，这是大家不想看到的结果。
怎么办？
Uncorrelated subqueries treated as DEPENDENT by MySQL  提出了同样的问题但是却没有给出解决方案。
MySql 官方 给出的解决方案是：
If you have a slow 'correlated' subquery with IN, you can optimize it with a join to get around the bug described by Ryan and Stephen. After the optimization the execution time is no longer O(M×N).
于是我们的 update 语句改写为：
[sql]  view plain  copy
 print ?
  1. update mer_stage m join proc p on m.stage_id = p.associated_id set m.editable = 1  
  2.         where p.proc_id =6446 or p.proc_id =6447 or p.proc_id =6450;  
它的执行计划是：
它的执行计划是
执行这个 update，用时 0.047s，意料之中。搞定。
有趣的是，我们来做一个尝试，把该 update 改为 select：
[sql]  view plain  copy
 print ?
  1. select * from mer_stage where stage_id in (select associated_id from proc where proc_id in (6446 , 6447 , 6450));  

它的执行时间是 0.053 s，毫秒级。
该 sql 的执行计划是：
update改为select后的执行计划

同样的写法，唯一不同的是一个 update 另一个 select，差别咋就那么大呢？看来优化器并不总是那么靠谱的，它在这里就对 update 那条 sql 的子查询优化的很糟糕。


参考资料

taking_wang
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用子查询——在where子句中的in操作符中&用来填充计算列
认知 行动 坚持
09-22 1212
1.子查询 子查询：即嵌套在其他查询中的查询。 2.利用子查询进行过滤 在使用子查询时，把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。 3.作为计算字段使用子查询 相关子查询：涉及外部查询的子查询，只要列名可能有多义 性，就必须使用这种语法（表名和列名由一个句点分隔）。 where A表.字段= B表.字段 -- 子查询,分步骤先查出最里面的子查询，再一步步往外层查询 -- 第一步，检索包含物品TNT2的所有订单的编号 SELECT order_num FROM orderit
【数据分析师_02_SQL+MySQL】018_MySQL的子查询（WHERE..IN(..)，嵌套查询）
Lyun911的博客
12-20 392
MySQL的子查询1 子查询的用法1.1 不推荐的方法（观察流程）1.2 推荐的方法（子查询）1.3 索引查询的逻辑图2 计算字段作为子查询使用 SELECT语句是SQL的查询，SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。 作为计算字段使用子查询和利用子查询进行过滤。 1 子查询的用法 目标：找出购买物品TNT2的所有订单的客户信息 1.1 不推荐的方法（观察流程） select order_num from orderitems where prod_id = 'TNT2'
解决MySQL中IN子查询会导致无法使用索引问题
12-15
今天看到一篇关于MySQL的IN子查询优化的案例， 一开始感觉有点半信半疑（如果是换做在SQL Server中，这种情况是绝对不可能的，后面会做一个简单的测试。） 随后动手按照他说的做了一个表来测试验证，发现MySQL的IN子查询做的不好，确实会导致无法使用索引的情况（IN子查询无法使用所以，场景是MySQL，截止的版本是5.7.18） MySQL的测试环境 测试表如下 create table test_table2 ( id int auto_increment primary key, pay_id int, pay_time datetime, other_col
WHERE条件IN中的子查询如果最顶级带有group by会无效
努力努力，努力努力的博客
07-19 1523
WHERE条件IN中的子查询如果最顶级带有group by会无效
mysql in查询优化
最新发布
warrah 南极狼
04-13 895
不过我还是想知道，这个效果怎么样，于是在本地的mariadb上运行，效果如下，嗯，貌似有进步。还不如in，固然chatgpt说的专家，也就是砖家。说明采用的是物化视图，因为物化视图是预计算和存储的查询结果，用于提高查询性能，也就是说。将in的内容，创建一个临时表，按照别人的示例，我为什么执行不通过呢？，按照这个说的集中方法，验证一下。版本不支持，而我们的生产环境就是mysql5.7,因此这条路是走不通的。，但那是临时表，也就是in里面的内容，所以没有影响。下面的in里面的值不是固定的，
WHERE语句中BETWEEN与IN的使用教程-MySQL
php菜鸟技术天地
09-19 1万+
MySQL BETWEEN 用法 MySQL BETWEEN 语法 BETWEEN 运算符用于 WHERE 表达式中，选取介于两个值之间的数据范围。BETWEEN 同 AND 一起搭配使用，语法如下： WHERE column BETWEEN value1 AND value2 WHERE column NOT BETWEEN value1 AND value2 通常 val
警惕 MySql 更新 sql 的 WHERE 从句中的 IN() 子查询时出现的性能陷阱
热门推荐
Defonds 的专栏
07-03 3万+
mer_stage 表有 216423 条记录，DDL：CREATE TABLE `mer_stage` ( `STAGE_ID` int(11) NOT NULL AUTO_INCREMENT, `MER_ID` int(11) NOT NULL, `MER_CODE` varchar(16) DEFAULT NULL, `MER_NAME` varchar(80) NOT NU
Mysql子查询IN中使用LIMIT应用示例
12-15
需求是这样的，我有3张表，infor信息表，mconfig物料配置表，maaply物料申请表，要求是读出申请表中哪些人申请哪些物料 于是我先是这样写的： 代码如下: SELECT infor.name,infor.phone,infor.add, mconfig.mname,...
浅谈mysql的子查询联合与in的效率
09-09
总之，理解MySQL中的子查询和`IN`操作符的工作原理，并结合实际场景进行性能测试，是提升数据库效率的关键。开发者应时刻关注查询性能，适时调整查询结构，以适应高并发和大数据量的业务需求。同时，保持对数据库...
浅谈pymysql查询语句中带有in时传递参数的问题
12-17
直接给出例子说明： cs = conn.cursor() ...在工作中有时需要查询上万行指定的数据，就会用到SQL语句中 select * from table1 where table1.name in (‘ ‘ , ‘ ‘ ) 的条件查询，所以自己写了个小小
SQL语句，in子查询
weixin_73480865的博客
05-06 496
写出关于in 子查询的SQL语句
MySql学习(三) —— 子查询(where、from、exists) 及 连接查询(left join、right join、inner join、union join)
遇见即为缘
09-07 1004
同样的，使用goods表来练习子查询，表结构如下：    所有数据(cat_id与category.cat_id关联)：    类别表：    mingoods(连接查询时作测试)       回到顶部 一、子查询 1、where型子查询：把内层查询的结果作为外层查询的比较条件 1.1 查询id最大的一件商品(使用排序+分页实现) 　　：mysql> SELE...
pgsql 常用查询汇总(查询数据表字段)
keepstudy_yangzh的博客
04-14 2万+
查询数据库表中字段及类型 SELECT table_catalog,table_schema,table_name,column_name,ordinal_position,is_nullable,data_type,character_maximum_length,numeric_precision,udt_name FROM information_schema.columns As ...
ORMLite 常用方法说明
ruyi366的专栏
01-29 1538
ORMLite查询封装了许多函数，用于构建查询语句，以下总结了一些常用的方法。
SQL 语句 where id in() 出现的问题
qq_1334124306
11-28 7481
自己编写sql查询 where id in() 出现的问题 mybatis 中当 DAO 传入的 List 参数时,会自动将参数封装成 Map 参数,而 map中的 key 会自动用 list , value 就是你传入的 List 参数. 修改 第一种: 将 List 参数封装为 Map 然后再传入,在 XML 配置页面写上相应 key 值. 第二种: 将 condition 的值修...
mysql update in_Mysql优化笔记之UPDATE IN
weixin_29117669的博客
01-18 2385
在项目开发中，同事在原本功能的基础上加入了新的业务操作代码后，导致业务操作经常超时。故对其代码进行调试跟踪后定位到，在update数据库记录时写了in子查询，完整sql如下(非原sql)：update wms_stock set qty1 = 0 where id in (select stock_id from wms_order_line l where l.a_qty1 = 'a' and ...
SQL: "IN" Function
冯立彬的博客
10-25 7914
 SQL: "IN" FunctionThe IN function helps reduce the need to use multiple OR conditions.译：IN函数有助于减少OR条件的复合使用。The syntax for the IN function is:译：IN函数的语法：SELECT columnsFROM tablesWHERE col
mysql in 加子查询,MySQL – 子查询和WHERE IN
weixin_42510645的博客
03-17 225
我有一个项目属性表,以及一个user_favorites表,结构如下：> id> item_id> user_id>最爱(布尔)当用户“收藏”某个项目时,它会使用项目ID,用户ID向该表添加一行,并将favorite属性设置为1(如果他们“不喜欢”,则将其更改为0).我正在试图弄清楚如何在查看产品列表时显示用户的收藏夹.我目前根据用户搜索输入进行查询并获取一系列产品ID.现...
mysql where in 子查询 语句会走索引吗
04-29
MySQL中的WHERE IN子查询语句在某些情况下会走索引，具体取决于查询条件和索引类型。如果查询条件中的值和索引类型完全匹配，那么WHERE IN子查询就能够利用索引进行优化。 比如说，如果查询语句使用了B+树索引，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值