Solving the Query Problem of Database N+1

Time:2019-7-30

demand

The data table is as follows:

Department table

|id|name|

User table

|id|name|department_id|

The requirement is to get the data of the following structure:

[
    {
        "id":1,
        "name":"test",
        "department_id":1,
        "department":{
            "id":1,
            "Name": "testing department"
        }
    }
]

Method 1: Cyclic query

  1. Query User List
  2. Loop the user list to query the corresponding department information
$users = $db->query('SELECT * FROM `user`');
foreach($users as &$user) {
    $users['department'] = $db->query('SELECT * FROM `department` WHERE `id` = '.$user['department_id']);
}

The query times of this method are: 1 + N (1 query list, N query departments), which has the lowest performance and is not desirable.

Method 2: Connecting tables

  1. Query user and department data through linked tables
  2. Processing returned data
$users = $db->query('SELECT * FROM `user` INNER JOIN `department` ON `department`.`id` = `user`.`department_id`');
// Manually process the returned result as a requirement structure

This method also has limitations, ifuseranddepartmentIt is not possible to connect tables without being on the same server.

Method 3: 1 + 1 query

  1. This method first queries the list of users once.
  2. Remove the Department ID from the list to form an array
  3. Query the Department in step 2
  4. Merge final data

The code is roughly as follows:

$users = $db->query('SELECT * FROM `user`');
$departmentIds =[ ];
foreach($users as $user) {
    if(!in_array($user['department_id'], $departmentIds)) {
        $departmentIds[] = $user['department_id'];
    }
}
$departments = $db->query('SELECT * FROM `department` WHERE id in ('.join(',',$department_id).')');
$map = []; // [Department ID = > Department item]
foreach($departments as $department) {
    $map[$department['id']] = $department;
}

foreach($users as $user) {
    $user['department'] = $map[$user['department_id']] ?? null;
 }

This method has no restrictions on the two tables, and is a better approach under the prevailing situation of microservices.

Recommended Today

Java security framework

The article is mainly divided into three parts1. The architecture and core components of spring security are as follows: (1) authentication; (2) authority interception; (3) database management; (4) authority caching; (5) custom decision making; and;2. To build and use the environment, the current popular spring boot is used to build the environment, and the actual […]