1.插入测试数据

1.usera

插入500w数据

2.userb

插入500w数据

3.userall

(主表)

•     usert1 (子表) 插入250w数据
•     usert2 (子表) 插入250w数据

4.userall2

(主表)

•     usert3 (子表) 插入250w数据
•     usert4 (子表) 插入250w数据

• 建表语句

CREATE TABLE usertb(    id serial,    uname  varchar(20) ,    ucreatetime  datetime  ,    age int(11))ENGINE=MYISAMDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=1ROW_FORMAT=COMPACT;

CREATE TABLE usertall(    id serial,    uname  varchar(20) ,    ucreatetime  datetime  ,    age int(11))ENGINE=MERGE union(usert1,usert2) insert_method=lastDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=1ROW_FORMAT=COMPACT;

• 使用存储过程插入测试数据

delimiter $$SET AUTOCOMMIT = 0$$create  procedure test1()begindeclare v_cnt decimal (10)  default 0 ;dd:loop        insert into userta values        (null,'用户1',now(),20),        (null,'用户2',now(),20),        (null,'用户3',now(),20),        (null,'用户4',now(),20),        (null,'用户5',now(),20),        (null,'用户6',now(),20),        (null,'用户7',now(),20),        (null,'用户8',now(),20),        (null,'用户9',now(),20),        (null,'用户0',now(),20);        commit;        set v_cnt = v_cnt+10 ;            if  v_cnt = 5000000 then leave dd;            end if;        end loop dd ;end;$$delimiter;

• 调用存储过程

call test1;

• 测试查询时间

select * from userta;       --3.63s

注意点

1.分表必须保证主表和子表表结构一致，否则报错

Error Code : 1168 Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist