目录1 数据准备1.1 新建数据表1.2 新增100万条数据2 基础知识2.1 explain type2.2 explain Extra3 索引失效场景3.1 查询类型错误3.1.1 失效场景3.1.2 解决方案3.2 索引列参与运算3.2.1 失效场景3.2.2 解决方案3.3 MySQL放弃使用索引3.3.1 失效场景3.3.2 解决方案一3.3.3 解决方案二3.4 错误使用通配符3.4.1 数据准备3.4.2 失效场景一3.4.3 失效场景二3.4.4 解决方案3.5 OR连接无索引字段3.5.1 失效场景3.5.2 解决方案3.6 未用到覆盖索引3.6.1 失效场景3.6.2 解决方案3.7 联合索引失效3.7.1 完整使用3.7.2 失效场景一：索引不完整3.7.3 失效场景二：索引中断3.7.4 失效场景三：非等值匹配3.7.5 失效场景四：最左索引缺失4 文章总结
CREATE TABLE `player` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
`player_id` varchar(256) NOT NULL COMMENT ‘运动员编号’,
`player_name` varchar(256) NOT NULL COMMENT ‘运动员名称’,
`height` int(11) NOT NULL COMMENT ‘身高’,
`weight` int(11) NOT NULL COMMENT ‘体重’,
`type` varchar(256) DEFAULT ‘0’ COMMENT ‘球员类型’,
`game_performance` text COMMENT ‘最近一场比赛表现’,
PRIMARY KEY (`id`),
KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),
KEY `idx_type` (`type`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
`player_id` varchar(256) NOT NULL COMMENT ‘运动员编号’,
`player_name` varchar(256) NOT NULL COMMENT ‘运动员名称’,
`height` int(11) NOT NULL COMMENT ‘身高’,
`weight` int(11) NOT NULL COMMENT ‘体重’,
`type` varchar(256) DEFAULT ‘0’ COMMENT ‘球员类型’,
`game_performance` text COMMENT ‘最近一场比赛表现’,
PRIMARY KEY (`id`),
KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),
KEY `idx_type` (`type`),
KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
以上数据表声明三个索引：
联合索引：idx_name_height_weight普通索引：idx_type普通索引：idx_height
@SpringBootTest(classes=TestApplication.class)
@RunWith(SpringJUnit4ClassRunner.class)
public class PlayerServiceTest {
@RunWith(SpringJUnit4ClassRunner.class)
public class PlayerServiceTest {
@Resource
private PlayerRepository playerRepository;
@Test
public void initBigData() {
for (int i=0; i < 1000000; i++) {
PlayerEntity entity=new PlayerEntity();
entity.setPlayerId(UUID.randomUUID().toString());
entity.setPlayerName(“球员_” + System.currentTimeMillis());
entity.setType(“0”);
entity.setWeight(150);
entity.setHeight(188);
entity.setGamePerformance(“{“runDistance”:8900.0,”passSuccess”:80.12,”scoreNum”:3}”);
playerRepository.insert(entity);
}
}
}
执行计划中访问类型是重要分析指标：
Extra表示执行计划扩展信息：
本章节介绍索引失效十种场景：
查询类型错误索引列参与运算错误使用通配符未用到覆盖索引OR连接无索引字段MySQL放弃使用索引联合索引失效索引不完整索引中断非等值匹配最左索引缺失
3.1.1 失效场景
explain select * from player where type=0
3.1.2 解决方案
数据表定义字段为类型，查询必须使用相同类型：
3.2.1 失效场景
explain select * from player where height + 1 > 189
3.2.2 解决方案
explain select * from player where height > 188
3.3.1 失效场景
MySQL发现如果使用索引性能低于全表扫描则放弃使用索引。例如在表中100万条数据字段值全部是，所以执行如下语句时放弃使用索引：
explain select * from player where height > 187
3.3.2 解决方案一
调整查询条件值：
explain select * from player where height > 188
3.3.3 解决方案二
强制指定索引，这种方法不一定可以提升性能：
3.4.1 数据准备
避免出现3.3章节失效问题此处修改一条数据：
update player set player_name=’测试球员’ where id=1
3.4.2 失效场景一
explain select * from player where player_name like ‘%测试’
3.4.3 失效场景二
explain select * from player where player_name like ‘%测试%’
3.4.4 解决方案
explain select * from player where player_name like ‘测试%’
3.5.1 失效场景
有索引，无索引：
explain select * from player where type=’0′ or weight=150
3.5.2 解决方案
新增索引，拼装查询数据
explain
select * from player where type=’0′
union
select * from player where weight=150
select * from player where type=’0′
union
select * from player where weight=150
3.6.1 失效场景
表示使用索引，但是需要回表查询
explain select * from player where player_name like ‘测试%’
3.6.2 解决方案
覆盖索引含义是查询时索引列完全包含查询列，查询过程无须回表（需要在同一棵索引树）性能得到提升。表示使用覆盖索引并且用过滤查询结果：
explain select id,player_name,height,weight from player where player_name like ‘测试%’
3.7.1 完整使用
联合索引完整使用=778：
explain select * from player where player_name=’球员_1682577684751′ and height=188 and weight=150
3.7.2 失效场景一：索引不完整
不在查询条件，所以只用到，所以=774：
explain select * from player where player_name=’球员_1682577684751′ and height=188
3.7.3 失效场景二：索引中断
不在查询条件，所以只用到，所以=770：
explain select * from player where player_name=’球员_1682577684751′ and weight=150
3.7.4 失效场景三：非等值匹配
非等值匹配，所以只用到，所以=774：
explain select * from player where player_name=’球员_1682577684751′ and height > 188 and weight=150
3.7.5 失效场景四：最左索引缺失
最左索引不在查询条件，全表扫描
explain select * from player where weight=150
本文第一进行测试数据准备，第二介绍执行计划相关知识，第三介绍索引失效10种场景：查询类型错误，索引列参与运算，错误使用通配符，未用到覆盖索引，OR连接无索引字段，MySQL放弃使用索引，联合索引中索引不完整，索引中断，非等值匹配，最左索引缺失。
以上就是MySQL索引失效十种场景与优化方案的详细内容，更多关于MySQL索引失效的资料请关注脚本之家其它相关文章！
您可能感兴趣的文章:MySQL索引优化之不适合构建索引及索引失效的几种情况详解MySQL索引失效的几种情况小结MySQL索引失效原因以及SQL查询语句不走索引原因详解MySQL索引命中与失效代码实现MySQL数据库索引原理及优化策略
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
