mysql分表

MySQL高级特性-合并表

MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。构成一个MERGE数据表结构的各成员MyISAM数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。

create database testdb;
show databases;
use testdb;

create table test1( id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
) engine=myisam default charset=utf8 auto_increment=1;

create table test2 like test1

show tables;

# 指令告诉MySQL把所有的INSERT语句都发送到合并表的最后一个表上。定义FIRST或LAST是控制插入数据位置的唯一方式（但是也可以直接插入到下属表中）。
create table test_main( id bigint primary key,
name varchar(20),
sex tinyint not null default '0'
) engine=merge union(test1,test2) insert_method=last charset=utf8;


create table test_main2( id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
)  engine=merge union(test1,test2) insert_method=last charset=utf8 auto_increment=1;

# 插入两条数据到test1
insert into test1(name, sex) values('a1',1);
insert into test1(name, sex) values('a2',2);

# 插入两条数据到test2
insert into test2(name, sex) values('b1',1);
insert into test2(name, sex) values('b2',1);

select * from test1;
select * from test2;
select * from test_main;
select * from test_main2;

# 从主表插入，是插入到test2
insert into test_main2(name, sex) values('b4',1); 

#对其它表没影响
drop table test_main;

#加入新表
create table test3 like test1;

#重建主表
create table test_main( id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
)  engine=merge union(test1,test2,test3) insert_method=last charset=utf8 auto_increment=1;

# 从test_main主表插入，是插入到test3
insert into test_main(name, sex) values('b5',1); 


#换个引擎
create table test_main3( id bigint auto_increment primary key,
name varchar(20),
sex tinyint not null default '0'
)  engine=innodb union(test1,test2,test3) insert_method=last charset=utf8 auto_increment=1;

#查不到任何内容。只能是MyISAM引擎
select * from test_main3;

合并表对性能的影响
MySQL对合并表的实现对性能有一些重要的影响。和其他MySQL特性一样，它在某些条件下性能会更好。下面是关于它的一些注意事项：

1) 合并表比含有同样数据的非合并表需要更多的文件描述符。尽管合并表看上去是一个表，它实际是逐个打开了下属表。这样的结果就是单个表的缓存可以创建许多文件描述符。因此，即使已经配置了表的缓存，让服务器线程的文件描述符数量不要超过操作系统的限制，合并表仍然有可能导致超过这一限制。

2) 创建合并表的CREATE语句不会检查下属表是否是兼容的。如果下属表的定义有轻微的不一样，MySQL会创建合并表，但是却无法使用。同样，如果在创建了一个有效的合并表之后对某个下属表进行了改变，它也会无法工作，并且会显示下面的错误信息："ERROR 1168（HY000）：无法打开定义不同的下属表，或者非MyISAM表，或者不存在的表"。

3)访问合并表的查询访问了每一个下属表。这也许会使单行键查找比单个表慢。在合并表中限制下属表是一个好主意，尤其是它是联接中的第二个或以后的表。每次操作访问的数据越少，那么访问每个表的开销相对于整个操作而言就越重要。下面是一些如何使用合并表的注意事项：

4)范围查找受访问所有下属表的开销的影响小于单个查找。

对索引表的表扫描和对单个表一样快。

一旦唯一键和主键查询成功，它们就立即停止。在这种情况下，服务器会挨个访问下属表，一旦查找到了值，就不会再查找更多的表。

下属表读取的顺序和CREAT TABLE语句中定义的一致。如果经常需要按照特定的顺序取得数据，可以利用这种特性使合并排序操作更快。

合并表的长处

合并表在处理数据方面既有积极的一面，也有消极的一面。

1) 经典的例子就是日志记录。日志是只追加的，所以可以每天用一个表。每天创建新的表并把它加入到合并表中。也可以把以前的表从合并表中移除掉，把它转化为压缩的MyISAM表，再把它们加回到合并表中。

2) 日志追加这并不是合并表的唯一用途。它们通常都被用于数据仓库程序，因为它的另一个长处就是管理大量的数据。在实际中不太可能管理一个TB级别的表，但是如果是由单个50GB的表组成的合并表，任务就会简单很多。

当管理极其巨大的数据库时，考虑的绝不仅仅是常规操作。还要考虑崩溃与恢复。使用小表是很好的主意。检查和修复一系列的小表比起一个大表要快得多，尤其是大表和内存不匹配的时候。还可以并行地检查和修复多个小表。

数据仓库中另外一个顾虑就是如何清理掉老的数据。对巨型表使用DELETE语句最佳状况下效率不高，而在最坏情况下则是一场灾难。但是更改合并表的定义是很简单的，可以使用DROP TABLE命令删除老的数据。这可以轻易地实现自动化。

3）合并表并非只对日志和大量数据有效。它可以方便地按需创建繁忙的表。创建和删除合并表的代价是很低的。索引可以像对视图使用UNION ALL命令那样使用合并表。但它的开销更低，因为服务器不会把结果放到临时表中然后再传递给客户端。这使得它对于报告和仓库化数据非常有用。例如，要创建一个每晚都会运行的任务，它会把昨天的数据和8天前、15天前、以及之前的每一周的数据进行合并。使用合并表就可以创建无须修改的查询，并且自动地访问合适的数据。甚至还可以创建临时合并表，这是视图无法做到的。

因为合并表没有隐藏下属的MyISAM表，所以它提供了一些分区表无法提供的特性：

一个MyISAM表可以包含很多合并表。

可以通过拷贝.frm、.MYI、.MYD文件在服务器之间拷贝下属表。

可以轻易地把更多的表添加到合并表中。这只需要创建一个新表并且更改合并定义即可。

可以创建只包含想要的数据的临时合并表，例如某个特定时间段的数据。这是分区表无法做到的。

如果想对某个表进行备份、恢复、更改、修复，或者其他的操作，可以把它从合并表中移除，完成所有的工作之后再把它加回来。

可以使用myisampack压缩某些或所有的下属表。

分区表正好相反，MySQL隐藏了分区表的分区，并只能通过分区表访问所有的分区

变通方案：

创建一个视图，但是要每个月重建一次，把新表包含进来，这样可以用到innodb表

具体方案：

建立视图，最新数据数据写入固定表，例如t_data，当某个月一号，把历史数导入到上个月名称的一个历史表，例如t_data_201811
视图是v_data包含历史表（.......,t_data_201811）和当前表t_data