mysql分表+查询

最近面试，问到关与mysql数据库的分表一些问题，如设计一个千万级别的会员数据库，当时想到了分表，但是当问到如何跟据username查询到用户的信息时，这时，有点卡住了，所以为了让自已重新认识分表，而记录下来。

垂直分表：

　　其实没啥好讲，就是 主键+常用列 放在原表中，再讲 主键+一些不常用列 放在另外的表中。

　　这样一个数据页就可以存放更多数据。 但是缺点也明显，可能会增加join 或 union之类的操作。

 

水平分表：

　　今天面试被问到水平分表，突然愣住了，分都知道，但分完如何有效查询就不好说了。

　　原则：具体情况具体分析。

　　常见几种分法：

　　　　1、按时间分

　　　　　　典型应用：新闻类、qq状态、朋友圈动态等关注实时或最近的，可以用时间划分，比如当月一张表，上个月一张表。

　　　　2、按区间分

　　　　　　通常每张表都会有个自增id，可以利用自增id分，比如

　　　　　　user1表 是1~50

　　　　　　user2表 是51~100      //insert 操作完成后，判断id值，超过50w时，创建新表

　　　　3、hash分表

　　　　　　实质上没啥意思，对每一条插入的数据进行取模， 对于单挑记录查询还ok，如果查询相邻几行数据时，就悲剧了。 感兴趣的同学可以看下面相关链接第一条。

 

　　分表后查询：

　　　　1、对于时间水平分表：

　　　　　　假设朋友圈状态表，每天都会产生20w条记录, 建表 table_20150401 存放。然后用cron跑一个脚本，每天晚上凌晨创建新表，比如 table_20150402。在php程序中

　　　　 执行插入的函数进行封装，使用下面这两个函数。　

insertData($data){
    $table = "table_".date("Ymd",time()); //生成当天表名
    insert($data,$table);  // 插入新的表中
}

GetData($condition){  
    //如果条件里面带了时间查找，比如:$condition[time] , 则分析出对应表名，选择对应一张或多张表
    $table = "table_".date("Ymd",time()); //生成当天表名
    Get($condition,$table);  // 在新的表中查找
}

　　　　2、对于id区间划分

　　　　　　新闻或朋友圈状态id 1~1000   1001~2000
            　　　　一、数据库里面建表 breakup_table   //这个表里专门记录，新分表和原表的 记录数，方便确定查找哪个表
            　　　　比如：   news_1  1   //起始key为1
                  　　　　　　news_2  1001  //起始key为1001
                  　　　　　　news_3  2001  

　　　　　　　　二、上述 news_1 之类的数据，第一次需要同数据库拿，之后可以放到session或memcached里面

 

insertUser($data){
　　 $table_num = getCurrentNewNum(); //这个值用memcached去包裹，if($count < 2001)return 3;elseif($count < 1001)return 2;else 1;
    $table = "table_".$table_num); //生成指定表名
    insert($data,$table);  // 插入新的表中
}

GetUser($condition){  
    $table_num = getCurrentNewNum();
    $table = "table_".$table_num); //生成指定表名
    Get($condition,$table);  // 在新的表中查找
}

 

　　　　3、对于hash分表查询：

　　　　　　　　典型user表 //单独拉出来讲，因为比较特别，第一次查找可能比较花时间，因为必须根据用户名确定去找哪个表
        　　　　　　　　可以这做：
                　　　　　　　　$md5_val = md5($user_name);  //用crc32()应该也可以，但未尝试，如果尝试记得%u，使其不为负
                　　　　　　　　$first_val = substr($md5_val, 0,1);//然后去取第一个值 
                　　　　　　　　$decimal = hexdec($first_val); //十六进制转十进制
                　　　　　　　　$table_num = $decimal%3 + 1; // 求余3，使得只有三张表，table_1,table_2,table_3
            　　　　　　　　　 主要原理，利用user_name唯一性，导出md5唯一性，然后求余限制分表数量

insertUser($data){

    $table = "table_".$table_num); //生成指定表名
    insert($data,$table);  // 插入新的表中
}

GetUser($condition){  
    
    $table = "table_".$table_num); //生成指定表名
    Get($condition,$table);  // 在新的表中查找
}

　　　　hash 分表也可用于id区间分表，即用id值 取模。

 

水平分表，典型缺点，对于group by或order by之类的查询是灾难。　　　　　　

 

 

相关链接：

　　http://www.phpddt.com/php/mysql-tables.html  //介绍了利用merge分表