記錄工作過程中一次業務最佳化

小吴的成长之路發表於2024-03-19

1需求

使用者需要輸入身份證和姓名進行登入，登入時需要判斷是否存在在資料庫存在，登入成功後需要記錄登入的資訊以及微信Id，如果用到不同的微信賬號，需要記錄多次記錄。

2設計

2.1資料庫設計

img

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業務流程

img

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修改後表資料庫設計

img
在ExamStu表中增加登入時間(LoginTime yyyy-MM-dd HH格式),增加登入次數，預設登入次數為0。

2.4.2修改後流程資料

img
考生登入時，同步更新考生表中登入時間和登入次數

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

總結

大部分人面對的程式設計不復雜，多注意細節。

相關文章