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

德滨

1 数据准备


1.1 新建数据表

1. CREATE TABLE `player` (
   
2.   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
   
3.   `player_id` varchar(256) NOT NULL COMMENT '运动员编号',
   
4.   `player_name` varchar(256) NOT NULL COMMENT '运动员名称',
   
5.   `height` int(11) NOT NULL COMMENT '身高',
   
6.   `weight` int(11) NOT NULL COMMENT '体重',
   
7.   `type` varchar(256) DEFAULT '0' COMMENT '球员类型',
   
8.   `game_performance` text COMMENT '最近一场比赛表现',
   
9.   PRIMARY KEY (`id`),
   
10.   KEY `idx_name_height_weight` (`player_name`,`height`,`weight`),
    
11.   KEY `idx_type` (`type`),
    
12.   KEY `idx_height` (`height`)
    
13. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

复制代码

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

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

1.2 新增100万条数据

1. @SpringBootTest(classes = TestApplication.class)
   
2. @RunWith(SpringJUnit4ClassRunner.class)
   
3. public class PlayerServiceTest {
   
4. 
   
5.     @Resource
   
6.     private PlayerRepository playerRepository;
   
7. 
   
8.     @Test
   
9.     public void initBigData() {
   
10.         for (int i = 0; i < 1000000; i++) {
    
11.             PlayerEntity entity = new PlayerEntity();
    
12.             entity.setPlayerId(UUID.randomUUID().toString());
    
13.             entity.setPlayerName("球员_" + System.currentTimeMillis());
    
14.             entity.setType("0");
    
15.             entity.setWeight(150);
    
16.             entity.setHeight(188);
    
17.             entity.setGamePerformance("{"runDistance":8900.0,"passSuccess":80.12,"scoreNum":3}");
    
18.             playerRepository.insert(entity);
    
19.         }
    
20.     }
    
21. }

复制代码

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 解决方案

数据表定义

1. type

复制代码

字段为

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

1. height

复制代码

字段值全部是

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

1. type

复制代码

有索引，

1. weight

复制代码

无索引：

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

复制代码

3.5.2 解决方案

1. weight

复制代码

新增索引，

1. union

复制代码

拼装查询数据

1. explain
   
2. select * from player where type = '0'
   
3. union
   
4. select * from player where weight = 150

复制代码

3.6 未用到覆盖索引


3.6.1 失效场景

1. Using index condition

复制代码

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

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

复制代码

3.6.2 解决方案

覆盖索引含义是查询时索引列完全包含查询列，查询过程无须回表（需要在同一棵索引树）性能得到提升。

1. Using Index; Using where

复制代码

表示使用覆盖索引并且用

1. where

复制代码

过滤查询结果：

1. explain select id,player_name,height,weight from player where player_name like '测试%'

复制代码

3.7 联合索引失效


3.7.1 完整使用

联合索引

1. idx_name_height_weight

复制代码

完整使用

1. key_len

复制代码

=778：

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

复制代码

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

1. weight

复制代码

不在查询条件，所以只用到

1. idx_name_height

复制代码

，所以

1. key_len

复制代码

= 774：

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

复制代码

3.7.3 失效场景二：索引中断

1. height

复制代码

不在查询条件，所以只用到

1. idx_name

复制代码

，所以

1. key_len

复制代码

= 770：

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

复制代码

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

1. height

复制代码

非等值匹配，所以只用到

1. idx_name_height

复制代码

，所以

1. key_length

复制代码

=774：

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

复制代码

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

1. player_name

复制代码

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

1. explain select * from player where weight = 150

复制代码

4 文章总结

本文第一进行测试数据准备，第二介绍执行计划相关知识，第三介绍索引失效10种场景：查询类型错误，索引列参与运算，错误使用通配符，未用到覆盖索引，OR连接无索引字段，MySQL放弃使用索引，联合索引中索引不完整，索引中断，非等值匹配，最左索引缺失。
以上就是MySQL索引失效十种场景与优化方案的详细内容，更多关于MySQL索引失效的资料请关注脚本之家其它相关文章！

来源:https://www.jb51.net/article/283474.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！