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

数据运维 2023-06-09 共饮一杯手机阅读

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

                        目录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 &lt; 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);
    }
}

}