业务场景:任务命中记录列表需要显示每个任务的最新命中时间
命中记录表
CREATE TABLE `t_alarm_notice_all` (
`id` varchar(32) NOT NULL,
`taskId` varchar(125) ,
`alarmType` smallint(4) DEFAULT NULL ,
`objectId` varchar(125) DEFAULT NULL ,
`idCard` varchar(18) DEFAULT NULL ,
`name` varchar(125) DEFAULT NULL ,
`hitType` varchar(20) NOT NULL ,
`hitValue` varchar(200) NOT NULL,
`model` smallint(4) DEFAULT NULL ,
`sml` decimal(10,10) DEFAULT NULL,
`ceid` varchar(125) DEFAULT NULL ,
`cead` varchar(250) DEFAULT NULL ,
`lon` varchar(20) DEFAULT NULL ,
`lat` varchar(20) DEFAULT NULL ,
`latm` datetime DEFAULT NULL ,
`imtm` datetime NOT NULL COMMENT ,
`rowkey` text,
`gathers` text ,
`childId` text ,
`taskName` varchar(255) DEFAULT NULL ,
`faceImgPath` varchar(200) DEFAULT NULL,
`bgImgPath` varchar(200) DEFAULT NULL,
`engineMatchInfos` varchar(1000) DEFAULT NULL,
`groupId` varchar(255) DEFAULT NULL ,
`snapshotId` varchar(255) DEFAULT NULL ,
`engineMatchList` text,
`originHitValue` varchar(200) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_hitType` (`hitType`) USING BTREE,
KEY `idx_ceid` (`ceid`) USING BTREE,
KEY `idx_taskId` (`taskId`) USING BTREE,
KEY `idx_latm` (`latm`,`taskId`) USING BTREE,
KEY `idx_hitvalue` (`hitValue`) USING BTREE,
KEY `idx_object` (`alarmType`,`objectId`) USING BTREE,
KEY `idx_union` (`hitValue`,`objectId`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC ;
SET FOREIGN_KEY_CHECKS = 1;
sql语句
select
t.taskId,
max(t.latm) as latm
from
t_alarm_notice_all t
group by
t.taskId
sql语句很简单,就是根据任务id进行分组然后取出同组中命中时间最大的一条记录。
在单表70w的记录下,这条语句执行的时间达到了15秒
使用 optimizer_trace
分析大法,用法详情参考 mysql优化之为什么我limit10也会全表扫描.
分析发现扫描的行数高达 1千1百万条。
explain 命令分析语句发现使用的索引是 taskId
流程分析
- mysql 遍历 taskid索引,taskid大概有10条,总数差不多为70万条。
- 获得每个taskid索引下面的主键id去主键索引中拿到latm的值存入sort_buffer中。
- 重复12操作,或得到不同taskid对应的一个数据列表。
- 将sort_buffer中的数据进行排序,取出最大数据。
这里不确定是不是把所有输出存入sort_buffer中。因为最大值直接可以再扫描的时候就对于同一个taskid取数据的时候判断最大或最小值
耗时的操作主要集中在 1 2之间。如果能在taskid索引下面就能直接获得已经排序好的latm,就不需要234的操作。
建立联合索引 idx_task_latm
ALTER TABLE `t_alarm_notice_all` ADD INDEX idx_taskid_latm(taskId,latm );
添加了索引之后的查询逻辑变为
1 . 遍历 idx_taskid_latm 索引,找到一个taskid,取出该id下的最后一条数据,直接放入结果集中。
2 . 遍历下一条索引,结束
查询结果为 0.25秒。
总结
group by 的字段需要添加索引,而且必须是开头,因为其他业务的需要,这张表本身有 latm_taskid的索引,但是对于 group by taskid 的操作,latm_taskid 的索引并没有起作用.