今天遇到一个性能问题,最后定位到一个删除操作特别慢。
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 的例句:
- --查询
- 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 ;
- -- 修改
- 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' ;
- --删除
- 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%