mysql的merge分区

1.测试数据

1.usera 插入 500w 数据
2.userb 插入 500w 数据
3.userall (主表)

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

4.userall2 (主表)

usert3 *(子表)* **插入250w数据**
usert4 *(子表)* **插入250w数据**
  • 建表语句
1
2
3
4
5
6
7
8
9
CREATE TABLE usertb(
id serial,
uname varchar(20) ,
ucreatetime datetime ,
age int(11))
ENGINE=MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT;
1
2
3
4
5
6
7
8
9
CREATE TABLE usertall(
id serial,
uname varchar(20) ,
ucreatetime datetime ,
age int(11))
ENGINE=MERGE union(usert1,usert2) insert_method=last
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT;
  • 使用存储过程插入测试数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
delimiter $$
SET AUTOCOMMIT = 0$$
create procedure test1()
begin
declare 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;
  • 调用存储过程
1
call test1;
  • 测试查询时间
1
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

LeoQin wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客!
0%