发布于 由admin

PHP 数据库读写分离与分库分表策略

嘿，大家好！欢迎来到今天的数据库性能提升研讨会。今天咱们不讲大道理，就来聊聊PHP项目里，如何通过读写分离和分库分表，让你的数据库跑得更快，更稳。

一、开场白：数据库，你的老伙计，也需要休息！

咱们的网站、App，背后都站着一个默默奉献的老伙计——数据库。它兢兢业业地存储着数据，响应着各种请求。但时间久了，数据量大了，并发高了，老伙计也难免会腰酸背痛，响应变慢。这时候，我们就得想想办法，让它轻松一些，跑得更快。

读写分离和分库分表，就是两种常用的“按摩”手法，能有效缓解数据库的压力。

二、读写分离：让老伙计各司其职！

想象一下，你是一家餐厅的老板，客人来了，既要点菜，又要结账，老板一个人忙不过来。怎么办？当然是分工合作！点菜的负责点菜，结账的负责结账。

读写分离就是这个道理。把数据库分成主库（Master）和从库（Slave），主库负责处理写操作（INSERT、UPDATE、DELETE），从库负责处理读操作（SELECT）。这样，读写操作就不会互相影响，提高了整体性能。

1. 读写分离的原理

  • 主库负责写： 所有的写操作都先在主库上执行。
  • 主从同步： 主库将数据同步到从库，保证数据一致性。
  • 从库负责读： 所有的读操作都从从库上读取数据。

2. PHP代码实现读写分离

最简单的方法，就是根据SQL语句的类型，选择不同的数据库连接。

<?php

class Database {
    private $master_config;
    private $slave_config;
    private $master_conn;
    private $slave_conn;

    public function __construct($master_config, $slave_config) {
        $this->master_config = $master_config;
        $this->slave_config = $slave_config;
    }

    // 连接主库
    private function connectMaster() {
        if (!$this->master_conn) {
            try {
                $dsn = "mysql:host={$this->master_config['host']};dbname={$this->master_config['dbname']};charset={$this->master_config['charset']}";
                $this->master_conn = new PDO($dsn, $this->master_config['username'], $this->master_config['password']);
                $this->master_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            } catch (PDOException $e) {
                die("Master database connection failed: " . $e->getMessage());
            }
        }
        return $this->master_conn;
    }

    // 连接从库 (随机选择一个从库)
    private function connectSlave() {
        if (!$this->slave_conn) {
            // 如果有多个slave，随机选择一个
            $slave = $this->slave_config[array_rand($this->slave_config)]; // 假设 $this->slave_config 是一个数组，包含多个从库的配置
            try {
                $dsn = "mysql:host={$slave['host']};dbname={$slave['dbname']};charset={$slave['charset']}";
                $this->slave_conn = new PDO($dsn, $slave['username'], $slave['password']);
                $this->slave_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            } catch (PDOException $e) {
                die("Slave database connection failed: " . $e->getMessage());
            }
        }
        return $this->slave_conn;
    }

    // 执行SQL语句
    public function query($sql) {
        $sql = trim($sql); // 去除首尾空格
        $is_select = strncasecmp($sql, 'select', 6) === 0; // 忽略大小写比较

        if ($is_select) {
            $conn = $this->connectSlave();
        } else {
            $conn = $this->connectMaster();
        }

        try {
            $stmt = $conn->prepare($sql);
            $stmt->execute();

            if ($is_select) {
                return $stmt->fetchAll(PDO::FETCH_ASSOC);
            } else {
                return $stmt->rowCount(); // 返回受影响的行数
            }
        } catch (PDOException $e) {
            echo "SQL Error: " . $e->getMessage();
            return false;
        }
    }

    // 关闭连接
    public function close() {
        $this->master_conn = null;
        $this->slave_conn = null;
    }
}

// 示例配置
$master_config = [
    'host' => '127.0.0.1',
    'dbname' => 'your_db',
    'username' => 'root',
    'password' => 'your_password',
    'charset' => 'utf8mb4'
];

$slave_config = [
    [ // 多个从库配置
        'host' => '127.0.0.1',
        'dbname' => 'your_db',
        'username' => 'root',
        'password' => 'your_password',
        'charset' => 'utf8mb4'
    ],
    [
        'host' => '127.0.0.2', // 另一个从库
        'dbname' => 'your_db',
        'username' => 'root',
        'password' => 'your_password',
        'charset' => 'utf8mb4'
    ]
];

// 使用示例
$db = new Database($master_config, $slave_config);

// 查询
$results = $db->query("SELECT * FROM users WHERE id = 1");
print_r($results);

// 插入
$affected_rows = $db->query("INSERT INTO users (name, email) VALUES ('Test User', '[email protected]')");
echo "Affected rows: " . $affected_rows;

$db->close();

?>

代码解释：

  • Database 类封装了数据库连接和查询操作。
  • connectMaster() 和 connectSlave() 分别负责连接主库和从库。 connectSlave() 可以随机选择从库，实现负载均衡。
  • query() 方法根据SQL语句的类型，选择不同的数据库连接。
  • strncasecmp($sql, 'select', 6) === 0; 用于判断SQL语句是否是SELECT语句，忽略大小写。
  • 使用 PDO 进行数据库连接和操作，更加安全和灵活。

3. 读写分离的注意事项

  • 数据同步延迟： 主库同步到从库需要时间，可能存在数据延迟。对于实时性要求高的场景，需要考虑这个问题。
  • 事务一致性： 跨多个数据库的事务，需要使用分布式事务来保证一致性。
  • 故障切换： 主库挂了，需要手动或自动切换到从库。
  • 配置复杂性： 读写分离的配置相对复杂，需要仔细配置。

三、分库分表：把大象切成小块！

如果说读写分离是让老伙计分工合作，那么分库分表就是把大象切成小块，让老伙计更容易消化。

当数据量大到单个数据库无法承受时，我们就需要将数据分散到多个数据库（分库）或多个表（分表）。

1. 分库分表的策略

  • 垂直分库： 按照业务模块划分数据库，例如用户库、商品库、订单库。
  • 水平分库： 将一个数据库的数据分散到多个数据库，例如按照用户ID的范围划分数据库。
  • 垂直分表： 将一个表的不同字段拆分到多个表，例如将用户表拆分成用户基本信息表和用户扩展信息表。
  • 水平分表： 将一个表的数据分散到多个表，例如按照用户ID的范围划分表。

2. 分库分表的关键：路由算法

分库分表的核心在于路由算法，也就是根据什么规则将数据分散到不同的数据库或表。

常用的路由算法有：

  • 范围路由： 按照数据范围划分，例如用户ID在1-10000的用户数据存储在表1，10001-20000的用户数据存储在表2。
  • 哈希路由： 对某个字段进行哈希运算，然后根据哈希值将数据分散到不同的数据库或表。例如 hash(user_id) % table_count。
  • 取模路由： 对某个字段取模运算，然后根据模值将数据分散到不同的数据库或表。例如 user_id % table_count。

3. PHP代码实现分库分表

这里以水平分表为例，使用取模路由算法。

<?php

class ShardingDatabase {
    private $db_config;
    private $table_count;

    public function __construct($db_config, $table_count) {
        $this->db_config = $db_config;
        $this->table_count = $table_count;
    }

    // 获取表名
    private function getTableName($user_id, $base_table_name) {
        $table_index = $user_id % $this->table_count;
        return $base_table_name . '_' . $table_index;
    }

    // 连接数据库
    private function connect() {
        try {
            $dsn = "mysql:host={$this->db_config['host']};dbname={$this->db_config['dbname']};charset={$this->db_config['charset']}";
            $conn = new PDO($dsn, $this->db_config['username'], $this->db_config['password']);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $conn;
        } catch (PDOException $e) {
            die("Database connection failed: " . $e->getMessage());
        }
    }

    // 执行SQL语句
    public function query($sql, $user_id, $base_table_name) {
        $table_name = $this->getTableName($user_id, $base_table_name);
        $sql = str_replace($base_table_name, $table_name, $sql); // 替换表名

        $conn = $this->connect();

        try {
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            echo "SQL Error: " . $e->getMessage();
            return false;
        }
    }

    public function insert($sql, $user_id, $base_table_name) {
        $table_name = $this->getTableName($user_id, $base_table_name);
        $sql = str_replace($base_table_name, $table_name, $sql); // 替换表名

        $conn = $this->connect();

        try {
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            return $stmt->rowCount();
        } catch (PDOException $e) {
            echo "SQL Error: " . $e->getMessage();
            return false;
        }
    }
}

// 示例配置
$db_config = [
    'host' => '127.0.0.1',
    'dbname' => 'your_db',
    'username' => 'root',
    'password' => 'your_password',
    'charset' => 'utf8mb4'
];

$table_count = 4; // 分成4张表
$base_table_name = 'users'; // 原始表名

// 使用示例
$db = new ShardingDatabase($db_config, $table_count);

// 查询用户ID为123的用户数据
$results = $db->query("SELECT * FROM users WHERE id = 123", 123, $base_table_name);
print_r($results);

// 插入用户数据
$affected_rows = $db->insert("INSERT INTO users (name, email) VALUES ('New User', '[email protected]')", 456, $base_table_name);
echo "Affected rows: " . $affected_rows;
?>

代码解释：

  • ShardingDatabase 类封装了分表逻辑。
  • getTableName() 方法根据用户ID和表数量，计算出实际的表名。
  • query() 方法和 insert()方法 将SQL语句中的原始表名替换为实际的表名。
  • str_replace($base_table_name, $table_name, $sql); 用于替换SQL语句中的表名。

4. 分库分表的注意事项

  • 路由算法的选择： 选择合适的路由算法非常重要，需要根据业务场景和数据特点进行选择。
  • 数据迁移： 分库分表后，需要将原有数据迁移到新的数据库或表中。
  • 跨库查询： 跨多个数据库的查询，需要使用分布式查询或数据同步。
  • 全局ID： 分库分表后，需要生成全局唯一的ID，例如使用雪花算法。
  • 事务一致性： 跨多个数据库的事务，需要使用分布式事务来保证一致性。
  • 维护成本： 分库分表增加了维护成本，需要仔细规划和管理。

四、读写分离 + 分库分表：双剑合璧，天下无敌？

实际上，读写分离和分库分表可以结合使用，进一步提高数据库的性能和扩展性。

例如，可以先对数据库进行分库分表，然后对每个分库进行读写分离。

五、总结：选择适合你的方案！

读写分离和分库分表都是优化数据库性能的有效手段。但是，它们也增加了系统的复杂性。因此，在选择方案时，需要根据你的实际情况，权衡利弊，选择最适合你的方案。

特性 读写分离 分库分表 读写分离+分库分表
解决问题 读写压力大 数据量过大 读写压力大 & 数据量过大
实现方式 主从复制 根据路由算法分散数据 先分库分表，再对每个分库进行读写分离
优点 简单易实现，提高读性能 提高存储容量和查询性能 性能和扩展性最佳
缺点 可能存在数据延迟，写性能受限 配置和维护复杂，跨库查询困难 最复杂，需要考虑更多因素
适用场景 读多写少的应用，对数据实时性要求不高 数据量巨大，需要水平扩展的应用 大型互联网应用，需要高并发、大数据量的支持
关键技术 主从复制配置，读写路由策略 路由算法选择，数据迁移，全局ID生成，分布式事务 结合读写分离和分库分表的技术
代码复杂度 较低 较高 最高

希望今天的分享对你有所帮助！ 记住，没有银弹，只有最适合你的方案。在实际应用中，要根据你的业务需求和技术能力，选择合适的策略。

好了，今天的研讨会就到这里，谢谢大家！ 如果有什么问题，欢迎提问。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注