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

原创

用户2825890

发布于 2022-12-11 16:04:16

1K0

发布于 2022-12-11 16:04:16

文章被收录于专栏：点滴随笔

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

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

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

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='报警表';

查询语句为：

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"

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根据最左原则未命中索引（虽然创建了），这就导致关联查询不能命中索引。调整关联表的索引----增加索引：

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

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

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

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

alarm