mysql分库分表实战及php代码操作完整实例_mysql教程_开发教程

当mysql单表达到几千万时，查询一次要很久，如果有联合查询，有可能会死在那

mysql分库分表主要就是解决这个问题，减小数据库的负担，缩短查询时间

分库：

1）按功能分

用户类库、商品类库、订单类库、日志类、统计类库...

1）按地区分

每个城市或省市一个同样的库，加上后缀或前缀如：db_click_bj、db_click_sh...

分表：

1、横向分表解决表记录太大问题

1）按某个字段分，

如：discuz的附件表分成10个附件分表pre_forum_attachment_0到pre_forum_attachment_9，还有1个附件索引表pre_forum_attachment存储tid和附件id关系

根据主题的tid最后一位来决定附件要保存在哪个分表，

2）按日期分表

一些日志、统计类的可以按年、月、日、周分表

如：点击量统计click_201601、click_201602

3）使用mysql的merge

先把分表创建好，然后创建总表指定engine= MERGE UNION=(table1,table2) INSERT_METHOD = LAST;

2、纵向分表解决列过多问题

1）经常组合查询的列放在一个表，常用字段的表可考虑Memory引擎

2）不经常使用的字段单独成表

3）把text、blob等大字段拆分放在附表

如：phpcms的文章表分成主表v9_news和从表v9_news_data，主表存标题、关键字、浏览量等，从表存具体内容、模板等

很多主流mvc框架都提供了切换数据库访问方法

thinkphp切换数据库

$this->db(1,"mysql://root:123456@localhost:3306/test")->query("查询sql");//数据库连接信息可放在配置文件

$this->db(1)->query("查询sql");//下次直接使用编号1定位

分表的话在查询前先根据分表规则把表名查出

这里用两台机子简单以同个业务库分库，同个表分表，演示插入、查询如何定位库和表并最终成功执行

两台机子：

server1：192.168.1.198

server2：192.168.1.199

两台机子都执行下面操作

1、先创建10个数据库，每个数据库10个表

当然也可以改成百库百表，也可手动创建，我为了方便写了个脚本批量创建

create.php

<?php

ini_set('memory_limit', '-1');

$con=mysql_connect("192.168.1.198","open","123456");

if($con){

for($i=0;$i<10;$i++){//10个库

$sql="drop database cloude_{$i};";//删库谨慎

mysql_query($sql);

$sql="create database cloude_{$i} default character set utf8 collate utf8_general_ci;";

$do=mysql_query($sql,$con)or die(mysql_error());

if($do){

mysql_select_db("cloude_{$i}",$con);

mysql_query("set name gtf8");

for($j=0;$j<10;$j++){ //10个表

$sql="drop table if exists user_{$j};";

mysql_query($sql);

$sql="create table user_{$j}

(

id char(36) not null primary key,

name char(15) not null default '',

password char(32) not null default '',

sex char(1) not null default '男'

)engine=InnoDB;";

$do=mysql_query($sql,$con) or die(mysql_error());

if($do){

//echo "create table user_{$j} successful! <br/>";

}else{

//echo "create error!";

}

}else{

echo "connect error!!!!";

}

2、分库分表路由实现

Config.php

<?php

class Config{

public $dsn;

public $user;

public $password;

public $dbname; //分库分表后得到的数据库名

public $table; //分库分表后得到的表名

private static $config;//mysql配置数组

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'];

}

3、数据库配置文件

mysql.php

<?php

$default = array(

'unix_socket' => null,

'host' => '192.168.1.198',

'port' => '3306',

'user' => 'open',

'password' => '123456',

);

$db_199 = array(

'unix_socket' => null,

'host' => '192.168.1.199',

'port' => '3306',

'user' => 'open',

'password' => '123456',

);

$config = array(

// 不进行分库分表的数据库

'db' => array(

'hadoop' => $default,

// 分库分表

'shared' => array(

'cloude' => array(

'host' => array(

/**

* 编号为 0 到 4 的库使用的链接配置

'0-4' => $default,

/**

* 编号为 5 到 9 的库使用的链接配置

'5-9' => $db_199,

// 分库分表规则

/**

* 下面的配置对应10库10表

* 如果根据 uid 进行分表，假设 uid 为 224，对应的库表为：

* (224 / 1) % 10 = 4 为编号为 4 的库

* (224 / 10) % 10 = 1 为编号为 2 的表

'database_split' => array(1, 10),

'table_split' => array(10, 10),

);

return $config;
(责任编辑：最模板)