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

1 数据准备

1.1 新建数据表

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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万条数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

@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 失效场景

?

1	explain select * from player where type = 0

3.1.2 解决方案

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

3.2 索引列参与运算

3.2.1 失效场景

?

1	explain select * from player where height + 1 > 189

3.2.2 解决方案

?

1	explain select * from player where height > 188

3.3 MySQL放弃使用索引

3.3.1 失效场景

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

?

1	explain select * from player where height > 187

3.3.2 解决方案一

调整查询条件值：

?

1	explain select * from player where height > 188

3.3.3 解决方案二

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

3.4 错误使用通配符

3.4.1 数据准备

避免出现3.3章节失效问题此处修改一条数据：

?

1	update player set player_name = '测试球员' where id = 1

3.4.2 失效场景一

?

1	explain select * from player where player_name like '%测试'

3.4.3 失效场景二

?

1	explain select * from player where player_name like '%测试%'

3.4.4 解决方案

?

1	explain select * from player where player_name like '测试%'

3.5 OR连接无索引字段

3.5.1 失效场景

type有索引，weight无索引：

?

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

3.5.2 解决方案

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

?

1 2 3 4

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

3.6 未用到覆盖索引

3.6.1 失效场景

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

?

1	explain select * from player where player_name like '测试%'

3.6.2 解决方案

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

?

1	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：

?

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

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

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

?

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

3.7.3 失效场景二：索引中断

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

?

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

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

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

?

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

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

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

?

1	explain select * from player where weight = 150

4 文章总结

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

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

原文链接：https://juejin.cn/post/7230120223000870971