mysql delete 不走索引_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 | | ALL | NULL | NULL | NULL | NULL | NULL | NULL |

| 1 | SIMPLE | t_policy | eq_ref | PRIMARY | PRIMARY | 302 | .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 的例句：

--查询

SELECTtp.tp_id, tp.tpmc, tp.leveid, tp.tpdz, tp.jgm, tp.scsj, tp.pbzyid, tp.ksbfsj, tp.jsbfsj, tp.status, tp.tpbzFROMqdgl_tupian tpINNERJOINqdgl_pqb  pq

ONtp.tp_id=pq.tpidWHEREpq.bfjgm='27010825'ANDps_bfsj >='2013-01'ANDps_bfsj

-- 修改

UPDATEjx_lsjl jxINNERJOINisa_gtxx gtONjx.ckid=gt.gtbhsetjx.ckid=gt.gtidWHEREgt.jgm='27010825'ANDjx.jgm='27010825';

--删除

DELETEjxFROMjx_lsjl jxINNERJOINisa_gtxx gtONjx.ckid=gt.gtbhWHEREgt.jgm='27010825'ANDjx.jgm='27010825';

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

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