当一个表数据记录过大时就会出现性能瓶颈,而一般对应的解决办法是要么做分区表,要么分表,分区表就不说了,分表又分为垂直分割和水平分割,具体区 别请自行搜索。一般而言,分库分表属于水平分割,按照一定的规则将数据插入到不同的表中去。而分库则可以很方便的转移数据库的压力,比如将一个很大库的分 别放在不同的服务器上。
下面是我写的一个分库分表的实现:
下面这个例子展示了如何使用上述的 Model 类:
- <?php
- /**
- * User: guoyu
- * Date: 14-8-12
- * Time: 下午3:16
- */
- namespace App\Model\Database;
- class Config
- {
- public $dsn;
- public $user;
- public $password;
- /**
- * @var string 分库分表后得到的数据库名
- */
- public $dbname;
- /**
- * @var string 分库分表后得到的表名
- */
- public $table;
- /**
- * @var array MySQL 配置数组
- */
- private static $config;
- /**
- * @var string 配置文件路径
- */
- private static $configFile = 'mysql.php';
- public function __construct($dbname, $table, $id = 0)
- {
- if (is_null(static::$config)) {
- $config = include(static::$configFile);
- static::$config = $config;
- }
- $config = static::$config;
- if (isset($config['shared']) && isset($config['shared'][$dbname])) {
- $dbconfig = $config['shared'][$dbname];
- $id = is_numeric($id) ? (int)$id : crc32($id);
- $database_id = ($id / $dbconfig['database_split'][0]) % $dbconfig['database_split'][1];
- $table_id = ($id / $dbconfig['table_split'][0]) % $dbconfig['table_split'][1];
- foreach ($dbconfig['host'] as $key => $conf) {
- list($from, $to) = explode('-', $key);
- if ($from <= $database_id && $database_id <= $to) {
- $the_config = $conf;
- }
- }
- $this->dbname = $dbname . '_' . $database_id;
- $this->table = $table . '_' . $table_id;
- } else {
- $this->dbname = $dbname;
- $this->table = $table;
- $the_config = $config['db'][$dbname];
- }
- $c = $the_config;
- if (isset($c['unix_socket']) && $c['unix_socket']) {
- $this->dsn = sprintf('mysql:dbname=%s;unix_socket=%s', $this->dbname, $c['unix_socket']);
- } else {
- $this->dsn = sprintf('mysql:dbname=%s;host=%s;port=%s', $this->dbname, $c['host'], $c['port']);
- }
- $this->user = $c['user'];
- $this->password = $c['password'];
- }
- }
- Config 类就做一个事情,根据配置文件,去拿到对应的库和表的链接配置,然后客户可以根据 dsn 去链接对应的数据库。对应的配置文件如下:
- [php] view plaincopyprint?在CODE上查看代码片派生到我的代码片
- <?php
- /**
- * User: guoyu
- * Date: 14-8-6
- * Time: 上午11:19
- */
- $default = array(
- 'unix_socket' => null,
- 'host' => 'localhost',
- 'port' => '3306',
- 'user' => 'root',
- 'password' => '',
- );
- $config = array(
- // 不进行分库分表的数据库
- 'db' => array(
- 'my_site' => $default,
- ),
- // 分库分表
- 'shared' => array(
- 'user' => array(
- 'host' => array(
- /**
- * 编号为 0 到 10 的库使用的链接配置
- */
- '0-10' => $default,
- /**
- * 编号为 11 到 28 的库使用的链接配置
- */
- '11-28' => $default,
- /**
- * 编号为 29 到 99 的库使用的链接配置
- */
- '29-99' => $default,
- ),
- // 分库分表规则
- /**
- * 下面的配置对应百库百表
- * 如果根据 uid 进行分表,假设 uid 为 543234678,对应的库表为:
- * (543234678 / 1) % 100 = 78 为编号为 78 的库
- * (543234678 / 100) % 100 = 46 为编号为 46 的表
- */
- 'database_split' => array(1, 100),
- 'table_split' => array(100, 100),
- ),
- ),
- );
- return $config;
- 给出一个使用这个分库分表的例子:
- [php] view plaincopyprint?在CODE上查看代码片派生到我的代码片
- <?php
- /**
- * User: guoyu
- * Date: 14-8-6
- * Time: 上午10:23
- */
- namespace App\Model;
- use App\Model\Database\Config;
- use \PDO;
- abstract class Model
- {
- /**
- * @var Config
- */
- public $config;
- /**
- * @var PDO
- */
- public $connection;
- protected $dbnamePrefix;
- protected $tablePrefix;
- /**
- * @var string 分库分表后对应的表
- */
- protected $table;
- public function __construct($id)
- {
- $this->config = new Config($this->dbnamePrefix, $this->tablePrefix, $id);
- $this->connection = new Pdo($this->config->dsn, $this->config->user, $this->config->password);
- $this->table = $this->config->table;
- }
- public function update(array $data, array $where = array())
- {
- }
- public function select(array $where)
- {
- }
- public function insert(array $data)
- {
- }
- public function query($sql)
- {
- return $this->connection->query($sql);
- }
- }
来自:http://blog.csdn.net/phpfenghuo/article/details/45342311
- <?php
- /**
- * User: guoyu
- * Date: 14-8-12
- * Time: 下午4:06
- */
- require 'Config.php';
- require 'Model.php';
- use App\Model\Model;
- class User extends Model
- {
- protected $dbnamePrefix = 'user';
- protected $tablePrefix = 'userinfo';
- }
- $user = new User(4455345345);
- print_r($user);
来源: http://www.phpxs.com/code/1003139/