[Go] Learn from Facebook and enable the asynchronous implementation of MySQL queries through the association Process

From: The asynchronous implementation of MySQL query via the co-process

Objective

Recently studied Ben Linders's speech and learned that Facebook's MySQL query can be asynchronous to improve performance. Because Facebook was implemented earlier, they had to hack PHP to achieve it. Now the php5.5, no need to hack can be achieved.
For the performance of a Web site, the bottleneck is mostly from the database. A general database query takes a large proportion of the overall time-consuming of a request. If you can improve the efficiency of database query, the overall response time of the site will be greatly reduced. If the MySQL query can be implemented asynchronously, you can implement multiple SQL statements at the same time. This can greatly shorten the time-consuming MySQL queries.

Why is async faster than synchronization?

Synchronizes queries when opposed to asynchronous queries. In general, MySQL query queries are synchronous. Let's compare the two ways below. An example of this comparison is the request for two times Select Sleep (1). This statement takes approximately 1000ms on the MySQL server side.

Execution flow of synchronous mode:

The first step is to send the first query request to the MySQL server side. It's probably time-consuming 1ms
The second step, the MySQL server side returns the results of the first query. It's probably time-consuming 1000ms
The third step is to send the request again to the MySQL server. It's probably time-consuming 1ms
In the fourth step, the MySQL server returns the results of the second query. It's probably time-consuming 1000ms
Synchronous execution two times select Sleep (1), which takes approximately 2002ms of time.

Execution flow of asynchronous mode:

The first step is to send the first query request to the MySQL server side. It's probably time-consuming 1ms
The second step is to send a second query request to the server while waiting for the first request to return data. It's probably time-consuming 1ms
The third step is to accept the two query requests returned by the MySQL server side. It's probably time-consuming 1000ms.

Comparative analysis
Asynchronous queries are faster than synchronous queries because multiple query statements are executed concurrently on the server side, greatly reducing the server-side response time. Parallel is generally faster than serial. The longer the SQL statement executes, the more obvious the effect is.

How do I implement an asynchronous query for MySQL?

The key to implementing an asynchronous query is to separate the sending request from the accepted return data. This feature is provided in just mysqlnd.
The corresponding methods in MYSQLND are:
Mysqlnd_async_query sending a query request
Mysqlnd_reap_async_query getting query Results
The mysqli extension is encapsulated for this feature of MYSQLND, and can be passed in Mysqli_async when the query method is called.

Specific code implementation can see the blog in PHP MySQL database asynchronous query implementation

Why use a co-process?
See the code implementation of blog post, is not the sense of writing and usually not the same? Generally in the project, we are in the form of function to call each other, the function contains the database query. In order to maintain this habit, convenient for everyone to use, so the introduction of the association process. Yield and generator are provided in php5.5 to facilitate our implementation of the process. The sample code is as follows:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 66676869707172

<?phpfunction f1(){    $db = new db();    $obj = $db->async_query(‘select sleep(1)‘);    echo "f1 async_query \n";    yield $obj;    $row = $db->fetch();    echo "f1 fetch\n";    yield $row;}function f2(){    $db = new db();    $obj = $db->async_query(‘select sleep(1)‘);    echo "f2 async_query\n";    yield $obj;    $row = $db->fetch();    echo "f2 fetch\n";    yield $row;}$gen1 = f1();$gen2 = f2();$gen1->current();$gen2->current();$gen1->next();$gen2->next();$ret1 = $gen1->current();$ret2 = $gen2->current(); var_dump($ret1);var_dump($ret2);class db{    static $links;    private $obj;    function getConn(){        $host = ‘127.0.0.1‘;        $user = ‘demo‘;        $password = ‘demo‘;        $database = ‘demo‘;        $this->obj = new mysqli($host, $user, $password, $database);        self::$links[spl_object_hash($this->obj)] = $this->obj;        return self::$links[spl_object_hash($this->obj)];    }   function async_query($sql){        $link = $this->getConn();        $link->query($sql, MYSQLI_ASYNC);        return $link;    }    function fetch(){        for($i = 1; $i <= 5; $i++){            $read = $errors = $reject = self::$links;            $re = mysqli_poll($read, $errors, $reject, 1);            foreach($read as $obj){                if($this->obj === $obj){                    $sql_result = $obj->reap_async_query();                    $sql_result_array = $sql_result->fetch_array(MYSQLI_ASSOC);//只有一行                    $sql_result->free();                    return $sql_result_array;                }            }        }    }}?>

The results of the execution on the terminal command line are as follows:

12345678910111213141516

$time php ./async.php f1 async_query f2 async_queryf1 fetchf2 fetcharray(1) {  ["sleep(1)"]=>  string(1) "0"}array(1) {  ["sleep(1)"]=>  string(1) "0"}real    0m1.016suser    0m0.007s

From the results we can see that the execution process is that the MySQL query was sent two times before the return data of the database is accepted. Under normal circumstances, at least 2000ms is required to complete the execution. However, real 0m1.016s shows that two queries take only 1016ms of time.
Tips: The above code is just sample code, and there are some areas that need to be perfected.

Attention

It is important to note that this parallel execution is not necessarily a good solution if the MySQL server itself is heavily loaded. Because, the MySQL server will create a separate thread for each link to process. If you create too many threads, it can be a burden to the system.

Resources

Facebook asynchronous MySQL how Facebook Queries Databases

[Go] Learn from Facebook and enable the asynchronous implementation of MySQL queries through the association Process