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数量。

03 结论

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

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

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