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

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

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

语句很简单explain 发现 t_policy 没有走索引。

mysql> explain delete  from t_policy where id in (select policyid from t_policy_user where userid = 1067919);   
+----+--------------------+---------------+----------------+-----------------+----------+---------+------+--------+-------------+
| id | select_type        | table         | type           | possible_keys   | key      | key_len | ref  | rows   | Extra       |
+----+--------------------+---------------+----------------+-----------------+----------+---------+------+--------+-------------+
|  1 | PRIMARY            | t_policy      | ALL            | NULL            | NULL     | NULL    | NULL | 101523 | Using where |
|  2 | DEPENDENT SUBQUERY | t_policy_user | index_subquery | policyid,userid | policyid | 302     | func |      1 | Using where |
+----+--------------------+---------------+----------------+-----------------+----------+---------+------+--------+-------------+

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

mysql> explain select *  from t_policy where id in (select policyid from t_policy_user where userid = 1067919);       
+----+--------------+---------------+--------+-----------------+---------+---------+----------------------+------+-------+
| id | select_type  | table         | type   | possible_keys   | key     | key_len | ref                  | rows | Extra |
+----+--------------+---------------+--------+-----------------+---------+---------+----------------------+------+-------+
|  1 | SIMPLE       | <subquery2>   | ALL    | NULL            | NULL    | NULL    | NULL                 | NULL | NULL  |
|  1 | SIMPLE       | t_policy      | eq_ref | PRIMARY         | PRIMARY | 302     | <subquery2>.policyid |    1 | NULL  |
|  2 | MATERIALIZED | t_policy_user | ref    | policyid,userid | userid  | 8       | const                |    1 | NULL  |
+----+--------------+---------------+--------+-----------------+---------+---------+----------------------+------+-------+

网上一通查，说可以用 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

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

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%