最近公司的某一表单因为数据量有点小大,经常因为那个啥全表查询操作产生内存溢出,简单来说,就是数据库经常罢工,然后嘞就打算分一下表。具体的那些高大上的理论知识咱是说不来。就简单为大家介绍一下本人所做的分表的一个思路哈。
首先嘞,考虑一下现实因素,我们公司这边呢,数据库是跟不上如此大的查询操作,就算是查询优化,也还是避免不了业务需求。有的时候他就是需要全表扫描,来做数据分析,或者优化处理。所以嘞,就只能分表了。但,这里面也有讲究。我这边只查询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')<br>"; 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')<br>"; 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')<br>"; 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')<br>"; 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')<br>"; 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')<br>"; 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')<br>"; 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')<br>"; 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')<br>"; 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')<br>"; 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); }