开发者

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

目录
  • 1 数据准备
    • 1.1 新建数据表
    • 1.2 新增100万条数据
  • 2 基础知识
    • 2.1 explain type
    • 2.2 explain Extra
  • 3 索引失效场景
    • 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 失效场景
      • js
      • 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(javascript).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类型,查询必须使用相同类型:

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编程客栈 * fro开发者_mariadbm player where height > 187

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

 

3.3.2 解决方案一

调整查询条件值:

explain select * from player where height > 188

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

 

3.3.3 解决方案二

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

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放弃使用索引,联合索引中索引不完整,索引中断,非等值匹配,最左索引缺失。

以上就是MySQL索引失效十种场景与优化方案的详细内容,更多关于MySQL索引失效的资料请关注我们其它相关文章!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新数据库

数据库排行榜