#################################################################
###                            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