分表分库方法总结

最新推荐文章于 2023-02-12 17:20:23 发布
最新推荐文章于 2023-02-12 17:20:23 发布
阅读量115 收藏
点赞数
文章标签： 数据库 php

案例一：

1，背景：一个地址薄的应用程序，设计的用户量为2亿，统计出每个用户的地址薄为30个左右，整个数据量为60亿，使用mysql数据库
计划分为：1000个表，100个库 

2，分库分表代码

    private function getDbNo($email)  
    {  
        $m = md5($email);  
        $n = hexdec(substr($m, 0, 16));  
        $tableNo = fmod($n, 1000);  
        $dbNo = $tableNo % 100;  
        return array($dbNo, $tableNo);  
    }

　　3，配合的连接访问代码

require_once 'Db/Config.php';

class Db_Adapter
{
    const MASTER    = 0;
    const SLAVE     = 1;

    private static $instances = array();

    private $conf = array();

    private $conns = array();

    private $conn = NULL;
    private $stmt = NULL;
    
    public function __construct($conf)
    {
        $this->conf = $conf;
    }

    public function execute($sql, $params)
    {
        $cmd = substr(strtolower(trim($sql)), 0, 6);
        if ($cmd == 'select') {
            $conn = $this->getConn(self::SLAVE);
        } else {
            $conn = $this->getConn(self::MASTER);
        }

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

        $this->conn = $conn;
        $this->stmt = $stmt;
    }

    public function fetch()
    {
        return $this->stmt->fetch();
    }

    public function fetchAll()
    {
        return $this->stmt->fetchAll();
    }

    public function lastInsertId($name = NULL)
    {
        return $this->conn->lastInsertId($name);
    }

    public function rowCount()
    {
        return $this->stmt->rowCount();
    }

    private function getConn($type)
    {
        if ($type == self::SLAVE && isset($this->conf[self::SLAVE])) {
            $id = 0;
        } else {
            $id = 1;
        }

        if (!isset($this->conns[$id])) {
            $conf = $this->conf[$id];
            $this->conns[$id] = new PDO(
                $conf['dsn'], $conf['user'], $conf['pass'],
                self::dbOptions);
        }

        return $this->conns[$id];
    }

    public static function getInstance($dbName, $dbNo = 0)
    {
        $key = $dbName . '_' . $dbNo;
        if (!isset(self::$instances[$key])) {
            $conf = Db_Config::getConfig($dbName, $dbNo); //连接配置参数
            self::$instances[$key] = new self($conf);
        }

        return self::$instances[$key];
    }
}

　　
4，潜在问题
如果某个表中的那些用户的地址薄联系人超多，如每个人1000个，则可能出现该表超大，需要把该表区分为子表，暂时没有配置中心来处理该情况。
(若真的出现该情况，在连接参数这个地方继续作一次hash)。

转于：http://hanyh.iteye.com/blog/431323

 

 案例二：

<?php
include 'config.php';
class Model{
        //用户名
        protected $user;
        //密码
        protected $pwd;
        //主机
        protected $host;
        //库名，是一个数组
        protected $dbName=array();
        //字符集
        protected $charset='utf8';
        //连接资源是一个数组
        protected $_link=array();
        //通用表名
        protected $tabName;
        //真实表名
        protected $trueTabName;
        //表前缀
        protected $prefix;
        //字段缓存
        protected $fields;
        //创建表的sql语句
        protected $createSql='CREATE TABLE IF NOT EXISTS __TABLENAME__(
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `username` char(15) NOT NULL,
  `password` char(32) NOT NULL,
  `createtime` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;';

//1,通过ID取余，得到真实表名    mod
//2,用户名截取前几位   substr
//3,md5                        md5
//4,不带分库分表        none

        protected $partition=array(
                'type'=>'md5',        
                
                'rule'=>1,
        
        );


        public function __construct($tabName=''){
                $this->user=DB_USER;
                $this->host=DB_HOST;
                $this->dbName[0]=DB_NAME;
                $this->charset=DB_CHARSET;
                $this->prefix=DB_PREFIX;
                $this->pwd=DB_PWD;

                if(empty($tabName)){
                //userModel
                //newModel
                        $this->tabName=$this->prefix.ucfirst(strtolower(substr(get_class($this),0,-5)));

                }else{
                        $this->tabName=$this->prefix.$tabName;
                }

                $this->_link[0]=$this->connect($this->host,$this->user,$this->pwd,$this->dbName,$this->charset);

        }

        public function connect($host,$user,$pwd,$dbName,$charset,$linkId=0){
                $conn=mysql_connect($host,$user,$pwd);

                
                if(mysql_errno()){
                        $this->error(-1,$conn);
                        return false;
                }
        

                if(!$this->selectDb($dbName[$linkId],$conn)){
                        $this->error(-2,$conn);
                        return false;        
                }
                
                if(!$this->setCharset($charset,$conn)){
                        $this->error(-3,$conn);
                        return false;
                }
        

                return $conn;



        }


        public function selectDb($dbName,$conn){
                if(mysql_select_db($dbName,$conn)){

                        return true;
                }else{
                        return false;
                }        
        }

        public function setCharset($charset,$conn){
                if(mysql_set_charset($charset,$conn)){
                        return true;
                }else{
                        return false;
                }

        }

        public function addServer($host,$user,$pwd,$dbName,$charset,$linkId){
                $this->dbName[$linkId]=$dbName;
                $this->_link[$linkId]=$this->connect($host,$user,$pwd,$dbName,$charset,$linkId);

        }

        public function getTrueTable($content,$linkId=0){
                switch($this->partition['type']){
                        case 'mod':
                                if(!is_int($content)){
                                        $this->error(-4);
                                        return false;
                                }
                                $string=$content%$this->partition['rule'];
                                break;
                        case 'substr':
                                $string=substr($content,0,$this->partition['rule']);
                                break;
                        case 'md5':
                                $string=substr(md5($content),0,$this->partition['rule']);
                                break;
                        case 'none':
                                $string=null;
                                break;
                }

                if(empty($string)){
                        $this->trueTableName=$this->tabName;

                }else{
                        $this->trueTableName=$this->tabName.'_'.$string;
                }

                //第一，判断表是否存在，存在返回表字段缓存
                //第二，不存在，则创建表，返回字段缓存

                        $this->existsTable($this->trueTableName,$linkId);


        }
        //表是否存在
        //是否缓存了字段

        protected function existsTable($tableName,$linkId=0){
                $database=$this->dbName[$linkId];
                $sql='select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`=\''.$database.'\' and `TABLE_NAME`=\''.$tableName.'\'';
        
                if($this->execute($sql,$linkId)){
                        //表存在
                        if(file_exists('cache/'.md5($this->tabName).'.php')){
                                $this->fields=include 'cache/'.md5($this->tabName).'.php';
                        }else{
                                //暂时留着不写，待会来写
                                $this->fields=$this->getFieldCache($linkId);
                        }

                }else{
                        //表不存在
                        $this->createTable($this->trueTableName,$linkId);
                        $this->fields=$this->getFieldCache($linkId);

                }

        }

        protected function getFieldCache($linkId=0){
                if(file_exists('cache/'.md5($this->tabName).'.php')){
                        $fields=include 'cache/'.md5($this->tabName).'.php';
                        return $fields;        
                }
                $sql="desc $this->trueTableName";
                $f=$this->query($sql,$linkId);
                
                $fields=$this->writeFields($f);

                return $fields;
                

        }

        protected function writeFields($f){
                foreach($f as $key=>$value){
                        $fields[]=$value['Field'];
                        
                        if($value['Key']=='PRI'){
                                $fields['_pk']=$value['Field'];
                        }
                        if($value['Extra']=='auto_increment'){
                                $fields['_auto']=$value['Field'];
                        }
                }
                $string="<?php \n return ".var_export($fields,true)."\n?>";
                
                file_put_contents('cache/'.md5($this->tabName).'.php',$string);
                return $fields;
                        
        }

        protected function createTable($tabName,$linkId=0){
                $sql=str_replace('__TABLENAME__',$tabName,$this->createSql);
        
                $this->execute($sql,$linkId);
        }

        //不需要返回结果集我用execute方法
        public function  execute($sql,$linkId=0){
                $conn=$this->_link[$linkId];
        
                $result=mysql_query($sql,$this->_link[$linkId]);
                if($result&&mysql_affected_rows()){
                
                        return mysql_affected_rows();
                }else{
                        return false;
                }

        }


        //需要返回结果集我用query方法
        public function query($sql,$linkId=0){
                $result=mysql_query($sql,$this->_link[$linkId]);
                
                if($result&&mysql_affected_rows()){
                        while($row=mysql_fetch_assoc($result)){

                                $rows[]=$row;
                        }
                }else{
                        return false;
                }
                return $rows;
        }


        public function error($num,$conn){
                switch($num){
                        case -1:
                                $string='连接数据库服务器失败'.mysql_error($conn);
                                break;
                        case -2:
                                $string='选择数据失败';
                                break;
                        case -3:
                                $string='设置字符集失败';
                                break;
                        case -4:
                                $string='数据库路由时选择的是取余，传入的不是整型';
                                break;
                }
        }

        


        //查最大值
        public function max($field,$linkId=0){
                if(!in_array($field,$this->fields)){
                        return false;
                }
                $sql="select max($field) as re from $this->trueTableName";
                $result=$this->query($sql,$linkId);
                $row=$result['re'];
                return $row;
                

        }        

        

        //查最小值
        public function min($field,$linkId=0){
                if(!in_array($field,$this->fields)){
                        return false;
                }
                $sql="select min($field) as re from $this->trueTableName";
                $result=$this->query($sql,$linkId);
                $row=$result['re'];
                return $row;
                

        }
        //求和
        public function sum($field,$linkId=0){
                if(!in_array($field,$this->fields)){
                        return false;
                }
                $sql="select sum($field) as re from $this->trueTableName";
                $result=$this->query($sql,$linkId);
                $row=$result['re'];
                return $row;
                

        }
        //最平均数
        public function avg($field,$linkId=0){
                if(!in_array($field,$this->fields)){
                        return false;
                }
                $sql="select avg($field) as re from $this->trueTableName";
                $result=$this->query($sql,$linkId);
                $row=$result['re'];
                return $row;
                

        }
        //求总数
        public function count($field='',$linkId=0){
                if(empty($field)){
                        $field=$this->fields['_pk'];
                }
                $sql="select count($field) as re from $this->trueTableName";
                $result=$this->query($sql,$linkId);
                $row=$result['re'];
                return $row;
        }
        //
        //删除
        public function delete($data,$where='',$linkId=0,$order='',$limit=''){
                //delete from 表  where 字段  order by  字段 limit 
                
                if(is_array($data)){
                        $value=join(',',$data);
                }else{
                        $value=(int)$data;
                }
                $fields=$this->fields['_pk'];

                if(empty($where)){

                        $sql="delete from $this->trueTableName where $fields in ($value)";
                }else{
                        $where='where '.$where;
                        if(!empty($order)){
                                $order='order by '.$order;        
                        }
                        if(!empty($limit)){
                                $limit='limit '.$limit;        
                        }

                        $sql="delete from $this->trueTableName $where $order $limit";
                }
                return $this->execute($sql,$linkId);
        }
        //
        //修改
        public function save($data,$where,$linkId=0,$order='',$limit=''){

                //update 表  set 字段=值,字段=值 where 条件 order  limit
                $key=array_keys($data);
                $newKey=array_intersect($key,$this->fields);
        
                foreach($data as $key=>$value){
                        if(!in_array($key,$newKey))
                                continue;
                        $update.=$key.'="'.$value.'",';
                
                }
                $update=rtrim($update,',');
                
                if(!empty($order)){
                        $order='order by '.$order;
                }
                if(!empty($limit)){
                        $limit='limit '.$limit;
                }

                if(!empty($where)){
                        $where='where '.$where;
                }


                $sql="update $this->trueTableName set $update $where $order $limit";

                echo $sql;
                $result=$this->execute($sql,$linkId);
                return $result;

        }



        //增加
        public function add($data,$linkId=0){
                //insert into 表(字段) values(值)
                $key=array_keys($data);
                $newKey=array_intersect($key,$this->fields);
                foreach($data as $key=>$value){
                        if(!in_array($key,$newKey))
                                continue;
                        $values.="'".$value."',";
                }
                $values=trim($values,',');
                $fields=join(',',$newKey);
                $sql="insert into $this->trueTableName($fields) values($values)";
                echo $sql;
                $result=$this->execute($sql,$linkId);
                return $result;
        }


        //单条查询
        public function find($linkId=0,$where='',$order=''){
                //select * from 表 where  order  limit 1
                $field=join(',',$this->fields);
                if(!empty($where)){
                        $where='where '.$where;
                }
                if(!empty($order)){
                        $order='order by '.$order;
                }
                $sql="select $field from $this->trueTableName $where $order limit 1";
                $result=$this->query($sql,$linkId);
                return $result[0];
                
        }

        //多条查询
        public function select($field='',$linkId=0,$where='',$order='',$limit=''){
                //select * from 表 where  order  limit
                if(empty($field)){
                        $fields=join(',',$this->fields);
                }else{
                        if(is_array($field)){
                                $newKey=array_intersect($field,$this->fields);
                                $fields=implode(',',$newKey);
                        }else{
                                $fields=$field;
                        }
                }
                if(!empty($where)){
                        $where='where '.$where;
                }
                if(!empty($order)){
                        $order='order by '.$order;
                }
                if(!empty($limit)){
                        $limit='limit '.$limit;
                }
                $sql="select $fields from $this->trueTableName $where $order $limit";
                $result=$this->query($sql,$linkId);
                return $result;
                
        }
        //按照字段来查询数据


        function __call($name,$param){
                $key=substr($name,0,5);
                if(strtolower($key)=='getby'){
                        $field=strtolower(substr($name,5));
                
                        if(!in_array($field,$this->fields)){
                                return false;
                        }
                        $f=join(',',$this->fields);
                        $value=$param[0];
                        $sql="select $f  from $this->trueTableName where $field='$value'";
        
                        $result=$this->query($sql);
                        return $result[0];

                }
        }



}

?>

　　

 案例三：1亿条数据在PHP中实现Mysql数据库分表100张

首先创建100张表：

$i=0;
while($i<=99){
echo "$newNumber \r\n";
$sql="CREATE TABLE `code_".$i."` (
 `full_code` char(10) NOT NULL,
 `create_time` int(10) unsigned NOT NULL,
 PRIMARY KEY  (`full_code`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8";
mysql_query($sql);
$i++;

 full_code作为主键，我们对full_code做hash

$table_name=get_hash_table('code',$full_code);

function get_hash_table($table,$code,$s=100){
$hash = sprintf("%u", crc32($code));
echo $hash;
$hash1 = intval(fmod($hash, $s));
 return $table."_".$hash1;
}

 最后我们使用merge存储引擎来实现一张完整的code表

CREATE TABLE IF NOT EXISTS `code` (  
`full_code` char(10) NOT NULL,
`create_time` int(10) unsigned NOT NULL,
INDEX(full_code)  
) TYPE=MERGE UNION=(code_0,code_1,code_2.......) INSERT_METHOD=LAST ;

 这样我们通过select * from code就可以得到所有的full_code数据了。

weixin_34212762
关注
从AbstractRoutingDataSource说分库分表实现
weixin_33936401的博客
08-26 134
2019独角兽企业重金招聘Python工程师标准>>> ...
从AbstractRoutingDataSource说分库分表实现 路由规则
superswang的博客
03-08 3582
本文转自  http://wely.iteye.com/blog/2275725 很多人不知分库分表怎么实现，可能是把它想得复杂了。事实上，我们将复杂的事情分工后就简单了。如果仅仅是单库分表，那直接在代码中根据分表的维度得到表名后缀，如“0001”，然后比如在mybatis下，sql语句就可以这么写“select * from user_#tbIndex#”。程序中我们能
大数据存储之分表分库
weixin_33971977的博客
05-06 159
2019独角兽企业重金招聘Python工程师标准>>> ...
数据库分库分表的总结
击水三千里的专栏
02-16 575
（1）为什么要分库分表？（设计高并发系统的时候，数据库层面该如何设计？） 分表是啥意思？就是把一个表的数据放到多个表中，然后查询的时候你就查一个表。比如按照用户id来分表，将一个用户的数据就放在一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以控制每个表的数据量在可控的范围内，比如每个表就固定在200万以内。 分库是啥意思？就是你一个库一般我们经验而言，最多支撑到并发200...
005 分库分表_多数据源切换读写分离实战
诸般世界
10-26 727
1.数据库层面Mysql（MariaDB），实现读写分离、主从切换、数据源切换： 首先实现读写分离，就意味着需要有两个数据源，当写操作的时候对主库（master）使用，当读操作的时候对从库（slave）使用。那么在启动数据库连接池的时候即要启动两个，在实际使用时可以在方法上加上自定义注解的形式来区分读写。 2.实现解析： （1）配置好两个druid数据源，然后要区分开两个数据源...
MySQL分库分表总结讲解
09-09
MySQL分库分表是一种数据库优化策略，用于应对大数据量带来的性能瓶颈。随着业务的发展，单个数据库中的数据量会逐渐增大，导致查询速度下降，甚至影响到应用的正常运行。此时，通过分库分表，将单一的数据库或表...
MyBatis实现Mysql数据库分库分表操作和总结（推荐）
08-29
MyBatis实现Mysql数据库分库分表操作和总结 MyBatis是一款流行的持久层框架，能够与Mysql数据库进行交互。在大规模的应用系统中，数据库的性能和可扩展性变得非常重要。为了解决这个问题，需要实现数据库的分库分表...
超大数据量存储常用数据库分表分库算法总结
09-10
在应对超大数据量存储时，数据库分表分库是一种有效的解决方案，它能够提高查询效率，增强系统的稳定性。本文主要介绍了三种常见的分表分库算法，并探讨了分表带来的问题及选择算法的考量因素。 首先，按照自然时间...
分库分表总结
05-29
为了解决这一问题，"分库分表"成为了一种常见的优化策略。本文将深入探讨分库分表的概念、原理以及实际应用中的注意事项。 一、分库分表简介 分库分表，顾名思义，就是将一个大的数据库分解为多个较小的数据库或表...
sharding-jdbc之——分库分表实例完整源码
02-25
sharding-jdbc之——分库分表实例完整源码，详情请参见博文：http://blog.csdn.net/l1028386804/article/details/79368021
分库分表，多数据源的切换
06-16
通过使用sharding-jdbc分库分表，以及多数据源的切换
MYSQL数据库数据拆分之分库分表总结
心疼笔记本
06-05 523
这篇文章主要介绍了MYSQL数据库数据拆分之分库分表总结,需要的朋友可以参考下 数据存储演进思路一：单库单表 单库单表是最常见的数据库设计，例如，有一张用户(user)表放在数据库db中，所有的用户都可以在db库中的user表中查到。 数据存储演进思路二：单库多表 随着用户数量的增加，user表的数据量会越来越大，当数据量达到一定程度的时候对user表的查询会渐渐的变慢，从而影响整
水平分表、分库和垂直分表、分库和公共表的代码实现和讲解
杨杨吖的博客
01-15 1300
水平分表、分库和垂直分表、分库和公共表的代码实现和讲解
【Java】分库分表 项目实战最简单逻辑 全代码 （个人梳理）
博桑根本提不起劲的博客
01-23 1592
【Java】分库分表 最简单的判断逻辑 全代码 （个人梳理） 需求：根据ID首位奇偶性分库 根据ID末位奇偶性分表 文章目录【Java】分库分表 最简单的判断逻辑 全代码 （个人梳理）前言一、分库分表前提：配置多数据源二、在DAO层做分库分表逻辑判断1.ShardDao2.抒写Dao层实现类提供Dao层接口提供数据库PO类Service层提供个接口ServiceImpl调用一下Controller层调service总结此文章版权声明写给读此文章的你 前言 现在网上用中间件MyCat,Sharding
分布式或者springboot+mybatis集成sharding实现分库分表的代码实现详细过程。
weixin_42562259的博客
08-23 2596
使用sharding在spring框架中结合mybatis对数据进行分库分表处理，根据业务逻辑实现分库分表增加查询效率。
springboot自定义分库分表插件+代码
爆裂无球的程序空间
05-02 5421
springboot分库分表 每天多学一点点~ 博主最近遇到订单需求，在公司大牛指导下，自己写了个分库分表，在这里记录一下，如有不足之处，欢迎各位指出。 话不多说，这就开始吧… 文章目录springboot分库分表1.springboot配置多个数据源2.分库分表 策略模式3.项目启动流程4.运行结果5.总结+源码6.存在的问题7.结语 1.springboot配置多个数据源 springboo...
【Java开发笔记】分库分表
最新发布
见见大魔王
02-12 1934
为什么要分库分表？
分库分表的原理和实施
qq_40419080的博客
03-25 1387
文章目录一、分库分表背景1 什么是分库分表？2 为什么要分库分表？从性能上看从可用性上看二 、如何分库分表1 分库？分表？还是既分库又分表？2 如何选择我们自己的切分方案？3 如何对数据进行切分？（1）水平切分（2）垂直切分三 、分库分表之后带来的新问题1 分库分表后，如何让数据均匀散落在各个分库分表内？2 分库分表环境下，如何解决分库后主键ID的唯一性问题？3 分库分表环境下，事务问题怎么解决？4 分库分表后，历史数据如何平滑迁移？5 TDDL配置分库分表路由时的注意事项四 、分库分表
Sharding-JDBC分表分库实践与配置详解
总结以上知识点，Sharding-JDBC作为一款在应用层面实现分表分库的中间件，通过提供丰富的策略和易用的接口，极大地简化了分布式数据库架构的使用门槛，使得开发者能够更加专注于业务逻辑的实现，同时确保了系统的高...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值