mysql简单数据库分表操作

  最近公司的某一表单因为数据量有点小大，经常因为那个啥全表查询操作产生内存溢出，简单来说，就是数据库经常罢工，然后嘞就打算分一下表。具体的那些高大上的理论知识咱是说不来。就简单为大家介绍一下本人所做的分表的一个思路哈。

  首先嘞，考虑一下现实因素，我们公司这边呢，数据库是跟不上如此大的查询操作，就算是查询优化，也还是避免不了业务需求。有的时候他就是需要全表扫描，来做数据分析，或者优化处理。所以嘞，就只能分表了。但，这里面也有讲究。我这边只查询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);
}