Mysql join left查询无法命中索引一例
2023-02-18 15:49:48 时间
在一个查询日志中发现有慢查询,但相关的表都有索引,通过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
相关文章
- T-SQL程序练习02
- T-SQL程序练习03
- 【MySQL系列】- MySQL执行计划一览
- 安卓开发过程中的RatingBar、Handler以及GPS在大型项目中的使用【Android】
- DVWA漏洞演练平台 - SQL注入
- 审计一套CMS中的SQL注入
- mysql 函数: find_in_set()、instr()
- PHP 编程SQL注入问题与代码
- Redis 数据库配置与应用
- MySQL 之基础命令(精简笔记)
- MySQL 之高级命令(精简笔记)
- MySQL 之单表查询(精简笔记)
- MySQL 权限与备份管理(精简笔记)
- MySQL 存储过程与函数(精简笔记)
- PHP SQL 注入代码审计
- 教你快速实现“基于Docker快速构建基于Prometheus的MySQL监控系统”
- MySQL 字符串与时间操作函数
- 客快物流大数据项目(九十七):ClickHouse的SQL语法
- MySQL 索引与性能调优
- PHP 应用PDO技术操作数据库