Boost PHP Performance with AMPHP’s Asynchronous MySQL Client

Overview

AMPHP is a collection of event‑driven PHP libraries designed with coroutines and concurrency in mind. amphp/mysql is an asynchronous MySQL client that distributes queries across a scalable pool of available connections, running entirely in user‑space PHP without requiring external extensions such as ext/mysqli or ext/pdo.

Features

Provides a non‑blocking API for issuing multiple MySQL queries concurrently.

Transparent connection pool that overcomes MySQL’s synchronous connection protocol.

Supports MySQL transport encoding, including gzip and TLS encryption.

Offers parameterised prepared statements.

Nested transactions with commit and rollback event hooks.

Unbuffered results to reduce memory usage for large result sets.

Full MySQL protocol support, including all asynchronous commands.

Installation

Install the package via Composer:

composer require amphp/mysql

Usage

Getting Started

<?php
/**
 * @desc   mysql.php
 * @author Tinywan (ShaoBo Wan)
 * @date   2024/8/16 11:19
 */
declare(strict_types=1);

require 'vendor/autoload.php';

use Amp\Mysql\MysqlConfig;
use Amp\Mysql\MysqlConnectionPool;

$config = MysqlConfig::fromString("host=127.0.0.1 user=root password=123456 db=test");
$pool = new MysqlConnectionPool($config);

$statement = $pool->prepare("SELECT * FROM mall_member WHERE member_time = :member_time Limit 10");
$timeOne = microtime(true);
$result = $statement->execute(['member_time' => 0]);
foreach ($result as $key => $row) {
    echo "[x] [".$key."] ".$row['member_name'].PHP_EOL;
}
$timeTwo = microtime(true);

echo "[x] Run Time Result : ".($timeTwo - $timeOne).PHP_EOL;

Sample output shows the fetched rows and the total execution time (≈0.046 seconds in the example).

Iterator Example

<?php
require 'support/bootstrap.php';
use Amp\Future;
use Amp\Mysql\MysqlConfig;
use Amp\Mysql\MysqlConnectionPool;
use function Amp\async;

$db = new MysqlConnectionPool(MysqlConfig::fromAuthority(DB_HOST, DB_USER, DB_PASS, DB_NAME));
$db->query("DROP TABLE IF EXISTS tmp");
$db->query("CREATE TABLE IF NOT EXISTS tmp (a INT(10), b INT(10))");
print "Table successfully created." . PHP_EOL;

$statement = $db->prepare("INSERT INTO tmp (a, b) VALUES (?, ? * 2)");
$future = [];
foreach (range(1, 5) as $num) {
    $future[] = async(fn () => $statement->execute([$num, $num]));
}
$results = Future\await($future);
print "Insertion successful (if it wasn't, an exception would have been thrown by now)" . PHP_EOL;

$result = $db->query("SELECT a, b FROM tmp");
foreach ($result as $row) {
    var_dump($row);
}
$db->query("DROP TABLE tmp");
$db->close();

Transaction Support

<?php
require 'support/bootstrap.php';
require 'support/generic-table.php';
use Amp\Mysql\MysqlConfig;
use Amp\Mysql\MysqlConnectionPool;

$db = new MysqlConnectionPool(MysqlConfig::fromAuthority(DB_HOST, DB_USER, DB_PASS, DB_NAME));
createGenericTable($db);

$transaction = $db->beginTransaction();
$transaction->execute("INSERT INTO tmp VALUES (?, ? * 2)", [6, 6]);
$result = $transaction->execute("SELECT * FROM tmp WHERE a >= ?", [5]); // Two rows should be returned.
foreach ($result as $row) {
    var_dump($row);
}
$transaction->rollback();
// Run same query again, should only return a single row since the other was rolled back.
$result = $db->execute("SELECT * FROM tmp WHERE a >= ?", [5]);
foreach ($result as $row) {
    var_dump($row);
}
$db->close();

The examples demonstrate how to perform concurrent queries, iterate over result sets without buffering, and manage nested transactions with commit/rollback hooks using the asynchronous API.