php - 如何让我的 PDO 连接更有效率？

Question

好的，我想我应该首先说明下面的代码片段是我的最终目标。我希望能够在我的应用程序中的任何位置运行这样的一行，并让它处理 PDO 连接和执行:

数据库::查询('QUERY');

为此，我在后台运行了以下两个文件。

Connection.php:处理 PDO 连接。

class Connection {

    /**
     * The PDO connection.
     *
     * @var PDO
     */
    protected $pdo = NULL;

    /**
     * The type of database we're connection to.
     *
     * @var string
     */
    protected $type = '';

    /**
     * The name of the connected database.
     *
     * @var string
     */
    protected $database = '';

    /**
     * The database connection details.
     *
     * @var array
     */
    protected $config = array();

    /**
     * Create the connection instance.
     *
     */
    public function __construct()
    {
        // Import the configuration information from database.php
        $this->config = Config::load('database');

        // Pull the database type
        $this->type = $this->config['database'];

        // Pull the database name
        if(isset($this->config[$this->type]['database']))
        {
            $this->database = $this->config[$this->type]['database'];   
        }

        // Check to see if a connection has been made or not
        if($this->pdo==NULL)
        {
            // Create the connection
            $this->pdo = $this->createConnection();
        }   
    }

    /**
     * Query the database.
     *
     * @param string $query
     * @return array
     */
    public function query($query)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Execute the raw query
            $query = $this->pdo->query($query);

            return $query;
        }

        return false;
    }

    /**
     * Execute a query on the database.
     *
     * @param string $query
     * @return int
     */
    public function exec($query)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Execute the raw query
            $execution = $this->pdo->exec($query);

            return $execution;
        }

        return false;
    }

    /**
     * Execute a query and return the last inserted Id
     *
     * @param string $query
     * @return int
     */
    public function execLastId($query)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Execute the query and return the Id
            if($this->exec($query))
            {
                return $this->pdo->lastInsertId();
            }
        }

        return false;
    }

    /**
     * Prepare and execute against the database.
     *
     * @param string $query
     * @param array $params
     * @return array
     */
    public function prepare($query, $params)
    {
        // Check to see if we have a connection
        if($this->pdo!=NULL)
        {
            // Prepare the query
            $query = $this->pdo->prepare($query);

            // Execute the query
            $query->execute($params);

            return $query->fetchAll();
        }

        return false;
    }

    /**
     * Create a new PDO connection.
     *
     * @return PDO
     */
    protected function createConnection()
    {
        // See if we can attempt to make a connection
        if(isset($this->config[$this->type]))
        {
            $hasDSN = false;

            // Decide what DSN to use
            if($this->type=='mysql')
            {
                $hasDSN = true;

                $dsn = $this->getMySQLDSN();
            }

            // If a DSN has been found, make the connection
            if($hasDSN)
            {
                $username = $this->config[$this->type]['username'];
                $password = $this->config[$this->type]['password'];

                return new PDO($dsn, $username, $password); 
            }
        }

        return NULL;
    }

    /**
     * Get the MySQL DSN.
     *
     * @return string
     */
    protected function getMySQLDSN()
    {
        return 'mysql:host='.$this->config['mysql']['hostname'].';dbname='.$this->database;
    }

}

Database.php:是连接之间的中介。

class Database {

    /**
     * Run a raw query on the database.
     *
     * @param string $query
     * @return array
     */
    public static function query($query)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->query($query);
    }

    /**
     * Execute a query on the database.
     *
     * @param string $query
     * @return int
     */
    public static function exec($query)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->exec($query);
    }

    /**
     * Execute a query and return the last inserted Id
     *
     * @param string $query
     * @return int
     */
    public static function execLastId($query)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->execLastId($query);
    }

    /**
     * Prepare and then execute a query.
     *
     * @param string $query
     * @param array $params
     * @return array
     */
    public static function prepare($query, array $params)
    {
        // Create the connection
        $conn = new Connection;

        // Return the query
        return $conn->prepare($query, $params);
    }
}

我的问题是:这是否有效？有更简单的方法吗？我将不胜感激任何指导。我喜欢将自己视为初学者，但我确实缺乏确保应用程序高效并减轻自身重量的经验。

Answer 1

这效率不高。每次执行查询时，都会创建一个新的 Connection 对象，该对象又会创建一个新的 PDO 连接。连接到数据库会给你一些开销。您实际上不需要每次都连接。您可以只连接一次并将该连接用于后续查询。

因此，通过一个小改动，您可以让您的数据库类在首次使用时创建连接:

class Database {

    /**
     * Reference to the connection
     */
    private static $connection = null;

    /**
     * Run a raw query on the database.
     *
     * @param string $query
     * @return array
     */
    public static function query($query)
    {
        // Get the connection
        $conn = self::getConnection();

        // Return the query
        return $conn->query($query);
    }

    public static function getConnection()
    {
      // Create the connection if needed.
      if (self::$connection === null)
      {
        self::$connection = new Connection;
      }
      // Return new or existing instance.
      return self::$connection;
    }

    ...