mysql简单数据库分表操作

2018-03-01 11:08:10来源:网络收集作者:ku9158人点击

分享
最近公司的某一表单因为数据量有点小大,经常因为那个啥全表查询操作产生内存溢出,简单来说,就是数据库经常罢工,然后嘞就打算分一下表。具体的那些高大上的理论知识咱是说不来。就简单为大家介绍一下本人所做的分表的一个思路哈。
首先嘞,考虑一下现实因素,我们公司这边呢,数据库是跟不上如此大的查询操作,就算是查询优化,也还是避免不了业务需求。有的时候他就是需要全表扫描,来做数据分析,或者优化处理。所以嘞,就只能分表了。但,这里面也有讲究。我这边只查询ID,然后进行了一下全表扫描,但是嘞,很不幸的事,内存溢出。服务器就这么干脆的挂了。。。
所以累,就只能考虑横向切分了。以下,是本人的具体方案。
首先嘞,以之前的表单作为主表,然后嘞,自己写脚本来从主表中取出数据,插入到分表中。(注:分表的数据结构与主表的数据结构要保持完全一致(看需求,AUTO_INCREMENT=11064 像这东西,我们这边是没有什么必要))
下面是本人的脚本。本人是根据主表中的数据ID来进行取余查询的。假设要划分为N张表,那么计算公式为ID%N=S。这个S值就是用来判断你要插入到那张表中的。相信大家差不多都能理解了。接下来附上代码(声明:其实还有好多中实现方案,本人只是提出最简单的一种,充其量小白水平。各位看官勿喷哈。。。):
$sentence_query = mysqli_query($link,"select * from sentence limit 100");// order by id desc limit 1
while ($sentence_data = mysqli_fetch_assoc($sentence_query)) {
$id = $sentence_data['id'];
$name = $sentence_data['name'];
$industry = $sentence_data['industry'];
$position = $sentence_data['position'];
$status = $sentence_data['status'];
$times = $sentence_data['times'];
$numbers = $sentence_data['numbers'];
$id_num = $id%10;
if($id_num == 0) {
echo "insert into sentence1(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence1(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 1) {
echo "insert into sentence2(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence2(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 2) {
echo "insert into sentence3(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence3(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 3) {
echo "insert into sentence4(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence4(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 4) {
echo "insert into sentence5(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence5(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 5) {
echo "insert into sentence6(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence6(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 6) {
echo "insert into sentence7(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence7(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 7) {
echo "insert into sentence8(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence8(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 8) {
echo "insert into sentence9(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence9(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}else if($id_num == 9) {
echo "insert into sentence10(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')
";
mysqli_query($link,"insert into sentence10(name,industry,position,status,times,numbers) value('$name','$industry','$position','$status','$times','$numbers')");
}
mysqli_query($link,"delete from sentence where id = ".$id);
}

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台