当单表达到几千万时,查询一次要很久,如果有联合查询,有可能会死在那
分库分表主要就是解决这个问题,减小数据库的负担,缩短查询时间
分库:
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++){
- $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++){
- $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){
-
- }else{
-
- }
- }
- }
- }
- }else{
- echo "connect error!!!!";
- }
2、分库分表路由实现
Config.PHP
- <?php
- class Config{
- public $dsn;
- public $user;
- public $password;
- public $dbname;
- public $table;
- private static $config;
- 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' => $default,
-
-
-
- '5-9' => $db_199,
-
- ),
-
-
-
-
-
-
-
-
- 'database_split' => array(1, 10),
- 'table_split' => array(10, 10),
- ),
- ),
- );
- return $config;
4、模型类操作数据库
Model.php
- <?php
- require_once 'Config.php';
- class Model{
- public $config;
- public $connection;
- protected $dbnamePrefix;
- protected $tablePrefix;
- protected $dbname;
- 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->connection->exec("set names utf8");
- $this->dbname = $this->config->dbname;
- $this->table = $this->config->table;
- }
-
- public function update(array $data, array $where = array()){
-
- }
-
- public function select(array $condition){
- $sqlwhere='';
- if(!empty($condition)){
- foreach ($condition as $field => $value) {
- $where[] = '`'.$field.'`='."'".addslashes($value)."'";
- }
- $sqlwhere .= ' '.implode(' and ', $where);
- }
- $sql="select * from ".$this->dbname.'.'.$this->table;
- if($sqlwhere){
- $sql.=" where $sqlwhere";
- }
- $res=$this->connection->query($sql);
- $data['data']=$res->fetchAll(PDO::FETCH_ASSOC);
- $data['info']=array("dsn"=>$this->config->dsn,"dbname"=>$this->dbname,"table"=>$this->table,"sql"=>$sql);
- return $data;
- }
- public function insert(array $arrData) {
- $name = $values = '';
- $flag = $flagV = 1;
- $true = is_array( current($arrData) );
- if($true) {
-
- foreach($arrData as $arr) {
- $values .= $flag ? '(' : ',(';
- foreach($arr as $key => $value) {
- if($flagV) {
- if($flag) $name .= "$key";
- $values .= "'$value'";
- $flagV = 0;
- } else {
- if($flag) $name .= ",$key";
- $values .= ",'$value'";
- }
- }
- $values .= ') ';
- $flag = 0;
- $flagV = 1;
- }
- } else {
-
- foreach($arrData as $key => $value) {
- if($flagV) {
- $name = "$key";
- $values = "('$value'";
- $flagV = 0;
- } else {
- $name .= ",$key";
- $values .= ",'$value'";
- }
- }
- $values .= ") ";
- }
-
- $sql = "insert into ".$this->dbname.'.'.$this->table." ($name) values $values";
- if( ($rs = $this->connection->exec($sql) ) > 0 ) {
- return array("dsn"=>$this->config->dsn,"dbname"=>$this->dbname,"table"=>$this->table,"sql"=>$sql);
- }
- return false;
- }
- public function query($sql){
- return $this->connection->query($sql);
- }
- }
5、测试
使用主键id作为分表字段,那最好就不要使用自增了,可使用uuid
User.php
- <?php
- require 'Config.php';
- require 'Model.php';
- class User extends Model
- {
- protected $dbnamePrefix = 'cloude';
- protected $tablePrefix = 'user';
- }
-
- function create_uuid($prefix = ""){
- $str = md5(uniqid(mt_rand(), true));
- $uuid = substr($str,0,8) . '-';
- $uuid .= substr($str,8,4) . '-';
- $uuid .= substr($str,12,4) . '-';
- $uuid .= substr($str,16,4) . '-';
- $uuid .= substr($str,20,12);
- return $prefix . $uuid;
- }
-
- $userId=create_uuid();
- $user = new User($userId);
- $data=array('id'=>$userId,'name'=>'大明'.$userId,'password'=>'14e1b600b1fd579f47433b88e8d85291','sex'=>'男');
- if($result=$user->insert($data)){
- echo '插入成功:','<pre/>';
- print_r($result);
- }
-
- $condition=array("id"=>$userId);
- $list=$user->select($condition);
- if($list){
- echo '查询成功:','<pre/>';
- print_r($list);
- }
6、结果
插入成功会返回插入到哪个库哪个表,查询成功返回从哪个库哪个表查的
分库分表注意事项:
1、维度问题
假如用户购买了商品,需 要将交易记录保存取来,如果按照用户的纬度分表,则每个用户的交易记录都保存在同一表中,所以很快很方便的查找到某用户的购买情况,但是某商品被购买的情 况则很有可能分布在多张表中,查找起来比较麻烦。反之,按照商品维度分表,可以很方便的查找到此商品的购买情况,但要查找到买人的交易记录比较麻烦。
所以常见的解决方式有:
通过扫表的方式解决,此方法基本不可能,效率太低了。
记录两份数据,一份按照用户纬度分表,一份按照商品维度分表。
通过搜索引擎解决,但如果实时性要求很高,又得关系到实时搜索
2、避免分表join操作 因为关联的表有可能不在同一数据库中
3、避免跨库事务
避免在一个事务中修改db0中的表的时候同时修改db1中的表,一个是操作起来更复杂,效率也会有一定影响
4、分表宜多不宜少;这样做主要是为了尽量避免后期可能遇到的二次拆分
5、尽量把同一组数据放到同一DB服务器上
例如将卖家a的商品和交易信息都放到db0中,当db1挂了的时候,卖家a相关的东西可以正常使用。也就是说避免数据库中的数据依赖另一数据库中的数据
文章来源http://blog.csdn.net/nuli888/article/details/52143065