性能优化-数据准备：使用存储过程生成百万测试数据
1 概述2 创建数据库3 建表4 创建存储过程4.1 创建存储过程-学生表4.1 创建存储过程-班级表4.1 创建存储过程-课程表4.1 创建存储过程-成绩表4.1 创建存储过程-给每个班级分配学生人数4.1 创建存储过程-给每个学生分配1个课程的默认100次历史成绩 5 生成100万数据6 查询数据1 概述
在这里我们为什么要去生成100万数据呢？因为在很多实际项目上我们可能会接触到需要进行性能调优的问题，比如碰到某个查询接口特别慢，那么老板或者你的领导就需要你去处理了，为啥让你干呢，你不干谁干，像那种小公司的，公司的后端技术除了你还会有谁，难道你要你的领导去干吗？那是不可能，领导要喝茶，哪有闲工夫陪你玩，优哉游哉不香吗？
在我们做数据库的性能调优之前做一下数据准备，因为数据一旦达到100万级别对查询速度都会产生影响，所以我们就先生成个百万数据玩玩，下面我们开始啦。
基本思路
创建4张表：学生表、班级表、课程表、成绩表，在这里以“学生表”为主表
说明：一个学生只有一个班级，一个学生有多门课的成绩，一个学生在一门课上会有多个成绩（历史成绩，以日期进行历史成绩的区分）
举例：假如在某所高中开设了
10门课程，学校共有30个班，每班分配n人，总学生数为30*n人每个学生每个课程考了m次，成绩范围是0~100分课程：语文、数学、英语、政治、历史、地理、生物、化学、物理、体育；
学生：在这里学生的姓名可以按照输入某个“中文”+ 计数 进行生成，比如以“李欣欣”+
n的方式生成，n为自然数，举例：李欣欣1，…,李欣欣1000班级：高一
（1）班 ~（10）班，高二（1）班 ~（10）班，高三（1）班 ~（10）班成绩：每天考一次每门课程，连续考试
100天，从-01-01开始考试，加上100天即可，每门分数按随机数[0,100]分生成。
涉及主要知识点
如何创建存储过程if else语句数据类型转换字符串相加临时表创建while循环以及双循环问题[1,100]的随机数生成
2 创建数据库
我们在这里使用命令创建一个DataPerformanceTestDB的数据库
use master -- 设置当前数据库为master,以便访问sysdatabases表goif not exists(select 1 from sysdatabases where name='DataPerformanceTestDB')--DataPerformanceTestDB 百万性能测试数据库create database DataPerformanceTestDB on primary -- 默认就属于primary文件组,可省略(/*--数据文件的具体描述--*/name='DataPerformanceTestDB_data', -- 主数据文件的逻辑名称filename='D:\Data\SQL Server \百万数据性能测试\DataPerformanceTestDB_data.mdf', -- 主数据文件物理存储位置size=5mb, --主数据文件的初始大小--maxsize=100mb, -- 主数据文件增长的最大值，不要限制数据库大小，数据一旦增加到上限则无法使用filegrowth=15%--主数据文件的增长率)log on(/*--日志文件的具体描述,各参数含义同上--*/name='dataPerformanceTestDB_log',filename='D:\Data\SQL Server \百万数据性能测试\DataPerformanceTestDB_log.ldf',-- 日志文件物理存储位置size=2mb,filegrowth=1mb)go
3 建表
我们开始创建4张表，如下图所示：
use DataPerformanceTestDB go--创建4张表：--“Student学生表”的ClassId 与 ”Class 班级表“的Id 关联--"Student 学生表"和"Course 课程表"通过"Score 成绩表"的StudentId和CourseId进行关联if not exists(select 1 from sysobjects where name='Student')--Student 学生表create table Student (Id uniqueidentifier not null primary key,SNo varchar(10), --学号SName varchar(20), --学生姓名SAge int, --年龄SSex bit, --性别：男1，女0ClassId uniqueidentifier, --班级Id，关联班级表)goif not exists(select 1 from sysobjects where name='Class')--Class 班级表create table Class (Id uniqueidentifier not null primary key,ClNo varchar(20) not null, --班级编号ClName nvarchar(20) not null, --班级名称HeadMasterName nvarchar(20) not null, --班主任TelPhone varchar(50), --班主任电话号码Declaration nvarchar(200) --班级宣言)goif not exists(select 1 from sysobjects where name='Course')--Course 课程表create table Course (Id uniqueidentifier not null primary key,CName nvarchar(100) not null, --课程名称CRoom nvarchar(100) --教室)goif not exists(select 1 from sysobjects where name='Score')--Score 成绩表create table Score (Id uniqueidentifier not null primary key,StudentId uniqueidentifier, --学生Id，关联学生表CourseId uniqueidentifier, --课程Id，关联课程表GradeDate datetime not null,--历史成绩日期，对一门课的历史成绩以作区分Score float --成绩)go
4 创建存储过程
4.1 创建存储过程-学生表
基本思路
根据学生Id判断数据是否存在；若存在，则再次通过学号SNo和姓名SName判断数据是否相同，若相同，则返回标识；若不同则更新数据；若不存在，则插入新的数据
use DataPerformanceTestDB go--使用存储过程插入数据Create proc sp_Insert_Student@Id uniqueidentifier,--唯一标识@SNo varchar(10),--学号@SName varchar(20),--姓名@Age int,--年龄@Sex bit,--性别@ClassId uniqueidentifier,--班级Id@rtn int output--输出as--定义临时变量declare@tempSNo varchar(10),--学号@tempSName varchar(20)--姓名if exists(select * from Student where Id=@Id)--如果id存在beginselect @tempSNo=SNo,@tempSName=SName from Student where Id=@Idif ((@tempSNo=@SNo) and (@tempSName=@SName))beginset @rtn=0 --有相同的数据，直接返回值endelsebeginupdate Student set SNo=@SNo,SName=@SName where Id=@Idset @rtn=2 --有主键相同的数据，进行更新处理endendelse--如果id不存在begininsert into Student values(@Id,@SNo,@SName,@Age,@Sex,@ClassId)set @rtn=1 --没有相同的数据，进行插入处理end
4.1 创建存储过程-班级表
基本思路
根据班级Id判断数据是否存在；若存在，则再次通过班级号ClNo和班级名称ClName判断数据是否相同，若相同，则返回标识；若不同则更新数据；若不存在，则插入新的数据
--使用存储过程插入数据Create proc sp_Insert_Class@Id uniqueidentifier,--唯一标识@ClNo varchar(20),--班级号@ClName nvarchar(20),--班级名称@HeadMasterName nvarchar(20),--班主任@TelPhone varchar(50),--电话号码@Declaration nvarchar(200),--班级宣言@cl_rtn int output--输出asdeclare@tempClNo varchar(20),--参数：班级号@tempClName varchar(20)--参数：班级名称if exists(select * from Class where Id=@Id)beginselect @tempClNo=ClNO,@tempClName=ClName from Class where Id=@Idif ((@tempClNo=@ClNO) and (@tempClName=@ClName))beginset @cl_rtn=0 --有相同的数据，直接返回值endelsebeginupdate Class set ClNo=@ClNo,CLName=@ClName where Id=@Idset @cl_rtn=2 --有主键相同的数据，进行更新处理endendelsebegininsert into Class values(@Id,@ClNo,@ClName,@HeadMasterName,@TelPhone,@Declaration)set @cl_rtn=1 --没有相同的数据，进行插入处理end
4.1 创建存储过程-课程表
基本思路
根据课程Id判断数据是否存在；若存在，则再次通过课程名称CName和教室CRoom判断数据是否相同，若相同，则返回标识；若不同则更新数据；若不存在，则插入新的数据
use DataPerformanceTestDB go--使用存储过程插入数据Create proc sp_Insert_Course@Id uniqueidentifier,--唯一标识@CName varchar(20),--课程名称@CRoom nvarchar(20),--教室@c_rtn int output--输出as--定义临时变量declare@tempCName varchar(10),@tempCRoom varchar(20)if exists(select * from Course where Id=@Id)--如果id存在beginselect @tempCName=CName,@tempCRoom=CRoom from Course where Id=@Idif ((@tempCName=@CName) and (@tempCRoom=@CRoom))beginset @c_rtn=0 --有相同的数据，直接返回值endelsebeginupdate Course set CName=@CName,CRoom=@CRoom where Id=@Idset @c_rtn=2 --有主键相同的数据，进行更新处理endendelse--如果id不存在begininsert into Course values(@Id,@CName,@CRoom)set @c_rtn=1 --没有相同的数据，进行插入处理end
4.1 创建存储过程-成绩表
基本思路
根据成绩Id判断数据是否存在；若存在，则再次通过学生Id：StudentId和课程Id：CourseId判断数据是否相同，若相同，则返回标识；若不同则更新数据；若不存在，则插入新的数据
use DataPerformanceTestDB go--使用存储过程插入数据Create proc sp_Insert_Score@Id uniqueidentifier,--唯一标识@StudentId uniqueidentifier,--班级Id@CourseId uniqueidentifier,--课程Id@GradeDate datetime,--历史成绩日期，对一门课的历史成绩以作区分@Score float,--成绩@s_rtn int output--输出as--定义临时变量declare@tempStudentId uniqueidentifier,@tempCourseId uniqueidentifierif exists(select * from Score where Id=@Id)--如果id存在beginselect @StudentId=StudentId,@CourseId=CourseId from Score where Id=@Idif ((@tempStudentId=@StudentId) and (@tempCourseId=@CourseId))beginset @s_rtn=0 --有相同的数据，直接返回值endelsebeginupdate Score set StudentId=@StudentId,CourseId=@CourseId where Id=@Idset @s_rtn=2 --有主键相同的数据，进行更新处理endendelse--如果id不存在begininsert into Score values(@Id,@StudentId,@CourseId,@GradeDate,@Score)set @s_rtn=1 --没有相同的数据，进行插入处理end
4.1 创建存储过程-给每个班级分配学生人数
基本思路
将班级表的所有数据插入到临时表中；外循环临时表每一条数据，内循环分配到每个班级的数量数据；在内循环中针对需要的字段进行赋值并插入数据
use DataPerformanceTestDBgo-- =============================================-- Author: love2zq-- Create date: -09-05-- Description: 给每个班级分配学生人数-- =============================================ALTER procedure [dbo].[sp_Create_AssignStudent]@@sName varchar(10),--学生姓名@n int,--每班人数@classNum int = 1--分配多少个班级，默认只针对一个班级加学生asbegindeclare@i int,--数据条数@j int,--循环标识@stuId uniqueidentifier,--学生主键Id@classId uniqueidentifier,--班级主键Id@sNo varchar(10),--学号@sName varchar(10),--学生姓名@count int,--学生数量@rtn int,--学生表输出提示@msg nvarchar(100)--输出提示--把所有班级信息存到临时表select * into #temp from (select Id,ROW_NUMBER() over(order by ClNo) as row from Class) aselect @count = COUNT(1) from #temp;--设置分配班级数量if @classNum > @count set @classNum = @countset @i=1--设置变量i--循环临时表while @i<=@classNum--班级循环beginset @j = 1--注意：此@j变量一定要放在外循环begin和内循环外部之间，否则无法产生双循环效果while @j<=@n--学生循环beginset @stuId = NEWID()set @sNo = convert(varchar(10), @i) + convert(varchar(10), @j)set @sName = @@sName + convert(varchar(10), @i) + convert(varchar(10), @j)select @classId = Id from #temp where row = @i; --获取班级Idexec sp_Insert_Student @stuId,@sNo,@sName,18,0,@classId,@rtn outputset @j=@j+1endset @i=@i+1enddrop table #temp; --删除临时表end
4.1 创建存储过程-给每个学生分配1个课程的默认100次历史成绩
基本思路
将学生表的所有数据插入到临时表中；外循环``临时表每一条数据，内循环每科成绩的次数；在内循环中针对需要的字段进行赋值并插入数据
use DataPerformanceTestDBgo-- =============================================-- Author: love2zq-- Create date: -09-05-- Description: 给每个学生分配1个课程的默认100次历史成绩-- =============================================create procedure sp_Create_StudentScore@courseId uniqueidentifier,--课程id@times int = 100--每个学生每门课程的成绩次数，默认100次asbegindeclare @stuId varchar(50);--学生iddeclare @count int; --学生数量declare @i int;--循环标识declare @j int;--循环标识declare@scoreId uniqueidentifier;--成绩主键Iddeclare @gradeDate datetime;--历史成绩日期declare @score float; --成绩declare@c_rtn int;--课程表输出提示--把所有学生信息存到临时表select * into #temp from (select Id,ROW_NUMBER() over(order by SNo) as row from Student) aselect @count = COUNT(1) from #temp;--查询临时表总记录数set @i = 1--循环临时表while (@i <= @count)beginset @j = 1--注意：此@j变量一定要放在外循环begin和内循环外部之间，否则无法产生双循环效果while @j<=@times--考试成绩次数beginset @scoreId = NEWID()--自动生成Idselect @stuId = Id from #temp where row = @i;--获取学生Idset @score = cast(floor(rand()*100) as float) --cast将整型转换为float类型数据set @gradeDate = dateadd(DAY,@j,'-01-01')--初始日期为-01-01，加上@j天exec sp_Insert_Score @scoreId,@stuId,@courseId,@gradeDate,@score,@c_rtn outputset @j=@j+1endset @i = @i + 1enddrop table #temp;--删除临时表endgo
5 生成100万数据
由于数据量生成过多，下面的代码执行时间可能过长，需要耐心等待，大概会执行1个小时20分钟左右哦
declare@cl_rtn int,@c_rtn int,@msg nvarchar(100)--输出提示;-----------------------------------------------------------------------------------------------------------------------------------------------------------------*******插入班级数据*******-----------------------------------------------------------------------------------------------------------------------------------------------------------------exec sp_Insert_Class '3DCEA8D9-92D8-484B-BAC1-DB13F0CB347F','GZ0101','高一(1)班','刘齐','13385421639','阳光少年，花样少女',@cl_rtn output--下面输出可加可不加，只是一个提示作用--beginif @cl_rtn=0set @msg='课程表=>' + '3DCEA8D9-92D8-484B-BAC1-DB13F0CB347F' + '：已经存在相同的。'else if @cl_rtn=1set @msg='课程表=>' + '3DCEA8D9-92D8-484B-BAC1-DB13F0CB347F' + '：插入成功。'elseset @msg='课程表=>' + '3DCEA8D9-92D8-484B-BAC1-DB13F0CB347F' + '：更新成功。'print @msg--endexec sp_Insert_Class 'F441C02F-835A-488B-A70F-71FE96655955','GZ0102','高一(2)班','刘吉','13385421638','老司机队伍，勇拿第一',@cl_rtn outputexec sp_Insert_Class '760AE59A-D8A5-4B44-B701-E08500378F74','GZ0103','高一(3)班','李欣颖','13313145201','母语为主，英语为辅',@cl_rtn outputexec sp_Insert_Class '9F28DD25-49CE-4C73-A594-D19B561FD31B','GZ0104','高一(4)班','李国庆','13313145225','党中央路线是群众路线，你们就是党的群众',@cl_rtn outputexec sp_Insert_Class 'B66763ED-70E9-4F1A-90F7-2F23709A5884','GZ0105','高一(5)班','朱明朝','13313149225','历史留给人的教训就是从未从历史中得到任何教训',@cl_rtn outputexec sp_Insert_Class 'C0BAD0C9-757A-44FB-898A-21CB86EE5810','GZ0106','高一(6)班','徐霞客','13313949225','山川大河，地大物博，如画如诗',@cl_rtn outputexec sp_Insert_Class '473D3B0F-B0DB-46A8-A711-31B0CF013924','GZ0107','高一(7)班','屠呦呦','13613949225','救死扶伤乃本职，奈何医者不自医',@cl_rtn outputexec sp_Insert_Class '1338AA6B-4ADD-4C89-80E6-AA90D1969A3D','GZ0108','高一(8)班','侯德榜','13316949225','帝国垄断，国中尚无自给自足？',@cl_rtn outputexec sp_Insert_Class 'B0691073-7232-4EC2-BF05-D7449ADC50E6','GZ0109','高一(9)班','杨振宁','13313949225','一声爆炸，国之骄傲',@cl_rtn outputexec sp_Insert_Class 'E8F75344-0673-493A-8E0D-E54861C603D3','GZ0110','高一(10)班','子非鱼','13313649225','忍耐克己百折不屈',@cl_rtn outputexec sp_Insert_Class 'D078B827-E046-4FD6-8A64-387A420DF767','GZ0201','高二(1)班','刘齐','13385421639','阳光少年，花样少女',@cl_rtn outputexec sp_Insert_Class 'AF5CB351-FDE0-482C-8B00-586C1BC0F0AE','GZ0202','高二(2)班','刘吉','13385421638','老司机队伍，勇拿第一',@cl_rtn outputexec sp_Insert_Class '3FA698FC-4DB8-40FD-A499-59AE804EEA8D','GZ0203','高二(3)班','李欣颖','13313145201','母语为主，英语为辅',@cl_rtn outputexec sp_Insert_Class '5983885B-8104-4F98-B777-37E39E660DF9','GZ0204','高二(4)班','李国庆','13313145225','党中央路线是群众路线，你们就是党的群众',@cl_rtn outputexec sp_Insert_Class '94FFFA6C-0A66-4092-BDBF-5A4F3EE2C7DB','GZ0205','高二(5)班','朱明朝','13313149225','历史留给人的教训就是从未从历史中得到任何教训',@cl_rtn outputexec sp_Insert_Class 'FFB34518-3581-43B5-8BA4-EDB32F036F9F','GZ0206','高二(6)班','徐霞客','13313949225','山川大河，地大物博，如画如诗',@cl_rtn outputexec sp_Insert_Class '0826DCB4-3393-4706-9EF7-79DCD1C88152','GZ0207','高二(7)班','屠呦呦','13613949225','救死扶伤乃本职，奈何医者不自医',@cl_rtn outputexec sp_Insert_Class '28A2CEED-21EC-428F-9F22-0BCC95C14CD7','GZ0208','高二(8)班','侯德榜','13316949225','帝国垄断，国中尚无自给自足？',@cl_rtn outputexec sp_Insert_Class '675214FC-4127-4FFD-81FA-B247FAF00C89','GZ0209','高二(9)班','杨振宁','13313949225','一声爆炸，国之骄傲',@cl_rtn outputexec sp_Insert_Class '2E7C95FA-5F8B-4724-8EC4-49A1081E7F7C','GZ0210','高二(10)班','子非鱼','13313649225','忍耐克己百折不屈',@cl_rtn outputexec sp_Insert_Class 'DD11B770-DC0B-4DA1-BCA3-29045852F497','GZ2001','高三(1)班','刘齐','13385421639','阳光少年，花样少女',@cl_rtn outputexec sp_Insert_Class 'B615B058-E009-4591-8B5C-631965E2816B','GZ2002','高三(2)班','刘吉','13385421638','老司机队伍，勇拿第一',@cl_rtn outputexec sp_Insert_Class 'DCF72A56-2A9E-46CF-BB54-802443A7CCB0','GZ2003','高三(3)班','李欣颖','13313145201','母语为主，英语为辅',@cl_rtn outputexec sp_Insert_Class '1B209C00-34ED-4EF9-8F39-BB8363FB60B2','GZ','高三(4)班','李国庆','13313145225','党中央路线是群众路线，你们就是党的群众',@cl_rtn outputexec sp_Insert_Class '2868A43E-7E0D-4FF0-B8C9-3275AB7F80FC','GZ','高三(5)班','朱明朝','13313149225','历史留给人的教训就是从未从历史中得到任何教训',@cl_rtn outputexec sp_Insert_Class '26B9B50F-4928-43BC-9BF0-8F17FFBF0835','GZ','高三(6)班','徐霞客','13313949225','山川大河，地大物博，如画如诗',@cl_rtn outputexec sp_Insert_Class '4B907A49-BED6-4F54-9036-50DA7B346AAF','GZ','高三(7)班','屠呦呦','13613949225','救死扶伤乃本职，奈何医者不自医',@cl_rtn outputexec sp_Insert_Class '53B2B9AC-592E-4CF8-BD87-27FB082CA454','GZ','高三(8)班','侯德榜','13316949225','帝国垄断，国中尚无自给自足？',@cl_rtn outputexec sp_Insert_Class '9D09218A-6ACB-4087-9C47-7D2265CD80E7','GZ','高三(9)班','杨振宁','13313949225','一声爆炸，国之骄傲',@cl_rtn outputexec sp_Insert_Class '09E4C136-640A-4EE1-B9F7-18446F5E4230','GZ','高三(10)班','子非鱼','13313649225','忍耐克己百折不屈',@cl_rtn output -----------------------------------------------------------------------------------------------------------------------------------------------------------------*******插入课程数据*******-----------------------------------------------------------------------------------------------------------------------------------------------------------------exec sp_Insert_Course '43650D43-CDA5-4F81-934F-2DF9B03038A0','语文','1教1101',@c_rtn output--下面输出可加可不加，只是一个提示作用--beginif @c_rtn=0set @msg='课程表=>' + '43650D43-CDA5-4F81-934F-2DF9B03038A0' + '：已经存在相同的。'else if @c_rtn=1set @msg='课程表=>' + '43650D43-CDA5-4F81-934F-2DF9B03038A0' + '：插入成功。'elseset @msg='课程表=>' + '43650D43-CDA5-4F81-934F-2DF9B03038A0' + '：更新成功。'print @msg--endexec sp_Insert_Course '8C35128B-4AD1-4506-861B-A96F41072FDD','数学','1教1102',@c_rtn outputexec sp_Insert_Course '12A0C937-A570-4E3A-A5A9-D4841387E617','英语','1教1103',@c_rtn outputexec sp_Insert_Course '31AD3945-AB92-4F83-8CB9-19B01389B61F','政治','1教1104',@c_rtn outputexec sp_Insert_Course 'AA571527-97B1-4C3E-A651-ECA8B505B991','历史','1教1105',@c_rtn outputexec sp_Insert_Course 'DC3BB0A9-A046-4900-AFB1-76B850A937D7','地理','1教1106',@c_rtn outputexec sp_Insert_Course '6DA53E65-E564-4D4C-A7A0-966B0EE0FC65','生物','1教1107',@c_rtn outputexec sp_Insert_Course 'F1E0C121-3FFE-4F9C-825E-BB19EC43A4F9','化学','1教1108',@c_rtn outputexec sp_Insert_Course '7529A8CD-403B-45B5-8274-3E352558CF31','物理','1教1109',@c_rtn outputexec sp_Insert_Course 'BCBD9E50-602B-4097-9DD7-8E8CD751AA04','体育','1教1110',@c_rtn output-----------------------------------------------------------------------------------------------------------------------------------------------------------------*******插入学生数据*******-------------------------------------------------------------------------------------------------------------------------------------------------------------------给予每个班100人，30个班即 30*100 = 3000人exec sp_Create_AssignStudent '李思思',100,30-----------------------------------------------------------------------------------------------------------------------------------------------------------------*******插入学生成绩数据*******-------------------------------------------------------------------------------------------------------------------------------------------------------------------给予每个学生每门课程200次历史成绩，共有3000人*10门课*200次=600,0000数据exec sp_Create_StudentScore '43650D43-CDA5-4F81-934F-2DF9B03038A0',200--语文,200次成绩exec sp_Create_StudentScore '8C35128B-4AD1-4506-861B-A96F41072FDD',200--数学,200次成绩exec sp_Create_StudentScore '12A0C937-A570-4E3A-A5A9-D4841387E617',200--英语,200次成绩exec sp_Create_StudentScore '31AD3945-AB92-4F83-8CB9-19B01389B61F',200--政治,200次成绩exec sp_Create_StudentScore 'AA571527-97B1-4C3E-A651-ECA8B505B991',200--历史,200次成绩exec sp_Create_StudentScore 'DC3BB0A9-A046-4900-AFB1-76B850A937D7',200--地理,200次成绩exec sp_Create_StudentScore '6DA53E65-E564-4D4C-A7A0-966B0EE0FC65',200--生物,200次成绩exec sp_Create_StudentScore 'F1E0C121-3FFE-4F9C-825E-BB19EC43A4F9',200--化学,200次成绩exec sp_Create_StudentScore '7529A8CD-403B-45B5-8274-3E352558CF31',200--物理,200次成绩exec sp_Create_StudentScore 'BCBD9E50-602B-4097-9DD7-8E8CD751AA04',200--体育,200次成绩
6 查询数据
--查询关联数据select a.SName,b.ClName,ame,d.CRoom,c.Score from Student aleft join Class b on a.ClassId = b.idleft join Score c on a.Id = c.StudentIdleft join Course d on c.CourseId = d.Id--清空数据delete from Class;delete from Student;delete from Score;delete from Course
如果觉得《【SQL Server】性能优化-数据准备：使用存储过程生成百万数据》对你有帮助，请点赞、收藏，并留下你的观点哦！
