MySQL索引失效的十种场景与优化方案【超详细讲解】

偏执的太偏执、 2023-09-27 20:02 15阅读 0赞

## 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 ###

执行计划中访问类型是重要分析指标：

![Image 1][]

[Image 1]: