mysql优化之如何用复合索引将查询从15秒达到0.025秒

业务场景：任务命中记录列表需要显示每个任务的最新命中时间
命中记录表

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
流程分析

1. mysql 遍历 taskid索引，taskid大概有10条，总数差不多为70万条。
2. 获得每个taskid索引下面的主键id去主键索引中拿到latm的值存入sort_buffer中。
3. 重复12操作，或得到不同taskid对应的一个数据列表。
4. 将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 的索引并没有起作用.