跑测试速度对比跑测试速度对比

flyway
文章图片
./gradlew test.png MEMORY 、InnoDB 对比

vim TestDB.sql

1 CREATE TABLE `memory` ( 2`id` int(11) NOT NULL AUTO_INCREMENT, 3`code` int(11) NOT NULL, 4PRIMARY KEY (`id`) 5 ) ENGINE=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 6 7 8 CREATE TABLE `innodb` ( 9`id` int(11) NOT NULL AUTO_INCREMENT, 10`code` int(11) NOT NULL, 11PRIMARY KEY (`id`) 12 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

vim memory.sql

insert into memory values(null,1);

vim innodb.sql

insert into innodb values(null,2);

【跑测试速度对比】注：
复制： Esc + yy + 复制次数 + p
删除一行： Esc + dd
删除多行： Esc + 删除行数 + dd
删除从某一行至最后一行： 选中起始行 + v + Shift G + dd

导入 *.sql

$ mysql -u root -pthoughtworks -h 127.0.0.1 TestDB < TestDB.sql// 显示表属性 mysql> show table status from TestDB ; +--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name| Engine | Version | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time| Update_time| Check_time | Collation| Checksum | Create_options | Comment | +--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | innodb | InnoDB |10 | Dynamic| 47405 |33 |1589248 |0 |0 |4194304 |46885 | 2017-03-05 03:01:15 | 2017-03-05 04:21:45 | NULL| utf8_general_ci |NULL ||| | memory | MEMORY |10 | Fixed|0 |9 |0 |3774870 |0 |0 |1 | 2017-03-05 04:20:41 | NULL| NULL| utf8_general_ci |NULL ||| +--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 2 rows in set (0.00 sec)

计算 insert 执行时间

$time mysql -u root -ppassword -h 127.0.0.1 TestDB < memory.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql -u root -ppassword -h 127.0.0.1 TestDB < memory.sql1.93s user 6.54s system 25% cpu 33.541 total$time mysql -u root -ppassword -h 127.0.0.1 TestDB < innodb.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql -u root -ppassword -h 127.0.0.1 TestDB < innodb.sql4.38s user 11.15s system 3% cpu 8:32.89 total

insert 对比：

次数	MEMORY	InnoDB
2000	0.02s user 0.04s system 28% cpu 0.184 total	0.02s user 0.06s system 3% cpu 2.390 total
100000	0.42s user 1.75s system 25% cpu 8.457 total	0.98s user 2.94s system 3% cpu 2:05.15 total
4000000	1.93s user 6.54s system 25% cpu 33.541 total	4.38s user 11.15s system 3% cpu 8:32.89 total

mysqldump 使用

导出 docker 中 mysql 数据库 BronzeSword 的信息到 bs.sql 文件中：

docker exec -i assembly_mysql_1 mysqldump -u root -ppassword BronzeSword > bs.sql

mysqldump 命令参数

--no-create-info，-t ：只导出数据，而不添加 CREATE TABLE 语句。

docker exec -i assembly_mysql_1 mysqldump -u root -ppassword -t BronzeSword > data.sql

--no-data，-d：不导出任何数据，只导出数据库表结构。

docker exec -i assembly_mysql_1 mysqldump -u root -ppassword -d BronzeSword > BronzeSword.sql

Java 程序中更新已有数据库的存储引擎思路： (InnoDB -> MEMORY)

导出表结构： docker exec -i assembly_mysql_1 mysqldump -u root -ppassword -t BronzeSword > data.sql
导出数据：docker exec -i assembly_mysql_1 mysqldump -u root -ppassword -t BronzeSword > data.sql
更新表类型：sed -i \"s/InnoDB/MEMORY/g\" BronzeSword.sql
导入表：docker exec -i assembly_mysql_1 mysql -u root -ppassword BronzeSword < ./BronzeSword.sql
导入数据：docker exec -i assembly_mysql_1 mysql -u root -ppassword BronzeSword < ./data.sql

Java 代码如下：

导入导出：

try { -Process exec = Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c", -"docker exec -i assembly_mysql_1 mysqldump -u root -ppassword -t BronzeSword > ./data.sql" -+ "docker exec -i assembly_mysql_1 mysqldump -u root -ppassword -d BronzeSword > ./BronzeSword.sql; " -+ "sed -i \"s/InnoDB/MEMORY/g\" BronzeSword.sql; " -+ "docker exec -i assembly_mysql_1 mysql -u root -ppassword BronzeSword < ./BronzeSword.sql; " + "docker exec -i assembly_mysql_1 mysql -u root -ppassword BronzeSword < ./data.sql; "}); -exec.waitFor(); -} catch (IOException e) { -e.printStackTrace(); -} catch (InterruptedException e) { -e.printStackTrace(); -}

注：导出的 .sql 文件在当前项目的根目录下

每次执行测试时导入数据：

@Before public void setUp() throws Exception { Process exec = Runtime.getRuntime().exec(new String[]{"/bin/sh", "-c", "docker exec -i assembly_mysql_1 mysql -u root -ppasswordBronzeSword < ./data.sql; "}); exec.waitFor(); session.startManagedSession(); }

执行测试时 MEMORY 与 InnoDB 速度对比：
MEMORY ：执行 sed -i \"s/InnoDB/MEMORY/g\" BronzeSword.sql;
InnoDB ：不执行 sed -i \"s/InnoDB/MEMORY/g\" BronzeSword.sql;

./gradlew flywayclean flywaymigrate test;

InnoDB	MEMEORY
40.353	49.633
38.783	39.4
39.291	39.145
39.41	40.099
38.356	38.929
38.144	40.703
38.169	38.286
38.725	39.713
38.411	38.233

http://blog.csdn.net/aasmfox/article/details/40747145
http://www.linuxidc.com/Linux/2012-10/72884.htm
http://www.cnblogs.com/mywolrd/archive/2009/11/12/1930688.html