MySQL的插入,如果不更新
我在MySQLMySQL的插入,如果不更新
wi_indv_training(trn_id,ind_id,is_deleted)
wi_trn_org(trn_id,grp_id,is_deleted)
wi_indv_org(ind_id,grp_id,is_deleted)
这里三个表,多组可以被分配到由wi_trn_org表处理的培训。 多个人可以分配到多个组。许多个人都参与培训。在这里,我们提供的个人已经与团体相关联。 现在在插入一个组进行训练时,该组中的个人必须与训练相关联。在给定的训练中可能已经有is_deleted = yes标记的组和个人(部分或全部)。
现在,在插入时,我想用is_deleted = no标记已删除的组和个人,如果数据已经存在于相应的表中,则应该将数据作为新数据插入。为此,我实施了以下性能非常缓慢的方法;
foreach ($grp_id as $key => $value) {
# code...
$rs=$this->db->pdoQuery("SELECT * FROM wi_trn_org
WHERE trn_id='$trn_id'
AND grp_id='$value'")->results();
if(count($rs)>0)
{
$this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=0
WHERE trn_id='$trn_id' AND grp_id='$value'");
$grp_indv=$this->db->pdoQuery("SELECT ind_id
FROM wi_indv_org
WHERE grp_id='$value'
AND is_deleted=0")->results();
foreach ($grp_indv as $ke => $va)
{
$ind_id=$va['ind_id'];
$rows=$this->db->pdoQuery("SELECT * FROM wi_indv_training
WHERE trn_id='$trn_id'
AND ind_id='$ind_id'")->results();
if(count($rows)>0)
{
$this->db->pdoQuery("UPDATE wi_indv_training
SET is_deleted=0,indv_source='Group'
WHERE trn_id='$trn_id'
AND ind_id='$ind_id'");
}
else
{
$this->db->insert("wi_indv_training",
array("ind_id"=>$ind_id,
"indv_source"=>'Group',
"trn_id"=>$trn_id,
"attendance"=>"yes",
"active"=>"yes"));
}
}
}
else
{
$this->db->insert("wi_trn_org",array('trn_id'=>$trn_id,'grp_id'=>$value));
$grp_indv=$this->db->pdoQuery("SELECT ind_id
FROM wi_indv_org
WHERE grp_id='$value'
AND is_deleted=0")->results();
foreach ($grp_indv as $ke => $va)
{
$ind_id=$va['ind_id'];
$rows=$this->db->pdoQuery("SELECT *
FROM wi_indv_training
WHERE trn_id='$trn_id'
AND ind_id='$ind_id'")->results();
if(count($rows)>0)
{
$this->db->pdoQuery("UPDATE wi_indv_training
SET is_deleted=0,
indv_source='Group'
WHERE trn_id='$trn_id'
AND ind_id='$ind_id'");
}
else
{
$this->db->insert("wi_indv_training",
array("ind_id"=>$ind_id,
"indv_source"=>'Group',
"trn_id"=>$trn_id,
"attendance"=>"yes",
"active"=>"yes"));
}
}
}
}
任何人都可以帮助关于性能增强的最佳算法?
这里的主要问题是你在一个循环中的多个查询:
foreach ($grp_id as $key => $value) {
# code...
$rs=$this->db->pdoQuery("SELECT * FROM wi_trn_org WHERE trn_id='$trn_id' AND grp_id='$value'")->results();
...
}
没有为每个SQL查询的性能损失,因此,如果你的循环运行1000次,你打的性能瓶颈1001次。
解决此问题的方法是在循环内重新构造查询,作为循环外部查询的一部分。第一个查询可以重写为原始查询的JOIN,这可能会给您足够的性能来解决您的问题。如果这还不够,你可以将所有这些折成Stored Routine。
我研究并找到了解决我的查询,即
$this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=1 WHERE trn_id='$trn_id'");
$this->db->pdoQuery("UPDATE wi_indv_training SET is_deleted=1 WHERE trn_id='$trn_id'");
$this->db->pdoQuery("INSERT INTO wi_trn_org(trn_id, grp_id) SELECT $trn_id AS trn_id, grp_id FROM wi_group WHERE grp_id IN ($grp) AND grp_id NOT IN (SELECT DISTINCT (grp_id) FROM wi_trn_org)");
$this->db->pdoQuery("UPDATE wi_trn_org SET is_deleted=0 WHERE trn_id='$trn_id' AND grp_id IN ($grp)");
$this->db->pdoQuery("INSERT INTO wi_indv_training(ind_id, trn_id) SELECT ind_id, $trn_id AS trn_id FROM wi_indv_org WHERE grp_id IN ($grp) AND ind_id NOT IN (SELECT DISTINCT (ind_id) FROM wi_indv_training WHERE trn_id =$trn_id)");
$this->db->pdoQuery("UPDATE wi_indv_training SET is_deleted=0,indv_source='Group',attendance='yes',active='yes' WHERE trn_id='$trn_id' AND ind_id IN (SELECT DISTINCT(ind_id) FROM wi_indv_org WHERE grp_id IN ($grp))");
这为我提供了在一定程度上增强性能的解决方案。这个算法是否真的增强了查询性能?
让我们来看看。你说你有一些表演。您发布了一些查询。根据你写的内容,结果是你有表现。 Ergo是唯一可能的逻辑结论,即上面的代码确实提高了性能。问题是,你是否了解幕后发生了什么以及数据库如何调整以实现性能? – 2014-09-01 15:04:41
是的,我明白了背后发生了什么。 – 2014-09-02 14:01:25
能否请您详细说明一下例子。我尝试使用INSERT INTO ... ON DUPLICATE KEY UPDATE。但我无法得到结果。 – 2014-09-01 05:33:58
你做了我建议的重试:试图让第一个SQL SELECT语句退出循环?那工作?这将比尝试解决整个事情更简单 - 取决于您的数据库大小,这可能就足够了。 – JasonS 2014-09-01 12:13:33
这减少了Loop中多个SELECT查询的开销,从而减少了时滞。 – 2014-09-02 04:21:18