#################################################################
### mysql安装 ##
#################################################################
略
#################################################################
### 分表(这里我们拿myweb_user来举例其它表类似) ##
#################################################################
用merge这种存储引擎分表
1 创建20张数据表表结构和myweb_user一模一样(20张表用来保存真正的数据)
create table myweb_user0 like myweb_user;
create table myweb_user1 like myweb_user;
create table myweb_user2 like myweb_user;
create table myweb_user3 like myweb_user;
create table myweb_user4 like myweb_user;
create table myweb_user5 like myweb_user;
create table myweb_user6 like myweb_user;
create table myweb_user7 like myweb_user;
create table myweb_user8 like myweb_user;
create table myweb_user9 like myweb_user;
create table myweb_user10 like myweb_user;
create table myweb_user11 like myweb_user;
create table myweb_user12 like myweb_user;
create table myweb_user13 like myweb_user;
create table myweb_user14 like myweb_user;
create table myweb_user15 like myweb_user;
create table myweb_user16 like myweb_user;
create table myweb_user17 like myweb_user;
create table myweb_user18 like myweb_user;
create table myweb_user19 like myweb_user;
2 创建myweb_user_merge总表
create table myweb_user_merge like myweb_user;
ALTER TABLE myweb_user_merge ENGINE = MERGE UNION=(myweb_user0,myweb_user1,myweb_user2,myweb_user3,myweb_user4,myweb_user5,myweb_user6,myweb_user7,myweb_user8,myweb_user9,myweb_user10,myweb_user11,myweb_user12,myweb_user13,myweb_user14,myweb_user15,myweb_user16,myweb_user17,myweb_user18,myweb_user19) INSERT_METHOD=no AUTO_INCREMENT=1;
3 创建存储引擎把myweb_user表中原来的信息按照uid对20取余分别插入到不同的分表中
mysql > DELIMITER //
mysql > CREATE PROCEDURE separate_table_myweb_user()
BEGIN
insert into myweb_user0 select * from myweb_user where uid % 20 = 0 order by uid asc;
insert into myweb_user1 select * from myweb_user where uid % 20 = 1 order by uid asc;
insert into myweb_user2 select * from myweb_user where uid % 20 = 2 order by uid asc;
insert into myweb_user3 select * from myweb_user where uid % 20 = 3 order by uid asc;
insert into myweb_user4 select * from myweb_user where uid % 20 = 4 order by uid asc;
insert into myweb_user5 select * from myweb_user where uid % 20 = 5 order by uid asc;
insert into myweb_user6 select * from myweb_user where uid % 20 = 6 order by uid asc;
insert into myweb_user7 select * from myweb_user where uid % 20 = 7 order by uid asc;
insert into myweb_user8 select * from myweb_user where uid % 20 = 8 order by uid asc;
insert into myweb_user9 select * from myweb_user where uid % 20 = 9 order by uid asc;
insert into myweb_user10 select * from myweb_user where uid % 20 = 10 order by uid asc;
insert into myweb_user11 select * from myweb_user where uid % 20 = 11 order by uid asc;
insert into myweb_user12 select * from myweb_user where uid % 20 = 12 order by uid asc;
insert into myweb_user13 select * from myweb_user where uid % 20 = 13 order by uid asc;
insert into myweb_user14 select * from myweb_user where uid % 20 = 14 order by uid asc;
insert into myweb_user15 select * from myweb_user where uid % 20 = 15 order by uid asc;
insert into myweb_user16 select * from myweb_user where uid % 20 = 16 order by uid asc;
insert into myweb_user17 select * from myweb_user where uid % 20 = 17 order by uid asc;
insert into myweb_user18 select * from myweb_user where uid % 20 = 18 order by uid asc;
insert into myweb_user19 select * from myweb_user where uid % 20 = 19 order by uid asc;
END;
//
mysql > DELIMITER ;
4 调用separate_table_myweb_user存储过程把myweb_user的信息插入到各个分表中
MariaDB [myweb_merge_merge]> call separate_table_myweb_user();
###################################################################################
### 建立测试结果保存的excel相关表 ##
###################################################################################
表格记录信息:
内存大小(单位M):992 M cpu核数: cpu频率 1 Intel(R) Pentium(R) CPU G620 @ 2.60GHz
测试序号:
测试sql:
测试的表名:
并发量/测试的次数/sql语句的次数:
每次测试的平均执行时间/最短执行时间/最大执行时间(单位:秒):
mysql占内存:
测试前:
usr sys idl int csw
测试中:
usr sys idl int csw
压力测试使用的语句:
###################################################################################
#### 对分表和原表进行压力测试 #
###################################################################################
这里我只列出一个查询语句压力测试的详细过程(其他的方法一样这里就只列出结果)
1 对原表(myweb_user)查询语句进行压力测试(使用到索引)
1.1 获取硬件相关信息
[root@vm_mysql1 ~]# cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
1 Intel(R) Pentium(R) CPU G620 @ 2.60GHz
可以得出cpu核数为1 频率为2.6GHz
[root@vm_mysql1 ~]# free -m
total used free shared buffers cached
Mem: 992 853 138 0 120 425
内存总数为992M
1.2 查看原表(myweb_user)的数据条数
MariaDB [myweb_merge_merge]> select count(*) from myweb_user;
+----------+
| count(*) |
+----------+
| 925921 |
+----------+
1 row in set (0.00 sec)
1.3 创建含索引的压力测试语句
如:select SQL_NO_CACHE * from myweb_user where uid=4921112;
1.3.1 先对上面的语句进行测试--看是否有用到索引
MariaDB [myweb_merge_merge]> explain select SQL_NO_CACHE * from myweb_user where uid=4921112\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: myweb_user
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
有用到主键索引查询条数为1
1.3.2 把要测试的sql语句写入到文件
[root@vm_mysql1 ~]# mkdir /myweb_mysql_yaliceshi
[root@vm_mysql1 ~]# cd /myweb_mysql_yaliceshi/
[root@vm_mysql1 myweb_mysql_yaliceshi]# vi yaliceshi1.sql;
select SQL_NO_CACHE * from myweb_user where uid=4921112;
1.3.3 使用mysqlslap对其进行压力测试并记录结果到excel(压力测试结果表_mysqlslap.xls)表格
1.3.3.1 对并发为1进行测试
[root@vm_mysql1 myweb_mysql_yaliceshi]# mysqlslap -P3306 --concurrency=1 --iterations=100 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi1.sql --number-of-queries=100 --debug-info -uroot -p123456789
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.013 seconds
Minimum number of seconds to run all queries: 0.013 seconds
Maximum number of seconds to run all queries: 0.017 seconds
Number of clients running queries: 1
Average number of queries per client: 100
User time 0.15, System time 0.18
Maximum resident set size 3248, Integral resident set size 0
Non-physical pagefaults 941, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 20402, Involuntary context switches 102
得出查询并发数为1时查询100次的平均时间0.013 seconds,最大时间0.017 seconds,最小时间0.013 seconds
通过dstat和top查询在执行过程中cpu的使用量和mysqld进程的内存使用量等行馆的相关信息保存到表格
通过dstat我们可以得出在测试的过程中的cpu使用情况
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
0 0 100 0 0 0|5079B 10k| 0 0 | 2B 6B| 26 78
66 34 0 0 0 0| 0 0 |1609B 842B| 0 0 |1020 30k
66 34 0 0 0 0| 0 0 |1869B 346B| 0 0 |1031 30k
用户空间大约为66%左右,系统空间大约34%,io等待占用的cpu为0%,cpu的空闲为0%
通过top命令查看到mysqld对内存的占有率为:21.4%
把测试相关结构保存到"压力测试结果表_mysqlslap.xls"表格中
1.3.3.2 对并发为50进行测试
[root@vm_mysql1 myweb_mysql_yaliceshi]# mysqlslap -P3306 --concurrency=50 --iterations=100 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi1.sql --number-of-queries=100 --debug-info -uroot -p123456789
具体测试过程中其他步骤相同把相关的信息记录到excel表中
2 对分表后的表进行相同的测试:
具体内容和上面部分差不多,这里只列出不同的部分
创建测试用的sql语句
[root@vm_mysql1 ~]# cd /myweb_mysql_yaliceshi/
[root@vm_mysql1 myweb_mysql_yaliceshi]# vi yaliceshi2.sql;
select SQL_NO_CACHE * from myweb_user_merge where uid=4921112;
并发为1时的测试
mysqlslap -P3306 --concurrency=1 --iterations=100 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi2.sql --number-of-queries=100 --debug-info -uroot -p123456789
把结果记录到excel文件中
并发为50时
mysqlslap -P3306 --concurrency=1 --iterations=100 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi2.sql --number-of-queries=100 --debug-info -uroot -p123456789
把结果记录到文件中
3 对无索引的查询语句进行测试(分别对分表前后进行测试并记录结果到)
测试语句举例:
select * from myweb_user where nickname='testuser';
mysqlslap -P3306 --concurrency=1 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi3.sql --number-of-queries=5 --debug-info -uroot -p123456789
具体测试方法同上和注意事件同上
4 对更新语句进行测试
4.1 有索引的测试语句举例
4921112
1831638104@myweb.com
4.1.1 对myweb_user表测试
测试语句举例:
update myweb_user set email='1831638104@1234.com' where uid=4921112;
mysqlslap -P3306 --concurrency=50 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi5.sql --number-of-queries=100 --debug-info -uroot -p123456789
结果记录到文件中
4.1.2 对myweb_user_merge表测试
update myweb_user_merge set email='1831638104@1234.com' where uid=4921112;
mysqlslap -P3306 --concurrency=50 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi6.sql --number-of-queries=100 --debug-info -uroot -p123456789
4.2 无索引的测试语句举例
4.2.1 对myweb_user表测试
测试语句举例:
update myweb_user set email='testuser@1234.com' where nickname='testuser';
mysqlslap -P3306 --concurrency=5 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi7.sql --number-of-queries=10 --debug-info -uroot -p123456789
结果记录到文件中
4.2.2 对myweb_user_merge表测试
测试语句举例:
update myweb_user_merge set email='testuser@1234.com' where nickname='testuser';
mysqlslap -P3306 --concurrency=5 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi8.sql --number-of-queries=10 --debug-info -uroot -p123456789
结果记录到文件中
5 对删除语句进行测试
5.1 有索引的测试语句举例
5.1.1 对myweb_user 表的测试
delete from myweb_user order by uid desc limit 1;
mysqlslap -P3306 --concurrency=10 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi9.sql --number-of-queries=100 --debug-info -uroot -p123456789
5.1.2 对myweb_user_merge 表的测试
delete from myweb_user_merge order by uid desc limit 1;
mysqlslap -P3306 --concurrency=10 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi10.sql --number-of-queries=100 --debug-info -uroot -p123456789
5.2 对没有索引的测试语句举例
5.2.1 对myweb_user 表的测试
delete from myweb_user order by nickname desc limit 1;
mysqlslap -P3306 --concurrency=5 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi11.sql --number-of-queries=10 --debug-info -uroot -p123456789
5.2.2 对myweb_user_merge 表的测试
delete from myweb_user_merge order by nickname desc limit 1;
mysqlslap -P3306 --concurrency=5 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi12.sql --number-of-queries=10 --debug-info -uroot -p123456789
6 对插入语句进行测试
6.1 对myweb_user 表的测试
我们编写存储过程来测试
DELIMITER //
CREATE PROCEDURE insert_into_myweb_user()
BEGIN
declare maxid int;
declare username varchar(50);
declare nickname varchar(50);
declare email varchar(50);
declare randnum int;
select max(uid)+1 into maxid from myweb_user;
set randnum=round(rand()*10000);
set username=concat('testuser_',randnum);
set nickname=username;
set email=concat(username,'@qq.com');
insert into myweb_user(字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
end //
DELIMITER ;
编写文件用于测试
call insert_into_myweb_user();
mysqlslap -P3306 --concurrency=2 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi13.sql --number-of-queries=100 --debug-info -uroot -p123456789
6.2 对 myweb_user_merge 表的测试
我们编写存储过程来测试
DELIMITER //
CREATE PROCEDURE insert_into_myweb_user_merge()
BEGIN
declare maxid int;
declare username varchar(50);
declare nickname varchar(50);
declare email varchar(50);
declare randnum int;
select max(uid)+1 into maxid from myweb_user_merge;
set randnum=round(rand()*10000);
set username=concat('testuser_',randnum);
set nickname=username;
set email=concat(username,'@qq.com');
IF MOD(maxid,20)= 0 THEN
insert into myweb_user0 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 1 THEN
insert into myweb_user1 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 2 THEN
insert into myweb_user2 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 3 THEN
insert into myweb_user3 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 4 THEN
insert into myweb_user4 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 5 THEN
insert into myweb_user5 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 6 THEN
insert into myweb_user6 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 7 THEN
insert into myweb_user7 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 8 THEN
insert into myweb_user8 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 9 THEN
insert into myweb_user9 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 10 THEN
insert into myweb_user10 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 11 THEN
insert into myweb_user11 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 12 THEN
insert into myweb_user12 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 12 THEN
insert into myweb_user13 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 14 THEN
insert into myweb_user14 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 15 THEN
insert into myweb_user15 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 16 THEN
insert into myweb_user16 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 17 THEN
insert into myweb_user17 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 18 THEN
insert into myweb_user18 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSE
insert into myweb_user19 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
END IF;
end //
DELIMITER ;
编写文件用于测试
call insert_into_myweb_user_merge();
mysqlslap -P3306 --concurrency=2 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi14.sql --number-of-queries=100 --debug-info -uroot -p123456789
经过上面的增删改查测试发现在100万行数据的情况下用merge分表性能还没有原表好。下面我们看分区的情况
##################################################################
#### 对myweb_user进行分区测试 ##
##################################################################
1 创建分区表 根据uid%20的余数进行20个分区
CREATE TABLE `myweb_user_partition_list` (
...
....
...
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION by list (MOD(uid,20))
(
PARTITION p0 VALUES IN (0) engine=myisam,
PARTITION p1 VALUES IN (1) engine=myisam,
PARTITION p2 VALUES IN (2) engine=myisam,
PARTITION p3 VALUES IN (3) engine=myisam,
PARTITION p4 VALUES IN (4) engine=myisam,
PARTITION p5 VALUES IN (5) engine=myisam,
PARTITION p6 VALUES IN (6) engine=myisam,
PARTITION p7 VALUES IN (7) engine=myisam,
PARTITION p8 VALUES IN (8) engine=myisam,
PARTITION p9 VALUES IN (9) engine=myisam,
PARTITION p10 VALUES IN (10) engine=myisam,
PARTITION p11 VALUES IN (11) engine=myisam,
PARTITION p12 VALUES IN (12) engine=myisam,
PARTITION p13 VALUES IN (13) engine=myisam,
PARTITION p14 VALUES IN (14) engine=myisam,
PARTITION p15 VALUES IN (15) engine=myisam,
PARTITION p16 VALUES IN (16) engine=myisam,
PARTITION p17 VALUES IN (17) engine=myisam,
PARTITION p18 VALUES IN (18) engine=myisam,
PARTITION p19 VALUES IN (19) engine=myisam
);
2 把数据myweb_user表中的数据插入到分区表
insert into myweb_user_partition_list select * from myweb_user;
3 对分区后的表进行测试
这里主要是对无索引的情况下进行测试
3.1 查询语句测试
select * from myweb_user_partition_list where nickname='testuser';
mysqlslap -P3306 --concurrency=1 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi15.sql --number-of-queries=5 --debug-info -uroot -p123456789
3.2 更新语句测试
update myweb_user_partition_list set email='testuser@1234.com' where nickname='testuser';
mysqlslap -P3306 --concurrency=5 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi16.sql --number-of-queries=10 --debug-info -uroot -p123456789
3.3 删除语句测试
delete from myweb_user_partition_list order by nickname desc limit 1;
mysqlslap -P3306 --concurrency=5 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi17.sql --number-of-queries=10 --debug-info -uroot -p123456789
3.4 插入测试
编写存储过程进行测试
DELIMITER //
CREATE PROCEDURE insert_into_myweb_user_partition_list()
BEGIN
declare maxid int;
declare username varchar(50);
declare nickname varchar(50);
declare email varchar(50);
declare randnum int;
select max(uid)+1 into maxid from myweb_user_partition_list;
set randnum=round(rand()*10000);
set username=concat('testuser_',randnum);
set nickname=username;
set email=concat(username,'@qq.com');
insert into myweb_user_partition_list(字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
end //
DELIMITER ;
call insert_into_myweb_user_partition_list();
mysqlslap -P3306 --concurrency=1 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi18.sql --number-of-queries=100 --debug-info -uroot -p123456789
测试结果表明在100万数据的情况下除了在删除数据的时候,其他情况下分区效率比不上未分区
#######################################################################
### 普通分表测试 ##
#######################################################################
1 我们也是使用对20求余的方式来进行分表
分成
myweb_user_0,myweb_user_1,myweb_user_2,myweb_user_3,myweb_user_4,myweb_user_5,myweb_user_6,myweb_user_7,myweb_user_8,myweb_user_9,myweb_user_10,myweb_user_11,myweb_user_12,myweb_user_13,myweb_user_14,myweb_user_15,myweb_user_16,myweb_user_17,myweb_user_18,myweb_user_19
create table myweb_user_0 like myweb_user;
create table myweb_user_1 like myweb_user;
create table myweb_user_2 like myweb_user;
create table myweb_user_3 like myweb_user;
create table myweb_user_4 like myweb_user;
create table myweb_user_5 like myweb_user;
create table myweb_user_6 like myweb_user;
create table myweb_user_7 like myweb_user;
create table myweb_user_8 like myweb_user;
create table myweb_user_9 like myweb_user;
create table myweb_user_10 like myweb_user;
create table myweb_user_11 like myweb_user;
create table myweb_user_12 like myweb_user;
create table myweb_user_13 like myweb_user;
create table myweb_user_14 like myweb_user;
create table myweb_user_15 like myweb_user;
create table myweb_user_16 like myweb_user;
create table myweb_user_17 like myweb_user;
create table myweb_user_18 like myweb_user;
create table myweb_user_19 like myweb_user;
2 用存储过程分表把数据导入到不同的表中
mysql > DELIMITER //
mysql > CREATE PROCEDURE separate_table_myweb_user_common()
BEGIN
insert into myweb_user_0 select * from myweb_user where uid % 20 = 0 order by uid asc;
insert into myweb_user_1 select * from myweb_user where uid % 20 = 1 order by uid asc;
insert into myweb_user_2 select * from myweb_user where uid % 20 = 2 order by uid asc;
insert into myweb_user_3 select * from myweb_user where uid % 20 = 3 order by uid asc;
insert into myweb_user_4 select * from myweb_user where uid % 20 = 4 order by uid asc;
insert into myweb_user_5 select * from myweb_user where uid % 20 = 5 order by uid asc;
insert into myweb_user_6 select * from myweb_user where uid % 20 = 6 order by uid asc;
insert into myweb_user_7 select * from myweb_user where uid % 20 = 7 order by uid asc;
insert into myweb_user_8 select * from myweb_user where uid % 20 = 8 order by uid asc;
insert into myweb_user_9 select * from myweb_user where uid % 20 = 9 order by uid asc;
insert into myweb_user_10 select * from myweb_user where uid % 20 = 10 order by uid asc;
insert into myweb_user_11 select * from myweb_user where uid % 20 = 11 order by uid asc;
insert into myweb_user_12 select * from myweb_user where uid % 20 = 12 order by uid asc;
insert into myweb_user_13 select * from myweb_user where uid % 20 = 13 order by uid asc;
insert into myweb_user_14 select * from myweb_user where uid % 20 = 14 order by uid asc;
insert into myweb_user_15 select * from myweb_user where uid % 20 = 15 order by uid asc;
insert into myweb_user_16 select * from myweb_user where uid % 20 = 16 order by uid asc;
insert into myweb_user_17 select * from myweb_user where uid % 20 = 17 order by uid asc;
insert into myweb_user_18 select * from myweb_user where uid % 20 = 18 order by uid asc;
insert into myweb_user_19 select * from myweb_user where uid % 20 = 19 order by uid asc;
END;
//
DELIMITER ;
call separate_table_myweb_user_common();
3 进行测试
这里我们主要是对没有索引的情况下进行测试
3.1 查询测试
3.1.1 我们使用union all来查询
select * from myweb_user_0 where nickname='testuser' union all select * from myweb_user_1 where nickname='testuser' union all select * from myweb_user_2 where nickname='testuser' union all select * from myweb_user_3 where nickname='testuser' union all select * from myweb_user_4 where nickname='testuser' union all select * from myweb_user_5 where nickname='testuser' union all select * from myweb_user_6 where nickname='testuser' union all select * from myweb_user_7 where nickname='testuser' union all select * from myweb_user_8 where nickname='testuser' union all select * from myweb_user_9 where nickname='testuser' union all select * from myweb_user_10 where nickname='testuser' union all select * from myweb_user_11 where nickname='testuser' union all select * from myweb_user_12 where nickname='testuser' union all select * from myweb_user_13 where nickname='testuser' union all select * from myweb_user_14 where nickname='testuser' union all select * from myweb_user_15 where nickname='testuser' union all select * from myweb_user_16 where nickname='testuser' union all select * from myweb_user_17 where nickname='testuser' union all select * from myweb_user_18 where nickname='testuser' union all select * from myweb_user_19 where nickname='testuser';
mysqlslap -P3306 --concurrency=1 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi19.sql --number-of-queries=5 --debug-info -uroot -p123456789
3.1.2 我们使用视图来查询
3.1.2.1 创建视图
create view myweb_user_view as select * from myweb_user_0 where nickname='testuser' union all select * from myweb_user_1 where nickname='testuser' union all select * from myweb_user_2 where nickname='testuser' union all select * from myweb_user_3 where nickname='testuser' union all select * from myweb_user_4 where nickname='testuser' union all select * from myweb_user_5 where nickname='testuser' union all select * from myweb_user_6 where nickname='testuser' union all select * from myweb_user_7 where nickname='testuser' union all select * from myweb_user_8 where nickname='testuser' union all select * from myweb_user_9 where nickname='testuser' union all select * from myweb_user_10 where nickname='testuser' union all select * from myweb_user_11 where nickname='testuser' union all select * from myweb_user_12 where nickname='testuser' union all select * from myweb_user_13 where nickname='testuser' union all select * from myweb_user_14 where nickname='testuser' union all select * from myweb_user_15 where nickname='testuser' union all select * from myweb_user_16 where nickname='testuser' union all select * from myweb_user_17 where nickname='testuser' union all select * from myweb_user_18 where nickname='testuser' union all select * from myweb_user_19;
3.1.2.2 在视图的基础上进行查询测试
select * from myweb_user_view where nickname='testuser';
mysqlslap -P3306 --concurrency=1 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi21.sql --number-of-queries=5 --debug-info -uroot -p123456789
我们发现通过视图来查询比直接连表查询要慢了一倍
3.2 更新测试 分表的更新测试包含两个方面:一:通过nickname或者其他查找用户的id值,二根据id值更新相应表的数据信息。这里我们通过编写存储过程来实现
update myweb_user set email='testuser@1234.com' where nickname='testuser';
3.2.1 存储过程编写 (这里只有当符合条件的查询只有一条时才能这样测试所以先把原表和测试表的一条数据改了保持统一)
MariaDB [myweb_merge_merge]> update myweb_user set nickname='aaabbb' where uid=46;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [myweb_merge_merge]> update myweb_user_6 set nickname='aaabbb' where uid=46;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
DELIMITER //
CREATE PROCEDURE update_from_myweb_user_divide()
BEGIN
declare currentuid int;
select uid into currentuid from (select uid from myweb_user_0 where nickname='testuser' union all select uid from myweb_user_1 where nickname='testuser' union all select uid from myweb_user_2 where nickname='testuser' union all select uid from myweb_user_3 where nickname='testuser' union all select uid from myweb_user_4 where nickname='testuser' union all select uid from myweb_user_5 where nickname='testuser' union all select uid from myweb_user_6 where nickname='testuser' union all select uid from myweb_user_7 where nickname='testuser' union all select uid from myweb_user_8 where nickname='testuser' union all select uid from myweb_user_9 where nickname='testuser' union all select uid from myweb_user_10 where nickname='testuser' union all select uid from myweb_user_11 where nickname='testuser' union all select uid from myweb_user_12 where nickname='testuser' union all select uid from myweb_user_13 where nickname='testuser' union all select uid from myweb_user_14 where nickname='testuser' union all select uid from myweb_user_15 where nickname='testuser' union all select uid from myweb_user_16 where nickname='testuser' union all select uid from myweb_user_17 where nickname='testuser' union all select uid from myweb_user_18 where nickname='testuser' union all select uid from myweb_user_19 where nickname='testuser') as myweb_user_tmp;
IF MOD(currentuid,20)= 0 THEN
update myweb_user_0 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 1 THEN
update myweb_user_1 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 2 THEN
update myweb_user_2 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 3 THEN
update myweb_user_3 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 4 THEN
update myweb_user_4 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 5 THEN
update myweb_user_5 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 6 THEN
update myweb_user_6 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 7 THEN
update myweb_user_7 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 8 THEN
update myweb_user_8 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 9 THEN
update myweb_user_9 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 10 THEN
update myweb_user_10 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 11 THEN
update myweb_user_11 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 12 THEN
update myweb_user_12 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 13 THEN
update myweb_user_13 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 14 THEN
update myweb_user_14 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 15 THEN
update myweb_user_15 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 16 THEN
update myweb_user_16 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 17 THEN
update myweb_user_17 set email='testuser@1234.com' where uid=currentuid;
ELSEIF MOD(currentuid,20)= 18 THEN
update myweb_user_18 set email='testuser@1234.com' where uid=currentuid;
ELSE
update myweb_user_19 set email='testuser@1234.com' where uid=currentuid;
END IF;
end;
//
DELIMITER ;
call update_from_myweb_user_divide();
mysqlslap -P3306 --concurrency=5 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi22.sql --number-of-queries=10 --debug-info -uroot -p123456789
3.3 插入测试--这里我还是编写存储过程来实现
create table temp_id(id int); # 创建一个临时表来保存最大的id值
DELIMITER //
CREATE PROCEDURE insert_into_myweb_user_divide()
BEGIN
declare maxid int;
declare username varchar(50);
declare nickname varchar(50);
declare email varchar(50);
declare randnum int;
select id into maxid from temp_id;
update temp_id set id=id+1;
set maxid=maxid+1;
set randnum=round(rand()*10000);
set username=concat('testuser_',randnum);
set nickname=username;
set email=concat(username,'@qq.com');
IF MOD(maxid,20)= 0 THEN
insert into myweb_user_0 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 1 THEN
insert into myweb_user_1 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 2 THEN
insert into myweb_user_2 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 3 THEN
insert into myweb_user_3 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 4 THEN
insert into myweb_user_4 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 5 THEN
insert into myweb_user_5 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 6 THEN
insert into myweb_user_6 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 7 THEN
insert into myweb_user_7 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 8 THEN
insert into myweb_user_8 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 9 THEN
insert into myweb_user_9 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 10 THEN
insert into myweb_user_10 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 11 THEN
insert into myweb_user_11 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 12 THEN
insert into myweb_user_12 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 12 THEN
insert into myweb_user_13 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 14 THEN
insert into myweb_user_14 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 15 THEN
insert into myweb_user_15 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 16 THEN
insert into myweb_user_16 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 17 THEN
insert into myweb_user_17 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSEIF MOD(maxid,20)= 18 THEN
insert into myweb_user_18 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
ELSE
insert into myweb_user_19 values (字段1,字段2,.......,字段n) values (值1,值2,.....,值n);
END IF;
end //
DELIMITER ;
call insert_into_myweb_user_divide();
mysqlslap -P3306 --concurrency=1 --iterations=5 --create-schema='myweb_merge_merge' --engine=myisam --query=/myweb_mysql_yaliceshi/yaliceshi23.sql --number-of-queries=100 --debug-info -uroot -p123456789
mysql分表分区技术及压力测试
原创文章标签 mysql 分表 分区 mysqlsl 文章分类 MySQL 数据库
©著作权归作者所有:来自51CTO博客作者冰冻火焰的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:简单dns服务器搭建
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章