PHP中MySQL数据库的优化方案：

一、SQL语句优化

1. 索引优化

// 创建索引
CREATE INDEX idx_user_name ON users(username);
CREATE INDEX idx_user_status ON users(status);

// 复合索引（注意字段顺序）
CREATE INDEX idx_user_status_created ON users(status, created_at);

// 使用EXPLAIN分析SQL
EXPLAIN SELECT * FROM users WHERE username = 'test' AND status = 1;

2. 查询优化

// ❌ 避免使用SELECT *
$sql = "SELECT * FROM users WHERE status = 1";

// ✅ 只查询需要的字段
$sql = "SELECT id, username, email FROM users WHERE status = 1";

// ❌ 避免在WHERE子句中使用函数
$sql = "SELECT * FROM users WHERE DATE(created_at) = '2024-01-01'";

// ✅ 优化为范围查询
$sql = "SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'";

// ✅ 使用LIMIT限制结果集
$sql = "SELECT id, username FROM users WHERE status = 1 LIMIT 20";

3. JOIN优化

// ✅ 确保JOIN字段有索引
$sql = "SELECT u.username, o.order_no 
        FROM users u 
        INNER JOIN orders o ON u.id = o.user_id 
        WHERE u.status = 1";

// ✅ 小表驱动大表
// 如果users表小，orders表大，这样写更好
$sql = "SELECT u.username, o.order_no 
        FROM users u 
        LEFT JOIN orders o ON u.id = o.user_id 
        WHERE u.status = 1";

二、PHP代码层优化

1. 使用PDO预处理

<?php
class Database {
    private $pdo;
    
    public function __construct($config) {
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false, // 使用真正的预处理
        ];
        $this->pdo = new PDO($dsn, $config['user'], $config['pass'], $options);
    }
    
    // 使用预处理语句
    public function getUserById($id) {
        $stmt = $this->pdo->prepare("SELECT id, username FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }
    
    // 批量插入优化
    public function batchInsertUsers($users) {
        $values = [];
        $params = [];
        
        foreach ($users as $user) {
            $values[] = "(?, ?, ?)";
            $params[] = $user['username'];
            $params[] = $user['email'];
            $params[] = $user['created_at'];
        }
        
        $sql = "INSERT INTO users (username, email, created_at) VALUES " . implode(',', $values);
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }
}

2. 连接池管理

<?php
class ConnectionPool {
    private $connections = [];
    private $maxConnections = 10;
    
    public function getConnection() {
        if (count($this->connections) < $this->maxConnections) {
            // 创建新连接
            $connection = $this->createConnection();
            array_push($this->connections, $connection);
            return $connection;
        }
        
        // 复用现有连接
        return array_shift($this->connections);
    }
    
    public function releaseConnection($connection) {
        array_push($this->connections, $connection);
    }
    
    private function createConnection() {
        // 创建数据库连接
        return new PDO("mysql:host=localhost;dbname=test", "user", "pass");
    }
}

3. 缓存查询结果

<?php
class UserModel {
    private $cache;
    private $db;
    
    public function __construct() {
        $this->cache = new Redis(); // 使用Redis缓存
        $this->cache->connect('127.0.0.1', 6379);
        $this->db = new PDO("mysql:host=localhost;dbname=test", "user", "pass");
    }
    
    public function getUserStats($userId) {
        $cacheKey = "user_stats_{$userId}";
        
        // 尝试从缓存获取
        $stats = $this->cache->get($cacheKey);
        if ($stats) {
            return json_decode($stats, true);
        }
        
        // 缓存不存在，查询数据库
        $stmt = $this->db->prepare("
            SELECT 
                COUNT(*) as order_count,
                SUM(amount) as total_amount
            FROM orders 
            WHERE user_id = ? 
            AND created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
        ");
        $stmt->execute([$userId]);
        $stats = $stmt->fetch(PDO::FETCH_ASSOC);
        
        // 存入缓存，有效期5分钟
        $this->cache->setex($cacheKey, 300, json_encode($stats));
        
        return $stats;
    }
}

三、架构层面优化

1. 读写分离

<?php
class DatabaseProxy {
    private $master;
    private $slaves = [];
    
    public function __construct($masterConfig, $slavesConfig) {
        $this->master = $this->connect($masterConfig);
        foreach ($slavesConfig as $slaveConfig) {
            $this->slaves[] = $this->connect($slaveConfig);
        }
    }
    
    public function query($sql, $params = []) {
        // 判断是否写操作
        if (preg_match('/^(INSERT|UPDATE|DELETE)/i', trim($sql))) {
            return $this->execute($this->master, $sql, $params);
        }
        
        // 读操作随机选择一个从库
        $slave = $this->slaves[array_rand($this->slaves)];
        return $this->execute($slave, $sql, $params);
    }
    
    private function execute($connection, $sql, $params) {
        $stmt = $connection->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
}

2. 分库分表策略

<?php
class ShardingStrategy {
    private $tableCount = 10;
    
    // 根据用户ID分表
    public function getUserTable($userId) {
        $tableIndex = $userId % $this->tableCount;
        return "users_{$tableIndex}";
    }
    
    // 根据时间分表
    public function getOrderTable($date) {
        $yearMonth = date('Y_m', strtotime($date));
        return "orders_{$yearMonth}";
    }
    
    public function insertUser($userData) {
        $userId = $userData['id'];
        $tableName = $this->getUserTable($userId);
        
        $sql = "INSERT INTO {$tableName} (id, username, email) VALUES (?, ?, ?)";
        // 执行插入操作
    }
}

3. 异步处理优化

<?php
class AsyncDatabase {
    public function asyncQuery($sql, $callback) {
        // 使用Swoole或其他异步框架
        go(function() use ($sql, $callback) {
            $db = new PDO("mysql:host=localhost;dbname=test", "user", "pass");
            $result = $db->query($sql);
            $callback($result->fetchAll());
        });
    }
}

四、配置优化

1. MySQL配置优化

# my.cnf 优化配置
[mysqld]
# 连接数优化
max_connections = 500
max_connect_errors = 10000

# 缓冲区优化
innodb_buffer_pool_size = 4G  # 设置为物理内存的70%
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2

# 查询缓存
query_cache_type = 1
query_cache_size = 256M

# 临时表优化
tmp_table_size = 64M
max_heap_table_size = 64M

# 排序优化
sort_buffer_size = 2M
join_buffer_size = 2M

2. PHP-FPM配置优化

; php-fpm.conf
pm = dynamic
pm.max_children = 50
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 35
pm.max_requests = 500

五、监控与维护

1. 慢查询监控

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

2. 定期维护

<?php
class DatabaseMaintenance {
    private $db;
    
    public function __construct($pdo) {
        $this->db = $pdo;
    }
    
    // 定期优化表
    public function optimizeTables() {
        $tables = $this->db->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
        
        foreach ($tables as $table) {
            $this->db->exec("OPTIMIZE TABLE {$table}");
            $this->db->exec("ANALYZE TABLE {$table}");
        }
    }
    
    // 清理过期数据
    public function cleanExpiredData() {
        // 删除6个月前的日志
        $this->db->exec("DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)");
        
        // 清理未验证的用户
        $this->db->exec("DELETE FROM users WHERE status = 0 AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)");
    }
}

这些优化方案需要根据实际项目需求和服务器配置来选择合适的组合使用。建议从SQL语句和索引优化开始，逐步深入架构层面的优化。

已有 17 条评论

DatabaseGuru

The EXPLAIN command is probably the most underused tool in SQL optimization. People spend hours guessing why queries are slow when EXPLAIN tells them exactly what's happening. Good article for including that.
PHPDeveloper

索引优化那部分太实用了。之前有个查询要好几秒，加了复合索引后直接毫秒级。不过有个坑提醒大家：复合索引的字段顺序真的很重要，最左前缀原则必须遵守，不然索引用不上。

PHP中MySQL数据库的优化方案：

一、SQL语句优化

1. 索引优化

2. 查询优化

3. JOIN优化

二、PHP代码层优化

1. 使用PDO预处理

2. 连接池管理

3. 缓存查询结果

三、架构层面优化

1. 读写分离

2. 分库分表策略

3. 异步处理优化

四、配置优化

1. MySQL配置优化

2. PHP-FPM配置优化

五、监控与维护

1. 慢查询监控

2. 定期维护

已有 17 条评论

LABEL CLASSIFICATION

CATEGORY ARTICLES

LATEST RELEASE

NEWEST REPLIES

登录

PHP中MySQL数据库的优化方案：

一、SQL语句优化

1. 索引优化

2. 查询优化

3. JOIN优化

二、PHP代码层优化

1. 使用PDO预处理

2. 连接池管理

3. 缓存查询结果

三、架构层面优化

1. 读写分离

2. 分库分表策略

3. 异步处理优化

四、配置优化

1. MySQL配置优化

2. PHP-FPM配置优化

五、监控与维护

1. 慢查询监控

2. 定期维护

已有 17 条评论

LABEL CLASSIFICATION

CATEGORY ARTICLES

LATEST RELEASE

NEWEST REPLIES