Mysql 带 in 的删除操作会不走索引

心已赠人 2022-05-26 03:27 135阅读 0赞

今天遇到一个性能问题，最后定位到一个删除操作特别慢。

delete  from t_policy where id in (select policyid from t_policy_user where userid = 1067919);

语句很简单explain 发现 t\_policy 没有走索引。

同样的语句改为select就很快，explain 发现会走索引。

网上一通查，说可以用 inner join 代替 in 果然可以！

mysql> explain delete tp from t_policy tp inner join t_policy_user tpu on tp.id = tpu.policyid where tpu.userid = 1067919;
    +----+-------------+-------+--------+-----------------+---------+---------+------------------+------+-------+
    | id | select_type | table | type   | possible_keys   | key     | key_len | ref              | rows | Extra |
    +----+-------------+-------+--------+-----------------+---------+---------+------------------+------+-------+
    |  1 | SIMPLE      | tpu   | ref    | policyid,userid | userid  | 8       | const            |    1 | NULL  |
    |  1 | SIMPLE      | tp    | eq_ref | PRIMARY         | PRIMARY | 302     | mdm.tpu.policyid |    1 | NULL  |
    +----+-------------+-------+--------+-----------------+---------+---------+------------------+------+-------+

以下为inner join 的例句：

1.  \--查询  
2.  SELECT tp.tp\_id, tp.tpmc, tp.leveid, tp.tpdz, tp.jgm, tp.scsj, tp.pbzyid, tp.ksbfsj, tp.jsbfsj, tp.status, tp.tpbz   FROM  qdgl\_tupian tp INNER JOIN  qdgl\_pqb  pq  
3.   ON  tp.tp\_id=pq.tpid  WHERE  pq.bfjgm='27010825'  AND  ps\_bfsj >= '2013-01' AND  ps\_bfsj< 2013-05-31  ;

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \-- 修改  
2.    UPDATE jx\_lsjl jx INNER JOIN isa\_gtxx gt ON jx.ckid=gt.gtbh  set jx.ckid=gt.gtid WHERE gt.jgm='27010825' AND jx.jgm='27010825' ;

**\[sql\]**  [view plain][]  [copy][view plain]

1.  \--删除  
2.   DELETE jx FROM  jx\_lsjl jx INNER JOIN isa\_gtxx gt ON jx.ckid=gt.gtbh    WHERE gt.jgm='27010825' AND jx.jgm='27010825' ;

至于mysql为什么会有这样的问题，找到的个人认为比较靠谱的解释是：

**之所以会卡  
是因为子查询的操作会铁定会创建临时表，当然创建内存临时表并不可怕  
但是当你数据量大了以后，内存临时表的单表大小限制后，临时表会转换为写磁盘形式的物理内存表  
  
这两个参数决定了你临时表的大小  
tmp\_table\_size  max\_heap\_table\_size  
  
你可以查看这两个状态状态的变化，看你数据库性语句是否有问题。  
Created\_tmp\_disk\_tables/Created\_tmp\_tables<5%   **

[view plain]: https://blog.csdn.net/jenminzhang/article/details/9629749#