php - 从两个相关表中批量删除性能

Question

我有两个表，结构如下:(系统中每种对象类型都有多个 ai_$object 表)

       ai_$object         ,       ai_ext_ids
--------------------        -------------------------- 
ext_id | hash                    id | ext_id | object_name | sfID

通过批量删除，我得到了 sf_ids，并且需要从这两个表中删除记录，这种删除的最愚蠢的方法是这样的:

伪代码-->

$idToBeDeleted = SELECT ext_id FROM ai_tr_tbl_extids WHERE objectName='$object' AND sfID ='$s'" ;

DELETE FROM ai_tr_tbl_extids WHERE objectName='$object' AND sfID ='$s';

DELETE FROM ai_$object WHERE ext_id='$idToBeDeleted';

我的问题是如何提高这部分的性能？这段代码在循环中运行，我应该使用数组进行批量删除来执行此删除吗: SELECT * FROM table WHERE ID IN (1,2,3,4)

Answer 1

应该为 objectName 列建立索引以获得更好的性能。并尝试以下查询。

$idToBeDeleted = SELECT ext_id FROM ai_tr_tbl_extids WHERE objectName='$object' AND sfID ='$s'" ;

DELETE FROM ai_tr_tbl_extids WHERE ext_id='$idToBeDeleted';

DELETE FROM ai_$object WHERE ext_id='$idToBeDeleted';

但是，如果您能够获取要删除的 ext_id 列表，则可以删除循环并使用 IN 进行删除。

DELETE FROM ai_tr_tbl_extids WHERE ext_id IN ($idToBeDeletedCommaSeparatedValue');

DELETE FROM ai_$object WHERE ext_id IN ($idToBeDeletedCommaSeparatedValue');