where 1=1是否造成性能影响?

MySQL索引误解

讨论见http://www.iteye.com/topic/710338

帖子中说如果使用了where 1=1,则索引查询会失效。通过实验,该结论是错误的:

mysql> explain select * from pu_user_message where userid=1\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: pu_user_message

         type: ref

possible_keys: userid_index

          key: userid_index

      key_len: 5

          ref: const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

mysql> explain select * from pu_user_message where 1=1 and userid=1\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: pu_user_message

         type: ref

possible_keys: userid_index

          key: userid_index

      key_len: 5

          ref: const

         rows: 1

        Extra: Using where

1 row in set (0.00 sec)

两次执行完全一样,均使用了索引。

17:23:11 [http-nio-8005-exec-99] DEBUG cn.iheal.system.mapper.HemolistMapper.updateByOrigin - ==> Preparing: update HEMOLIST set XTXH = ?, BRID = ?, TXCS = ?, JZSJ = ?, KSSJ = ?, JSSJ = ?, ZBLB = ?, TXZT = ?, BRLY = ?, BRKS = ?, FFFS = ?, SFZT = ?, SBID = ?, TLXH = ?, YSDM = ?, SCTZ = ?, TQTZ = ?, TQTW = ?, TQMB = ?, TQHX = ?, TQSZY = ?, TQSSY = ?, TXFS = ?, SYKYJ = ?, KYFA = ?, GSSJ = ?, GSZJ = ?, GSZL = ?, GTZ = ?, TXSJ = ?, XLL = ?, TXQXHID = ?, TXQFY = ?, ZHY = ?, ZHYZL = ?, TXYLS = ?, NND = ?, JND = ?, GND = ?, TSQG = ?, TXYWD = ?, CLZL = ?, BQPG = ?, TQSZ = ?, SXXZ = ?, TQXL = ?, TQZZ = ?, TQJZ = ?, TXLX = ?, CZBZ = ?, CZGH = ?, XGGH = ?, XGRQ = ?, KSGH = ?, KSCZRQ = ?, KYYP = ?, JBBZ = ?, GSYC = ?, JZFS = ?, ZHLBZ = ?, ZGDM = ?, ZGJM = ?, GSZJ_DFZ = ?, YTSL = ?, TQXT = ?, DDPF = ?, TTPF = ?, NJS = ?, NTS = ?, XY = ?, XDJH = ?, TDW = ?, DWGNTX = ?, QTDM = ?, DYBZ = ?, YCPF = ?, ZHFS = ?, ISWZBR = ?, GCSPF = ?, CHXBSCS = ?, ZKND = ?, QTYP = ?, YJDB = ?, FGFS = ?, CFBZ = ?, CFYS = ?, CFRQ = ?, TZJC = ?, CLBW = ?, YINS = ?, JINYL = ?, BRCH = ?, GSZJSJ = ?, TQCYSJ = ?, TXQXHID2 = ?, QRGH = ?, DGCD = ?, DMD = ?, JMH = ?, CCFF = ?, GLQXHID = ?, CENTERID = ?, XLPG = ?, SLHRZPG = ?, ZJXY = ?, KFPG = ?, KFPG_ZA = ?, KFPG_ZANR = ?, TXQPH = ?, ZYKS = ?, ZYCH = ?, TXYPF = ?, CFXH = ?, TSQK = ?, BLHM = ?, BZNR = ?, DCCLL = ?, DCCLSJ = ?, HXDZZ = ?, YQRYJC = ?, WCYQS = ?, BARCODE = ?, TQDCSJ = ?, TQDCSL = ?, ZJYP = ?, ZJJL = ?, ZJSJ = ?, JYSLS = ?, BRBZ = ?, TSBR = ?, BYL = ?, YSZLXJ = ?, ZYHM = ?, RZDZ = ?, CCFF_A = ?, CCFF_V = ?, DDGZ = ?, FGFS1 = ?, MZHM = ?, HTL = ?, BZXX = ?, TQTTBW = ?, TQTTXZ = ?, TQTTSJ = ?, TQTTPGFS = ?, YJFJ = ?, SUMALL = ?, CCFS = ?, NNL = ?, DMZC = ?, JMZC = ?, GSSJ2 = ?, GSZJ2 = ?, GSZL2 = ?, KYYP2 = ?, BAHM = ?, ZJGSJL = ?, KTN = ?, KCL = ?, DW = ?, MBTSL = ?, CFTSL = ?, XZTSL = ?, DGQK = ?, NLQK = ?, CCFX = ?, CGFX = ?, KYFA2 = ?, SYFS = ?, DDD = ?, RGLR = ?, JCLL = ?, HSL = ?, XFFS = ?, RYFS = ?, SJHS = ?, BASIC_GROUP = ?, DEVGLQJLXH = ?, JBBZDOC = ?, ARCHIVING_STATUS = ?, AZDW = ?, VZDW = ?, LZFX = ?, LZJL = ?, DGYX = ?, CLQX = ? where 1 = 1 and XTXH = ? and BRID = ? and TXCS = ? and JZSJ = ? and KSSJ is null and JSSJ is null and ZBLB = ? and TXZT = ? and BRLY = ? and BRKS is null and FFFS is null and SFZT = ? and SBID = ? and TLXH = ? and YSDM = ? and SCTZ = ? and TQTZ = ? and TQTW = ? and TQMB = ? and TQHX = ? and TQSZY = ? and TQSSY = ? and TXFS = ? and SYKYJ = ? and KYFA = ? and GSSJ = ? and GSZJ = ? and GSZL = ? and GTZ = ? and TXSJ = ? and XLL = ? and TXQXHID = ? and TXQFY is null and ZHY is null and ZHYZL is null and TXYLS = ? and NND = ? and JND = ? and GND = ? and TSQG = ? and TXYWD = ? and CLZL = ? and BQPG = ? and TQSZ = ? and SXXZ is null and TQXL is null and TQZZ = ? and TQJZ = ? and TXLX = ? and CZBZ = ? and CZGH = ? and XGGH = ? and XGRQ = ? and KSGH is null and KSCZRQ is null and KYYP = ? and JBBZ is null and GSYC is null and JZFS = ? and ZHLBZ is null and ZGDM is null and ZGJM is null and GSZJ_DFZ is null and YTSL is null and TQXT is null and DDPF is null and TTPF is null and NJS is null and NTS is null and XY is null and XDJH is null and TDW is null and DWGNTX is null and QTDM is null and DYBZ is null and YCPF is null and ZHFS is null and ISWZBR is null and GCSPF is null and CHXBSCS is null and ZKND is null and QTYP is null and YJDB is null and FGFS is null and CFBZ = ? and CFYS = ? and CFRQ = ? and TZJC is null and CLBW is null and YINS is null and JINYL is null and BRCH = ? and GSZJSJ is null and TQCYSJ is null and TXQXHID2 is null and QRGH is null and DGCD is null and DMD is null and JMH is null and CCFF is null and GLQXHID is null and CENTERID is null and XLPG is null and SLHRZPG is null and ZJXY is null and KFPG is null and KFPG_ZA is null and KFPG_ZANR is null and TXQPH is null and ZYKS is null and ZYCH is null and TXYPF is null and CFXH = ? and TSQK is null and BLHM is null and BZNR is null and DCCLL is null and DCCLSJ is null and HXDZZ is null and YQRYJC is null and WCYQS is null and BARCODE is null and TQDCSJ is null and TQDCSL is null and ZJYP is null and ZJJL is null and ZJSJ is null and JYSLS is null and BRBZ is null and TSBR is null and BYL is null and YSZLXJ is null and ZYHM is null and RZDZ is null and CCFF_A is null and CCFF_V is null and DDGZ is null and FGFS1 is null and MZHM is null and HTL is null and BZXX is null and TQTTBW is null and TQTTXZ is null and TQTTSJ is null and TQTTPGFS is null and YJFJ is null and SUMALL is null and CCFS is null and NNL is null and DMZC is null and JMZC is null and GSSJ2 is null and GSZJ2 is null and GSZL2 is null and KYYP2 is null and BAHM is null and ZJGSJL is null and KTN is null and KCL is null and DW is null and MBTSL is null and CFTSL is null and XZTSL is null and DGQK is null and NLQK is null and CCFX is null and CGFX is null and KYFA2 is null and SYFS is null and DDD is null and RGLR is null and JCLL is null and HSL is null and XFFS = ? and RYFS is null and SJHS is null and BASIC_GROUP is null and DEVGLQJLXH is null and JBBZDOC is null and ARCHIVING_STATUS is null and AZDW is null and VZDW is null and LZFX is null and LZJL is null and DGYX is null and CLQX is null 17:23:11 [http-nio-8005-exec-99] DEBUG cn.iheal.system.mapper.HemolistMapper.updateByOrigin - ==> Parameters: 67379(Long), 182(Long), 188(Integer), 2025-06-30 17:09:24.0(Timestamp), null, null, 3(Short), 1(Short), 1(Short), null, null, 0(Short), 9(Integer), 99(Integer), 50003(String), 55.0(Double), 55.5(Double), 36.8(Double), 101(Short), 20(Short), 48(Short), (Short), 1(Short), 1(Short), 2(Short), 2500.0(Double), 0.0(Double), 2500.0(Double), 54.2(Double), 4.0(Double), 230(Short), 4(Integer), null, null, null, 500(Short), 137.0(Double), 2.0(Double), 1.5(Double), 34.0(Double), 37.0(Double), 0.0(Double), 乏力仍存,建议住院治疗(String), 1(Short), null, null, 55.5(Double), 0.0(Double), 0(Short), 1(Short), 20006(String), 50003(String), 2025-06-30 17:12:20.0(Timestamp), null, null, 74857(Integer), null, null, 2(Short), null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 1(Short), 50003(String), 2025-06-30 17:12:21.0(Timestamp), null, null, null, null, 07(String), null, null, null, null, null, null, null, null, null, 0(Long), null, null, null, null, null, null, null, null, null, null, 278(Long), null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0(Short), null, null, null, null, null, null, null, null, null, null, null, null, 67379(Long), 182(Long), 188(Integer), 2025-06-30 17:09:24.0(Timestamp), 3(Short), 1(Short), 1(Short), 0(Short), 9(Integer), 99(Integer), 50003(String), 55.0(Double), 55.5(Double), 36.8(Double), 101(Short), 20(Short), 48(Short), 79(Short), 1(Short), 1(Short), 2(Short), 2500.0(Double), 0.0(Double), 2500.0(Double), 54.2(Double), 4.0(Double), 230(Short), 4(Integer), 500(Short), 137.0(Double), 2.0(Double), 1.5(Double), 34.0(Double), 37.0(Double), 1300.0(Double), 乏力仍存,建议住院治疗(String), 1(Short), 55.5(Double), 0.0(Double), 0(Short), 1(Short), 20006(String), 50003(String), 2025-06-30 17:12:20.0(Timestamp), 74857(Integer), 2(Short), 1(Short), 50003(String), 2025-06-30 17:12:21.0(Timestamp), 07(String), 278(Long), 0(Short) 17:23:11 [http-nio-8005-exec-99] DEBUG cn.iheal.system.mapper.HemolistMapper.updateByOrigin - <== Updates: 189这是更行了189行数据吗
07-08
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值