使用PHP实现实时聊天功能的数据库设计与优化

CREATE TABLE User (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) UNIQUE NOT NULL,
    avatar VARCHAR(255)
);

CREATE TABLE ChatRecord (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    send_time DATETIME NOT NULL,
    content TEXT,
    FOREIGN KEY (sender_id) REFERENCES User(id),
    FOREIGN KEY (receiver_id) REFERENCES User(id)
);

// 创建数据库连接池
$pool = new SwooleCoroutineChannel(100);

// 向连接池中添加连接
$pool->push(new mysqli('localhost', 'user', 'password', 'database'));

// 从连接池中获取连接
$connection = $pool->pop();

// 执行数据库操作
$result = $connection->query("SELECT * FROM User");

// 将连接放回连接池中
$pool->push($connection);

// 创建协程
go(function () {
    // 异步查询
    $result = $db->query("SELECT * FROM User");

    // 处理查询结果
    while ($row = $result->fetch_assoc()) {
        // ...
    }
});

// 读操作从只读实例读取
$result = $read_only_db->query("SELECT * FROM User");

// 写操作由主数据库处理
$result = $master_db->query("INSERT INTO User (username) VALUES ('John')");