记录工作过程中一次业务优化

1需求

用户需要输入身份证和姓名进行登录，登录时需要判断是否存在在数据库存在，登录成功后需要记录登录的信息以及微信Id，如果用到不同的微信账号，需要记录多次记录。

2设计

2.1数据库设计

CREATE TABLE `ExamDefine`  (   `Id` varchar(36) NOT NULL COMMENT '主键编码',   `ExamName` varchar(100) NOT NULL COMMENT '任务名称',   PRIMARY KEY (`Id`) ); CREATE TABLE `ExamStu`  (   `Id` varchar(36) NOT NULL COMMENT '主键编码',   `ExamDefineId` varchar(36) NOT NULL COMMENT '任务编码',   `StuName` varchar(100) NOT NULL COMMENT '考生姓名',   `CertificateType` int NOT NULL COMMENT '证件类型',   `IdentificationID` varchar(50) NOT NULL COMMENT '证件号码',   PRIMARY KEY (`Id`) ); CREATE TABLE `StuLogin`  (   `Id` varchar(36) NOT NULL COMMENT '主键编码',   `StuName` varchar(100) NOT NULL COMMENT '考生姓名',   `IdentificationID` varchar(50) NOT NULL COMMENT '证件号码',   `LoginTime` datetime NOT NULL COMMENT '登录时间',   `wxId` varchar(100) NOT NULL COMMENT '微信Id',   PRIMARY KEY (`Id`) ); ALTER TABLE `ExamStu` ADD CONSTRAINT `ExamDefineId` FOREIGN KEY (`ExamDefineId`) REFERENCES `ExamDefine` (`Id`); 

2.2业务流程

2.3业务统计需求

1.统计任务考生人数，已登录人数，未登录人数，多账号登录人数
2.按照曲线方式统计每天登录人数、累计登录人数

2.3.1Sql语句

• 统计任务考生人数，已登录人数，未登录人数，多账号登录人数

SELECT 	count( 1 ) StuCount, 	Sum( LoginCount > 0 ) LoginCount, 	Sum( LoginCount > 1 ) MultipleLogins FROM 	ExamStu 	LEFT JOIN ( SELECT IdentificationID, count( 1 ) LoginCount FROM StuLogin GROUP BY IdentificationID ) lg ON ExamStu.IdentificationID = lg.IdentificationID  WHERE 	ExamDefineId = @ExamDefineId 

ExamStu表120w记录,StuLogin表60w记录,多账号登录6w执行需要4s多(数据仅供参考)

• 按照曲线方式统计每天登录人数、累计登录人数

 SELECT 	ROW_NUMBER ( ) Over ( ORDER BY LoginTime DESC ) AS Sort, 	LoginTime, 	Count( lg.RepeatCount > 0 ) AS LoginCount  FROM 	ExamStu 	LEFT JOIN ( SELECT IdentificationID, DATE_FORMAT(Max(LoginTime), '%Y-%m-%d %H') AS LoginTime, Count( * ) AS RepeatCount FROM StuLogin GROUP BY IdentificationID ) lg ON ExamStu.IdentificationID = lg.IdentificationID  WHERE 	ExamStu.ExamDefineId = @ExamDefineId  GROUP BY 	LoginTime  ORDER BY 	LoginTime DESC 

两个表关联，又使用内置函数，需要的时间也比较久

2.4优化思路

• 减少表关联,能尽量在一个表中统计就在一个表中进行统计
• 减少函数使用，考虑增加冗余字段

2.4.1修改后表数据库设计

在ExamStu表中增加登录时间(LoginTime yyyy-MM-dd HH格式),增加登录次数，默认登录次数为0。

2.4.2修改后流程数据

考生登录时，同步更新考生表中登录时间和登录次数

2.4.3修改后Sql语句

• 统计任务考生人数，已登录人数，未登录人数，多账号登录人数

SELECT 	count( 1 ) StuCount, 	Sum( LoginCount > 0 ) LoginCount, 	Sum( LoginCount > 1 ) MultipleLogins FROM 	ExamStu WHERE 	ExamDefineId = @ExamDefineId 

• 按照曲线方式统计每天登录人数、累计登录人数

 SELECT 	ROW_NUMBER ( ) Over ( ORDER BY LoginTime DESC ) AS Sort, 	LoginTime, 	Count( LoginCount > 1 ) AS LoginCount  FROM 	ExamStu WHERE 	ExamStu.ExamDefineId = @ExamDefineId  GROUP BY 	LoginTime  ORDER BY 	LoginTime DESC 

总结

大部分人面对的编程不复杂，多注意细节。