PHP MySQL代码优化 - PHP MySQL code optimize

PHP MySQL code optimize

本文关键字：代码优化 MySQL PHP | 更新日期: 2023-09-27

是否有任何MySQL函数将优化这段代码?子ID获取所有父ID

function get_parents() {
$ids = array();
while($id) :
    $query = "SELECT placement_id FROM referrals WHERE user_id = $id";
    $query = $this->db->query($query);        
    $result = $query->row();  
    if(!isset($result->placement_id)) :
        break;
    elseif(isset($result->placement_id) && $result->placement_id == 2) :
        break;
    endif;
    $id = $result->placement_id;            
    array_push($ids, $id);
    if($result) :
        continue;
    endif;
    break;
endwhile;
return $ids;
}

上面的代码将返回给定user_id的所有父ID，如果没有找到，这将停止。我发现这段代码太慢，负载太重。

我的表

relations table
|   id  |   user_id |   placement_id    |
|   1   |       2   |       NULL        |
|   2   |       3   |       2           |
|   3   |       4   |       2           |
|   4   |       5   |       3           |
|   5   |       6   |       4           |
|   6   |       7   |       3           |
|   7   |       8   |       3           |
|   8   |       9   |       3           |
|   9   |       10  |       6           |
|   10  |       11  |       5           |
|   11  |       12  |       6           |
|   12  |       13  |       4           |
|   13  |       14  |       3           |
|   14  |       15  |       9           |
|   15  |       16  |       10          |

user_id为子节点，父节点为placement_id

您可以将代码重写为:

function get_parents() {
$ids = array();
while($id){
    $query = "SELECT placement_id FROM referrals WHERE user_id = $id";
    $query = $this->db->query($query);        
    $result = $query->row();  
    if(isset($result->placement_id) && $result->placement_id !== 2)
    {
        $id = $result->placement_id;            
        array_push($ids, $id);
    }
}
    return $ids;
}

它排除了一些额外的函数调用，如continue,break等。Make sure you have INT as type of user_id with indexing on this column.

我个人会这样做:

<?php
define('MAX_NEST_DEPTH', 15);
function get_parent($child_id) {
  $child_id = (int) $child_id;
  $parents = array();
  $counter = 0;
  do {
    $sql = "SELECT placement_id
      FROM referrals
      WHERE user_id = {$child_id}";
    $query = $this->db->query($query);
    $result = $query->row();
    $child_id = (int) $result->placement_id;
    $parent[] = $child_id;
    $counter++;
  }
  while ($child_id !=  0 || $counter == MAX_NEST_DEPTH);
  return $parents;
}

你不会在这里循环查询，mysql不支持n-level嵌套SELECT，否则我们可以一次完成。