Optimization of multi-level distribution docking third-party API data acquisition system

Recently I am working on a multi-level distribution management system based on Youzan. All members’ stores are in Youzan Mall and use Youzan API to obtain their performance. However, Youzan only provides one level of distribution, so this system was created. In order to reduce workload and clarify hierarchical relationships, OOP design methods were adopted to encapsulate databases and tables into base classes, distribution members, storefronts and other inheritance tables.

However, serious performance problems occurred when listing sales reports and distributors. Since the performance rewards of distributors are linked to their subordinate distributors, when encapsulating the database, a DFS traversal was performed to obtain the results of all distributors. The relationship tree, however, does not require such a relationship when listing sales reports. DFS spends a lot of time on the PHP language, resulting in a page taking more than 10 seconds to open, so the first step is to optimize it. , set the DFS switch in the database encapsulation function:

This is a partial implementation of the Database class, in which some sensitive data has been hidden.

<?php
namespace System;
require_once(&#39;KdtApiClient.php&#39;);
class Database
{
	const db_adr="";
	const db_usr="";
	const db_pwd="";
	const db_db="";
	public $member=array();
	public function init($dfs=true,$store=true)
	{
		$mysqli=new \mysqli(self::db_adr,self::db_usr,self::db_pwd,self::db_db);
		if($mysqli->connect_error) throw new Exception('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
		$result=$mysqli->query("select `id` from member_new");
		$row=$result->fetch_array();		
		$i=0;
		while($row)
		{
			$this->member[$i]=new Member($row[0],$dfs,$store);
			$row=$result->fetch_array();
			$i++;
		}
		$mysqli->close();
	}
	static public function doQuery($string)
	{
		$mysqli=new \mysqli(self::db_adr,self::db_usr,self::db_pwd,self::db_db);
		if($mysqli->connect_error) throw new Exception('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
		return $mysqli->query($string);
	}
	static public function querySell($start,$end)
        {}
}

The member class is inherited from Table:

class Table
{
	public $data=array();
	protected $table_name;
	public function __construct($id)
	{
		$this->data['id']=$id;
		$mysqli=new \mysqli(Database::db_adr,Database::db_usr,Database::db_pwd,Database::db_db);
		if($mysqli->connect_error) throw new Exception('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
		$result=$mysqli->query("select * from ".$this->table_name." where `id`=$id");
		$row=$result->fetch_assoc();
		$this->data=$row;
		$mysqli->close();
	}
	public function updateAll()				//Do NOT CHANGE ID!!!
	{
		reset($this->data);
		while($data=each($this->data))
		{
			$querystring="update ".$this->table_name." set `".$data[0]."`='$data[1]' where `id`='".$this->data['id']."'";
			Database::doQuery($querystring);
		}
		reset($this->data);
	}
	public function update($key)
	{
		$querystring="update ".$this->table_name." set `$key`='".$this->data[$key]."' where `id`='".$this->data['id']."'";
		Database::doQuery($querystring);
	}
	public function set($key,$data)		//recommended
	{
		$this->data[$key]=$data;
		$this->update($key);
	}
	public function get($key)				//recommended
	{
		return $this->data[$key];
	}

}

encapsulates the basic operations on the table, simplifying future code writing. The constructor of the member class below can complete the DFS function. After adding switch parameters, it can be used in a certain In some cases, the efficiency is greatly improved without using DFS:

class Member extends Table
{
	protected $table_name="member_new";
	public $infer=array();
	public $store=array();
	public function __construct($id,$dfs=true,$store=true)
	{
		parent::__construct($id);
		$mysqli=new \mysqli(Database::db_adr,Database::db_usr,Database::db_pwd,Database::db_db);
		if($mysqli->connect_error) throw new Exception('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
		if($dfs){
			$result=$mysqli->query("select `id` from ".$this->table_name." where `super`=".$this->data['id']);
			if($result){
				$row=$result->fetch_array();
				$i=0;
				while($row)
				{
					$this->infer[$i]=new Member($row[0]);
					$row=$result->fetch_array();
					$i++;
				}
			}
		}
		if($store)
		{
			$result=$mysqli->query("select `id` from store_new where `member`=".$this->data['id']);
			if($result){
				$row=$result->fetch_array();
				$i=0;
				while($row)
				{
					$this->store[$i]=new Store($row[0]);
					$row=$result->fetch_array();
					$i++;
				}
			}
		}
		$mysqli->close();
	}

(Some sensitive functions have been hidden)

In addition, caching support is added to the php script that responds to the front-end AJAX, because the api call speed of Youzan is slower , however, the data of this kind of distribution mall does not have very high real-time requirements, so server caching is used to reduce the slowdown caused by communication with Youzan. In addition, localized objects replace reading information from the remote database. , and also reduces the cost of querying the database. The implementation of the Cache object is as follows, storing the serialization code of the object locally on the server:

<?php
	namespace System;
    class Cache
    {
        public function __construct()
        {
        }
        
        static public function readCache($string)
        {
			error_reporting(1);
            $file=fopen($string.".ser","r");
            if(!$file)return false;
            $ser=fread($file,filesize($string.".ser"));
            fclose($file);
            $array=array();
            $array=unserialize($ser);
            if(time()-$array[&#39;time&#39;]>3600*24)return false;
            return $array['data'];
        }

        static public function updateCache($string,$data)
        {
            $array=array();
            $array['time']=time();
            $array['data']=$data;
            $file=fopen($string.".ser","w");
            fwrite($file,serialize($array));
            fclose($file);
        }
    }

The back-end AJAX response page chooses to obtain local data based on the feedback of the Cache class, or request server updates Local data:

<?php
require_once(&#39;System/db.php&#39;);
require_once(&#39;System/cache.php&#39;);
use \System\Database;
use \System\Cache;

switch($_GET[&#39;action&#39;])
{
	case &#39;num&#39;:
		$result=Database::doQuery("select count(*) from member_new");
		$row=$result->fetch_array();
		echo $row[0];
		exit(0);
	case 'get':
		if($array=Cache::readCache("member"))
		{
			echo json_encode($array);
		}
		else
		{
			$db=new Database();
			$db->init(false,true);
			$arr=array();
			for($i=0;$i<count($db->member);$i++)
			{
				$arr[$i]=array();
				$arr[$i]=$db->member[$i]->data;
				$arr[$i]['password']=null;
				$arr[$i]['name']=iconv("GBK","utf-8",$arr[$i]['name']);
				$arr[$i]['nickname']=iconv("GBK","utf-8",$arr[$i]['nickname']);
				$arr[$i]['sell']=$db->member[$i]->getSell();
			}
			Cache::updateCache("member",$arr);
			echo json_encode($arr);
		}
		exit(0);

After the above optimization, the original response time of the page was about 15s, and now the response time is about 0.5s.

Copyright statement: This article is an original article by the blogger and may not be reproduced without the blogger's permission.

The above introduces the optimization of multi-level distribution docking third-party API data acquisition system, including aspects of the content, I hope it will be helpful to friends who are interested in PHP tutorials.