PHP 개발에서 데이터베이스 연결 및 쿼리 성능을 최적화하는 방법

// 连接数据库
$connection = mysqli_connect('localhost', 'user', 'password', 'database', null, null, MYSQLI_CLIENT_FOUND_ROWS | MYSQLI_CLIENT_COMPRESS | MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_INTERACTIVE | MYSQLI_CLIENT_IGNORE_SPACE);

// 使用连接进行查询
$result = mysqli_query($connection, 'SELECT * FROM table');

// 关闭连接
mysqli_close($connection);

class ConnectionPool
{
    private static $instance;
    private $connections;

    private function __construct()
    {
        // 初始化连接池
        $this->connections = new SplQueue();
    }

    public static function getInstance()
    {
        if (!self::$instance) {
            self::$instance = new ConnectionPool();
        }

        return self::$instance;
    }

    public function getConnection()
    {
        if (!$this->connections->isEmpty()) {
            return $this->connections->dequeue();
        }

        // 如果连接池中没有空闲的连接，则创建一个新的连接
        $connection = mysqli_connect('localhost', 'user', 'password', 'database', null, null, MYSQLI_CLIENT_FOUND_ROWS | MYSQLI_CLIENT_COMPRESS | MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_INTERACTIVE | MYSQLI_CLIENT_IGNORE_SPACE);
        return $connection;
    }

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

// 使用连接池进行查询
$pool = ConnectionPool::getInstance();
$connection = $pool->getConnection();
$result = mysqli_query($connection, 'SELECT * FROM table');
// 释放连接
$pool->releaseConnection($connection);

-- 在name字段上创建索引
CREATE INDEX index_name ON table (name);

-- 查询时使用索引
SELECT * FROM table WHERE name = 'John';

// 限制返回的列数
$result = mysqli_query($connection, 'SELECT name, age FROM table');

// 使用JOIN替代子查询
$result = mysqli_query($connection, 'SELECT t1.name, t2.age FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id');

// 使用WHERE子句过滤数据
$result = mysqli_query($connection, 'SELECT * FROM table WHERE age > 18');

// 使用UNION合并查询
$result = mysqli_query($connection, 'SELECT name FROM table1 UNION SELECT name FROM table2');

// 使用EXPLAIN分析查询计划
$result = mysqli_query($connection, 'EXPLAIN SELECT * FROM table');