Mysql join left查询无法命中索引一例
2023-06-13 09:15:50 时间
在一个查询日志中发现有慢查询,但相关的表都有索引,通过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
相关文章
- MySQL Error number: 3941; Symbol: ER_ALTER_CONSTRAINT_ENFORCEMENT_NOT_SUPPORTED; SQLSTATE: HY000 报错 故障修复 远程处理
- MySQL:掌握完整的配置信息(mysql配置信息)
- MySQL查询进程:深入解析(mysql查询进程)
- 效率分表后MySQL查询性能大幅提升(mysql分表后查询)
- 优化MySQL表的索引使用方法(mysql表的索引)
- MySQL实现城市信息存储的表结构(mysql城市数据表)
- MySQL表关联查询:最佳实践.(mysql表关联查询)
- 了MySQL: 无法正常启动(mysql起不来)
- MySQL 合并查询:精彩实例分享(mysql合并查询)
- MySQL实现快速全文索引查询(mysql全文索引查询)
- MySQL中的二进制数据存储方式(mysql二进制数据)
- 连接Mysql提示Can’t connect to local MySQL server through socket的解决方法
- 如何查询MySQL数据库中的表个数?(mysql查询表个数)
- MySQL多线程访问:加速查询效率的关键技术(mysql多线程访问)
- MySQL建立联合索引的简明指南(mysql建联合索引)
- MySQL深入探究:获取表的注释(mysql获取表的注释)
- MySQL分区表建索引:优化方案分析(mysql分区表建索引)
- MySQL中使用OR语句进行多条件查询(mysql中使用or)
- 如何使用cmd命令行调出MySQL(cmd调出mysql)
- 检查MySQL本地安装情况使用CMD(cmd检查mysql安装)
- ABP从SQL Server变更为MySQL(abp更改为mysql)
- MySQL开始中午支持优化数据库查询效率(mysql中午支持)
- 如何设置MySQL一般日志25字浓缩如下MySQL一般日志配置详解(mysql一般日志配置)
- MySQL优化技巧如何使用不定索引提升效率(mysql不定索引)
- MySQL多表查询实现高效数据检索(mysql 不同表查询)
- MySQL下载无法找到64位安装包(mysql下载没有64位)
- MySQL双层树查询快速定位数据位置(mysql两层树查询)