mysql简单数据库分表操作

存储架构 2018-02-24 阅读原文

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

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

责编内容by:CSDN博客阅读原文】。感谢您的支持!

您可能感兴趣的

Need help updating a mysql table by loading csv fi... Following is my script to upload the table employee with a csv file. The file is...
Timing load & index for sysbench tables This post compares MyRocks, InnoDB and TokuDB on the time required to load and i...
从MySQL到HBase:数据存储方案转型的演进... 本文大致会从以下几个方面入手,谈谈笔者对数据存储方案选型的看法: 从MySQL到HBase集群化方案的演化 MySQL与HBase的性能...
MozMEAO SRE Status Report – September 19, 20... Here’s what happened on the MozMEAO SRE team from September 5th - September 19th...
GitHub 的 MySQL 高可用性实践分享 GitHub 使用 MySQL 作为所有非 git 仓库数据的主要存储, 它的可用性对 GitHub 的访问操作至关重要。GitHub 站点本身、GitH...