MYSQL＆PHP：在PHP while循环中运行INSERT INTO SELECT查询，运行缓慢

Question

我对PHP和MYSQL真的很陌生，我一个月前都不知道，所以请原谅我的马虎/糟糕的代码:)

我有我的PHP中下面的代码：

$starttime = microtime(true); 
$q_un = 'SELECT i.id AS id 
      FROM items i 
      WHERE i.id NOT IN (SELECT item_id FROM purchased_items WHERE user_id=' . $user_id . ')'; 
$r_un = mysqli_query($dbc, $q_un); 
if (mysqli_num_rows($r_un) > 0) { 
while ($row_un = mysqli_fetch_array($r_un, MYSQLI_ASSOC)) { 
    $item_id = $row_un['id']; 
    $q_rec = 'INSERT INTO compatibility_recommendations (
       `recommendation`, 
       `user_id`, 
       `item_id`) 
       SELECT 
        ((SUM(a.rating*(a.compat-80)))/(SUM(a.compat-80)))*10 AS rec, 
        a.user_id AS user_id, 
        a.item_id AS item_id 
       FROM 
        (SELECT r.rating AS rating, 
         c.user2_id AS rater, 
         c.user1_id AS user_id, 
         c.compatibility AS compat, 
         r.item_id AS item_id 
        FROM ratings r 
        RIGHT JOIN compatibility_ratings c ON r.user_id=c.user2_id 
        WHERE c.user1_id=' . $user_id . ' AND r.item_id=' . $item_id . ' AND c.compatibility>80) a 
       ON DUPLICATE KEY UPDATE 
        recommendation = VALUES(recommendation)'; 
    $r_rec = mysqli_query($dbc, $q_rec); 
} 
} 
$endtime = microtime(true); 
$duration = $endtime - $starttime;</code> 

第一个查询选择当前用户，$ USER_ID，尚未购买的物品清单。然后在返回的每一行（item）上运行一个while循环，在此循环中执行主查询。

这下一个查询是从收视率表中获取信息，其中item_id等于当前正在查询的item_id，并将其加入到具有正确联接的预先计算的用户兼容性表中。

然后，我对评级和兼容性评级运算算法以形成推荐值，然后将推荐item_id和user_id插入到稍后调用的另一个表中。在（item_id，user_id）列上有一个2列唯一键，因此在末尾的ON DUPLICATE KEY UPDATE

所以我今天早上写了这段代码，对自己很满意，因为它确实是我需要的做。

问题是，可以预见，它很慢。在我的测试数据库中，有5个测试用户和100个测试项目以及200个评分的随机分组，它需要2.5秒才能通过while循环。我期待它会变慢，但不会这么慢。一旦添加了更多的用户和项目，这真的很难。主要问题是插入...在重复密钥更新部分，我的磁盘利用率达到100％，我可以告诉我的笔记本电脑的硬盘正在寻求疯狂。我知道我可能会在生产中使用固态硬盘，但我仍然预计有数千个项目和用户会遇到大规模问题。

所以我的主要问题在于：任何人都可以提供任何建议，如何优化我的代码，或完全rejig的东西，以提高速度。我确信在while循环中插入查询是一个不好的方法，我只是想不出任何其他方式来获得完全相同的结果

在此先感谢和抱歉，如果我格式化我的问题正确

Answer 1

$starttime = microtime(true); 
$q_un = " 

INSERT INTO compatibility_recommendations 
(recommendation 
,user_id 
,item_id 
) 
SELECT ((SUM(a.rating*(a.compat-80)))/(SUM(a.compat-80)))*10 rec 
     , a.user_id 
     , a.item_id 
    FROM 
     (SELECT r.rating rating 
      , c.user2_id rater 
      , c.user1_id user_id 
      , c.compatibility compat 
      , r.item_id 
      FROM compatibility_ratings c 
      JOIN ratings r 
      ON r.user_id = c.user2_id 

      JOIN items i 
      ON i.id = r.item_id 

      LEFT 
      JOIN purchased_items p 
      ON p.item_id = i.id 
      AND p.user_id = $user_id 

     WHERE c.user1_id = $user_id 
      AND c.compatibility > 80 
      AND p.item_id IS NULL 
    ) a 
GROUP BY a.item_id 
ON DUPLICATE KEY UPDATE recommendation = VALUES(recommendation); 

"; 

$r_rec = mysqli_query($dbc, $q_rec); 
} 
} 
$endtime = microtime(true); 
$duration = $endtime - $starttime;</code> 

对于任何进一步的改进，我们真的需要看到正确的DDL和上面的SELECT的解释。

Answer 2

见https://stackoverflow.com/a/14456661/2782404

FETCH_ASSOC可能比fetch_array显著快，你应立即获取你访问值之前。

Answer 3

我与刀片发现，我一直在寻找here

每个项目的第二个查询正在采取0.002秒只是选择的答案，但随后0.06秒，所以我异型查询，发现“查询结束“占据了查询时间的99％。我已经设置innodb_flush_log_at_trx_commit = 0，但对这个答案的评论皱眉。但是，我不使用交易，那么这种方法会有什么后果/替代方案吗？它确实将我的while循环时间从2.5秒减少到了0.08秒。