在网上找了很多mysql分表的做法,都大同小异,天下文章一大抄,哈哈,今天我也来抄一抄,不过我是经过实战操作的。
参考的是这里 https://www.cnblogs.com/lucky-man/p/6207873.html。我的做法是:
水平分表
1,假设现在是存储用户聊天记录(哈哈,还是抄的上面链接同志的) 预先建了3个表 然后根据用户uid取模来判定将聊天记录存放在哪个表中:
CREATE TABLE `msg_0` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`content` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
CREATE TABLE `msg_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`content` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
CREATE TABLE `msg_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`content` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
2,php 代码
<?php
header("Content-type=text/html;charset=utf8");
$link = mysql_connect("localhost",'root','111111');
if (!$link){
die("数据库连接失败");
}
mysql_select_db("test");
//水平分表
$uid = mt_rand(1,100);//模拟用户uid
$yu = $uid%3; //分了3个表 所以是%3来取模
$table = "msg_".$yu; //判断是哪个表
$content = "hello word_".$table;
//插入数据
$sql="insert into ".$table."(uid,content) values(".$uid.",'".$content."')";
$re = mysql_query($sql);
//查询数据
$s = "select * from ".$table;
$re = mysql_query($s);
while ($row = mysql_fetch_array($re,MYSQL_ASSOC )){
ps($row);
}
function ps($str){
echo "<pre>";
print_r($str);
}
function pse($str){
echo "<pre>";
print_r($str);
exit;
}
?>
就是上面那么多内容,水平分表真的就这么简单么?额,反正在网上找的差不多讲的都是这个意思。还有一种是利用mysql自身的merge引擎分表,跟我这个 差不多,自己查查吧!
垂直分表
垂直分表就是将一个表中不经常用到的,或者像text这种类型比较大的字段挪到一个表中,其他常用的字段单独一个表,
如:现在有个user表:如下,有很多字段,现在分成两个表,一个user1用了存放用户主要的属性字段,另一个表user2,用了存放各种时间相关的字段
mysql> desc user;
+----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| group_id | int(10) unsigned | NO | | 0 | |
| username | varchar(32) | NO | MUL | | |
| nickname | varchar(50) | NO | MUL | | |
| password | varchar(32) | NO | | | |
| salt | varchar(30) | NO | | | |
| email | varchar(100) | NO | MUL | | |
| mobile | varchar(11) | NO | MUL | | |
| avatar | varchar(255) | NO | | | |
| birthday | date | NO | | NULL | |
| prevtime | int(10) unsigned | NO | | 0 | |
| logintime | int(10) unsigned | NO | | 0 | |
| loginip | varchar(50) | NO | | | |
| loginfailure | tinyint(1) unsigned | NO | | 0 | |
| joinip | varchar(50) | NO | | | |
| jointime | int(10) unsigned | NO | | 0 | |
| createtime | int(10) unsigned | NO | | 0 | |
| updatetime | int(10) unsigned | NO | | 0 | |
mysql> desc user1;
+----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| group_id | int(10) unsigned | NO | | 0 | |
| username | varchar(32) | NO | MUL | | |
| nickname | varchar(50) | NO | MUL | | |
| password | varchar(32) | NO | | | |
| salt | varchar(30) | NO | | | |
| email | varchar(100) | NO | MUL | | |
| mobile | varchar(11) | NO | MUL | | |
| avatar | varchar(255) | NO | | | |
| birthday | date | NO | | NULL | |
mysql> desc user2;
+----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+----------------+
| uid | int(10) unsigned | NO | | |
| prevtime | int(10) unsigned | NO | | 0 | |
| logintime | int(10) unsigned | NO | | 0 | |
| loginip | varchar(50) | NO | | | |
| loginfailure | tinyint(1) unsigned | NO | | 0 | |
| joinip | varchar(50) | NO | | | |
| jointime | int(10) unsigned | NO | | 0 | |
| createtime | int(10) unsigned | NO | | 0 | |
| updatetime | int(10) unsigned | NO | | 0 | |
表user2中通过uid来关联user1表