Mysql join left查询无法命中索引一例

在一个查询日志中发现有慢查询，但相关的表都有索引，通过EXPLAIN，发现并未命中索引

Alarm表，查询主表。关联查询预计的索引为motorcadeId

代码语言：javascript复制

CREATE TABLE `user_motorcade` (   
`userId` bigint(20) NOT NULL COMMENT '角色ID',   
`motorcadeId` int(10) NOT NULL DEFAULT '0' COMMENT '车队ID',   
`isDelete` int(1) DEFAULT '0' COMMENT '是否删除(0 存在  1 删除)',   
`type` int(2) DEFAULT '0' COMMENT '权限类型',   
KEY `motorcadeId` (`motorcadeId`,`userId`,`isDelete`) USING BTREE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户 车辆权限表';

用户车队表，查询关联表。关联查询预计的索引为motorcadeId

代码语言：javascript复制

CREATE TABLE `alarm` (  
`id` bigint(20) NOT NULL AUTO_INCREMENT, 
`motorcadeId` int(10) NOT NULL DEFAULT '0', 
`startTime` datetime DEFAULT NULL COMMENT '开始时间'  
KEY `m_idx` (`motorcadeId`) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='报警表';

查询语句为：

代码语言：javascript复制

EXPLAIN
SELECT
	a.STATUS,
	a.moveMileage,
	a.iccid,
	a.startTime
FROM
	alarm a 
	LEFT JOIN user_motorcade um  ON a.motorcadeId = um.motorcadeId 
WHERE
 	um.userId = 1 
	AND um.isDelete = 0
	AND	a.startTime BETWEEN "2022-11-10 00:00:00" 
	AND "2022-12-10 15:20:26" 

代码语言：javascript复制

1	SIMPLE	um		ref	motorcadeId,userId	userId	13	const,const	15	100.00	Using where
1	SIMPLE	a		ALL	id_starttime,iccid_3,stime,m_idx,stm_idx			 	2948496	50.00	Using where

查询计划告诉我们，Alarm虽然创建了索引，但并未命中。但两个表分开以motorcadeId作为条件时，是可以命中索引的。问题出在，关联表的isDelete和userId根据最左原则未命中索引（虽然创建了），这就导致关联查询不能命中索引。调整关联表的索引----增加索引：

代码语言：javascript复制

 KEY `userId` (`userId`,`isDelete`)

此时关联表命中了索引，关联查询主表也命中索引。优化成功

代码语言：javascript复制

1	SIMPLE	um		ref	motorcadeId,userId	userId	13	const,const	15	100.00	Using where
1	SIMPLE	a		ref	id_starttime,iccid_3,stime,m_idx,stm_idx	m_idx	4	tbox.um.motorcadeId	48496	50.00	Using where

alarm 索引 优化

0 人点赞