記一次有意思的 SQL 實現 → 分組後取每組的第一條記錄

開心一刻今天，朋友氣衝衝的走到我面前朋友：我不是談了個女朋友，談了三個月嘛，昨天我偷看她手機，你猜她給我備註什麼我：備註什麼？朋友：舔狗 2 號！我一聽，氣就上來了，說道：走，找她去，這婆娘確實該罵，臭不要臉的朋友拉住我，勸到：哎哎，不是去罵她，是找她理論，叫她改成舔狗1號，是我先來的！ ...

開心一刻

　　今天，朋友氣衝衝的走到我面前

　　朋友：我不是談了個女朋友，談了三個月嘛，昨天我偷看她手機，你猜她給我備註什麼

　　我：備註什麼？

　　朋友：舔狗 2 號！

　　我一聽，氣就上來了，說道：走，找她去，這婆娘確實該罵，臭不要臉的

　　朋友拉住我，勸到：哎哎，不是去罵她，是找她理論，叫她改成舔狗1號，是我先來的！

　　我：滾，我不認識你

需求背景

　　環境

　　 MySQL 版本：8.0.27

　　有四張表：業務信息表、任務表、業務任務表、任務執行日誌表

CREATE TABLE `t_business` (
  `business_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '業務id',
  `business_name` VARCHAR(100) NOT NULL COMMENT '業務名',
  `note` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '備註',
  `create_user` BIGINT(20) NOT NULL COMMENT '創建人',
  `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間',
  `modify_user` BIGINT(20) NOT NULL COMMENT '最終修改人',
  `modify_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最終修改時間',
  PRIMARY KEY (`business_id`) USING BTREE
) ENGINE=InnoDB COMMENT='業務信息';

CREATE TABLE `t_task` (
  `task_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '任務id',
  `task_name` VARCHAR(100) NOT NULL COMMENT '業務名',
  `note` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '備註',
  `create_user` BIGINT(20) NOT NULL COMMENT '創建人',
  `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間',
  `modify_user` BIGINT(20) NOT NULL COMMENT '最終修改人',
  `modify_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最終修改時間',
  PRIMARY KEY (`task_id`) USING BTREE
) ENGINE=InnoDB COMMENT='任務信息';

CREATE TABLE `t_business_task` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
    `business_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '業務id',
  `task_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '任務id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='業務任務關係';

CREATE TABLE `t_task_exec_log` (
  `log_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日誌id',
  `task_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '任務id',
  `exec_status` VARCHAR(50) NOT NULL COMMENT '執行狀態, 失敗：fail，成功：success',
    `data_date` DATE NOT NULL COMMENT '數據日期',
  `note` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '備註',
  `create_user` BIGINT(20) NOT NULL COMMENT '創建人',
  `create_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '創建時間',
  `modify_user` BIGINT(20) NOT NULL COMMENT '最終修改人',
  `modify_time` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最終修改時間',
  PRIMARY KEY (`log_id`) USING BTREE
) ENGINE=InnoDB COMMENT='任務執行日誌';

View Code

　　它們關係如下

　　一個業務下有多個任務，一個任務又可以屬於不同的業務；同個業務下，一個任務最多關聯一次

　　任務每執行一次就會生成一條執行日誌；執行日誌的數據日期小於等於任務執行的當前日期，比如昨天執行的任務的數據日期可以是前天的

　　四張表的數據量分別如下

　　需求

　　按業務分頁，每個業務可以展開顯示關聯的任務信息以及任務最新的執行成功信息

　　任務最新的執行成功信息：狀態成功，數據日期最大的那條執行日誌信息；如果數據日期一致，則取最終修改時間最大的

　　後端返回的 JSON 數據類似如下

實現方式

　　先分頁查業務和任務，再根據任務id迴圈查最新的執行成功信息

　　1、關聯查詢業務和任務

　　　　如果查詢條件帶任務信息（任務ID，任務名），那麼 t_business 需要關聯 t_business_task 、 t_task 來查

　　　　因為這三張表的數據量都比較小，聯表查沒什麼問題

　　2、根據上一步查到的 task_id 集逐個去查 t_task_exec_log

　　　　 SQL 類似如下

　　　　可以建個組合索引 idx_status_task_date_modify(exec_status,task_id,data_date,modify_time)

　　3、將第 1、2 步的數據進行組合

　　　　將任務的最新執行成功信息添加到任務信息中

　　邏輯非常清晰，代碼實現起來也非常簡單

　　但是，一個任務id就查一次資料庫，這顯然是有很大性能問題的（一般，公司的開發規範內都會有一條：禁止迴圈查資料庫）

　　先分頁查業務和任務，再根據任務id批量查最新的執行成功信息

　　1、關聯查詢業務和任務

　　2、根據第 1 步查到的任務id集批量查 t_task_exec_log

　　　　因為這是多個任務一起查，也就沒法用 LIMIT 1 了

　　　　那如何查出每個任務的最新執行成功的那一條記錄了？

　　　　這裡也就對應了文章的標題：分組後取每組的第 1 條記錄

　　　　實現方式其實有很多，我這裡提供一種，如下

　　　　結合索引 idx_status_task_date_modify(exec_status,task_id,data_date,modify_time) ，查詢速度還行

　　　　大家細看這個 SQL ，是不是發現了有意思的東西：GROUP_CONCAT(log_id ORDER BY data_date DESC,modify_time DESC)

　　　　是不是知識盲區，是不是有點東西？

　　3、將第 1、2 步的數據進行組合

　　新增任務最新執行成功記錄表

　　一般而言，大數據量的日誌表是不參與複雜查詢的，所以單獨拎出來一個表，專門記錄任務最新執行成功信息

　　一個任務最多只有一條記錄，不存在則直接插入表中，存在則根據 data_date DESC,modify_time DESC 與表中記錄做比較，看是否需要進行表中記錄更新

　　因為一個任務最多只有一條記錄，那麼 t_task_latest_exec_log 的數據量是小於等於 t_task 的數據量的，也就是說數據量不大

　　那麼用一個 SQL 就可以實現業務（直接聯表 t_business 、 t_business_task 、 t_task 、 t_task_latest_exec_log ）

　　然後在後端代碼中進行數據格式的處理，返回前端需要的格式

　　新增表後，其初始數據該如何導入了？

總結

　　1、大家寫 SQL 的時候，一定要多結合執行計劃來寫

　　　　神奇的 SQL 之 MySQL 執行計劃 → EXPLAIN，讓我們瞭解 SQL 的執行過程！

　　2、 t_task_latest_exec_log 初始數據的導入

　　　　其實比較簡單，如下所示

INSERT INTO t_task_latest_exec_log(task_id,data_date,create_user,create_time,modify_user,modify_time)
SELECT t2.task_id, t2.data_date, t2.create_user, t2.create_time, t2.modify_user,t2.modify_time FROM (
    SELECT SUBSTRING_INDEX(GROUP_CONCAT(log_id ORDER BY data_date DESC,modify_time DESC),',',1) log_id
    FROM t_task_exec_log
    WHERE exec_status = 'success'
    GROUP BY task_id
) t INNER JOIN t_task_exec_log t2 ON t.log_id = t2.log_id;

View Code

　　　　一定要去執行，你會發現大驚喜！

　　3、多和同事溝通，多和需求方溝通

　　　　多和同事溝通，集思廣益，說不定就找到合適的解決方案了

　　　　多和需求方溝通，多談談個人的見解，也許需求改動一丟丟，但我們實現卻容易很多

　　4、留疑

　　　　1、分組後如何取前 N 條

　　　　2、分組後如何取倒數 N 條