优化PHP对MySQL数据库的并发访问处理

// 连接池实现
class ConnectionPool {
    private $connections;
    private $maxConnections;

    public function __construct($maxConnections) {
        $this->connections = new SplQueue();
        $this->maxConnections = $maxConnections;
    }

    public function getConnection() {
        if (!$this->connections->isEmpty()) {
            return $this->connections->dequeue();
        } elseif (count($this->connections) < $this->maxConnections) {
            $conn = new mysqli('localhost', 'username', 'password', 'database');
            if ($conn->connect_error) {
                throw new Exception("Connection failed: " . $conn->connect_error);
            }
            return $conn;
        } else {
            // 等待连接可用
            usleep(50000); // 等待50毫秒
            return $this->getConnection();
        }
    }

    public function releaseConnection($conn) {
        $this->connections->enqueue($conn);
    }
}

// 使用事务
$mysqli = $pool->getConnection();
$mysqli->autocommit(FALSE);

try {
    $mysqli->query("START TRANSACTION");
    $mysqli->query("INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')");
    $mysqli->query("INSERT INTO orders (user_id, product_id) VALUES (LAST_INSERT_ID(), 1)");
    $mysqli->query("COMMIT");
} catch (Exception $e) {
    $mysqli->query("ROLLBACK");
    throw $e;
} finally {
    $mysqli->autocommit(TRUE);
    $pool->releaseConnection($mysqli);
}

// 使用Memcached作为缓存
$memcached = new Memcached();
$memcached->addServer('localhost', 11211);

$key = 'user_data_1';
if (($data = $memcached->get($key)) === false) {
    $mysqli = $pool->getConnection();
    $result = $mysqli->query("SELECT * FROM users WHERE id = 1");
    $data = $result->fetch_assoc();
    $memcached->set($key, $data, 3600); // 缓存一小时
    $pool->releaseConnection($mysqli);
}

// 使用缓存中的数据
echo $data['name'];

// 使用Prepared Statements
$mysqli = $pool->getConnection();
$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
$stmt->close();
$pool->releaseConnection($mysqli);