MySQL运行在内存中性能测试报告

00 测试先决条件

​ 1、docker启动MySQL，ramdisk划分空间，找到MySQL容器的目录，将MySQL容器的所有文件全部移动到ramdisk划分的空间内，将MySQL容器原始位置留下移动后的ramdisk的软连接。

​ 2、数据库创建四张表并且包含所需字段，并且对MySQL的四张表的id字段建立b+树索引。

CREATE TABLE `table`  (
  `int_1` int NOT NULL,
  `int_2` int NOT NULL,
  `int_3` int NOT NULL,
  `int_4` int NOT NULL,
  `int_5` int NOT NULL,
  `dou_1` double NOT NULL,
  `dou_2` double NOT NULL,
  `dou_3` double NOT NULL,
  `dou_4` double NOT NULL,
  `dou_5` double NOT NULL,
  `str_1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `str_2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `str_3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `str_4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `str_5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `id` bigint NOT NULL,
  `_d` int NOT NULL,
  INDEX `idindex`(`id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

01 测试方法

​ 利用MySQL自带压测工具mysqlslap忽略网络IO情况即MySQL地址为127.0.0.1，通过控制并发连接数量和指定需要执行的SQL的实现测试不同操作性能。

通过四线程对四张表进行读写混合测试，通过改变与数据库交互的次数，观察并记录数据库执行消耗的时间。

a. 测试插入

​ 插入SQL中产生随机数据分别插入到四个表中。控制并发数量为20，多次执行该SQL，执行时记录MySQL的cpu消耗以及结束时记录时间。

set @id=round(rand() * 10000000, 0); 
 INSERT INTO table1 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
 VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');
 INSERT INTO table2 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
 VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');
 INSERT INTO table3 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
 VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');
 INSERT INTO table4 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
 VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');

b.测试更新

​ 在插入测试中，插入的数据全部为随机数据，所以测试更新中采用先查一次全表的id后根据id修改全表的所有字段的策略。

 UPDATE table1 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update'  WHERE id in ( select id from (select id from table1) a);  
 UPDATE table2 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update'  WHERE id in ( select id from (select id from table2) a); 
 UPDATE table3 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update'  WHERE id in ( select id from (select id from table3) a); 
 UPDATE table4 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update'  WHERE id in ( select id from (select id from table4) a); 

c.测试查询

​ 查询为走索引的全表查询，每次查询会查询四个表中的全部数据。

SELECT id FROM table1; 
SELECT id FROM table2;  
SELECT id FROM table3;
SELECT id FROM table4;

d.测试删除

​ 在插入测试中，插入的数据是随机的，没有办法完全模拟每一条都有对应的删除条件时候的性能，这里采用了先获取全表的id结果，利用SQL的“in”操作进行删除。

delete from table1 where id in ( select id from (select id from table1) a);  
delete from table2 where id in ( select id from (select id from table2) a);  
delete from table3 where id in ( select id from (select id from table3) a);  
delete from table4 where id in ( select id from (select id from table4) a);  

e.测试混合

​ 混合即一次操作完成增删改查的操作，也就是表中id可以固定。所以每张表根据生成的id进行对应操作即可。

set @id=round(rand() * 10000000, 0); 
INSERT INTO table1 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
    VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');
UPDATE table1 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update' WHERE id = @id;
SELECT id FROM table1  WHERE id = @id;
DELETE FROM table1 WHERE id = @id;
INSERT INTO table2 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
    VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');
UPDATE table2 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update' WHERE id = @id;
SELECT id FROM table2  WHERE id = @id;
DELETE FROM table2 WHERE id = @id;
INSERT INTO table3 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
    VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');
UPDATE table3 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update' WHERE id = @id;
SELECT id FROM table3  WHERE id = @id;
DELETE FROM table3 WHERE id = @id;
INSERT INTO table4 (int_5, int_1, int_2, int_3, dou_1, dou_2, dou_3, dou_4, dou_5, int_4, str_1, str_2, str_3, str_4, str_5, id, _d)
    VALUES (@id, @id, @id, @id, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id*1.1, @id, 'insert', 'insert', 'insert', 'insert', 'insert', @id, '0');
UPDATE table4 SET str_1 = 'update',str_2 = 'update',str_3 = 'update',str_4 = 'update',str_5 = 'update' WHERE id = @id;
SELECT id FROM table4  WHERE id = @id;
DELETE FROM table4 WHERE id = @id;

02 测试结果

​ 测试结果如下图所示，其中总数据量为每种操作与数据库的交互次数即SQL数量。

总数据量	插入(s)	更新(s)	查询(s)	删除(s)	混合	总消耗时间(s)
1w	0.096	0.122	0.663	0.046	0.606	1.533
10w	0.934	1.109	92.536(1.54min)	0.411	5.344	100.334(1.67min)
20w	1.621	2.191	265.174(4.42min)	0.981	10.766	280.733(4.67min)
30w	2.785	3.6	599.901(10.00min)	1.325	15.984	623.595(10.39min)
40w	3.539	4.709	1067.021(17.78min)	1.734	21.511	1098.514(18.30min)
50w	4.596	6.148	1673.285(27.89min)	2.256	26.668	1712.953(28.55min)

03 结论

​ 1、通过肉眼模糊观察实验中MySQL使用CPU情况发现，在所有操作(操作时间太短的除外)CPU均可以四核跑满，此时磁盘IO不是MySQL的性能瓶颈。

​ 2、MySQL运行于内存中对于io密集型操作（insert or delete）性能很高，在大数据量上对于select等全表扫描或者索引查询仍会有性能瓶颈。(50w的QPS为289)

​ 3、MySQL完成插入50w数据后内存多占用200MB。