如何提高嵌套集模型中插入的千位数的速度?
问题描述:
我正在使用MySQL。我想要插入属于嵌套集模型的100.000个不同级别的地方(root_id,lft,rgt,level)。如果我不必担心树,我会做一个批量插入;但在这种情况下,我必须为这个订单的每个对象做3个查询:如何提高嵌套集模型中插入的千位数的速度?
foreach ($places as $place)
{
# get parent place from $ancestor_array (woeid => ids)
$parent = $ancestors[:parent_woeid]
update place set lft = lft + 2 where root_id = :root_id and lft >= :obj_lft;
update place set rgt = rgt + 2 where root_id = :root_id and rgt >= :obj_lft;
insert into place (..., root_id, lft, rgt, level, ...) values (..., :obj_root_id, :obj_lft, :obj_rgt, :obj_level, ...);
...
}
这需要很长的时间......所以它的时间来尝试更聪明。这是我在想什么:
foreach ($places as $place)
{
# get parent place from $ancestor_array (woeid => ids)
$parent = $ancestors[:parent_woeid]
$new_admins[] = array('parent_woeid' => :parent_woeid, ...data place....)
$woeids[] = :parent_woeid;
}
# lock + bulk insert of $new_admins with lft=rgt=level=root_id= null + unlock
insert into place (...) values (...), (...), (...), ...., (...)
# get ids of those places
select from place where woeid in(:array_woeids)
# lock + bulk update with 3 updates per place + unlock
update place set lft= lft + 2 where root_id = :root_id and lft >= :obj_lft;
update place set rgt = rgt + 2 where root_id = :root_id and rgt >= :obj_lft;
update place set lft=:obj_lft, rgt=:obj_rgt where id=:id
# i have to update the ancestors lft & rgt values on the $ancestors_array,
# because we have them "cached" in the array in order to avoid doing a
# select per inserted place.
你觉得呢?你会怎么做?您是否将所有插入和更新保存到文件中,并使用LOAD DATA INFILE
语法代替此选项?
有没有其他的选择我错过了?
非常感谢!
答
那岂不是更有效刚刚插入的项目,并重新计算所有的LFT/RGT指标,只有当插入的所有项目?
但是要做到这一点,你必须知道每个项目的父ID,所以你有什么要立足于指标。我个人有成功保持LFT + LRG + parentId的每个数据项,它使维护更容易(你可以做到在任何时间重新计算琐碎/完整性检查)。
+0
这就是我期待的答案!实际上,我已经有了parent_id,我已经有了重建树的任务。非常感谢! – fesja 2011-03-20 10:46:41
我现在不再使用嵌套集合模型,并选择一个邻接列表 - 生命太短。 – 2011-03-18 13:44:51