mysql分表

标签: mysql | 发表时间:2015-07-03 23:24 | 作者:wsluozefeng
出处:http://www.iteye.com

第一种分表方法: 通过merge生成合并表的形式,该方法用于已经存在的大表需要分表的情况

举例情况:good表中有300万条数据,需要分成2个分表g1和g2

 

步骤1:备份good表,以免操作失败,用于恢复数据

 

步骤2:将good表中的数据分摊到分表中去:

              1.使用程序处理,

     2.在mysql中使用 create table g1 select * from good where ...., 需要注意字符,字段类型等问题

                     insert into g1 select * from good where .....,  需要先手动建表

 

步骤3:删除good表,通过merge重新创建合并表good,语句如下:

       CREATE TABLE good (

.....

       ) TYPE=MERGE UNION=(分表1,分表2) INSERT_METHOD=LAST;

       ps:INSERT_METHOD参数用于决定当向合并表中插入数据时候,数据真正插入的是哪个分表,last表示向最后一个分表插入数据,first则是向第一分表插入, 0表示不允许插入 

 

           创建合并表会碰到不少创建不成功的情况,需要看情况处理,碰到的一些情况:

      1.分表和主表的字段总数和类型需要一致,包括列名、顺序,UNION中的表必须同属一个DATABASE

     2.分表类型必须是MyISAM的

     3.合并表中的“索引字段”必须在分表中都存在

     4.定义在合并表中的索引没有任何作用,索引是由分表控制的,例如两个分表中存在着同样的一个索引,那么在MERGE表中会有两个一样的索引,也就说每个分表的索引在merge表中都会存在,所以在merge表中不能根据该索引进行唯一性检索

     5.可以通过修改.mrg文件(在mysql对应的数据库目录中)来修改MERGE表,每个基本表的名字占一行。注意:修改后要通过FLUSH TABLES刷新表缓存。

 

创建成功后,直接访问合并表即可

 

上面的方法的PHP实现代码如下:

$con = new PDO("mysql:host=localhost;dbname=test", 'root', 'ajia123');

$sql = "select count(*) from good";

$rel = $con->query($sql)->fetch();

$totalRow = isset($rel[0]) ? $rel[0] : 1;       //合并表的总记录数

 

$tablePerRow = 1000000;

$item        = ceil( $totalRow/$tablePerRow );  //每个分表的记录数

 

for( $i=1; $i<=$item; $i++){

 

        $tableName[] = "g{$i}";

 

//先创建分表,再插入记录数

        $sql = "CREATE TABLE `g{$i}` (

                          `id` int(11) NOT NULL DEFAULT '0',

                          `name` char(20) NOT NULL DEFAULT '',

                          `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

                          `the_date` int(8) DEFAULT '0'

                        ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

 

        $con->query($sql);

 

        $start = (($i-1)*$tablePerRow+1);

        $end   = ($i*$tablePerRow);

        $sql2  = "insert into g{$i} select * from good where id between {$start} and {$end}";

 

        $con->query($sql2);

}

 

//生成合并表

$allSql = "CREATE TABLE gg (

  `id` int(11) NOT NULL,

  `name` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  `the_date` int(8) DEFAULT '0'

) TYPE=MERGE UNION=(".implode(",", $tableName).") INSERT_METHOD=LAST;";

 

$con->query($allSql);



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [mysql] 推荐:

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

MySQL Replication 线程

- - CSDN博客推荐文章
Replication 线程. Mysql 的Replication 是一个异步的复制过程,从一个Mysql instace(我们称之为Master)复制到另一个Mysql instance(我们称之Slave). 在Master 与Slave 之间的实现整个复制过程主. 要由三个线程来完成,其中两个线程(Sql 线程和IO 线程)在Slave 端,另外一个线程(IO 线程)在Master 端.

mysql backup 脚本

- - ITeye博客
网上备份脚本很多,但考虑都不周全. 保证创建备份文件只能是创建者跟root可以访问,其他用户没有权限,保证了数据库备份的安全. 上面脚本是负责备份的份数管理,. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

Oracle MySQL Or NoSQL续

- - Sky.Jian 朝阳的天空
接前面一篇,这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来,也算是前面博文中PPT的一个文字版解读吧. Oracle,MySQL 还是 NoSQL. 随着阿里系的“去IOE”运动在社区的宣传声越来越大,国内正在掀起一股“去xxx”的技术潮. 不仅仅是互联网企业,包括运营商以及金融机构都已经开始加入到这个潮流之中.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

MySql动态SQL

- - SQL - 编程语言 - ITeye博客
13.7. 用于预处理语句的SQL语法. MySQL 5.1对服务器一方的预制语句提供支持. 如果您使用合适的客户端编程界面,则这种支持可以发挥在MySQL 4.1中实施的高效客户端/服务器二进制协议的优势. 候选界面包括MySQL C API客户端库(用于C程序)、MySQL Connector/J(用于Java程序)和MySQL Connector/NET.

MySQL 性能

- - 谁主沉浮
这里罗列了一些基本的 MySQL 性能提示,但不是放之四海而皆准,需要根据实际的应用情况而决定. 使用标准化设计(数据库三范式),记住表的联合查询(join)性能不会差. 选择合适的字符集,虽然UTF16无所不能,但需要两倍的存储;UTF8适合各种字符,但比latin1慢,尽可能选用latin1(此条不适合中文).

mysql explain 解析

- - SQL - 编程语言 - ITeye博客
Mysql Explain 详解. 例如: explain select * from t3 where id=3952602;. 二.explain输出解释. | id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |.

mysql profile使用

- - 数据库 - ITeye博客
mysql的sql语句优化都使用explain,但是这个没有办法知道详细的Memory/CPU等使用量. MySQL Query Profiler, 可以查询到此 SQL 语句会执行多少, 并看出 CPU/Memory 使用. 量, 执行过程 System lock, Table lock 花多少时间等等.

mysql tips两则

- - jackyrong
mysql tips两则,都可以用来查看当前执行的语句. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.