sql 记一次sql写方法优化：查询每个分组最新的记录 2020-11-3

biz_cloudsign_login是登录记录，表数据28万。
需求是：查询每个人最新登录记录。

表结构

CREATE TABLE `biz_cloudsign_login` (
`id`  bigint(20) NOT NULL AUTO_INCREMENT ,
`business_system_code`  int(11) NOT NULL ,
`user_department`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`employee_num`  varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`user_name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`identity_number`  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`client_id`  varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`client_ip`  varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`random_num`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`cert_id`  varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`encrypted_token`  longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`updated_at`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`),
INDEX `index_eml` (`id`, `employee_num`) USING BTREE ,
INDEX `index_employee_num` (`employee_num`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=289464
ROW_FORMAT=DYNAMIC
;

思路：
1、使用子查询先将目标表的 id,employee_num 查询出来，根据id倒叙排列（id自增)
2、再使用id进行自关联查询，按employee_num 分组

SELECT
    b.* 
FROM
    ( SELECT id,employee_num FROM biz_cloudsign_login ORDER BY id DESC ) a join biz_cloudsign_login b ON a.id = b.id
GROUP BY
    a.employee_num  order by null

添加了相应索引的最优执行计划

image.png

是先使用SELECT id,employee_num FROM biz_cloudsign_login ORDER BY id DESC 将所有的id,employee_num 记录查询出来，尽管加上了索引。但是由于是全索引的扫描。也是非常慢的。事实上这里查询出了我们本不需要的数据：旧的登录记录

优化写法

巧妙组合使用 MAX( id )和按 employee_num分组直接达到了排序的目的，因为这个表的id是int类型且自增。那么SELECT MAX( id ) 'id' FROM biz_cloudsign_login GROUP BY employee_num 就直接查询出了最新的每个人的登录记录id。然后再关联查询就好了！这种方式就比上面的方式扫描的数据少了很多


    SELECT
        v.* 
    FROM
        biz_cloudsign_login v
        INNER JOIN ( SELECT MAX( id ) 'id' FROM biz_cloudsign_login GROUP BY employee_num ) c ON v.id = c.id

查看执行计划

image.png

比起上面的查询，这条sql只需要扫描 1571+1571 三千条数据就能得到执行结果。而上面却要将整个表扫描一遍，那就是28万！

若数据量实在是太大，我们可以根据服务器配置酌情调大buff pool。会有显著的优化效果。具体看看这篇
https://www.jianshu.com/p/5bf36975fd73

SET GLOBAL innodb_buffer_pool_size = 6442450944 -- 6G

最后编辑于：2020.11.06 10:35:35

sql 记一次sql写方法优化： 查询每个分组最新的记录 2020-11-3

优化写法

sql 记一次sql写方法优化：查询每个分组最新的记录 2020-11-3