Mysql 带 in 的删除操作会不走索引-白红宇

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

阅读量：4215 次

发布时间：2019-05-26

本文共 3651 字，大约阅读时间需要 12 分钟。

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

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 的例句：

--查询  
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  
 ON  tp.tp_id=pq.tpid  WHERE  pq.bfjgm='27010825'  AND  ps_bfsj >= '2013-01' AND  ps_bfsj< 2013-05-31  ;  

[sql]
 
-- 修改  
  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]
 
--删除  
 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%

转载地址：http://sqnmi.baihongyu.com/

你可能感兴趣的文章

php图像处理函数大全(缩放、剪裁、缩放、翻转、旋转、透明、锐化的实例总结)