服务器配置
创建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 |
