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