Mysql8性能測試

伺服器配置

建立test1資料庫

1.    CREATE DATABASE IF NOT EXISTS test1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;             

建立年級、班級、學生表

1.    DROP TABLE IF EXISTS `grade`;  
2.    CREATE TABLE `grade` (  
3.      `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'id',  
4.      `gradeName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '班級名稱',  
5.      PRIMARY KEY (`id`)  
6.    ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

1.    DROP TABLE IF EXISTS `class`;  
2.    CREATE TABLE `class` (  
3.      `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id',  
4.      `className` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '班級名稱',  
5.      `gradeId` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '年級id',  
6.      PRIMARY KEY (`id`)  
7.    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.    DROP TABLE IF EXISTS `student`;  
2.    CREATE TABLE `student` (  
3.      `id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'id',  
4.      `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '學生姓名',  
5.      `age` int(2) DEFAULT NULL COMMENT '年齡',  
6.      `gender` int(1) DEFAULT NULL COMMENT '性别，1=男，2=女',  
7.      `classId` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '班級id',  
8.      `gradeId` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '年級id',  
9.      `chineseScore` int(3) DEFAULT NULL COMMENT '國文成績',  
10.      `englishScore` int(3) DEFAULT NULL COMMENT '英文成績',  
11.      `mathScore` int(3) DEFAULT NULL COMMENT '數學成績',  
12.      PRIMARY KEY (`id`)  
13.    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
           

解決報錯

如執行報如下錯（不影響執行結果）：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

修改 /etc/my.cnf檔案，在尾部加入以下内容：

1.    sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'             

重新開機mysql

1.    Systemctl restart mysqld             

最終修改結果如下圖：

插入年級、班級資料

1.    insert into grade values(replace(UUID(),'-',''),'1年級');  
2.    insert into grade values(replace(UUID(),'-',''),'2年級');  
3.    insert into grade values(replace(UUID(),'-',''),'3年級');            

1.    #注意，gradeId以年級表實際值為準  
2.    insert into class values(replace(UUID(),'-',''),'1年級1班','d684baa545fc11ea80e5000c29d5571e');    
3.    insert into class values(replace(UUID(),'-',''),'1年級2班','d684baa545fc11ea80e5000c29d5571e');    
4.    insert into class values(replace(UUID(),'-',''),'1年級3班','d684baa545fc11ea80e5000c29d5571e');    
5.    insert into class values(replace(UUID(),'-',''),'1年級4班','d684baa545fc11ea80e5000c29d5571e');    
6.    insert into class values(replace(UUID(),'-',''),'1年級5班','d684baa545fc11ea80e5000c29d5571e');    
7.    insert into class values(replace(UUID(),'-',''),'2年級1班','d687455e45fc11ea80e5000c29d5571e');    
8.    insert into class values(replace(UUID(),'-',''),'2年級2班','d687455e45fc11ea80e5000c29d5571e');    
9.    insert into class values(replace(UUID(),'-',''),'2年級3班','d687455e45fc11ea80e5000c29d5571e');    
10.    insert into class values(replace(UUID(),'-',''),'2年級4班','d687455e45fc11ea80e5000c29d5571e');    
11.    insert into class values(replace(UUID(),'-',''),'2年級5班','d687455e45fc11ea80e5000c29d5571e');    
12.    insert into class values(replace(UUID(),'-',''),'3年級1班','d6895bc645fc11ea80e5000c29d5571e');    
13.    insert into class values(replace(UUID(),'-',''),'3年級2班','d6895bc645fc11ea80e5000c29d5571e');    
14.    insert into class values(replace(UUID(),'-',''),'3年級3班','d6895bc645fc11ea80e5000c29d5571e');    
15.    insert into class values(replace(UUID(),'-',''),'3年級4班','d6895bc645fc11ea80e5000c29d5571e');    
16.    insert into class values(replace(UUID(),'-',''),'3年級5班','d6895bc645fc11ea80e5000c29d5571e');            

結果如下：

編寫存儲過程（向學生表插入指定數量的測試資料）

1.    DROP PROCEDURE IF EXISTS `test_insert_rows`;  
2.    DELIMITER ;;  
3.    CREATE DEFINER=`root`@`%` PROCEDURE `test_insert_rows`(IN count INT)  
4.    BEGIN  
5.      
6.        DECLARE i int DEFAULT 0;  
7.        DECLARE _id varchar(32);  
8.        DECLARE _name varchar(10);  
9.        DECLARE _gender int(1);  
10.        DECLARE _classId varchar(32);  
11.        DECLARE _gradeId varchar(32);  
12.        DECLARE _chineseScore int(3);  
13.        DECLARE _englishScore int(3);  
14.        DECLARE _mathScore int(3);  
15.        DECLARE _class varchar(10);  
16.        DECLARE _age int(2);  
17.      
18.        while i < count do         
19.              
20.            #随機選擇插入的班級  
21.            select concat(floor(rand()*3+1),'年級',floor(rand()*5+1),'班') into _class;  
22.            #id為UUID随機數  
23.            select replace(UUID(),'-','') into _id;  
24.            #姓名為10位随機字元串              
25.            select substr(md5(rand()),1,10) into _name;  
26.            #性别随機選擇1或2  
27.            select floor(rand()*2+1) into _gender;  
28.            #随機選擇班級和年級  
29.            select id,gradeId into _classId,_gradeId from class where className=_class;  
30.            #國文、英文、數學成績生成50-100的随機數  
31.            select floor(rand()*(101-50)+50) into _chineseScore;  
32.            select floor(rand()*(101-50)+50) into _englishScore;  
33.            select floor(rand()*(101-50)+50) into _mathScore;  
34.            #年齡生成6-12的随機數  
35.            select floor(rand()*(13-6)+6) into _age;  
36.      
37.            insert into student values(_id,_name,_age,_gender,_classId,_gradeId,_chineseScore,_englishScore,_mathScore);          
38.              
39.            set i = i+1;  
40.      
41.        end while;  
42.          
43.        select 'completed';  
44.      
45.      
46.    END  
47.    ;;  
48.    DELIMITER ;            

編寫查詢sql（查詢所有年級各班的學生總人數、男女生總人數、最大/最小/平均年齡、各科的最大/最小/平均分數）

1.    SELECT  
2.        g.gradeName AS '年級',  
3.        c.className AS '班級',  
4.        count(s.id) AS '總人數',  
5.        min(s.age) AS '最小年齡',  
6.        max(s.age) AS '最大年齡',  
7.        round(avg(s.age), 2) AS '平均年齡',  
8.        (  
9.            SELECT  
10.                count(1)  
11.            FROM  
12.                student  
13.            WHERE  
14.                classId = c.id  
15.            AND gender = 1  
16.        ) AS '男生人數',  
17.        (  
18.            SELECT  
19.                count(1)  
20.            FROM  
21.                student  
22.            WHERE  
23.                classId = c.id  
24.            AND gender = 2  
25.        ) AS '女生人數',  
26.        min(s.chineseScore) AS '國文最低分',  
27.        max(s.chineseScore) AS '國文最高分',  
28.        round(avg(s.chineseScore), 2) AS '國文平均分',  
29.        min(s.englishScore) AS '英語最低分',  
30.        max(s.englishScore) AS '英語最高分',  
31.        round(avg(s.englishScore), 2) AS '英語平均分',  
32.        min(s.mathScore) AS '數學最低分',  
33.        max(s.mathScore) AS '數學最高分',  
34.        round(avg(s.mathScore), 2) AS '數學平均分'  
35.    FROM  
36.        class AS c  
37.    LEFT JOIN student AS s ON s.classId = c.id  
38.    LEFT JOIN grade AS g ON c.gradeId = g.id  
39.    GROUP BY  
40.        s.classId  
41.    ORDER BY  
42.        g.gradeName,  
43.        c.className 
           

插入10萬條學生表測試資料，進行測試

1.    #調用存儲過程，向學生表插入10萬條資料  
2.    call test_insert_rows(100000);             

測試結果：0.981s

插入100萬條學生表測試資料，進行測試

1.    #調用存儲過程，向學生表插入100萬條資料  
2.    call test_insert_rows(1000000);             

測試結果：13.803s

插入1000萬條學生表測試資料，進行測試

1.    #調用存儲過程，向學生表插入1000萬條資料  
2.    call test_insert_rows(10000000);  
           

測試結果： 198.747s，将虛拟配置調整為2顆cpu 8核+4G記憶體後，測試結果幾乎沒有變化。調整前和調整後，檢視虛拟機資源使用情況基本相同，cpu占用95%左右，記憶體13%左右。

插入1億條學生表測試資料，進行測試

1.    #調用存儲過程，向學生表插入1億條資料  
2.    call test_insert_rows(100000000);  
           

測試結果： 3536.844s，59分鐘

測試結論

在目前伺服器配置情況下，測試結果：

資料量	10萬	100萬	1000萬	1億
結果	0.981s	13.803s	198.747s	3536.844s