目录

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 文章总结

1 数据准备

1.1 新建数据表

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

以上数据表声明三个索引：

(资料图片)

联合索引：idx_name_height_weight普通索引：idx_type普通索引：idx_height

1.2 新增100万条数据

@SpringBootTest(classes = TestApplication.class)
@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);
        }
    }
}

2 基础知识

2.1 explain type

执行计划中访问类型是重要分析指标：

2.2 explain Extra

Extra表示执行计划扩展信息：

3 索引失效场景

本章节介绍索引失效十种场景：

查询类型错误索引列参与运算错误使用通配符未用到覆盖索引OR连接无索引字段MySQL放弃使用索引联合索引失效索引不完整索引中断非等值匹配最左索引缺失

3.1 查询类型错误

3.1.1 失效场景

explain select * from player where type = 0

3.1.2 解决方案

数据表定义type字段为varchar类型，查询必须使用相同类型：

3.2 索引列参与运算

3.2.1 失效场景

explain select * from player where height + 1 > 189

3.2.2 解决方案

explain select * from player where height > 188

3.3 MySQL放弃使用索引

3.3.1 失效场景

MySQL发现如果使用索引性能低于全表扫描则放弃使用索引。例如在表中100万条数据height字段值全部是188，所以执行如下语句时放弃使用索引：

explain select * from player where height > 187

3.3.2 解决方案一

调整查询条件值：

explain select * from player where height > 188

3.3.3 解决方案二

强制指定索引，这种方法不一定可以提升性能：

3.4 错误使用通配符

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 OR连接无索引字段

3.5.1 失效场景

type有索引，weight无索引：

explain select * from player where type = "0" or weight = 150

3.5.2 解决方案

weight新增索引，union拼装查询数据

explain
select * from player where type = "0" 
union
select * from player where weight = 150

3.6 未用到覆盖索引

3.6.1 失效场景

Using index condition表示使用索引，但是需要回表查询

explain select * from player where player_name like "测试%"

3.6.2 解决方案

覆盖索引含义是查询时索引列完全包含查询列，查询过程无须回表（需要在同一棵索引树）性能得到提升。Using Index; Using where表示使用覆盖索引并且用where过滤查询结果：

explain select id,player_name,height,weight from player where player_name like "测试%"

3.7 联合索引失效

3.7.1 完整使用

联合索引idx_name_height_weight完整使用key_len=778：

explain select * from player where player_name = "球员_1682577684751" and height = 188 and weight = 150

3.7.2 失效场景一：索引不完整

weight不在查询条件，所以只用到idx_name_height，所以key_len= 774：

explain select * from player where player_name = "球员_1682577684751" and height = 188

3.7.3 失效场景二：索引中断

height不在查询条件，所以只用到idx_name，所以key_len= 770：

explain select * from player where player_name = "球员_1682577684751" and weight = 150

3.7.4 失效场景三：非等值匹配

height非等值匹配，所以只用到idx_name_height，所以key_length=774：

explain select * from player where player_name="球员_1682577684751" and height > 188 and weight = 150

3.7.5 失效场景四：最左索引缺失

player_name最左索引不在查询条件，全表扫描

explain select * from player where weight = 150

4 文章总结

本文第一进行测试数据准备，第二介绍执行计划相关知识，第三介绍索引失效10种场景：查询类型错误，索引列参与运算，错误使用通配符，未用到覆盖索引，OR连接无索引字段，MySQL放弃使用索引，联合索引中索引不完整，索引中断，非等值匹配，最左索引缺失。

以上就是MySQL索引失效十种场景与优化方案的详细内容，更多关于MySQL索引失效的资料请关注脚本之家其它相关文章！