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

执行计划中拜访类型是重要分析目标：

MySQL索引失效十种场景与优化方案


2.2 explain Extra

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

MySQL索引失效十种场景与优化方案


3 索引失效场景

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

  • 查询类型过错
  • 索引列参加运算
  • 过错运用通配符
  • 未用到掩盖索引
  • OR衔接无索引字段
  • MySQL抛弃运用索引
  • 联合索引失效
    • 索引不完好
    • 索引中止
    • 非等值匹配
    • 最左索引缺失

3.1 查询类型过错

3.1.1 失效场景

explain select * from player where type = 0

MySQL索引失效十种场景与优化方案


3.1.2 解决方案

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

explain select * from player where type = '0'

MySQL索引失效十种场景与优化方案


3.2 索引列参加运算

3.2.1 失效场景

explain select * from player where height + 1 > 189

MySQL索引失效十种场景与优化方案


3.2.2 解决方案

explain select * from player where height > 188

MySQL索引失效十种场景与优化方案


3.3 MySQL抛弃运用索引

3.3.1 失效场景

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

explain select * from player where height > 187

MySQL索引失效十种场景与优化方案


3.3.2 解决方案一

调整查询条件值：

explain select * from player where height > 188

MySQL索引失效十种场景与优化方案


3.3.3 解决方案二

强制指定索引，这种办法不一定能够提高功能：

explain select * from player force index(idx_height) where height > 187

MySQL索引失效十种场景与优化方案


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 '%测验'

MySQL索引失效十种场景与优化方案


3.4.3 失效场景二

explain select * from player where player_name like '%测验%'

MySQL索引失效十种场景与优化方案


3.4.4 解决方案

explain select * from player where player_name like '测验%'

MySQL索引失效十种场景与优化方案


3.5 OR衔接无索引字段

3.5.1 失效场景

type有索引，weight无索引：

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

MySQL索引失效十种场景与优化方案


3.5.2 解决方案

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

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

MySQL索引失效十种场景与优化方案


3.6 未用到掩盖索引

3.6.1 失效场景

Using index condition表明运用索引，但是需要回表查询

explain select * from player where player_name like '测验%'

MySQL索引失效十种场景与优化方案


3.6.2 解决方案

掩盖索引含义是查询时索引列彻底包含查询列，查询过程无须回表（需要在同一棵索引树）功能得到提高。Using Index; Using where表明运用掩盖索引而且用where过滤查询结果：

explain select id,player_name,height,weight from player where player_name like '测验%'

MySQL索引失效十种场景与优化方案


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

MySQL索引失效十种场景与优化方案


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

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

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

MySQL索引失效十种场景与优化方案


3.7.3 失效场景二：索引中止

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

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

MySQL索引失效十种场景与优化方案


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

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

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

MySQL索引失效十种场景与优化方案


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

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

explain select * from player where weight = 150

MySQL索引失效十种场景与优化方案


4 文章总结

本文榜首进行测验数据预备，第二介绍执行计划相关常识，第三介绍索引失效10种场景：查询类型过错，索引列参加运算，过错运用通配符，未用到掩盖索引，OR衔接无索引字段，MySQL抛弃运用索引，联合索引中索引不完好，索引中止，非等值匹配，最左索引缺失。


5 延伸阅览

MySQL深分页问题原理与三种解决方案