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
网友评论